Description: Places part of an SQL command line into buffer to make a single SQL command line from multiple lines in the script. Used with SQLEXECUTE.
Syntax:
Tcl: SQLPREPARE "partial sql command line"
JScript: SQLPREPARE("partial sql command line");
VB Script: SQLPREPARE "partial sql command line"
Arguments: incomplete SQL command line
Returns: none
See Also: SQLDIRECT, SQLEXECUTE
Examples:
#
WebDATA is an ODBC DSN attached to an Access Database
# COMPARE with SQLDIRECT
entry
SQLPREPARE "CONNECT SESSION 1 TO
WebDATA"
SQLEXECUTE
SQLPREPARE "USE SESSION
1"
SQLEXECUTE
# WebSQLdata is an ODBC DSN attached
to an SQL Server RDBS
#
it needs a user and password
# COMPARE with
SQLDIRECT entry
SQLDIRECT "CONNECT SESSION 1 TO
WebSQLdata, tomc, mypassword"
SQLDIRECT "USE SESSION
1"
#Insert data into
table
#For ODBC Databases that do not
support level 2 commands (e.g. Access)
#
break into two lines using SQL prepare
SQLPREPARE "INSERT INTO Table1
VALUES ('[GETVAL %TTMDATE]',"
SQLPREPARE "'[GETVAL %TTMTIME]',
[GETVAL AMPLITUDE], [GETVAL TIMER])"
SQLEXECUTE
#
declare a pointer to newest data just entered and retrieve newest
data
SQLPREPARE "DECLARE C1 CURSOR FOR
SELECT '[GETVAL %TTMDATE]',"
SQLPREPARE "'[GETVAL %TTMTIME]',
[GETVAL AMPLITUDE], "
SQLPREPARE "[GETVAL TIMER] FROM
Table1"
SQLEXECUTE
SQLPREPARE "OPEN C1"
SQLEXECUTE
SQLPREPARE "FETCH NEXT C1 INTO
text1, text2, text3, text4"
SQLEXECUTE
SQLPREPARE "CLOSE C1"
SQLEXECUTE
SQLPREPARE "DISCONNECT SESSION
1"
SQLEXECUTE
# Get newest 12 records from ODBC database
# WebDATA is an ODBC DSN attached to an Access Database on SCADA Node
SQLDIRECT "CONNECT SESSION 3 TO WebDATA"
SQLDIRECT "USE SESSION 3"
# DECLARE A POINTER TO NEWEST DATA JUST ENTERED
SQLPREPARE "DECLARE C3 CURSOR FOR "
SQLPREPARE "SELECT * FROM Table1 "
SQLPREPARE "ORDER BY Date DESC, Time DESC"
SQLEXECUTE
SQLDIRECT "OPEN C3"
for {set Z 1} {$Z<=12} {incr Z} {
SQLDIRECT "FETCH NEXT C3 INTO text1, text2, text3, text4, text5"
set str1 "[GETVAL text1] [GETVAL text2] [format %9.1f [GETVAL text3]] [format %7.1f [GETVAL text4]] [GETVAL text5]"
set x $Z
array set Array1 [list $Z $str1]
}
SQLDIRECT "CLOSE C3"
SQLDIRECT "DISCONNECT SESSION 3"
This command is used to execute an SQL command that is more than one line long. An SQLEXECUTE must be used when a complete SQL command is formed. SQLPREPARE allows a single large SQL command to be written across multiple lines in a script.
SQLPREPARE with a SQLEXECUTE is the equivalent of a single SQLDIRECT command. However, the Script editor effectively limits an SQLDIRECT command to 1000 characters. For longer than 1000 characters on one SQL command line, use SQLPREPARE and SQLEXECUTE. SQLPREPARE has a 16,000 (16k) character buffer limit.
SQLPREPARE will interact with the related keymacro commands <SQLDIRECT> and <SQLEXECUTE> allowing a script to initialize or open an SQL session and user keymacro commands to enter data.
WebAccess supports both Level 1 and Level 2 SQL statement. SQL is an acronym for Structured Query Language. Please refer to an SQL manual for complete syntax of the SQL language. These commands are used to interact with a Database file like Access or SQL Server through an ODBC DSN.
You can only FETCH and INSERT using IO Tags, Internal Tags and Local Screen Tags. FETCH and INSERT do not support local Tcl variables