SQLPrepare (ODBC 1.0, Core)
SQLPrepare prepares an SQL string for execution.
Syntax
RETCODE SQLPrepare(hstmt, szSqlStr, cbSqlStr)
The SQLPrepare function accepts the following arguments.
Type
|
Argument
|
Use
|
Description |
HSTMT
|
hstmt
|
Input
|
Statement handle. |
UCHAR FAR *
|
szSqlStr
|
Input
|
SQL text string. |
SDWORD
|
cbSqlStr
|
Input
|
Length of szSqlStr. |
Returns
SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_STILL_EXECUTING, SQL_ERROR, or SQL_INVALID_HANDLE.
Diagnostics
When SQLPrepare 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 SQLPrepare 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.) |
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 degree of 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. |
22005
|
Error in assignment
|
The argument szSqlStr contained an SQL statement that contained a literal or parameter and the value was incompatible with the data type of the associated table column. |
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. |
34000
|
Invalid cursor name
|
The argument szSqlStr contained a positioned DELETE or a positioned UPDATE and the cursor referenced by the statement being prepared 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. |
42000
|
Syntax error or access violation
|
The argument szSqlStr contained a statement for which the user did not have the required privileges. |
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
|
Base table 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 name 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, INSERT, or UPDATE 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 already 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 in the base table. |
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
|
The driver was unable to allocate memory required to support execution or completion of the function. |
S1008
|
Operation canceled
|
Asynchronous processing was enabled for the hstmt. The function was called and before it completed execution, SQLCancel was called on the hstmt. Then the function was called again on the hstmt.
The function 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 asynchronously executing 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. |
S1090
|
Invalid string or buffer length
|
(DM) The argument cbSqlStr was less than or equal to 0, but not equal to SQL_NTS. |
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. |
Comments
The application calls SQLPrepare to send an SQL statement to the data source for preparation. 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 string at the appropriate position.
Note If an application uses SQLPrepare to prepare and SQLExecute to submit a COMMIT or ROLLBACK statement, it will not be interoperable between DBMS products. To commit or roll back a transaction, call SQLTransact.
The driver modifies the statement to use the form of SQL used by the data source, then submits it to the data source for preparation. In particular, the driver modifies the escape clauses used to define ODBC-specific SQL. (For a description of SQL statement grammar, see Appendix C, "SQL Grammar.") For the driver, an hstmt is similar to a statement identifier in embedded SQL code. If the data source supports statement identifiers, the driver can send a statement identifier and parameter values to the data source.
Once a statement is prepared, the application uses hstmt to refer to the statement in later function calls. The prepared statement associated with the hstmt may be reexecuted by calling SQLExecute until the application frees the hstmt with a call to SQLFreeStmt with the SQL_DROP option or until the hstmt is used in a call to SQLPrepare, SQLExecDirect, or one of the catalog functions (SQLColumns, SQLTables, and so on). Once the application prepares a statement, it can request information about the format of the result set.
Some drivers cannot return syntax errors or access violations when the application calls SQLPrepare. A driver may handle syntax errors and access violations, only syntax errors, or neither syntax errors nor access violations. Therefore, an application must be able to handle these conditions when calling subsequent related functions such as SQLNumResultCols, SQLDescribeCol, SQLColAttributes, and SQLExecute.
Depending on the capabilities of the driver and data source and on whether the application has called SQLBindParameter, parameter information (such as data types) might be checked when the statement is prepared or when it is executed. For maximum interoperability, an application should unbind all parameters that applied to an old SQL statement before preparing a new SQL statement on the same hstmt. This prevents errors that are due to old parameter information being applied to the new 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.
Code Example
See SQLBindParameter, SQLParamOptions, SQLPutData, and SQLSetPos.
Related Functions
For information about
|
See |
Allocating a statement handle
|
SQLAllocStmt |
Assigning storage for a column in a result set
|
SQLBindCol |
Canceling statement processing
|
SQLCancel |
Executing an SQL statement
|
SQLExecDirect |
Executing a prepared SQL statement
|
SQLExecute |
Returning the number of rows affected by a statement
|
SQLRowCount |
Setting a cursor name
|
SQLSetCursorName |
Assigning storage for a parameter
|
SQLBindParameter |
Executing a commit or rollback operation
|
SQLTransact |
SQLPrimaryKeys (ODBC 1.0, Level 2)
SQLPrimaryKeys returns the column names that comprise the primary key for a table. The driver returns the information as a result set. This function does not support returning primary keys from multiple tables in a single call.
Syntax
RETCODE SQLPrimaryKeys(hstmt, szTableQualifier, cbTableQualifier, szTableOwner, cbTableOwner, szTableName, cbTableName)
The SQLPrimaryKeys function accepts the following arguments:
Type
|
Argument
|
Use
|
Description |
HSTMT
|
hstmt
|
Input
|
Statement handle. |
UCHAR FAR *
|
szTableQualifier
|
Input
|
Qualifier name. If a driver supports qualifiers for some tables but not for others, such as when the driver retrieves data from different DBMSs, an empty string ("") denotes those tables that do not have qualifiers. |
SWORD
|
cbTableQualifier
|
Input
|
Length of szTableQualifier. |
UCHAR FAR *
|
szTableOwner
|
Input
|
Table owner. If a driver supports owners for some tables but not for others, such as when the driver retrieves data from different DBMSs, an empty string ("") denotes those tables that do not have owners. |
SWORD
|
cbTableOwner
|
Input
|
Length of szTableOwner. |
UCHAR FAR *
|
szTableName
|
Input
|
Table name. |
SWORD
|
cbTableName
|
Input
|
Length of szTableName. |
Returns
SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_STILL_EXECUTING, SQL_ERROR, or SQL_INVALID_HANDLE.
Diagnostics
When SQLPrimaryKeys 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 SQLPrimaryKeys 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.) |
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. |
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. |
IM001
|
Driver does not support this function
|
(DM) The driver associated with the hstmt does not support the function. |
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
|
The driver was unable to allocate memory required to support execution or completion of the function. |
S1008
|
Operation canceled
|
Asynchronous processing was enabled for the hstmt. The function was called and before it completed execution, SQLCancel was called on the hstmt. Then the function was called again on the hstmt.
The function was called and, before it completed execution, SQLCancel was called on the hstmt from a different thread in a multithreaded application. |
S1010
|
Function sequence error
|
(DM) An asynchronously executing 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. |
S1090
|
Invalid string or buffer length
|
(DM) The value of one of the name length arguments was less than 0, but not equal to SQL_NTS. |
|
|
The value of one of the name length arguments exceeded the maximum length value for the corresponding qualifier or name. |
S1C00
|
Driver not capable
|
A table qualifier was specified and the driver or data source does not support qualifiers.
A table owner was specified and the driver or data source does not support owners. |
|
|
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 requested result set. The timeout period is set through SQLSetStmtOption, SQL_QUERY_TIMEOUT. |
Comments
SQLPrimaryKeys returns the results as a standard result set, ordered by TABLE_QUALIFIER, TABLE_OWNER, TABLE_NAME, and KEY_SEQ. The following table lists the columns in the result set.
Note SQLPrimaryKeys might not return all primary keys. For example, a Paradox driver might only return primary keys for files (tables) in the current directory.
The lengths of VARCHAR columns shown in the table are maximums; the actual lengths depend on the data source. To determine the actual lengths of the TABLE_QUALIFIER, TABLE_OWNER, TABLE_NAME, and COLUMN_NAME columns, call SQLGetInfo with the SQL_MAX_QUALIFIER_NAME_LEN, SQL_MAX_OWNER_NAME_LEN, SQL_MAX_TABLE_NAME_LEN, and SQL_MAX_COLUMN_NAME_LEN options.
Column Name
|
Data Type
|
Comments |
TABLE_QUALIFIER
|
Varchar(128)
|
Primary key table qualifier identifier; NULL if not applicable to the data source. If a driver supports qualifiers for some tables but not for others, such as when the driver retrieves data from different DBMSs, it returns an empty string ("") for those tables that do not have qualifiers. |
TABLE_OWNER
|
Varchar(128)
|
Primary key table owner identifier; NULL if not applicable to the data source. If a driver supports owners for some tables but not for others, such as when the driver retrieves data from different DBMSs, it returns an empty string ("") for those tables that do not have owners. |
TABLE_NAME
|
Varchar(128)
not NULL
|
Primary key table identifier. |
COLUMN_NAME
|
Varchar(128)
not NULL
|
Primary key column identifier. |
KEY_SEQ
|
Smallint
not NULL
|
Column sequence number in key (starting with 1). |
PK_NAME
|
Varchar(128)
|
Primary key identifier. NULL if not applicable to the data source. |
Note The PK_NAME column was added in ODBC 2.0. ODBC 1.0 drivers may return a different, driver-specific column with the same column number.
Code Example
See SQLForeignKeys.
Related Functions
For information about
|
See |
Assigning storage for a column in a result set
|
SQLBindCol |
Canceling statement processing
|
SQLCancel |
Fetching a block of data or scrolling through a result set
|
SQLExtendedFetch (extension) |
Fetching a row of data
|
SQLFetch |
Returning the columns of foreign keys
|
SQLForeignKeys (extension) |
Returning table statistics and indexes
|
SQLStatistics (extension) |
SQLProcedureColumns (ODBC 1.0, Level 2)
SQLProcedureColumns returns the list of input and output parameters, as well as the columns that make up the result set for the specified procedures. The driver returns the information as a result set on the specified hstmt.
Syntax
RETCODE SQLProcedureColumns(hstmt, szProcQualifier, cbProcQualifier, szProcOwner, cbProcOwner, szProcName, cbProcName, szColumnName, cbColumnName)
The SQLProcedureColumns function accepts the following arguments:
Type
|
Argument
|
Use
|
Description |
HSTMT
|
hstmt
|
Input
|
Statement handle. |
UCHAR FAR *
|
szProcQualifier
|
Input
|
Procedure qualifier name. If a driver supports qualifiers for some procedures but not for others, such as when the driver retrieves data from different DBMSs, an empty string ("") denotes those procedures that do not have qualifiers. |
SWORD
|
cbProcQualifier
|
Input
|
Length of szProcQualifier. |
UCHAR FAR *
|
szProcOwner
|
Input
|
String search pattern for procedure owner names. If a driver supports owners for some procedures but not for others, such as when the driver retrieves data from different DBMSs, an empty string ("") denotes those procedures that do not have owners. |
SWORD
|
cbProcOwner
|
Input
|
Length of szProcOwner. |
UCHAR FAR *
|
szProcName
|
Input
|
String search pattern for procedure names. |
SWORD
|
cbProcName
|
Input
|
Length of szProcName. |
UCHAR FAR *
|
szColumnName
|
Input
|
String search pattern for column names. |
SWORD
|
cbColumnName
|
Input
|
Length of szColumnName. |
Returns
SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_STILL_EXECUTING, SQL_ERROR, or SQL_INVALID_HANDLE.
Diagnostics
When SQLProcedureColumns 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 SQLProcedureColumns 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.) |
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. |
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. |
IM001
|
Driver does not support this function
|
(DM) The driver associated with the hstmt does not support the function. |
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
|
The driver was unable to allocate memory required to support execution or completion of the function. |
S1008
|
Operation canceled
|
Asynchronous processing was enabled for the hstmt. The function was called and before it completed execution, SQLCancel was called on the hstmt. Then the function was called again on the hstmt.
The function was called and, before it completed execution, SQLCancel was called on the hstmt from a different thread in a multithreaded application. |
S1010
|
Function sequence error
|
(DM) An asynchronously executing 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. |
S1090
|
Invalid string or buffer length
|
(DM) The value of one of the name length arguments was less than 0, but not equal to SQL_NTS. |
|
|
The value of one of the name length arguments exceeded the maximum length value for the corresponding qualifier or name. |
S1C00
|
Driver not capable
|
A procedure qualifier was specified and the driver or data source does not support qualifiers.
A procedure owner was specified and the driver or data source does not support owners. |
|
|
A string search pattern was specified for the procedure owner, procedure name, or column name and the data source does not support search patterns for one or more of those arguments.
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. |
Comments
This function is typically used before statement execution to retrieve information about procedure parameters and columns from the data source’s catalog. For more information about stored procedures, see "Using ODBC Extensions to SQL" in Chapter 6, "Executing SQL Statements."
Note SQLProcedureColumns might not return all columns used by a procedure. For example, a driver might only return information about the parameters used by a procedure and not the columns in a result set it generates.
The szProcOwner, szProcName, and szColumnName arguments accept search patterns. For more information about valid search patterns, see "Search Pattern Arguments" earlier in this chapter.
SQLProcedureColumns returns the results as a standard result set, ordered by PROCEDURE_QUALIFIER, PROCEDURE_OWNER, PROCEDURE_NAME, and COLUMN_TYPE. The following table lists the columns in the result set. Additional columns beyond column 13 (REMARKS) can be defined by the driver.
The lengths of VARCHAR columns shown in the table are maximums; the actual lengths depend on the data source. To determine the actual lengths of the PROCEDURE_QUALIFIER, PROCEDURE_OWNER, PROCEDURE_NAME, and COLUMN_NAME columns, an application can call SQLGetInfo with the SQL_MAX_QUALIFIER_NAME_LEN, SQL_MAX_OWNER_NAME_LEN, SQL_MAX_PROCEDURE_NAME_LEN, and SQL_MAX_COLUMN_NAME_LEN options.
Column Name
|
Data Type
|
Comments |
PROCEDURE_QUALIFIER
|
Varchar(128)
|
Procedure qualifier identifier; NULL if not applicable to the data source. If a driver supports qualifiers for some procedures but not for others, such as when the driver retrieves data from different DBMSs, it returns an empty string ("") for those procedures that do not have qualifiers. |
PROCEDURE_OWNER
|
Varchar(128)
|
Procedure owner identifier; NULL if not applicable to the data source. If a driver supports owners for some procedures but not for others, such as when the driver retrieves data from different DBMSs, it returns an empty string ("") for those procedures that do not have owners. |
PROCEDURE_NAME
|
Varchar(128)
not NULL
|
Procedure identifier. |
COLUMN_NAME
|
Varchar(128)
not NULL
|
Procedure column identifier. |
COLUMN_TYPE
|
Smallint
not NULL
|
Defines the procedure column as parameter or a result set column:
SQL_PARAM_TYPE_UNKNOWN: The procedure column is a parameter whose type is unknown. (ODBC 1.0)
SQL_PARAM_INPUT: The procedure column is an input parameter. (ODBC 1.0)
SQL_PARAM_INPUT_OUTPUT: the procedure column is an input/output parameter. (ODBC 1.0)
SQL_PARAM_OUTPUT: The procedure column is an output parameter. (ODBC 1.0)
SQL_RETURN_VALUE: The procedure column is the return value of the procedure. (ODBC 2.0)
SQL_RESULT_COL: The procedure column is a result set column. (ODBC 1.0) |
DATA_TYPE
|
Smallint
not NULL
|
SQL data type. This can be an ODBC SQL data type or a driver-specific SQL data type. For a list of valid ODBC SQL data types, see "SQL Data Types" in Appendix D, "Data Types." For information about driver-specific SQL data types, see the driver’s documentation. |
TYPE_NAME
|
Varchar(128)
not NULL
|
Data source–dependent data type name; for example, "CHAR", "VARCHAR", "MONEY", "LONG VARBINARY", or "CHAR ( ) FOR BIT DATA". |
PRECISION
|
Integer
|
The precision of the procedure column on the data source. NULL is returned for data types where precision is not applicable. For more information concerning precision, see "Precision, Scale, Length, and Display Size," in Appendix D, "Data Types." |
LENGTH
|
Integer
|
The length in bytes of data transferred on an SQLGetData or SQLFetch operation if SQL_C_DEFAULT is specified. For numeric data, this size may be different than the size of the data stored on the data source. For more information, see "Precision, Scale, Length, and Display Size," in Appendix D, "Data Types." |
SCALE
|
Smallint
|
The scale of the procedure column on the data source. NULL is returned for data types where scale is not applicable. For more information concerning scale, see "Precision, Scale, Length, and Display Size," in Appendix D, "Data Types." |
RADIX
|
Smallint
|
For numeric data types, either 10 or 2. If it is 10, the values in PRECISION and SCALE give the number of decimal digits allowed for the column. For example, a DECIMAL(12,5) column would return a RADIX of 10, a PRECISION of 12, and a SCALE of 5; a FLOAT column could return a RADIX of 10, a PRECISION of 15 and a SCALE of NULL.
If it is 2, the values in PRECISION and SCALE give the number of bits allowed in the column. For example, a FLOAT column could return a RADIX of 2, a PRECISION of 53, and a SCALE of NULL.
NULL is returned for data types where radix is not applicable. |
NULLABLE
|
Smallint
not NULL
|
Whether the procedure column accepts a NULL value:
SQL_NO_NULLS: The procedure column does not accept NULL values.
SQL_NULLABLE: The procedure column accepts NULL values.
SQL_NULLABLE_UNKNOWN: It is not known if the procedure column accepts NULL values. |
REMARKS
|
Varchar(254)
|
A description of the procedure column. |
Code Example
See SQLProcedures.
Related Functions
For information about
|
See |
Assigning storage for a column in a result set
|
SQLBindCol |
Canceling statement processing
|
SQLCancel |
Fetching a block of data or scrolling through a result set
|
SQLExtendedFetch (extension) |
Fetching a row of data
|
SQLFetch |
Returning a list of procedures in a data source
|
SQLProcedures (extension) |
SQLProcedures (ODBC 1.0, Level 2)
SQLProcedures returns the list of procedure names stored in a specific data source. Procedure is a generic term used to describe an executable object, or a named entity that can be invoked using input and output parameters, and which can return result sets similar to the results returned by SQL SELECT expressions.
Syntax
RETCODE SQLProcedures(hstmt, szProcQualifier, cbProcQualifier, szProcOwner, cbProcOwner, szProcName, cbProcName)
The SQLProcedures function accepts the following arguments:
Type
|
Argument
|
Use
|
Description |
HSTMT
|
hstmt
|
Input
|
Statement handle. |
UCHAR FAR *
|
szProcQualifier
|
Input
|
Procedure qualifier. If a driver supports qualifiers for some tables but not for others, such as when the driver retrieves data from different DBMSs, an empty string ("") denotes those tables that do not have qualifiers. |
SWORD
|
cbProcQualifier
|
Input
|
Length of szProcQualifier. |
UCHAR FAR *
|
szProcOwner
|
Input
|
String search pattern for procedure owner names. If a driver supports owners for some procedures but not for others, such as when the driver retrieves data from different DBMSs, an empty string ("") denotes those procedures that do not have owners. |
SWORD
|
cbProcOwner
|
Input
|
Length of szProcOwner. |
UCHAR FAR *
|
szProcName
|
Input
|
String search pattern for procedure names. |
SWORD
|
cbProcName
|
Input
|
Length of szProcName. |
Returns
SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_STILL_EXECUTING, SQL_ERROR, or SQL_INVALID_HANDLE.
Diagnostics
When SQLProcedures 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 SQLProcedures 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.) |
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. |
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. |
IM001
|
Driver does not support this function
|
(DM) The driver associated with the hstmt does not support this function. |
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
|
The driver was unable to allocate memory required to support execution or completion of the function. |
S1008
|
Operation canceled
|
Asynchronous processing was enabled for the hstmt. The function was called and before it completed execution, SQLCancel was called on the hstmt. Then the function was called again on the hstmt.
The function was called and, before it completed execution, SQLCancel was called on the hstmt from a different thread in a multithreaded application. |
S1010
|
Function sequence error
|
(DM) An asynchronously executing 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. |
S1090
|
Invalid string or buffer length
|
(DM) The value of one of the name length arguments was less than 0, but not equal to SQL_NTS. |
|
|
The value of one of the name length argu-ments exceeded the maximum length value for the corresponding qualifier or name. |
S1C00
|
Driver not capable
|
A procedure qualifier was specified and the driver or data source does not support qualifiers.
A procedure owner was specified and the driver or data source does not support owners. |
|
|
A string search pattern was specified for the procedure owner or procedure name and the data source does not support search patterns for one or more of those arguments.
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 requested result set. The timeout period is set through SQLSetStmtOption, SQL_QUERY_TIMEOUT. |
Comments
SQLProcedures lists all procedures in the requested range. A user may or may not have permission to execute any of these procedures. To check accessibility, an application can call SQLGetInfo and check the SQL_ACCESSIBLE_PROCEDURES information value. Otherwise, the application must be able to handle a situation where the user selects a procedure which it cannot execute.
Note SQLProcedures might not return all procedures. Applications can use any valid procedure, regardless of whether it is returned by SQLProcedures.
SQLProcedures returns the results as a standard result set, ordered by PROCEDURE_QUALIFIER, PROCEDURE_OWNER, and PROCEDURE_NAME. The following table lists the columns in the result set.
The lengths of VARCHAR columns shown in the table are maximums; the actual lengths depend on the data source. To determine the actual lengths of the PROCEDURE_QUALIFIER, PROCEDURE_OWNER, and PROCEDURE_NAME columns, an application can call SQLGetInfo with the SQL_MAX_QUALIFIER_NAME_LEN, SQL_MAX_OWNER_NAME_LEN, and SQL_MAX_PROCEDURE_NAME_LEN options.
Column Name
|
Data Type
|
Comments |
PROCEDURE_QUALIFIER
|
Varchar(128)
|
Procedure qualifier identifier; NULL if not applicable to the data source. If a driver supports qualifiers for some procedures but not for others, such as when the driver retrieves data from different DBMSs, it returns an empty string ("") for those procedures that do not have qualifiers. |
PROCEDURE_OWNER
|
Varchar(128)
|
Procedure owner identifier; NULL if not applicable to the data source. If a driver supports owners for some procedures but not for others, such as when the driver retrieves data from different DBMSs, it returns an empty string ("") for those procedures that do not have owners. |
PROCEDURE_NAME
|
Varchar(128)
not NULL
|
Procedure identifier. |
NUM_INPUT_PARAMS
|
N/A
|
Reserved for future use. Applications should not rely on the data returned in these result columns. |
NUM_OUTPUT_PARAMS
|
N/A
|
Reserved for future use. Applications should not rely on the data returned in these result columns. |
NUM_RESULT_SETS
|
N/A
|
Reserved for future use. Applications should not rely on the data returned in these result columns. |
REMARKS
|
Varchar(254)
|
A description of the procedure. |
PROCEDURE_TYPE
|
Smallint
|
Defines the procedure type:
SQL_PT_UNKNOWN: It cannot be determined whether the procedure returns a value.
SQL_PT_PROCEDURE: The returned object is a procedure; that is, it does not have a return value.
SQL_PT_FUNCTION: The returned object is a function; that is, it has a return value. |
Note The PROCEDURE_TYPE column was added in ODBC 2.0. ODBC 1.0 drivers might return a different, driver-specific column with the same column number.
The szProcOwner and szProcName arguments accept search patterns. For more information about valid search patterns, see "Search Pattern Arguments" earlier in this chapter.
Code Example
In this example, an application uses the procedure AddEmployee to insert data into the EMPLOYEE table. The procedure contains input parameters for NAME, AGE, and BIRTHDAY columns. It also contains one output parameter that returns a remark about the new employee. The example also shows the use of a return value from a stored procedure. For the return value and each parameter in the procedure, the application calls SQLBindParameter to specify the ODBC C data type and the SQL data type of the parameter and to specify the storage location and length of the parameter. The application assigns data values to the storage locations for each parameter and calls SQLExecDirect to execute the procedure. If SQLExecDirect returns SQL_SUCCESS or SQL_SUCCESS_WITH_INFO, the return value and the value of each output or input/output parameter is automatically put into the storage location defined for the parameter in SQLBindParameter.
#define NAME_LEN 30
#define REM_LEN 128
UCHAR szName[NAME_LEN], szRemark[REM_LEN];
SWORD sAge, sEmpId;
SDWORD cbEmpId, cbName, cbAge = 0, cbBirthday = 0, cbRemark;
DATE_STRUCT dsBirthday;
/* Define parameter for return value (Employee ID) from procedure. */
SQLBindParameter(hstmt, 1, SQL_PARAM_OUTPUT, SQL_C_SLONG, SQL_INTEGER,
0, 0, &sEmpId, 0, &cbEmpId);
/* Define data types and storage locations for Name, Age, Birthday */
/* input parameter data. */
SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR,
NAME_LEN, 0, szName, 0, &cbName);
SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_SSHORT, SQL_SMALLINT,
0, 0, &sAge, 0, &cbAge);
SQLBindParameter(hstmt, 4, SQL_PARAM_INPUT, SQL_C_DATE, SQL_DATE,
0, 0, &dsBirthday, 0, &cbBirthday);
/* Define data types and storage location for Remark output parameter */
SQLBindParameter(hstmt, 5, SQL_PARAM_OUTPUT, SQL_C_CHAR, SQL_CHAR,
REM_LEN, 0, szRemark, REM_LEN, &cbRemark);
strcpy(szName, "Smith, John D."); /* Specify first row of */
sAge = 40; /* parameter data. */
dsBirthday.year = 1952;
dsBirthday.month = 2;
dsBirthday.day = 29;
cbName = SQL_NTS;
/* Execute procedure with first row of data. After the procedure */
/* is executed, sEmpId and szRemark will have the values */
/* returned by AddEmployee. */
retcode = SQLExecDirect(hstmt, "{?=call AddEmployee(?,?,?,?)}",SQL_NTS);
strcpy(szName, "Jones, Bob K."); /* Specify second row of */
sAge = 52; /* parameter data */
dsBirthday.year = 1940;
dsBirthday.month = 3;
dsBirthday.day = 31;
/* Execute procedure with second row of data. After the procedure */
/* is executed, sEmpId and szRemark will have the new values */
/* returned by AddEmployee. */
retcode = SQLExecDirect(hstmt,
"{?=call AddEmployee(?,?,?,?)}", SQL_NTS);
Related Functions
For information about
|
See |
Assigning storage for a column in a result set
|
SQLBindCol |
Canceling statement processing
|
SQLCancel |
Fetching a block of data or scrolling through a result set
|
SQLExtendedFetch (extension) |
Fetching a row of data
|
SQLFetch |
Returning information about a driver or data source
|
SQLGetInfo (extension) |
Returning the parameters and result set columns of a procedure
|
SQLProcedureColumns (extension) |
Syntax for invoking stored procedures
|
Chapter 6, "Executing SQL Statements" |
SQLPutData (ODBC 1.0, Level 1)
SQLPutData allows an application to send data for a parameter or column to the driver at statement execution time. This function can be used to send character or binary data values in parts to a column with a character, binary, or data source–specific data type (for example, parameters of the SQL_LONGVARBINARY or SQL_LONGVARCHAR types).
Syntax
RETCODE SQLPutData(hstmt, rgbValue, cbValue)
The SQLPutData function accepts the following arguments.
Type
|
Argument
|
Use
|
Description |
HSTMT
|
hstmt
|
Input
|
Statement handle. |
PTR
|
rgbValue
|
Input
|
Pointer to storage for the actual data for the parameter or column. The data must use the C data type specified in the fCType argument of SQLBindParameter (for parameter data) or SQLBindCol (for column data). |
SDWORD
|
cbValue
|
Input
|
Length of rgbValue. Specifies the amount of data sent in a call to SQLPutData. The amount of data can vary with each call for a given parameter or column. cbValue is ignored unless it is SQL_NTS, SQL_NULL_DATA, or SQL_DEFAULT_PARAM; the C data type specified in SQLBindParameter or SQLBindCol is SQL_C_CHAR or SQL_C_BINARY; or the C data type is SQL_C_DEFAULT and the default C data type for the specified SQL data type is SQL_C_CHAR or SQL_C_BINARY. For all other types of C data, if cbValue is not SQL_NULL_DATA or SQL_DEFAULT_PARAM, the driver assumes that the size of rgbValue is the size of the C data type specified with fCType and sends the entire data value. For more information, see "Converting Data from C to SQL Data Types" in Appendix D, "Data Types." |
Returns
SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_STILL_EXECUTING, SQL_ERROR, or SQL_INVALID_HANDLE.
Diagnostics
When SQLPutData 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 SQLPutData 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 data sent for a character or binary parameter or column in one or more calls to SQLPutData exceeded the maximum length of the associated character or binary column.
The fractional part of the data sent for a numeric or bit parameter or column was truncated.
Timestamp data sent for a date or time parameter or column was truncated. |
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. |
22001
|
String data right truncation
|
The SQL_NEED_LONG_DATA_LEN information type in SQLGetInfo was "Y" and more data was sent for a long parameter (the data type was SQL_LONGVARCHAR, SQL_LONGVARBINARY, or a long, data source–specific data type) than was specified with the pcbValue argument in SQLBindParameter.
The SQL_NEED_LONG_DATA_LEN information type in SQLGetInfo was "Y" and more data was sent for a long column (the data type was SQL_LONGVARCHAR, SQL_LONGVARBINARY, or a long, data source–specific data type) than was specified in the length buffer corresponding to a column in a row of data that was added or updated with SQLSetPos. |
22003
|
Numeric value out of range
|
SQLPutData was called more than once for a parameter or column and it was not being used to send character C data to a column with a character, binary, or data source–specific data type or to send binary C data to a column with a character, binary, or data source–specific data type.
The data sent for a numeric parameter or column 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 data sent for a parameter or column was incompatible with the data type of the associated table column. |
22008
|
Datetime field overflow
|
The data sent for a date, time, or timestamp parameter or column was, respectively, an invalid date, time, or timestamp. |
IM001
|
Driver does not support this function
|
(DM) The driver associated with the hstmt does not support the function. |
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
|
The driver was unable to allocate memory required to support execution or completion of the function. |
S1008
|
Operation canceled
|
Asynchronous processing was enabled for the hstmt. The function was called and before it completed execution, SQLCancel was called on the hstmt. Then the function was called again on the hstmt.
The function was called and, before it completed execution, SQLCancel was called on the hstmt from a different thread in a multithreaded application.
SQLExecute, SQLExecDirect, or SQLSetPos was called for the hstmt and returned SQL_NEED_DATA. SQLCancel was called before data was sent for all data-at-execution parameters or columns. |
S1009
|
Invalid argument value
|
(DM) The argument rgbValue was a null pointer and the argument cbValue was not 0, SQL_DEFAULT_PARAM, or SQL_NULL_DATA. |
S1010
|
Function sequence error
|
(DM) The previous function call was not a call to SQLPutData or SQLParamData.
The previous function call was a call to SQLExecDirect, SQLExecute, or SQLSetPos where the return code was SQL_NEED_DATA. |
|
|
(DM) An asynchronously executing function (not this one) was called for the hstmt and was still executing when this function was called. |
S1090
|
Invalid string or buffer length
|
The argument rgbValue was not a null pointer and the argument cbValue was less than 0, but not equal to SQL_NTS or SQL_NULL_DATA. |
S1T00
|
Timeout expired
|
The timeout period expired before the data source completed processing the parameter value. The timeout period is set through SQLSetStmtOption, SQL_QUERY_TIMEOUT. |
Comments
For an explanation of how data-at-execution parameter data is passed at statement execution time, see "Passing Parameter Values" in SQLBindParameter. For an explanation of how data-at-execution column data is updated or added, see "Using SQLSetPos" in SQLSetPos.
Note An application can use SQLPutData to send data in parts only when sending character C data to a column with a character, binary, or data source–specific data type or when sending binary C data to a column with a character, binary, or data source–specific data type. If SQLPutData is called more than once under any other conditions, it returns SQL_ERROR and SQLSTATE 22003 (Numeric value out of range).
Code Example
In the following example, an application prepares an SQL statement to insert data into the EMPLOYEE table. The statement contains parameters for the NAME, ID, and PHOTO columns. For each parameter, the application calls SQLBindParameter to specify the C and SQL data types of the parameter. It also specifies that the data for the first and third parameters will be passed at execution time, and passes the values 1 and 3 for later retrieval by SQLParamData. These values will identify which parameter is being processed.
The application calls GetNextID to get the next available employee ID number. It then calls SQLExecute to execute the statement. SQLExecute returns SQL_NEED_DATA when it needs data for the first and third parameters. The application calls SQLParamData to retrieve the value it stored with SQLBindParameter; it uses this value to determine which parameter to send data for. For each parameter, the application calls InitUserData to initialize the data routine. It repeatedly calls GetUserData and SQLPutData to get and send the parameter data. Finally, it calls SQLParamData to indicate it has sent all the data for the parameter and to retrieve the value for the next parameter. After data has been sent for both parameters, SQLParamData returns SQL_SUCCESS.
For the first parameter, InitUserData does not do anything and GetUserData calls a routine to prompt the user for the employee name. For the third parameter, InitUserData calls a routine to prompt the user for the name of a file containing a bitmap photo of the employee and opens the file. GetUserData retrieves the next MAX_DATA_LEN bytes of photo data from the file. After it has retrieved all the photo data, it closes the photo file.
Note that some application routines are omitted for clarity.
#define NAME_LEN 30
#define MAX_DATA_LEN 1024
SDWORD cbNameParam, cbID = 0; cbPhotoParam, cbData;
SWORD sID;
PTR pToken, InitValue;
UCHAR Data[MAX_DATA_LEN];
retcode = SQLPrepare(hstmt,
"INSERT INTO EMPLOYEE (NAME, ID, PHOTO) VALUES (?, ?, ?)",
SQL_NTS);
if (retcode == SQL_SUCCESS) {
/* Bind the parameters. For parameters 1 and 3, pass the */
/* parameter number in rgbValue instead of a buffer address. */
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR,
NAME_LEN, 0, 1, 0, &cbNameParam);
SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_SSHORT,
SQL_SMALLINT, 0, 0, &sID, 0, &cbID);
SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT,
SQL_C_BINARY, SQL_LONGVARBINARY,
0, 0, 3, 0, &cbPhotoParam);
/* Set values so data for parameters 1 and 3 will be passed */
/* at execution. Note that the length parameter in the macro */
/* SQL_LEN_DATA_AT_EXEC is 0. This assumes that the driver */
/* returns "N" for the SQL_NEED_LONG_DATA_LEN information */
/* type in SQLGetInfo. */
cbNameParam = cbPhotoParam = SQL_LEN_DATA_AT_EXEC(0);
sID = GetNextID(); /* Get next available employee ID number. */
retcode = SQLExecute(hstmt);
/* For data-at-execution parameters, call SQLParamData to get the */
/* parameter number set by SQLBindParameter. Call InitUserData. */
/* Call GetUserData and SQLPutData repeatedly to get and put all */
/* data for the parameter. Call SQLParamData to finish processing */
/* this parameter and start processing the next parameter. */
while (retcode == SQL_NEED_DATA) {
retcode = SQLParamData(hstmt, &pToken);
if (retcode == SQL_NEED_DATA) {
InitUserData((SWORD)pToken, InitValue);
while (GetUserData(InitValue, (SWORD)pToken, Data, &cbData))
SQLPutData(hstmt, Data, cbData);
}
}
}
VOID InitUserData(sParam, InitValue)
SWORD sParam;
PTR InitValue;
{
UCHAR szPhotoFile[MAX_FILE_NAME_LEN];
switch sParam {
case 3:
/* Prompt user for bitmap file containing employee photo. */
/* OpenPhotoFile opens the file and returns the file handle. */
PromptPhotoFileName(szPhotoFile);
OpenPhotoFile(szPhotoFile, (FILE *)InitValue);
break;
}
}
BOOL GetUserData(InitValue, sParam, Data, cbData)
PTR InitValue;
SWORD sParam;
UCHAR *Data;
SDWORD *cbData;
{
switch sParam {
case 1:
/* Prompt user for employee name. */
PromptEmployeeName(Data);
*cbData = SQL_NTS;
return (TRUE);
case 3:
/* GetNextPhotoData returns the next piece of photo data and */
/* the number of bytes of data returned (up to MAX_DATA_LEN). */
Done = GetNextPhotoData((FILE *)InitValue, Data,
MAX_DATA_LEN, &cbData);
if (Done) {
ClosePhotoFile((FILE *)InitValue);
return (TRUE);
}
return (FALSE);
}
return (FALSE);
}
Related Functions
For information about
|
See |
Canceling statement processing
|
SQLCancel |
Executing an SQL statement
|
SQLExecDirect |
Executing a prepared SQL statement
|
SQLExecute |
Returning the next parameter to send data for
|
SQLParamData (extension) |
Assigning storage for a parameter
|
SQLBindParameter |
Copyright © 1992-1997 Solid Information Technology Ltd All rights reserved.