On a hunch, I switched to DBD-ODBC - and the problem vanished!
Now - a followup question -
What are the pros and cons of using
1. DBD-ADO and a Microsoft Data Link connection string
versus
2. DBD-ODBC and defining a system DSN
How do their behaviors differ? Which is more efficent? Connection pooling
issues, etc.
I'm sure this has been discussed many times - could someone please point me
to a document where I can read about this issue?
Thanks!
----- Original Message -----
From: "Bodo Eing" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>; "Garrison G. Lutz" <[EMAIL PROTECTED]>
Sent: Friday, June 22, 2001 4:24 AM
Subject: Re: Wierd DBD-ADO NULL behavior
> From: "Garrison G. Lutz" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Subject: Wierd DBD-ADO NULL behavior
> Date sent: Wed, 20 Jun 2001 15:35:55 -0400
>
> Hi,
>
> I do not use DBD-ADO nor MS SQL, so I can only guess, but
>
> >
> > I am using DBD-ADO (latest version) and DBI 1.14 to connect to MS SQL
Server 2000 and execute a User stored procedure called "Step_Two". I use a
hash to hold the parameter values like this:
> >
> > if (CONDITION) { $hash{'key'} = undef; }
> > else { $hash['key'} = 'value'; }
> >
> > $query = '{call Step_Two(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}';
> > @placeholders = (..., $hash{'param'}, ....);
> >
> > $sth = $dbh->prepare($query);
> > $sth->execute(@placeholders);
> >
> > if the CONDITION is met, I need the stored procedure to receive a NULL
value for the parameter so that it can update the database with NULL values.
> >
> > However, if at least one of the parameters is NULL, I get the following
error:
> >
> > DBD::ADO::st execute failed: Can't execute statement '{call Step_Two(?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}': Lasterror: -2146824580: OLE
exception from "ADODB.Command": Parameter object is improperly defined.
Inconsistent or incomplete information was provided. Win32::OLE(0.1501)
error
> 0x800a0e7c in METHOD/PROPERTYGET "Execute"
> >
> > undef for a value should translate into NULL, so what am I doing wrong??
>
> In fact, NULLs returned from your database always translate to Perl's
> undef, but vice versa things get more sophisticated, especially if
> placeholders are used. If one of your placeholder parameters is
> passed to a WHERE clause, you may even run into extra trouble (see
> the DBI pod for this). My suggestion is the following approach:
>
> 0. Does your underlying database scheme allow NULLs ? If yes
>
> 1. Hard code some values (with appropriate quoting) including one or
> more NULLs into your statement and test if it works, like
>
> $query = '{call Step_Two('foo', 'bar', NULL, ....., 'baz')}';
> $sth = $dbh->prepare($query);
> $sth->execute;
> If this does not work, I have to leave it to the rest of the list.
>
> 2. If it works, try to interpolate your values into your statement
> with the quote()-method, which will translate undef'ed values into
> unquoted NULLs:
>
> $query = "{call Step_Two($dbh->quote($hash{'param1'}), $dbh-
> >quote($hash{'param2'}).....)}";
>
> ......
>
> 3. If this does not work or if you still want to stick to
> placeholders, try saying
>
> $query = '{call Step_Two(?, ?, NULL, ....., ?)}';
> $sth = $dbh->prepare($query);
> $sth->execute(@values_not_containing_the_undefined_one);
>
> If this works, you can check your parameter hash for undefined values
> and generate an appropriate placeholder-style statement on the fly,
> saying;
>
> my @parameter_names = qw(your parameter names here in correct order);
> my @placeholders = map { if (defined $parameters{$_}) {'?'} else
> {'NULL'} } @parameter_names;
> my $query = '{call Step_Two(' . join (', ', @placeholders) . ')}';
>
> ### now get the values without the undef'ed ones
>
> for (@parameter_names) {
> push @values, $parameters{$_} if defined $parameters{$_};
> }
>
> ### now try
>
> $sth = $dbh->prepare($query);
> $sth->execute(@values);
>
> > Thanks!
>
> As said above, all this is just a guess, but your desparation
> justified a try...
>
> Bodo
> [EMAIL PROTECTED]
>
>
>
