Win32::ODBC - ODBC API Perl 5 extension
use Win32::ODBC;
$db = new Win32::ODBC( "My DSN" ) || die "Error connecting: " . Win32::ODBC::Error();
if( ! $db->Sql( "SELECT * FROM Foo" ) )
{
while( $db->FetchRow( \%Data ) )
{
$Row++;
print "$Row)\n";
foreach $Column ( keys( %Data ) )
{
print "\t'$Column' = '$Data{$Column}'\n";
}
}
}
else
{
print "Unable to execute query: " . $db->Error() . "\n";
}
$db->Close();
The Win32::ODBC is an interface into the ODBC API. The original API is mirrored as closely as possible so that coders from other languages will be able to quickly prototype ODBC code.
Open DataBase Connectivity (ODBC) is an industry wide standard way to communicate with databases. By using the ODBC API an application can be written that can perform database transactions on any database that has an ODBC driver. Simply stated this means if you write an application using ODBC you can use it to interact with SQL Server, DB2, Oracle, SQL Anywhere, Microsoft Access, and Fox. If your database engine has an ODBC driver for it then your application can interact with it.
The Win32::ODBC extension was designed to run under Win32. It is possible to port to other platforms such as Mac and UNIX. Contact the author for details.
Win32::ODBC requires ODBC 2.5 or higher. ODBC and some Microsoft ODBC drivers can be found on Microsoft's data web site as part of the Microsoft Data Access Components (MDAC) package:
http://www.microsoft.com/data/
Win32::ODBC makes use of a concept of database connections. When you create a Win32::ODBC object it refers to a single connection to a SQL database engine.
Technically the object abstracts a database connection and statement. If you create two Win32::ODBC objects to the same DSN you will have two seperate ODBC database connections to the database engine each with one ODBC statement. Multiple statements per ODBC datbase connection is possible by using cloning.
The first place to check for information on Win32::ODBC is the Roth Consulting web Win32::ODBC site:
http://www.roth.net/perl/odbc/
Information regarding the ODBC API can be found at Microsoft's Microsoft Developer Network (MSDN) online:
http://msdn.microsoft.com/
Roth Consulting also has a news server that hosts an ODBC forum:
There are plenty of books available regarding ODBC. Some that we found useful are listed on the Roth Consulting book page:
http://www.roth.net/books/
There are several web sites that have tutorials on SQL.
There are several alternatives to Win32::ODBC available such as the DataBase Interface (DBI) version called DBD::ODBC. This ODBC Perl extension is available across different platforms such as Mac and UNIX. It is a good tool to use for ODBC access into databases although it lacks some of the functionality that Win32::ODBC has.
http://xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
There are non ODBC database interfaces available as well such as OraPerl and SyPerl. Check a local CPAN site or the official Perl web site:
http://www.perl.com/
Win32::ODBC supports the following functions:
The ODBC environment represents the general ODBC platform. Some environment settings alter how all connections and statements work.
An ODBC connection refers to a a path created between a Perl script and an database.
An ODBC statement or (stmt) is a SQL message that is submitted to the database through an ODBC connection. Examples of statements (also known as queries) are like ``SELECT * FROM Foo'' and ``INSERT INTO Foo SET( Age, Name ) VALUES( 12, 'Joel' )''.
ODBC supports multiple statements per connection. This can be handy since you can have multiple active statements. This allows a script to create a generate a result set with one statment and have another statement make use of the result set from the first one. This is accomplished by cloning a Win32::ODBC connection using the new() function.
A cloned ODBC object is an copy of an existing ODBC connection. The new object
shares the same ODBC connection as it's parent however it has a unique ODBC statement. This is typically used when there is a need to have one query refer to
the result set (or cursor) of another query.
Another reason for cloning stems from some database engines not being able to have multiple connections from the same process or user (such as SQL Server). Cloning allows a script to use the same ODBC connection with multiple ODBC statements.
A cursor represents the current location within a result set. Think about a cursor in a data set as you would think about a cursor on a computer screen. It simply points to the current point of attention. There are different types of cursors that can affect how an application functions. Refer to the section called Cursors Are Our Friends.
Tells ODBC to create a data set that contains table information about the DSN. Use FetchRow() and Data() or DataHash() to retrieve the data.
Note: The Catalog() and Tables() methods are synonymous. There are no differences between the two. They both
exist for backward compatibility.
The passed in values represent:
$Qualifier.........The name of the database. This is sometimes refered to
as the I<qualifier> or I<catalog>.
$Owner.............The owner (or schema) of the database owner.
$TableName.........The name of a particular table.
$TableType.........The type of the table. There are 4 table types:
'TABLE'..............A user table
'SYSTEM TABLE'.......A system table
'VIEW'...............A defined view
'LOCAL TEMPORARY'....A temporary local storage
There may be additional types defined by specific ODBC drivers.
If the method is successful then a result set is created containing a row for each
matching table, view or local temporary storage. Use C<FetchRow()> to procure each
row.
The resulting data set contains the following fields:
REMARKS.........The table's comments
TABLE_CAT.......The database the table resides in
TABLE_NAME......The name of the table
TABLE_SCHEM.....Typically this is the owner of the table
TABLE_TYPE......The type of table (refer to C<$TableType> above)
Note: If any of the parameters are empty strings then it is considered a wildcard and all
Note: The table type requires the prefix and suffix apostrophes therefore 'TABLE' is okay but TABLE is not.
Note: You can specify multiple table types as long as they are seperated by commas.
Example
use Win32::ODBC;
$DSN = "My DSN" unless( $DSN = $ARGV[0] );
print "$DSN:\n";
$db = new Win32::ODBC( $DSN ) || die "Error: " . Win32::ODBC::Error();
if( $db->Catalog( '', '', '', "'TABLE','SYSTEM TABLE'" ) )
{
my %Data;
print "Available tables:\n";
while( $db->FetchRow( \%Data ) )
{
print "\t$Data{TABLE_NAME} is a $Data{TABLE_TYPE} (owned by '$Data{TABLE_SCHEM}')\n";
}
}
$db->Close();
Returns:
TRUE......if a result set is created (even if the result set has no rows).
undef.....if the function failed.
Refer to Tables() and TableList().
Returns the attribute $Attribute on each of the fields @FieldNames in the current record set. If @FieldNames is empty then all fields are assumed.
Returns:
Hash array of attributes.
Configures a Data Source Name (DSN). There are 6 different flavors of DSN configurations that this function can support:
ODBC_ADD_DSN...........Adds a new DSN.
ODBC_MODIFY_DSN........Modifies an existing DSN.
ODBC_REMOVE_DSN........Removes an existing DSN.
ODBC_ADD_SYS_DSN.......Adds a new DSN.
ODBC_MODIFY_SYS_DSN....Modifies an existing DSN.
ODBC_REMOVE_SYS_DSN....Removes an existing DSN.
You must specify the $Driver (which can be retrieved by using DataSources() or Drivers()).
$Attribute1 should be "DSN=xxx" where xxx is the name of the DSN. Other attributes can be any DSN attribute such as:
"UID=Cow"
"PWD=Moo"
"Description=My little bitty Data Source Name"
Notes:
If you use ODBC_ADD_DSN or ODBC_ADD_SYS_DSN, then you must include at least the "DSN=xxx" and the location of the database (ie. for MS Access databases you must
specify the Database Qualifier: "DBQ=c:\\...\\MyDatabase.mdb").
If you use ODBC_MODIFY_DSN or ODBC_MODIFY_SYS_DSN, then you need only to specify the the "DNS=xxx"
attribute. Any other attribute you include will be changed to what you
specify.
If you use ODBC_REMOVE_DSN or ODBC_REMOVE_DSN, you only need to specify the "DSN=xxx"
attribute.
Example:
In this example notice that we are creating a system DSN. The DSN will make
use of the Microsoft Access database. The driver "Microsoft Access Driver (*.mdb)" was obtained by a call to Drivers().
use Win32::ODBC;
$Result = Win32::ODBC::ConfigDSN( ODBC_ADD_SYS_DSN, "Microsoft Access Driver (*.mdb)", ("DSN=My DSN", "Description=The Win32 ODBC Test DSN for Perl", "DBQ=c:\\temp\\MyData.mdb", "DEFAULTDIR=c:\\temp", "UID=MyUserId", "PWD=MyPassword" ));
Returns:
TRUE.....successful
undef....failure
Returns the connection number associated with the ODBC connection. The connection number is used internally and has no real practical use other than identifying a unique Win32::ODBC object.
Note: Do not rely on the connection number as it's format may change in future versions.
Example:
use Win32::ODBC;
$db = new Win32::ODBC( "My DSN" ) || die "Error: " . Win32::ODBC::Error();
$ConNum = $db->Connection();
Returns:
Number identifying an ODBC connection
Closes the ODBC connection. Once a connection is no longer needed it should be closed. Failure to close open database connections could prevent others from accessing data. Additionally each connection takes up memory so it is wise to promptly close all unnecessary connections.
Example:
use Win32::ODBC;
$db = new Win32::ODBC( "My DSN" ) || die "Error: " . Win32::ODBC::Error();
$db->Close();
Returns:
Nothing
Returns the contents of column $Column or all of the columns in the current row (if nothing is specified). The
parameters passed in can be either a column name string or a numerical
column number.
Note: This function exists for backward compatibility. The prefered way of retrieving column values is using DataHash() or FetchRow().
Notice that in this example the first parameter passed into Data() indicates a column number (in this case column number 5). The second
parameter is a string so it requests the value of the column with the name
of "UserAge".
Example:
use Win32::ODBC;
$db = new Win32::ODBC( "My DSN" ) || die "Error: " . Win32::ODBC::Error();
if( ! $db->Sql( "SELECT * FROM Foo" ) )
{
while( $db->FetchRow() )
{
my( $Name, $Age ) = $db->Data( 5, 'UserAge' );
}
}
$db->Close();
Returns:
The value of the specified column(s).
Refer to DataHash() and FetchRow().
Returns the contents for $Field1, $Field2, ... or the entire row (if nothing is passed in) as a hash consisting of column
names (as keys) and column values (as key values):
ColumnName => ColumnData
If parameters are passed in they can either represent the name of a field or the field's column number.
Note: If you want to specify a column name that consists only of numbers then
pass in a text string with the numeric name such as "123". Otherwise passing in the numeric literal 123 will attempt to retrieve the value for column number 123.
Note: You can intermix column names and column numbers.
Example:
use Win32::ODBC;
$db = new Win32::ODBC( "My DSN" ) || die "Error: " . Win32::ODBC::Error();
if( ! $db->Sql( "SELECT * FROM Foo" ) )
{
while( $db->FetchRow() )
{
my( %Data ) = $db->DataHash( "name", 3, "Age");
}
}
$db->Close();
Returns
Hash array with keys = Field Names and values = FieldData.
Refer to Data() and FetchRow().
Returns a hash of Data Sources Names (keys) and the ODBC driver (values) they use. They are returned in the form of:
$ArrayName{'DSN'}=Driver
Where DSN is the Data Source Name and Driver is the ODBC Driver used.
Example:
use Win32::ODBC;
%DSNList= Win32::ODBC::DataSources();
print "DSN's available from this user account:\n";
map { print "DSN: $_\nODBC Driver: $DataSources{$_}\n\n" } (keys %DSNList );
Returns:
Hash array.
Returns a hash of ODBC Drivers and thier attributes. They are returned in the form of:
$ArrayName{'DRIVER'}=Attrib1;Attrib2;Attrib3;...
Where DRIVER is the ODBC Driver Name and AttribX are the driver defined attributes.
Example:
use Win32::ODBC;
%DriverList = Win32::ODBC::Drivers();
print "The following ODBC drivers are installed:\n";
foreach $Driver ( keys( %DriverList ) )
{
print "$Driver\n";
print "\tAttributes:\n";
map { print "\t\t$_\n" } ( split( /;/, $DriverList{$Driver} ) );
}
B<Returns:>
Hash array of ODBC driver names (keys) and driver attributes (values).
item= DropCursor( [$CloseType] )
Drops the cursor associated with the ODBC object forcing the cursor to be
deallocated. This overrides SetStmtCloseType() but the ODBC object does not lose the StmtCloseType setting.
$CloseType can be any valid SmtpCloseType and will perform a close on the ODBC statement using the specified close
type.
Possible close types:
SQL_DONT_CLOSE.....Do not ever drop (aka close) the cursor.
The script must manually close it.
SQL_DROP...........Drop the cursor immediately.
This will lose it's result set. I<This has been depreciated in ODBC 3.0>.
SQL_CLOSE..........Close the cursor (and loose results).
The same cursor can later be reopened with another query.
SQL_UNBIND.........All bound parameters are lost.
SQL_RESET_PARAMS...All bound parameters are reset.
For all practical purposes this is the same as C<SQL_UNBIND>.
This can be useful if you need to
Example:
use Win32::ODBC;
$db = new Win32::ODBC( "My DSN" ) || die "Error: " . Win32::ODBC::Error();
$db->SetStmtCloseType( SQL_DONT_CLOSE );
if( ! $db->Sql( "SELECT * FROM Foo" ) )
{
while( $db->FetchRow( \%Data ) )
{
map { print "$Data{$_}\n" ( keys( %Data ) );
}
# Force the cursor to drop before the next SQL query
$db->DropCursor( SQL_DROP );
$db->Sql( "INSERT INTO Foo SET( Age, Name ) VALUES( 32, 'Joel' )" );
}
$db->Close();
Returns:
TRUE if successful.
undef if not successful.
Refer to GetStmtCloseType() and SetStmtCloseType().
Returns the last encountered error. If an optional array reference is passed in then the array is populated with all error messages produced by an ODBC call. Each element in the array will be a hash with keys for the error number, description and SQL State.
The returned value is context dependent. If it is called in an array context a 3 element array is returned:
( $ErrorNumber, $ErrorText, $ConnectionNumber )
if called in a scalar context a string is returned:
"[Error Number] [Connection Number] [Error Text] [SQLState]"
Note: A connection number may be missing if the this was called as a function.
Note: If an array reference is passed in the contents of the array will be destroyed even if there are no error messages.
Note: This is both a function and a method.
Example 1: called as a function:
use Win32::ODBC;
$db = new Win32::ODBC( "My DSN" ) || die "Could not connect: " . Win32::ODBC::Error();
$db->Close;
Example 2: called as a method:
use Win32::ODBC;
$db = new Win32::ODBC( "My DSN" ) || die "Could not connect: " . Win32::ODBC::Error();
if( ! $db->Sql( "SELECT * FROM Foo" ) )
{
print "There was a problem with the query: " . $db->Error() . "\n";
}
$db->Close();
Example 3: retrieving all error messages:
use Win32::ODBC;
$db = new Win32::ODBC( "My DSN" ) || die "Could not connect: " . Win32::ODBC::Error();
if( ! $db->Sql( "SELECT * FROM Foo" ) )
{
my @ErrorList;
$db->Error( \@ErrorList );
foreach my $ErrorMessage ( @ErrorList )
{
print "SQLState: $ErrorMessage->{SQLState}\n";
print "ErrorNum: $ErrorMessage->{Number}\n";
print "Text: $ErrroMessage->{Text}\n\n";
}
}
$db->Close();
Returns:
Array or String.
Retrieves the next record from the keyset.
If an optional hash reference is passed in as the first parameter then it will be populated with the contents of the row (keys are column names and column values are the hash's values). This eliminates the need to call Data() or DataHash().
The optional parameters $Row and $Type are applicable only if the ODBC driver supports SQLExtendedFetch(). Otherwise they are ignored. $Row refers to how far into the key set to move the cursor. Refer to
documentation on SQLExtendedFetch()
for more information regarding these two parameters.
Note: The method will call either SQLFetch() or SQLExtendedFetch() depending upon what the ODBC driver supports.
Example: using FetchRow() in the classic way:
use Win32::ODBC;
$db = new Win32::ODBC( "My DSN" ) || die "Error: " . Win32::ODBC::Error();
if( ! $db->Sql( "SELECT * FROM Foo" ) )
{
while( $db->FetchRow() )
{
my %Data = $db->DataHash();
map { print "$Data{$_}\n" ( keys( %Data ) );
}
}
$db->Close();
Example: using FetchRow() to fetch the row and data:
use Win32::ODBC;
$db = new Win32::ODBC( "My DSN" ) || die "Error: " . Win32::ODBC::Error();
if( ! $db->Sql( "SELECT * FROM Foo" ) )
{
my %Data;
while( $db->FetchRow( \%Data ) )
{
map { print "$Data{$_}\n" ( keys( %Data ) );
}
}
$db->Close();
Returns:
TRUE....Retrieved the next record.
undef...Either an error occured or there are no more records left to read.
=head2 FieldNames()
Returns an array of fieldnames found in the current data set. There is no guarantee on order.
Example:
use Win32::ODBC;
$db = new Win32::ODBC( "My DSN" ) || die "Error: " . Win32::ODBC::Error();
if( ! $db->Sql( "SELECT * FROM Foo" ) )
{
while( $db->FetchRow() )
{
my @Fields = $db->FieldNames();
print "Fields: " . join( ", ", @Fields ) . "\n";
}
}
$db->Close();
Returns:
Array of field names
Returns a flag indicating whether a specified function is supported by the ODBC driver.
Note: This method only works with ODBC 3.0 and higher.
Example:
use Win32::ODBC;
$db = new Win32::ODBC( "My DSN" ) || die "Can not connect: " . Win32::ODBC::Error() . "\n";
if( ! $db->FunctionExists( SQL_API_SQLEXTENDEDFETCH ) )
{
print "This ODBC driver does not support SQLExtendedFetch()\n";
}
$db->Close();
Result:
TRUE if the function is supported
undef if the function is not supported
Refer to GetFunctions().
This method is the ODBC 3.x equivilent to GetConnectOption(). Either methods can be used. Refer to GetConnectOption() for details.
Refer to GetConnectOption(), SetConnectOption(), SetConnectAttr().
Returns an array of connection numbers showing what connections are
currently open. A connection number is an id that uniquely references a Win32::ODBC object. Do not rely on the format of these connection numbers as they may
change in future versions.
Connection numbers do not have any intrinsic value.
Example:
use Win32::ODBC;
$db1 = new Win32::ODBC( "My DSN1" ) || die "Error: " . Win32::ODBC::Error();
$db2 = new Win32::ODBC( "My DSN2" ) || die "Error: " . Win32::ODBC::Error();
$db3 = new Win32::ODBC( "My DSN3" ) || die "Error: " . Win32::ODBC::Error();
@ConnectionList = Win32::ODBC::GetConnections();
print "The following ODBC objects are currently running:\n";
map { print "\t$_" } ( @ConnectionList );
$db3->Close();
$db2->Close();
$db1->Close();
Returns:
Array of unique connection numbers
Returns the value of the connection option specified by $Option. Refer to ODBC documentation for more information on the options and
values (online at http://msdn.microsoft.com/library/psdk/dasdk/odch4zn6.htm
).
Example:
use Win32::ODBC;
$db = new Win32::ODBC( "My DSN" ) || die "Error: " . Win32::ODBC::Error();
$Timeout = $db->GetConnectOption( SQL_ATTR_LOGIN_TIMEOUT );
print "The current login timeout value is $Timeout seconds.\n";
$db->Close();
Returns:
String or scalar depending upon the option specified.
Refer to SetConnectOption().
Returns the name of the current cursor. Every cursor has a name. If your script does not name the cursor with SetCursorName() then the ODBC driver or manager will automatically name it for you.
Example:
use Win32::ODBC;
$db = new Win32::ODBC( "My DSN" ) || die "Error: " . Win32::ODBC::Error();
if( ! $db->Sql( "SELECT * FROM Foo" ) )
{
my $Name = $db->GetCursorName();
print "The current cursor is called $Name\n";
}
$db->Close();
Returns:
String or undef
Refer to SetCursorName().
Retrieves the current row from the dataset.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
returns: Array of field data where the first element is either: ...TRUE....NOT successful. ...FALSE...successful
Returns the configuration for the specified DSN. If no DSN is specified then the current connection is used.
The returning associative array consists of:
'DSN keyword' => keyword value
Example 1: Calling as a function:
use Win32::ODBC;
%DataSources = Win32::ODBC::DataSources();
print "DSN's available from this user account:\n";
foreach $DSN ( keys( %DataSources ) )
{
my %Config = Win32::ODBC::GetDSN( $DSN );
print "$DSN\n ODBC Driver: $DataSources{$DSN}\n";
print " Configuration:\n";
foreach my $Attrib ( keys( %Config ) )
{
print " $Attrib => '$Config{$Attrib}'\n";
}
print "\n";
}
Example 2: Calling as a method:
use Win32::ODBC;
$DSN = "My DSN" unless( $DSN = $ARGV[0] );
print "$DSN:\n";
$db = new Win32::ODBC( $DSN ) || die "Can not connect: " . Win32::ODBC::Error() . "\n";
# First report the config of the DSN...
%Config = Win32::ODBC::GetDSN( $DSN );
print " Configuration:\n";
foreach $Attrib ( keys( %Config ) )
{
print " $Attrib => '$Config{$Attrib}'\n";
}
# Next report what the ODBC driver reports as the DSN...
%Config = $db->GetDSN( $DSN );
foreach $Attrib ( keys( %Config ) )
{
print " $Attrib => '$Config{$Attrib}'\n";
}
$db->Close();
Returns:
Associative array.
Refer to DataSources() and Drivers().
Returns a hash of flags indicating the ODBC Drivers ability to support specified functions. If no functions are specifed then a large hash is returned containing all possible functions and their values.
$FunctionN must be in the form of an ODBC API constant like:
SQL_API_SQLTRANSACT
The returned array will contain the results like:
SQL_API_SQLTRANSACT => Flag value
Example:
use Win32::ODBC;
$db = new Win32::ODBC( "My DSN") || die "Can not connect: " . Win32::ODBC::Error() . "\n";
$Functions = $db->GetFunctions(SQL_API_SQLTRANSACT, SQL_API_SQLSETCONNECTOPTION );
print "Connection can set options.\n" if( $Functions{SQL_API_SQLSETCONNECTOPTION} );
print "Connection can manualy perform transactions.\n" if( $Functions{SQL_API_SQLTRANSACT} );
$db->Close();
Returns:
Hash of functions and flags
Returns the value of the particular option specified. Information regarding the various GetInfo() options can be found in any good ODBC reference or online at Microsoft's MSDN ( http://msdn.microsoft.com/library/psdk/dasdk/odch5fu7.htm )
Example:
use Win32::ODBC;
$db = new Win32::ODBC( "My DSN" ) || die "Can not connect: " . Win32::ODBC::Error() . "\n";
$Strings = $db->GetInfo( SQL_STRING_FUNCTIONS );
if( $Strings & SQL_FN_STR_SUBSTRING )
{
print " SubString() function is supported.\n";
}
else
{
print " SubString() function is not supported.\n";
}
if( "Y" eq $db->GetInfo( SQL_DATA_SOURCE_READ_ONLY ) )
{
print " The database is set to READ-ONLY.\n";
}
else
{
print " The database is set to READ/WRITE.\n";
}
$db->Close();
Returns: Scalar or string
This will return the data transfer buffer size limit. Refer to the section called Data Transfer Buffers.
Example:
use Win32::ODBC;
$db = new Win32::ODBC( "My DSN" ) || die "Can not connect: " . Win32::ODBC::Error() . "\n";
$Size = $db->GetMaxBufSize();
if( 20480 <= $Size )
{
$Size = $db->SetMaxBufSize( 20480 );
}
if( ! $db->Sql( "SELECT * FROM Foo" ) )
{
while( $db->Fetch( \%Data ) )
{
map { print "'$Data{$_}' "; } ( keys( %Data ) );
print "\n";
}
}
$db->Close();
Result:
New maximum trasfer buffer size
Refer to SetMaxBufSize() and the Data Transfer Buffers section.
This returns the SQL State as reported by ODBC. The SQL state is a code that the ODBC Manager or ODBC Driver returns after the execution of an SQL function. This is helpful for debugging purposes.
Returns:
String representing the SQL State.
Refer to Error(), GetErrors() and GetMessages().
This method is the ODBC 3.x equivilent to GetStmtOption(). Either methods can be used. Refer to GetStmtOption() for details.
Refer to GetStmtOption(), SetStmtAttr(), SetStmtOption().
Returns the type of closure that will be used everytime the statment is
freed. The return type is any one of the valid SmtpCloseType types:
Possible close types:
SQL_DONT_CLOSE.....Do not ever drop (aka close) the cursor.
The script must manually close it.
SQL_DROP...........Drop the cursor immediately.
This will lose it's result set. I<This has been depreciated in ODBC 3.0>.
SQL_CLOSE..........Close the cursor (and loose results).
The same cursor can later be reopened with another query.
SQL_UNBIND.........All bound parameters are lost.
SQL_RESET_PARAMS...All bound parameters are reset.
For all practical purposes this is the same as C<SQL_UNBIND>.
Example:
use Win32::ODBC;
$db = new Win32::ODBC( "My DSN" ) || die "Error: " . Win32::ODBC::Error();
$OrigCloseType = $db->GetStmtCloseType();
$db->SetStmtCloseType( SQL_DONT_CLOSE );
if( ! $db->Sql( "SELECT * FROM Foo" ) )
{
while( $db->FetchRow( \%Data ) )
{
map { print "$Data{$_}\n" ( keys( %Data ) );
}
# Force the cursor to drop before the next SQL query
$db->DropCursor( SQL_DROP );
$db->SetStmtCloseType( $OrigCloseType );
$db->Sql( "INSERT INTO Foo SET( Age, Name ) VALUES( 32, 'Joel' )" );
}
$db->Close();
Returns:
String indicating which close type will be used.
Refer to DropCursor() and SetStmtCloseType().
Returns the value of a statement option specified by $Option. Refer to ODBC documentation for more information on the options and
values (online at http://msdn.microsoft.com/library/psdk/dasdk/odch77jm.htm
).
Example:
use Win32::ODBC;
$db = new Win32::ODBC( "My DSN" ) || die "Error: " . Win32::ODBC::Error();
if( 100 > $db->GetStmtOption( SQL_ROWSET_SIZE ) )
{
# Set the rows size to 100
$db->SetStmtOption( SQL_ROWSET_SIZE, 100 );
}
if( ! $db->Sql( "SELECT * FROM Foo" ) )
{
while( $db->FetchRow( \%Data ) )
{
map { print "'$Data{$_}' "; } ( keys( %Data ) );
}
}
$db->Close();
Returns:
String or numeric value depending upon the option specified.
Refer to GetStmtOption(), SetStmtAttr(), GetStmtAttr().
This will report whether or not there is data yet to be retrieved from the query. This can occur if the query results in multiple result sets as in:
SELECT * FROM Foo SELECT * FROM Bar
If there are multiple result sets (or a possibility of multiple result sets) it is a good idea to call MoreResults() to determine whether or not all results sets have been processed.
Note: Not all ODBC drivers support multiple result sets and/or MoreResults().
Example:
use Win32::ODBC;
$db = new Win32::ODBC( "My DSN" ) || die "Error: " . Win32::ODBC::Error();
if( ! $db->Sql( "SELECT * FROM Foo SELECT * FROM Bar" ) )
{
do
{
while( $db->FetchRow( \%Data ) )
{
map { print "'$Data{$_}' "; } ( keys( %Data ) );
}
} while( $db->MoreResults() );
}
$db->Close();
Returns:
TRUE if there is more data
undef if there is no more data
Creates a new ODBC connection based on $DSN. If you specify an already existing ODBC object that object will be a clone of $ODBC_Object.
You can specify SQL Connect Options that are implimented before the actual connection to the DSN takes place. These option/values are the same specified in Get/SetConnectOption() (see below) and are defined in the ODBC API specs.
DSN-less connections use the new() function to specify the DNS
information. This is done by passing in a full connection string into the
new() function. The string consists of a DRIVER=xxxxxx
name/value pair plus any additional attributes all delimited by semicolons.
as in:
$db = new Win32::ODBC( "Driver=SQL Server;Database=MyDatabase;Server=MyServer;Trusted_Connection=Yes" );
The driver (``SQL Server'') is one of the key names returned by a call to Win32::ODBC::Drivers(). The other keywords are found in either the documentation for the database or can be found in the Registry. Create a temporary system DSN and look into the Registry key: HKEY_LOCAL_MACHINE\Software\ODBC\ODBC.INI\DSN_NAME Where DSN_NAME is the name of the temporary DSN you created. In this key there will be a series of keywords. You will want to specify the keywords with their appropriate values. WITH THE EXCEPTION OF THE DRIVER KEYWORD. In the previous paragraph I described the driver keyword.
returns: Undef if it failed.
For UPDATE, INSERT, and DELETE statements the returned value is the number of rows affected by the request or -1 if the number of affected rows is not available.
Note: This function is not supported by all ODBC drivers! Some drivers do support this but not for all statements (eg. it is supported for UPDATE, INSERT and DELETE commands but not for the SELECT command).
Note: Many data sources cannot return the number of rows in a result set before fetching them; for maximum interoperability, applications should not rely on this behavior.
Returns:
Number of affected rows or -1 if not supported by driver in the current context.
This will execute the $Sql command and dump to the screen info about it.
This is used primarily for debugging.
returns: Nothing
Sets the value of the specified connect $Option. Refer to ODBC documentation
for more information on the options and values.
returns: TRUE....change was successful
FALSE...change was not successful
Sets the name of the current cursor.
returns: TRUE....successful
FALSE...not successful
Moves the cursor to the row $Row within the current keyset (not the current
data/result set).
returns: TRUE....successful
FALSE...not successful
This will set the MaxBufSize for a particular connection.
This will most likely never be needed but...
The amount of memory that is allocated to retrieve a records field data
is dynamic and changes when it need to be larger. I found that a memo
field in an MS Access database ended up requesting 4 Gig of space. This was
a bit much so there is an imposed limit (2,147,483,647 bytes) that can be
allocated for data retrieval. Since it is possible that someone has a
database with field data greater than 10240 you can use this function to
increase the limit up to a ceiling of 2,147,483,647 (recompile if you need
more).
returns: Max number of bytes.
This will set a particular hstmt close type for the connection. This is
the same as ODBCFreeStmt(hstmt, $Type).
By default, the currect object's connection will be used. If $Connection is
a valid connection number, then it will be used.
Types:
SQL_CLOSE
SQL_DROP
SQL_UNBIND
SQL_RESET_PARAMS
Returns the newly set type.
returns: String.
Sets the value of the specified statement $Option. Refer to ODBC
documentation for more information on the options and values.
returns: TRUE.....change was successful.
FALSE....change was not successful.
=head2 ShutDown()
This will close the ODBC connection and dump to the screen info about it.
This is used primarily for debugging.
returns: Nothing
Executes the SQL command $SQLString on a particular connection.
returns: Error number if it failed
Returns the catalog of tables that are availabe in the DSN.
If you do not know what the parameters are just leave them "".
returns: Array of table names.
Forces the ODBC connection to perform a Rollback or Commit transaction.
$Type may be:
SQL_COMMIT
SQL_ROLLBACK
*** NOTE: This only works with ODBC Drivers that support transactions.
Your Driver supports it if TRUE is returned from:
$O->GetFunctions($O->SQL_API_SQLTRANSACT)[1]
(see GetFunctions for more details)
returns: TRUE....success.
FALSE...failure.
If an ODBC driver supports asynchronous execution Perl can start a SQL query and while it is processing the Perl script can continue processing other code.
It is up to the Perl script to poll the Win32::ODBC connection to see if the query has finished processing. Once the query has finished then the Perl script can continue fetching rows and processing the columns as normal.
Currently asynchronous support exists for:
Not all ODBC drivers support asynchronous execution. For those drivers that do they support one of two different types: connection or statement based. Use the GetInfo() method to determine which one a given driver supports. This code sample demonstrates how to set the driver for :
$Result = $db->GetInfo( SQL_ASYNC_MODE );
if( SQL_AM_NONE == $Result )
{
# No support for asynchronous mode
$AsynchMode = 0;
}
if( SQL_AM_CONNECTION == $Result )
{
# Set the driver for asynch mode
$AsynchMode = $db->SetConnectAttr( SQL_ATTR_ASYNC_ENABLE, SQL_ASYNC_ENABLE_ON );
}
elsif( SQL_AM_STATEMENT == $Result )
{
$AsynchMode = $db->SetStmtAttr( SQL_ATTR_ASYNC_ENABLE, SQL_ASYNC_ENABLE_ON );
}
print "Asynchronous support is " . ( ( $AsynchMode )? "active":"disabled" ) . "\n";
Note: You GetInfo( SQL_ASYNC_MODE ) is only supported in ODBC 3.0 when the ODBC level has been set to 3.0 ( by
calling Win32::ODBC::ODBCLevel( SQL_OV_ODBC3 ) ).
For drivers which support connection based asynch execution (eg. SQL
Server) the
SQL_ATTR_ASYNC_ENABLE connection attribute must be turned on:
$db->SetConnectOption( SQL_ASYNC_ENABLE, SQL_ASYNC_ENABLE_ON );
...or if the ODBC 3.0 level has been set:
$db->SetConnectAttr( SQL_ATTR_ASYNC_ENABLE, SQL_ASYNC_ENABLE_ON );
If the driver only supports statement based asynch execution set the statement option:
$db->SetStmtOption( SQL_ASYNC_ENABLE, SQL_ASYNC_ENABLE_ON );
...or if the ODBC 3.0 level has been set:
$db->SetStmtAttr( SQL_ATTR_ASYNC_ENABLE, SQL_ASYNC_ENABLE_ON );
Once asynch execution has been set the script executes a SQL statement like
normal. If the Sql() method returns nothing then the query is being executed. From here the
script processes code or sleeps occasionally calling
$db-Ready()> to see if the query has finished. If $db-Ready()> returns a TRUE value then normal processing can continue such as calling
$db-FetchRow()>. If $db-Ready()> returns a FALSE value then the query is still processing. If there is a need to cancel the
executing query the script can call $db-Cancel()>. If the query is successfully canceled then it
returns TRUE.
Example:
use Win32::ODBC;
Win32::ODBC::ODBCLevel( SQL_OV_ODBC3 );
$DSN = "Internet Services";
$db = new Win32::ODBC( "My DSN" ) || die "Can not connect: " . Win32::ODBC::Error() . "\n";
$Sql = "SELECT DISTINCT ClientHost AS IP From HTTPConnections";
$db->SetConnectAttr( SQL_ATTR_ASYNC_ENABLE, SQL_ASYNC_ENABLE_ON );
if( ! $db->Sql( $Sql ) )
{
while( ! $db->Ready() )
{
$iCount++;
print " ...waiting for query to end ($iCount).\n";
if( 10000 < $iCount )
{
print "Canceling the query: " . $db->Cancel() . "\n";
last;
}
}
$iCount = 0;
while( $db->FetchRow( \%Data ) )
{
$iCount++;
print "$iCount) $Data{'IP'}\n";
}
}
else
{
print "Error: " . $db->Error() . "\n";
}
print "Finished!\n";
When data is submitted to or fetched from a database engine it is stored in a buffer. Depending upon the data type these buffers can become quite large. It is concievable that a data type may need to allocate more memory than a computer has.
For example a Microsoft Access database may have a memo field that requires 200M of buffer space to successfully transfer data. If the computer only has 128M of memory then there is no way to allocate this much memory. Typically a computer would allocate this amount by using paging memory, however this leads to disk thrashing and significantly impacts system performance.
The way around this is to limit the size that a transfer buffer can be. If the buffer is too small to successfully transfer data then it will be transfered in multiple passes. This may result in a time penalty therefore it may be necessary to increase the data transfer buffer size.
The current maximum buffer size can be discovered with a call to GetMaxBufSize. The maximum data transfer buffer size can be changed with a call to SetMaxBufSize(). This buffer size can be changed at any time however the buffer size limit is really only applied after a call that creates a result set such as Sql() and <Catalog()>.
Think of this... you submit a query to a SQL server over a very slow
internet connection. Every time you perform a $db-FetchRow()> you are actually sending a request for the next
row across the net to the server. The server then formats the data and
sends it to your machine.
Simple eh?
This is how a normal request operates. Here is something fun to try--add up the time it takes to do the following:
Wow, that adds up to quite a bit of time. But wait, you mean that this happens for every call to FetchRow()? Wow that is one big old chunk of overhead!
So now you go and ponder things only to come back some time later and suggest...``Hey, what if we were to do a FetchRow() but ask the server to return blocks of 200 records instead of one at a time. That would cut the overhead out by roughly 200 times, right? Yes, now you are starting to understand the rational behind cursors!
When you submit a query the server generates a result set. This result set consists of all records that match your query. Normally you fetch a record one at a time. If you were to collect several records in one fetch and locally cache them in some buffer somewhere you would be creating a cursor. This cursor you created is only a subset of the entire result set produced by your query. This subset is known as a rowset, that is, a set of rows from your result set. Kinda' obvious, eh?
Okay, so let's say that you created a cursor consisting of 200 records. You have probably already figured this next tidbit out but just for the sake of completion...the cursor's rowset size is 200. Obvious? Good.
Now that you have a cursor with a rowset size of 200 you can look at each of of the 200 rows without ever having to talk to the server. Since a program can only analyze one row at a time there exists the concept of the current row. Another obvious definition.
The server does not know what you did with the rows that it sent to you. Therefore the server is unaware that you have a cursor. It is up to you to manage your little cursor of rows. This means that once you have examined all 200 rows and need to look at the next 200 you request the server to send you the next batch of rows. Once again you cache them locally (probably overwriting your current cached rowset). Now you have new rowset of 200 rows.
This is known as a static cursor. You have cached the rows and they do not change until you overwrite them with the next rowset.
Let's say that you have collected your rowset of 200 records. Each row represents one of 200 seats in a concert hall. Your program needs to monitor when one of the rows changes. This way customers can ask you about a specific seat and you simply check for the particular row number representing the seat. If another ticket agent has already reserved the seat you need your cached rows to know about this.
This type of cursor is a dynamic cursor. This type of cursor requires that the ODBC driver and the server talk with each other quite a often so that the server can inform the driver that the cursor needs to be updated. If there is a need to update then the affected records in the cursor are replaced.
There is a hybrid of the two types: static and dynamic. This is a keyset driven cursor. In short it saves a series of keys that represent records. The keys in your locally cached cursor are updated but the data in the records are not (only the keys are updated). If your application sees that the key has changed then you need to request the new record from the server.
A Perl Win32::ODBC object really represents a connection to the datasource. It represents a connection (hDbc), not a statement (hStmt). A statement is allocated and destroyed for every SQL query submitted. Because of this all statement options are dropped every time a call to Sql() is executed with the exception of calling Sql() on a prepared statement. For example to set the rowset size on a statement a script must do it AFTER the call to Sql() as in:
if( ! $db->Sql( "My DSN" ) )
{
$db->SetStmtOption( SQL_ROWSET_SIZE, 200 );
while( $db->FetchRow() )
{
...process code...
}
}
You can set the cusor close type to be SQL_DONT_DROP. This will prevent the cursor from being dropped between calls to
<Sql()>. For example:
$db->SetStmtCloseType( SQL_DONT_DROP );
$db->SetStmtOption( SQL_ROWSET_SIZE, 200 );
if( ! $db->Sql( "My DSN" ) )
{
while( $db->FetchRow() )
{
...process code...
}
}
if( ! $db->Sql( "My Other DSN" ) )
{
while( $db->FetchRow() )
{
...process code...
}
}
You can specify different types of cursors. This is done before preparing or submitting a SQL query and is performed by calling SetStmtOption() with a statement type of SQL_CURSOR_TYPE. Valid statement types are:
SQL_CURSOR_FORWARD_ONLY.............The cursor only scrolls forward.
SQL_CURSOR_STATIC...................The data in the result set is static.
SQL_CURSOR_DYNAMIC..................The driver only saves and uses the keys for the
rows in the rowset.
SQL_CURSOR_KEYSET_DRIVEN............The driver saves and uses the keys for
the number of rows specified in the
SQL_ATTR_KEYSET_SIZE statement attribute.
By default ODBC connections use SQL_CURSOR_FORWARD_ONLY.
Note: The cursor type can not be specified after the SQL statement has been prepared.
If the specified cursor type is not supported by the data source, the
driver substitutes a different cursor type and returns SQLState 01S02 (Option value changed). For a mixed or dynamic cursor, the driver
substitutes, in order, a keyset-driven or static cursor. For a
keyset-driven cursor, the driver substitutes a static cursor.
Example:
$db = new Win32::ODBC( "MyDSN" ) || die "Could not connect: " . Win32::ODBC::Error() . "\n";
$db->SetStmtOption( SQL_CURSOR_TYPE, SQL_CURSOR_STATIC );
if( ! $db->Sql( "select * from foo" ) )
{
...process code...
}
You can change a cursor's rowset size:
$Size = 200;
$db->SetStmtOption( SQL_ROWSET_SIZE, $Size );
Just as with other statement options this must be performed before the
query or prepare. If you already have an open cursor then you should first
close it. See the next seciton on closing cursors with the function DropCursor().
Normally Win32::ODBC closes (or drops) cursors automatically everytime a Prepare() or
Sql() method is called. When the cursor is dropped it will unbind bound
parameters and the current result set is discarded. All statment options
are also discarded. This means that the cursor type, the cursor's rowset
size, cursor name and other statement related options are invalidated. They
must be reapplied if they are still needed.
Under certain circumstances it may be necessary to preserve the cursor and
not drop it when calling Prepare() or Sql(). In this case you can tell the Win32::ODBC object that when it performs an
automatic drop of the cursor that it should actually perform a different
function. This is accomplished with a call to the SetStmtCloseType().
Valid options are:
SQL_DONT_CLOSE............Do not ever drop the cursor unless explicity told so.
SQL_DROP..................Go ahead close the cursor and drop the statement.
This will reset all statement options
SQL_CLOSE.................Only close the cursor but leave the statement alive.
This will leave the statement options intact and active
SQL_RESET_PARAMS..........Leave the cursor and stmt untouched but remove all bound
parameters.
SQL_UNBIND................Leave the cursor and stmt untouched but unbind all columns.
(this is automatically performed when you resubmit a query
since bound columns must rebind to retrieve the results)
Example:
$db = new Win32::ODBC( "MyDSN" ) || die "Could not connect: " . Win32::ODBC::Error() . "\n";
$db->SetStmtOption( SQL_CURSOR_TYPE, SQL_CURSOR_STATIC );
$db->SetStmtOption( SQL_ROWSET_SIZE, 4000 );
# Do not allow the cursor to drop and stmt to close...
$db->SetStmtCloseType( SQL_DONT_CLOSE );
if( ! $db->Sql( "SELECT * FROM Foo" ) )
{
...process code...
# if you want to explicitly drop the cursor...
$db->DropCursor();
}
Okay so thus far you have seen what a cursor is and how to create one. But how do you play with it once it is created? Huh? Yeah, what about that? No problem. There are two ways to move about within a cursor:
The Win32::ODBC extension was originally written by Dan Demaggio and was called NT::ODBC. Since then there have been massive changes to the code base leading to the latest version which is almost a total rewrite.
Special thanks go out to Joe C. Jr. for donating the original documentation. It served users well. Check out his web site: http://xxxxxxxxxxxxxxxxxxxxx
Also special thanks to Bill Cowan of Metagenix (http://www.metagenix.com) for insight and testing.
And, of course, thanks to the countless thousands of users who have submitted bug reports, code patches, full fledged fixes and general feedback.
The Win32::ODBC was written by the folks at Roth Consulting. Principle coder was Dave Roth <rothd@roth.net> http://www.roth.net/
This document was last modified on 1999.12.11.