DIAdem

cancel
Showing results for 
Search instead for 
Did you mean: 

Change in SQL commands from DIAdem 2014 to DIAdem 2015

Solved!
Go to solution

Hello!

At this moment I am working in DIAdem 2014 for retrieving some data from an Oracle Database. I'm using the next command to connect and everything works fine.

Call SQL_Connect("Data_Source_Name","User_ID","PASSWORD")

However, a migration to DIAdem 2015 is planned and the SQL commands will not be available. For this, I am trying to use the ADOBD objects as follows:

Dim oDBConn

set oDBConn=CreateObject("ADODB.Connection")

oDBConn.ConnectionString=" Data Source= Data_Source_Name ; Username=User_ID; Password=PASSWORD"

oDBConn.open

But the next error message is always shown:

[Oracle][ODBC][Ora]ORA-01017: invalid username/password; logon denied

 

Can anybody help me with this?

 

Thank you,

 

Andres Uribe

 

 

 

0 Kudos
Message 1 of 6
(2,140 Views)
Solution
Accepted by topic author AndresUribe

Hi Andres,

 

This works for me...

OPTION EXPLICIT
Dim z, zMax, ADO, RecordSet, Table, ColStr, CondStr, SortStr, ConnectionStr, QueryStr, RowVals, Channels, AsPos

ConnectionStr = "DSN=Data_Source_Name;UID=User_ID;PWD=PASSWORD;"

' construct the SQL query to execute
Table   = "DataTableWrite"
ColStr  = "*" ' "DatID,Name,Owner"
CondStr = " WHERE DatID < 200"
SortStr = " ORDER BY Name"
QueryStr = "SELECT " & ColStr & " FROM " & Table & CondStr & SortStr
MsgBox QueryStr

' Connect to the data base
Set ADO = CreateObject("ADODB.Connection")
ADO.Open ConnectionStr 

' Execute the query and import the resulting data records into a VBScript variable
Set RecordSet = ADO.Execute(QueryStr)
RowVals = RecordSet.GetRows()

' Send the resulting data records from the query to new channels in the Data Portal
Call DataDelAll
Call GroupCreate(Table)
Call GroupDefaultSet(GroupCount)
Channels = Split(ColStr, ",")
IF Trim(ColStr) = "*" THEN
  zMax = RecordSet.Fields.Count-1
  ReDim Channels(zMax)
  FOR z = 0 TO zMax
    Channels(z) = RecordSet.Fields(z).Name
  NEXT ' z
END IF
For z = 0 To UBound(Channels)
  AsPos = InStr(UCase(Channels(z)), " AS ")
  IF AsPos > 1 THEN Channels(z) = Trim(Mid(Channels(z), AsPos+1))
  Channels(z) = "[" & GroupDefaultGet & "]/" & Channels(z)
Next ' z
Channels = ArrayToChannels(RowVals, Channels, 1)

' Disconnect from the database and output the query used and any errors
ADO.Close

Brad Turpin

DIAdem Product Support Engineer

National Instruments

Message 2 of 6
(2,085 Views)

Thank you so much for your help Smiley Happy

 

Andres Uribe

0 Kudos
Message 3 of 6
(2,080 Views)

Hi Brad,

I connected to SQL Server successfully with your instructions. But now I want to know if I can sent the table to the navigator panel, like the following picture?1.png

 

0 Kudos
Message 4 of 6
(1,023 Views)

Hi wyy645,

 

The "SQL Table Reader" that shipped with 32bit versions of DIAdem does not work in 64bit versions of DIAdem and does not ship with them.  It's hard to tell from your tiny picture, but it looks like you have your data tables displaying in the tree view of the NAVIGATOR panel, as if you used the "SQL Table Reader" to show your data base as a "Data Store".  From there, in 32bit versions of DIAdem, you can just drag an entire SQL table or selected columns from the NAVIGATOR tree view into the Data Portal to load them.  This no longer works for 64bit versions of DIAdem.

 

However, the VBScript posted above will work for both 32bit and 64bit versions of DIAdem, and it does load the columns from a particular SQL table into the Data Portal, so I'm a little puzzled by your question.  The operative line to focus on is the one with the "ArrayToChannels()" command in it.

 

Brad Turpin

DIAdem Product Support Engineer

National Instruments

0 Kudos
Message 5 of 6
(883 Views)

I got that. Thanks a lot.

0 Kudos
Message 6 of 6
(877 Views)