11-24-2016 06:08 AM - edited 11-24-2016 06:08 AM
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
Solved! Go to Solution.
11-25-2016 10:29 AM
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
11-25-2016 10:44 AM
Thank you so much for your help
Andres Uribe
04-29-2019 01:27 AM
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?
07-09-2019 12:49 PM
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
07-09-2019 09:01 PM
I got that. Thanks a lot.