ODBC(3) User Contributed Perl Documentation ODBC(3)NAME
Win32::ODBC - ODBC Extension for Win32
SYNOPSIS
To use this module, include the following statement at the top of your
script:
use Win32::ODBC;
Next, create a data connection to your DSN:
$Data = new Win32::ODBC("MyDSN");
NOTE: MyDSN can be either the DSN as defined in the ODBC Administrator,
or it can be an honest-to-God DSN Connect String.
Example: "DSN=My Database;UID=Brown Cow;PWD=Moo;"
You should check to see if $Data is indeed defined, otherwise there has
been an error.
You can now send SQL queries and retrieve info to your heart's content!
See the description of the methods provided by this module below and
also the file TEST.PL as referred to in INSTALLATION NOTES to see how
it all works.
Finally, MAKE SURE that you close your connection when you are
finished:
$Data->Close();
DESCRIPTION
Background
This is a hack of Dan DeMaggio's <dmag@umich.edu> NTXS.C ODBC
implementation. I have recoded and restructured most of it including
most of the ODBC.PM package, but its very core is still based on Dan's
code (thanks Dan!).
The history of this extension is found in the file HISTORY.TXT that
comes with the original archive (see "INSTALLATION NOTES" below).
Benefits
And what are the benefits of this module?
· The number of ODBC connections is limited by memory and ODBC itself
(have as many as you want!).
· The working limit for the size of a field is 10,240 bytes, but you
can increase that limit (if needed) to a max of 2,147,483,647
bytes. (You can always recompile to increase the max limit.)
· You can open a connection by either specifing a DSN or a connection
string!
· You can open and close the connections in any order!
· Other things that I can not think of right now... :)
CONSTANTS
This package defines a number of constants. You may refer to each of
these constants using the notation "ODBC::xxxxx", where "xxxxx" is the
constant.
Example:
print ODBC::SQL_SQL_COLUMN_NAME, "\n";
SPECIAL NOTATION
For the method documentation that follows, an * following the method
parameters indicates that that method is new or has been modified for
this version.
CONSTRUCTOR
new ( ODBC_OBJECT | DSN [, (OPTION1, VALUE1), (OPTION2, VALUE2) ...] )
*
Creates a new ODBC connection based on "DSN", or, if you specify an
already existing ODBC object, then a new ODBC object will be
created but using the ODBC Connection specified by "ODBC_OBJECT".
(The new object will be a new hstmt using the hdbc connection in
"ODBC_OBJECT".)
"DSN" is Data Source Name or a proper "ODBCDriverConnect" string.
You can specify SQL Connect Options that are implemented before the
actual connection to the DSN takes place. These option/values are
the same as specified in "GetConnectOption"/"SetConnectOption" (see
below) and are defined in the ODBC API specs.
Returns a handle to the database on success, or undef on failure.
METHODS
Catalog ( QUALIFIER, OWNER, NAME, TYPE )
Tells ODBC to create a data set that contains table information
about the DSN. Use "Fetch" and "Data" or "DataHash" to retrieve the
data. The returned format is:
[Qualifier] [Owner] [Name] [Type]
Returns true on error.
ColAttributes ( ATTRIBUTE [, FIELD_NAMES ] )
Returns the attribute "ATTRIBUTE" on each of the fields in the list
"FIELD_NAMES" in the current record set. If "FIELD_NAMES" is empty,
then all fields are assumed. The attributes are returned as an
associative array.
ConfigDSN ( OPTION, DRIVER, ATTRIBUTE1 [, ATTRIBUTE2, ATTRIBUTE3, ... ]
)
Configures a DSN. "OPTION" takes on one of the following values:
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 System DSN.
ODBC_MODIFY_SYS_DSN....Modifies an existing System DSN.
ODBC_REMOVE_SYS_DSN....Removes an existing System DSN.
You must specify the driver "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"
Returns true on success, false on failure.
NOTE 1: If you use "ODBC_ADD_DSN", then you must include at least
"DSN=xxx" and the location of the database.
Example: For MS Access databases, you must specify the
DatabaseQualifier:
"DBQ=c:\\...\\MyDatabase.mdb"
NOTE 2: If you use "ODBC_MODIFY_DSN", then you need only specify
the "DNS=xxx" attribute. Any other attribute you include will be
changed to what you specify.
NOTE 3: If you use "ODBC_REMOVE_DSN", then you need only specify
the "DSN=xxx" attribute.
Connection ()
Returns the connection number associated with the ODBC connection.
Close ()
Closes the ODBC connection. No return value.
Data ( [ FIELD_NAME ] )
Returns the contents of column name "FIELD_NAME" or the current row
(if nothing is specified).
DataHash ( [ FIELD1, FIELD2, ... ] )
Returns the contents for "FIELD1, FIELD2, ..." or the entire row
(if nothing is specified) as an associative array consisting of:
{Field Name} => Field Data
DataSources ()
Returns an associative array of Data Sources and ODBC remarks about
them. They are returned in the form of:
$ArrayName{'DSN'}=Driver
where DSN is the Data Source Name and ODBC Driver used.
Debug ( [ 1 | 0 ] )
Sets the debug option to on or off. If nothing is specified, then
nothing is changed.
Returns the debugging value (1 or 0).
Drivers ()
Returns an associative array of ODBC Drivers and their 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.
DropCursor ( [ CLOSE_TYPE ] )
Drops the cursor associated with the ODBC object. This forces the
cursor to be deallocated. This overrides "SetStmtCloseType", but
the ODBC object does not lose the "StmtCloseType" setting.
"CLOSE_TYPE" can be any valid "SmtpCloseType" and will perform a
close on the stmt using the specified close type.
Returns true on success, false on failure.
DumpData ()
Dumps to the screen the fieldnames and all records of the current
data set. Used primarily for debugging. No return value.
Error ()
Returns the last encountered error. The returned value is context
dependent:
If called in a scalar context, then a 3-element array is returned:
( ERROR_NUMBER, ERROR_TEXT, CONNECTION_NUMBER )
If called in a string context, then a string is returned:
"[ERROR_NUMBER] [CONNECTION_NUMBER] [ERROR_TEXT]"
If debugging is on then two more variables are returned:
( ..., FUNCTION, LEVEL )
where "FUNCTION" is the name of the function in which the error
occurred, and "LEVEL" represents extra information about the error
(usually the location of the error).
FetchRow ( [ ROW [, TYPE ] ] )
Retrieves the next record from the keyset. When "ROW" and/or "TYPE"
are specified, the call is made using "SQLExtendedFetch" instead of
"SQLFetch".
NOTE 1: If you are unaware of "SQLExtendedFetch" and its
implications, stay with just regular "FetchRow" with no parameters.
NOTE 2: The ODBC API explicitly warns against mixing calls to
"SQLFetch" and "SQLExtendedFetch"; use one or the other but not
both.
If ROW is specified, it moves the keyset RELATIVE "ROW" number of
rows.
If ROW is specified and "TYPE" is not, then the type used is
RELATIVE.
Returns true when another record is available to read, and false
when there are no more records.
FieldNames ()
Returns an array of fieldnames found in the current data set. There
is no guarantee on order.
GetConnections ()
Returns an array of connection numbers showing what connections are
currently open.
GetConnectOption ( OPTION )
Returns the value of the specified connect option "OPTION". Refer
to ODBC documentation for more information on the options and
values.
Returns a string or scalar depending upon the option specified.
GetCursorName ()
Returns the name of the current cursor as a string or undef.
GetData ()
Retrieves the current row from the dataset. This is not generally
used by users; it is used internally.
Returns an array of field data where the first element is either
false (if successful) and true (if not successful).
getDSN ( [ DSN ] )
Returns an associative array indicating the configuration for the
specified DSN.
If no DSN is specified then the current connection is used.
The returned associative array consists of:
keys=DSN keyword; values=Keyword value. $Data{$Keyword}=Value
GetFunctions ( [ FUNCTION1, FUNCTION2, ... ] )
Returns an associative array indicating the ability of the ODBC
Driver to support the specified functions. If no functions are
specified, then a 100 element associative array is returned
containing all possible functions and their values.
"FUNCTION" must be in the form of an ODBC API constant like
"SQL_API_SQLTRANSACT".
The returned array will contain the results like:
$Results{SQL_API_SQLTRANSACT}=Value
Example:
$Results = $O->GetFunctions(
$O->SQL_API_SQLTRANSACT,
SQL_API_SQLSETCONNECTOPTION
);
$ConnectOption = $Results{SQL_API_SQLSETCONNECTOPTION};
$Transact = $Results{SQL_API_SQLTRANSACT};
GetInfo ( OPTION )
Returns a string indicating the value of the particular option
specified.
GetMaxBufSize ()
Returns the current allocated limit for MaxBufSize. For more info,
see "SetMaxBufSize".
GetSQLState () *
Returns a string indicating 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 a SQL function. This is helpful for
debugging purposes.
GetStmtCloseType ( [ CONNECTION ] )
Returns a string indicating the type of closure that will be used
everytime the hstmt is freed. See "SetStmtCloseType" for details.
By default, the connection of the current object will be used. If
"CONNECTION" is a valid connection number, then it will be used.
GetStmtOption ( OPTION )
Returns the value of the specified statement option "OPTION". Refer
to ODBC documentation for more information on the options and
values.
Returns a string or scalar depending upon the option specified.
MoreResults ()
This will report whether there is data yet to be retrieved from the
query. This can happen if the query was a multiple select.
Example:
"SELECT * FROM [foo] SELECT * FROM [bar]"
NOTE: Not all drivers support this.
Returns 1 if there is more data, undef otherwise.
RowCount ( CONNECTION )
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 1: This function is not supported by all ODBC drivers! Some
drivers do support this but not for all statements (e.g., it is
supported for UPDATE, INSERT and DELETE commands but not for the
SELECT command).
NOTE 2: 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 the number of affected rows, or -1 if not supported by the
driver in the current context.
Run ( SQL )
Executes the SQL command SQL and dumps to the screen info about it.
Used primarily for debugging.
No return value.
SetConnectOption ( OPTION ) *
Sets the value of the specified connect option OPTION. Refer to
ODBC documentation for more information on the options and values.
Returns true on success, false otherwise.
SetCursorName ( NAME ) *
Sets the name of the current cursor.
Returns true on success, false otherwise.
SetPos ( ROW [, OPTION, LOCK ] ) *
Moves the cursor to the row "ROW" within the current keyset (not
the current data/result set).
Returns true on success, false otherwise.
SetMaxBufSize ( SIZE )
This sets the MaxBufSize for a particular connection. This will
most likely never be needed but...
The amount of memory that is allocated to retrieve the field data
of a record 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 10,240, you can use this function to increase the
limit up to a ceiling of 2,147,483,647 (recompile if you need
more).
Returns the max number of bytes.
SetStmtCloseType ( TYPE [, CONNECTION ] )
Sets a particular hstmt close type for the connection. This is the
same as "ODBCFreeStmt(hstmt, TYPE)". By default, the connection of
the current object will be used. If "CONNECTION" is a valid
connection number, then it will be used.
"TYPE" may be one of:
SQL_CLOSE
SQL_DROP
SQL_UNBIND
SQL_RESET_PARAMS
Returns a string indicating the newly set type.
SetStmtOption ( OPTION ) *
Sets the value of the specified statement option "OPTION". Refer to
ODBC documentation for more information on the options and values.
Returns true on success, false otherwise.
ShutDown ()
Closes the ODBC connection and dumps to the screen info about it.
Used primarily for debugging.
No return value.
Sql ( SQL_STRING )
Executes the SQL command "SQL_STRING" on the current connection.
Returns ? on success, or an error number on failure.
TableList ( QUALIFIER, OWNER, NAME, TYPE )
Returns the catalog of tables that are available in the DSN. For an
unknown parameter, just specify the empty string "".
Returns an array of table names.
Transact ( TYPE ) *
Forces the ODBC connection to perform a rollback or commit
transaction.
"TYPE" may be one of:
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 on success, false otherwise.
Version ( PACKAGES )
Returns an array of version numbers for the requested packages
(ODBC.pm or ODBC.PLL). If the list "PACKAGES" is empty, then all
version numbers are returned.
LIMITATIONS
What known problems does this thing have?
· If the account under which the process runs does not have write
permission on the default directory (for the process, not the ODBC
DSN), you will probably get a runtime error during a
"SQLConnection". I don't think that this is a problem with the
code, but more like a problem with ODBC. This happens because some
ODBC drivers need to write a temporary file. I noticed this using
the MS Jet Engine (Access Driver).
· This module has been neither optimized for speed nor optimized for
memory consumption.
INSTALLATION NOTES
If you wish to use this module with a build of Perl other than
ActivePerl, you may wish to fetch the original source distribution for
this module at:
ftp://ftp.roth.net:/pub/ntperl/ODBC/970208/Bin/Win32_ODBC_Build_CORE.zip
or one of the other archives at that same location. See the included
README for hints on installing this module manually, what to do if you
get a parse exception, and a pointer to a test script for this module.
OTHER DOCUMENTATION
Find a FAQ for Win32::ODBC at:
http://www.roth.net/odbc/odbcfaq.htm
AUTHOR
Dave Roth <rothd@roth.net>
CREDITS
Based on original code by Dan DeMaggio <dmag@umich.edu>
DISCLAIMER
I do not guarantee ANYTHING with this package. If you use it you are
doing so AT YOUR OWN RISK! I may or may not support this depending on
my time schedule.
HISTORY
Last Modified 1999.09.25.
COPYRIGHT
Copyright (c) 1996-1998 Dave Roth. All rights reserved.
Courtesy of Roth Consulting: http://www.roth.net/consult/
Use under GNU General Public License. Details can be found at:
http://www.gnu.org/copyleft/gpl.html
perl v5.14.2 2005-09-17 ODBC(3)
