Robert Kiesling > UnixODBC-0.34 > UnixODBC

Download:
UnixODBC-0.34.tar.gz

Dependencies

Annotate this POD

View/Report Bugs
Module Version: 0.34   Source  

NAME ^

UnixODBC - Perl extension for unixODBC.

SYNOPSIS ^

  use UnixODBC ':all';

DESCRIPTION ^

Perl extension for unixODBC library API.

ODBC Overview

The Open Database Connectivity Standard is an Application Programming Interface for DBMS servers and clients.

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 result sets.

ODBC uses one or more Data Source Names, or DSNs, to connect to databases. DSNs contain information about DBMS server-specific driver libraries, network connections, authentication information, and DBMS parameters. Refer to UnixODBC::DriverConf(3) and the documentation for unixODBC itself.

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 Handles

UnixODBC.pm does not provide support for descriptor handles.

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 a 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;

ODBC 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_DATA

Direction

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_BOOKMARK

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 = true | 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 = id

  - $SQL_ATTR_PACKET_SIZE = size

  - $SQL_ATTR_QUIET_MODE = $SQL_TRUE | $SQL_FALSE

  - $SQL_ATTR_TXN_ISOLATION = $SQL_TRUE | $SQL_FALSE

  - $SQL_ATTR_CURRENT_CATALOG = catalog

  - $SQL_ATTR_TRANSLATE_LIB = lib

  - $SQL_ATTR_USE_BOOKMARKS = $SQL_TRUE | $SQL_FALSE

Options 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 = catalog_name
    (Set with SQLSetConnectAttr)    

  - $SQL_ATTR_TRANSLATE_LIB = lib
    (Set with SQLSetConnectAttr)    

Statement Attributes

Attributes used by SQLSetStmtAttr and SQLGetStmtAttr.

  - $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 = ptr

  - $SQL_ATTR_KEYSET_SIZE = $SQL_ATTR_KEYSET_SIZE_DEFAULT | size_ptr

  - $SQL_ATTR_MAX_LENGTH = numeric_value

  - $SQL_ATTR_MAX_ROWS = $SQL_MAX_ROWS_DEFAULT | numeric_value

  - $SQL_ATTR_NOSCAN = $SQL_NOSCAN_ON | $SQL_NOSCAN_OFF | 
      $SQL_NOSCAN_DEFAULT

  - $SQL_ATTR_QUERY_TIMEOUT = $SQL_QUERY_TIMEOUT_DEFAULT | 
      numeric_value

  - $SQL_ATTR_RETRIEVE_DATA = $SQL_RD_ON | $SQL_RD_OFF |
      $SQL_RD_DEFAULT

  - $SQL_ATTR_ROW_NUMBER = 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                      Numeric

Boolean 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)
  - Nullable

SQLConnect (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_SQLTRANSACT

SQLGetInfo (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.58 2008-01-21 09:16:56 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'));

See "Connect Attributes"

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);

See "Connect Attributes"

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_REFRESH

The value of lock can be:

  - $SQL_LOCK_NO_CHANGE
  - $SQL_LOCK_EXCLUSIVE
  - $SQL_LOCK_UNLOCK
  - $SQL_SETPOS_MAX_LOCK_VALUE

SQLSetScrollOptions(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");

SQLSetStmtOption (statement_handle, option, value)

See "SQLSetStmtAttr".

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_ALL

The reserved parameter can be:

  - $SQL_QUICK
  - $SQL_ENSURE

These 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;
  }

EXPORTS

Refer to the @EXPORT_OK array in UnixODBC.pm.

VERSION INFORMATION AND CREDITS ^

Version 0.34

Copyright © 2002 - 2005, 2008 Robert Kiesling, rkies@cpan.org.

Licensed under the same terms as Perl. Refer to the file, "Artistic," for details.

SEE ALSO ^

perl(1), UnixODBC::DriverConf(3), UnixODBC::BridgeServer(3), tkdm(1), alltypes(1), apifuncs(1), colattributes(1), connectinfo(1), datasources(1), driverinfo(1), sqltables(1), odbcbridge(1),

syntax highlighting: