Examples
- Updated2024-09-12
- 7 minute(s) read
ODBC/SQL > Examples
Examples
This page has two examples for SQL script commands. Each script line is explained.
Example 1
The first example reads the channels Time, Pressure, and Acceleration from the DB1 data store and loads these channels to the Data Portal.
-
The SQL_Connect command connects DIAdem as User1 to the ODBC data store DB1, via the ODBC/SQL interface.
VBScript Python
Call SQL_Connect("DB1","User1","","")
-
The SQL_AutoCommit command disables the Commit mode, so you must confirm each action with Commit.
VBScript Python
Call SQL_AutoCommit(0)
-
The SQL_Select command calls the columns Time, Pressure, and Acceleration from the Measurement1 table and reads the rows that have values greater than 0 from the Time column. DIAdem sorts the rows according to the Time column.
VBScript Python
Call SQL_Select("Time,Pressure,Acceleration","Measurement1","Time>10 ORDER BY Time","")
-
The SQL_Blocksize variable contains the number of rows read from the ODBC DB1 data store. The SQL_Result variable contains the data that has been read, as text in a matrix. The following line reads the third value of the Pressure column to the T1 variable:
VBScript Python
T1 = SQL_Result(3,2)
-
The SQL_Colname variable receives the names of the columns that have been read. The following line reads the column name of the first column Time to the T2 variable:
VBScript Python
T2 = SQL_ColName(1)
-
The SQL_BindVar command creates the Real type TestVariable variable with the length of the Pressure column. The APPEND method specifies that DIAdem appends data to the end of the variable.
VBScript Python
Call SQL_BindVar("Pressure","TestVariable","R","APPEND")
-
The SQL_ResultRows variable receives the number of transferred values. The following line reads the number of values to the L1 variable:
VBScript Python
L1 = SQL_ResultRows
-
The SQL_BindChannel command links the Acceleration column to the third data channel of the Data Portal. This data channel is numeric.
VBScript Python
Call SQL_BindChannel("3","Acceleration","n")
-
In the following line, the SQL_BindChannel command connects the Time column to the Time data channel of the Data Portal. If the Time data channel already exists in the Data Portal, DIAdem overwrites it. If the Time data channel does not exist in the Data Portal, DIAdem creates a data channel with the name of the Time column.
VBScript Python
Call SQL_BindChannel("Time","Time","n")
-
In the following loop, the SQL_FetchNext command continues to call other data until the SQL_Next variable has the value 0.
VBScript Python
Do While SQL_Next Call SQL_FetChNext() Loop
-
In the Pressure column of the Measurement1 table, the SQL_Update command changes the value that is in the same row as the value 7 in the Time column, to 0.7.
VBScript Python
Call SQL_Update("Measurement1","Pressure","0.7","Time=7","")
-
In the following line, the SQL_Transact command undoes the last statement:
VBScript Python
Call SQL_Transact("Rollback")
-
In the Measurement1 table, the SQL_Update command changes the value in the Pressure column, which is in the same row as the value 7 in the Time column, to 7.0.
VBScript Python
Call SQL_Update("Measurement1","Pressure","7.0","Time=7","")
-
The Commit parameter in the SQL_Transact command runs the last statement on the DB1 data store.
VBScript Python
Call SQL_Transact("Commit")
-
The SQL_Insert command inserts a new row in the Measurement1 table, assigns the value 1200 to this row in the Time column, and assigns the value 0.12 to this row in the Pressure column.
VBScript Python
Call SQL_Insert("Measurement1","Time,Pressure","1200,0.12")
-
The Commit parameter in the SQL_Transact command runs the last statement on the DB1 data store.
VBScript Python
Call SQL_Transact("Commit")
-
The SQL_Delete command deletes the rows in the Measurement1 table that have values greater than 1000 in the Time column.
VBScript Python
Call SQL_Delete("Measurement1","Time > 1000","")
-
The Commit parameter in the SQL_Transact command runs the last statement on the DB1 data store.
VBScript Python
Call SQL_Transact("Commit")
-
The SQL_ResultRows variable receives the number of deleted rows and transfers this value to the L1 variable.
VBScript Python
L1 = SQL_ResultRows
-
The SQL_ExecDirect command deletes the Measurement1 table from the current data store.
VBScript Python
Call SQL_ExecDirect("DROP TABLE Measurement1","","","")
-
The SQL_Disconnect command disconnects the DB1 data store via the ODBC/SQL interface.
VBScript Python
Call SQL_Disconnect
Example 2
Example 2 writes data into the DBTest database.
-
The SQL_Connect command connects DIAdem to the ODBC data store DBTest, via the ODBC/SQL interface.
VBScript Python
Call SQL_Connect("DBTest","","")
-
The SQL_Delete command deletes the Test table from the DBTest data store.
VBScript Python
Call SQL_Delete("Test","","")
-
In the following lines, the SQL_Insert command inserts the value 10.2 into the TestReal1 column and inserts the value 100.5 into the TestReal2 column in the Test table.
VBScript Python
R1 = 10.2 R2 = 100.5 T1 = "TestReal1,TestReal2" T2 = str(R1) & "," & str(R2) Call SQL_Insert("Test",T1,T2)
-
In the following lines the SQL_Insert command inserts the text from the T2 text variable into the TestText1 column in the Test table.
VBScript Python
T1 = "TestText1" T2 =""" & "Test1" & """ Call SQL_Insert("Test",T1,T2)
-
In the following lines the SQL_Insert command inserts the text from the T2 text variable into the columns TestText1 and TestText2 in the Test table.
VBScript Python
T1 = "TestText1,TestText2" T3 = "Test1" T4 = "Test2" T2 = """ & T3 & "","" & T4 & """ Call SQL_Insert("Test",T1,T2)
-
In the following lines the SQL_ExecDirect command uses DIAdem text variables to insert several texts into the Test table.
VBScript Python
T3 = "TestExecDirect1" T4 = "TestExecDirect2" T5 = "TestExecDirect3" T1= "Insert into Test (TestText1,TestText2,TestText3) Values (""" & T3 & """,""" & T4 & """,""" & T5 & """ )" Call SQL_ExecDirect(T1,"","")
-
In the following lines the SQL_Insert command uses text variables to insert two dates into the columns TestDate1 and TestDate2 in the Test table. The format depends on the format set for the table field.
VBScript Python
T3 = "27.10.1999" T4 = Str(TTR(CurrDate),"dd.mm.yyyy") T1 = "TestDate1,TestDate2" T2 = """ & T3 & "","" & T4 & """ Call SQL_Insert("Test",T1,T2)
-
The SQL_Disconnect command disconnects the data storage DBTest, if the SQL_ConnectStat variable with the value 1 indicates that this data store is still connected.
VBScript Python
If (SQL_ConnectStat <> 0) Then Call SQL_Disconnect() End If
 | Note Refer to The ODBC/SQL Interface for general information about the ODBC/SQL commands. |
Related Topics
Command: SQL_AutoCommit | Command: SQL_Delete | Command: SQL_ExecDirect | Command: SQL_FetchNext | Command: SQL_Insert | Command: SQL_Select | Command: SQL_Transact | Command: SQL_Update | ODBC/SQL