DBD::ADO - A DBI driver for Microsoft ADO (Active Data Objects)
use DBI(); my $dbh = DBI->connect("dbi:ADO:$dsn", $usr, $pwd, $att ) or die $DBI::errstr;
The DBD::ADO module supports ADO access on a Win32 machine. DBD::ADO is written to support the standard DBI interface to data sources.
It is recommended that you use recent versions of the following prerequisites:
http://search.cpan.org/~timb/DBI/
http://search.cpan.org/~jdb/Win32-OLE/
http://msdn.microsoft.com/data/
Use the DBI connect method to establish a database connection:
my $dbh = DBI->connect("dbi:ADO:$dsn", $usr, $pwd, $att ) or die $DBI::errstr;
where
$dsn - is an ADO ConnectionString $usr - is a user name $pwd - is a password $att - is a hash reference with additional attributes
Typical connection attributes are
RaiseError => 1 PrintError => 0 AutoCommit => 0
See the DBI module documentation for full details.
Driver specific connection attributes are:
ado_ConnectionTimeout ado_Mode
These attributes initialize the corresponding ADO Connection properties before opening the connection.
An ADO ConnectionString usually contains either a 'Provider' or a 'File Name' argument. If you omit these arguments, Provider defaults to MSDASQL (Microsoft OLE DB Provider for ODBC). Therefore you can pass an ODBC connection string (with DSN or DSN-less) as valid ADO connection string. If you use the OLE DB Provider for ODBC, it may be better to omit this additional layer and use DBD::ODBC with the ODBC driver.
In addition the following attributes may be set in the connection string:
Attributes CommandTimeout ConnectionString ConnectionTimeout CursorLocation DefaultDatabase IsolationLevel Mode
Warning: This feature is supported for backward compatibility. It's saver to use the driver specific attributes described above. Anyway, the application is responsible for passing the correct values when setting any of these attributes.
See the ADO documentation for more information on connection strings.
ADO ConnectionString examples:
test File Name=test.udl Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\data\test.mdb Provider=VFPOLEDB;Data Source=C:\data\test.dbc Provider=MSDAORA
For more examples, see e.g.:
http://www.able-consulting.com/tech.htm
Because ADO doesn't provide a data source repository, DBD::ADO uses it's own. It tries to load Local::DBD::ADO::DSN and expects an array of hashes describing the data sources. See ex/Local/DBD/ADO/DSN.pm for an example.
Warning: This is experimental and may change.
Warning: Check for the unlikly case that a file Local/DBD/ADO/DSN.pm exists in your module search path which causes unwanted side effects when loaded.
The prepare methods allows attributes (see DBI):
$sth = $dbh->prepare( $statement ) or die $dbh->errstr; $sth = $dbh->prepare( $statement, \%attr ) or die $dbh->errstr;
DBD::ADO's prepare() supports setting the CursorType, e.g.:
$sth = $dbh->prepare( $sql, { ado_cursortype => 'adOpenForwardOnly' } ) ... # the CursorType attribute is deprecated: $sth = $dbh->prepare( $sql, { CursorType => 'adOpenForwardOnly' } ) ...
Possible cursortypes are:
adOpenForwardOnly (default) adOpenKeyset adOpenDynamic adOpenStatic
It may be necessary to prepare the statement using cursortype 'adOpenStatic' when using a statement handle within a statement handle:
while( my $table = $sth1->fetchrow_hashref ) { ... my $col = $sth2->fetchrow_hashref; ... }
Changing the CursorType is a solution to the following problem:
Can't execute statement 'select * from authors': Lasterror : -2147467259 OLE exception from "Microsoft OLE DB Provider for SQL Server": Cannot create new connection because in manual or distributed transaction mode. Win32::OLE(0.1403) error 0x80004005: "Unspecified error" in METHOD/PROPERTYGET "Open" Description : Cannot create new connection because in manual or distributed transaction mode. HelpContext : 0 HelpFile : NativeError : 0 Number : -2147467259 Source : Microsoft OLE DB Provider for SQL Server SQLState :
Normally, the datatypes of placeholders are known after the statement is prepared. In this case, you don't need to provide any type information:
$sth->bind_param( 1, $value );
Sometimes, you need to specify a type for the parameter, e.g.:
$sth->bind_param( 1, $value, SQL_NUMERIC );
As a last resort, you can provide an ADO-specific type, e.g.:
$sth->bind_param( 1, $value, { ado_type => 6 } ); # adCurrency
If no type is given (neither by the provider nor by you), the datatype defaults to SQL_VARCHAR (adVarChar).
This can be utilized (with IN parameters) to support simple call-by-reference, allowing for lazy parameter binding.
$sth->bind_param_inout( 1, \$value, 1024 );
The contents of $value will not be dereferenced until the call to $sth->execute(); is made. To use IN/OUT parameter types with stored procedures, remember that you will need to specify the appropriate command type when preparing the statement, e.g.:
$sth->execute();
$sth = $dbh->prepare('sproc_name', { CommandType => 'adCmdStoredProc' } );
After execution, all call-by-reference parameters will be updated with the parameter values reported by the ADO command object.
For stored procedures, the parameter at index 0 is treated as the return value of the procedure. After execution, it is copied to the $sth->{ado_returnvalue} attribute.
0
$sth->{ado_returnvalue}
There exists two implementations of type_info_all(). Which version is used depends on the ado_ti_ver database handle attribute:
$dbh->{ado_ti_ver} = 1
The first implementations tries to find for various DBI types a set of ADO types supported by the provider. The algorithm is highly sophisticated. It tends to generate more duplicate type codes and names.
$dbh->{ado_ti_ver} = 2
The second implementations is quite straightforward. It uses the set which the provider returns and tries to map various ADO codes to DBI/ODBC codes. The mapping is similar to the one used in column_info(). Duplicate type codes and names tend to occur less often. The rows are ordered by DATA_TYPE, but not necessarily by 'how closely each type maps to the corresponding ODBC SQL data type'. This second sort criterion is difficult to achieve.
$sth = $dbh->table_info( $catalog, $schema, $table, $type, \%attr ); $sth = $dbh->table_info( \%attr ); # deprecated $sth = $dbh->table_info( undef, undef, undef,'VIEW', { ado_columns => 1, ado_trim_catalog => 0, ado_filter => q{TABLE_NAME LIKE 'C%'}, } );
Returns an active statement handle that can be used to fetch information about tables and views that exist in the database. By default the handle contains the columns described in the DBI documentation:
TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE, REMARKS
DBD::ADO allows selection criteria to be specified in the attributes hash for backward compatibility. In this way the record set can be restricted, for example, to only include tables of type 'VIEW':
%attr = ( TABLE_TYPE => 'VIEW') # deprecated
You can add criteria for any of the following columns:
TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE
DBD::ADO also allows the recordset to be filtered on a criteria string: a string made up of one or more individual clauses concatenated with AND or OR operators.
%attr = ( ado_filter => q{TABLE_TYPE LIKE 'SYSTEM%'} )
The criteria string is made up of clauses in the form FieldName-Operator-Value. This is more flexible than using column criteria in that the filter allows a number of operators:
<, >, <=, >=, <>, =, or LIKE
The Fieldname must be one of the ADO 'TABLES Rowset' column names:
TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, DESCRIPTION, TABLE_GUID, TABLE_PROPID, DATE_CREATED, DATE_MODIFIED
Value is the value with which you will compare the field values (for example,'Smith', #8/24/95#, 12.345, or $50.00). Use single quotes with strings and pound signs (#) with dates. For numbers, you can use decimal points, dollar signs, and scientific notation. If Operator is LIKE, Value can use wildcards. Only the asterisk (*) and percent sign (%) wild cards are allowed, and they must be the last character in the string. Value cannot be null.
Additional ADO-only fields will be included if the ado_columns attribute is set to true:
ado_columns
%attr = ( ado_columns => 1 );
Some ADO providers include path info in the TABLE_CAT column. This information will be trimmed if the ado_trim_catalog attribute is set to true:
ado_trim_catalog
%attr = ( ado_trim_catalog => 1 );
@names = $dbh->tables( $catalog, $schema, $table, $type, \%attr );
Returns a list of table and view names. Accepts any of the attributes described in the "table_info" method:
@names = $dbh->tables( undef, undef, undef,'VIEW');
The row for TYPE 'table' is always the first row. Its PAGES field will be NULL (undef).
undef
The $quick argument is ignored.
$quick
$sth = $dbh->ado_open_schema( $QueryType, @Criteria ) or die ...;
This method can be used to obtain database schema information from the provider. It returns a valid statement handle upon success.
$QueryType may be any valid ADO SchemaEnum name such as
$QueryType
adSchemaTables adSchemaIndexes adSchemaProviderTypes
@Criteria (optional) is a list of query constraints depending on each $QueryType.
@Criteria
Example:
my $sth = $dbh->ado_open_schema('adSchemaCheckConstraints','Catalog1');
Note: With DBI version 1.36 and earlier, the func() method has to be used to call private methods implemented by the driver:
$h->func( @func_arguments, $func_name ) or die ...;
where $func_name is 'ado_open_schema'. You can use 'OpenSchema' for backward compatibility.
$func_name
my $sth = $dbh->func('adSchemaCheckConstraints','Catalog1','OpenSchema');
See ex/OpenSchema.pl for a working example.
An ADO provider may return a collection of more than one error. After stringification , DBD::ADO concatenates these error messages to set the errstr value of the handle. However, the err value is set to the LastError known to Win32::OLE. Usually, this is the native OLE DB error code. These codes contain the following severity codes (see oledberr.h from the MDAC SDK):
00 - Success 01 - Informational 10 - Warning 11 - Error
The err value is set to 0 if all error codes belong to the Success or Informational category, which doesn't trigger the normal DBI error handling mechanisms.
The standard SQLSTATE is seldom supported by ADO providers and cannot be relied on.
The db/st handle attribute 'ado_max_errors' limits the number of errors extracted from the errors collection. To avoid time-consuming processing of huge error collections, it defaults to 50.
This attribute indicates the time (in seconds) after which a command is canceled. Typically, cancellation may occur during a long running execute method. Not all ADO providers support this functionality. Whereas ADO's Command object doesn't inherit the Connection's CommandTimeout setting, DBD::ADO's statement handle is initialized with the ado_commandtimeout attribute of its associated database handle.
execute
Since version 2.92, DBD::ADO supports the DBI handle attributes LongReadLen and LongTruncOk. LongReadLen defaults to 2147483647 (for backwards compatibility).
LongReadLen
LongTruncOk
2147483647
For multibyte strings, it's unspecified whether LongReadLen means bytes or characters. It's passed through to ADO's GetChunk method and ActualSize property.
GetChunk
ActualSize
Caveat: Unexpected truncation errors may occur if the ADO provider (e.g. Microsoft.Jet.OLEDB.4.0) uses bytes for ActualSize but characters for GetChunk.
Caveat: The ADO provider may have problems if the long field isn't the last one in the list of selected columns, e.g.:
Description : Catastrophic failure HelpContext : 5000000 HelpFile : NativeError : Number : -2147418113 Source : Microsoft JET Database Engine SQLState :
The (experimental) blob_read method ignores the $offset argument for long fields (ADO maintains a current offset internally). To ensure that blob_read reads from the beginning, LongReadLen must be set to 0.
blob_read
$offset
Proper Unicode support depends on all components involved in your application: the DBMS, the ADO provider, Perl and some perl modules.
In short: Perl 5.8 and Win32::OLE 0.16 (or later) are strongly recommended and Win32::OLE has to be prepared to use the correct codepage:
Win32::OLE->Option( CP => Win32::OLE::CP_UTF8 );
More detailed notes can be found at
http://purl.net/stefan_ram/pub/perl_unicode_en
It seems that the size of the first inserted string is sticky. Inserted strings longer than the first one are truncated.
As a workaround, the ado_size attribute for bind_param was introduced in version 2.95:
ado_size
bind_param
$sth->bind_param( $p_num, $bind_value, { ado_size => $size } );
MSDAORA may throw an error, return an empty result set or loop forever when CursorLocation is set to adUseClient. This setting is used in catalog methods for sorting and filtering.
CursorLocation
adUseClient
Tim Bunce and Phlip. With many thanks to Jan Dubois and Jochen Wiedmann for additions, debuggery and general help. Special thanks to Thomas Lowery, who maintained this module 2001-2003. Current maintainer is Steffen Goeldner.
This software is supported via the dbi-users mailing list. For more information and to keep informed about progress you can join the mailing list by sending a message to dbi-users-help@perl.org
Please post details of any problems (or changes you needed to make) to dbi-users@perl.org and CC them to me (sgoeldner@cpan.org).
Copyright (c) 1998, Tim Bunce Copyright (c) 1999, Tim Bunce, Phlip, Thomas Lowery Copyright (c) 2000, Tim Bunce, Thomas Lowery Copyright (c) 2001, Tim Bunce, Thomas Lowery, Steffen Goeldner Copyright (c) 2002, Thomas Lowery, Steffen Goeldner Copyright (c) 2003, Thomas Lowery, Steffen Goeldner Copyright (c) 2004-2011 Steffen Goeldner All rights reserved. You may distribute under the terms of either the GNU General Public License or the Artistic License, as specified in the Perl README file.
ADO Reference book: ADO 2.0 Programmer's Reference David Sussman and Alex Homer Wrox ISBN 1-861001-83-5 ADO: ActiveX Data Objects Jason T. Roff O'Reilly ISBN 1-56592-415-0 http://www.oreilly.com/catalog/ado/index.html
If there's anything better please let me know.
DBI, DBD::ODBC, Win32::OLE
To install DBD::ADO, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DBD::ADO
CPAN shell
perl -MCPAN -e shell install DBD::ADO
For more information on module installation, please visit the detailed CPAN module installation guide.