ODBC API

SQLExecDirect executes a prepareable statement, using the current values of the parameter marker variables if any parameters exist in the statement.

SQLExecDirect is the fastest way to submit an SQL statement for one-time execution.

Syntax

RETCODE SQLExecDirect(hstmt, szSqlStr, cbSqlStr)

The SQLExecDirect function accepts the following arguments.

Type	Argument	Use	Description
HSTMT	hstmt	Input	Statement handle
UCHAR FAR *	szSqlStr	Input	SQL statement to be executed
SDWORD	cbSqlStr	Input	Length of szSqlStr

Returns

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_ERROR, SQL_NEED_DATA, SQL_STILL_EXECUTING or SQL_INVALID_HANDLE

Diagnostics

When SQLExecDirect returns SQL_ERROR or SQL_SUCCESS_WITH_INFO, an associated SQLSTATE value may be obtained by calling SQLError. The following table lists the SQLSTATE values commonly returned by SQLExecDirect and explains each one in the context of this function; the notation "(DM)" precedes the descriptions of SQLSTATEs returned by the Driver Manager. The return code associated with each SQLSTATE value is SQL_ERROR, unless noted otherwise.

SQLSTATE	Error	Description
01000	General warning	Driver-specific informational message. (Function returns SQL_SUCCESS_WITH_INFO.)
01004	Data truncated	The argument szSqlStr contained an SQL statement that contained a character or binary parameter or literal and the value exceeded the maximum length of the associated table column. The argument szSqlStr contained an SQL statement that contained a numeric parameter or literal and the fractional part of the value was truncated. The argument szSqlStr contained an SQL statement that contained a date or time parameter or literal and a timestamp value was truncated.
01006	Privilege not revoked	The argument szSqlStr contained an SQL statement that was REVOKE and the user did not have the specified privilege. (Funtion returns SQL_SUCCESS_WITH_INFO.)
01S03	No rows updated or deleted	The argument szSqlStr contained an SQL statement that was a positioned update or delete statement and no rows were updated or deleted. (Function returns SQL_SUCCESS_WITH_INFO.)
01S04	More than one row updated or deleted	The argument szSqlStr contained an SQL statement that was a positioned update or delete statement and more than one row was updated or deleted. (Function returns SQL_SUCCESS_WITH_INFO.
07001	Wrong number of parameters	The number of parameters specified in SQLBindParameter was less than the number of parameters in the prepared statement associatd with the hstmt.
08S01	Communication link failure	The communication link between the driver and the data source to which the driver was connected failed before the function completed processing.
21S01	Insert value list does not match column list	The argument szSqlStr contained an INSERT statement and the number of values to be inserted did not match the derived table.
21S02	Degree of derived table does not match column list	The argument szSqlStr contained a CREATE VIEW statement and the number of names specified is not the same degree as the derived table defined by the query specification.
22003	Numeric value out of range	The argument szSqlStr contained an SQL statement that contained a numeric parameter and the parameter value caused the whole (as opposed to fractional) part of the number to be truncated when assigned to the associated table column.
22005	Error in assignment	The argument szSqlStr contained an SQL statement that contained a parameter and the value was incompatible with the data type of the associated table column.
22008	Datetime field overflow	The argument szSqlStr contained an SQL statement that contained a date, time or timestamp parameter or literal and the value was, respectively, and invalid date, time or timestamp.
22012	Division by Zero	The argument szSqlStr contained an SQL statement that contained an arithmetic expression which caused division by zero.
23000	Integrity constraint violation	The argument szSqlStr contained an SQL statement that contained a parameter. The parameter value was NULL for a column defined as NOT NULL in the associated table column, a duplicate value was supplied for a column constrained to contain only unique values, or some other integrity constraint was violated.
24000	Invalid cursor state	(DM) A cursor was open on the hstmt and SQLFetch or SQLExtendedFetch had been called. A cursor was open on the hstmt but SQLFetch or SQLExtendedFetch had not been called. The argument szSqlStr contained an SQL statement that contained a positioned update or delete statement and the cursor was positioned before the start of the result set or after the end of the result set.
34000	Invalid cursor name	The argument szSqlStr contained a positioned update or delete statement and the cursor referenced by the statement being executed was not open.
37000	Syntax error or access violation	The argument szSqlStr contained an SQL statement that was not preparable or contained a syntax error.
40001	Serialization failure	The transaction to which the argument szSqlStr contained an SQL statement that belonged was terminated to prevent deadlock.
42000	Syntax error or access violation	The user did not have permission to execute the prepared statement associated with the hstmt
IM001	Driver does not support this function.	(DM) The driver associated with the hstmt does not support the function.
S0001	Base table or view already exists	The argument szSqlStr contained a CREATE TABLE or CREATE VIEW statement and the table name or view name specified already exists.
S0002	Table or view not found	The argument szSqlStr contained a DROP TABLE or a DROP VIEW statement and the specified table name or view name did not exist. The argument szSqlStr contained an ALTER TABLE statement and the specified table name did not exist. The argument szSqlStr contained a CREATE VIEW statement and a table name or view name defined by the query specification did not exist. The argument szSqlStr contained a CREATE INDEX statement and the specified table name did not exist. The argument szSqlStr contained a GRANT or REVOKE statement and the specified table name or view did not exist. The argument szSqlStr contained a SELECT statement and a specified table name or view name did not exist. The argument szSqlStr contained a DELETE, UPDATE or INSERT statement and the specified table name did not exist. The argument szSqlStr contained a CREATE TABLE statement and a table specified in a constraint (referencing a table other than the one being created) did not exist.
S0011	Index alredy exists	The argument szSqlStr contained a CREATE INDEX statement and the specified index name already existed.
S0012	Index not found	The argument szSqlStr contained a DROP INDEX statement and the specified index name did not exist.
S0021	Column already exists	The argument szSqlStr contained an ALTER TABLE statement and the column specified in the ADD clause is not unique or identifies an existing column.
S0022	Column not found	The argument szSqlStr contained a CREATE INDEX statement and one or more of the column names specified in the column list did not exist. The argument szSqlStr contained a GRANT or REVOKE statement and a specified column name did not exist. The argument szSqlStr contained a SELECT, DELETE, INSERT or UPDATE statement and a specified column name did not exist. The argument szSqlStr contained a CREATE TABLE statement and a column specified in a constraint (referencing a table other than the one being created) did not exist.
S1000	General error	An error occurred for which there was no specific SQLSTATE and for which no implementation-specific SQLSTATE was defined. The error message returned by SQLError in the argument szErrorMsg describes the error and its cause.
S1001	Memory allocation failure	(DM) The Driver Manager was unable to allocate memory for the connection handle. The driver was unable to allocate memory for the connection handle.
S1008	Operation Canceled	Asynchronous processing was enabled for the hstmt. The funtion was called and before it completed execution, SQLCancel was called on the hstmt from a different thread in a multithreaded application.
S1009	Invalid argument value	(DM) The argument szSqlStr was a null pointer.
S1010	Function sequence error	(DM) An application asynchronously execution function (not this one) was called for the hstmt and was still executing when this function was called. (DM) SQLExecute, SQLExecDirect, or SQLSetPos was called for the hstmt and returned SQL_NEED_DATA. This function was called before data was sent for all data-at-execution parameters or columns. (DM) The hstmt was not prepared. Either the hstmt was not in an executed state, or a cursor was open on the hstmt and SQLFetch or SQLExtendedFetch had not been called.
S1090	Invalid string or buffer length	A parameter value, set with SQLBindParameter, was a null pointer and the paramete rlength value was not 0, SQL_NULL_DATA, SQL_DATA_AT_EXEC, or less than or equal to SQL_LEN_DATA_AT_EXEC_OFFSET. A parameter value, set with SQLBindParameter, was not a null pointer and the parameter length value was less than 0, but was not SQL_NTS, SQL_NULL_DATA, or SQL_DATA_AT_EXEC, or less than or equal to SQL_LEN_DATA_AT_EXEC_OFFSET.
S1109	Invalid cursor position	The prepared statement was a positioned update or delete statement and the cursor was positioned (by SQLSetPos or SQLExtendedFetch) on a row for which the value in the rgfRowStatus array in SQLExtendedFetch was SQL_ROW_DELETED or SQL_ROW_ERROR.
S1C00	Driver not capable	The combination of the current settings of the SQL_CONCURRENCY and SQL_CURSOR_TYPE statement options was not supported by the driver or data source.
S1T00	Timeout expired	The timeout period expired before the data source returned the result set. The timeout period is set through SQLSetStmtOption, SQL_QUERY_TIMEOUT.
SQLExecDirect can return any SQLSTATE that can be returned by SQLPrepare based on when the data source evaluates the SQL statement associated with the hstmt.

Comments

SQLExecDirect The application calls SQLExecDirect to send an SQL statement to the data source. The driver modifies the statement to use the form of SQL used byt he data source, then submits it to the data source. In particular, the driver modifies the escape clauses used to define ODBC-specific SQL. For a description of SQL statement grammar, see "Supporting ODBC Extensions to SQL".

The application can include one or more parameter markers in the SQL statement. TO include a parameter marker, the application embeds a question mark (?) into the SQL statement at the appropriate position.

If the SQL statement is a SELECT statement and if the application called SQLSetCursorName to associate a cursor with an hstmt, then the driver uses the specified cursor. Otherwise, the driver generates a cursor name.

If the data source is in a manual-commit mode (requiring explicit transaction initiation), and a transaction has not already been initiated, the driver initiates a transaction before it sends the SQL statement.

If an application uses SQLExecDirect to submit a COMMIT or ROLLBACK statement, it will not be interoperable between DBMS products. To commit or roll back a transaction, call SQLTransact.

If SQLExecDirect enounters a data-at-execution parameter, it returns SQL_NEED_DATA. The application sends the data using SQLParamData and SQLPutData. See SQLBindParameter, SQLParamOptions, SQLParamData and SQLPutData for more information.

Code Example

See SQLBindCol, SQLExtendedFetch, SQLGetData and SQLProcedures.

Related Functions

For information about	See
Assigning storage for a column result set	SQLBindCol
Canceling statement processing	SQLCancel
Executing a prepared SQL statement	SQLExecute
Fetching a block of data or scrolling through a result set	SQLExtendedFetch
Fetching a row of data	SQLFetch
Returning a cursor name	SQLGetCursorName
Fetching part or all of a column of data	SQLGetData (extension)
Returning the next parameter to send data for	SQLParamData (extension)
Preparing a statement for execution	SQLPrepare
Sending parameter data at execution time	SQLPutData (extension)
Setting a cursor name	SQLSetCursorName
Setting a statement option	SQLSetStmtOption (extension)
Executing a commit or rollback operation	SQLTransact

ODBC Router

ODBC Router transparently makes all ODBC drivers on a central Windows Server useable by your network's Linux, Macintosh and Windows systems. Three years in the making, ODBC Router has saved its customers millions of dollars in DLL installation and ODBC support costs for less than the price of a new PC and a few minutes installation time. ODBC Router provides a low cost, turnkey database network with enterprise class IT support.

Ditching the ODBC Administrator Control Panel:

By linking your application with the free ODBC Router client-side SDK instead of the ODBC Driver Manager, the need for your end-users to deal with driver installation or an ODBC Control Panel is gone! We even provide a setup function your code may call to display a "network browser" that enables your customer to "find" their ODBC Router server on the network, then "choose" the exact database they want to work with and finally return to your application with a fully-formed ODBC connection-string that may be stored and passed back to SQLConnect or SQLDriverConnect anytime your user wants to initiate a connection that data source. No more walking your customers through the process of adding data sources to ODBC Control Panel or, in the case of non-Windows computers, no need to worry about whether or not a compatible third-party ODBC Driver Manager has been installed for use with your code. (Remember that ODBC Router supports Mac OS 9, Mac OS X, Linux and Windows.) If there ever are any client-side ODBC support issues, AugSoft can handle them directly with the customer freeing you to focus on the application.

ODBC or JDBC?

JDBC drivers launch a CPU-intensive virtual machine in the background on your machine, which is bad for shared servers and for battery powered laptops or entry level desktops (that typically have slow busses and drives). As the world shifted to laptops and shared servers, the whole "virtual machine" concept became a support nightmare and so these days good Java apps are compiled to run as native (not emulated) code. Java developers may use the operating system's native ODBC support from within the JDBC class library using the sun.jdbc.odbc.JdbcOdbcDriver driver with a URL as shown below.

jdbc:odbc:dsn[;key=value]*

Example:

jdbc:odbc:finance;UID=cfo;

IT techs may then complete the database connection on the Customer's machine using ODBC Router or the database vendor's official ODBC driver.

NOTE: By creating ODBC data sources with ODBC Router, your apps will enjoy native speed and database independent connections from either Java/C/C++/C#/ObjC or PHP/PERL/Python/Ruby/BASIC on Linux, Macintosh and Windows. Also be aware that using ODBC Router with the Mac platform is an especially good idea because database vendors have not kept their Mac drivers in sync with Windows and there are actual third-party vendors who wrap freeware and JDBC drivers inside of ODBC "shells" without warning their customers! This problem is of great concern to developers because fake drivers almost always fly past the IT guys who test with speed deamon desktops, but fail the enterprise when user laptops and iMacs take too long to run queries or slowly corrupt the database when they do. IT guys often chalk this up to "network problems" leaving users with poison drivers to avoid their database. ODBC Router addresses this issue by enabling official vendor supported Windows ODBC drivers (on a Windows Server) to be accessed from all platforms, network wide.

ODBC 3.x?

It's not here yet. Even in 2010, most ODBC drivers are ODBC 1.x and 2.x. The ODBC Driver Manager translates between 3.x and 2.x or 2.x and 1.x ODBC calls. Therefore, if you don't need UNICODE, it's a bad idea to use ODBC 3.x API calls. That said, UNICODE is a Good Thing and there are actually at least three databases that natively support it now, so look for 3.x to be here soon.

Need ODBC API Help?

We really know ODBC and we routinely provide code-level ODBC help to our customers. Our low cost ODBC Router systems are available now at our online store and if your site buys them, you may open a ticket to ask ODBC development questions --we offer both E-Mail and On-Call support options in seven of the G8's timezones! Be sure to test your ODBC Router and ask for any needed installation help before purchase because we aren't Fry's --no refunds please, our prices are too low for such nonsense and we're too busy supporting real Customers!