Book HomeJava and XSLTSearch this book

Chapter 24. ODBC Extension for Win32

Contents:

ODBC Methods and Properties

Win32::ODBC allows you to make a connection to a database that supports ODBC under both Win32 and Unix. Win32::ODBC allows you to support as many ODBC connections as are allowed by memory and ODBC.

Generally, you'll use Win32::ODBC to access databases such as those created under Microsoft Access, but there's also an ODBC driver available for MySQL if you want to connect to MySQL via ODBC and aren't able to use DBI and a DBD.

To get started, you'll need to use new( ) to create a new Win32::ODBC object. As arguments, new( ) takes a connection string (DSN) or an open ODBC object if one is given. new( ) returns a database handle on success and undef on failure. For example:

use Win32::ODBC;

my $dsn = "DSN=Name of Database;UID=Your UID Here;PWD=dbpasswd;"
my $DB  = Win32::ODBC->new($dsn);

24.1. ODBC Methods and Properties

Once you have created an ODBC object, you can use its methods or adjust its properties as you require. ODBC methods are implemented as you'd expect with the Perl object syntax.

The following methods are defined by Win32::ODBC.

Catalog

$obj->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

Win32::ODBC->ColAttributes(attributes, [field_names])

Returns the attributes attributes 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

Win32::ODBC->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

driver is a required argument. attribute1 is the DSN, whose format is shown earlier. ConfigDSN( ) returns true on success and false on failure.

Connection

Win32::ODBC->Connection(  )

Returns the connection number associated with the ODBC connection.

Close

Win32::ODBC->Close(  )

Closes the ODBC connection. No return value.

Data

Win32::ODBC->Data([field_name])

Returns the contents of column name field_name or the current row (if nothing is specified).

DataHash

Win32::ODBC->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

Win32::ODBC->DataSources(  )

Returns an associative array of data sources and ODBC remarks about them. They are returned in the form of:

$ArrayName{'DSN'}=Driver
Debug

Win32::ODBC->Debug( [ 0 | 1 ] )

Sets the debug option to on or off. If nothing is specified, then nothing is changed. Returns the debugging value (1 or 0).

Drivers

Win32::ODBC->Drivers(  )

Returns an associative array of ODBC Drivers and their attributes. They are returned in the form of:

$ArrayName{'driver'}=Attrib1;Attrib2;Attrib3;...

in which driver is the ODBC driver name, and AttribX are the driver-defined attributes.

DropCursor

Win32::ODBC->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 statement using the specified close type. Returns true on success, false on failure.

DumpData

Win32::ODBC->DumpData(  )

Dumps to the screen the fieldnames and all records of the current data set. Used primarily for debugging. No return value.

Error

Win32::ODBC->Error(  )

Returns the last encountered error. The returned value is context-dependent.

If called in a scalar context, then a three-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 )

function is the name of the function in which the error occurred, and levelrepresents extra information about the error (usually the location of the error).

FetchRow

Win32::ODBC->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. Returns true when another record is available to read and false when there are no more records.

FieldNames

Win32::ODBC->FieldNames(  )

Returns an array of fieldnames found in the current data set. The order isn't specified.

GetConnections

Win32::ODBC->GetConnections(  )

Returns an array of connection numbers showing which connections are currently open.

GetConnectOption

Win32::ODBC->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

Win32::ODBC->GetCursorName(  )

Returns the name of the current cursor as a string or undef.

GetData

Win32::ODBC->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 in which the first element is either false (if successful) or true (if not successful).

getDSN

Win32::ODBC->getDSN([dsn])

Returns an associative array indicating the configuration for the specified dsn. If no dsnis specified, then the current connection is used. The returned associative array consists of:

keys=DSN keyword; values=Keyword value. 
$Data{$Keyword}=Value
GetFunctions

Win32::ODBC->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 such as SQL_API_SQLTRANSACT. The returned array will contain the results:

$Results{SQL_API_SQLTRANSACT}=Value

For example:

$Results = $O->GetFunctions(
    $O->SQL_API_SQLTRANSACT,
    SQL_API_SQLSETCONNECTOPTION
);
$ConnectOption = $Results{SQL_API_SQLSETCONNECTOPTION};
$Transact = $Results{SQL_API_SQLTRANSACT};
GetInfo

Win32::ODBC->GetInfo([option])

Returns a string indicating the value of the particular option specified.

GetMaxBufSize

Win32::ODBC->GetMaxBufSize(  )

Returns the current allocated limit for MaxBufSize. For more info, see SetMaxBufSize.

GetSQLState

Win32::ODBC->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

Win32::ODBC->GetStmtCloseType([connection])

Returns a string indicating the type of closure that will be used every time 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

Win32::ODBC->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 on the option specified.

MoreResults

Win32::ODBC->MoreResults([connection])

Reports whether there is data yet to be retrieved from the query. This can happen if the query was a multiple select:

"SELECT * FROM [foo] SELECT * FROM [bar]"

Returns 1 if there is more data, undef otherwise.

RowCount

Win32::ODBC->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. Returns the number of affected rows, or -1 if not supported by the driver in the current context.

Run

Win32::ODBC->Run(sql)

Executes the SQL command sql and dumps to the screen info about it. Used primarily for debugging. No return value.

SetConnectOption

Win32::ODBC->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

Win32::ODBC->SetCursorName(name)

Sets the name of the current cursor. Returns true on success, false otherwise.

SetPos

Win32::ODBC->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

Win32::ODBC->SetMaxBufSize(size)

Sets the MaxBufSize for a particular connection. The amount of memory allocated to retrieve the field data of a record is dynamic and changes when it needs to be larger. Returns the maximum number of bytes.

SetStmtCloseType

Win32::ODBC->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

Win32::ODBC->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

Win32::ODBC->Shutdown(  )

Closes the ODBC connection and dumps to the screen info about it. Used primarily for debugging. No return value.

Sql

Win32::ODBC->Sql(SQL_STRING)

Executes the SQL command SQL_STRING on the current connection. Returns ? on success, or an error number on failure.

TableList

Win32::ODBC->TableList(qualifier, owner, name, type)

Returns the catalog of tables available in the DSN. For an unknown parameter, just specify the empty string "". Returns an array of table names.

Transact

Win32::ODBC->Transact(type)

Forces the ODBC connection to perform a rollback or commit transaction. type may be one of:

SQL_COMMIT
SQL_ROLLBACK

Returns true on success, false otherwise.

Version

Win32::ODBC->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.



Library Navigation Links

Copyright © 2002 O'Reilly & Associates. All rights reserved.