SQLExecute() function

The SQLExecute function runs a SQL query within a script. If the statement includes a SELECT, the BindList argument designates the name of the Bind List to use for binding the database columns with InTouch tags. If the Bind List is NULL, no tag associations are made.

Category

SQL

Syntax

SQLExecute(ConnectionID,BindList,StatementID);

Arguments

ConnectionID

Name of a memory integer tag that holds the number (ID) assigned by the SQLConnect() function to each database connection.

BindList

The BindList argument can be a zero-length string. If StatementID is associated with a row-returning query, then the logical table is updated with the result of SQLExecute(). If a real Bind List is specified, then the result is associated with the BindList argument. A zero-length Bind List is useful when it is known in advance that the StatementID is not associated with a row-returning query.

StatementID

Integer value returned by SQL when a SQLPrepareStatement() function is used.

Remarks

Errors are returned in the function return. If the statement has been prepared, the statement handle returned from the prepare should be passed. If the statement has not been prepared, the statement handle should be zero.

Note: The SQLExecute() function can be called only once for a statement that has not been prepared. If the statement has been prepared, it can be called multiple times.

A default statement is associated with a connection ID. It can be a textual SQL statement (SELECT, INSERT, DELETE, or UPDATE), the name of a query in MS Access (with or without parameters), or the name of a stored procedure in MS SQL Server (with or without parameters).

The default statement is modified by the SQLLoadStatement(), SQLSetStatement(), and SQLAppendStatement() functions. The default statement is used by SQLExecute() whenever StatementID = 0 is specified.

Examples

This example loads the SQL statements from the lotquery.sql file and places the results of the SELECT statement to InTouch tags specified by the Bind List.

ResultCode = SQLLoadStatement (ConnectionID, "c:\myappdir\lotquery.sql");

ResultCode = SQLExecute (ConnectionID, "BindList", 0);

ResultCode = SQLNext (ConnectionID);

This SQLSetStatement() function must be used for complex queries and string expressions greater than 131 characters. When the string expression exceeds 131 characters use the SQLAppend() function.

SQLSetStatement(ConnectionID, "Select Speed, Ser_No from tablename where Ser_No =’" + Serial_input + "’");

SQLExecute(ConnectionID, "BindList", 0);

In the previous example, the StatementID argument is set to zero so the statement does not have to call SQLPrepareStatement(Connection_Id, StatementID) before the execute statement.

Because the StatementID is not created by the SQLPrepare statement to properly end this SELECT, use the SQLEnd() function instead of the SQLClearStatement() function.

SQLSetStatement(Connection_Id, "Select Speed, Ser_No from tablename where Ser_No =’" + Serial_input + "’");

SQLPrepareStatement(Connection_Id, StatementID);

SQLExecute(Connection_Id, StatementID);

In the above example, the StatementID is created by a SQLPrepareStatement function call and used by the SQLExecute function. To end this SELECT statement, use a SQLClearStatement() function call within a script to free resources and the StatementID.

The SQLExecute() function supports some stored procedures. For example, suppose you create a stored procedure on the database server named "LotInfoProc," that contains the following select statement: "Select LotNo, LotName from LotInfo".

You write the InTouch QuickScript to run the stored procedure based upon the type of database that you are using. The following example shows script statements to run a stored procedure for a SQL Server database.

ResultCode = SQLSetStatement (ConnectionID,"LotInfoProc");

ResultCode = SQLExecute(ConnectionID, "BindList", 0);

ResultCode = SQLNext (ConnectionID);

{Get results of Select}

The following example shows script statements to run a stored procedure for an Oracle database.

ResultCode = SQLSetStatement (ConnectionID, "{CALL LotInfoProc}");

ResultCode = SQLExecute(ConnectionID, "BindList", 0);

ResultCode = SQLNext (ConnectionID);

{Get results of Select}

See Also

SQLConnect(), SQLPrepareStatement()