Error while inserting null values with DBI

vsudha has asked for the wisdom of the Perl Monks concerning the following question:

Hi,

When I am trying to insert null values into the database, I am getting the following error.

Server message number=102 severity=15 state=1 line=312 server=NYTIBV3T061 text=Incorrect syntax near ','.

. the code which I am executing is the following.

$sql = insert into test(id,name) values($Id,\"$name\"); $dbh->do($sql).

Here id is integer and name is varchar. If $Id is null then I am getting the above error.
How to resolve this error?
Thanks,
Sudha

Comment on Error while inserting null values with DBI

Replies are listed 'Best First'.
Re: Error while inserting null values with DBI by moritz (Cardinal) on Jan 24, 2008 at 10:01 UTC
If `$Id` is undef or the empty string, your SQL becomes `...values(,"$name")`, which is invalid SQL. Use placeholders instead: `my $statement = $dbh->prepare('INSERT INTO test(id, name) VALUES(?,?)' +); $statement->execute($Id, $name);` [download] This will work even if `$Id` is undef (if the db schema allows NULL values for id), or if `$name` contains quotes.	[reply] [d/l] [select]
Re: Error while inserting null values with DBI by mpeppler (Vicar) on Jan 24, 2008 at 10:02 UTC
A couple of solutions. 1. Use placeholders: `my $sth = $dbh->prepare("insert test(id, name) values(?, ?)"); $sth->execute($id, $name);` [download] If $id is undef then a correct NULL value will be passed to the data server (which from the error message would appear to be Sybase or MS-SQL). An alternative solution: `$id = 'NULL' unless defined($id); $dbh->do("insert test(id, name) values($id, \"$name\")");` [download] The latter solution is much inferior to the placeholder solution, and is incomplete and bug-prone (i.e. if $name is NULL instead of $id you need to handle this differently, not to mention quoting issues with strings...) Michael	[reply] [d/l] [select]