DBD-ODBC
view release on metacpan or search on metacpan
my($drh, $dbname, $user, $auth, $attr)= @_;
#$user = q{} unless defined $user;
#$auth = q{} unless defined $auth;
# create a 'blank' dbh
my $this = DBI::_new_dbh($drh, {
'Name' => $dbname,
'USER' => $user,
'CURRENT_USER' => $user,
});
# Call ODBC _login func in Driver.xst file => dbd_db_login6
# and populate internal handle data.
# There are 3 versions (currently) if you have a recent DBI:
# dbd_db_login (oldest)
# dbd_db_login6 (with attribs hash & char * args) and
# dbd_db_login6_sv (as dbd_db_login6 with perl scalar args
DBD::ODBC::db::_login($this, $dbname, $user, $auth, $attr) or return;
return $this;
}
## use critic
sub data_sources {
my ($drh, $attr) = @_;
my $dsref = DBD::ODBC::dr::_data_sources( $drh, $attr );
if( defined( $dsref ) && ref( $dsref ) eq "ARRAY" ) {
return @$dsref;
}
return (); # Return empty array
}
}
{ package DBD::ODBC::db; # ====== DATABASE ======
use strict;
use warnings;
use constant SQL_DRIVER_HSTMT => 5;
use constant SQL_DRIVER_HLIB => 76;
use constant SQL_DRIVER_HDESC => 135;
sub parse_trace_flag {
my ($h, $name) = @_;
return DBD::ODBC->parse_trace_flag($name);
}
sub private_attribute_info {
return {
odbc_ignore_named_placeholders => undef, # sth and dbh
odbc_default_bind_type => undef, # sth and dbh
odbc_force_bind_type => undef, # sth and dbh
odbc_force_rebind => undef, # sth and dbh
odbc_async_exec => undef, # sth and dbh
odbc_exec_direct => undef,
odbc_describe_parameters => undef,
odbc_SQL_ROWSET_SIZE => undef,
odbc_SQL_DRIVER_ODBC_VER => undef,
odbc_cursortype => undef,
odbc_query_timeout => undef, # sth and dbh
odbc_has_unicode => undef,
odbc_out_connect_string => undef,
odbc_version => undef,
odbc_err_handler => undef,
odbc_putdata_start => undef, # sth and dbh
odbc_column_display_size => undef, # sth and dbh
odbc_utf8_on => undef, # sth and dbh
odbc_driver_complete => undef,
odbc_batch_size => undef,
odbc_array_operations => undef, # sth and dbh
odbc_taf_callback => undef,
odbc_trace => undef, # dbh
odbc_trace_file => undef, # dbh
};
}
sub prepare {
my($dbh, $statement, @attribs)= @_;
# create a 'blank' sth
my $sth = DBI::_new_sth($dbh, {
'Statement' => $statement,
});
# Call ODBC func in ODBC.xs file.
# (This will actually also call SQLPrepare for you.)
# and populate internal handle data.
DBD::ODBC::st::_prepare($sth, $statement, @attribs)
or return;
return $sth;
}
sub column_info {
my ($dbh, $catalog, $schema, $table, $column) = @_;
$catalog = q{} if (!$catalog);
$schema = q{} if (!$schema);
$table = q{} if (!$table);
$column = q{} if (!$column);
# create a "blank" statement handle
my $sth = DBI::_new_sth($dbh, { 'Statement' => "SQLColumns" });
_columns($dbh,$sth, $catalog, $schema, $table, $column)
or return;
return $sth;
}
sub columns {
my ($dbh, $catalog, $schema, $table, $column) = @_;
$catalog = q{} if (!$catalog);
$schema = q{} if (!$schema);
$table = q{} if (!$table);
$column = q{} if (!$column);
# create a "blank" statement handle
my $sth = DBI::_new_sth($dbh, { 'Statement' => "SQLColumns" });
connection attribute. However, in ODBC 2.0 you could set statement
attributes on a connection handle and it acted as a default for all
subsequent statement handles created under that connection handle. If
you are using ODBC 3 the driver manager continues to map this call but
the ODBC Driver needs to act on it (the MS SQL Server driver still
appears to but some other ODBC drivers for MS SQL Server do not).
Secondly, somewhere a long the line MS decided it was no longer valid
to retrieve the SQL_ROWSET_SIZE attribute from a connection handle in
an ODBC 3 application (which DBD::ODBC now is). In itself, this would
not be a problem except for a minor bug in DBI which until release
1.616 mistakenly issued a FETCH on any attribute mentioned in the
connect method call. As a result, it you use a DBI prior to 1.616 and
attempt to set odbc_SQL_ROWSET_SIZE in the connect method call, DBI
issues a FETCH on odbc_SQL_ROWSET_SIZE and the driver manager throws
it out as an invalid attribute thus resulting in an error. The only
way around this (other than upgrading DBI) is to set
odbc_SQL_ROWSET_SIZE AFTER the call to connect. Thirdly, MS withdrew
the SQLROWSETSIZE macro from the sql header files in MDAC 2.7 for 64
bit platforms i.e., SQLROWSETSIZE is not defined on 64 bit platforms
from MDAC 2.7 as it is in a "#ifdef win32" (see
http://msdn.microsoft.com/en-us/library/ms716287%28v=vs.85%29.aspx).
Setting SQL_ROWSET_SIZE still seems to take effect on 64 bit platforms
but you can no longer retrieve its value from a connection handle
(hence the issue above with DBI redundant FETCH).
=head3 odbc_exec_direct
Force DBD::ODBC to use C<SQLExecDirect> instead of
C<SQLPrepare>/C<SQLExecute>.
There are drivers that only support C<SQLExecDirect> and the DBD::ODBC
do() override does not allow returning result sets. Therefore, the
way to do this now is to set the attribute odbc_exec_direct.
NOTE: You may also want to use this option if you are creating
temporary objects (e.g., tables) in MS SQL Server and for some
reason cannot use the C<do> method. see
L<http://technet.microsoft.com/en-US/library/ms131667.aspx> which says
I<Prepared statements cannot be used to create temporary objects on
SQL Server 2000 or later...>. Without odbc_exec_direct, the temporary
object will disappear before you can use it.
There are currently two ways to get this:
$dbh->prepare($sql, { odbc_exec_direct => 1});
and
$dbh->{odbc_exec_direct} = 1;
B<NOTE:> Even if you build DBD::ODBC with unicode support you can
still not pass unicode strings to the prepare method if you also set
odbc_exec_direct. This is a restriction in this attribute which is
unavoidable.
=head3 odbc_SQL_DRIVER_ODBC_VER
This, while available via get_info() is captured here. I may get rid
of this as I only used it for debugging purposes.
=head3 odbc_cursortype
This allows multiple concurrent statements on SQL*Server. In your
connect, add
{ odbc_cursortype => 2 }.
If you are using DBI > 1.41, you should also be able to use
{ odbc_cursortype => DBI::SQL_CURSOR_DYNAMIC }
instead. For example:
my $dbh = DBI->connect("dbi:ODBC:$DSN", $user, $pass,
{ RaiseError => 1, odbc_cursortype => 2});
my $sth = $dbh->prepare("one statement");
my $sth2 = $dbh->prepare("two statement");
$sth->execute;
my @row;
while (@row = $sth->fetchrow_array) {
$sth2->execute($row[0]);
}
See F<t/20SqlServer.t> for an example.
In versions of SQL Server 2005 and later see "Multiple Active Statements (MAS)" in the DBD::ODBC::FAQ instead of using this attribute.
=head3 odbc_has_unicode
A read-only attribute signifying whether DBD::ODBC was built with the
C macro WITH_UNICODE or not. A value of 1 indicates DBD::ODBC was built
with WITH_UNICODE else the value returned is 0.
Building WITH_UNICODE affects columns and parameters which are
SQL_C_WCHAR, SQL_WCHAR, SQL_WVARCHAR, and SQL_WLONGVARCHAR, SQL,
the connect method and a lot more. See L</Unicode>.
When odbc_has_unicode is 1, DBD::ODBC will:
=over
=item bind all string columns as wide characters (SQL_Wxxx)
This means that UNICODE data stored in these columns will be returned
to Perl correctly as unicode (i.e., encoded in UTF-8 and the UTF-8 flag set).
=item bind parameters the database declares as wide characters or unicode parameters as SQL_Wxxx
Parameters bound where the database declares the parameter as being a
wide character, or where the parameter data is unicode, or where the
parameter type is explicitly set to a wide type (e.g., SQL_Wxxx) are bound
as wide characters in the ODBC API and DBD::ODBC encodes the perl parameters
as UTF-16 before passing them to the driver.
=item SQL
SQL passed to the C<prepare> or C<do> methods which has the UTF-8 flag
set will be converted to UTF-16 before being passed to the ODBC APIs
C<SQLPrepare> or C<SQLExecDirect>.
=item connection strings
Connection strings passed to the C<connect> method will be converted
to UTF-16 before being passed to the ODBC API
C<SQLDriverConnectW>. This happens irrespective of whether the UTF-8
flag is set on the perl connect strings because unixODBC requires an
application to call SQLDriverConnectW to indicate it will be calling
the wide ODBC APIs.
=back
NOTE: You will need at least Perl 5.8.1 to use UNICODE with DBD::ODBC.
NOTE: Binding of unicode output parameters is coded but untested.
cannot fit in an int are truncated. See RT 81911.
From DBD::ODBC 1.42_1 DBD::ODBC
o defines this method which will return the affected rows in an IV
(and IVs are guaranteed to be able to hold a pointer) so you
can get the real affected rows without truncation.
o if it detects an overflow in the execute method it will issue
a warning (if Warn is on which it is by default) and return INT_MAX.
At some stage DBI may change to fix the issue this works around.
NOTE: the return from odbc_rows is not the raw value returned by
SQLRowCount. It is the same as execute normally returns e.g., 0E0 (for
0), -1 for unknown and N for N rows affected where N > 0.
=head3 odbc_lob_read
$chrs_or_bytes_read = $sth->odbc_lob_read($column_no, \$lob, $length, \%attr);
Reads C<$length> bytes from the lob at column C<$column_no> returning
the lob into C<$lob> and the number of bytes or characters read into
C<$chrs_or_bytes_read>. If an error occurs undef will be returned.
When there is no more data to be read 0 is returned.
NOTE: This is currently an experimental method and may change in the
future e.g., it may support automatic concatenation of the lob
parts onto the end of the C<$lob> with the addition of an extra flag
or destination offset as in DBI's undocumented blob_read.
The type the lob is retrieved as may be overridden in C<%attr> using
C<TYPE =E<gt> sql_type>. C<%attr> is optional and if omitted defaults
to SQL_C_BINARY for binary columns and SQL_C_CHAR/SQL_C_WCHAR for
other column types depending on whether DBD::ODBC is built with
unicode support. C<$chrs_or_bytes_read> will by the bytes read when
the column types SQL_C_CHAR or SQL_C_BINARY are used and characters
read if the column type is SQL_C_WCHAR.
When built with unicode support C<$length> specifies the amount of
buffer space to be used when retrieving the lob data but as it is
returned as SQLWCHAR characters this means you at most retrieve
C<$length/2> characters. When those retrieved characters are encoded
in UTF-8 for Perl, the C<$lob> scalar may need to be larger than
C<$length> so DBD::ODBC grows it appropriately.
You can retrieve a lob in chunks like this:
$sth->bind_col($column, undef, {TreatAsLOB=>1});
while(my $retrieved = $sth->odbc_lob_read($column, \my $data, $length)) {
print "retrieved=$retrieved lob_data=$data\n";
}
NOTE: to retrieve a lob like this you B<must> first bind the lob
column specifying BindAsLOB or DBD::ODBC will 1) bind the column as
normal and it will be subject to LongReadLen and b) fail
odbc_lob_read.
NOTE: Some database engines and ODBC drivers do not allow you to
retrieve columns out of order (e.g., MS SQL Server unless you are
using cursors). In those cases you must ensure the lob retrieved is
the last (or only) column in your select list.
NOTE: You can retrieve only part of a lob but you will probably have
to call finish on the statement handle before you do anything else
with that statement. When only retrieving part of a large lob you
could see a small delay when you call finish as some protocols used
by ODBC drivers send the lob down the socket synchronously and there is
no way to stop it (this means the ODBC driver needs to read all the
lob from the socket even though you never retrieved it all yourself).
NOTE: If your select contains multiple lobs you cannot read part of
the first lob, the second lob then return to the first lob. You must
read all lobs in order and completely or read part of a lob and then
do no further calls to odbc_lob_read.
=head2 Private DBD::ODBC Functions
You use DBD::ODBC private functions like this:
$dbh->func(arg, private_function_name, @args);
=head3 GetInfo
B<This private function is now superseded by DBI's get_info method.>
This function maps to the ODBC SQLGetInfo call and the argument
should be a valid ODBC information type (see ODBC specification).
e.g.
$value = $dbh->func(6, 'GetInfo');
which returns the C<SQL_DRIVER_NAME>.
This function returns a scalar value, which can be a numeric or string
value depending on the information value requested.
=head3 GetTypeInfo
B<This private function is now superseded by DBI's type_info and
type_info_all methods however as it is used by those methods it
still exists.>
This function maps to the ODBC SQLGetTypeInfo API and the argument
should be a SQL type number (e.g. SQL_VARCHAR) or
SQL_ALL_TYPES. SQLGetTypeInfo returns information about a data type
supported by the data source.
e.g.
use DBI qw(:sql_types);
$sth = $dbh->func(SQL_ALL_TYPES, GetTypeInfo);
DBI::dump_results($sth);
This function returns a DBI statement handle for the SQLGetTypeInfo
result-set containing many columns of type attributes (see ODBC
specification).
NOTE: It is VERY important that the C<use DBI> includes the
C<qw(:sql_types)> so that values like SQL_VARCHAR are correctly
( run in 0.704 second using v1.01-cache-2.11-cpan-5837b0d9d2c )