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);

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.

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

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.

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.

Win32::ODBC->Connection( ) 

Returns the connection number associated with the ODBC connection.

Win32::ODBC->Close( ) 

Closes the ODBC connection. No return value.

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

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

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

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

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 ( or ).

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.

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.

Win32::ODBC->DumpData( ) 

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

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).

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.

Win32::ODBC->FieldNames( ) 

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

Win32::ODBC->GetConnections( ) 

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

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.

Win32::ODBC->GetCursorName( ) 

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

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).

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

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};

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

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

Win32::ODBC->GetMaxBufSize( ) 

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

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.

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.

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.

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 if there is more data, undef otherwise.

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.

Win32::ODBC->Run(sql) 

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

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.

Win32::ODBC->SetCursorName(name) 

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

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.

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.

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.

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.

Win32::ODBC->Shutdown( ) 

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

Win32::ODBC->Sql(SQL_STRING) 

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

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.

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.

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.