12.4.39    SQLPREPARE

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