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();
The history of this extension is found in the file HISTORY.TXT that comes with the original archive (see ``INSTALLATION NOTES'' below).
Example:
print ODBC::SQL_SQL_COLUMN_NAME, "\n";
"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.
[Qualifier] [Owner] [Name] [Type]
Returns true on error.
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.
{Field Name} => Field Data
$ArrayName{'DSN'}=Driver
where DSN is the Data Source Name and ODBC Driver used.
Returns the debugging value (1 or 0).
$ArrayName{'DRIVER'}=Attrib1;Attrib2;Attrib3;...
where DRIVER is the ODBC Driver Name and AttribX are the driver-defined attributes.
Returns true on success, false on failure.
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).
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.
Returns a string or scalar depending upon the option specified.
Returns an array of field data where the first element is either false (if successful) and true (if not successful).
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
"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};
By default, the connection of the current object will be used. If "CONNECTION" is a valid connection number, then it will be used.
Returns a string or scalar depending upon the option specified.
Example:
"SELECT * FROM [foo] SELECT * FROM [bar]"
NOTE: Not all drivers support this.
Returns 1 if there is more data, undef otherwise.
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.
No return value.
Returns true on success, false otherwise.
Returns true on success, false otherwise.
Returns true on success, false otherwise.
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.
"TYPE" may be one of:
SQL_CLOSE
SQL_DROP
SQL_UNBIND
SQL_RESET_PARAMS
Returns a string indicating the newly set type.
Returns true on success, false otherwise.
No return value.
Returns ? on success, or an error number on failure.
Returns an array of table names.
"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.
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.
http://www.roth.net/odbc/odbcfaq.htm
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