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)