Hi,

just wondering how to achieve this. Want to query (PostgresSQL DB)
successively 3 different SQL.

1. delete from main_table
2. insert into main_table
3. update log_table

Can it be done successively w/o calling dbh->connect each time? how do I
put the prepare and the bind_param and the execute?

currently, only with #3, (Update)

$dbh_pg = DBI->connect
(
"DBI:Pg:host=$pg_server_name;
port=$pg_server_port,
database=$pg_default_db",
$pg_user, $pg_passwd,
{RaiseError => 0, PrintError => 0}
);
$sth_pg = $dbh_pg->prepare("update sync_log set last_sync=?,
record_update_date_time=current_timestamp where table_name=?");
$sth_pg->bind_param(1,$to_datetime);
$sth_pg->bind_param(2,$table_name);
$sth_pg->execute();

$dbh_pg =3D DBI->connect
(
"DBI:Pg:host=3D$pg_server_name;
port=3D$pg_server_port,
database=3D$pg_default_db",
$pg_user, $pg_passwd,
{RaiseError =3D> 0, PrintError =3D> 0}
);

$dbh_pg->do(<<EOS,{},$to_datetime, $table_name) || die $dbh_pg->errstr;
update sync_log set last_sync=3D?,
record_update_date_time=3Dcurrent_timestamp
where table_name=3D?
EOS

#-- add your other does here

$dbh_pg->commit; #-- if you need to commit it.


-----Original Message-----
From: Ow Mun Heng [mailto:Ow.Mun.Heng [at] wdc.com]
Sent: Wednesday, August 29, 2007 11:19 PM
To: dbi-users [at] perl.org
Subject: Multiple queries (SQL) in 1 connection instance

Hi,

just wondering how to achieve this. Want to query (PostgresSQL DB)
successively 3 different SQL.

1. delete from main_table
2. insert into main_table
3. update log_table

Can it be done successively w/o calling dbh->connect each time? how do I
put the prepare and the bind_param and the execute?

currently, only with #3, (Update)

$dbh_pg =3D DBI->connect
(
"DBI:Pg:host=3D$pg_server_name;
port=3D$pg_server_port,
database=3D$pg_default_db",
$pg_user, $pg_passwd,
{RaiseError =3D> 0, PrintError =3D> 0}
);
$sth_pg =3D $dbh_pg->prepare("update sync_log set last_sync=3D?,
record_update_date_time=3Dcurrent_timestamp where table_name=3D?");
$sth_pg->bind_param(1,$to_datetime);
$sth_pg->bind_param(2,$table_name);
$sth_pg->execute();