From Mysql to Data Portal

[SCKCEN-Engineer]

Dear All,

I am new in DIAdem 12.0, I want to create a VBS script to create queries from a mysql server.
Data is structured in different tables  and in its table data is structured in the way you see below ,
I want to find the sensors with the same sensor_id and make channels with the respective date and value.

For example I used the following commands:

CallSQL_Select("sensor_id,measdate,measvalue","seal_pp_a_final","sensor_id=2 ORDER BY sensor_id","")

Call SQL_BindChannel("date1","measdate","n")

Call SQL_BindChannel("PP-A1","measvalue","n")

It worked but the date values are not in this format 2013-06-19 00:00:00 do you know how I can solve this problem ?

Since there are many sensors can I use a loop for example if I want to read from sensor_id (1-100)

write something like

For i=1 to 100

CallSQL_Select("sensor_id,measdate,measvalue","seal_pp_a_final","sensor_id=i ORDER BY sensor_id","")

Call SQL_BindChannel("date(i)","measdate","n")

Call SQL_BindChannel("PP-A(i)","measvalue","n")

i=i+1

End

Looking forward to your Comments .
Thank you in advance,
Ioannis

Sensor_id	Datetime	Value
1	2013-06-19 00:00:00	1.2
1	2013-06-19 01:00:00	1.3
2	2013-06-19 00:00:00	5.6
2	2013-06-19 01:00:00	5.7
3	2013-06-19 00:00:00	310.2
…	…	…

[Brad_Turpin]

Hi Engineer,

I would strongly advise you to switch to the ADO method of querying the data base columns.  ADO will return the query results as variants, which allows you to handle all the different column data types with no effort.  There are additional advantages to ADO as well.  I adapted a standard example script I use with your column and table names and query and order conditions:

' needs to be set to a valid ADO connection string for data base
ConnectionStr = "Dsn=ABCDEFG;Uid=;Pwd=;"

' construct the SQL query to execute
Table   = "seal_pp_a_final"
ColStr  = "sensor_id,measdate,measvalue"
CondStr = " WHERE sensor_id=2"
SortStr = " ORDER BY sensor_id"
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()
ChanNames = Split(ColStr, ",")

' Send the resulting data records from the query to new channels in the Data Portal
Call DataDelAll
Call GroupCreate(Table)
Call GroupDefaultSet(GroupCount)
Channels = ArrayToChannels(RowVals, ChanNames, 1)

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

Brad Turpin

DIAdem Product Support Engineer

National Instruments