DBD-ODBC
view release on metacpan or search on metacpan
submitting. Fixed.
1.08 2004-03-06
Added check in Makefile.PL to detect if the environment variable LANG is
Set. If so, prints a warning about potential makefile generation
issues.
Change to use dbivport.h per new DBI spec.
Add ability to set the cursor type during the connect. This may allow
some servers which do not support multiple concurrent statements to
permit them -- tested with SQL Server. Thanks to Martin Busik! See
odbc_cursortype information in the ODBC POD.
1.07 2004-02-19
Added to Subversion version control hosted by perl.org. Thanks Robert!
See ODBC.pm POD for more information.
Added contributing section to ODBC.pm POD -- see more details there!
Added parameter to odbc_errhandler for the NativeError -- thanks to
Martin Busik.
0.29 2001-08-22
Cygwin patches from Neil Lunn (untested by me). Thanks Neil!
SQL_ROWSET_SIZE attribute patch from Andrew Brown
There are only 2 additional lines allowing for the setting of
SQL_ROWSET_SIZE as db handle option.
The purpose to my madness is simple. SqlServer (7 anyway) by default
supports only one select statement at once (using std ODBC cursors).
According to the SqlServer documentation you can alter the default
setting of three values to force the use of server cursors - in which
case multiple selects are possible.
The code change allows for:
$dbh->{SQL_ROWSET_SIZE} = 2; # Any value > 1
For this very purpose.
The setting of SQL_ROWSET_SIZE only affects the extended fetch command
as far as I can work out and thus setting this option shouldn't affect
DBD::ODBC operations directly in any way.
* Handles ConfigDSN which the driver exports to configure data
sources.
* Provides APIs to install and uninstall drivers (SQLInstallDriver).
* Maps ODBC versions e.g. so an ODBC 2.0 application can work with an
ODBC 3.0 driver and vice versa.
* Maps ODBC states between different versions of ODBC.
* Provides a cursor library for drivers which only support
forward-only cursors.
* Provides SQLDataSources and SQLDrivers so an application can find
out what ODBC drivers are installed and what ODBC data sources are
defined.
* Provides an ODBC administrator which driver writers can use to
install ODBC drivers and users can use to define ODBC data sources.
The ODBC Driver Manager is the piece of software which interacts with
the drivers for the application. It "hides" some of the differences
Multiple Active Statements (MAS) are concurrent statements created
from the same database handle which both have pending actions on them
(e.g. they both have executed a select statement but not retrieved all
the available rows yet).
DBD::ODBC does support MAS but whether you can actually use MAS is
down to the ODBC Driver.
By default MS SQL Server did not used to support multiple active
statements if any of them were select statements. You could get around
this (with caution) by changing to a dynamic cursor. There is a "hack"
in DBD::ODBC which can be used to enable MAS but you have to fully
understand the implications of doing so(see
L<DBD::ODBC/odbc_SQL_ROWSET_SIZE> and L<DBD::ODBC/odbc_cursortype>).
In MS SQL Server 2005, there is a new thing called MARS (Multiple
Active Result Sets) which allows multiple active select statements but
it has some nasty implications if you are also doing transactions. To
enable MARS from DBD::ODBC add "MARS_Connection=Yes" to the connection
string as in:
$h->DBI->connect('dbi:ODBC:DSN=mydsn;MARS_Connection=Yes;');
NOTE: Even though you may be using MS SQL Server 2005 if you are using
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,
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.
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).
int dbd_st_finish(SV *sth, imp_sth_t *imp_sth)
{
dTHX;
D_imp_dbh_from_sth;
RETCODE rc;
if (DBIc_TRACE(imp_sth, DBD_TRACING, 0, 3))
TRACE1(imp_sth, " dbd_st_finish(%p)\n", sth);
/* Cancel further fetches from this cursor. */
/* We don't close the cursor till DESTROY (dbd_st_destroy). */
/* The application may re execute(...) it. */
/* XXX semantics of finish (eg oracle vs odbc) need lots more thought */
/* re-read latest DBI specs and ODBC manuals */
if (DBIc_ACTIVE(imp_sth) && imp_dbh->hdbc != SQL_NULL_HDBC) {
rc = SQLFreeStmt(imp_sth->hstmt, SQL_CLOSE);/* TBD: 3.0 update */
if (!SQL_SUCCEEDED(rc)) {
dbd_error(sth, rc, "finish/SQLFreeStmt(SQL_CLOSE)");
return 0;
{ "odbc_SQL_DRIVER_ODBC_VER", SQL_DRIVER_ODBC_VER, PARAM_READ, PARAM_TYPE_CUSTOM },
{ "odbc_SQL_ROWSET_SIZE", SQL_ROWSET_SIZE, PARAM_READWRITE, PARAM_TYPE_UINT },
{ "odbc_ignore_named_placeholders", ODBC_IGNORE_NAMED_PLACEHOLDERS, PARAM_READWRITE, PARAM_TYPE_CUSTOM },
{ "odbc_default_bind_type", ODBC_DEFAULT_BIND_TYPE, PARAM_READWRITE, PARAM_TYPE_CUSTOM },
{ "odbc_force_bind_type", ODBC_FORCE_BIND_TYPE, PARAM_READWRITE, PARAM_TYPE_CUSTOM },
{ "odbc_force_rebind", ODBC_FORCE_REBIND, PARAM_READWRITE, PARAM_TYPE_CUSTOM },
{ "odbc_async_exec", ODBC_ASYNC_EXEC, PARAM_READWRITE, PARAM_TYPE_CUSTOM },
{ "odbc_err_handler", ODBC_ERR_HANDLER, PARAM_READWRITE, PARAM_TYPE_CUSTOM },
{ "odbc_exec_direct", ODBC_EXEC_DIRECT, PARAM_READWRITE, PARAM_TYPE_CUSTOM },
{ "odbc_version", ODBC_VERSION, PARAM_READWRITE, PARAM_TYPE_CUSTOM },
{ "odbc_cursortype", ODBC_CURSORTYPE, PARAM_READWRITE, PARAM_TYPE_CUSTOM },
{ "odbc_query_timeout", ODBC_QUERY_TIMEOUT, PARAM_READWRITE, PARAM_TYPE_CUSTOM },
{ "odbc_putdata_start", ODBC_PUTDATA_START, PARAM_READWRITE, PARAM_TYPE_CUSTOM },
{ "odbc_column_display_size", ODBC_COLUMN_DISPLAY_SIZE, PARAM_READWRITE, PARAM_TYPE_CUSTOM },
{ "odbc_utf8_on", ODBC_UTF8_ON, PARAM_READWRITE, PARAM_TYPE_CUSTOM },
{ "odbc_has_unicode", ODBC_HAS_UNICODE, PARAM_READ, PARAM_TYPE_CUSTOM },
{ "odbc_out_connect_string", ODBC_OUTCON_STR, PARAM_READ, PARAM_TYPE_CUSTOM},
{ "odbc_describe_parameters", ODBC_DESCRIBE_PARAMETERS, PARAM_READWRITE, PARAM_TYPE_CUSTOM },
{ "odbc_batch_size", ODBC_BATCH_SIZE, PARAM_READWRITE, PARAM_TYPE_CUSTOM },
{ "odbc_array_operations", ODBC_ARRAY_OPERATIONS, PARAM_READWRITE, PARAM_TYPE_CUSTOM },
{ "odbc_taf_callback", ODBC_TAF_CALLBACK, PARAM_READWRITE, PARAM_TYPE_CUSTOM },
/* */
/*======================================================================*/
SV *dbd_st_FETCH_attrib(SV *sth, imp_sth_t *imp_sth, SV *keysv)
{
dTHX;
STRLEN kl;
char *key = SvPV(keysv,kl);
int i;
SV *retsv = NULL;
T_st_params *par;
char cursor_name[256];
SWORD cursor_name_len;
RETCODE rc;
for (par = S_st_fetch_params; par->len > 0; par++)
if (par->len == kl && strEQ(key, par->str))
break;
if (par->len <= 0)
return Nullsv;
while(--i >= 0)
av_store(av, i, newSViv(imp_sth->fbh[i].ColSqlType));
break;
case 8: /* sol_length */
av = newAV();
retsv = newRV_inc(sv_2mortal((SV*)av));
while(--i >= 0)
av_store(av, i, newSViv(imp_sth->fbh[i].ColLength));
break;
case 9: /* CursorName */
rc = SQLGetCursorName(imp_sth->hstmt, cursor_name,
sizeof(cursor_name), &cursor_name_len);
if (!SQL_SUCCEEDED(rc)) {
dbd_error(sth, rc, "st_FETCH/SQLGetCursorName");
return Nullsv;
}
retsv = newSVpv(cursor_name, cursor_name_len);
break;
case 10: /* odbc_more_results */
retsv = newSViv(imp_sth->moreResults);
if (i == 0 && imp_sth->moreResults == 0) {
int outparams = (imp_sth->out_params_av) ?
AvFILL(imp_sth->out_params_av)+1 : 0;
if (DBIc_TRACE(imp_sth, DBD_TRACING, 0, 4)) {
TRACE0(imp_sth,
" numfields == 0 && moreResults = 0 finish\n");
}
int ftype)
{
dTHX;
D_imp_dbh(dbh);
D_imp_sth(sth);
RETCODE rc;
int dbh_active;
size_t max_stmt_len;
#if 0
/* TBD: cursorname? */
char cname[128]; /* cursorname */
#endif
imp_sth->henv = imp_dbh->henv; /* needed for dbd_error */
imp_sth->hdbc = imp_dbh->hdbc;
imp_sth->done_desc = 0;
if ((dbh_active = check_connection_active(aTHX_ dbh)) == 0) return 0;
rc = SQLAllocHandle(SQL_HANDLE_STMT, imp_dbh->hdbc, &imp_sth->hstmt);
/*
* post_connect
* ==========
*
* Operations to perform immediately after we have connected.
*
* NOTE: prior to DBI subversion version 11605 (fixed post 1.607)
* DBD_ATTRIB_DELETE segfaulted so instead of calling:
* DBD_ATTRIB_DELETE(attr, "odbc_cursortype",
* strlen("odbc_cursortype"));
* we do the following:
* hv_delete((HV*)SvRV(attr), "odbc_cursortype",
* strlen("odbc_cursortype"), G_DISCARD);
*/
static int post_connect(
pTHX_
SV *dbh,
imp_dbh_t *imp_dbh,
SV *attr)
{
D_imp_drh_from_dbh;
SQLRETURN rc;
} else {
imp_dbh->odbc_sqldescribeparam_supported = 0;
if (DBIc_TRACE(imp_dbh, CONNECTION_TRACING, 0, 0))
TRACE0(imp_dbh,
" !!SQLGetFunctions(SQL_API_SQLDESCRIBEPARAM) failed:\n");
AllODBCErrors(imp_dbh->henv, imp_dbh->hdbc, 0,
DBIc_TRACE(imp_dbh, DBD_TRACING, 0, 3),
DBIc_LOGPIO(imp_dbh));
}
/* odbc_cursortype */
{
SV **svp;
UV odbc_cursortype = 0;
DBD_ATTRIB_GET_IV(attr, "odbc_cursortype", 15,
svp, odbc_cursortype);
if (svp && odbc_cursortype) {
if (DBIc_TRACE(imp_dbh, CONNECTION_TRACING, 0, 0))
TRACE1(imp_dbh,
" Setting cursor type to: %"UVuf"\n", odbc_cursortype);
/* delete odbc_cursortype so we don't see it again via STORE */
(void)hv_delete((HV*)SvRV(attr), "odbc_cursortype",
strlen("odbc_cursortype"), G_DISCARD);
rc = SQLSetConnectAttr(imp_dbh->hdbc,(SQLINTEGER)SQL_CURSOR_TYPE,
(SQLPOINTER)odbc_cursortype,
(SQLINTEGER)SQL_IS_INTEGER);
if (!SQL_SUCCEEDED(rc) && (DBIc_TRACE(imp_dbh, CONNECTION_TRACING, 0, 0)))
TRACE1(imp_dbh, " !!Failed to set SQL_CURSORTYPE to %d\n",
(int)odbc_cursortype);
}
}
/* odbc_query_timeout */
{
SV **svp;
UV odbc_timeout = 0;
DBD_ATTRIB_GET_IV(
attr, "odbc_query_timeout", strlen("odbc_query_timeout"),
svp, odbc_timeout);
if (svp && odbc_timeout) {
imp_dbh->odbc_query_timeout = odbc_timeout;
if (DBIc_TRACE(imp_dbh, CONNECTION_TRACING, 0, 0))
TRACE1(imp_dbh, " Setting DBH query timeout to %d\n",
(int)odbc_timeout);
/* delete odbc_cursortype so we don't see it again via STORE */
(void)hv_delete((HV*)SvRV(attr), "odbc_query_timeout",
strlen("odbc_query_timeout"), G_DISCARD);
}
}
/* odbc_putdata_start */
{
SV **svp;
IV putdata_start_value;
examples/multiple_active_stmts.pl view on Meta::CPAN
$attrs,
);
},
MARs => sub {
local $connect_args{DSN} = $connect_args{DSN} . ';MARS_Connection=Yes';
DBI->connect (
(map { $connect_args{$_} } (qw/DSN USER PASS/) ),
$attrs,
);
},
server_cursors_hack => sub {
DBI->connect (
(map { $connect_args{$_} } (qw/DSN USER PASS/) ),
{ %$attrs, odbc_SQL_ROWSET_SIZE => 2 },
);
},
cursor_type => sub {
DBI->connect (
(map { $connect_args{$_} } (qw/DSN USER PASS/) ),
{ %$attrs, odbc_cursortype => 2 },
);
},
};
for (sort keys %$dbhmakers) {
print "\n\nTrying with $_\n";
my $dbh = $dbhmakers->{$_}->();
$dbh->{odbc_SQL_ROWSET_SIZE} = 2;
eval { $dbh->do ('DROP TABLE test_foo') };
examples/thrtest.pl view on Meta::CPAN
use DBI ;
use Carp ;
use Carp::Heavy ;
sub dotests
{
my ($doerr, $count) = @_ ;
my $dbh = undef ;
my $cursor1 = undef ;
my $cursor2 = undef ;
my $cursor3 = undef ;
my $action ;
my $tid = threads -> tid() ;
my $concnt = 0 ;
my $discnt = 0 ;
my $half = $count / 2 ;
print "start tid = $tid\n" ;
#DBI -> trace (3) ;
$login = '' ;
examples/thrtest.pl view on Meta::CPAN
my $action = int(rand() * 10) ;
print "--> #$tid action = $action count = $count doerr = $doerr\n" ;
if ($action == 0 && $doerr )
{
# create a syntax error
my $sth = $dbh->prepare("SELECT userID, authHandler FROM") ;
die "no error" if (!$DBI::errstr) ;
}
elsif ($action == 1 && !$cursor1)
{
$cursor1 -> finish if ($cursor1) ;
$cursor1 = $dbh->prepare("SELECT userID, authHandler, password
FROM thrtest1 WHERE login = ? and locked IS NULL
ORDER BY password");
die "db error $DBI::errstr" if (!$doerr && $DBI::errstr) ;
}
elsif ($action == 2 && !$cursor2)
{
$cursor2 -> finish if ($cursor2) ;
$cursor2 = $dbh->prepare("SELECT authMode, data FROM
thrtest2 WHERE handlerID = ?");
die "db error $DBI::errstr" if (!$doerr && $DBI::errstr) ;
}
elsif ($action == 3 && !$cursor3)
{
$cursor3 -> finish if ($cursor3) ;
$cursor3 = $dbh->prepare("UPDATE thrtest2 SET lastLogin =
now() WHERE userID = ?");
die "db error $DBI::errstr" if (!$doerr && $DBI::errstr) ;
}
elsif ($action == 4 && $cursor1 && $login)
{
#$cursor1 -> finish if ($cursor1) ;
#$cursor1 = $dbh->prepare("SELECT userID, authHandler, password
# FROM thrtest1 WHERE login = ? and locked IS NULL
# ORDER BY password");
#
$cursor1->execute($login) ;
$cursor1->bind_columns(\($userID, $authHandler, $passwd));
$cursor1->fetch;
die "**** user is = $userID, should = $id" if ($id ne $userID) ;
die "**** db error $DBI::errstr" if (!$doerr && $DBI::errstr) ;
}
elsif ($action == 5 && $authHandler && $cursor2)
{
# $cursor2 -> finish if ($cursor2) ;
# $cursor2 = $dbh->prepare("SELECT authMode, data FROM
# thrtest2 WHERE handlerID = ?");
$cursor2->execute($authHandler) ;
$cursor2->bind_columns(\($authMode, $data));
$cursor2->fetch;
die "**** mode is = $authMode, should = $mode for $authHandler (login=$login)" if ($mode ne $authMode) ;
die "**** db error $DBI::errstr" if (!$doerr && $DBI::errstr) ;
}
elsif ($action == 6)
{
$cursor3 = undef ;
}
elsif ($action == 7)
{
$cursor2 = undef ;
}
elsif ($action == 8)
{
$cursor1 = undef ;
}
elsif ($action == 9)
{
$cursor3 = undef ;
$cursor2 = undef ;
$cursor1 = undef ;
if ($discnt++ % 10 == 0)
{
$dbh ->disconnect ;
die "db error $DBI::errstr" if (!$doerr && $DBI::errstr) ;
$dbh = undef ;
}
my $i = int(rand() * 3) ;
$login = ('richter', 'test', 'XX')[$i] ;
$id = ('gr', 'tt', 'xx')[$i] ;
$mode = ('Windows', 'Windows', '')[$i] ;
t/20SqlServer.t view on Meta::CPAN
$dbh = DBI->connect($dsn, $ENV{DBI_USER}, $ENV{DBI_PASS}, {PrintError => 0});
ok($dbh, "Connected with MARS_Connection");
diag("$DBI::errstr\n$dsn\n") if !$dbh;
SKIP: {
skip "could not connect with MARS_Connection attribute", 1 if !$dbh;
ok(!&Multiple_concurrent_stmts($dbh, 0), "Multiple concurrent statements should fail");
$dbh->disconnect;
};
$dbh = DBI->connect($dsn, $ENV{DBI_USER}, $ENV{DBI_PASS}, { odbc_cursortype => 2, PrintError => 0 });
# $dbh->{odbc_err_handler} = \&err_handler;
ok(&Multiple_concurrent_stmts($dbh, 1), "Multiple concurrent statements succeed (odbc_cursortype set)");
SKIP: {
skip "MS SQL Server version < 9", 1 if ($m_dbmsversion < 9);
$dbh->disconnect; # throw away non-mars connection
$dsn = "$base_dsn;MARS_Connection=yes;";
$dbh = DBI->connect($dsn, $ENV{DBI_USER}, $ENV{DBI_PASS}, {PrintError => 0});
my $tst = "Multiple concurrent statements succeed with MARS";
if (&Multiple_concurrent_stmts($dbh,1)) {
pass($tst);
} else {
t/rt_62033.t view on Meta::CPAN
# after the above if it errors. When we call odbc_more_results it actually
# ends up doing a SQLDescribe. For most drivers I've tested they
# are ok with this but a few (freeTDS) are not. The problem with freeTDS
# is that if you then omit the SQLMoreResults and continue with this test
# you'll get an SQL_ERROR from the next execute without an error msg
# so it would seem there is no way to make this work in freeTDS as it
# stands.
#
# Some drivers (basically all those I've tested except freeTDS) need you
# to call SQLMoreResults even if the above fails or you'll get invalid
# cursor state on the next statement (MS SQL Server and MS native client
# driver).
if ($s->{NUM_OF_FIELDS} == 0) {
my $x = $s->{odbc_more_results};
}
if ($expect) {
# for the error case where we attempt to insert a NULL into column b
# we'd expect odbc_more_results to return 0/false - there are no more
# results
( run in 0.371 second using v1.01-cache-2.11-cpan-4d50c553e7e )