NAME
UnixODBC - Perl extension for unixODBC.
SYNOPSIS
use UnixODBC ':all';DESCRIPTION
Perl extension for unixODBC library API.
ODBC Overview
The Open Database Connectivity standard provides a DBMS server-independent application programming interface for client programs. An application that is written using ODBC functions should, in theory, work equally well with any DBMS server that provides an ODBC API interface.
ODBC provides functions that connect to a DBMS server; set and get server and connection parameters; query the DBMS server for database information like data types, and table and column information; and prepare SQL queries, transactions, and retrieve results sets.
ODBC uses one or more Data Source Names, or DSNs, to connect to databases. DSNs contain information about the DBMS server-specific driver libraries, the hostname, authentication information, and DBMS-specific parameters. The unixODBC libraries provide the GUI utility ODBCConfig and the text-mode odbcinst utility to maintain driver and DSN definitions. You can determine, for example, which DBMS drivers are installed on the system with the Unix command:
shell> odbcinst -q -dYou can get the names of the DSNs on the system with the Unix command:
shell> odbcinst -q -sRefer to the section "Drivers and DSNs" to find out how to configure ODBC DBMS drivers and DSNs.
ODBC Data Access Clients
ODBC client programs use data "handles" to maintain information about the system's ODBC environment, the connection to the DSN, and the DBMS query being performed. ODBC calls these handles:
- Environment Handles
- Connection Handles
- Statement Handles
- Descriptor HandlesUnixODBC.pm does not yet provide full support for descriptor handle types.
All operations between the client program and the DBMS server are performed using these handles and ODBC functions. Client programs generally have the structure:
- Allocate an environment handle.
- Perform configuration based on the ODBC environment.
- Allocate a connection handle.
- Connect to the DSN.
- Allocate a statement handle.
- Prepare and execute a query statement.
- Retrieve the results.
- De-allocate the statement, connection, and environment handles.The following ODBC client program connects to a DSN named "Catalog," sends the SQL query "select * from titles" to the DBMS server, and retrieves and prints the results. After nearly every ODBC function call, it checks that the function executed successfully, and if not, prints the diagnostic record and exits.
use UnixODBC ':all';
# ODBC Handles
my $evh; # Environment handle
my $cnh; # Connection handle
my $sth; # Statement handle
# Return Value of Function Calls
my $r; # result
# Common Data Buffers and Data Lengths
my $buf; # Buffer for results
my $rlen; # Length of Returned Value.
# Variables for Diagnostic Messages
my $diagrecno = 1;
my $sqlstate;
# DSN Name and Login Information. Edit the DSN, login,
# and password when using this script on another system.
my $DSN = 'Catalog';
my $UserName = 'user';
my $PassWord = 'password';
# SQL Query. In this example, the table is named "titles"
# and contains 5 data columns.
my $query = 'select * from titles;';
# Allocate the environment handle. If the function resulted in
# an error, print the Diagnostic information and exit the
# program.
$r = SQLAllocHandle ($SQL_HANDLE_ENV, $SQL_NULL_HANDLE, $evh);
# After function calls, retrieve the diagnostic record, print
# the diagnostic message, and exit if the function was
# unsuccessful.
if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
SQLGetDiagRec ($SQL_HANDLE_ENV, $evh, $diagrecno, $sqlstate,
$native, $buf, $SQL_MAX_MESSAGE_LENGTH, $rlen);
print "$buf\n";
exit 1
}
# Specify ODBC Version 2
$r = SQLSetEnvAttr($evh, $SQL_ATTR_ODBC_VERSION, $SQL_OV_ODBC2, 0);
if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
SQLGetDiagRec ($SQL_HANDLE_ENV, $evh, $diagrecno, $sqlstate,
$native, $buf, $SQL_MAX_MESSAGE_LENGTH, $rlen);
print "$buf\n";
exit 1;
}
# Allocate the connection handle.
$r = SQLAllocHandle ($SQL_HANDLE_DBC, $evh, $cnh);
if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
SQLGetDiagRec ($SQL_HANDLE_ENV, $evh, $diagrecno, $sqlstate,
$native, $buf, $SQL_MAX_MESSAGE_LENGTH, $rlen);
print "$buf\n";
exit 1;
}
# Connect to the data source. $SQL_NTS in place of the length of the
# preceding parameter indicates a null-terminated string.
$r = SQLConnect ($cnh, $DSN, $SQL_NTS,
$UserName, $SQL_NTS,
$PassWord, $SQL_NTS);
if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
SQLGetDiagRec ($SQL_HANDLE_DBC, $cnh, $diagrecno, $sqlstate,
$native, $buf, $SQL_MAX_MESSAGE_LENGTH, $rlen);
print "$buf\n";
exit 1;
}
# Allocate a statement handle.
$r = SQLAllocHandle ($SQL_HANDLE_STMT, $cnh, $sth);
if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
SQLGetDiagRec ($SQL_HANDLE_STMT, $sth, $diagrecno, $sqlstate,
$native, $buf, $SQL_MAX_MESSAGE_LENGTH, $rlen);
print "$buf\n";
exit 1;
}
# Prepare the SQL query.
$r = SQLPrepare ($sth, $query, length ($query));
if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
SQLGetDiagRec ($SQL_HANDLE_STMT, $sth, $diagrecno, $sqlstate,
$native, $buf, $SQL_MAX_MESSAGE_LENGTH, $rlen);
print "$buf\n";
exit 1;
}
# Execute the SQL query.
$r = SQLExecute ($sth);
if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
SQLGetDiagRec ($SQL_HANDLE_STMT, $sth, $diagrecno, $sqlstate,
$native, $buf, $SQL_MAX_MESSAGE_LENGTH, $rlen);
print "$buf\n";
exit 1;
}
# Loop to retrieve data rows.
# Keep looping and then exit when there is no more data. More
# complex programs may need to check for the number of rows and
# columns in the result set before retrieving the data.
while (1) {
# Fetch the next row of data in the result set.
$r = SQLFetch ($sth);
# Exit the loop if there is no more data.
last if $r == $SQL_NO_DATA;
# Loop to retrieve the data for columns 1..5.
foreach my $column (1..5) {
$r = SQLGetData ($sth, $column, $SQL_C_CHAR, $buf,
$SQL_MAX_MESSAGE_LENGTH, $rlen);
# Print results with fields delimited by tabs.
print "$buf\t";
}
# Delimit rows in the output with newlines.
print "\n";
}
# Clean up. De-allocate the statement handle
$r = SQLFreeHandle ($SQL_HANDLE_STMT, $sth);
if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
SQLGetDiagRec ($SQL_HANDLE_STMT, $sth, $diagrecno, $sqlstate,
$native, $buf, $SQL_MAX_MESSAGE_LENGTH, $rlen);
print "$buf\n";
exit 1;
}
# Disconnect from the DSN.
$r = SQLDisconnect ($cnh);
if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
SQLGetDiagRec ($SQL_HANDLE_DBC, $cnh, $diagrecno, $sqlstate,
$native, $buf, $SQL_MAX_MESSAGE_LENGTH, $rlen);
print "$buf\n";
exit 1;
}
# De-allocate the connection handle.
$r = SQLFreeConnect ($cnh);
if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
SQLGetDiagRec ($SQL_HANDLE_DBC, $cnh, $diagrecno, $sqlstate,
$native, $buf, $SQL_MAX_MESSAGE_LENGTH, $rlen);
print "$buf\n";
exit 1;
}
# De-allocate the environment handle.
$r = SQLFreeHandle ($SQL_HANDLE_ENV, $evh);
if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
SQLGetDiagRec ($SQL_HANDLE_ENV, $evh, $diagrecno, $sqlstate,
$native, $buf, $SQL_MAX_MESSAGE_LENGTH, $rlen);
print "$buf\n";
exit 1;
}
exit 0;Basic Drivers and DSNs
On each host system, each ODBC-accessible DBMS server has an ODBC Driver defined for it. Driver definitions are contained in a file named odbcinst.ini. They are accessible using the utility programs ODBCConfig or odbcinst, as described above.
If neither utility is present on the system, consult the person who provided the ODBC libraries, or compile and install the unixODBC source code distribution from the Internet sites listed below.
Each ODBC accessible database must have a DSN defined for it. DSN definitions are contained in a file named odbc.ini. Each DSN must specify a DBMS Driver, a database name, and the host name, which is normally the local system, unless the data source is an ODBC bridge. ODBC can also use network proxies, like DBI::proxy or UnixODBC::BridgeServer, but these APIs are not part of the ODBC standard, and are not specified differently by DSNs.
If you need to connect to data sources on remote systems, refer to the UnixODBC::BridgeServer manual page and the API described there.
If necessary, you can edit odbcinst.ini or odbc.ini yourself, or create templates for Drivers and DSNs. Often the files are located in /usr/local/etc, but they may also be in /etc, or some other location. Check the unixODBC distribution for the specific directory layout. You will need to know the configuration of the DBMS server and the ODBC library modules for the Driver definition, and the name of the database and login data at least for the DSN definition.
Here is an odbcinst.ini entry for a MySQL-MyODBC Driver:
[MySQL 3.23.49]
Description = MySQL-MyODBC Sample - Edit for your system.
Driver = /usr/local/lib/libmyodbc3-3.51.02.so
Setup = /usr/local/lib/libodbcmyS.so.1.0.0
FileUsage = 1
CPTimeout =
CPReuse = Here is the odbc.ini entry for a DSN named "Contacts," which uses the MySQL driver.
[Contacts]
Description = Names and Addresses Sample - Edit for your system.
Driver = MySQL 3.23.49
Server = localhost
Port = 3396
Socket = /tmp/mysql.sock
Database = ContactsHere is another odbcinst.ini Driver definition, for PostgreSQL:
[PostgreSQL 7.x]
Description = Postgresql 7.x Sample - Edit for your system
Driver = /usr/local/lib/libodbcpsql.so.2.0.0
Setup = /usr/local/lib/libodbcpsqlS.so.1.0.0
FileUsage = 1
CPTimeout =
CPReuse = And here is the odbc.ini entry for a DSN that uses the PostgreSQL Driver:
[Postgresql]
Description = Sample DSN - Edit for your system.
Driver = PostgreSQL 7.x
Trace = No
TraceFile =
Database = gutenberg
Servername = localhost
Username = postgres
Password = postgres
Port = 5432
Protocol = 6.4
ReadOnly = No
RowVersioning = No
ShowSystemTables = No
ShowOidColumn = No
FakeOidIndex = No
ConnSettings =
Server = localhostODBC API
UnixODBC.pm implements a subset of the ODBC API. The UnixODBC.pm API differs from the standard C-language ODBC API in that UnixODBC.pm generally does not require parameters to be passed by reference. Where a function modifies multiple parameters, the parameter references are translated by UnixODBC.pm into the native ODBC C-language calling convention.
Return Values
ODBC API functions can return the following status values:
- $SQL_NULL_DATA
- $SQL_DATA_AT_EXEC
- $SQL_SUCCESS
- $SQL_SUCESS_WITH_INFO
- $SQL_NO_DATA
- $SQL_ERROR
- $SQL_INVALID_HANDLE
- $SQL_STILL_EXECUTING
- $SQL_NEED_DATADirection
Specifies the direction of a row fetch for calls to functions like SQLFetchScroll, SQLDataSources, and SQLDrivers. The ODBC API defines the following directions:
- $SQL_FETCH_FIRST
- $SQL_FETCH_NEXT
- $SQL_FETCH_PRIOR
- $SQL_FETCH_LAST
- $SQL_FETCH_ABSOLUTE
- $SQL_FETCH_RELATIVE
- $SQL_FETCH_BOOKMARKRefer to "SQLDrivers", "SQL_FETCH_SCROLL", and "SQLDataSources" (below).
Attributes
Attributes describe how an environment handle, connect handle, or statement handle communicate with the DBMS server; what functions the ODBC environment performs; and information about the DBMS server. Many of the attributes provide different information depending on the DBMS driver, and many are read-only.
Environment Attributes
Attributes used by "SQLSetEnvAttr" and "SQLGetEnvAttr", and their possible values.
- $SQL_ATTR_OUTPUT_NTS = I<true> | I<false>
- $SQL_ATTR_ODBC_VERSION = $SQL_OV_ODBC2 | $SQL_OV_ODBC3
- $SQL_ATTR_CP_MATCH = $SQL_CP_STRICT_MATCH | $SQL_CP_RELAXED_MATCH
- $SQL_ATTR_CONNECTION_POOLING = $SQL_CP_OFF | $SQL_CP_ONE_PER_DRIVER
| $SQL_CP_ONE_PER_HENV | $SQL_CP_DEFAULT
- $SQL_ATTR_UNIXODBC_ENVATTR = I<environment_variable>Connect Attributes
Attributes used by "SQLSetConnectAttr" and "SQLGetConnectAttr", and their possible values.
- $SQL_ATTR_TRACE = $SQL_OPT_TRACE_OFF | $SQL_OPT_TRACE_ON |
$SQL_OPT_TRACE_DEFAULT
- $SQL_ATTR_TRACEFILE = $SQL_OPT_TRACE_FILE_DEFAULT | I<filename>
- $SQL_ATTR_LOGIN_TIMEOUT = $SQL_LOGIN_TIMEOUT_DEFAULT
- $SQL_ATTR_ODBC_CURSORS = $SQL_CUR_USE_IF_NEEDED | $SQL_CUR_USE_ODBC |
$SQL_CUR_USE_DRIVER | $SQL_CUR_DEFAULT
- $SQL_ATTR_ASYNC_ENABLE = $SQL_ASYNC_ENABLE_ON | $SQL_ASYNC_ENABLE_OFF |
$SQL_ASYNC_ENABLE_DEFAULT
- $SQL_ATTR_ACCESS_MODE = $SQL_MODE_READ_WRITE | $SQL_MODE_READ_ONLY |
$SQL_MODE_DEFAULT
- $SQL_ATTR_AUTO_IPD = $SQL_ATTR_ENABLE_AUTO_IPD
- $SQL_ATTR_AUTOCOMMIT = $SQL_AUTOCOMMIT_ON | $SQL_AUTOCOMMIT_OFF |
$SQL_AUTOCOMMIT_DEFAULT
- $SQL_ATTR_CONNECTION_TIMEOUT = I<interval>
- $SQL_ATTR_METADATA_ID = I<id>
- $SQL_ATTR_PACKET_SIZE = I<size>
- $SQL_ATTR_QUIET_MODE = $SQL_TRUE | $SQL_FALSE
- $SQL_ATTR_TXN_ISOLATION = $SQL_TRUE | $SQL_FALSE
- $SQL_ATTR_CURRENT_CATALOG = I<catalog>
- $SQL_ATTR_TRANSLATE_LIB = I<lib>
- $SQL_ATTR_USE_BOOKMARKS = $SQL_TRUE | $SQL_FALSEOptions recognized by "SQLSetConnectOption", "SQLGetConnectOption".
- $SQL_ATTR_TRACE = $SQL_OPT_TRACE_OFF | $SQL_OPT_TRACE_ON |
$SQL_OPT_TRACE_DEFAULT
- $SQL_ATTR_TRACEFILE = $SQL_OPT_TRACE_FILE_DEFAULT | I<filename>
(Set with SQLSetConnectAttr)
- $SQL_ATTR_ACCESS_MODE = $SQL_MODE_READ_WRITE | $SQL_MODE_READ_ONLY |
$SQL_MODE_DEFAULT
- $SQL_ATTR_TXN_ISOLATION = $SQL_TRUE | $SQL_FALSE
- $SQL_ATTR_LOGIN_TIMEOUT = $SQL_LOGIN_TIMEOUT_DEFAULT
- $SQL_ATTR_AUTOCOMMIT = $SQL_AUTOCOMMIT_ON | $SQL_AUTOCOMMIT_OFF |
$SQL_AUTOCOMMIT_DEFAULT
- $SQL_ATTR_USE_BOOKMARKS = $SQL_TRUE | $SQL_FALSE
- $SQL_ATTR_ODBC_CURSORS = $SQL_CUR_USE_IF_NEEDED | $SQL_CUR_USE_ODBC |
$SQL_CUR_USE_DRIVER | $SQL_CUR_DEFAULT
- $SQL_ATTR_CURRENT_CATALOG = I<catalog_name>
(Set with SQLSetConnectAttr)
- $SQL_ATTR_TRANSLATE_LIB = I<lib>
(Set with SQLSetConnectAttr) Statement Attributes
Attributes used by "SQLSetStmtAttr", "SQLGetStmtAttr", "SQLSetStmtOption", and "SQLGetStmtOption".
- $SQL_ATTR_ASYNC_ENABLE = $SQL_ASYNC_ENABLE_OFF | $SQL_ASYNC_ENABLE_ON |
$SQL_ASYNC_ENABLE_DEFAULT
- $SQL_ATTR_CONCURRENCY = $SQL_CONCUR_READ_ONLY | $SQL_CONCUR_LOCK |
$SQL_CONCUR_ROWVER | $SQL_CONCUR_VALUES | $SQL_CONCUR_DEFAULT
- $SQL_ATTR_CURSOR_TYPE = $SQL_CURSOR_FORWARD_ONLY |
$SQL_CURSOR_KEYSET_DRIVEN | $SQL_CURSOR_DYNAMIC | $SQL_CURSOR_STATIC |
$SQL_CURSOR_TYPE_DEFAULT
- $SQL_ATTR_SIMULATE_CURSOR = $SQL_TRUE | $SQL_FALSE
- $SQL_ATTR_ENABLE_AUTO_IPD = $SQL_TRUE | $SQL_FALSE
- $SQL_ATTR_FETCH_BOOKMARK_PTR = I<ptr>
- $SQL_ATTR_KEYSET_SIZE = $SQL_ATTR_KEYSET_SIZE_DEFAULT | I<size_ptr>
- $SQL_ATTR_MAX_LENGTH = I<numeric_value>
- $SQL_ATTR_MAX_ROWS = $SQL_MAX_ROWS_DEFAULT | I<numeric_value>
- $SQL_ATTR_NOSCAN = $SQL_NOSCAN_ON | $SQL_NOSCAN_OFF |
$SQL_NOSCAN_DEFAULT
- $SQL_ATTR_QUERY_TIMEOUT = $SQL_QUERY_TIMEOUT_DEFAULT |
I<numeric_value>
- $SQL_ATTR_RETRIEVE_DATA = $SQL_RD_ON | $SQL_RD_OFF |
$SQL_RD_DEFAULT
- $SQL_ATTR_ROW_NUMBER = I<numeric_calue>UnixODBC Functions
SQLAllocConnect (environment_handle, new_connection_handle);
SQLAllocConnect is a convenience function that calls SQLAllocHandle to allocate a connection handle.
$r = SQLAllocConnect ($evh, $cnh);SQLAllocEnv (new_environment_handle)
SQLAllocEnv is a convenience function that calls SQLAllocHandle to allocate an environment handle.
$r = SQLAllocEnv ($evh);SQLAllocHandle (handle_type, parent_handle, new_handle)
# Allocate an environment handle.
$r = SQLAllocHandle ($SQL_HANDLE_ENV, $SQL_NULL_HANDLE, $evh);
# Allocate a connection handle.
$r = SQLAllocHandle ($SQL_HANDLE_DBC, $evh, $cnh);
# Allocate a statement handle.
$r = SQLAllocHandle ($SQL_HANDLE_STMT, $cnh, $sth);
# Allocate a descriptor handle.
$r = SQLAllocHandle ($SQL_HANDLE_DESC, $cnh, $desc);SQLColAttribute (statement_handle, column_number, attribute_to_select, text_of_attribute, maxsize, returned_text_length, numeric_attr);
Attributes defined for columns depend on the DBMS server and Driver implementation. However, unixODBC defines the following column attributes:
Attribute Column Type
---------------- ----
- $SQL_COLUMN_COUNT Numeric
- $SQL_COLUMN_NAME Character
- $SQL_COLUMN_TYPE Numeric
- $SQL_COLUMN_LENGTH Numeric
- $SQL_COLUMN_PRECISION Numeric
- $SQL_COLUMN_SCALE Boolean
- $SQL_COLUMN_DISPLAY_SIZE Numeric
- $SQL_COLUMN_NULLABLE Boolean
- $SQL_COLUMN_UNSIGNED Boolean
- $SQL_COLUMN_MONEY Boolean
- $SQL_COLUMN_UPDATABLE Boolean
- $SQL_COLUMN_AUTO_INCREMENT Boolean
- $SQL_COLUMN_CASE_SENSITIVE Boolean
- $SQL_COLUMN_SEARCHABLE Numeric
- $SQL_COLUMN_TYPE_NAME Character
- $SQL_COLUMN_TABLE_NAME Character
- $SQL_COLUMN_OWNER_NAME Character
- $SQL_COLUMN_QUALIFIER_NAME Character
- $SQL_COLUMN_LABEL Character
- $SQL_COLUMN_DRIVER_START NumericBoolean types return 1 for true, 0 for false.
# Display the number of attribute columns.
$r = SQLColAttribute ($sth, $column_number,
$SQL_COLUMN_COUNT, $char_attribute,
$SQL_MAX_MESSAGE_LENGTH,
$returned_length, $numeric_attribute);
print "$numeric_attribute\n";
# Display the name of first attribute column.
$r = SQLColAttribute ($sth,
1, # Specify column 1.
$SQL_COLUMN_NAME,
$name,
$SQL_MAX_MESSAGE_LENGTH,
$returned_length,
$numeric_attribute);
print "$name\n";SQLCancel (statement_handle)
SQLCancel cancels a function call in progress on a statement handle.
$r = SQLCancel ($sth);SQLColumnPrivileges (statement_handle, catalog_name, catalog_name_length, schema_name, schema_name_length, table_name, table_name_length, column_name, column_name_length);
SQLColumns (statement_handle, catalog_name, catalog_name_length, schema_name, schema_name_length, table_name, table_name_length, column_name, column_name_length)
# Get info for each column in a table. The table
# name is required.
$r = &UnixODBC::SQLColumns ($sth,
'', 0,
'', 0,
$table_name, length ($table_name),
'', 0);
if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
SQLGetDiagRec ($SQL_HANDLE_STMT, $sth, 1, $sqlstate,
$native, $message_text, 255, $mlen);
exit 1;
}
# Display the number of columns in the result set
$r = SQLNumResultCols ($sth,$ncols);
if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
SQLGetDiagRec ($SQL_HANDLE_STMT, $sth, 1, $sqlstate,
$native, $message_text,
$SQL_MAX_MESSAGE_LENGTH, $mlen);
exit 1;
}
print "$ncols Columns\n";
while (1) {
$r = SQLFetch ($sth);
last if $r == $SQL_NO_DATA;
if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
SQLGetDiagRec ($SQL_HANDLE_STMT, $sth, 1, $sqlstate,
$native, $message_text,
$SQL_MAX_MESSAGE_LENGTH, $mlen);
exit 1;
}
foreach my $cn (1..$ncols) {
$r = SQLGetData ($sth, $cn, $SQL_C_CHAR, $rbuf, 255, $mlen);
if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
SQLGetDiagRec ($SQL_HANDLE_STMT, $sth, 1, $sqlstate,
$native, $message_text,
$SQL_MAX_MESSAGE_LENGTH, $mlen);
exit 1;
}
print "$rbuf\t"; # Tabs delimit fields in the output.
}
print "\n"; # Newlines delimit rows in the output.
} The result set contains at least the following information for each column:
- Catalog Name
- Schema Name
- Table Name
- Column Name
- SQL Data Type (Numeric Code)
- Data Type Name
- Column Size
- Data Length
- Decimal Digits
- Radix (10 or 2)
- NullableSQLConnect (connection_handle, dsn, dsn_length, username, username_length, password, password_length)
# Connect to a Data Source.
$r = SQLConnect ($cnh,
$DSN,
length ($DSN),
$UserName,
length ($UserName),
$PassWord,
length ($PassWord));
if ($r != $SQL_SUCCESS) {
SQLGetDiagRec ($SQL_HANDLE_STMT, $sth, $diagrecno,
$sqlstate, $native, $buf,
$SQL_MAX_MESSAGE_LENGTH, $rlen);
}SQLDataSources (environment_handle, direction, DSN, DSN_max_length, returned_DSN_length, drivername, drivername_max_length, returned_driver_length )
# Print a list of DSNs and their drivers.
# Fetch names of the first DSN and Driver.
$r = SQLDataSources ( $envhandle,
$SQL_FETCH_FIRST,
$dsn_buf,
$buflen,
$rlen1,
$driver_buf,
$buflen,
$rlen2);
if (($r != $SQL_SUCCESS) && ($r != $SQL_NO_DATA)) {
SQLGetDiagRec ($SQL_HANDLE_ENV, $envhandle, $diagrecno,
$sqlstate, $native, $buf,
$SQL_MAX_MESSAGE_LENGTH, $rlen);
exit 1;
}
print "$dsn_buf -- $driver_buf\n";
# Fetch the names of the following DSNs and Drivers.
while ($r != $SQL_NO_DATA) {
$r = SQLDataSources ($envhandle,
$SQL_FETCH_NEXT,
$dsn_buf,
$buflen,
$rlen1,
$driver_buf,
$buflen,
$rlen2);
if (($r != $SQL_SUCCESS) && ($r != $SQL_NO_DATA)) {
SQLGetDiagRec ($SQL_HANDLE_ENV, $envhandle, $diagrecno,
$sqlstate, $native, $buf,
$SQL_MAX_MESSAGE_LENGTH, $rlen);
exit 1;
}
print "$dsnname -- $drivername\n";
}SQLDescribeCol (statement_handle, column_number, column_name, max_length, returned_length, data_type, column_size, decimal_digits, nullable)
SQLDescribeCol describes a column of a result set produced by a SQLExecute or SQLExecDirect function call.
$r = SQLDescribeCol ($sth, $column_number, $name, $SQL_MAX_MESSAGE_LENGTH,
$name_length, $type, $size, $decimal_places,
$nullable);
if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
SQLGetDiagRec ($SQL_HANDLE_ENV, $envhandle, $diagrecno,
$sqlstate, $native, $buf,
$SQL_MAX_MESSAGE_LENGTH, $rlen);
exit 1;
}
print "$name, $type, $size, $decimal_places, $nullable\n";SQLDisconnect (statement_handle)
$r = SQLDisconnect ($sth);SQLDrivers (environment_handle, direction, driver_description, maximum_description_length, returned_description_length, driver_attributes, maximum_attribute_length, returned_attribute_length)
Fetch a list of Drivers and their descriptions.
# Fetch the name and description of the first Driver.
$r = SQLDrivers ($envhandle,
$SQL_FETCH_FIRST,
$driver_buf,
$buflen,
$rlen1,
$desc_buf,
$buflen,
$rlen2);
if (($sqlresult != $SQL_SUCCESS) && ($sqlresult != $SQL_NO_DATA)) {
SQLGetDiagRec ($SQL_HANDLE_ENV, $envhandle, $diagrecno,
$sqlstate, $native, $buf,
$SQL_MAX_MESSAGE_LENGTH, $rlen);
exit 1;
}
print "$driver_buf, $desc_buf\n";
# Fetch the names and descriptions of the following drivers.
while (1) {
$r =
SQLDrivers ($envhandle,
$SQL_FETCH_NEXT,
$driver_buf,
$buflen,
$rlen1,
$desc_buf,
$buflen,
$rlen2);
# Exit the while loop if no more entries.
last if $r == $SQL_NO_DATA;
if ($r != $SQL_SUCCESS) {
SQLGetDiagRec ($SQL_HANDLE_ENV, $envhandle, $diagrecno,
$sqlstate, $native, $buf,
$SQL_MAX_MESSAGE_LENGTH, $rlen);
exit 1;
}
print "$driver_buf, $desc_buf\n";
}SQLEndTran (handle_type, handle, completion_type)
SQLEndTran cancels a transaction. Completion_type can be either:
- $SQL_COMMIT
- $SQL_ROLLBACK
$r = SQLEndTran ($SQL_HANDLE_DBC, $cnh, $SQL_ROLLBACK);SQLError (environment_handle, connection_handle, statement_handle, sqlstate, native_error, text, maximum_length, text_length)
# Display SQL error information - the information is driver
# dependent.
$r = SQLError ($evh, $cnh, $sth, $state, $native, $text,
$SQL_MAX_MESSAGE_LENGTH, $length);
print "$state, $native, $text\n";SQLExecDirect (statement_handle, statement_text, text_length)
$r = &UnixODBC::SQLExecDirect ($sth, $query, length ($query));
if ($r != $SQL_SUCCESS) {
SQLGetDiagRec ($SQL_HANDLE_STMT, $sth, $diagrecno,
$sqlstate, $native, $buf,
$SQL_MAX_MESSAGE_LENGTH, $rlen);
exit 1;
}SQLExecute (statement_handle)
Executes a query prepared by SQLPrepare (below).
SQLFetch (statement_handle)
Fetches the next row of the result set of a query.
# Fetch rows of a result set that contains 5 columns.
while (1) {
$r = SQLFetch ($sth);
# Exit the loop if there are no more rows to be fetched.
last if $r == $SQL_NO_DATA;
foreach my $column_number (1..$total_columns_in_result_set) {
$r = SQLGetData ($sth,
$column_number,
$SQL_C_CHAR,
$col_data,
$SQL_MAX_MESSAGE_LENGTH,
$column_len);
print "$col_data\t"; # Tab is column delimiter in output.
}
print "\n"; # Newline is row delimiter.
}SQLFetchScroll (sth, direction, row_number)
Fetches data rows while specifying direction and row number. Refer to "Direction", above.
# Query Data source tables, then fetch and display
# table names.
$r = SQLTables ($sth, '', 0, '', 0, '', 0, '', 0);
if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
SQLGetDiagRec ($SQL_HANDLE_STMT, $sth, $diagrecno,
$sqlstate, $native, $buf,
$SQL_MAX_MESSAGE_LENGTH, $rlen);
exit 1;
}
my $row = 1;
$r = SQLFetchScroll ($sth,
$SQL_FETCH_ABSOLUTE,
$row++);
if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
SQLGetDiagRec ($SQL_HANDLE_STMT, $sth, $diagrecno,
$sqlstate, $native, $buf,
$SQL_MAX_MESSAGE_LENGTH, $rlen);
exit 1;
}
# Table name is 3rd column of results.
$r = SQLGetData ($sth,
3,
$SQL_C_CHAR,
$table_name,
$SQL_MAX_MESSAGE_LENGTH,
$rlen);
if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
SQLGetDiagRec ($SQL_HANDLE_STMT, $sth, $diagrecno, $sqlstate,
$native, $buf,
$SQL_MAX_MESSAGE_LENGTH, $rlen);
exit 1;
}
print "$row. $table_name\n";
while (1) {
$r = SQLFetchScroll ($sth,
$SQL_FETCH_ABSOLUTE,
$row++);
# Exit while loop if there are no more rows to fetch.
last if $r == $SQL_NO_DATA;
# Table name is 3rd column of results.
$r = SQLGetData ($sth,
3,
$SQL_C_CHAR,
$table_name,
$SQL_MAX_MESSAGE_LENGTH,
$table_name_len);
print "$row. $table_name\n";
}
if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
SQLGetDiagRec ($SQL_HANDLE_STMT, $sth, $diagrecno,
$sqlstate, $native, $buf,
$SQL_MAX_MESSAGE_LENGTH, $rlen);
exit 1;
}SQLForeignKeys (statement_handle, native_key_table_catalog_name, native_key_table_catalog_name_length, native_key_table_schema_name, native_key_table_schema_name_length, native_key_table_name, native_key_table_name_length, foreign_key_catalog_name, foreign_key_catalog_name_length, foreign_key_table_schema_name, foreign_key_table_schema_name_length, foreign_key_table_name, foreign_key_table_name_length)
SQLFreeConnect (connection_handle)
Convenience function to de-allocate a connection handle. Refer to SQLFreeHandle, below.
$r = SQLFreeConnect ($cnh);SQLFreeEnv (environment_handle)
Convenience function to de-allocate an environment handle.
SQLFreeHandle (handle_type, handle)
De-allocate a valid handle.
# De-allocate an environment handle.
$r = SQLFreeHandle ($SQL_HANDLE_ENV, $evh);
# De-allocate a connection handle.
$r = SQLFreeHandle ($SQL_HANDLE_DBC, $cnh);
# De-allocate a statement handle.
$r = SQLFreeHandle ($SQL_HANDLE_STMT, $sth);SQLFreeStmt (statement_handle, option)
SQLFreeStmt de-allocates a statement handle and provides the following options.
- $SQL_CLOSE
- $SQL_DROP
- $SQL_RESET_PARMS
- $SQL_UNBIND
# De-allocate the statment handle after closing the cursor.
$r = SQLFreeStmt ($sth, $SQL_CLOSE);
SQLGetConnectAttr (connection_handle, attribute, buffer_for_returned_data, buffer_length, length_of_returned_data)
# Print the name of the Driver Manager trace file.
$r = SQLGetConnectAttr ($cnh, $SQL_ATTR_TRACEFILE,
$ibuf, $SQL_MAX_MESSAGE_LENGTH, $ibuflen);Attributes are listed in "Connect Attributes".
See also "SQLSetConnectAttr" and "SQLGetInfo".
SQLGetConnectAttr is deprecated in the ODBC 3.0 standard.
SQLGetConnectOption (connection_handle, option, value)
Attributes are listed in "Connect Attributes".
$r = SQLGetConnectOption ($cnh, $SQL_AUTOCOMMIT, $buf);
print "SQL_AUTOCOMMIT = SQL_AUTOCOMMIT_ON\n" if $buf == $SQL_AUTOCOMMIT_ON;
print "SQL_AUTOCOMMIT = SQL_AUTOCOMMIT_OFF\n" if $buf == $SQL_AUTOCOMMIT_OFF;Refer also to "SQLSetConnectOption".
SQLGetCursorName (statement_handle, result_buffer, maximum_buffer_length, length_of_result)
SQLGetCursorName retrieves the name of the cursor set by "SQLSetCursorName".
SQLGetData (statement_handle, column_number, sqltype, data, maximum_column_width, returned_data_length)
Retrieves data for each column after a "SQLFetch" or "SQLFetchScroll". Refer to the examples above.
SQLGetDiagField (handle_type, handle, record_number, diagnostic_identifier, data_buffer, maximum_buffer_length, length_of_returned data)
Get a field from a diagnostic record. unixODBC defines the following diagnostic record identifiers:
- $SQL_DIAG_RETURNCODE = 1;
- $SQL_DIAG_NUMBER = 2;
- $SQL_DIAG_ROW_COUNT = 3;
- $SQL_DIAG_SQLSTATE = 4;
- $SQL_DIAG_NATIVE = 5;
- $SQL_DIAG_MESSAGE_TEXT = 6;
- $SQL_DIAG_DYNAMIC_FUNCTION = 7;
- $SQL_DIAG_CLASS_ORIGIN = 8;
- $SQL_DIAG_SUBCLASS_ORIGIN = 9;
- $SQL_DIAG_CONNECTION_NAME = 10;
- $SQL_DIAG_SERVER_NAME = 11;
- $SQL_DIAG_DYNAMIC_FUNCTION_CODE = 12;
# Print the SQL Error code.
$r = SQLGetDiagField ($SQL_HANDLE_STMT, $sth, 1, $SQL_DIAG_NATIVE,
$text, $SQL_MAX_MESSAGE_LENGTH, $length);
$text = sprintf "%d%d", $text;
print "$text\n";SQLGetDiagRec (handle_type, handle, record_number, SQL_state, SQL_native_error, error_message_buffer, maximum_message_buffer_length, returned_error_message_length)
Retrieve a diagnostic record after an ODBC function call. Refer to the code examples in the entries for other functions.
SQLGetEnvAttr (environment_handle, attribute, data_buffer, maximum_buffer_length, length_of_returned_data)
# Display the version of ODBC supported by the driver.
$result = SQLGetEnvAttr ($evh,
$SQL_ATTR_ODBC_VERSION,
$odbc_version,
$SQL_MAX_MESSAGE_LENGTH,
$returned_length);
print "ODBC Version $odbc_version.\n";SQLGetFunctions (connection_handle, function, supported)
Determine if a connection supports an API function. The parameter supported contains a boolean value.
unixODBC defines the following function selectors:
- $SQL_API_SQLALLOCHANDLESTD
- $SQL_API_SQLBULKOPERATIONS
- $SQL_API_SQLALLOCCONNECT
- $SQL_API_SQLALLOCENV
- $SQL_API_SQLALLOCHANDLE
- $SQL_API_SQLALLOCSTMT
- $SQL_API_SQLBINDCOL
- $SQL_API_SQLBINDPARAM
- $SQL_API_SQLCANCEL
- $SQL_API_SQLCLOSECURSOR
- $SQL_API_SQLCOLATTRIBUTE
- $SQL_API_SQLCOLUMNS
- $SQL_API_SQLCONNECT
- $SQL_API_SQLCOPYDESC
- $SQL_API_SQLDATASOURCES
- $SQL_API_SQLDESCRIBECOL
- $SQL_API_SQLDISCONNECT
- $SQL_API_SQLENDTRAN
- $SQL_API_SQLERROR
- $SQL_API_SQLEXECDIRECT
- $SQL_API_SQLEXECUTE
- $SQL_API_SQLFETCH
- $SQL_API_SQLFETCHSCROLL
- $SQL_API_SQLFREECONNECT
- $SQL_API_SQLFREEENV
- $SQL_API_SQLFREEHANDLE
- $SQL_API_SQLFREESTMT
- $SQL_API_SQLGETCONNECTATTR
- $SQL_API_SQLGETCONNECTOPTION
- $SQL_API_SQLGETCURSORNAME
- $SQL_API_SQLGETDATA
- $SQL_API_SQLGETDESCFIELD
- $SQL_API_SQLGETDESCREC
- $SQL_API_SQLGETDIAGFIELD
- $SQL_API_SQLGETDIAGREC
- $SQL_API_SQLGETENVATTR
- $SQL_API_SQLGETFUNCTIONS
- $SQL_API_SQLGETINFO
- $SQL_API_SQLGETSTMTATTR
- $SQL_API_SQLGETSTMTOPTION
- $SQL_API_SQLGETTYPEINFO
- $SQL_API_SQLNUMRESULTCOLS
- $SQL_API_SQLPARAMDATA
- $SQL_API_SQLPREPARE
- $SQL_API_SQLPUTDATA
- $SQL_API_SQLROWCOUNT
- $SQL_API_SQLSETCONNECTATTR
- $SQL_API_SQLSETCONNECTOPTION
- $SQL_API_SQLSETCURSORNAME
- $SQL_API_SQLSETDESCFIELD
- $SQL_API_SQLSETDESCREC
- $SQL_API_SQLSETENVATTR
- $SQL_API_SQLSETPARAM
- $SQL_API_SQLSETSTMTATTR
- $SQL_API_SQLSETSTMTOPTION
- $SQL_API_SQLSPECIALCOLUMNS
- $SQL_API_SQLSTATISTICS
- $SQL_API_SQLTABLES
- $SQL_API_SQLTRANSACTSQLGetInfo (connection_handle, attribute, result, maximum_result_length, length_of_returned_data)
Get information about a connection handle. The following client lists the attributes and their values for a valid connection handle. The DSN, user name, and password given as command line arguments.
Note that some Info attributes return scalar strings, others unsigned integers. The example program, "connectinfo," shows how to cope with different data types and attribute masks.
#!/usr/bin/perl -w
# $Id: UnixODBC.pm,v 1.40 2004/03/06 22:12:36 kiesling Exp $
$VERSION=1.0;
use UnixODBC qw(:all);
use Getopt::Long;
my $evh = 0;
my $cnh = 0;
my $sth = 0;
my $r = 0;
##
## DSN, username, and password from command line.
##
my $DSN = '';
my $UserName = '';
my $PassWord = '';
my $Numeric = '';
my $usage=<<EOH;
Usage: connectinfo [--help] | [--labels] [--user=<username>] [--password=<password>] --dsn=<DSN>
--help Print this help and exit.
--dsn Data source name.
--user DBMS login name.
--password DBMS login password.
--numeric Print numeric values instead of labels.
EOH
my $help; # Print help and exit.
GetOptions ('help' => \$help,
'dsn=s' => \$DSN,
'user=s' => \$UserName,
'password=s' => \$PassWord,
'numeric' => \$Numeric);
if ($help || (not length ($DSN)))
{
print $usage;
exit 0;
}
my ($ibuf, $ibuflength);
my %string_attrs = ('SQL_DATA_SOURCE_NAME', 2,
'SQL_SERVER_NAME', 13,
'SQL_DBMS_NAME', 17,
'SQL_DBMS_VER', 18,
'SQL_USER_NAME', 47,
'SQL_ORDER_BY_COLUMNS_IN_SELECT', 90,
'SQL_ACCESSIBLE_TABLES', 19,
'SQL_DATA_SOURCE_READ_ONLY', 25,
'SQL_ACCESSIBLE_PROCEDURES', 20,
'SQL_INTEGRITY', 73,
'SQL_SEARCH_PATTERN_ESCAPE', 14,
'SQL_IDENTIFIER_QUOTE_CHAR', 29,
'SQL_XOPEN_CLI_YEAR', 10000,
'SQL_CATALOG_NAME', 10003,
'SQL_DESCRIBE_PARAMETER', 10002,
'SQL_COLLATION_SEQ',10004,
);my %numeric_attrs = ('SQL_MAX_DRIVER_CONNECTIONS', 0, 'SQL_FETCH_DIRECTION', 8, 'SQL_MAX_IDENTIFIER_LEN', 10005, 'SQL_ASYNC_MODE', 10021, 'SQL_OUTER_JOIN_CAPABILITIES', 115, 'SQL_MAX_CONCURRENT_ACTIVITIES', 1, 'SQL_MAXIMUM_CONCURRENT_ACTIVITIES', 1, 'SQL_CURSOR_COMMIT_BEHAVIOR', 23, 'SQL_DEFAULT_TRANSACTION_ISOLATION', 26, 'SQL_IDENTIFIER_CASE', 28, 'SQL_MAXIMUM_COLUMN_NAME_LENGTH', 30, 'SQL_MAXIMUM_CURSOR_NAME_LENGTH', 31, 'SQL_MAXIMUM_SCHEMA_NAME_LENGTH', 32, 'SQL_MAXIMUM_CATALOG_NAME_LENGTH', 34, 'SQL_MAX_TABLE_NAME_LEN', 35, 'SQL_SCROLL_CONCURRENCY', 43, 'SQL_TRANSACTION_CAPABLE', 46, 'SQL_TRANSACTION_CAPABLE', 46, 'SQL_TRANSACTION_ISOLATION_OPTION', 72, 'SQL_TRANSACTION_ISOLATION_OPTION', 72, 'SQL_GETDATA_EXTENSIONS', 81, 'SQL_NULL_COLLATION', 85, 'SQL_ALTER_TABLE', 86, 'SQL_SPECIAL_CHARACTERS', 94, 'SQL_MAXIMUM_COLUMNS_IN_GROUP_BY', 97, 'SQL_MAXIMUM_COLUMNS_IN_INDEX', 98, 'SQL_MAXIMUM_COLUMNS_IN_ORDER_BY', 99, 'SQL_MAXIMUM_COLUMNS_IN_SELECT', 100, 'SQL_MAX_COLUMNS_IN_TABLE', 101, 'SQL_MAXIMUM_INDEX_SIZE', 102, 'SQL_MAXIMUM_ROW_SIZE', 104, 'SQL_MAXIMUM_STATEMENT_LENGTH', 105, 'SQL_MAXIMUM_TABLES_IN_SELECT', 106, 'SQL_MAXIMUM_USER_NAME_LENGTH', 107, 'SQL_CURSOR_SENSITIVITY', 10001, );
$SIG{PIPE} = sub { print "SIGPIPE: ". $! . "\n"};
$r = SQLAllocHandle ($SQL_HANDLE_ENV, $SQL_NULL_HANDLE, $evh);
if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
print "SQLAllocHandle evh: ";
&getdiagrec ($SQL_HANDLE_ENV, $evh);
exit 1;
}
$r = SQLSetEnvAttr($evh, $SQL_ATTR_ODBC_VERSION, $SQL_OV_ODBC2, 0);
if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
&getdiagrec ($SQL_HANDLE_ENV, $evh);
exit 1;
}
$r = SQLAllocHandle ($SQL_HANDLE_DBC, $evh, $cnh);
if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
&getdiagrec ($SQL_HANDLE_ENV, $evh);
exit 1;
}
$r = SQLConnect ($cnh, $DSN, $SQL_NTS,
$UserName, $SQL_NTS,
$PassWord, $SQL_NTS);
if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
&getdiagrec ($SQL_HANDLE_DBC, $cnh);
exit 1;
}
foreach my $it (keys %string_attrs) {
$ibuf = '';
no warnings;
$r = SQLGetInfo ($cnh, $string_attrs{$it},$ibuf,
$SQL_MAX_MESSAGE_LENGTH, $ibuflength);
use warnings;
print "$it \= $ibuf\n";
}
foreach my $it (keys %numeric_attrs) {
$ibuf = '';
no warnings;
$r = SQLGetInfo ($cnh, $numeric_attrs{$it},$ibuf, $SQL_IS_UINTEGER, 0);
use warnings;
if ($Numeric) {
$ibuf = sprintf "%u", $ibuf;
print "$it \= $ibuf\n" if length ($ibuf);
} else {
print "$it \= ";
if ($it =~ /SQL_ASYNC_MODE/) {
print "SQL_AM_NONE\n" if $ibuf == 0;
print "SQL_AM_CONNECTION\n" if $ibuf == 1;
print "SQL_AM_STATEMENT\n" if $ibuf == 2;
} elsif ($it =~ /SQL_CURSOR_COMMIT_BEHAVIOR/) {
print "SQL_CB_DELETE\n" if $ibuf == 0;
print "SQL_CB_CLOSE\n" if $ibuf == 1;
print "SQL_CB_PRESERVE\n" if $ibuf == 2;
} elsif ($it =~ /SQL_FETCH_DIRECTION/) {
$s = mask_labels ($ibuf, 'SQL_FD_FETCH_NEXT',
'SQL_FD_FETCH_FIRST',
'SQL_FD_FETCH_LAST',
'SQL_FD_FETCH_PRIOR',
'SQL_FD_FETCH_ABSOLUTE',
'SQL_FD_FETCH_RELATIVE');
print "$s\n";
} elsif ($it =~ /SQL_GETDATA_EXTENSIONS/) {
$s = mask_labels ($ibuf, 'SQL_GD_ANY_COLUMN',
'SQL_GD_ANY_ORDER');
print "$s\n";
} elsif ($it =~ /SQL_IDENTIFIER_CASE/) {
print 'SQL_IC_UPPER' if $ibuf == $SQL_IC_UPPER;
print 'SQL_IC_LOWER' if $ibuf == $SQL_IC_LOWER;
print 'SQL_IC_SENSITIVE' if $ibuf == $SQL_IC_SENSITIVE;
print 'SQL_IC_MIXED' if $ibuf == $SQL_IC_MIXED;
print "\n";
} elsif ($it =~ /SQL_OUTER_JOIN_CAPABILITIES/) {
$s = mask_labels ($ibuf, SQL_OJ_LEFT, SQL_OJ_RIGHT,
SQL_OJ_FULL, SQL_OJ_NESTED,
SQL_OJ_NOT_ORDERED,
SQL_OJ_INNER, SQL_OJ_ALL_COMPARISON_OPS);
print "$s\n";
} elsif ($it =~ /SQL_SCROLL_CONCURRENCY/) {
$s = mask_labels ($ibuf, SQL_SCCO_READ_ONLY,SQL_SCCO_LOCK,
SQL_SCCO_OPT_ROWVER,SQL_SCCO_OPT_VALUES);
print "$s\n";
} elsif ($it =~ /SQL_TRANSACTION_CAPABLE/) {
print 'SQL_TC_NONE' if $ibuf == $SQL_TC_NONE;
print 'SQL_TC_DML' if $ibuf == $SQL_TC_DML;
print 'SQL_TC_ALL' if $ibuf == $SQL_TC_ALL;
print 'SQL_TC_DDL_COMMIT' if $ibuf == $SQL_TC_DDL_COMMIT;
print 'SQL_TC_DDL_IGNORE' if $ibuf == $SQL_TC_DDL_IGNORE;
print "\n";
} elsif ($it =~ /SQL_TRANSACTION_ISOLATION_OPTION/) {
$s = mask_labels ($ibuf, SQL_TRANSACTION_READ_UNCOMMITTED,
SQL_TRANSACTION_READ_COMMITTED,
SQL_TRANSACTION_REPEATABLE_READ,
SQL_TRANSACTION_SERIALIZABLE);
print "$s\n";
} elsif ($it =~ /SQL_NULL_COLLATION/) {
$s = mask_labels ($ibuf, SQL_NC_START, SQL_NC_END);
print "$s\n";
} elsif ($it =~ /SQL_ALTER_TABLE/) {
$s = mask_labels ($ibuf, 'SQL_AT_ADD_COLUMN',
'SQL_AT_DROP_COLUMN');
print "$s\n";
} else {
print "$ibuf\n";
}
}
}
$r = SQLDisconnect ($cnh);
if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
&getdiagrec ($SQL_HANDLE_DBC, $cnh);
exit 1;
}
$r = SQLFreeConnect ($cnh);
if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
&getdiagrec ($SQL_HANDLE_DBC, $cnh);
exit 1;
}
$r = SQLFreeHandle ($SQL_HANDLE_ENV, $evh);
if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
&getdiagrec ($SQL_HANDLE_ENV, $evh);
exit 1;
}
exit 0;
sub getdiagrec {
my ($handle_type, $handle) = @_;
my ($sqlstate, $native, $message_text, $mlen);
print 'SQLGetDiagRec: ';
$r = &UnixODBC::SQLGetDiagRec ($handle_type, $handle, 1, $sqlstate,
$native, $message_text,
$SQL_MAX_MESSAGE_LENGTH,
$mlen);
if ($r == $SQL_NO_DATA) {
print "result \= SQL_NO_DATA\n";
} elsif (($r == 1) || ($r == 0)) {
print "$message_text\n";
} else {
print "sqlresult = $r\n";
}
return $r;
}
sub mask_labels {
my $val = shift;
my @labels = @_;
my $m = 0;
my $s = '';
foreach my $a (@labels) {
if (ord($val) & hex(${$a})) {
$s .= ' | ' if $m;
$s .= "$a";
$m++;
}
}
return $s;
}SQLGetStmtAttr (statement_handle, attribute, result, maximum_result_length, actual_result_length)
Get an attribute of a statement handle. unixODBC recognizes the following statement attributes:
- $SQL_ATTR_CONCURRENCY
- $SQL_ATTR_CURSOR_TYPE
- $SQL_ATTR_SIMULATE_CURSOR
- $SQL_ATTR_CURSOR_SCROLLABLE
- $SQL_ATTR_CURSOR_SENSITIVITY
- $SQL_ATTR_USE_BOOKMARKS
- $SQL_ATTR_APP_ROW_DESC
- $SQL_ATTR_APP_PARAM_DESC
- $SQL_ATTR_IMP_ROW_DESC
- $SQL_ATTR_APP_IMP_PARAM_DESC
- $SQL_ATTR_CURSOR_SCROLLABLE
- $SQL_ATTR_CURSOR_SENSITIVITY
- $SQL_ATTR_METADATA_ID
- $SQL_FETCH_BOOKMARK_PTR
- $SQL_ATTR_ROW_STATUS_PTR
- $SQL_ATTR_ROWS_FETCHED_PTR
- $SQL_ATTR_ROW_ARRAY_SIZE
- $SQL_STMT_DRIVER_MIN
# Requires a prepared SQL statement -
$r = SQLGetStmtAttr ($sth, $SQL_ATTR_ROW_NUMBER, $row, $SQL_IS_INTEGER, 0);See also "SQLSetStmtAttr".
SQLGetTypeInfo (statement_handle, type)
Get info for data types. The type parameters are listed here.
- $SQL_ALL_TYPES
- $SQL_UNKNOWN_TYPE
- $SQL_CHAR == $SQL_C_CHAR
- $SQL_NUMERIC == $SQL_C_NUMERIC
- $SQL_DECIMAL
- $SQL_INTEGER == $SQL_C_LONG
- $SQL_SMALLINT == $SQL_C_SHORT
- $SQL_FLOAT
- $SQL_REAL == $SQL_C_REAL
- $SQL_DOUBLE == $SQL_C_DOUBLE
- $SQL_DATE == $SQL_DATETIME == $SQL_C_DATE
- $SQL_VARCHAR
- $SQL_INTERVAL
- $SQL_TIME == $SQL_C_TIME
- $SQL_TIMESTAMP == $SQL_C_TIMESTAMP
- $SQL_LONGVARCHAR
- $SQL_BINARY == $SQL_C_BINARY
- $SQL_VARBINARY
- $SQL_LONGVARBINARY
- $SQL_BIGINT
- $SQL_TINYINT == $SQL_C_TINYINT
- $SQL_BIT == $SQL_C_BIT
- $SQL_GUID
- $SQL_C_SLONG
- $SQL_C_LONG
- $SQL_C_SSHORT
- $SQL_C_SHORT
- $SQL_C_STINYINT
- $SQL_TINYINT
- $SQL_C_ULONG
- $SQL_C_LONG
- $SQL_C_USHORT
- $SQL_C_SHORT
- $SQL_C_UTINYINT
- $SQL_TINYINT
- SQL_C_BOOKMARK == $SQL_C_ULONG;
$r = SQLGetTypeInfo ($sth, $SQL_CHAR);
$r = SQLNumResultCols ($sth,$ncols);
if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
SQLGetDiagRec ($SQL_HANDLE_ENV, $evh, $diagrecno, $sqlstate,
$native, $buf, $SQL_MAX_MESSAGE_LENGTH, $rlen);
print "$buf\n";
exit 1
}
foreach my $i (1..$ncols) {
$r = SQLColAttribute ($sth, $i,
$SQL_COLUMN_NAME, $char_attribute,
$SQL_MAX_MESSAGE_LENGTH, $mlen, $nattr);
if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
SQLGetDiagRec ($SQL_HANDLE_ENV, $evh, $diagrecno, $sqlstate,
$native, $buf, $SQL_MAX_MESSAGE_LENGTH, $rlen);
print "$buf\n";
exit 1
}
print "$char_attribute\t";
}
print "\n";
while (1) {
$r = SQLFetch ($sth);
if ($r!=$SQL_SUCCESS) {
SQLGetDiagRec ($SQL_HANDLE_ENV, $evh, $diagrecno, $sqlstate,
$native, $buf, $SQL_MAX_MESSAGE_LENGTH, $rlen);
print "$buf\n";
exit 1
}
last if $r == $SQL_NO_DATA;
foreach my $cn (1..4) {
$r=&UnixODBC::SQLGetData ($sth, $cn, $SQL_C_CHAR,
$rbuf, $SQL_MAX_MESSAGE_LENGTH, $mlen);
print "$rbuf\t";
}
print "\n";
}SQLMoreResults (statement_handle)
SQLMoreResults checks if there is further data in a result set after a SQLSetPos request.
SQLNativeSQL (connection_handle, statement, statement_text_length, driver_statement_output, maxlength, statement_output_length)
SQLNumResultCols (statement_handle, number_of_columns)
Retrieves the number of columns in a result set after a query is executed. Refer to the example for "SQLColumns", above.
SQLPrepare (statement_handle, query, length_of_query)
Prepare a SQL query for execution. Refer to the example in "ODBC Data Access Clients", above.
SQLPrimaryKeys (statement_handle, catalog_name, catalog_name_length, schema_name, schema_name_length, table_name, table_name_length)
Return a result set of primary keys for the table. The table name is required.
SQLProcedureColumns (statement_handle, catalog_name, catalog_name_length, schema_name, schema_name_length, table_name, table_name_length, column_name, column_name_length)
SQLProcedures (statement_handle, catalog_name, catalog_name_length, schema_name, schema_name_length, table_name, table_name_length)
SQLRowCount (statement_handle, rows_in_result_set)
Retrieve the number of rows in the result set of a SQL query.
# Print the number of rows after a query.
$r = SQLPrepare ($sth, 'select * from titles', 20);
if ($r!=$SQL_SUCCESS) {
SQLGetDiagRec ($SQL_HANDLE_ENV, $evh, $diagrecno, $sqlstate,
$native, $buf, $SQL_MAX_MESSAGE_LENGTH, $rlen);
print "$buf\n";
exit 1;
}
$r = SQLExecute ($sth);
if ($r!=$SQL_SUCCESS) {
SQLGetDiagRec ($SQL_HANDLE_ENV, $evh, $diagrecno, $sqlstate,
$native, $buf, $SQL_MAX_MESSAGE_LENGTH, $rlen);
print "$buf\n";
exit 1;
}
$r = &UnixODBC::SQLRowCount ($sth,$nrows);
if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
SQLGetDiagRec ($SQL_HANDLE_ENV, $evh, $diagrecno, $sqlstate,
$native, $buf, $SQL_MAX_MESSAGE_LENGTH, $rlen);
print "$buf\n";
exit 1;
}
$rowlabel = $nrows == 1 ? "row" : "rows";
print "$nrows $rowlabel\n";SQLSetConnectAttr ($cnh, <attrib>, <value>, <length>)
$r = SQLSetConnectAttr ($cnh, $SQL_ATTR_TRACE, $SQL_ATTR_TRACE_ON,
length ($SQL_ATTR_TRACE_ON));
$r = SQLSetConnectAttr ($cnh, $SQL_ATTR_TRACEFILE, '/tmp/odbc.trace',
length ('/tmp/tmp.trace'));SQLSetConnectAttr is deprecated in the ODBC standard.
SQLSetConnectOption (connection_handle, attribute, value)
# Log Driver Manager function calls to /tmp/sql.log
$r = SQLSetConnectOption ($cnh, $SQL_OPT_TRACE, $SQL_OPT_TRACE_ON);SQLSetCursorName (statement_handle, cursor_name, length_of_cursor_name)
# Set the name of the cursor.
$cursor = 'cursor1';
$r = SQLSetCursorName ($sth, $cursor, length ($cursor));SQLSetEnvAttr (environment_handle, attribute, value, length)
For a list of attributes described in "Environment Attributes", above.
$r = SQLSetEnvAttr($evh, $SQL_ATTR_ODBC_VERSION, $SQL_OV_ODBC3, 0);SQLSetPos (statement_handle, row, operation, lock)
The value of operation can be:
- $SQL_POSITION
- $SQL_REFRESHThe value of lock can be:
- $SQL_LOCK_NO_CHANGE
- $SQL_LOCK_EXCLUSIVE
- $SQL_LOCK_UNLOCK
- $SQL_SETPOS_MAX_LOCK_VALUESQLSetScrollOptions(statement_handle, concurrency, row_keyset, row_rowset)
Deprecated in ODBC 3.0.
SQLSetStmtAttr (statement_handle, attribute, value, length_of_value)
For a list of attributes, see "SQLGetStmtAttr", above.
$r = SQLSetStmtAttr ($sth, $SQL_ATTR_CONCURRENCY, "$SQL_CONCUR_DEFAULT",
length ("$SQL_CONCUR_DEFAULT");
$r = SQLSetStmtAttr ($sth, $SQL_ATTR_CURSOR_TYPE, "$SQL_CURSOR_TYPE_DEFAULT",
length ("$SQL_CURSOR_TYPE_DEFAULT");SQLSpecialColumns (statement_handle, identifier_type, catalog_name, catalog_name_length, schema_name, schema_name_length, table_name, table_name_length, scope, nullable)
SQLStatistics (statement_handle, catalog_name, catalog_name_length, schema_name, schema_name_length, table_name, table_name_length, unique, reserved)
The parameter reserved can have the value of either:
- $SQL_INDEX_UNIQUE
- $SQL_INDEX_ALLThe reserved parameter can be:
- $SQL_QUICK
- $SQL_ENSUREThese values can appear in the result set:
- $SQL_INDEX_CLUSTERED
- $SQL_INDEX_HASHED
- $SQL_INDEX_OTHER The result set of SQLStatistics is driver-dependent.
SQLTablePrivileges (statement_handle, catalog_name, catalog_name_length, schema_name, schema_name_length, table_name, table_name_length)
SQLTables (statement_handle, catalog_name, catalog_name_length, schema_name, schema_name_length, table_name, table_name_length, column_name, column_name_length)
The following script prints a list of tables for a DSN given on the command line.
use UnixODBC qw(:all);
use Getopt::Long;
# ODBC Handles
my $env;
my $cnh;
my $sth;
# Function Return Value
my $r;
# Data Buffers and Lengths
my $buf;
my $rlen; # Actual length of returned data.
##
## DSN, username, and password from command line arguments.
##
my $DSN;
my $UserName;
my $PassWord;
my $Verbose = '';
# Help Text
my $usage=<<EOH;
Usage: sqltables [--help] | [--verbose] [--dsn=DSN --user=username --password=password]
--help Print this help and exit.
--verbose Print tables' catalog, schema, name, and type.
--dsn Data source name.
--user DBMS login name.
--password DBMS login password.
EOH
# Get the DSN and login data from the command line.
GetOptions ('help' => \$help,
'verbose' => \$Verbose,
'dsn=s' => \$DSN,
'user=s' => \$UserName,
'password=s' => \$PassWord);
# If necessary print the help message and exit.
if ($help || (not length ($DSN)) || (not length ($UserName))
|| (not length ($UserName)) || (not length ($PassWord)))
{
print $usage;
exit 1;
}
# Fields defined in SQLTables result set.
my ($table_cat, $table_schem, $table_name, $table_type, $remarks);
# Allocate Environment Handle.
$r = SQLAllocHandle ($SQL_HANDLE_ENV, $SQL_NULL_HANDLE, $evh);
if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
print "SQLAllocHandle evh: ";
getdiagrec ($SQL_HANDLE_ENV, $evh);
exit 1;
}
# Set the ODBC Version
$r = SQLSetEnvAttr($evh, $SQL_ATTR_ODBC_VERSION, $SQL_OV_ODBC2, 0);
if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
getdiagrec ($SQL_HANDLE_ENV, $evh);
exit 1;
}
# Allocate a connection handle.
$r = SQLAllocHandle ($SQL_HANDLE_DBC, $evh, $cnh);
if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
getdiagrec ($SQL_HANDLE_ENV, $evh);
exit 1;
}
# Connect to the DSN given on the command line.
$r = SQLConnect ($cnh, $DSN, $SQL_NTS,
$UserName, $SQL_NTS,
$PassWord, $SQL_NTS);
if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
getdiagrec ($SQL_HANDLE_DBC, $cnh);
exit 1;
}
# Allocate a statement handle.
$r = SQLAllocHandle ($SQL_HANDLE_STMT, $cnh, $sth);
if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
getdiagrec ($SQL_HANDLE_DBC, $cnh);
exit 1;
}
# Get table information. Blank parameters are treated as matching
# every catalog, schema, table, and column for the DSN.
$r = SQLTables ($sth, '', 0, '', 0, '', 0, '', 0);
if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
getdiagrec ($SQL_HANDLE_STMT, $sth);
exit 1;
}
while (1) {
# Fetch the next row of data.
$r = SQLFetch ($sth);
# Exit the while loop if there are no more rows to fetch.
last if $r == $SQL_NO_DATA;
$r = SQLGetData ($sth, 1, $SQL_C_CHAR, $table_cat,
$SQL_MAX_MESSAGE_LENGTH, $rlen);
$r = SQLGetData ($sth, 2, $SQL_C_CHAR, $table_schem,
$SQL_MAX_MESSAGE_LENGTH, $rlen);
$r = SQLGetData ($sth, 3, $SQL_C_CHAR, $table_name,
$SQL_MAX_MESSAGE_LENGTH, $rlen);
$r = SQLGetData ($sth, 4, $SQL_C_CHAR, $table_type,
$SQL_MAX_MESSAGE_LENGTH, $rlen);
$r = SQLGetData ($sth, 5, $SQL_C_CHAR, $remarks,
$SQL_MAX_MESSAGE_LENGTH, $rlen);
# Delimit fields with tabs and lines with newlines.
if ($Verbose) {
print "$table_cat\t$table_schem\t$table_name\t$table_type\t$remarks\n";
} else {
print "$table_name\n";
}
}
if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
getdiagrec ($SQL_HANDLE_STMT, $sth);
exit 1;
}
# Clean up. Disconnect from DSN and de-allocate statement,
# connection, and environment handles.
$r = SQLFreeHandle ($SQL_HANDLE_STMT, $sth);
if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
getdiagrec ($SQL_HANDLE_STMT, $sth);
exit 1;
}
$r = SQLDisconnect ($cnh);
if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
getdiagrec ($SQL_HANDLE_DBC, $cnh);
exit 1;
}
$r = SQLFreeConnect ($cnh);
if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
getdiagrec ($SQL_HANDLE_DBC, $cnh);
exit 1;
}
$r = SQLFreeHandle ($SQL_HANDLE_ENV, $evh);
if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
getdiagrec ($SQL_HANDLE_ENV, $evh);
exit 1;
}
# Subroutine to print a SQL diagnostic record.
sub getdiagrec {
my ($handle_type, $handle) = @_;
my ($sqlstate, $native, $message_text, $mlen);
my $diagrecno = 1;
print 'SQLGetDiagRec: ';
$r = SQLGetDiagRec ($handle_type, $handle, $diagrecno,
$sqlstate, $native, $buf, $SQL_MAX_MESSAGE_LENGTH,
$rlen);
if ($r == $SQL_NO_DATA) {
print "result \= SQL_NO_DATA\n";
} elsif (($r == $SQL_SUCCESS_WITH_INFO)
|| ($r == $SQL_SUCCESS)) {
print "$buf\n";
} else {
print "sqlresult = $r\n";
}
return $r;
}dm_log_open (program_name, logfilename);
Open a log file to record driver manager function calls.
dm_log_close ();
Close a log file opened with dm_log_open();
EXPORT
Refer to the @EXPORT_OK array in UnixODBC.pm.
VERSION INFORMATION AND CREDITS
Version 0.01
Copyright © 2002 - 2004 Robert Kiesling, rkies@cpan.org.
Licensed under the same terms as Perl. Refer to the file, "Artistic," for details.
SEE ALSO
perl(1), tkdm(1), alltypes(1), apifuncs(1), colattributes(1), connectinfo(1), datasources(1), driverinfo(1), sqltables(1), odbcbridge(1), UnixODBC::BridgeServer(3)
The unixODBC programmer and reference manuals at: http://www.unixodbc.org/ and the ODBC reference library at: http://msdn.microsoft.com/.
1 POD Error
The following errors were encountered while parsing the POD:
- Around line 4335:
Non-ASCII character seen before =encoding in '©'. Assuming CP1252