RE: MsSQL DBD::ODBC IsNull and undef
- From: martin.evans@xxxxxxxxxxxx (Martin J. Evans)
- Date: Mon, 25 Jul 2005 17:58:32 +0100 (BST)
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
>
>
.
- References:
- MsSQL DBD::ODBC IsNull and undef
- From: Brian Becker
- MsSQL DBD::ODBC IsNull and undef
- Prev by Date: MsSQL DBD::ODBC IsNull and undef
- Next by Date: Re: case insensitive selects
- Previous by thread: MsSQL DBD::ODBC IsNull and undef
- Next by thread: Re: MsSQL DBD::ODBC IsNull and undef
- Index(es):
Relevant Pages
|
