Preparing SQL requests
These functions prepare SQL requests:
-
SQLAllocHandle
This allocates memory for statement handle called with SQL_HANDLE_STMT. The value of the Tcl variable passed as the last argument is set on return to the Tcl statement handle.
odbc SQLAllocHandle SQL_HANDLE_STMT $hdbc hstmt
-
SQLPrepare
This runs a SQL statement multiple times. The statement can contain one or more parameter markers, such as embedded question marks
[?]
.odbc SQLPrepare $hstmt $insert SQL_NTS
-
SQLBindParameter
This binds a buffer to a parameter marker in a SQL statement.
For "data at statement running" parameters, the C SQL_LEN_DATA_AT_EXEC (length) macro has been supported in Tcl as a command. It may be used to supply the StrLen_or_IndPtr argument to SQLBindParameter.
In this case, the Tcl interface requires the ParameterValuePtr argument to be specified as a number. Typically, this is the parameter number.
set insert "INSERT INTO NAMEID VALUES (?, ?, ?)" odbc SQLBindParameter $hstmt 1 SQL_PARAM_INPUT \ SQL_C_SLONG SQL_INTEGER 0 0 id 0 NULL odbc SQLBindParameter $hstmt 1 SQL_PARAM_INPUT \ SQL_C_SLONG SQL_INTEGER 0 0 1 0\ StrLen_or_IndPtr1 set StrLen_or_IndPtr1 [SQL_LEN_DATA_AT_EXEC 0] odbc SQLBindParameter $hstmt 2 SQL_PARAM_INPUT \ SQL_C_DOUBLE SQL_DOUBLE 0 0 dTag 0 NULL odbc SQLBindParameter $hstmt 3 SQL_PARAM_INPUT \ SQL_C_CHAR SQL_CHAR 50 0 name 50 NULL
-
SQLSetStmtAttr
This sets an attribute that permits multiple values for input parameters. This permits bulk inserts. The Tcl variable bound with SQLBindParameter is expected to be a Tcl array with indexes "0," "1," "2," and so on.
odbc SQLSetStmtAttr $hstmt SQL_ATTR_PARAMSET_SIZE 7 0
-
SQLSetCursorName
This sets the positioned update and delete. In positioned update/delete, a result set is retrieved and searched for a particular record. It is usually retrieved with a SELECT statement containing a "FOR UPDATE" clause. That record can be modified or deleted using the cursor name assigned with SQLSetCursorName. It can also be modified by retrieving the implicit cursor name generated by the driver with SQLGetCursorName.
odbc SQLSetCursorName $hstmt MyCursor 8 odbc SQLGetCursorName $hstmt CursorName 12 \ NameLengthPtr
-
SQLSetStmtAttr
This sets a multitude of statement attributes including cursor characteristics.
odbc SQLSetStmtAttr $hstmt \ SQL_ATTR_CURSOR_SCROLLABLE SQL_SCROLLABLE 0