Re: MsSQL DBD::ODBC IsNull and undef
- From: martin.evans@xxxxxxxxxxxx (Martin J. Evans)
- Date: Tue, 26 Jul 2005 08:21:39 +0100
Brian,
Looks like the NULL is getting to the SQL Server ODBC driver to me - see below.
Brian Becker wrote:
Yes I am referring to the ODBC-ODBC Bridge.
Here is the esoobclient.log
<sniiped>
SQLBindParameter(2bfd88,1,1(Input::),1,12,1,0,0,0,2c17e8) ^oobc_expand_desc_recs(2bfe98,1) Expanding 0, n_descrec=0, expanding to 2 ^-oobc_expand_desc_recs()=2c18a4 ^oobc_expand_desc_recs(2bffd8,1) Expanding 0, n_descrec=0, expanding to 2 ^-oobc_expand_desc_recs()=2c19e4 Passthru:1 -SQLBindParameter(...)=0
parameter bound as a SQL_C_CHAR
SQLExecute(2bfd88) ^put_bound_parameters(2bfd88,27a4f0,1) Driver supports SQLNumParams : 1 SQLNumParams returns 1 parameters Processing parameter 1 of 1(1) Initial DataPtr=0, OctetLengthPtr=2c17e8(-1), IndicatorPtr=2c17e8(-1) No DataPtr - looking at indicator Indicator = -1 -^put_bound_parameters()=0
and the indicator says it is a NULL.
<snipped>
SQLFetch(2bfd88) ^fetch_bound_columns(2bfd88,27a4f0) ^fetch_column_descriptions(27a4f0,2bfd88,ffbff086) -^fetch_column_descriptions()=SQL_SUCCESS (highest result set column = 14)
<snipped>
and SQLFetch returns rows.
SQLPrepare(2c6558,2c9210,69) SQLPrepare:
select * from ORders where CustomerID = isnull(?,'VINET')
SQLBindParameter(2c6558,1,1(Input::),1,12,1,0,0,0,2c9290) ^oobc_expand_desc_recs(2c6668,1) Expanding 0, n_descrec=0, expanding to 2 ^-oobc_expand_desc_recs()=2c934c ^oobc_expand_desc_recs(2c7ad0,1) Expanding 0, n_descrec=0, expanding to 2 ^-oobc_expand_desc_recs()=2c948c Passthru:1 -SQLBindParameter(...)=0
as before
SQLExecute(2c6558) ^put_bound_parameters(2c6558,27a4f0,1) SQLNumParams returns 1 parameters Processing parameter 1 of 1(1) Initial DataPtr=0, OctetLengthPtr=2c9290(-1), IndicatorPtr=2c9290(-1) No DataPtr - looking at indicator Indicator = -1 -^put_bound_parameters()=0 -SQLExecute(...)=0
parameter is a NULL.
<snipped>
SQLFetch(2c6558) -SQLFetch(...)=100
no rows returned.
In both cases I'd say the NULL is getting to SQL Server. If you get hold of odbctest which used to be in the ODBC SDK you should be able to run this directly to the MS SQL Server driver on Windows. If I can squeeze some time today I'll try it here.
Martin
-----Original Message-----
From: Martin J. Evans [mailto:martin.evans@xxxxxxxxxxxx] Sent: Monday, July 25, 2005 12:59 PM
To: dbi-users@xxxxxxxx
Subject: RE: MsSQL DBD::ODBC IsNull and undef
Brian,
I presume by "Easysoft" you mean Easysoft ODBC-ODBC Bridge?
If so, can you run your test script again but creating an OOB log file which you can do by:
1. cd /tmp 2. create odbc.ini file containing only:
{Settings} Logging = 0xffffff
NOTE, they are curly braces.
3. WITHOUT changing directory, run you Perl script
perl /path_to_my_script/xxx.pl
4. send me the resulting esoobclient.log_<PID> which will appear in /tmp (where <PID> is the process ID).
Martin -- Martin J. Evans Easysoft Ltd, UK Development
On 25-Jul-2005 Brian Becker wrote:
I am trying to bind an undef into an IsNull on a varchar field. When
I
write the query binding directly into the IsNull I get no records, yet if I create a SQL variable, bind the undef to that, and then use the
SQL
variable - I get results. I have created a test script that
duplicates
the problem (sorry the test is not the best but it does illustrate the problem). The test script is done off of the Northwind database for
SQL
Server 2000.
Software: Easysoft unixODBC-2.2.11 perl, v5.8.6 built for sun4-solaris DBD-ODBC-1.13 DBI-1.48 SQL Server 2000
Test Script:
#!perl -w -I./t
use Test::More; $| = 1;
use_ok('DBI', qw(:sql_types)); use_ok('ODBCTEST');
# to help ActiveState's build process along by behaving (somewhat) if a dsn is not provided BEGIN { if (!defined $ENV{DBI_DSN}) { plan skip_all => "DBI_DSN is undefined"; } else { plan tests =>4; } }
#DBI->trace(2); my $dbh = DBI->connect(); unless($dbh) { # BAILOUT("Unable to connect to the database ($DBI::errstr)\nTests skipped.\n"); exit 0; }
$dbh->{AutoCommit} = 1;
#### testing a simple select
my $rc = 0; my $CustomerID; my $sql=" Declare \@CustomerID varchar(20) SET \@CustomerID=? select * from ORders where CustomerID = isnull(\@CustomerID,'VINET') "; my $sth = $dbh->prepare(qq{$sql}); $sth->bind_param(1,$CustomerID); $sth->execute(); my $RowCount=0; while(my $row = $sth->fetchrow_hashref()) { $RowCount++; } is($RowCount,5, "This works");
my $sql2=" select * from ORders where CustomerID = isnull(?,'VINET') "; my $sth2 = $dbh->prepare($sql2); $sth2->bind_param(1,$CustomerID); $sth2->execute(); $RowCount=0; while(my $row = $sth2->fetchrow_hashref()) { $RowCount++; } is($RowCount,5, "This Doesnt"); # Test(1); # clean up $sth->finish exit(0);
__END__
COMMAND OUTPUT:
#perl Makefile.PL Useless use of private variable in void context at Makefile.PL line
431.
Configuring DBD::ODBC ...
Remember to actually *READ* the README file!
And re-read it if you have any problems.
Using DBI 1.48 (for perl 5.008006 on sun4-solaris) installed in /usr/perl5.8.6/lib/site_perl/5.8.6/sun4-solaris/auto/DBI/ Using ODBC in /usr/local/easysoft/unixODBC
Umm, this looks like a unixodbc type of driver manager. We expect to find the sql.h, sqlext.h and (which were supplied with unixODBC) in $ODBCHOME/include directory alongside the /usr/local/easysoft/unixODBC/lib/libodbc.so library. in $ODBCHOME/lib
Checking if your kit is complete... Looks good Injecting selected odbc driver into cc command Injecting selected odbc driver into cc command Using DBI 1.48 (for perl 5.008006 on sun4-solaris) installed in /usr/perl5.8.6/lib/site_perl/5.8.6/sun4-solaris/auto/DBI/ Writing Makefile for DBD::ODBC
The DBD::ODBC tests will use these values for the database connection: DBI_DSN=dbi:ODBC:dsn_Northwind e.g. dbi:ODBC:demo DBI_USER=webuser DBI_PASS=test
#make cp Changes blib/lib/DBD/ODBC/Changes.pm cp ODBC.pm blib/lib/DBD/ODBC.pm /usr/bin/perl -p -e "s/~DRIVER~/ODBC/g" /usr/perl5.8.6/lib/site_perl/5.8.6/sun4-solaris/auto/DBI//Driver.xst > ODBC.xsi /usr/bin/perl /usr/perl5.8.6/lib/5.8.6/ExtUtils/xsubpp -typemap /usr/perl5.8.6/lib/5.8.6/ExtUtils/typemap ODBC.xs > ODBC.xsc && mv ODBC.xsc ODBC.c Warning: duplicate function definition 'data_sources' detected in ODBC.xs, line 202 gcc -c -I/usr/local/easysoft/unixODBC/include -I. -I/usr/perl5.8.6/lib/site_perl/5.8.6/sun4-solaris/auto/DBI/ -fno-strict-aliasing -pipe -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -O -DVERSION=\"1.13\" -DXS_VERSION=\"1.13\" -fPIC "-I/usr/perl5.8.6/lib/5.8.6/sun4-solaris/CORE" -I/usr/local/easysoft/unixODBC/include ODBC.c gcc -c -I/usr/local/easysoft/unixODBC/include -I. -I/usr/perl5.8.6/lib/site_perl/5.8.6/sun4-solaris/auto/DBI/ -fno-strict-aliasing -pipe -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -O -DVERSION=\"1.13\" -DXS_VERSION=\"1.13\" -fPIC "-I/usr/perl5.8.6/lib/5.8.6/sun4-solaris/CORE" -I/usr/local/easysoft/unixODBC/include dbdimp.c Running Mkbootstrap for DBD::ODBC () chmod 644 ODBC.bs rm -f blib/arch/auto/DBD/ODBC/ODBC.so LD_RUN_PATH="/usr/local/easysoft/unixODBC/lib" gcc -G
-L/usr/local/lib
ODBC.o dbdimp.o -o blib/arch/auto/DBD/ODBC/ODBC.so -L/usr/local/easysoft/unixODBC/lib -lodbc chmod 755 blib/arch/auto/DBD/ODBC/ODBC.so cp ODBC.bs blib/arch/auto/DBD/ODBC/ODBC.bs chmod 644 blib/arch/auto/DBD/ODBC/ODBC.bs Manifying blib/man3/DBD::ODBC.3
Then of course make test fails on my test script above.
Brian Becker Jaeb Center for Health Research 15310 Amberly Drive Suite 350 Tampa, FL 33647 tel: 813-975-8690 fax: 813-975-8761 email: bbecker@xxxxxxxx http://www.jaeb.org
.
- Follow-Ups:
- RE: [dbi] Re: MsSQL DBD::ODBC IsNull and undef
- From: Martin J. Evans
- RE: [dbi] Re: MsSQL DBD::ODBC IsNull and undef
- Prev by Date: Re: case insensitive selects
- Next by Date: RE: [dbi] Re: MsSQL DBD::ODBC IsNull and undef
- Previous by thread: RE: MsSQL DBD::ODBC IsNull and undef
- Next by thread: RE: [dbi] Re: MsSQL DBD::ODBC IsNull and undef
- Index(es):
Relevant Pages
|
