NAME

   BridgeServer.pm - ODBC API for network clients.

SYNOPSIS

    use UnixODBC::BridgeServer;
    UnixODBC::BridgeServer::main();

DESCRIPTION

UnixODBC::BridgeServer provides an object oriented API for communicating with ODBC driver managers on network systems. A UnixODBC::BridgeServer client is a subclass of RPC::PlClient, and communicates with a UnixODBC::BridgeServer daemon (see "man odbcbridge") on the DBMS server system using Remote Procedure Calls. UnixODBC::BridgeServer provides a network interface to the API in UnixODBC.pm. The UnixODBC manual page describes those functions.

Here is a client that uses the UnixODBC::BridgeServer API.

  #! /usr/bin/perl

  use RPC::PlClient;
  use UnixODBC qw (:all);
  use UnixODBC::BridgeServer;

  #
  # Edit for the Host Address, DSN, TableName, UserName and PassWord of 
  # the remote data source.
  #
  my $HostAddress = '127.0.0.1';
  my $DSN = 'Data_Source_Name';
  my $TableName = 'Table_Name';
  my $UserName = 'User_Name';
  my $PassWord = 'Password';

  # Port address used by client and odbcbridge daemon.

  my $Port = 9999;

  # Maximum field length.  The RPC::PlServer POD documentation describes
  # how to change this.

  my $MaxFieldLength = 65535;

  # ODBC Handles

  my $evh = 0;  # Environment Handle
  my $cnh = 0;  # Connection Handle
  my $sth = 0;  # Statement Handle

  # Return values for sql_get_diag_rec 

  my ($r, $sqlstate, $native, $text, $textlen);

  # SQL Query Text

  my $query = "select \* from $TableName\;";

  # Rows and columns in the result set

  my ($nrows, $ncols);

  # Create a RPC network client object.  This manages the 
  # network connection.

  my $client = 
      eval { RPC::PlClient->new('peeraddr' => $HostAddress,
                          'peerport' => $Port,
                          'application' => 'UnixODBC::BridgeServer',
                          'version' => $UnixODBC::VERSION,
                          'user' => $UserName,
                          'password' => $PassWord) }
     or do {
        print "Failed to make first connection: $@\n";
        exit 1;
     };

  # Create the BridgeClient object.

  my $c = $client -> ClientObject ('BridgeAPI', 'new');

  # Uncomment if you want the Driver Manager to log the ODBC 
  # function calls.  Also uncomment the call to dm_log_close,
  # below.

  # my $ODBCLogFile = '/tmp/sampleclient.log';
  # $c -> dm_log_open ('UnixODBC Bridge Sample Client', $ODBCLogFile);

  # Allocate an environment handle.

  $evh =  $c -> sql_alloc_env ();
  if (defined $evh) { 
      $r = $c -> 
          sql_set_env_attr ($evh, $SQL_ATTR_ODBC_VERSION, $SQL_OV_ODBC2, 0);
  } else {
      ($r, $sqlstate, $native, $text, $textlen) = 
          $c -> sql_get_diag_rec ($SQL_HANDLE_ENV, $evh, 1, 255);
      print "\nsql_alloc_handle: $r, $text, $textlen\n";
      exit 1;
  }

  # Allocate a connection handle.

  $cnh = $c -> sql_alloc_handle ($SQL_HANDLE_DBC, $evh);

  # Connect to the data source.

  $r = $c -> sql_connect ($cnh, $DSN, length($DSN),
                          $UserName, length($UserName), 
                          $PassWord, length($PassWord), 0);
  if ($r != 0) {
      ($r, $sqlstate, $native, $text, $textlen) = 
          $c -> sql_get_diag_rec ($SQL_HANDLE_DBC, $cnh, 1, 255);
      print "\nconnect: $r, $text, $textlen\n";
  }

  # Allocate a statement handle.

  $sth = $c -> sql_alloc_handle ($SQL_HANDLE_STMT, $cnh);
  if (! defined $sth) {
      ($r, $sqlstate, $native, $text, $textlen) = 
          $c -> sql_get_diag_rec ($SQL_HANDLE_DBC, $cnh, 1, 255);
      print "\nsql_alloc_handle sth: $r, $text, $textlen\n";
  }

  # Query the remote DBMS.

  $r = $c -> sql_exec_direct ($sth, $query, length ($query));
  if ($r != 0) {
      ($r, $sqlstate, $native, $text, $textlen) = 
          $c -> sql_get_diag_rec ($SQL_HANDLE_STMT, $sth, 1, 255);
      print "\nsql_exec_direct: $r, $text, $textlen\n";
  }

  # Get the number of columns in the result set.

  ($r, $ncols) = $c -> sql_num_result_columns ($sth);
  if ($r != 0) {
      ($r, $sqlstate, $native, $text, $textlen) = 
          $c -> sql_get_diag_rec ($SQL_HANDLE_STMT, $sth, 1, 255);
      print "\nsql_num_result_columns: $r, $text, $textlen\n";
  }

  # Get the number of rows in the result set.

  ($r, $nrows) = $c -> sql_row_count ($sth);
  if ($r != 0) {
      ($r, $sqlstate, $native, $text, $textlen) = 
          $c -> sql_get_diag_rec ($SQL_HANDLE_STMT, $sth, 1, 255);
      print "\nsql_num_result_columns: $r, $text, $textlen\n";
  }

  # Print the number of rows and columns in the result set.

  print "\n$nrows rows, $ncols columns\n";

  # Fetch each row and each column's contents.

  while (1) {
    $r = $c -> sql_fetch ($sth);
    last if $r == $SQL_NO_DATA;
    foreach my $colno (1..$ncols) {
        ($r, $text, $textlen) = 
            $c -> sql_get_data ($sth, $colno, $SQL_C_CHAR, $MaxFieldLength);
        print "$text\t";
    }
    print "\n";
  }

  # De-allocate the ODBC handles.

  $r = $c -> sql_free_handle ($SQL_HANDLE_STMT, $sth);
  if ($r != 0) {
      ($r, $sqlstate, $native, $text, $textlen) = 
          $c -> sql_get_diag_rec ($SQL_HANDLE_DBC, $cnh, 1, 255);
      print "\nfree_handle sth: $r, $text, $textlen\n";
  }

  $r = $c -> sql_disconnect ($cnh);
  if ($r != 0) {
      ($r, $sqlstate, $native, $text, $textlen) = 
          $c -> sql_get_diag_rec ($SQL_HANDLE_DBC, $cnh, 1, 255);
      print "\nconnect: $r, $text, $textlen\n";
  }

  $r = $c -> sql_free_connect ($cnh);
  if ($r != 0) {
      ($r, $sqlstate, $native, $text, $textlen) = 
          $c -> sql_get_diag_rec ($SQL_HANDLE_ENV, $evh, 1, 255);
      print "\nfree_connect: $r, $text, $textlen\n";
  }

  $r = $c -> sql_free_handle ($SQL_HANDLE_ENV, $evh);
  if ($r != 0) {
      ($r, $sqlstate, $native, $text, $textlen) = 
          $c -> sql_get_diag_rec ($SQL_HANDLE_ENV, $evh, 1, 255);
      print "\nfree_connect: $r, $text, $textlen\n";
  }

  # $c -> dm_log_close;

Application Programming Interface

ODBC Return Values

The ODBC API defines these return values for the status of function calls.

  Perl Variable                   Numeric Value
  -------------                   -------------
  $SQL_NULL_DATA                  -1
  $SQL_DATA_AT_EXEC               -2
  $SQL_SUCCESS                    0
  $SQL_SUCCESS_WITH_INFO          1
  $SQL_NO_DATA                    100
  $SQL_NO_DATA_FOUND              100
  $SQL_ERROR                      -1
  $SQL_INVALID_HANDLE             -2
  $SQL_STILL_EXECUTING            2
  $SQL_NEED_DATA                  99

Methods in the UnixODBC::BridgeServer API

$c is an instance of a UnixODBC client that has established a network connection to a UnixODBC server. The example scripts show how to construct the client object.

dm_log_open (application_name, log_file_name)

  Opens a log file on the remote server.  You must have 
  write privileges in that directory.

  Returns 0;

  $c -> dm_log_open ('ODBC Bridge', '/tmp/odbcbridge.log');

dm_log_close

  Closes the log file on the remote server.

  $c -> dm_log_close;

sql_alloc_connect (environment_handle)

  Returns a new connection handle, or undef on error.

  $cnh = $c -> sql_alloc_connect ($evh);

sql_alloc_env ()

  Returns a new environment handle, or undef on error.

  $evh = $c -> sql_alloc_env ();

sql_alloc_handle (handle_type, parent_handle)

  Returns the new handle, or undef on error.

  # Allocate an environment handle

  $evh = $c -> sql_alloc_handle ($SQL_HANDLE_ENV, $SQL_NULL_HANDLE);

  # Allocate a connection handle

  $cnh = $c -> sql_alloc_handle ($SQL_HANDLE_DBC, $evh);

  # Allocate a statement handle

  $sth = $c -> sql_alloc_handle ($SQL_HANDLE_STMT, $cnh);

sql_cancel (statement_handle)

  Returns the ODBC API return value.

  $r = $c -> sql_cancel ($sth);

sql_col_attribute (statement_handle, column_number, attribute, maxlength)

  Returns a list of
  - SQL return value
  - Text attribute if any
  - Length of text attribute
  - Numeric attribute

  ($r, $text, $textlen, $num) = 
      $c -> sql_col_attribute ($sth, 1, $SQL_COLUMN_NAME, 255);

sql_columns (statement_handle, catalog_name, catalog_name_length, schema_name, schema_name_length, table_name, table_name_length, column_name, column_name_length)

  Returns the ODBC API return value.

  # Retrieve and print all column names for table named $table
  $r = $c -> sql_columns ($sth, '', 0, '', 0, 
                          "$table", length($table), '' 0);
  while (1) {
    $r = $c -> sql_fetch ($sth);
    last if $r == $SQL_NO_DATA;
    if ($r != $SQL_SUCCESS) {
      ($r, $sqlstate, $native, $text, $textlen) = 
        $c -> sql_get_diag_rec ($SQL_HANDLE_STMT, $sth, 1, 255);
      print "[sql_fetch]$text\n";
      return 1;
    } 

    # Column names are the fourth column of the result set.
    ($r, $text, $textlen) = 
      $c -> sql_get_data ($sth, 4, $SQL_C_CHAR, 255);
    last if $r == $SQL_NO_DATA;
    print "$text\n";
    if ($r != $SQL_SUCCESS) {
       ($r, $sqlstate, $native, $text, $textlen) = 
        $c -> sql_get_diag_rec ($SQL_HANDLE_STMT, $sth, 1, 255);
      print "[sql_get_data]$text\n";
      return 1;
    } 
  }

sql_connect (connection_handle, data_source_name, user_name, user_name_length, password, password_length)

  Returns the ODBC API return value.

  $r = $c -> sql_connect ($cnh, 'Customers', 
                          'joe', length('joe'),
                          'password', length('password'));

sql_data_sources (environment_handle, orientation, maximum_dsn_name_length, maximimu_driver_name_length);

  Returns a list of
  - ODBC API return value.
  - DSN name.
  - Length of DSN name text.
  - Name of DBMS Driver for DSN.
  - Length of driver text.

  ($r, $dsnname, $dsnlength, $drivername, $drivernamelength) = 
  $c -> sql_data_sources ( $evh, $SQL_FETCH_FIRST, 
                           $messagelength1, 
                           $messagelength2 );

sql_describe_col (statement_handle, column_number, maxlength)

  Returns a list of 
  - SQL API return value
  - Column name
  - Name length
  - Data type
  - Size
  - Decimal digits
  - Nullable

  ($r, $name, $namelength, $type, $size, $decimal_digits, $nullable) 
    = $c -> sql_describe_col ($sth, 1, 255);

sql_disconnect (connection_handle)

  Returns the ODBC API return value.

  $r = sql_disconnect ($cnh);

sql_drivers (environment_handle, orientation, description_maximum_length, attribute_maximum_length)

  Returns a list of: 
  - SQL API return value
  - Driver description string
  - Driver description string length
  - Attribute description string
  - Attribute description string length

  ($r, $desc, $desc_len, $attr, $attr_len) =
    sql_drivers ($evh, $order, $desc_max_len, $attr_max_len);

sql_end_tran (handle_type, handle, completion_type)

  Returns the ODBC API return value.

  $r = sql_end_tran ($SQL_HANDLE_STMT, $sth, 0);

sql_error (environment_handle, connection_handle, statement_handle, maxlength)

  Returns an ODBC error message.

  ($r, $sqlstate, $native, $text, $textlen) = 
    $c -> sql_error ($evh, $cnh, $sth, $maxlength);

sql_exec_direct (statement_handle, query, query_length)

  Returns the ODBC SQL return value

  $r = $c -> sql_exec_direct ($sth, $query, length ($query));

sql_execute (statement_handle)

  Returns the ODBC API return value

  $r = $c -> sql_execute ($sth);

sql_fetch (statement_handle)

  Returns the ODBC API return value.

  $r = sql_fetch ($sth);

sql_fetch_scroll (statement_handle, orientation, offset);

  Returns the ODBC API return value.

  $r = $c -> sql_fetch_scroll ($sth, $SQL_FETCH_NEXT, $row++);

sql_foreign_keys (statement_handle, catalog_name, catalog_name_length, schema_name, schema_name_length, table_name, table_name_length, foreign_catalog_name, foreign_catalog_name_length, foreign_schema_name, foreign_schema_name_length, foreign_table_name, Iforeign_table_name_length>)

  Returns the ODBC API return value.

  $r = $c -> sql_foreign_keys ($sth, '', 0, '', 0, $table, length ($table),
                               '', 0, '', 0, $foreign_table, 
                               length ($foreign_table));

sql_free_connect (connection_handle)

  Returns the ODBC API return value.

  $r = $c -> sql_free_connect ($cnh);

sql_free_env (handle)

Convenience function to de-allocate an environment handle.

  $r = $c -> sql_free_env ($evh);

sql_free_handle (handle_type, handle)

  Returns the ODBC API return value.

  # Free environment handle

  $r = $c -> sql_free_handle ($SQL_HANDLE_ENV, $evh);

  # Free connection handle

  $r = $c -> sql_free_handle ($SQL_HANDLE_DBC, $cnh);

  # Free statement handle

  $r = $c -> sql_free_handle ($SQL_HANDLE_STMT, $sth);

sql_free_stmt (statement_handle, option)

  $r = $c -> sql_free_stmt ($sth, $SQL_CLOSE);

sql_get_connect_attr (connection_handle, attribute, maxlength)

sql_get_cursor_name (statement_handle, maxlength)

  Returns a list of 
  - API return value
  - Cursor name
  - Length of cursor name

  ($r, $cursorname, $length) = 
     $c -> sql_get_cursor_name ($sth, 255);

sql_get_data (statement_handle, column_number, data_type, maxlength)

  Returns a list of
  - API return value
  - Result text
  - Result text length

  ($r, $text, $len) = sql_get_data ($sth, 1, $SQL_C_CHAR, 255);

sql_get_diag_field (handle_type, handle, field_number, maxlength)

  Returns a list of
  - API return value
  - Server native error
  - ODBC error
  - ODBC error length

  ($r, $native, $text, $textlen) = 
     $c -> sql_get_diag_field ($SQL_HANDLE_STMT, $sth, 1, 255);

sql_get_diag_rec (handle_type, handle, record_number, maxlength)

  Returns a list of: 
  - API return value
  - SQL state
  - DBMS error number
  - Error text
  - Error text length

  If the return value is $SQL_NO_DATA, the remaining list elements
  are empty.

  ($r, $sqlstate, $native, $text, $textlen) = 
    $c -> sql_get_diag_rec ($SQL_HANDLE_ENV, $evh, 1, 255);

sql_get_env_attr (environment_handle, attribute, maxlength)

  Returns a list of: 
  - API return value
  - Attribute value
  - Attribute value length.

  If the return value is not $SQL_SUCCESS, the remaining list 
  elements are empty.

  ($result, $version, $versionlength) = 
    $c -> sql_get_env_attr ($evh, $SQL_ATTR_ODBC_VERSION, $maxlength)

sql_get_functions (connection_handle, function);

  Returns a list of 
  - API return value
  - Non-zero if function is supported, zero if not supported.

  my ($r, $s) = $c -> sql_get_functions ($cnh, $SQL_API_SQLALLOCHANDLESTD);

sql_get_info (connection_handle, attribute, maxlength);

  Returns a list of 
  - API return value
  - Attribute value
  - Attribute value length

  ($r, $dbmsname, $length) = 
     $c -> sql_get_info ($cnh, $SQL_SERVER_NAME, $maxlength)

sql_get_stmt_attr (statement_handle, attribute, maxlength)

  Returns a list of 
  - API return value
  - Attribute value
  - Attribute length

  my ($r, $text, $textlength) = 
    $c -> sql_get_stmt_attr ($sth, $SQL_ATTR_CURSOR_SCROLLABLE, 255);

sql_get_type_info (statement_handle, type)

Returns the ODBC API return value. The result of the query is a result set of the requested types.

  $r = $c -> sql_get_type_info ($sth, $SQL_ALL_TYPES);

sql_more_results (statement_handle)

  Returns the ODBC API return value.

  $r = $c -> sql_more_results ($sth);

sql_native_sql (connection_handle, query, query_length, maxlength)

  Returns a list of 
  - API return value
  - Translated SQL query
  - Length of translated query

  ($r, $nativequery, $length) = 
    $c -> sql_native_sql ($cnh, $query, length ($query), 255);

sql_num_result_columns (statment_handle)

  Returns a list of 
  - API return value
  - Number of columns in result set

  ($r, $ncols) = sql_num_result_columns ($sth);

sql_prepare (statment_handle, query, query_length)

  Returns the ODBC API value.

  $r = $c -> sql_prepare ($sth, $query, length ($query) );

sql_primary_keys (statement_handle, catalog_name, catalog_name_length, schema_name, schema_name_length, table_name, table_name_length)

  Return a result set of primary keys.  The table name is required.

sql_procedure_columns (statement_handle, catalog_name, catalog_name_length, schema_name, schema_name_length, procedure_name, procedure_name_length, column_name, column_name_length);

  Returns the ODBC API return value.

  $r = $c -> sql_procedure_columns ($sth, '', 0, '', 0, '', 0, '', 0);

sql_procedures (statement_handle, catalog_name, catalog_name_length, schema_name, schema_name_length, procedure_name, procedure_name_length);

  Returns the ODBC API return value.

  $r = &UnixODBC::SQLProcedures ($sth, '', 0, '', 0, '', 0);

sql_row_count (statement_handle)

  Returns a list of
  - API return value
  - Number of rows in result set

  ($r, $nrows) = sql_row_count ($sth);

sql_set_connect_attr (connection_handle, attribute, buffer, bufferlength)

  Deprecated in the ODBC standard.

sql_set_connect_option (connection_handle, option, value)

  # Write function call info to /tmp/sql.log.  

  $r = $c -> sql_set_connect_option ($cnh, $SQL_OPT_TRACE, 
                                     $SQL_OPT_TRACE_ON);

sql_set_cursor_name (statement_handle, cursorname, cursor_name_length)

  Returns the ODBC API return value.

  $r = $c -> sql_set_cursor_name ($sth, 'cursor', length('cursor'));

sql_set_env_attr (environment_handle, attribute, value, length_of_value_string)

  Returns the ODBC function return value.

  $r = sql_set_env_attr ($evh, $SQL_ATTR_ODBC_VERSION, $SQL_OV_ODBC2, 0);

sql_set_pos (statement_handle, row, orientation, lock)

  Returns the ODBC API return value.

  $r = $c -> sql_set_pos ($sth, 1, $SQL_POSITION, $SQL_LOCK_NO_CHANGE);

sql_set_scroll_options (statement_handle, concurrency, keyset, rowset)

Deprecated in ODBC 3.0.

sql_set_stmt_attr (statement_handle, attribute, value, length)

sql_special_columns (statement_handle, id_type, catalog_name, catalog_name_length, schema_name, schema_name_length, table_name, table_name_length, scope, nullable)

  Returns the ODBC API return value.

  $r = sql_special_columns ($sth, $SQL_ROWVER, '', 0, '', 0, 'titles', 6,
                            $SQL_SCOPE_CURROW, 0);

sql_statistics (statement_handle, catalog_name, catalog_name_length, schema_name, schema_name_length, table_name, table_name_length, unique, reserved)

  Returns the ODBC API return value.

  $r = $c -> sql_statistics ($sth, '', 0, '', 0, '', 0, 1, 1);

sql_table_privileges (statement_handle, catalog_name, catalog_name_length, schema_name, schema_name_length, table_name, table_name_length)

  Returns the ODBC API return value.

  $r = $c -> sql_table_privileges ($sth, '', 0, '', 0, '', 0);

sql_tables (statement_handle, catalog_name, catalog_name_length, schema_name, schema_name_length, table_name, table_name_length, table_type_name, table_type_name_length)

  Returns SQL API return value.  ODBC Level 3 drivers can specify
  wildcards.  Calls to sql_fetch and sql_get_data return a result
  set of:

  - Catalog name
  - Schema name
  - Table name
  - Table type
  - Remarks

  # Print the names of all tables of a DSN
  $r = sql_tables ($sth, '', 0, '', 0, '', 0, '' 0);
  while (1) {
     r = $c -> sql_fetch ($sth);
     last if $r == $SQL_NO_DATA;
     ($r, $text, $textlen) = 
       $c -> sql_get_data ($sth, 3, $SQL_C_CHAR, 255);
     if ($r != $SQL_SUCCESS) {
       ($r, $sqlstate, $native, $text, $textlen) = 
         $c -> sql_get_diag_rec ($SQL_HANDLE_STMT, $sth, 1, 255);
       print "Error: [sql_get_data]$text\n";
     } 
     print "$text\n";
  }

VERSION INFORMATION AND CREDITS

UnixODBC::BridgeServer.pm is part of the UnixODBC package.

Version: 0.25

Written by: Robert Allan Kiesling, rkies@cpan.org.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)