Trapping error for $dbh->do()

------_=_NextPart_001_01C79195.E5541954
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Hi,
=20
I am trying to execute multi SQL statements within the $dbh->do() and it
appears to work fine except it does not give me an error when part of
the SQL fails. For example:
=20
BEGIN WORK;
=20
CREATE TEMP TABLE p_temp AS
SELECT col1
, col2
, col3
FROM table1
, table2
WHERE blah blah;
=20
INSERT INTO some_destination_table=20
SELECT col1
, col2
, col3
, etc...
FROM table1
, table2
, table3;
=20
COMMIT;
=20
The part that does the CREATE TEMP TABLE failed because one of the
tables it is referencing does not exist, however, $dbh->do() did not
return any error. I did in fact turned on the RaiseError in the connect
statement.
=20
unless($dbh =3D DBI->connect("dbi:$dbDriver:$dbName", $dbUser,
$dbPass, { RaiseError =3D> 1 })) {
$MESSAGE =3D "ERROR: Connection failed to $dbName for user
$dbUser.";
print STDERR "$MESSAGE\n\n";
$STATUS =3D $FAILURE;
sub_exit();
}
=20
I am also trying to trap $dbh->do() using "eval".
=20
eval {
$dbh->do($sqlString);
};
if ($@) {
$MESSAGE =3D "ERROR: dbh->do($sqlString) failed. $@";
print STDERR "$MESSAGE\n\n";
$STATUS =3D $FAILURE;
sub_exit();
}
=20
Hope someone can shed some light for me. The versions I am using are:
=20
This is perl, v5.8.7 built for sun4-solaris
=20
$ perl -M'DBD::ODBC' -le 'print $DBD::ODBC::VERSION'
1.13
=20
Thanks.
=20
Peter

------_=_NextPart_001_01C79195.E5541954--

Re: Trapping error for $dbh->do()

Quoting the DBI man page
:
> do
> $rows = $dbh->do($statement) or die $dbh->errstr;
> $rows = $dbh->do($statement, \%attr) or die $dbh->errstr;
> $rows = $dbh->do($statement, \%attr, @bind_values) or die ...
>
> Prepare and execute a *SINGLE* statement.
If your DBD seems to support mutliple statements in a single $dbh->do(),
it does that by accident.

If you need "all or nothing", read about transactions:


If you just need to process several SQL commands, use a loop.
my @statements=(...);
foreach my $st (@statements) {
$dbh->do($st);
}

With transactions, you would wrap the entire loop and the final commit
inside an eval BLOCK, and call rollback if $@ is true after the eval.

NEVER, NEVER, NEVER put values inside the SQL statements, this begs for
trouble and usually performs suboptimal.

Hope that helps,
Alexander



Loo, Peter # PHX wrote:
> Hi,
>
> I am trying to execute multi SQL statements within the $dbh->do() and it
> appears to work fine except it does not give me an error when part of
> the SQL fails. For example:
>
> BEGIN WORK;
>
> CREATE TEMP TABLE p_temp AS
> SELECT col1
> , col2
> , col3
> >FROM table1
> , table2
> WHERE blah blah;
>
> INSERT INTO some_destination_table
> SELECT col1
> , col2
> , col3
> , etc...
> >FROM table1
> , table2
> , table3;
>
> COMMIT;
>
> The part that does the CREATE TEMP TABLE failed because one of the
> tables it is referencing does not exist, however, $dbh->do() did not
> return any error. I did in fact turned on the RaiseError in the connect
> statement.
>
> unless($dbh = DBI->connect("dbi:$dbDriver:$dbName", $dbUser,
> $dbPass, { RaiseError => 1 })) {
> $MESSAGE = "ERROR: Connection failed to $dbName for user
> $dbUser.";
> print STDERR "$MESSAGE\n\n";
> $STATUS = $FAILURE;
> sub_exit();
> }
>
> I am also trying to trap $dbh->do() using "eval".
>
> eval {
> $dbh->do($sqlString);
> };
> if ($@) {
> $MESSAGE = "ERROR: dbh->do($sqlString) failed. $@";
> print STDERR "$MESSAGE\n\n";
> $STATUS = $FAILURE;
> sub_exit();
> }
>
> Hope someone can shed some light for me. The versions I am using are:
>
> This is perl, v5.8.7 built for sun4-solaris
>
> $ perl -M'DBD::ODBC' -le 'print $DBD::ODBC::VERSION'
> 1.13
>
> Thanks.
>
> Peter
>
>

--
Alexander Foken
mailto:alexander@foken.de http://www.foken.de/alexander/

RE: Trapping error for $dbh->do()

Hi Alexander,

Thanks for your kind input. Completely understood except the sentence
starting with "NEVER, NEVER...". Will you kindly explain?

Thanks again.
=20
Peter

-----Original Message-----
From: Alexander Foken [mailto:alexander@foken.de]=20
Sent: Tuesday, May 08, 2007 2:11 PM
To: Loo, Peter # PHX
Cc: dbi-users@perl.org
Subject: Re: Trapping error for $dbh->do()

Quoting the DBI man page
:
> do
> $rows =3D $dbh->do($statement) or die $dbh->errstr;
> $rows =3D $dbh->do($statement, \%attr) or die $dbh->errstr;
> $rows =3D $dbh->do($statement, \%attr, @bind_values) or die ...
>
> Prepare and execute a *SINGLE* statement.
If your DBD seems to support mutliple statements in a single $dbh->do(),
it does that by accident.

If you need "all or nothing", read about transactions:=20


If you just need to process several SQL commands, use a loop.
my @statements=3D(...);
foreach my $st (@statements) {
$dbh->do($st);
}

With transactions, you would wrap the entire loop and the final commit
inside an eval BLOCK, and call rollback if $@ is true after the eval.

NEVER, NEVER, NEVER put values inside the SQL statements, this begs for
trouble and usually performs suboptimal.

Hope that helps,
Alexander



Loo, Peter # PHX wrote:
> Hi,
> =20
> I am trying to execute multi SQL statements within the $dbh->do() and=20
> it appears to work fine except it does not give me an error when part=20
> of the SQL fails. For example:
> =20
> BEGIN WORK;
> =20
> CREATE TEMP TABLE p_temp AS
> SELECT col1
> , col2
> , col3
> >FROM table1
> , table2
> WHERE blah blah;
> =20
> INSERT INTO some_destination_table
> SELECT col1
> , col2
> , col3
> , etc...
> >FROM table1
> , table2
> , table3;
> =20
> COMMIT;
> =20
> The part that does the CREATE TEMP TABLE failed because one of the=20
> tables it is referencing does not exist, however, $dbh->do() did not=20
> return any error. I did in fact turned on the RaiseError in the=20
> connect statement.
> =20
> unless($dbh =3D DBI->connect("dbi:$dbDriver:$dbName", $dbUser,=20
> $dbPass, { RaiseError =3D> 1 })) {
> $MESSAGE =3D "ERROR: Connection failed to $dbName for user=20
> $dbUser.";
> print STDERR "$MESSAGE\n\n";
> $STATUS =3D $FAILURE;
> sub_exit();
> }
> =20
> I am also trying to trap $dbh->do() using "eval".
> =20
> eval {
> $dbh->do($sqlString);
> };
> if ($@) {
> $MESSAGE =3D "ERROR: dbh->do($sqlString) failed. $@";
> print STDERR "$MESSAGE\n\n";
> $STATUS =3D $FAILURE;
> sub_exit();
> }
> =20
> Hope someone can shed some light for me. The versions I am using are:
> =20
> This is perl, v5.8.7 built for sun4-solaris
> =20
> $ perl -M'DBD::ODBC' -le 'print $DBD::ODBC::VERSION'
> 1.13
> =20
> Thanks.
> =20
> Peter
>
> =20

--
Alexander Foken
mailto:alexander@foken.de http://www.foken.de/alexander/



This E-mail message is for the sole use of the intended recipient(s) and
may contain confidential and privileged information. Any unauthorized
review, use, disclosure or distribution is prohibited. If you are not
the intended recipient, please contact the sender by reply E-mail, and
destroy all copies of the original message.

Re: Trapping error for $dbh->do()

Hello

Basically the reason for substitution variables.. the statement
SELECT * from my_table where col1 = 0 and col2 = 1 and col3 -3

where the predicate would be supplied by the substitution variables :0 and
:1 and :2 as in
my $sth=$dbh->prepare("select * from my_table where col1=:0 and col2=:1 and
col3=:2");

Martin--
This email message and any files transmitted with it contain confidential
information intended only for the person(s) to whom this email message is
addressed. If you have received this email message in error, please notify
the sender immediately by telephone or email and destroy the original
message without making a copy. Thank you.

----- Original Message -----
From: "Loo, Peter # PHX"
To: "Alexander Foken"
Cc:
Sent: Tuesday, May 08, 2007 5:44 PM
Subject: RE: Trapping error for $dbh->do()



Hi Alexander,

Thanks for your kind input. Completely understood except the sentence
starting with "NEVER, NEVER...". Will you kindly explain?

Thanks again.

Peter

-----Original Message-----
From: Alexander Foken [mailto:alexander@foken.de]
Sent: Tuesday, May 08, 2007 2:11 PM
To: Loo, Peter # PHX
Cc: dbi-users@perl.org
Subject: Re: Trapping error for $dbh->do()

Quoting the DBI man page
:
> do
> $rows = $dbh->do($statement) or die $dbh->errstr;
> $rows = $dbh->do($statement, \%attr) or die $dbh->errstr;
> $rows = $dbh->do($statement, \%attr, @bind_values) or die ...
>
> Prepare and execute a *SINGLE* statement.
If your DBD seems to support mutliple statements in a single $dbh->do(),
it does that by accident.

If you need "all or nothing", read about transactions:


If you just need to process several SQL commands, use a loop.
my @statements=(...);
foreach my $st (@statements) {
$dbh->do($st);
}

With transactions, you would wrap the entire loop and the final commit
inside an eval BLOCK, and call rollback if $@ is true after the eval.

NEVER, NEVER, NEVER put values inside the SQL statements, this begs for
trouble and usually performs suboptimal.

Hope that helps,
Alexander



Loo, Peter # PHX wrote:
> Hi,
>
> I am trying to execute multi SQL statements within the $dbh->do() and
> it appears to work fine except it does not give me an error when part
> of the SQL fails. For example:
>
> BEGIN WORK;
>
> CREATE TEMP TABLE p_temp AS
> SELECT col1
> , col2
> , col3
> >FROM table1
> , table2
> WHERE blah blah;
>
> INSERT INTO some_destination_table
> SELECT col1
> , col2
> , col3
> , etc...
> >FROM table1
> , table2
> , table3;
>
> COMMIT;
>
> The part that does the CREATE TEMP TABLE failed because one of the
> tables it is referencing does not exist, however, $dbh->do() did not
> return any error. I did in fact turned on the RaiseError in the
> connect statement.
>
> unless($dbh = DBI->connect("dbi:$dbDriver:$dbName", $dbUser,
> $dbPass, { RaiseError => 1 })) {
> $MESSAGE = "ERROR: Connection failed to $dbName for user
> $dbUser.";
> print STDERR "$MESSAGE\n\n";
> $STATUS = $FAILURE;
> sub_exit();
> }
>
> I am also trying to trap $dbh->do() using "eval".
>
> eval {
> $dbh->do($sqlString);
> };
> if ($@) {
> $MESSAGE = "ERROR: dbh->do($sqlString) failed. $@";
> print STDERR "$MESSAGE\n\n";
> $STATUS = $FAILURE;
> sub_exit();
> }
>
> Hope someone can shed some light for me. The versions I am using are:
>
> This is perl, v5.8.7 built for sun4-solaris
>
> $ perl -M'DBD::ODBC' -le 'print $DBD::ODBC::VERSION'
> 1.13
>
> Thanks.
>
> Peter
>
>

--
Alexander Foken
mailto:alexander@foken.de http://www.foken.de/alexander/



This E-mail message is for the sole use of the intended recipient(s) and
may contain confidential and privileged information. Any unauthorized
review, use, disclosure or distribution is prohibited. If you are not
the intended recipient, please contact the sender by reply E-mail, and
destroy all copies of the original message.

Re: Trapping error for $dbh->do()

Just three points:

* Pasting values into the SQL command requires careful quoting.
Parameters do this automatically.
* Failing to quote correctly allows SQL injection, a common exploit for
www tools, but this is also possible with command line and GUI tools.
With parameters, the DBD::whatever takes care of quoting as a last
resort, but usually, parameter values and command are transported
separately to the database. So SQL injection is IMPOSSIBLE when using
parameters. (Unless the DBD::whatever has a severe bug.)
* Pasting values into SQL command requires to parse the SQL for each set
of values. Using a single prepare() and repeated execute()s requires to
parse only once, the parser result can be recycled. This makes things
faster. prepare_cached() accelerates further.

Alexander

Loo, Peter # PHX wrote:
> Hi Alexander,
>
> Thanks for your kind input. Completely understood except the sentence
> starting with "NEVER, NEVER...". Will you kindly explain?
>
> Thanks again.
>
> Peter
>
> -----Original Message-----
> From: Alexander Foken [mailto:alexander@foken.de]
> Sent: Tuesday, May 08, 2007 2:11 PM
> To: Loo, Peter # PHX
> Cc: dbi-users@perl.org
> Subject: Re: Trapping error for $dbh->do()
>
> Quoting the DBI man page
> :
>
>> do
>> $rows = $dbh->do($statement) or die $dbh->errstr;
>> $rows = $dbh->do($statement, \%attr) or die $dbh->errstr;
>> $rows = $dbh->do($statement, \%attr, @bind_values) or die ...
>>
>> Prepare and execute a *SINGLE* statement.
>>
> If your DBD seems to support mutliple statements in a single $dbh->do(),
> it does that by accident.
>
> If you need "all or nothing", read about transactions:
>
>
> If you just need to process several SQL commands, use a loop.
> my @statements=(...);
> foreach my $st (@statements) {
> $dbh->do($st);
> }
>
> With transactions, you would wrap the entire loop and the final commit
> inside an eval BLOCK, and call rollback if $@ is true after the eval.
>
> NEVER, NEVER, NEVER put values inside the SQL statements, this begs for
> trouble and usually performs suboptimal.
>
> Hope that helps,
> Alexander
>
>
>
> Loo, Peter # PHX wrote:
>
>> Hi,
>>
>> I am trying to execute multi SQL statements within the $dbh->do() and
>> it appears to work fine except it does not give me an error when part
>> of the SQL fails. For example:
>>
>> BEGIN WORK;
>>
>> CREATE TEMP TABLE p_temp AS
>> SELECT col1
>> , col2
>> , col3
>> >FROM table1
>> , table2
>> WHERE blah blah;
>>
>> INSERT INTO some_destination_table
>> SELECT col1
>> , col2
>> , col3
>> , etc...
>> >FROM table1
>> , table2
>> , table3;
>>
>> COMMIT;
>>
>> The part that does the CREATE TEMP TABLE failed because one of the
>> tables it is referencing does not exist, however, $dbh->do() did not
>> return any error. I did in fact turned on the RaiseError in the
>> connect statement.
>>
>> unless($dbh = DBI->connect("dbi:$dbDriver:$dbName", $dbUser,
>> $dbPass, { RaiseError => 1 })) {
>> $MESSAGE = "ERROR: Connection failed to $dbName for user
>> $dbUser.";
>> print STDERR "$MESSAGE\n\n";
>> $STATUS = $FAILURE;
>> sub_exit();
>> }
>>
>> I am also trying to trap $dbh->do() using "eval".
>>
>> eval {
>> $dbh->do($sqlString);
>> };
>> if ($@) {
>> $MESSAGE = "ERROR: dbh->do($sqlString) failed. $@";
>> print STDERR "$MESSAGE\n\n";
>> $STATUS = $FAILURE;
>> sub_exit();
>> }
>>
>> Hope someone can shed some light for me. The versions I am using are:
>>
>> This is perl, v5.8.7 built for sun4-solaris
>>
>> $ perl -M'DBD::ODBC' -le 'print $DBD::ODBC::VERSION'
>> 1.13
>>
>> Thanks.
>>
>> Peter
>>
>>
>>
>
> --
> Alexander Foken
> mailto:alexander@foken.de http://www.foken.de/alexander/
>
>
>
> This E-mail message is for the sole use of the intended recipient(s) and
> may contain confidential and privileged information. Any unauthorized
> review, use, disclosure or distribution is prohibited. If you are not
> the intended recipient, please contact the sender by reply E-mail, and
> destroy all copies of the original message.
>

--
Alexander Foken
mailto:alexander@foken.de http://www.foken.de/alexander/