Brian,
OK, I've found this now. It is rather more complicated that it looks.
I did this in odbctest (directly to MS SQL Server driver):
create table mjenull(a varchar(20))
insert into table values ('AAA')
select * from table where a = isnull(NULL, 'AAA')
correctly returns 1 row.
now:
SQLPrepare(select * from table where a = isnull(?, 'AAA')
SQLBindParameter(stmt, 1, SQ_C_CHAR, SQL_VARCHAR, 1, 0, NULL, 0, ptr)
NOTE the 1 for ColumnSize
*ptr = SQL_NULL_DATA
SQLExecute
no rows returned.
repeat with ColumnSize = 20
SQLBindParameter(stmt, 1, SQ_C_CHAR, SQL_VARCHAR, 20, 0, NULL, 0, ptr)
*ptr = SQL_NULL_DATA
SQLExecute
1 row correctly returned.
now insert:
insert into table values ('A')
repeat with ColumnSize = 1 (as DBD::ODBC does):
SQLPrepare(select * from table where a = isnull(?, 'AAA')
SQLBindParameter(stmt, 1, SQ_C_CHAR, SQL_VARCHAR, 1, 0, NULL, 0, ptr)
NOTE the 1 for ColumnSize
*ptr = SQL_NULL_DATA
SQLExecute
1 row returned and it is the row containing 'A'
I'd guess this is because the SQL Server ODBC driver is looking at your
SQLBindParameter call to determine the type and size (1) and hence is saying
isnull is going to return 1 character -> 'A' in this case, even though we said
'AAA'.
The solution is to make sure the ColumnSize is what SQLDescribeParam returns.
The diff to DBD::ODBC 1.13 below is a total hack to make this work - it is not
complete because it doesn't handle drivers that don't have SQLDescribeParam
(and it is a hack). I'm afraid I don't have time right now to do this properly.
--- dbdimp.h Mon Oct 11 20:20:55 2004
+++ dbdimp.h.mine Tue Jul 26 10:57:05 2005
@@ -139,6 +139,7 @@
SWORD tgt_sql_type; /* the PH SQL type the stmt
expects */
SDWORD tgt_len; /* size or precision the stmt expects */
SDWORD cbValue; /* length of returned value OR
SQL_NULL_DATA */
+ UDWORD cbColDef; /* ColumnSize returned from
SQLDescribeParam */
SDWORD *indics; /* ptr to indicator array for param
arrays */
int is_array; /* TRUE => parameter array */
--- dbdimp.c Fri Nov 5 03:19:36 2004
+++ dbdimp.c.mine Tue Jul 26 11:25:41 2005
@@ -2532,6 +2532,7 @@
/* fall through */
/* return 0; */
} else {
+ phs->cbColDef = dp_cbColDef;
if (ODBC_TRACE_LEVEL(imp_sth) >=5)
PerlIO_printf(DBIc_LOGPIO(imp_dbh),
" SQLDescribeParam %s: SqlType=%s, ColDef=%d\n",
@@ -2747,7 +2748,7 @@
}
cbColDef = phs->biggestparam;
}
-
+
if (!SvOK(phs->sv)) {
/* if is_inout, shouldn't we null terminate the buffer and send
* it, instead?? */
@@ -2766,6 +2767,7 @@
} else {
rgbValue = NULL;
phs->cbValue = SQL_NULL_DATA;
+ cbColDef = phs->cbColDef;
}
}
else {
Martin
--
Martin J. Evans
Easysoft Ltd, UK
Development
On 26-Jul-2005 Martin J. Evans wrote:
> 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:[EMAIL PROTECTED]
>> Sent: Monday, July 25, 2005 12:59 PM
>> To: dbi-users@perl.org
>> 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 [EMAIL PROTECTED] varchar(20)
>>> SET [EMAIL PROTECTED]
>>> select * from ORders where CustomerID =
>>>isnull([EMAIL PROTECTED],'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: [EMAIL PROTECTED]
>>>http://www.jaeb.org
>>>
>>>
