Best practices for SQL error handling?
Hi all, are there any best practices for generic SQL error handling like how to test if a SELECT returned any values at all? I have noticed that different DBD drivers seem to produce different return values on selects - DBD-Oracle returns 0E0 if successful, DBD-CSV returns 0E0 if no row selected and the number of rows selected otherwise. Is there a good recommendation on how to handle this if I need to use the same code with different DBD drivers? Best regards, Reiner.
sql error
I'm drawing a blank on an error I'm getting inserting a date
(2001070112:00:00) into a varchar field.
Cannot execute: You have an error in your SQL syntax near ':00:00)' at
line 1
my $sql=q{INSERT INTO some_table(the_date) values(?)};
my $sth=$dbh-prepare($sql);
$sth-execute($the_date) || die Cannot execute: $DBI::errstr\n;
What could be wrong?
Jeff
RE: sql error
Oracle? If so, I think it wants you to do to_date(string, fmt) for the
insert. It sometimes gets unhappy if you try to insert dates that aren't the
same format as specified in its NLS params. If not Oracle,
nevermindIhavenoidea. :-
-Original Message-
From: Jeff Thies [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, June 26, 2001 1:02 PM
To: [EMAIL PROTECTED]
Subject: sql error
I'm drawing a blank on an error I'm getting inserting a date
(2001070112:00:00) into a varchar field.
Cannot execute: You have an error in your SQL syntax near ':00:00)' at
line 1
my $sql=q{INSERT INTO some_table(the_date) values(?)};
my $sth=$dbh-prepare($sql);
$sth-execute($the_date) || die Cannot execute: $DBI::errstr\n;
What could be wrong?
Jeff
RE: sql error
Alter session set nls_date_format='mmddhh24:mi:ss' ???
It probably doesn't like the dd being concatenated against the hh24 section
without a space either
-Original Message-
From: Jeff Thies [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, June 26, 2001 3:02 PM
To: [EMAIL PROTECTED]
Subject: sql error
I'm drawing a blank on an error I'm getting inserting a date
(2001070112:00:00) into a varchar field.
Cannot execute: You have an error in your SQL syntax near ':00:00)' at
line 1
my $sql=q{INSERT INTO some_table(the_date) values(?)};
my $sth=$dbh-prepare($sql);
$sth-execute($the_date) || die Cannot execute: $DBI::errstr\n;
What could be wrong?
Jeff
RE: sql error
You may find you have to wrap the date in single quotes. This may vary from DB to DB,
but some are picky about excepting anything
but normal numbers unquoted.
-Stephen
-Original Message-
From: Jeff Thies [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, June 26, 2001 04:02 PM
To: [EMAIL PROTECTED]
Subject: sql error
I'm drawing a blank on an error I'm getting inserting a date
(2001070112:00:00) into a varchar field.
Cannot execute: You have an error in your SQL syntax near ':00:00)' at
line 1
my $sql=q{INSERT INTO some_table(the_date) values(?)};
my $sth=$dbh-prepare($sql);
$sth-execute($the_date) || die Cannot execute: $DBI::errstr\n;
What could be wrong?
Jeff
Re: sql error / placeholders update
Jeff Thies wrote:
This is an update to a previous SQL error post. The problem is in the
placeholders.
This fails:
my $sql=q{INSERT INTO some_table(some_field) values(?)};
my $sth=$dbh-prepare($sql);
$sth-execute('some variable with a colon: in it');
This is OK:
my $sql=q{INSERT INTO some_table(some_field) values('some variable with
a colon: in it')};
my $sth=$dbh-prepare($sql);
$sth-execute();
as does a command line insert.
There seems to be a problem with the placeholder. I don't recall having
a problem like this before, so I suspect a DBI update by my webhost.
How do I retrieve the DBI version?
print $DBI::VERSION;
I wonder if there's a stray $ in your original SQL statement, so the
statement does not actually have a placeholder but does contain the
expanded date value. I'm not convinced by this; your post uses q{...}
which suppresses variable expansion. You don't indicate the DBMS you're
using, which doesn't help us.
--
Jonathan Leffler ([EMAIL PROTECTED], [EMAIL PROTECTED])
Guardian of DBD::Informix 1.00.PC1 -- see http://www.cpan.org/
#include disclaimer.h
problems installing DBD::Informix with SQL error -369 (HELP!)
Hi there .. trying to install DBD::Informix on a solaris machine. I've done it on the test server which (at least from my point if view) have an indentical environment and that worked ok. And the perl scripts run fine. Output from perl Makefile.PL on test server: ---OUTPUT START--- perl Makefile.PL Configuring Informix Database Driver for Perl Version 1.00.PC1 (2000-03-03) (aka DBD::Informix) You are using DBI version 1.14 and Perl version 5.00503 Remember to actually read the README file! Perl: perl5.00503 sun4-solaris dl_dlopen.xs System: sunos jumbo 5.5.1 generic_103640-27 sun4u sparc sunw,ultra-4 Compiler: gcc -O -I/usr/gnu/include -I/opt/gnu/include Using INFORMIX-ESQL Version 7.24.UC7 from /opt/informix-7.30 Assert macro will be disabled! Testing whether your Informix test environment will work... ESQLTEST Program Running: @(#)$Id: esqltest.ec version /main/20 2000-02-08 16:57:02 $ $DBI_DBNAME unset - defaulting to 'stores'. $DBD_INFORMIX_DATABASE unset - defaulting to 'stores'. $DBD_INFORMIX_DATABASE2 unset - defaulting to 'stores'. $DBD_INFORMIX_USERNAME is unset. $DBD_INFORMIX_USERNAME2 is unset. $DBD_INFORMIX_PASSWORD is unset. $DBD_INFORMIX_PASSWORD2 is unset. Testing connection to stores CONNECT TO 'stores' - no user info Testing concurrent connection to stores CONNECT TO 'stores' - no user info Your Informix environment is (probably) OK Checking if your kit is complete... Looks good Using DBI 1.14 installed in /usr/local/lib/perl5/site_perl/5.005/sun4-solaris/auto/DBI Writing Makefile for DBD::Informix -OUTPUT END-- On the 'problem' machine: -OUTPUT START-- bash$ perl Makefile.PL Configuring Informix Database Driver for Perl Version 1.00.PC1 (2000-03-03) (aka DBD::Informix) You are using DBI version 1.14 and Perl version 5.00503 Remember to actually read the README file! Perl: perl5.00503 sun4-solaris dl_dlopen.xs System: sunos joker 5.5.1 generic_103640-27 sun4u sparc sunw,ultra-4 Compiler: gcc -B/usr/ccs/bin/ -O -I/usr/local/include Using INFORMIX-ESQL Version 7.24.UC7 from /usr/informix Assert macro will be disabled! Testing whether your Informix test environment will work... gcc: file path prefix `/usr/ccs/bin/' never used ESQLTEST Program Running: @(#)$Id: esqltest.ec version /main/20 2000-02-08 16:57:02 $ $DBI_DBNAME unset - defaulting to 'stores'. $DBD_INFORMIX_DATABASE unset - defaulting to 'stores'. $DBD_INFORMIX_DATABASE2 unset - defaulting to 'stores'. $DBD_INFORMIX_USERNAME is unset. $DBD_INFORMIX_USERNAME2 is unset. $DBD_INFORMIX_PASSWORD is unset. $DBD_INFORMIX_PASSWORD2 is unset. Testing connection to stores CONNECT TO 'stores' - no user info SQL: -369: Invalid serial number. Please consult your installation instructions. Testing concurrent connection to stores CONNECT TO 'stores' - no user info SQL: -369: Invalid serial number. Please consult your installation instructions. *** Your Informix environment is not usable *** You must fix it before building or testing DBD::Informix -OUTPUT END-- Any help, hints ot trix are welcome! TIA Brd -- "Funny," he intoned funerally, "how just when you think life can't possibly get any worse it suddenly does."
Re: problems installing DBD::Informix with SQL error -369 (HELP!)
Dear Sir, You much set the environment values DBD_INFORMIX_USERNAME and DBD_INFORMIX_PASSWORD . Leesa [EMAIL PROTECTED] - Original Message - From: Brd Kregnes [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, March 21, 2001 1:43 AM Subject: problems installing DBD::Informix with SQL error -369 (HELP!) : Hi there .. : trying to install DBD::Informix on a solaris machine. I've done it on the : test server which (at least from my point if view) have an indentical : environment and that worked ok. And the perl scripts run fine. : : Output from perl Makefile.PL on test server: : : ---OUTPUT START--- : perl Makefile.PL : : Configuring Informix Database Driver for Perl Version 1.00.PC1 (2000-03-03) : (aka DBD::Informix) : You are using DBI version 1.14 and Perl version 5.00503 : Remember to actually read the README file! : : Perl: perl5.00503 sun4-solaris dl_dlopen.xs : System: sunos jumbo 5.5.1 generic_103640-27 sun4u sparc sunw,ultra-4 : Compiler: gcc -O -I/usr/gnu/include -I/opt/gnu/include : : Using INFORMIX-ESQL Version 7.24.UC7 from /opt/informix-7.30 : Assert macro will be disabled! : : Testing whether your Informix test environment will work... : ESQLTEST Program Running: : @(#)$Id: esqltest.ec version /main/20 2000-02-08 16:57:02 $ : $DBI_DBNAME unset - defaulting to 'stores'. : $DBD_INFORMIX_DATABASE unset - defaulting to 'stores'. : $DBD_INFORMIX_DATABASE2 unset - defaulting to 'stores'. : $DBD_INFORMIX_USERNAME is unset. : $DBD_INFORMIX_USERNAME2 is unset. : $DBD_INFORMIX_PASSWORD is unset. : $DBD_INFORMIX_PASSWORD2 is unset. : Testing connection to stores : CONNECT TO 'stores' - no user info : Testing concurrent connection to stores : CONNECT TO 'stores' - no user info : Your Informix environment is (probably) OK : : Checking if your kit is complete... : Looks good : Using DBI 1.14 installed in : /usr/local/lib/perl5/site_perl/5.005/sun4-solaris/auto/DBI : Writing Makefile for DBD::Informix : -OUTPUT END-- : : On the 'problem' machine: : : -OUTPUT START-- : : bash$ perl Makefile.PL : : Configuring Informix Database Driver for Perl Version 1.00.PC1 (2000-03-03) : (aka DBD::Informix) : You are using DBI version 1.14 and Perl version 5.00503 : Remember to actually read the README file! : : Perl: perl5.00503 sun4-solaris dl_dlopen.xs : System: sunos joker 5.5.1 generic_103640-27 sun4u sparc sunw,ultra-4 : Compiler: gcc -B/usr/ccs/bin/ -O -I/usr/local/include : : Using INFORMIX-ESQL Version 7.24.UC7 from /usr/informix : Assert macro will be disabled! : : Testing whether your Informix test environment will work... : gcc: file path prefix `/usr/ccs/bin/' never used : ESQLTEST Program Running: : @(#)$Id: esqltest.ec version /main/20 2000-02-08 16:57:02 $ : $DBI_DBNAME unset - defaulting to 'stores'. : $DBD_INFORMIX_DATABASE unset - defaulting to 'stores'. : $DBD_INFORMIX_DATABASE2 unset - defaulting to 'stores'. : $DBD_INFORMIX_USERNAME is unset. : $DBD_INFORMIX_USERNAME2 is unset. : $DBD_INFORMIX_PASSWORD is unset. : $DBD_INFORMIX_PASSWORD2 is unset. : Testing connection to stores : CONNECT TO 'stores' - no user info : SQL: -369: Invalid serial number. Please consult your installation : instructions. : : Testing concurrent connection to stores : CONNECT TO 'stores' - no user info : SQL: -369: Invalid serial number. Please consult your installation : instructions. : : : *** Your Informix environment is not usable : *** You must fix it before building or testing DBD::Informix : -OUTPUT END-- : : Any help, hints ot trix are welcome! : : TIA : : Brd : -- : "Funny," he intoned funerally, "how just when you think life : can't possibly get any worse it suddenly does." : :
