DBD::ODBC 1.14 does not work on Windows x64 using Microsoft ODBC drivers

I have Perl 5.8.8 built for Windows x64 from Activestate (version 822)
installed on a Windows 2003 x64 bit server (Xeon processor).

I have installed version 1.59 of DBI and version 1.14 of DBD::ODBC compiled
for x64 bit, using the freely available Microsoft SDK.

The ODBC-ODBC bridge used is supposed to be for 64 bit, as described by
Microsoft.

I ran the attached test script and get an error:
DBD::ODBC::st execute failed:
[Microsoft][ODBC SQL Server Driver]Invalid string or buffer length
(SQL-HY090)(DBD: st_execute/SQLExecute err=-1) at test_dbd-odbc.pl line 48.

This seems to be due to passing an undef value as an argument in the
DBI->execute() call.

The same script runs flawlessly on a in32.
The script also runs flawlessly if I use the DBD::mysql driver built for
Windows x64 bit.

Is this a bug in DBD::ODBC or in the Microsoft ODBC driver?

Attached script:
#!/usr/bin/perl
#
# Test script for DBD::ODBC for 64/32 bit Windows

use DBI;


# define $user and $password for your database
my $dsn = "dbi:ODBC:my_database_schema";


my $dbh;
eval {
$dbh = DBI->connect($dsn, $user, $password,
{ AutoCommit => 0, RaiseError => 0});

if (! defined $dbh) {
print "Cannot connect to database with DSN = $dsn";
exit(0);
} else {
print "Connected to SQL Server with parameters:\n",
" DSN = $dsn\n",
" User = $user\n",
" Password = ******\n";
}

my $drop_stmt = "if object_id(N'my_test',N'U') is not null " .
"drop table my_test";
$sth = $dbh->prepare($drop_stmt);
$sth->execute();

# create a table with one key and all other columns optional
my $create_stmt =
'create table my_test ' .
' (col nvarchar(80) not null,' .
' str nvarchar(20))';
my $sth = $dbh->prepare($create_stmt);
$sth->execute();

# insert a simple row with three nulls
my $value = undef;
my $insert_one =
'insert into my_test (col, str) values (?, ?)';
$sth = $dbh->prepare($insert_one);
$sth->execute('This is my key!', $value);

# on Windows x64 the above statement fails because of the last argument
$dbh->commit();

my $key;
my $str;
my $select = 'select col, str from my_test';
$sth = $dbh->prepare($select);
$sth->execute();

$sth->bind_col(1, \$key);
$sth->bind_col(2, \$str);
while ($sth->fetch) {
print "Retrieved ";
if (! defined $str) {
$str = "";
}
print "($key, $str) ";
print " from database table\n";
}


$dbh->commit();

$dbh->disconnect;
};
if ($@) {
print "Error in connecting to the database DSN: $dsn\n";
if (defined $dbh) {
$dbh->rollback();
print "with error " . $dbh->errstr() . "\n";
}
}

1;

Re: DBD::ODBC 1.14 does not work on Windows x64 using Microsoft ODBC drivers

Nelson Oliveira wrote:
> I have Perl 5.8.8 built for Windows x64 from Activestate (version 822)
> installed on a Windows 2003 x64 bit server (Xeon processor).
>
> I have installed version 1.59 of DBI and version 1.14 of DBD::ODBC compiled
> for x64 bit, using the freely available Microsoft SDK.
>
> The ODBC-ODBC bridge used is supposed to be for 64 bit, as described by
> Microsoft.

Could you tell me what this "ODBC-ODBC bridge" is and where you found it?

> I ran the attached test script and get an error:
> DBD::ODBC::st execute failed:
> [Microsoft][ODBC SQL Server Driver]Invalid string or buffer length
> (SQL-HY090)(DBD: st_execute/SQLExecute err=-1) at test_dbd-odbc.pl line 48.
>
> This seems to be due to passing an undef value as an argument in the
> DBI->execute() call.
>
> The same script runs flawlessly on a in32.
> The script also runs flawlessly if I use the DBD::mysql driver built for
> Windows x64 bit.
>
> Is this a bug in DBD::ODBC or in the Microsoft ODBC driver?
>
> Attached script:
> #!/usr/bin/perl
> #
> # Test script for DBD::ODBC for 64/32 bit Windows
>
> use DBI;
>
>
> # define $user and $password for your database
> my $dsn = "dbi:ODBC:my_database_schema";
>
>
> my $dbh;
> eval {
> $dbh = DBI->connect($dsn, $user, $password,
> { AutoCommit => 0, RaiseError => 0});
>
> if (! defined $dbh) {
> print "Cannot connect to database with DSN = $dsn";
> exit(0);
> } else {
> print "Connected to SQL Server with parameters:\n",
> " DSN = $dsn\n",
> " User = $user\n",
> " Password = ******\n";
> }
>
> my $drop_stmt = "if object_id(N'my_test',N'U') is not null " .
> "drop table my_test";
> $sth = $dbh->prepare($drop_stmt);
> $sth->execute();
>
> # create a table with one key and all other columns optional
> my $create_stmt =
> 'create table my_test ' .
> ' (col nvarchar(80) not null,' .
> ' str nvarchar(20))';
> my $sth = $dbh->prepare($create_stmt);
> $sth->execute();
>
> # insert a simple row with three nulls
> my $value = undef;
> my $insert_one =
> 'insert into my_test (col, str) values (?, ?)';
> $sth = $dbh->prepare($insert_one);
> $sth->execute('This is my key!', $value);
>
> # on Windows x64 the above statement fails because of the last argument
> $dbh->commit();
>
> my $key;
> my $str;
> my $select = 'select col, str from my_test';
> $sth = $dbh->prepare($select);
> $sth->execute();
>
> $sth->bind_col(1, \$key);
> $sth->bind_col(2, \$str);
> while ($sth->fetch) {
> print "Retrieved ";
> if (! defined $str) {
> $str = "";
> }
> print "($key, $str) ";
> print " from database table\n";
> }
>
>
> $dbh->commit();
>
> $dbh->disconnect;
> };
> if ($@) {
> print "Error in connecting to the database DSN: $dsn\n";
> if (defined $dbh) {
> $dbh->rollback();
> print "with error " . $dbh->errstr() . "\n";
> }
> }
>
> 1;
>
>

Microsoft changed their ODBC sql header files when they released 64 bit
windows so some of the ODBC APIs use a new SQLULEN which is 64 bits
where it was 32 bits before. My guess is that this is causing your
problem. I cannot at present build DBD::ODBC for ActiveState (or even
Perl for that matter) on Windows because the MS Visual Studio Express
won't build it. I believe the APIs affected are:

SQLDescribeParam - 4th arg
SQLBindParameter - 6th arg
SQLBindParam - 5th arg
SQLDescribeCol - 7th arg
SQLSetConnectOption - 3rd arg
SQLSetParam - 5th arg

Of course, DBD::ODBC only uses a few of these presently. You will
probably need to to at least change calls to SQLDescribeParam,
SQLBindParameter and SQLDescribeCol for your precise situation such that
the lengths use a SQLULEN/SQLLEN instead of SQLUINTEGER and SQLINTEGER
especially where a pointer to that type is passed.

I'm sorry I cannot help more at this stage but if you are in a position
to make those changes and confirm they work I'm happy to accept patches
until I can get hold of a 64bit windows machine and a working compiler.

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

Re: DBD::ODBC 1.14 does not work on Windows x64 using Microsoft ODBC drivers

Thanks!

Your guess was right. I changed the datatypes from a 4 byte type to
an 8 byte type in several places where DBD::ODBC interfaces with the
ODBC functions. I compiled it for WIN64 and ran the test suite in DBD::ODBC,
which for the most part reported everything ok. My test script runs and
more complex software we have here seems to run flawlessly as well.

I will send you the changed files directly to you in a separate email.

Nelson Oliveira
The Medea Group
Toronto, Ontario

Martin Evans wrote:

> Nelson Oliveira wrote:
>> I have Perl 5.8.8 built for Windows x64 from Activestate (version 822)
>> installed on a Windows 2003 x64 bit server (Xeon processor).
>>
>> I have installed version 1.59 of DBI and version 1.14 of DBD::ODBC
>> compiled
>> for x64 bit, using the freely available Microsoft SDK.
>>
>> The ODBC-ODBC bridge used is supposed to be for 64 bit, as described by
>> Microsoft.
>
> Could you tell me what this "ODBC-ODBC bridge" is and where you found it?

This is the Microsoft ODBC driver for SQL Server. It's found under
Control Panel->Administrative tools->ODBC sources

>
>> I ran the attached test script and get an error:
>> DBD::ODBC::st execute failed:
>> [Microsoft][ODBC SQL Server Driver]Invalid string or buffer length
>> (SQL-HY090)(DBD: st_execute/SQLExecute err=-1) at test_dbd-odbc.pl line
>> 48.
>>
>> This seems to be due to passing an undef value as an argument in the
>> DBI->execute() call.
>>
>> The same script runs flawlessly on a in32.
>> The script also runs flawlessly if I use the DBD::mysql driver built for
>> Windows x64 bit.
>>
>> Is this a bug in DBD::ODBC or in the Microsoft ODBC driver?
>>
>> Attached script:
>> #!/usr/bin/perl
>> #
>> # Test script for DBD::ODBC for 64/32 bit Windows
>>
>> use DBI;
>>
>>
>> # define $user and $password for your database
>> my $dsn = "dbi:ODBC:my_database_schema";
>>
>>
>> my $dbh;
>> eval {
>> $dbh = DBI->connect($dsn, $user, $password,
>> { AutoCommit => 0, RaiseError => 0});
>>
>> if (! defined $dbh) {
>> print "Cannot connect to database with DSN = $dsn";
>> exit(0);
>> } else {
>> print "Connected to SQL Server with parameters:\n",
>> " DSN = $dsn\n",
>> " User = $user\n",
>> " Password = ******\n";
>> }
>>
>> my $drop_stmt = "if object_id(N'my_test',N'U') is not null " .
>> "drop table my_test";
>> $sth = $dbh->prepare($drop_stmt);
>> $sth->execute();
>>
>> # create a table with one key and all other columns optional
>> my $create_stmt =
>> 'create table my_test ' .
>> ' (col nvarchar(80) not null,' .
>> ' str nvarchar(20))';
>> my $sth = $dbh->prepare($create_stmt);
>> $sth->execute();
>>
>> # insert a simple row with three nulls
>> my $value = undef;
>> my $insert_one =
>> 'insert into my_test (col, str) values (?, ?)';
>> $sth = $dbh->prepare($insert_one);
>> $sth->execute('This is my key!', $value);
>>
>> # on Windows x64 the above statement fails because of the last
>> argument
>> $dbh->commit();
>>
>> my $key;
>> my $str;
>> my $select = 'select col, str from my_test';
>> $sth = $dbh->prepare($select);
>> $sth->execute();
>>
>> $sth->bind_col(1, \$key);
>> $sth->bind_col(2, \$str);
>> while ($sth->fetch) {
>> print "Retrieved ";
>> if (! defined $str) {
>> $str = "";
>> }
>> print "($key, $str) ";
>> print " from database table\n";
>> }
>>
>>
>> $dbh->commit();
>>
>> $dbh->disconnect;
>> };
>> if ($@) {
>> print "Error in connecting to the database DSN: $dsn\n";
>> if (defined $dbh) {
>> $dbh->rollback();
>> print "with error " . $dbh->errstr() . "\n";
>> }
>> }
>>
>> 1;
>>
>>
>
> Microsoft changed their ODBC sql header files when they released 64 bit
> windows so some of the ODBC APIs use a new SQLULEN which is 64 bits
> where it was 32 bits before. My guess is that this is causing your
> problem. I cannot at present build DBD::ODBC for ActiveState (or even
> Perl for that matter) on Windows because the MS Visual Studio Express
> won't build it. I believe the APIs affected are:
>
> SQLDescribeParam - 4th arg
> SQLBindParameter - 6th arg
> SQLBindParam - 5th arg
> SQLDescribeCol - 7th arg
> SQLSetConnectOption - 3rd arg
> SQLSetParam - 5th arg
>
> Of course, DBD::ODBC only uses a few of these presently. You will
> probably need to to at least change calls to SQLDescribeParam,
> SQLBindParameter and SQLDescribeCol for your precise situation such that
> the lengths use a SQLULEN/SQLLEN instead of SQLUINTEGER and SQLINTEGER
> especially where a pointer to that type is passed.
>
> I'm sorry I cannot help more at this stage but if you are in a position
> to make those changes and confirm they work I'm happy to accept patches
> until I can get hold of a 64bit windows machine and a working compiler.
>
> Martin
> --
> Martin J. Evans
> Easysoft Limited
> http://www.easysoft.com
>
>

Re: DBD::ODBC 1.14 does not work on Windows x64 using Microsoft ODBC drivers

Nelson Oliveira wrote:
> Thanks!
>
> Your guess was right. I changed the datatypes from a 4 byte type to
> an 8 byte type in several places where DBD::ODBC interfaces with the
> ODBC functions. I compiled it for WIN64 and ran the test suite in DBD::ODBC,
> which for the most part reported everything ok. My test script runs and
> more complex software we have here seems to run flawlessly as well.
>
> I will send you the changed files directly to you in a separate email.
>
> Nelson Oliveira
> The Medea Group
> Toronto, Ontario

Thank you.

I look forward to them and will make the necessary changes in subversion
for the next release.

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

> Martin Evans wrote:
>
>> Nelson Oliveira wrote:
>>> I have Perl 5.8.8 built for Windows x64 from Activestate (version 822)
>>> installed on a Windows 2003 x64 bit server (Xeon processor).
>>>
>>> I have installed version 1.59 of DBI and version 1.14 of DBD::ODBC
>>> compiled
>>> for x64 bit, using the freely available Microsoft SDK.
>>>
>>> The ODBC-ODBC bridge used is supposed to be for 64 bit, as described by
>>> Microsoft.
>> Could you tell me what this "ODBC-ODBC bridge" is and where you found it?
>
> This is the Microsoft ODBC driver for SQL Server. It's found under
> Control Panel->Administrative tools->ODBC sources
>
>>> I ran the attached test script and get an error:
>>> DBD::ODBC::st execute failed:
>>> [Microsoft][ODBC SQL Server Driver]Invalid string or buffer length
>>> (SQL-HY090)(DBD: st_execute/SQLExecute err=-1) at test_dbd-odbc.pl line
>>> 48.
>>>
>>> This seems to be due to passing an undef value as an argument in the
>>> DBI->execute() call.
>>>
>>> The same script runs flawlessly on a in32.
>>> The script also runs flawlessly if I use the DBD::mysql driver built for
>>> Windows x64 bit.
>>>
>>> Is this a bug in DBD::ODBC or in the Microsoft ODBC driver?
>>>
>>> Attached script:
>>> #!/usr/bin/perl
>>> #
>>> # Test script for DBD::ODBC for 64/32 bit Windows
>>>
>>> use DBI;
>>>
>>>
>>> # define $user and $password for your database
>>> my $dsn = "dbi:ODBC:my_database_schema";
>>>
>>>
>>> my $dbh;
>>> eval {
>>> $dbh = DBI->connect($dsn, $user, $password,
>>> { AutoCommit => 0, RaiseError => 0});
>>>
>>> if (! defined $dbh) {
>>> print "Cannot connect to database with DSN = $dsn";
>>> exit(0);
>>> } else {
>>> print "Connected to SQL Server with parameters:\n",
>>> " DSN = $dsn\n",
>>> " User = $user\n",
>>> " Password = ******\n";
>>> }
>>>
>>> my $drop_stmt = "if object_id(N'my_test',N'U') is not null " .
>>> "drop table my_test";
>>> $sth = $dbh->prepare($drop_stmt);
>>> $sth->execute();
>>>
>>> # create a table with one key and all other columns optional
>>> my $create_stmt =
>>> 'create table my_test ' .
>>> ' (col nvarchar(80) not null,' .
>>> ' str nvarchar(20))';
>>> my $sth = $dbh->prepare($create_stmt);
>>> $sth->execute();
>>>
>>> # insert a simple row with three nulls
>>> my $value = undef;
>>> my $insert_one =
>>> 'insert into my_test (col, str) values (?, ?)';
>>> $sth = $dbh->prepare($insert_one);
>>> $sth->execute('This is my key!', $value);
>>>
>>> # on Windows x64 the above statement fails because of the last
>>> argument
>>> $dbh->commit();
>>>
>>> my $key;
>>> my $str;
>>> my $select = 'select col, str from my_test';
>>> $sth = $dbh->prepare($select);
>>> $sth->execute();
>>>
>>> $sth->bind_col(1, \$key);
>>> $sth->bind_col(2, \$str);
>>> while ($sth->fetch) {
>>> print "Retrieved ";
>>> if (! defined $str) {
>>> $str = "";
>>> }
>>> print "($key, $str) ";
>>> print " from database table\n";
>>> }
>>>
>>>
>>> $dbh->commit();
>>>
>>> $dbh->disconnect;
>>> };
>>> if ($@) {
>>> print "Error in connecting to the database DSN: $dsn\n";
>>> if (defined $dbh) {
>>> $dbh->rollback();
>>> print "with error " . $dbh->errstr() . "\n";
>>> }
>>> }
>>>
>>> 1;
>>>
>>>
>> Microsoft changed their ODBC sql header files when they released 64 bit
>> windows so some of the ODBC APIs use a new SQLULEN which is 64 bits
>> where it was 32 bits before. My guess is that this is causing your
>> problem. I cannot at present build DBD::ODBC for ActiveState (or even
>> Perl for that matter) on Windows because the MS Visual Studio Express
>> won't build it. I believe the APIs affected are:
>>
>> SQLDescribeParam - 4th arg
>> SQLBindParameter - 6th arg
>> SQLBindParam - 5th arg
>> SQLDescribeCol - 7th arg
>> SQLSetConnectOption - 3rd arg
>> SQLSetParam - 5th arg
>>
>> Of course, DBD::ODBC only uses a few of these presently. You will
>> probably need to to at least change calls to SQLDescribeParam,
>> SQLBindParameter and SQLDescribeCol for your precise situation such that
>> the lengths use a SQLULEN/SQLLEN instead of SQLUINTEGER and SQLINTEGER
>> especially where a pointer to that type is passed.
>>
>> I'm sorry I cannot help more at this stage but if you are in a position
>> to make those changes and confirm they work I'm happy to accept patches
>> until I can get hold of a 64bit windows machine and a working compiler.
>>
>> Martin
>> --
>> Martin J. Evans
>> Easysoft Limited
>> http://www.easysoft.com
>>
>>
>
>
>