second DBI handle?

Hi,

I'm trying to do a database fetch on one table, and an insert on another
table on the same DB. This looks like this:

$mssth=$msdbh->prepare("SELECT * FROM SyncroTess_in");
if ($mssth->execute) {
while (my @getdata=$mssth->fetchrow_array) {
.....
......
$msdbh->do("INSERT INTO SyncroTess_out Pakete, Ladestelle, AKNr,
time VALUES ('$einzelpaket','$ladestelle','$aknr','$now'");
....
....

and I get this error:

Tue Nov 20 14:55:51 2007 DBD::ODBC::db do failed: [Microsoft][ODBC SQL
Server Driver]Die Verbindung ist mit Ergebnissen von einem anderen hstmt
belegt (SQL-HY000)(DBD: Execute immediate failed err=-1) at soappush.pl
line 35.

(i.e. handle is in use)

how can I workaround this?

Lars
_______________________________________________
ActivePerl mailing list
ActivePerl@listserv.ActiveState.com
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs

RE: second DBI handle?

Oeschey, Lars (I/EK-142, extern) <> wrote:
> Hi,
> =

> I'm trying to do a database fetch on one table, and an insert on
> another table on the same DB. This looks like this: =

> =

> $mssth=3D$msdbh->prepare("SELECT * FROM SyncroTess_in"); if
> ($mssth->execute) { while (my @getdata=3D$mssth->fetchrow_array) {
> .....
> ......
> $msdbh->do("INSERT INTO SyncroTess_out Pakete, Ladestelle, AKNr,
> time VALUES ('$einzelpaket','$ladestelle','$aknr','$now'");
> ....
> ....
> =

> and I get this error:
> =

> Tue Nov 20 14:55:51 2007 DBD::ODBC::db do failed: [Microsoft][ODBC
> SQL Server Driver]Die Verbindung ist mit Ergebnissen von einem
> anderen hstmt belegt (SQL-HY000)(DBD: Execute immediate failed
> err=3D-1) at soappush.pl line 35. =

> =

> (i.e. handle is in use)

It looks like you are trying to execute a new statement handle while
fetching data from another statement handle associated with the same
database handle, on an SQL Server database via ODBC. According to the
book "Programming the Perl DBI" the ODBC drivers will not let you do
this.

> =

> how can I workaround this?

Creating another database handle might do the trick. If not, you will
have to delay your inserts until the fetch loop has finished.

HTH

-- =

Brian Raven =


==================== =====3D=
================
Atos Euronext Market Solutions Disclaimer
==================== =====3D=
================

The information contained in this e-mail is confidential and solely for the=
intended addressee(s). Unauthorised reproduction, disclosure, modification=
, and/or distribution of this email may be unlawful.
If you have received this email in error, please notify the sender immediat=
ely and delete it from your system. The views expressed in this message do =
not necessarily reflect those of Atos Euronext Market Solutions.

Atos Euronext Market Solutions Limited - Registered in England & Wales with=
registration no. 3962327. Registered office address at 25 Bank Street Lon=
don E14 5NQ United Kingdom. =

Atos Euronext Market Solutions SAS - Registered in France with registration=
no. 425 100 294. Registered office address at 6/8 Boulevard Haussmann 750=
09 Paris France.

L'information contenue dans cet e-mail est confidentielle et uniquement des=
tinee a la (aux) personnes a laquelle (auxquelle(s)) elle est adressee. Tou=
te copie, publication ou diffusion de cet email est interdite. Si cet e-mai=
l vous parvient par erreur, nous vous prions de bien vouloir prevenir l'exp=
editeur immediatement et d'effacer le e-mail et annexes jointes de votre sy=
steme. Le contenu de ce message electronique ne represente pas necessaireme=
nt la position ou le point de vue d'Atos Euronext Market Solutions.
Atos Euronext Market Solutions Limited Soci=E9t=E9 de droit anglais, enregi=
str=E9e au Royaume Uni sous le num=E9ro 3962327, dont le si=E8ge social se =
situe 25 Bank Street E14 5NQ Londres Royaume Uni.

Atos Euronext Market Solutions SAS, soci=E9t=E9 par actions simplifi=E9e, e=
nregistr=E9 au registre dui commerce et des soci=E9t=E9s sous le num=E9ro 4=
25 100 294 RCS Paris et dont le si=E8ge social se situe 6/8 Boulevard Hauss=
mann 75009 Paris France.
==================== =====3D=
================

_______________________________________________
ActivePerl mailing list
ActivePerl@listserv.ActiveState.com
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs

RE: second DBI handle?

>It looks like you are trying to execute a new statement handle while
>fetching data from another statement handle associated with the same
>database handle, on an SQL Server database via ODBC. According to the
>book "Programming the Perl DBI" the ODBC drivers will not let you do
>this.

>> how can I workaround this?

>Creating another database handle might do the trick. If not, you will
>have to delay your inserts until the fetch loop has finished.

Another way is to use one from the "fetchall" line of calls, and get all
your data at once. Close that statement, then process through the data you
got (doing inserts one at a time then if you wish). Just make sure you
delete your variable if the returned data is large and/or you're concerned
about memory usage.

-Wayne Simmons


--
Software Engineer
InterSystems USA, Inc.
303-858-1000

-----Original Message-----
From: activeperl-bounces@listserv.ActiveState.com
[mailto:activeperl-bounces@listserv.ActiveState.com] On Behalf Of Brian
Raven
Sent: Tuesday, November 20, 2007 8:10 AM
To: activeperl@listserv.ActiveState.com
Subject: RE: second DBI handle?

Oeschey, Lars (I/EK-142, extern) <> wrote:
> Hi,
>
> I'm trying to do a database fetch on one table, and an insert on
> another table on the same DB. This looks like this:
>
> $mssth=$msdbh->prepare("SELECT * FROM SyncroTess_in"); if
> ($mssth->execute) { while (my @getdata=$mssth->fetchrow_array) {
> .....
> ......
> $msdbh->do("INSERT INTO SyncroTess_out Pakete, Ladestelle, AKNr,
> time VALUES ('$einzelpaket','$ladestelle','$aknr','$now'");
> ....
> ....
>
> and I get this error:
>
> Tue Nov 20 14:55:51 2007 DBD::ODBC::db do failed: [Microsoft][ODBC
> SQL Server Driver]Die Verbindung ist mit Ergebnissen von einem
> anderen hstmt belegt (SQL-HY000)(DBD: Execute immediate failed
> err=-1) at soappush.pl line 35.
>
> (i.e. handle is in use)

It looks like you are trying to execute a new statement handle while
fetching data from another statement handle associated with the same
database handle, on an SQL Server database via ODBC. According to the
book "Programming the Perl DBI" the ODBC drivers will not let you do
this.

>
> how can I workaround this?

Creating another database handle might do the trick. If not, you will
have to delay your inserts until the fetch loop has finished.

HTH

--
Brian Raven



_______________________________________________
ActivePerl mailing list
ActivePerl@listserv.ActiveState.com
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs