Appendix E Comparison Between Embedded SQL and ODBC
- ODBC to Embedded SQL
- Embedded SQL to ODBC

APPENDIX E COMPARISON BETWEEN EMBEDDED SQL AND ODBC

This appendix compares ODBC and embedded SQL.

ODBC to Embedded SQL

The following table compares core ODBC functions to embedded SQL statements. This comparison is based on the X/Open and SQL Access Group SQL CAE specification (1992).

ODBC uses a parameter marker in place of a host variable, wherever a host variable would occur in embedded SQL.

The SQL language is based on the X/Open and SQL Access Group SQL CAE specification (1992).

ODBC Function	Statement	Comments
SQLAllocEnv	none	Driver Manager and driver memory allocation.
SQLAllocConnect	none	Driver Manager and driver memory allocation.
SQLConnect	CONNECT	Association management.
SQLAllocStmt	none	Driver Manager and driver memory allocation.
SQLPrepare	PREPARE	The prepared SQL string can contain any of the valid preparable functions as defined by the X/Open specification, including ALTER, CREATE, cursor-specification, searched DELETE, dynamic SQL positioned DELETE, DROP, GRANT, INSERT, REVOKE, searched UPDATE, or dynamic SQL positioned UPDATE.
SQLBindParameter	SET DESCRIPTOR	Dynamic SQL ALLOCATE DESCRIPTOR and dynamic SQL SET DESCRIPTOR. ALLOCATE DESCRIPTOR would normally be issued on the first call to SQLBindParameter for an hstmt. Alternatively, ALLOCATE DESCRIPTOR can be called during SQLAllocStmt, although this call would be unneeded by SQL statements containing no embedded parameters. The descriptor name is generated by the driver.
SQLSetCursorName	none	The specified cursor name is used in the DECLARE CURSOR statement generated by SQLExecute or SQLExecDirect.
SQLGetCursorName	none	Driver cursor name management.
SQLExecute	EXECUTE or DECLARE CURSOR and OPEN CURSOR	Dynamic SQL EXECUTE. If the SQL statement requires a cursor, then a dynamic SQL DECLARE CURSOR statement and a dynamic SQL OPEN are issued at this time.
SQLExecDirect	EXECUTE IMMEDIATE or DECLARE CURSOR and OPEN CURSOR	The ODBC function call provides for support for a cursor specification and statements allowed in an EXECUTE IMMEDIATE dynamic SQL statement. In the case of a cursor specification, the call corresponds to static SQL DECLARE CURSOR and OPEN statements.
SQLNumResultCols	GET DESCRIPTOR	COUNT form of dynamic SQL GET DESCRIPTOR.
SQLColAttributes	GET DESCRIPTOR	COUNT form of dynamic SQL GET DESCRIPTOR or VALUE form of dynamic SQL GET DESCRIPTOR with field-name in {NAME, TYPE, LENGTH, PRECISION, SCALE, NULLABLE}.
SQLDescribeCol	GET DESCRIPTOR	VALUE form of dynamic SQL GET DESCRIPTOR with field-name in {NAME, TYPE, LENGTH, PRECISION, SCALE, NULLABLE}.
SQLBindCol	none	This function establishes output buffers that correspond in usage to host variables for static SQL FETCH, and to an SQL DESCRIPTOR for dynamic SQL FETCH cursor USING SQL DESCRIPTOR descriptor.
SQLFetch	FETCH	Static or dynamic SQL FETCH. If the call is a dynamic SQL FETCH, then the VALUE form of GET DESCRIPTOR is used, with field-name in {DATA, INDICATOR}. DATA and INDICATOR values are placed in output buffers specified in SQLBindCol.
SQLRowCount	GET DIAGNOSTICS	Requested field ROW_COUNT.
SQLFreeStmt (SQL_CLOSE option)	CLOSE	Dynamic SQL CLOSE.
SQLFreeStmt (SQL_DROP option)	none	Driver Manager and driver memory deallocation.
SQLTransact	COMMIT WORK or COMMIT ROLLBACK	None.
SQLDisconnect	DISCONNECT	Association management.
SQLFreeConnect	none	Driver Manager and driver memory deallocation.
SQLFreeEnv	none	Driver Manager and driver memory deallocation.
SQLCancel	none	None.
SQLError	GET DIAGNOSTICS	GET DIAGNOSTICS retrieves information from the SQL diagnostics area that pertains to the most recently executed SQL statement. This information can be retrieved following execution and preceding the deallocation of the statement.

Embedded SQL to ODBC

The following tables list the relationship between the X/Open Embedded SQL language and corresponding ODBC functions. The section number shown in the first column of each table refers to the section of the X/Open and SQL Access Group SQL CAE specification (1992).

Declarative Statements

The following table lists declarative statements.

Section	SQL Statement	ODBC Function	Comments
4.3.1	Static SQL DECLARE CURSOR	none	Issued implicitly by the driver if a cursor specification is passed to SQLExecDirect.
4.3.2	Dynamic SQL DECLARE CURSOR	none	Cursor is generated automatically by the driver. To set a name for the cursor, use SQLSetCursorName. To retrieve a cursor name, use SQLGetCursorName.

Data Definition Statements

The following table lists data definition statements.

Section	SQL Statement	ODBC Function	Comments
5.1.2 5.1.3 5.1.4 5.1.5 5.1.6 5.1.7 5.1.8 5.1.9	ALTER TABLE CREATE INDEX CREATE TABLE CREATE VIEW DROP INDEX DROP TABLE DROP VIEW GRANT REVOKE	SQLPrepare, SQLExecute, or SQLExecDirect	None.

Data Manipulation Statements

The following table lists data manipulation statements.

Section	SQL Statement	ODBC Function	Comments
5.2.1	CLOSE	SQLFreeStmt (SQL_CLOSE option)	None.
5.2.2	Positioned DELETE	SQLExecDirect(..., "DELETE FROM table-name WHERE CURRENT OF cursor-name")	Driver-generated cursor-name can be obtained by calling SQLGetCursorName.
5.2.3	Searched DELETE	SQLExecDirect(..., "DELETE FROM table-name WHERE search-condition")	None.
5.2.4	FETCH	SQLFetch	None.
5.2.5	INSERT	SQLExecDirect (...,"INSERT INTO table-name ...")	Can also be invoked by SQLPrepare and SQLExecute.
5.2.6	OPEN	none	Cursor is OPENed implicitly by SQLExecute or SQLExecDirect when a SELECT statement is specified.
5.2.7	SELECT ...INTO	none	Not supported.
5.2.8	Positioned UPDATE	SQLExecDirect(..., "UPDATE table-name SET column-identifier = expression ...WHERE CURRENT OF cursor-name")	Driver-generated cursor-name can be obtained by calling SQLGetCursorName.
5.2.9	Searched UPDATE	SQLExecDirect(..., "UPDATE table-name SET column-identifier = expression ...WHERE search-condition")	None.

Dynamic SQL Statements

The following table lists dynamic SQL statements.

Section	SQL Statement	ODBC Function	Comments
5.3 (see 5.2.1)	Dynamic SQL CLOSE	SQLFreeStmt (SQL_CLOSE option)	None.
5.3(see5.2.2)	Dynamic SQL Positioned DELETE	SQLExecDirect(..., "DELETE FROM table-name WHERE CURRENT OF cursor-name")	Can also be invoked by SQLPrepare and SQLExecute.
5.3(see5.2.8)	Dynamic SQL Positioned UPDATE	SQLExecDirect(..., "UPDATE table-name SET column-identifier = expression ...WHERE CURRENT OF cursor-name")	Can also be invoked by SQLPrepare and SQLExecute.
5.3.3	ALLOCATE DESCRIPTOR	None	Descriptor information is implicitly allocated and attached to the hstmt by the driver. Allocation occurs at either the first call to SQLBindParameter or at SQLExecute or SQLExecDirect time.
5.3.4	DEALLOCATE DESCRIPTOR	SQLFreeStmt (SQL_DROP option)	None.
5.3.5	DESCRIBE	none	None.
5.3.6	EXECUTE	SQLExecute	None.
5.3.7	EXECUTE IMMEDIATE	SQLExecDirect	None.
5.3.8	Dynamic SQL FETCH	SQLFetch	None.
5.3.9	GET DESCRIPTOR	SQLNumResultCols SQLDescribeCol SQLColAttributes	COUNT FORM. VALUE form with field-name in {NAME, TYPE, LENGTH, PRECISION, SCALE, NULLABLE}.
5.3.10	Dynamic SQL OPEN	SQLExecute	None.
5.3.11	PREPARE	SQLPrepare	None.
5.3.12	SET DESCRIPTOR	SQLBindParameter	SQLBindParameter is associated with only one hstmt where a descriptor is applied to any number of statements with USING SQL DESCRIPTOR.

Transaction Control Statements

The following table lists transaction control statements.

Section	SQL Statement	ODBC Function	Comments
5.4.1	COMMIT WORK	SQLTransact (SQL_COMMIT option)	None.
5.4.2	ROLLBACK WORK	SQLTransact (SQL_ROLLBACK option)	None.

Association Management Statements

The following table lists association management statements.

Section	SQL Statement	ODBC Function	Comments
5.5.1	CONNECT	SQLConnect	None.
5.5.2	DISCONNECT	SQLDisconnect	ODBC does not support DISCONNECT ALL.
5.5.3	SET CONNECTION	None	The SQL Access Group (SAG) Call Level Interface allows for multiple simultaneous connections to be established, but only one connection to be active at one time. SAG-compliant drivers track which connection is active, and automatically switch to a different connection if a different connection handle is specified. However, the active connection must be in a state that allows the connection context to be switched, in other words, there must not be a transaction in progress on the current connection. Drivers that are not SAG-compliant are not required to support this behavior. That is, drivers that are not SAG-compliant are not required to return an error if the driver and its associated data source can simultaneously support multiple active connections.

Diagnostic Statement

The following table lists the GET DIAGNOSTIC statement.

Section	SQL Statement	ODBC Function	Comments
5.6.1	GET DIAGNOSTICS	SQLError SQLRowCount	For SQLError, the following fields from the diagnostics area are available: RETURNED_SQLSTATE, MESSAGE_TEXT, and MESSAGE_LENGTH. For SQLRowCount, the ROW_COUNT field is available.