ODBC API

SQLGetTypeInfo returns information about data types supported by the data source. The driver returns the information in the form of an SQL result set.

Important Applications must use the type names returned by the TYPE_NAME column in ALTER TABLE and CREATE TABLE statements; they must not use the sample type names listed in Appendix C, "SQL Grammar." SQLGetTypeInfo may return more than one row with the same value in the DATA_TYPE column.

Syntax

RETCODE SQLGetTypeInfo(hstmt, fSqlType)

The SQLGetTypeInfo function accepts the following arguments.

Type	Argument	Use	Description
HSTMT	hstmt	Input	Statement handle for the result set.
SWORD	fSqlType	Input	The SQL data type. This must be one of the following values: SQL_BIGINT SQL_BINARY SQL_BIT SQL_CHAR SQL_DATE SQL_DECIMAL SQL_DOUBLE SQL_FLOAT SQL_INTEGER SQL_LONGVARBINARY SQL_LONGVARCHAR SQL_NUMERIC SQL_REAL SQL_SMALLINT SQL_TIME SQL_TIMESTAMP SQL_TINYINT SQL_VARBINARY SQL_VARCHAR or a driver-specific SQL data type. SQL_ALL_TYPES specifies that information about all data types should be returned. For information about 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.

Returns

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_STILL_EXECUTING, SQL_ERROR or SQL_INVALID_HANDLE

Diagnostics

When SQLGetTypeInfo 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 SQLGetTypeInfo 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 drtiver was attempting to connect failed before the function completed processing.
24000	Invalid cursor state	(DM) A cursor was open on the hstmt and SQLFetch or SQLExtendedFetch had not been called. A result set was open on the hstmt but SQLFetch or SQLExtendedFetch had not been called.
IM001	Driver does not support this function	(DM) The driver specified by the data source name 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	(DM) The Driver Manager was unable to allocate memory for the connection handle. The driver was unable to allocate memory for the connection handle.
S1004	SQL data type out of range	(DM) The value specified for the argument fSqlType was in the block of numbers reserved for ODBC SQL data type indicators but was not a valid ODBC SQL data type indicator.
S1008	Operation canceled.	Asynchronous processing was enabled for the hstmt, then 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.
S1C00	Driver not capable	The value specified for the argument fSqlType was in the range of numbers reserved for driver-specific SQL data type indicators, but 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

SQLGetTypeInfo returns the results as a standard result set, ordered by DATA_TYPE and TYPE_NAME. The following table lists the columns in the result set.

Note SQLGetTypeInfo might not return all data types. For example, a driver might not return user-defined data types. Applications can use any valid data type, regardless of whether it is returned by SQLGetTypeInfo.

The lengths of VARCHAR columns shown in the table are maximums; the actual lengths depend on the data source.

Column Name	Data Type	Comments
TYPE_NAME	Varchar(128) not NULL	Data source-dependent data type name; for example, "CHAR", "VARCHAR", "MONEY", "LONG VARBINARY", or "CHAR () FOR BIT DATA". Applications must use this name in CREATE TABLE and ALTER TABLE statements.
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.
PRECISION	Integer	The maximum precision of the data type on the data source. NULL is returned for data types where precision is not applicable. For more information on precisions, see "Precision, Scale, Length, and Display Size" in Appendix D, "Data Types."
LITERAL_PREFIX	Varchar(128)	Character or characters used to prefix a literal; for example, a single quote (') for character data types or 0x for binary data types; NULL is returned for data types where a literal prefix is not applicable.
LITERAL_SUFFIX	Varchar(128)	Character or characters used to terminate a literal; for example, a single quote (') for character data types or 0x for binary data types; NULL is returned for data types where a literal suffix is not applicable.
CREATE_PARAMS	Varchar(128)	Parameters for data type definitions. For example, CREATE_PARAMS for DECIMAL would be "precision, scale"; CREATE_PARAMS for VARCHAR would equal "max length"; NULL is returned if there are no parameters for the data type definition, for example INTEGER. The driver supplies the CREATE_PARAMS text in the language of the country where it is used.
NULLABLE	Smallint not NULL	Whether the data type accepts a NULL value: SQL_NO_NULLS if the data type does not accept NULL values. SQL_NULLABLE if the data type accepts NULL values. SQL_NULLABLE_UNKNOWN if it is not known if the column accepts NULL values.
CASE_SENSITIVE	Smallint not NULL	Whether a character data type is case sensitive in collations and comparisons: TRUE if the data type is a character data type and is case sensitive. FALSE if the data type is not a character data type or is not case sensitive.
SEARCHABLE	Smallint not NULL	How the data type is used in a WHERE clause: SQL_UNSEARCHABLE if the data type cannot be used in a WHERE clause. SQL_LIKE_ONLY if the data type can be used in a WHERE clause only with the LIKE predicate. SQL_ALL_EXCEPT_LIKE if the data type can be used in a WHERE clause with all comparison operators except LIKE. SQL_SEARCHABLE if the data type can be used in a WHERE clause with any comparison operator.
UNSIGNED_ATTRIBUTE	Smallint	Whether the data type is unisgned: TRUE if the data type is unsigned. FALSE if the data type is signed. NULL is returned if the attribute is not applicable to the data type or the data type is not numeric.
MONEY	Smallint not NULL	Whether the data type is a money data type: TRUE if it is a money data type. FALSE if it is not.
AUTO_INCREMENT	Smallint	Whether the data type is autoincrementing: TRUE if the data type is autoincrementing. FALSE if the data type is not autoincrementing. NULL is returned if the attribute is not applicable to the data type or the data type is not numeric. An application can insert values into a column having this attribute, but cannot update the values in the column.
LOCAL_tYPE_NAME	Varchar(128)	Localized version of the data source-dependent name of the data type. NULL is returned if a localized name is not supported by the data source. This name is intended for display only, such as in dialog boxes.
MINIMUM_SCALE	Smallint	The minimum scale of the data type on the data source. If a data type has a fixed scale, the MINIMUM_sCALE and MAXIMUM_sCALE columns both contain this value. For example, an SQL_TIMESTAMP column might have a fixed scale for fractional seconds. NULL is returned where scale is not applicable. For more information, see "Precision, Scale, Length, and Display Size" in Appendix D, "Data Types."
MAXIMUM_SCALE	Smallint	The maximum scale of the data type on the data source. NULL is returned where the scale is not applicable. If the maximum scale is not defined separately on the data source, but is instead defined to be the same as the maximum precision, this column contains the same value as the PRECISION column. For more information, see "Precision, Scale, Length, and Display Size" in Appendix D, "Data Types."

Note The MINIMUM_SCALE and MAXIMUM_SCALE columns were added in ODBC 2.0. ODBC 1.0 drivers may return different, driver-specific columns with the same column numbers.

Attribute information can apply to data types or to specific columns in a result set. SQLGetTypeInfo returns information about attributes associated with data types; SQLColAttributes returns information about attributes associated with columns in a result set.

Code Example

(future)

Related Functions

For information about	See
Assigning storage for a column in a result set	SQLBindCol
Canceling statement processing	SQLCancel
Returning information about a column in a result set	SQLColAttributes
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)

ODBC Router

ODBC Router transparently makes all ODBC drivers on a central Windows Server useable by your network's iPhone/iPod, iPad, Linux, Mac and Windows systems. Years in the making here in the USA, ODBC Router has saved its customers millions of dollars in DLL installations and 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 iPhone/iPod, iPad, 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.

JDBC?

JDBC-in-ODBC drivers (like some 'actual' ODBC drivers, lol) launch a CPU-intensive virtual machine in the background on your machine, which is bad for battery powered laptops, high-volume web servers 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!