BridgeServer.pm - ODBC API for network clients.
use UnixODBC::BridgeServer; UnixODBC::BridgeServer::main();
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;
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
$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.
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');
Closes the log file on the remote server. $c -> dm_log_close;
Returns a new connection handle, or undef on error. $cnh = $c -> sql_alloc_connect ($evh);
Returns a new environment handle, or undef on error. $evh = $c -> sql_alloc_env ();
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);
Returns the ODBC API return value. $r = $c -> sql_cancel ($sth);
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);
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; } }
Returns the ODBC API return value. $r = $c -> sql_connect ($cnh, 'Customers', 'joe', length('joe'), 'password', length('password'));
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 );
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);
Returns the ODBC API return value. $r = sql_disconnect ($cnh);
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);
Returns the ODBC API return value. $r = sql_end_tran ($SQL_HANDLE_STMT, $sth, 0);
Returns an ODBC error message. ($r, $sqlstate, $native, $text, $textlen) = $c -> sql_error ($evh, $cnh, $sth, $maxlength);
Returns the ODBC SQL return value $r = $c -> sql_exec_direct ($sth, $query, length ($query));
Returns the ODBC API return value $r = $c -> sql_execute ($sth);
Returns the ODBC API return value. $r = sql_fetch ($sth);
Returns the ODBC API return value. $r = $c -> sql_fetch_scroll ($sth, $SQL_FETCH_NEXT, $row++);
Returns the ODBC API return value. $r = $c -> sql_foreign_keys ($sth, '', 0, '', 0, $table, length ($table), '', 0, '', 0, $foreign_table, length ($foreign_table));
Returns the ODBC API return value. $r = $c -> sql_free_connect ($cnh);
Convenience function to de-allocate an environment handle.
$r = $c -> sql_free_env ($evh);
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);
$r = $c -> sql_free_stmt ($sth, $SQL_CLOSE);
Returns a list of - API return value - Cursor name - Length of cursor name ($r, $cursorname, $length) = $c -> sql_get_cursor_name ($sth, 255);
Returns a list of - API return value - Result text - Result text length ($r, $text, $len) = sql_get_data ($sth, 1, $SQL_C_CHAR, 255);
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);
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);
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)
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);
Returns a list of - API return value - Attribute value - Attribute value length ($r, $dbmsname, $length) = $c -> sql_get_info ($cnh, $SQL_SERVER_NAME, $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);
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);
Returns the ODBC API return value. $r = $c -> sql_more_results ($sth);
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);
Returns a list of - API return value - Number of columns in result set ($r, $ncols) = sql_num_result_columns ($sth);
Returns the ODBC API value. $r = $c -> sql_prepare ($sth, $query, length ($query) );
Return a result set of primary keys. The table name is required.
Returns the ODBC API return value. $r = $c -> sql_procedure_columns ($sth, '', 0, '', 0, '', 0, '', 0);
Returns the ODBC API return value. $r = &UnixODBC::SQLProcedures ($sth, '', 0, '', 0, '', 0);
Returns a list of - API return value - Number of rows in result set ($r, $nrows) = sql_row_count ($sth);
Deprecated in the ODBC standard.
# Write function call info to /tmp/sql.log. $r = $c -> sql_set_connect_option ($cnh, $SQL_OPT_TRACE, $SQL_OPT_TRACE_ON);
Returns the ODBC API return value. $r = $c -> sql_set_cursor_name ($sth, 'cursor', length('cursor'));
Returns the ODBC function return value. $r = sql_set_env_attr ($evh, $SQL_ATTR_ODBC_VERSION, $SQL_OV_ODBC2, 0);
Returns the ODBC API return value. $r = $c -> sql_set_pos ($sth, 1, $SQL_POSITION, $SQL_LOCK_NO_CHANGE);
Deprecated in ODBC 3.0.
Returns the ODBC API return value. $r = sql_special_columns ($sth, $SQL_ROWVER, '', 0, '', 0, 'titles', 6, $SQL_SCOPE_CURROW, 0);
Returns the ODBC API return value. $r = $c -> sql_statistics ($sth, '', 0, '', 0, '', 0, 1, 1);
Returns the ODBC API return value. $r = $c -> sql_table_privileges ($sth, '', 0, '', 0, '', 0);
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"; }
UnixODBC::BridgeServer.pm is part of the UnixODBC package.
Version: 0.25
Written by: Robert Allan Kiesling, rkies@cpan.org.
perl(1), UnixODBC(3), tkdm(1), odbcbridge(1), remotedsn(1), remotetables(1), RPC::PlServer(3), RPC::PlClient(3).
To install UnixODBC, copy and paste the appropriate command in to your terminal.
cpanm
cpanm UnixODBC
CPAN shell
perl -MCPAN -e shell install UnixODBC
For more information on module installation, please visit the detailed CPAN module installation guide.