An application can submit any SQL statement supported by a data source. ODBC defines a standard syntax for SQL statements. For maximum interoperability, an application should only submit SQL statements that use this syntax; the driver will translate these statements to the syntax used by the data source. If an application submits an SQL statement that does not use the ODBC syntax, the driver passes it directly to the data source.
Note For CREATE TABLE and ALTER TABLE statements, applications should use the data type name returned by SQLGetTypeInfo in the TYPE_NAME column, rather than the data type name defined in the SQL grammar.
Note that statements can be executed a single time with SQLExecDirect or prepared with SQLPrepare and executed multiple times with SQLExecute. Note also that an application calls SQLTransact to commit or roll back a transaction.
Before an application can submit an SQL statement, it must allocate a statement handle for the statement. To allocate a statement handle, an application:
- 1. Declares a variable of type HSTMT. For example, the application could use the declaration:
HSTMT hstmt1;
- 2. Calls SQLAllocStmt and passes it the address of the variable and the connected hdbc with which to associate the statement. The driver allocates memory to store information about the statement, associates the statement handle with the hdbc, and returns the statement handle in the variable.
An application can submit an SQL statement for execution in two ways:
These options are similar, though not identical to, the prepared and immediate options in embedded SQL. For a comparison of the ODBC functions and embedded SQL, see Appendix E, "Comparison Between Embedded SQL and ODBC."
An application should prepare a statement before executing it if either of the following is true:
- The application will execute the statement more than once, possibly with intermediate changes to parameter values.
- The application needs information about the result set prior to execution.
A prepared statement executes faster than an unprepared statement because the data source compiles the statement, produces an access plan, and returns an access plan identifier to the driver. The data source minimizes processing time as it does not have to produce an access plan each time it executes the statement. Network traffic is minimized because the driver sends the access plan identifier to the data source instead of the entire statement.
Important Committing or rolling back a transaction, either by calling SQLTransact or by using the SQL_AUTOCOMMIT connection option, can cause the data source to delete the access plans for all hstmts on an hdbc. For more information, see the SQL_CURSOR_COMMIT_BEHAVIOR and SQL_CURSOR_ROLLBACK_BEHAVIOR information types in SQLGetInfo.
- 1. Calls SQLPrepare to prepare the statement.
- 2. Sets the values of any statement parameters. For more information, see "Setting Parameter Values" later in this chapter.
- 3. Retrieves information about the result set, if necessary. For more information, see "Determining the Characteristics of a Result Set" in Chapter 7, "Retrieving Results."
- 4. Calls SQLExecute to execute the statement.
- 5. Repeats steps 2 through 4 as necessary.
An application should execute a statement directly if both of the following are true:
- The application will execute the statement only once.
- The application does not need information about the result set prior to execution.
- 1. Sets the values of any statement parameters. For more information, see "Setting Parameter Values" later in this chapter.
- 2. Calls SQLExecDirect to execute the statement.
An SQL statement can contain parameter markers that indicate values that the driver retrieves from the application at execution time. For example, an application might use the following statement to insert a row of data into the EMPLOYEE table:
INSERT INTO EMPLOYEE (NAME, AGE, HIREDATE)
VALUES (?, ?, ?)
An application uses parameter markers instead of literal values if:
- It needs to execute the same prepared statement several times with different parameter values.
- The parameter values are not known when the statement is prepared.
- The parameter values need to be converted from one data type to another.
To set a parameter value, an application performs the following steps in any order:
- Calls SQLBindParameter to bind a storage location to a parameter marker and specify the data types of the storage location and the column associated with the parameter, as well as the precision and scale of the parameter.
- Places the parameter’s value in the storage location.
These steps can be performed before or after a statement is prepared, but must be performed before a statement is executed.
Parameter values must be placed in storage locations in the C data types specified in SQLBindParameter. For example:
Storage locations remain bound to parameter markers until the application calls SQLFreeStmt with the SQL_RESET_PARAMS option or the SQL_DROP option. An application can bind a different storage area to a parameter marker at any time by calling SQLBindParameter. An application can also change the value in a storage location at any time. When a statement is executed, the driver uses the current values in the most recently defined storage locations.
In auto-commit mode, every SQL statement is a complete transaction, which is automatically committed. In manual-commit mode, a transaction consists of one or more statements. In manual-commit mode, when an application submits an SQL statement and no transaction is open, the driver implicitly begins a transaction. The transaction remains open until the application commits or rolls back the transaction with SQLTransact.
If a driver supports the SQL_AUTOCOMMIT connection option, the default transaction mode is auto-commit; otherwise, it is manual-commit. An application calls SQLSetConnectOption to switch between manual-commit and auto-commit mode. Note that if an application switches from manual-commit to auto-commit mode, the driver commits any open transactions on the connection.
Applications should call SQLTransact, rather than submitting a COMMIT or ROLLBACK statement, to commit or roll back a transaction. The result of a COMMIT or ROLLBACK statement depends on the driver and its associated data source.
Important Committing or rolling back a transaction, either by calling SQLTransact or by using the SQL_AUTOCOMMIT connection option, can cause the data source to close the cursors and delete the access plans for all hstmts on an hdbc. For more information, see the SQL_CURSOR_COMMIT_BEHAVIOR and SQL_CURSOR_ROLLBACK_BEHAVIOR information types in SQLGetInfo.
ODBC extends the X/Open and SQL Access Group Call Level Interface to provide additional functions related to SQL statements. ODBC also extends the X/Open and SQL Access Group SQL CAE specification (1992) to provide common extensions to SQL. The remainder of this chapter describes these functions and SQL extensions.
To determine if a driver supports a specific function, an application calls SQLGetFunctions. To determine if a driver supports a specific ODBC extension to SQL, such as outer joins or procedure invocation, an application calls SQLGetInfo.
The following functions, known as catalog functions, return information about a data source’s catalog:
- SQLTables returns the names of tables stored in a data source.
- SQLTablePrivileges returns the privileges associated with one or more tables.
- SQLColumns returns the names of columns in one or more tables.
- SQLColumnPrivileges returns the privileges associated with each column in a single table.
- SQLPrimaryKeys returns the names of columns that comprise the primary key of a single table.
- SQLForeignKeys returns the names of columns in a single table that are foreign keys. It also returns the names of columns in other tables that refer to the primary key of the specified table.
- SQLSpecialColumns returns information about the optimal set of columns that uniquely identify a row in a single table or the columns in that table that are automatically updated when any value in the row is updated by a transaction.
- SQLStatistics returns statistics about a single table and the indexes associated with that table.
- SQLProcedures returns the names of procedures stored in a data source.
- SQLProcedureColumns returns a list of the input and output parameters, as well as the names of columns in the result set, for one or more procedures.
Each function returns the information as a result set. An application retrieves these results by calling SQLBindCol and SQLFetch.
To send parameter data at statement execution time, such as for parameters of the SQL_LONGVARCHAR or SQL_LONGVARBINARY types, an application uses the following three functions:
To indicate that it plans to send parameter data at statement execution time, an application calls SQLBindParameter and sets the pcbValue buffer for the parameter to the result of the SQL_LEN_DATA_AT_EXEC(length) macro. If the fSqlType argument is SQL_LONGVARBINARY or SQL_LONGVARCHAR and the driver returns "Y" for the SQL_NEED_LONG_DATA_LEN information type in SQLGetInfo, length is the total number of bytes of data to be sent for the parameter; otherwise, it is ignored.
The application sets the rgbValue argument to a value that, at run time, can be used to retrieve the data. For example, rgbValue might point to a storage location that will contain the data at statement execution time or to a file that contains the data. The driver returns the value to the application at statement execution time.
When the driver processes a call to SQLExecute or SQLExecDirect and the statement being executed includes a data-at-execution parameter, the driver returns SQL_NEED_DATA. To send the parameter data, the application:
- 1. Calls SQLParamData, which returns rgbValue (as set with SQLBindParameter) for the first data-at-execution parameter.
- 2. Calls SQLPutData one or more times to send data for the parameter. (More than one call will be needed if the data value is larger than the buffer; multiple calls are allowed only if the C data type is character or binary and the SQL data type is character, binary, or data source–specific.)
- 3. Calls SQLParamData again to indicate that all data has been sent for the parameter. If there is another data-at-execution parameter, the driver returns rgbValue for that parameter and SQL_NEED_DATA for the function return code. Otherwise, it returns SQL_SUCCESS for the function return code.
- 4. Repeats steps 2 and 3 for the remaining data-at-execution parameters.
For additional information, see the description of SQLBindParameter Chapter 13, "Function Reference."
To specify multiple sets of parameter values for a single SQL statement, an application calls SQLParamOptions. For example, if there are ten sets of column values to insert into a table — and the same SQL statement can be used for all ten operations — the application can set up an array of values, then submit a single INSERT statement.
If an application uses SQLParamOptions, it must allocate enough memory to handle the arrays of values.
By default, a driver executes ODBC functions synchronously; the driver does not return control to an application until a function call completes. If a driver supports asynchronous execution, however, an application can request asynchronous execution for the functions listed below. (All of these functions either submit requests to a data source or retrieve data. These operations may require extensive processing.)
Asynchronous execution is performed on a statement-by-statement basis. To execute a statement asynchronously, an application:
- 1. Calls SQLSetStmtOption with the SQL_ASYNC_ENABLE option to enable asynchronous execution for an hstmt. (To enable asynchronous execution for all hstmts associated with an hdbc, an application calls SQLSetConnectOption with the SQL_ASYNC_ENABLE option.)
- 2. Calls one of the functions listed earlier in this section and passes it the hstmt. The driver begins asynchronous execution of the function and returns SQL_STILL_EXECUTING.
Note If the application calls a function that cannot be executed asynchronously, the driver executes the function synchronously.
- 3. Performs other operations while the function is executing asynchronously. The application can call any function with a different hstmt or an hdbc not associated with the original hstmt. With the original hstmt and the hdbc associated with that hstmt, the application can only call the original function, SQLAllocStmt, SQLCancel, or SQLGetFunctions.
- 4. Calls the asynchronously executing function to check if it has finished. While the arguments must be valid, the driver ignores all of them except the hstmt argument. For example, suppose an application called SQLExecDirect to execute a SELECT statement asynchronously. When the application calls SQLExecDirect again, the return value indicates the status of the SELECT statement, even if the szSqlStr argument contains an INSERT statement.
- If the function is still executing, the driver returns SQL_STILL_EXECUTING and the application must repeat steps 3 and 4. If the function has finished, the driver returns a different code, such as SQL_SUCCESS or SQL_ERROR. For information about canceling a function executing asynchronously, see "Terminating Statement Processing" in Chapter 9, "Terminating Transactions and Connections."
- 5. Repeats steps 2 through 4 as needed.
To disable asynchronous execution for an hstmt, an application calls SQLSetStmtOption with the SQL_ASYNC_ENABLE option. To disable asynchronous execution for all hstmts associated with an hdbc, an application calls SQLSetConnectOption with the SQL_ASYNC_ENABLE option.
ODBC drivers for SOLID Server do not support asynchronous execution.
ODBC defines the following extensions to SQL, which are common to most DBMS’s:
- Date, time, and timestamp data
- Scalar functions such as numeric, string, and data type conversion functions
- LIKE predicate escape characters
- Outer joins
- Procedures
The syntax defined by ODBC for these extensions uses the escape clause provided by the X/Open and SQL Access Group SQL CAE specification (1992) to cover vendor-specific extensions to SQL. Its format is:
--(*vendor(vendor-name), product(product-name)
extension *)--
For the ODBC extensions to SQL, product-name is always "ODBC", since the product defining them is ODBC. Vendor-name is always "Microsoft", since ODBC is a Microsoft product. ODBC also defines a shorthand syntax for these extensions:
Most DBMS’s provide the same extensions to SQL as does ODBC. Because of this, an application may be able to submit an SQL statement using one of these extensions in either of two ways:
- Use the syntax defined by ODBC. An application that uses the ODBC syntax will be interoperable among DBMS’s.
- Use the syntax defined by the DBMS. An application that uses DBMS-specific syntax will not be interoperable among DBMS’s.
Due to the difficulty in implementing some ODBC extensions to SQL, such as outer joins, a driver might only implement those ODBC extensions that are supported by its associated DBMS. To determine whether the driver and data source support all the ODBC extensions to SQL, an application calls SQLGetInfo with the SQL_ODBC_SQL_CONFORMANCE flag. For information about how an application determines whether a specific extension is supported, see the section that describes the extension.
Note Many DBMS’s provide extensions to SQL other than those defined by ODBC. To use one of these extensions, an application uses the DBMS-specific syntax. The application will not be interoperable among DBMS’s.
Date, Time, and Timestamp Data
The escape clauses ODBC uses for date, time, and timestamp data are:
--(*vendor(Microsoft),product(ODBC) d 'value' *)--
--(*vendor(Microsoft),product(ODBC) t 'value' *)--
--(*vendor(Microsoft),product(ODBC) ts 'value' *)--
where d indicates value is a date in the "yyyy-mm-dd" format, t indicates value is a time in the "hh:mm:ss" format, and ts indicates value is a timestamp in the "yyyy-mm-dd hh:mm:ss[.f...]" format. The shorthand syntax for date, time, and timestamp data is:
{d 'value'}
{t 'value'}
{ts 'value'}
For example, each of the following statements updates the birthday of John Smith in the EMPLOYEE table. The first statement uses the escape clause syntax. The second statement uses the shorthand syntax. The third statement uses the native syntax for a DATE column in DEC’s Rdb and is not interoperable among DBMS’s.
UPDATE EMPLOYEE
SET BIRTHDAY=--(*vendor(Microsoft),product(ODBC)
d '1967-01-15' *)--
WHERE NAME='Smith, John' UPDATE EMPLOYEE SET BIRTHDAY={d '1967-01-15'}
WHERE NAME='Smith, John' UPDATE EMPLOYEE SET BIRTHDAY='15-Jan-1967'
WHERE NAME='Smith, John'
The ODBC escape clauses for date, time, and timestamp literals can be used in parameters with a C data type of SQL_C_CHAR. For example, the following statement uses a parameter to update the birthday of John Smith in the EMPLOYEE table:
UPDATE EMPLOYEE SET BIRTHDAY=? WHERE NAME='Smith, John'
A storage location of type SQL_C_CHAR bound to the parameter might contain any of the following values. The first value uses the escape clause syntax. The second value uses the shorthand syntax. The third value uses the native syntax for a DATE column in DEC’s Rdb and is not interoperable among DBMS’s.
"--(*vendor(Microsoft),product(ODBC)
d '1967-01-15' *)--" "{d '1967-01-15'}" "'15-Jan-1967'"
An application can also send date, time, or timestamp values as parameters using the C structures defined by the C data types SQL_C_DATE, SQL_C_TIME, and SQL_C_TIMESTAMP.
To determine if a data source supports date, time, or timestamp data, an application calls SQLGetTypeInfo. If a driver supports date, time, or timestamp data, it must also support the escape clauses for date, time, or timestamp literals.
Scalar Functions
Scalar functions — such as string length, absolute value, or current date — can be used on columns of a result set and on columns that restrict rows of a result set. The escape clause ODBC uses for scalar functions is:
--(*vendor(Microsoft),product(ODBC)
fn scalar-function *)--
where scalar-function is one of the functions listed in Appendix F, "Scalar Functions." The shorthand syntax for scalar functions is:
{fn scalar-function}
For example, each of the following statements creates the same result set of uppercase employee names. The first statement uses the escape clause syntax. The second statement uses the shorthand syntax. The third statement uses the native syntax for SOLID Server and is not interoperable among DBMS’s.
SELECT --(*vendor(Microsoft),product(ODBC)
fn UCASE(NAME) *)-- FROM EMPLOYEE SELECT {fn UCASE(NAME)} FROM EMPLOYEE SELECT UCASE(NAME) FROM EMPLOYEE
An application can mix scalar functions that use native syntax and scalar functions that use ODBC syntax. For example, the following statement creates a result set of last names of employees in the EMPLOYEE table. (Names in the EMPLOYEE table are stored as a last name, a comma, and a first name.) The statement uses the ODBC scalar function SUBSTRING and the SQL Server scalar function CHARINDEX and will only execute correctly on SQL Server.
SELECT {fn SUBSTRING(NAME, 1, CHARINDEX(',', NAME) – 1)} FROM EMPLOYEE
To determine which scalar functions are supported by a data source, an application calls SQLGetInfo with the SQL_NUMERIC_FUNCTIONS, SQL_STRING_FUNCTIONS, SQL_SYSTEM_FUNCTIONS, and SQL_TIMEDATE_FUNCTIONS flags.
Data Type Conversion Function
ODBC defines a special scalar function, CONVERT, that requests that the data source convert data from one SQL data type to another SQL data type. The escape clause ODBC uses for the CONVERT function is:
--(*vendor(Microsoft),product(ODBC)
fn CONVERT(value_exp, data_type) *)--
where value_exp is a column name, the result of another scalar function, or a literal value, and data_type is a keyword that matches the #define name used by an ODBC SQL data type (as defined in Appendix D, "Data Types"). The shorthand syntax for the CONVERT function is:
{fn CONVERT(value_exp, data_type)}
For example, the following statement creates a result set of the names and ages of all employees in their twenties. It uses the CONVERT function to convert each employee’s age from type SQL_SMALLINT to type SQL_CHAR. Each resulting character string is compared to the pattern "2%" to determine if the employee’s age is in the twenties.
SELECT NAME, AGE FROM EMPLOYEE WHERE
{fn CONVERT(AGE,SQL_CHAR)} LIKE '2%'
To determine if the CONVERT function is supported by a data source, an application calls SQLGetInfo with the SQL_CONVERT_FUNCTIONS flag. For more information about the CONVERT function, see Appendix F, "Scalar Functions."
LIKE Predicate Escape Characters
In a LIKE predicate, the percent character (%) matches zero or more of any character and the underscore character (_) matches any one character. The percent and underscore characters can be used as literals in a LIKE predicate by preceding them with an escape character. The escape clause ODBC uses to define the LIKE predicate escape character is:
--(*vendor(Microsoft),product(ODBC)
escape 'escape-character' *)--
where escape-character is any character supported by the data source. The shorthand syntax for the LIKE predicate escape character is:
{escape 'escape-character'}
For example, each of the following statements creates the same result set of department names that start with the characters "%AAA". The first statement uses the escape clause syntax. The second statement uses the shorthand syntax. The third statement uses the native syntax for Ingres and is not interoperable among DBMS’s. Note that the second percent character in each LIKE predicate is a wild-card character that matches zero or more of any character.
SELECT NAME FROM DEPT WHERE NAME LIKE '\%AAA%'
--(*vendor(Microsoft),product(ODBC) escape '\'*)-- SELECT NAME FROM DEPT WHERE NAME LIKE '\%AAA%'
{escape '\'} SELECT NAME FROM DEPT WHERE NAME LIKE '\%AAA%'
ESCAPE '\'
To determine whether LIKE predicate escape characters are supported by a data source, an application calls SQLGetInfo with the SQL_LIKE_ESCAPE_CLAUSE information type.
Outer Joins
ODBC supports the ANSI SQL-92 left outer join syntax. The escape clause ODBC uses for outer joins is:
--(*vendor(Microsoft),product(ODBC) oj outer-join *)--
where outer-join is:
table-reference LEFT OUTER JOIN {table-reference | outer-join} ON search-condition
table-reference specifies a table name, and search-condition specifies the join condition between the table-references. The shorthand syntax for outer joins is:
{oj outer-join}
An outer join request must appear after the FROM keyword and before the WHERE clause (if one exists). For complete syntax information, see Appendix C, "SQL Grammar."
For example, each of the following statements creates the same result set of the names and departments of employees working on project 544. The first statement uses the escape clause syntax. The second statement uses the shorthand syntax. The third statement uses the native syntax for Oracle and is not interoperable among DBMS’s.
SELECT EMPLOYEE.NAME, DEPT.DEPTNAME FROM
--(*vendor(Microsoft),product(ODBC) oj EMPLOYEE LEFT OUTER JOIN DEPT ON EMPLOYEE.DEPTID=DEPT.DEPTID*)--
WHERE EMPLOYEE.PROJID=544 SELECT EMPLOYEE.NAME, DEPT.DEPTNAME FROM
{oj EMPLOYEE LEFT OUTER JOIN DEPT ON EMPLOYEE.DEPTID=DEPT.DEPTID}
WHERE EMPLOYEE.PROJID=544 SELECT EMPLOYEE.NAME, DEPT.DEPTNAME FROM EMPLOYEE, DEPT WHERE (EMPLOYEE.PROJID=544) AND
(EMPLOYEE.DEPTID = DEPT.DEPTID (+))
To determine the level of outer joins a data source supports, an application calls SQLGetInfo with the SQL_OUTER_JOINS flag. Data sources can support two-table outer joins, partially support multi-table outer joins, fully support multi-table outer joins, or not support outer joins.
Procedures
An application can call a procedure in place of an SQL statement. The escape clause ODBC uses for calling a procedure is:
--(*vendor(Microsoft),product(ODBC)
[?=] call procedure-name
[([parameter][,[parameter]]...)] *)--
where procedure-name specifies the name of a procedure stored on the data source and parameter specifies a procedure parameter. A procedure can have zero or more parameters and can return a value. The shorthand syntax for procedure invocation is:
{[?=]call procedure-name [([parameter][,[parameter]]...)]}
For output parameters, parameter must be a parameter marker. For input and input/output parameters, parameter can be a literal, a parameter marker, or not specified. If parameter is a literal or is not specified for an input/output parameter, the driver discards the output value. If parameter is not specified for an input or input/output parameter, the procedure uses the default value of the parameter as the input value; the procedure also uses the default value if parameter is a parameter marker and the pcbValue argument in SQLBindParameter is SQL_DEFAULT_PARAM. If a procedure call includes parameter markers (including the "?=" parameter marker for the return value), the application must bind each marker by calling SQLBindParameter prior to calling the procedure.
Note For some data sources, parameter cannot be a literal value. For all data sources, it can be a parameter marker. For maximum interoperability, applications should always use a parameter marker for parameter.
If an application specifies a return value parameter for a procedure that does not return a value, the driver sets the pcbValue buffer specified in SQLBindParameter for the parameter to SQL_NULL_DATA. If the application omits the return value parameter for a procedure returns a value, the driver ignores the value returned by the procedure.
If a procedure returns a result set, the application retrieves the data in the result set in the same manner as it retrieves data from any other result set.
For example, each of the following statements uses the procedure EMPS_IN_PROJ to create the same result set of names of employees working on a project. The first statement uses the escape clause syntax. The second statement uses the shorthand syntax. For an example of code that calls a procedure, see SQLProcedures in Chapter 13, "Function Reference."
--(*vendor(Microsoft),product(ODBC)
call EMPS_IN_PROJ(?)*)-- {call EMPS_IN_PROJ(?)}
To determine if a data source supports procedures, an application calls SQLGetInfo with the SQL_PROCEDURES information type. To retrieve a list of the procedures stored in a data source, an application calls SQLProcedures. To retrieve a list of the input, input/output, and output parameters, as well as the return value and the columns that make up the result set (if any) returned by a procedure, an application calls SQLProcedureColumns.
ODBC also provides the following functions related to SQL statements. For more information about these functions, see Chapter 13, "Function Reference."
Copyright © 1992-1997 Solid Information Technology Ltd All rights reserved.