temporary table "disapears"

--0-118154584-1178724656=:32004
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

Hello,

I am using DBI 1.51 and DBD::ODBC 1.13, connecting to MSSQL2005.

Executing following statements:
$sth = $dbh->prepare('create table #foo (a int not null)');
$sth->execute();
$sth = $dbh->prepare('insert into #foo values (1)');
$sth->execute();

generate an error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '#foo'.

So, the temporary table "disapears".... (I tested it on Sybase, using DBD::Sybase, too, there is no an error). Since the two statements are dynamically created (between come other statements), I cannot execute in one batch
$sth = $dbh->prepare('create table #foo (a int not null)
insert into #foo values (1));
$sth->execute();

at once...

How can I avoid this problem?

Regards!

Andon


---------------------------------
Sucker-punch spam with award-winning protection.
Try the free Yahoo! Mail Beta.
--0-118154584-1178724656=:32004--

RE: temporary table "disapears"

Actually I tried this against SQL 2000, DBI 1.53 and DBD::ODBC 1.13...

You should be getting 2 errors, the same error from both prepares. In
other words, #foo isn't being treated as a proper table name.
Naturally, these statements work fine if you just use foo (which isn't
temp).

However, #foo should represent a "global temp" table, and this is not
being accepted as a valid name. Not sure why.

But ##foo works fine, and the table does persist across executes while
the $dbh connection is open. With 2 #'s, it's a "local temp" table
which means it's not visible to other sessions. If that's OK, perhaps
you can use that instead. =20

=20

-----Original Message-----
From: Andon Tschauschev [mailto:atschauschev@yahoo.com]=20
Sent: Wednesday, May 09, 2007 8:31 AM
To: dbi-users@perl.org
Subject: temporary table "disapears"=20

Hello,

I am using DBI 1.51 and DBD::ODBC 1.13, connecting to MSSQL2005.

Executing following statements:
$sth =3D $dbh->prepare('create table #foo (a int not null)');
$sth->execute(); $sth =3D $dbh->prepare('insert into #foo values (1)');
$sth->execute();

generate an error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
'#foo'.

So, the temporary table "disapears".... (I tested it on Sybase, using
DBD::Sybase, too, there is no an error). Since the two statements are
dynamically created (between come other statements), I cannot execute in
one batch $sth =3D $dbh->prepare('create table #foo (a int not null)
insert into #foo values (1)); $sth->execute();
=20
at once...

How can I avoid this problem?=20

Regards!

Andon

=20
---------------------------------
Sucker-punch spam with award-winning protection.
Try the free Yahoo! Mail Beta.

RE: temporary table "disapears"

--=_alternative 001FE8C9C12572D7_=
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset="us-ascii"

I'm pretty sure that #tmp is a local temporary table, and ##tmp is a
global temporary table...

So the original problem is most likely that the create table #tmp and the
insert into #tmp statements aren't being run on the same physical
connection. I don't know DBD::ODBC, but I can tell you that DBD::Sybase
could possibly have opened a second connection under the covers if it
thought the first statement hadn't been completely processed yet.

Michael





Extranet
campbelb@alcatel-lucent.com - 09.05.2007 18:40


To: atschauschev, dbi-users
cc:
Subject: RE: temporary table "disapears"

Actually I tried this against SQL 2000, DBI 1.53 and DBD::ODBC 1.13...

You should be getting 2 errors, the same error from both prepares. In
other words, #foo isn't being treated as a proper table name.
Naturally, these statements work fine if you just use foo (which isn't
temp).

However, #foo should represent a "global temp" table, and this is not
being accepted as a valid name. Not sure why.

But ##foo works fine, and the table does persist across executes while
the $dbh connection is open. With 2 #'s, it's a "local temp" table
which means it's not visible to other sessions. If that's OK, perhaps
you can use that instead.



-----Original Message-----
From: Andon Tschauschev [mailto:atschauschev@yahoo.com]
Sent: Wednesday, May 09, 2007 8:31 AM
To: dbi-users@perl.org
Subject: temporary table "disapears"

Hello,

I am using DBI 1.51 and DBD::ODBC 1.13, connecting to MSSQL2005.

Executing following statements:
$sth = $dbh->prepare('create table #foo (a int not null)');
$sth->execute(); $sth = $dbh->prepare('insert into #foo values (1)');
$sth->execute();

generate an error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
'#foo'.

So, the temporary table "disapears".... (I tested it on Sybase, using
DBD::Sybase, too, there is no an error). Since the two statements are
dynamically created (between come other statements), I cannot execute in
one batch $sth = $dbh->prepare('create table #foo (a int not null)
insert into #foo values (1)); $sth->execute();

at once...

How can I avoid this problem?

Regards!

Andon


---------------------------------
Sucker-punch spam with award-winning protection.
Try the free Yahoo! Mail Beta.


This message and any attachments (the "message") is
intended solely for the addressees and is confidential.
If you receive this message in error, please delete it and
immediately notify the sender. Any use not in accord with
its purpose, any dissemination or disclosure, either whole
or partial, is prohibited except formal approval. The internet
can not guarantee the integrity of this message.
BNP PARIBAS (and its subsidiaries) shall (will) not
therefore be liable for the message if modified.

---------------------------------------------

Ce message et toutes les pieces jointes (ci-apres le
"message") sont etablis a l'intention exclusive de ses
destinataires et sont confidentiels. Si vous recevez ce
message par erreur, merci de le detruire et d'en avertir
immediatement l'expediteur. Toute utilisation de ce
message non conforme a sa destination, toute diffusion
ou toute publication, totale ou partielle, est interdite, sauf
autorisation expresse. L'internet ne permettant pas
d'assurer l'integrite de ce message, BNP PARIBAS (et ses
filiales) decline(nt) toute responsabilite au titre de ce
message, dans l'hypothese ou il aurait ete modifie.


--=_alternative 001FE8C9C12572D7_=--

RE: temporary table "disapears"

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

You're right. It's the the other way around from what I said. However,
when I tested this yesterday it seemed I was getting an error on the
create command also. But I re-examined the results more carefully today
and the create worked OK; it was just the insert that failed. However
they were both run on the same connection (same $dbh handle). So it
seems that local temps don't persist after an execute() call, as Andon
supposed.


________________________________

From: michael.peppler@bnpparibas.com
[mailto:michael.peppler@bnpparibas.com]=20
Sent: Wednesday, May 09, 2007 10:49 PM
To: CAMPBELL, BRIAN D (BRIAN)
Cc: atschauschev@yahoo.com; dbi-users@perl.org
Subject: RE: temporary table "disapears"
=09
=09

I'm pretty sure that #tmp is a local temporary table, and ##tmp
is a global temporary table...=20
=09
So the original problem is most likely that the create table
#tmp and the insert into #tmp statements aren't being run on the same
physical connection. I don't know DBD::ODBC, but I can tell you that
DBD::Sybase could possibly have opened a second connection under the
covers if it thought the first statement hadn't been completely
processed yet.=20
=09
Michael=20
=09
=09
=09
=09
=09
Extranet=20
campbelb@alcatel-lucent.com - 09.05.2007 18:40=20
=20
=09
To: atschauschev, dbi-users=20

cc: =20

Subject: RE: temporary table "disapears"=20
=09
Actually I tried this against SQL 2000, DBI 1.53 and DBD::ODBC
1.13...
=09
You should be getting 2 errors, the same error from both
prepares. In
other words, #foo isn't being treated as a proper table name.
Naturally, these statements work fine if you just use foo (which
isn't
temp).
=09
However, #foo should represent a "global temp" table, and this
is not
being accepted as a valid name. Not sure why.
=09
But ##foo works fine, and the table does persist across executes
while
the $dbh connection is open. With 2 #'s, it's a "local temp"
table
which means it's not visible to other sessions. If that's OK,
perhaps
you can use that instead.
=09
=09
=09
-----Original Message-----
From: Andon Tschauschev [mailto:atschauschev@yahoo.com]
Sent: Wednesday, May 09, 2007 8:31 AM
To: dbi-users@perl.org
Subject: temporary table "disapears"
=09
Hello,
=09
I am using DBI 1.51 and DBD::ODBC 1.13, connecting to MSSQL2005.
=09
Executing following statements:
$sth =3D $dbh->prepare('create table #foo (a int not null)');
$sth->execute(); $sth =3D $dbh->prepare('insert into #foo values
(1)');
$sth->execute();
=09
generate an error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object
name
'#foo'.
=09
So, the temporary table "disapears".... (I tested it on Sybase,
using
DBD::Sybase, too, there is no an error). Since the two
statements are
dynamically created (between come other statements), I cannot
execute in
one batch $sth =3D $dbh->prepare('create table #foo (a int not
null)
insert into #foo values (1)); $sth->execute();
=09
at once...
=09
How can I avoid this problem?
=09
Regards!
=09
Andon
=09
=09
---------------------------------
Sucker-punch spam with award-winning protection.=20
Try the free Yahoo! Mail Beta.=20
=09
=09
This message and any attachments (the "message") is
intended solely for the addressees and is confidential.=20
If you receive this message in error, please delete it and=20
immediately notify the sender. Any use not in accord with=20
its purpose, any dissemination or disclosure, either whole=20
or partial, is prohibited except formal approval. The internet
can not guarantee the integrity of this message.=20
BNP PARIBAS (and its subsidiaries) shall (will) not=20
therefore be liable for the message if modified.=20
=09
---------------------------------------------
=09
Ce message et toutes les pieces jointes (ci-apres le=20
"message") sont etablis a l'intention exclusive de ses=20
destinataires et sont confidentiels. Si vous recevez ce=20
message par erreur, merci de le detruire et d'en avertir=20
immediatement l'expediteur. Toute utilisation de ce=20
message non conforme a sa destination, toute diffusion=20
ou toute publication, totale ou partielle, est interdite, sauf=20
autorisation expresse. L'internet ne permettant pas=20
d'assurer l'integrite de ce message, BNP PARIBAS (et ses
filiales) decline(nt) toute responsabilite au titre de ce=20
message, dans l'hypothese ou il aurait ete modifie.
=09


------_=_NextPart_001_01C7930E.FB7BDC01--

Re: temporary table "disapears"

CAMPBELL, BRIAN D (BRIAN) wrote:
> You're right. It's the the other way around from what I said. However,
> when I tested this yesterday it seemed I was getting an error on the
> create command also. But I re-examined the results more carefully today
> and the create worked OK; it was just the insert that failed. However
> they were both run on the same connection (same $dbh handle). So it
> seems that local temps don't persist after an execute() call, as Andon
> supposed.
>

What if you turn autocommit off - do the temporary tables exist for
longer then?

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
> ________________________________
>
> From: michael.peppler@bnpparibas.com
> [mailto:michael.peppler@bnpparibas.com]
> Sent: Wednesday, May 09, 2007 10:49 PM
> To: CAMPBELL, BRIAN D (BRIAN)
> Cc: atschauschev@yahoo.com; dbi-users@perl.org
> Subject: RE: temporary table "disapears"
>
>
>
> I'm pretty sure that #tmp is a local temporary table, and ##tmp
> is a global temporary table...
>
> So the original problem is most likely that the create table
> #tmp and the insert into #tmp statements aren't being run on the same
> physical connection. I don't know DBD::ODBC, but I can tell you that
> DBD::Sybase could possibly have opened a second connection under the
> covers if it thought the first statement hadn't been completely
> processed yet.
>
> Michael
>
>
>
>
>
> Extranet
> campbelb@alcatel-lucent.com - 09.05.2007 18:40
>
>
> To: atschauschev, dbi-users
>
> cc:
>
> Subject: RE: temporary table "disapears"
>
> Actually I tried this against SQL 2000, DBI 1.53 and DBD::ODBC
> 1.13...
>
> You should be getting 2 errors, the same error from both
> prepares. In
> other words, #foo isn't being treated as a proper table name.
> Naturally, these statements work fine if you just use foo (which
> isn't
> temp).
>
> However, #foo should represent a "global temp" table, and this
> is not
> being accepted as a valid name. Not sure why.
>
> But ##foo works fine, and the table does persist across executes
> while
> the $dbh connection is open. With 2 #'s, it's a "local temp"
> table
> which means it's not visible to other sessions. If that's OK,
> perhaps
> you can use that instead.
>
>
>
> -----Original Message-----
> From: Andon Tschauschev [mailto:atschauschev@yahoo.com]
> Sent: Wednesday, May 09, 2007 8:31 AM
> To: dbi-users@perl.org
> Subject: temporary table "disapears"
>
> Hello,
>
> I am using DBI 1.51 and DBD::ODBC 1.13, connecting to MSSQL2005.
>
> Executing following statements:
> $sth = $dbh->prepare('create table #foo (a int not null)');
> $sth->execute(); $sth = $dbh->prepare('insert into #foo values
> (1)');
> $sth->execute();
>
> generate an error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object
> name
> '#foo'.
>
> So, the temporary table "disapears".... (I tested it on Sybase,
> using
> DBD::Sybase, too, there is no an error). Since the two
> statements are
> dynamically created (between come other statements), I cannot
> execute in
> one batch $sth = $dbh->prepare('create table #foo (a int not
> null)
> insert into #foo values (1)); $sth->execute();
>
> at once...
>
> How can I avoid this problem?
>
> Regards!
>
> Andon
>
>
> ---------------------------------
> Sucker-punch spam with award-winning protection.
> Try the free Yahoo! Mail Beta.
>
>
> This message and any attachments (the "message") is
> intended solely for the addressees and is confidential.
> If you receive this message in error, please delete it and
> immediately notify the sender. Any use not in accord with
> its purpose, any dissemination or disclosure, either whole
> or partial, is prohibited except formal approval. The internet
> can not guarantee the integrity of this message.
> BNP PARIBAS (and its subsidiaries) shall (will) not
> therefore be liable for the message if modified.
>
> ---------------------------------------------
>
> Ce message et toutes les pieces jointes (ci-apres le
> "message") sont etablis a l'intention exclusive de ses
> destinataires et sont confidentiels. Si vous recevez ce
> message par erreur, merci de le detruire et d'en avertir
> immediatement l'expediteur. Toute utilisation de ce
> message non conforme a sa destination, toute diffusion
> ou toute publication, totale ou partielle, est interdite, sauf
> autorisation expresse. L'internet ne permettant pas
> d'assurer l'integrite de ce message, BNP PARIBAS (et ses
> filiales) decline(nt) toute responsabilite au titre de ce
> message, dans l'hypothese ou il aurait ete modifie.
>
>
>

RE: temporary table "disapears"

Martin, Autocommit off doesn't help local temps persist after the
execute.

Andon said that batching all the commands in the same execute is not an
option for him, so the only working alternative so far is to consider
global temps (##foo). They do persist after an execute and throughout
an entire session.

Consider these examples:

my $s1 =3D 'create table #foo (a int not null)';
my $s2 =3D 'insert into #foo values (1)';
my $s3 =3D 'select * from #foo';
$dbh->{AutoCommit} =3D 0; # trying to see if this help, but it
doesn't
my $sth;
$sth =3D $dbh->prepare($s1);
$sth->execute(); # works: table created
$sth =3D $dbh->prepare($s1);
$sth->execute(); # works: can recreate table because
original is gone
$sth =3D $dbh->prepare($s2);
$sth->execute(); # doesn't work: table is gone
$sth =3D $dbh->prepare($s3);
$sth->execute(); # doesn't work: table is gone
$sth =3D $dbh->prepare("$s1; $s2; $s3");
$sth->execute(); # works: table exists across batched
commands=20

-----Original Message-----
From: Martin Evans [mailto:martin.evans@easysoft.com]=20
Sent: Thursday, May 10, 2007 7:39 AM
To: dbi-users@perl.org
Subject: Re: temporary table "disapears"

CAMPBELL, BRIAN D (BRIAN) wrote:
> You're right. It's the the other way around from what I said. =20
> However, when I tested this yesterday it seemed I was getting an error

> on the create command also. But I re-examined the results more=20
> carefully today and the create worked OK; it was just the insert that=20
> failed. However they were both run on the same connection (same $dbh=20
> handle). So it seems that local temps don't persist after an=20
> execute() call, as Andon supposed.
>=20

What if you turn autocommit off - do the temporary tables exist for
longer then?

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
> ________________________________
>=20
> From: michael.peppler@bnpparibas.com
> [mailto:michael.peppler@bnpparibas.com]=20
> Sent: Wednesday, May 09, 2007 10:49 PM
> To: CAMPBELL, BRIAN D (BRIAN)
> Cc: atschauschev@yahoo.com; dbi-users@perl.org
> Subject: RE: temporary table "disapears"
> =09
> =09
>=20
> I'm pretty sure that #tmp is a local temporary table, and ##tmp
is a=20
> global temporary table...
> =09
> So the original problem is most likely that the create table
#tmp and=20
> the insert into #tmp statements aren't being run on the same physical=20
> connection. I don't know DBD::ODBC, but I can tell you that=20
> DBD::Sybase could possibly have opened a second connection under the=20
> covers if it thought the first statement hadn't been completely=20
> processed yet.
> =09
> Michael
> =09
> =09
> =09
> =09
> =09
> Extranet=20
> campbelb@alcatel-lucent.com - 09.05.2007 18:40
> =20
> =09
> To: atschauschev, dbi-users=20
>=20
> cc: =20
>=20
> Subject: RE: temporary table "disapears"=20
> =09
> Actually I tried this against SQL 2000, DBI 1.53 and DBD::ODBC=20
> 1.13...
> =09
> You should be getting 2 errors, the same error from both
prepares. =20
> In
> other words, #foo isn't being treated as a proper table name.
> Naturally, these statements work fine if you just use foo (which

> isn't
> temp).
> =09
> However, #foo should represent a "global temp" table, and this
is not
> being accepted as a valid name. Not sure why.
> =09
> But ##foo works fine, and the table does persist across executes

> while
> the $dbh connection is open. With 2 #'s, it's a "local temp"
> table
> which means it's not visible to other sessions. If that's OK,=20
> perhaps
> you can use that instead.
> =09
> =09
> =09
> -----Original Message-----
> From: Andon Tschauschev [mailto:atschauschev@yahoo.com]
> Sent: Wednesday, May 09, 2007 8:31 AM
> To: dbi-users@perl.org
> Subject: temporary table "disapears"
> =09
> Hello,
> =09
> I am using DBI 1.51 and DBD::ODBC 1.13, connecting to MSSQL2005.
> =09
> Executing following statements:
> $sth =3D $dbh->prepare('create table #foo (a int not null)');
> $sth->execute(); $sth =3D $dbh->prepare('insert into #foo values
(1)');
> $sth->execute();
> =09
> generate an error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object
name
> '#foo'.
> =09
> So, the temporary table "disapears".... (I tested it on Sybase,
using
> DBD::Sybase, too, there is no an error). Since the two
statements are
> dynamically created (between come other statements), I cannot
execute=20
> in
> one batch $sth =3D $dbh->prepare('create table #foo (a int not
> null)
> insert into #foo values (1)); $sth->execute();
> =09
> at once...
> =09
> How can I avoid this problem?
> =09
> Regards!
> =09
> Andon
> =09
> =09
> ---------------------------------
> Sucker-punch spam with award-winning protection.=20
> Try the free Yahoo! Mail Beta.=20
> =09
> =09
> This message and any attachments (the "message") is
> intended solely for the addressees and is confidential.=20
> If you receive this message in error, please delete it and=20
> immediately notify the sender. Any use not in accord with=20
> its purpose, any dissemination or disclosure, either whole=20
> or partial, is prohibited except formal approval. The internet
> can not guarantee the integrity of this message.=20
> BNP PARIBAS (and its subsidiaries) shall (will) not=20
> therefore be liable for the message if modified.=20
> =09
> ---------------------------------------------
> =09
> Ce message et toutes les pieces jointes (ci-apres le=20
> "message") sont etablis a l'intention exclusive de ses=20
> destinataires et sont confidentiels. Si vous recevez ce=20
> message par erreur, merci de le detruire et d'en avertir=20
> immediatement l'expediteur. Toute utilisation de ce=20
> message non conforme a sa destination, toute diffusion=20
> ou toute publication, totale ou partielle, est interdite, sauf=20
> autorisation expresse. L'internet ne permettant pas=20
> d'assurer l'integrite de ce message, BNP PARIBAS (et ses
> filiales) decline(nt) toute responsabilite au titre de ce=20
> message, dans l'hypothese ou il aurait ete modifie.
> =09
>=20
>=20

RE: temporary table "disapears"

--=_alternative 001F22CAC12572D8_=
Content-Type: text/plain; charset="US-ASCII"

You should run this with DBI->trace() turned on to see what DBD::ODBC
actually does. The temp tables should only be dropped when the connection
is closed.

Michael




Extranet
campbelb@alcatel-lucent.com - 11.05.2007 00:19


To: martin.evans, dbi-users
cc:
Subject: RE: temporary table "disapears"

Martin, Autocommit off doesn't help local temps persist after the
execute.

Andon said that batching all the commands in the same execute is not an
option for him, so the only working alternative so far is to consider
global temps (##foo). They do persist after an execute and throughout
an entire session.

Consider these examples:

my $s1 = 'create table #foo (a int not null)';
my $s2 = 'insert into #foo values (1)';
my $s3 = 'select * from #foo';
$dbh->{AutoCommit} = 0; # trying to see if this help, but it
doesn't
my $sth;
$sth = $dbh->prepare($s1);
$sth->execute(); # works: table created
$sth = $dbh->prepare($s1);
$sth->execute(); # works: can recreate table because
original is gone
$sth = $dbh->prepare($s2);
$sth->execute(); # doesn't work: table is gone
$sth = $dbh->prepare($s3);
$sth->execute(); # doesn't work: table is gone
$sth = $dbh->prepare("$s1; $s2; $s3");
$sth->execute(); # works: table exists across batched
commands

-----Original Message-----
From: Martin Evans [mailto:martin.evans@easysoft.com]
Sent: Thursday, May 10, 2007 7:39 AM
To: dbi-users@perl.org
Subject: Re: temporary table "disapears"

CAMPBELL, BRIAN D (BRIAN) wrote:
> You're right. It's the the other way around from what I said.
> However, when I tested this yesterday it seemed I was getting an error

> on the create command also. But I re-examined the results more
> carefully today and the create worked OK; it was just the insert that
> failed. However they were both run on the same connection (same $dbh
> handle). So it seems that local temps don't persist after an
> execute() call, as Andon supposed.
>

What if you turn autocommit off - do the temporary tables exist for
longer then?

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
> ________________________________
>
> From: michael.peppler@bnpparibas.com
> [mailto:michael.peppler@bnpparibas.com]
> Sent: Wednesday, May 09, 2007 10:49 PM
> To: CAMPBELL, BRIAN D (BRIAN)
> Cc: atschauschev@yahoo.com; dbi-users@perl.org
> Subject: RE: temporary table "disapears"
>
>
>
> I'm pretty sure that #tmp is a local temporary table, and ##tmp
is a
> global temporary table...
>
> So the original problem is most likely that the create table
#tmp and
> the insert into #tmp statements aren't being run on the same physical
> connection. I don't know DBD::ODBC, but I can tell you that
> DBD::Sybase could possibly have opened a second connection under the
> covers if it thought the first statement hadn't been completely
> processed yet.
>
> Michael
>
>
>
>
>
> Extranet
> campbelb@alcatel-lucent.com - 09.05.2007 18:40
>
>
> To: atschauschev, dbi-users
>
> cc:
>
> Subject: RE: temporary table "disapears"
>
> Actually I tried this against SQL 2000, DBI 1.53 and DBD::ODBC
> 1.13...
>
> You should be getting 2 errors, the same error from both
prepares.
> In
> other words, #foo isn't being treated as a proper table name.
> Naturally, these statements work fine if you just use foo (which

> isn't
> temp).
>
> However, #foo should represent a "global temp" table, and this
is not
> being accepted as a valid name. Not sure why.
>
> But ##foo works fine, and the table does persist across executes

> while
> the $dbh connection is open. With 2 #'s, it's a "local temp"
> table
> which means it's not visible to other sessions. If that's OK,
> perhaps
> you can use that instead.
>
>
>
> -----Original Message-----
> From: Andon Tschauschev [mailto:atschauschev@yahoo.com]
> Sent: Wednesday, May 09, 2007 8:31 AM
> To: dbi-users@perl.org
> Subject: temporary table "disapears"
>
> Hello,
>
> I am using DBI 1.51 and DBD::ODBC 1.13, connecting to MSSQL2005.
>
> Executing following statements:
> $sth = $dbh->prepare('create table #foo (a int not null)');
> $sth->execute(); $sth = $dbh->prepare('insert into #foo values
(1)');
> $sth->execute();
>
> generate an error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object
name
> '#foo'.
>
> So, the temporary table "disapears".... (I tested it on Sybase,
using
> DBD::Sybase, too, there is no an error). Since the two
statements are
> dynamically created (between come other statements), I cannot
execute
> in
> one batch $sth = $dbh->prepare('create table #foo (a int not
> null)
> insert into #foo values (1)); $sth->execute();
>
> at once...
>
> How can I avoid this problem?
>
> Regards!
>
> Andon
>
>
> ---------------------------------
> Sucker-punch spam with award-winning protection.
> Try the free Yahoo! Mail Beta.
>
>
> This message and any attachments (the "message") is
> intended solely for the addressees and is confidential.
> If you receive this message in error, please delete it and
> immediately notify the sender. Any use not in accord with
> its purpose, any dissemination or disclosure, either whole
> or partial, is prohibited except formal approval. The internet
> can not guarantee the integrity of this message.
> BNP PARIBAS (and its subsidiaries) shall (will) not
> therefore be liable for the message if modified.
>
> ---------------------------------------------
>
> Ce message et toutes les pieces jointes (ci-apres le
> "message") sont etablis a l'intention exclusive de ses
> destinataires et sont confidentiels. Si vous recevez ce
> message par erreur, merci de le detruire et d'en avertir
> immediatement l'expediteur. Toute utilisation de ce
> message non conforme a sa destination, toute diffusion
> ou toute publication, totale ou partielle, est interdite, sauf
> autorisation expresse. L'internet ne permettant pas
> d'assurer l'integrite de ce message, BNP PARIBAS (et ses
> filiales) decline(nt) toute responsabilite au titre de ce
> message, dans l'hypothese ou il aurait ete modifie.
>
>
>

--=_alternative 001F22CAC12572D8_=--

Re: temporary table "disapears"

An aside: Andon's report got me wondering if Oracle temp tables behave
correctly via DBI. My answer is: yes! Oracle only has the global temp
table model, but with data private to the session and may or maynot
survive a commit depending on how you have defined the temp table. I
blogged and posted my test case at
http://tardate.blogspot.com/2007/05/do-oracle-temp-tables-be have-correctly.html

On 5/11/07, michael.peppler@bnpparibas.com
wrote:
> You should run this with DBI->trace() turned on to see what DBD::ODBC
> actually does. The temp tables should only be dropped when the connection
> is closed.
>
> Michael
>
>
>
>
> Extranet
> campbelb@alcatel-lucent.com - 11.05.2007 00:19
>
>
> To: martin.evans, dbi-users
> cc:
> Subject: RE: temporary table "disapears"
>
> Martin, Autocommit off doesn't help local temps persist after the
> execute.
>
> Andon said that batching all the commands in the same execute is not an
> option for him, so the only working alternative so far is to consider
> global temps (##foo). They do persist after an execute and throughout
> an entire session.
>
> Consider these examples:
>
> my $s1 = 'create table #foo (a int not null)';
> my $s2 = 'insert into #foo values (1)';
> my $s3 = 'select * from #foo';
> $dbh->{AutoCommit} = 0; # trying to see if this help, but it
> doesn't
> my $sth;
> $sth = $dbh->prepare($s1);
> $sth->execute(); # works: table created
> $sth = $dbh->prepare($s1);
> $sth->execute(); # works: can recreate table because
> original is gone
> $sth = $dbh->prepare($s2);
> $sth->execute(); # doesn't work: table is gone
> $sth = $dbh->prepare($s3);
> $sth->execute(); # doesn't work: table is gone
> $sth = $dbh->prepare("$s1; $s2; $s3");
> $sth->execute(); # works: table exists across batched
> commands
>
> -----Original Message-----
> From: Martin Evans [mailto:martin.evans@easysoft.com]
> Sent: Thursday, May 10, 2007 7:39 AM
> To: dbi-users@perl.org
> Subject: Re: temporary table "disapears"
>
> CAMPBELL, BRIAN D (BRIAN) wrote:
> > You're right. It's the the other way around from what I said.
> > However, when I tested this yesterday it seemed I was getting an error
>
> > on the create command also. But I re-examined the results more
> > carefully today and the create worked OK; it was just the insert that
> > failed. However they were both run on the same connection (same $dbh
> > handle). So it seems that local temps don't persist after an
> > execute() call, as Andon supposed.
> >
>
> What if you turn autocommit off - do the temporary tables exist for
> longer then?
>
> Martin
> --
> Martin J. Evans
> Easysoft Limited
> http://www.easysoft.com
> > ________________________________
> >
> > From: michael.peppler@bnpparibas.com
> > [mailto:michael.peppler@bnpparibas.com]
> > Sent: Wednesday, May 09, 2007 10:49 PM
> > To: CAMPBELL, BRIAN D (BRIAN)
> > Cc: atschauschev@yahoo.com; dbi-users@perl.org
> > Subject: RE: temporary table "disapears"
> >
> >
> >
> > I'm pretty sure that #tmp is a local temporary table, and ##tmp
> is a
> > global temporary table...
> >
> > So the original problem is most likely that the create table
> #tmp and
> > the insert into #tmp statements aren't being run on the same physical
> > connection. I don't know DBD::ODBC, but I can tell you that
> > DBD::Sybase could possibly have opened a second connection under the
> > covers if it thought the first statement hadn't been completely
> > processed yet.
> >
> > Michael
> >
> >
> >
> >
> >
> > Extranet
> > campbelb@alcatel-lucent.com - 09.05.2007 18:40
> >
> >
> > To: atschauschev, dbi-users
> >
> > cc:
> >
> > Subject: RE: temporary table "disapears"
> >
> > Actually I tried this against SQL 2000, DBI 1.53 and DBD::ODBC
> > 1.13...
> >
> > You should be getting 2 errors, the same error from both
> prepares.
> > In
> > other words, #foo isn't being treated as a proper table name.
> > Naturally, these statements work fine if you just use foo (which
>
> > isn't
> > temp).
> >
> > However, #foo should represent a "global temp" table, and this
> is not
> > being accepted as a valid name. Not sure why.
> >
> > But ##foo works fine, and the table does persist across executes
>
> > while
> > the $dbh connection is open. With 2 #'s, it's a "local temp"
> > table
> > which means it's not visible to other sessions. If that's OK,
> > perhaps
> > you can use that instead.
> >
> >
> >
> > -----Original Message-----
> > From: Andon Tschauschev [mailto:atschauschev@yahoo.com]
> > Sent: Wednesday, May 09, 2007 8:31 AM
> > To: dbi-users@perl.org
> > Subject: temporary table "disapears"
> >
> > Hello,
> >
> > I am using DBI 1.51 and DBD::ODBC 1.13, connecting to MSSQL2005.
> >
> > Executing following statements:
> > $sth = $dbh->prepare('create table #foo (a int not null)');
> > $sth->execute(); $sth = $dbh->prepare('insert into #foo values
> (1)');
> > $sth->execute();
> >
> > generate an error:
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object
> name
> > '#foo'.
> >
> > So, the temporary table "disapears".... (I tested it on Sybase,
> using
> > DBD::Sybase, too, there is no an error). Since the two
> statements are
> > dynamically created (between come other statements), I cannot
> execute
> > in
> > one batch $sth = $dbh->prepare('create table #foo (a int not
> > null)
> > insert into #foo values (1)); $sth->execute();
> >
> > at once...
> >
> > How can I avoid this problem?
> >
> > Regards!
> >
> > Andon
> >
> >
> > ---------------------------------
> > Sucker-punch spam with award-winning protection.
> > Try the free Yahoo! Mail Beta.
> >
> >
> > This message and any attachments (the "message") is
> > intended solely for the addressees and is confidential.
> > If you receive this message in error, please delete it and
> > immediately notify the sender. Any use not in accord with
> > its purpose, any dissemination or disclosure, either whole
> > or partial, is prohibited except formal approval. The internet
> > can not guarantee the integrity of this message.
> > BNP PARIBAS (and its subsidiaries) shall (will) not
> > therefore be liable for the message if modified.
> >
> > ---------------------------------------------
> >
> > Ce message et toutes les pieces jointes (ci-apres le
> > "message") sont etablis a l'intention exclusive de ses
> > destinataires et sont confidentiels. Si vous recevez ce
> > message par erreur, merci de le detruire et d'en avertir
> > immediatement l'expediteur. Toute utilisation de ce
> > message non conforme a sa destination, toute diffusion
> > ou toute publication, totale ou partielle, est interdite, sauf
> > autorisation expresse. L'internet ne permettant pas
> > d'assurer l'integrite de ce message, BNP PARIBAS (et ses
> > filiales) decline(nt) toute responsabilite au titre de ce
> > message, dans l'hypothese ou il aurait ete modifie.
> >
> >
> >
>

RE: temporary table "disapears"

Michael,

Here is the trace (level 2) on the local temp create and insert. I
didn't see anything useful. Do you?

-----------During table create prepare:

-> prepare for DBD::ODBC::db (DBI::db=3DHASH(0x19b444c)~0x19b4740
'create table #foo (a int not null)') thr#2344ac
SQLPrepare returned 0

dbd_st_prepare'd sql f28456320, ExecDirect=3D0
create table #foo (a int not null)
<- prepare=3D DBI::st=3DHASH(0x19b4a4c) at odbc6.pl line 13

-----------During table create execute:

-> execute for DBD::ODBC::st (DBI::st=3DHASH(0x19b4a4c)~0x19b4980)
thr#2344ac
dbd_st_execute (for hstmt 28456320 before)...
dbd_describe sql 28456320: num_fields=3D0
dbd_describe skipped (no result cols) (sql f28456320)
dbd_st_execute got no rows: resetting ACTIVE, moreResults
<- execute=3D '0E0' at odbc6.pl line 15

-----------During table insert prepare:

-> prepare for DBD::ODBC::db (DBI::db=3DHASH(0x19b444c)~0x19b4740
'insert into #foo values (1)') thr#2344ac
SQLPrepare returned 0

dbd_st_prepare'd sql f28457400, ExecDirect=3D0
insert into #foo values (1)
<- prepare=3D DBI::st=3DHASH(0x19b4b00) at odbc6.pl line 17
-> DESTROY for DBD::ODBC::st (DBI::st=3DHASH(0x19b4980)~INNER)
thr#2344ac
<- DESTROY=3D undef at odbc6.pl line 18

-----------During table insert execute:

-> execute for DBD::ODBC::st (DBI::st=3DHASH(0x19b4b00)~0x19b4a7c)
thr#2344ac
dbd_st_execute (for hstmt 28457400 before)...
st_execute/SQLExecute error -1 recorded: [Microsoft][ODBC SQL Server
Driver][SQL Server]Invalid object name '#foo'. (SQL-42S02)
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be
prepared. (SQL-42000)(DBD: st_execute/SQLExecute err=3D-1)
!! ERROR: -1 '[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid
objectname '#foo'. (SQL-42S02)
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be
prepared. (SQL-42000)(DBD: st_execute/SQLExecute err=3D-1)' (err#0)
<- execute=3D undef at odbc6.pl line 19
DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver][SQL
Server]Invalid object name '#foo'. (SQL-42S02)
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be
prepared. (SQL-42000)(DBD: st_execute/SQLExecute err=3D-1) at odbc6.pl
line 19.

________________________________

From: michael.peppler@bnpparibas.com
[mailto:michael.peppler@bnpparibas.com]=20
Sent: Thursday, May 10, 2007 10:40 PM
To: CAMPBELL, BRIAN D (BRIAN)
Cc: martin.evans@easysoft.com; dbi-users@perl.org
Subject: RE: temporary table "disapears"
=09
=09

You should run this with DBI->trace() turned on to see what
DBD::ODBC actually does. The temp tables should only be dropped when the
connection is closed.=20
=09
Michael=20
=09
=09
=09
=09
Extranet=20
campbelb@alcatel-lucent.com - 11.05.2007 00:19=20
=20
=09
To: martin.evans, dbi-users=20

cc: =20

Subject: RE: temporary table "disapears"=20
=09
Martin, Autocommit off doesn't help local temps persist after
the
execute.
=09
Andon said that batching all the commands in the same execute is
not an
option for him, so the only working alternative so far is to
consider
global temps (##foo). They do persist after an execute and
throughout
an entire session.
=09
Consider these examples:
=09
my $s1 =3D 'create table #foo (a int not null)';
my $s2 =3D 'insert into #foo values (1)';
my $s3 =3D 'select * from #foo';
$dbh->{AutoCommit} =3D 0; # trying to see if this help, but
it
doesn't
my $sth;
$sth =3D $dbh->prepare($s1);
$sth->execute(); # works: table created
$sth =3D $dbh->prepare($s1);
$sth->execute(); # works: can recreate table
because
original is gone
$sth =3D $dbh->prepare($s2);
$sth->execute(); # doesn't work: table is gone
$sth =3D $dbh->prepare($s3);
$sth->execute(); # doesn't work: table is gone
$sth =3D $dbh->prepare("$s1; $s2; $s3");
$sth->execute(); # works: table exists across
batched
commands
=09
-----Original Message-----
From: Martin Evans [mailto:martin.evans@easysoft.com]
Sent: Thursday, May 10, 2007 7:39 AM
To: dbi-users@perl.org
Subject: Re: temporary table "disapears"
=09
CAMPBELL, BRIAN D (BRIAN) wrote:
> You're right. It's the the other way around from what I said.
> However, when I tested this yesterday it seemed I was getting
an error
=09
> on the create command also. But I re-examined the results
more
> carefully today and the create worked OK; it was just the
insert that
> failed. However they were both run on the same connection
(same $dbh
> handle). So it seems that local temps don't persist after an
> execute() call, as Andon supposed.
>
=09
What if you turn autocommit off - do the temporary tables exist
for
longer then?
=09
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
> ________________________________
>
> From: michael.peppler@bnpparibas.com
> [mailto:michael.peppler@bnpparibas.com]
> Sent: Wednesday, May 09, 2007 10:49 PM
> To: CAMPBELL, BRIAN D (BRIAN)
> Cc: atschauschev@yahoo.com; dbi-users@perl.org
> Subject: RE: temporary table "disapears"
>
>
>
> I'm pretty sure that #tmp is a local temporary table,
and ##tmp
is a
> global temporary table...
>
> So the original problem is most likely that the create
table
#tmp and
> the insert into #tmp statements aren't being run on the same
physical
> connection. I don't know DBD::ODBC, but I can tell you that
> DBD::Sybase could possibly have opened a second connection
under the
> covers if it thought the first statement hadn't been
completely
> processed yet.
>
> Michael
>
>
>
>
>
> Extranet
> campbelb@alcatel-lucent.com - 09.05.2007 18:40
>
>
> To: atschauschev, dbi-users
>
> cc:
>
> Subject: RE: temporary table "disapears"
>
> Actually I tried this against SQL 2000, DBI 1.53 and
DBD::ODBC
> 1.13...
>
> You should be getting 2 errors, the same error from
both
prepares.
> In
> other words, #foo isn't being treated as a proper
table name.
> Naturally, these statements work fine if you just use
foo (which
=09
> isn't
> temp).
>
> However, #foo should represent a "global temp" table,
and this
is not
> being accepted as a valid name. Not sure why.
>
> But ##foo works fine, and the table does persist
across executes
=09
> while
> the $dbh connection is open. With 2 #'s, it's a
"local temp"
> table
> which means it's not visible to other sessions. If
that's OK,
> perhaps
> you can use that instead.
>
>
>
> -----Original Message-----
> From: Andon Tschauschev
[mailto:atschauschev@yahoo.com]
> Sent: Wednesday, May 09, 2007 8:31 AM
> To: dbi-users@perl.org
> Subject: temporary table "disapears"
>
> Hello,
>
> I am using DBI 1.51 and DBD::ODBC 1.13, connecting to
MSSQL2005.
>
> Executing following statements:
> $sth =3D $dbh->prepare('create table #foo (a int not
null)');
> $sth->execute(); $sth =3D $dbh->prepare('insert into
#foo values
(1)');
> $sth->execute();
>
> generate an error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid
object
name
> '#foo'.
>
> So, the temporary table "disapears".... (I tested it
on Sybase,
using
> DBD::Sybase, too, there is no an error). Since the two
statements are
> dynamically created (between come other statements), I
cannot
execute
> in
> one batch $sth =3D $dbh->prepare('create table #foo (a
int not
> null)
> insert into #foo values (1)); $sth->execute();
>
> at once...
>
> How can I avoid this problem?
>
> Regards!
>
> Andon
>
>
> ---------------------------------
> Sucker-punch spam with award-winning protection.
> Try the free Yahoo! Mail Beta.
>
>
> This message and any attachments (the "message") is
> intended solely for the addressees and is
confidential.
> If you receive this message in error, please delete it
and
> immediately notify the sender. Any use not in accord
with
> its purpose, any dissemination or disclosure, either
whole
> or partial, is prohibited except formal approval. The
internet
> can not guarantee the integrity of this message.
> BNP PARIBAS (and its subsidiaries) shall (will) not
> therefore be liable for the message if modified.
>
>
---------------------------------------------
>
> Ce message et toutes les pieces jointes (ci-apres le
> "message") sont etablis a l'intention exclusive de ses
> destinataires et sont confidentiels. Si vous recevez
ce
> message par erreur, merci de le detruire et d'en
avertir
> immediatement l'expediteur. Toute utilisation de ce
> message non conforme a sa destination, toute diffusion
> ou toute publication, totale ou partielle, est
interdite, sauf
> autorisation expresse. L'internet ne permettant pas
> d'assurer l'integrite de ce message, BNP PARIBAS (et
ses
> filiales) decline(nt) toute responsabilite au titre de
ce
> message, dans l'hypothese ou il aurait ete modifie.
>
>
>=20
=09

RE: temporary table "disapears"

I believe I have a solution to the problem of supporting MS SQL local
temp tables without batching in a single prepare...

I've already established that global temps (##foo) persist after an
execute().
But to get local temps to persist (#foo) you need this attribute:

$dbh->{odbc_exec_direct} =3D 1;=20

However, local temps don't seem to persist after an error. Consider:

my $s1 =3D 'create table #foo (a int not null)';
my $s2 =3D 'insert into #foo values (1)';
my $sth;
$sth =3D $dbh->prepare($s1);
$sth->execute(); # works: table created
$sth =3D $dbh->prepare($s2);
$sth->execute(); # works: value inserted
$sth =3D $dbh->prepare($s1);
$sth->execute(); # doesn't work: table already exists
$sth =3D $dbh->prepare($s2);
$sth->execute(); # doesn't work: table gone because of
above error

Turning Autocommit off doesn't seem to alter this behavior.

Also, FYI, MS temp tables and the difference between global and local
temps is described here:

http://msdn2.microsoft.com/en-us/library/ms174979.aspx

-----Original Message-----
From: Paul Gallagher [mailto:gallagher.paul@gmail.com]=20
Sent: Friday, May 11, 2007 7:27 AM
To: michael.peppler@bnpparibas.com
Cc: CAMPBELL, BRIAN D (BRIAN); martin.evans@easysoft.com;
dbi-users@perl.org
Subject: Re: temporary table "disapears"

An aside: Andon's report got me wondering if Oracle temp tables behave
correctly via DBI. My answer is: yes! Oracle only has the global temp
table model, but with data private to the session and may or maynot
survive a commit depending on how you have defined the temp table. I
blogged and posted my test case at
http://tardate.blogspot.com/2007/05/do-oracle-temp-tables-be have-correct
ly.html

On 5/11/07, michael.peppler@bnpparibas.com
wrote:
> You should run this with DBI->trace() turned on to see what DBD::ODBC=20
> actually does. The temp tables should only be dropped when the=20
> connection is closed.
>
> Michael
>
>
>
>
> Extranet
> campbelb@alcatel-lucent.com - 11.05.2007 00:19
>
>
> To: martin.evans, dbi-users
> cc:
> Subject: RE: temporary table "disapears"
>
> Martin, Autocommit off doesn't help local temps persist after the=20
> execute.
>
> Andon said that batching all the commands in the same execute is not=20
> an option for him, so the only working alternative so far is to=20
> consider global temps (##foo). They do persist after an execute and=20
> throughout an entire session.
>
> Consider these examples:
>
> my $s1 =3D 'create table #foo (a int not null)'; my $s2 =3D 'insert =
into=20
> #foo values (1)'; my $s3 =3D 'select * from #foo';
> $dbh->{AutoCommit} =3D 0; # trying to see if this help, but it
> doesn't
> my $sth;
> $sth =3D $dbh->prepare($s1);
> $sth->execute(); # works: table created
> $sth =3D $dbh->prepare($s1);
> $sth->execute(); # works: can recreate table because
> original is gone
> $sth =3D $dbh->prepare($s2);
> $sth->execute(); # doesn't work: table is gone
> $sth =3D $dbh->prepare($s3);
> $sth->execute(); # doesn't work: table is gone
> $sth =3D $dbh->prepare("$s1; $s2; $s3");
> $sth->execute(); # works: table exists across batched
> commands
>

RE: temporary table "disapears"

We saw a similar problem creating temp tables with SQL Server. To solve
the issue we created the temp tables using the do method which keeps the
temp tables available to statement handles created against that database
handle.

HTH,
-D=20

-----Original Message-----
From: CAMPBELL, BRIAN D (BRIAN) [mailto:campbelb@alcatel-lucent.com]=20
Sent: Friday, May 11, 2007 12:34 PM
To: Paul Gallagher; michael.peppler@bnpparibas.com;
atschauschev@yahoo.com
Cc: martin.evans@easysoft.com; dbi-users@perl.org
Subject: RE: temporary table "disapears"

I believe I have a solution to the problem of supporting MS SQL local
temp tables without batching in a single prepare...

I've already established that global temps (##foo) persist after an
execute().
But to get local temps to persist (#foo) you need this attribute:

$dbh->{odbc_exec_direct} =3D 1;=20

However, local temps don't seem to persist after an error. Consider:

my $s1 =3D 'create table #foo (a int not null)';
my $s2 =3D 'insert into #foo values (1)';
my $sth;
$sth =3D $dbh->prepare($s1);
$sth->execute(); # works: table created
$sth =3D $dbh->prepare($s2);
$sth->execute(); # works: value inserted
$sth =3D $dbh->prepare($s1);
$sth->execute(); # doesn't work: table already exists
$sth =3D $dbh->prepare($s2);
$sth->execute(); # doesn't work: table gone because of
above error

Turning Autocommit off doesn't seem to alter this behavior.

Also, FYI, MS temp tables and the difference between global and local
temps is described here:

http://msdn2.microsoft.com/en-us/library/ms174979.aspx

-----Original Message-----
From: Paul Gallagher [mailto:gallagher.paul@gmail.com]=20
Sent: Friday, May 11, 2007 7:27 AM
To: michael.peppler@bnpparibas.com
Cc: CAMPBELL, BRIAN D (BRIAN); martin.evans@easysoft.com;
dbi-users@perl.org
Subject: Re: temporary table "disapears"

An aside: Andon's report got me wondering if Oracle temp tables behave
correctly via DBI. My answer is: yes! Oracle only has the global temp
table model, but with data private to the session and may or maynot
survive a commit depending on how you have defined the temp table. I
blogged and posted my test case at
http://tardate.blogspot.com/2007/05/do-oracle-temp-tables-be have-correct
ly.html

On 5/11/07, michael.peppler@bnpparibas.com
wrote:
> You should run this with DBI->trace() turned on to see what DBD::ODBC=20
> actually does. The temp tables should only be dropped when the=20
> connection is closed.
>
> Michael
>
>
>
>
> Extranet
> campbelb@alcatel-lucent.com - 11.05.2007 00:19
>
>
> To: martin.evans, dbi-users
> cc:
> Subject: RE: temporary table "disapears"
>
> Martin, Autocommit off doesn't help local temps persist after the=20
> execute.
>
> Andon said that batching all the commands in the same execute is not=20
> an option for him, so the only working alternative so far is to=20
> consider global temps (##foo). They do persist after an execute and=20
> throughout an entire session.
>
> Consider these examples:
>
> my $s1 =3D 'create table #foo (a int not null)'; my $s2 =3D 'insert into=
=20
> #foo values (1)'; my $s3 =3D 'select * from #foo';
> $dbh->{AutoCommit} =3D 0; # trying to see if this help, but it
> doesn't
> my $sth;
> $sth =3D $dbh->prepare($s1);
> $sth->execute(); # works: table created
> $sth =3D $dbh->prepare($s1);
> $sth->execute(); # works: can recreate table because
> original is gone
> $sth =3D $dbh->prepare($s2);
> $sth->execute(); # doesn't work: table is gone
> $sth =3D $dbh->prepare($s3);
> $sth->execute(); # doesn't work: table is gone
> $sth =3D $dbh->prepare("$s1; $s2; $s3");
> $sth->execute(); # works: table exists across batched
> commands
>

____________________________________________________________ _______________=
__________

The information contained in this email may be confidential and/or legally=
privileged. It has been sent for the sole use of the intended=
recipient(s). If the reader of this message is not an intended recipient,=
you are hereby notified that any unauthorized review, use, disclosure,=
dissemination, distribution, or copying of this communication, or any of=
its contents, is strictly prohibited. If you have received this=
communication in error, please contact the sender by reply email and=
destroy all copies of the original message. To contact our email=
administrator directly, send to postmaster@dlapiper.com

Thank you.
____________________________________________________________ _______________=
__________

RE: temporary table "disapears"

--0-1278426433-1179317097=:76178
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

Hello,

first of all, I want to thank you for your responses.

I cannot use do() to create the temp tables, it means for me that I must scan each statement for temp tables, extract it (if present), execute it separately with do() and the rest with prepare() and execute().

Global temp tables are not solution too, for example: if two clients use the same procedure simultaneously through different connections, which procedure in turn uses global temp table, what happens: the values from first client mess with the values with the second in the global temp table.

The solution, which work fine for me, is setting odbc_exec_direct to 1 (submitted from BRIAN). I read the documentation in DBD::ODBC about odbc_exec_direct, I didn't understand what really does, but it works :) .

Thanks!

Andon


"Priest, Darryl" wrote:
We saw a similar problem creating temp tables with SQL Server. To solve
the issue we created the temp tables using the do method which keeps the
temp tables available to statement handles created against that database
handle.

HTH,
-D

-----Original Message-----
From: CAMPBELL, BRIAN D (BRIAN) [mailto:campbelb@alcatel-lucent.com]
Sent: Friday, May 11, 2007 12:34 PM
To: Paul Gallagher; michael.peppler@bnpparibas.com;
atschauschev@yahoo.com
Cc: martin.evans@easysoft.com; dbi-users@perl.org
Subject: RE: temporary table "disapears"

I believe I have a solution to the problem of supporting MS SQL local
temp tables without batching in a single prepare...

I've already established that global temps (##foo) persist after an
execute().
But to get local temps to persist (#foo) you need this attribute:

$dbh->{odbc_exec_direct} = 1;

However, local temps don't seem to persist after an error. Consider:

my $s1 = 'create table #foo (a int not null)';
my $s2 = 'insert into #foo values (1)';
my $sth;
$sth = $dbh->prepare($s1);
$sth->execute(); # works: table created
$sth = $dbh->prepare($s2);
$sth->execute(); # works: value inserted
$sth = $dbh->prepare($s1);
$sth->execute(); # doesn't work: table already exists
$sth = $dbh->prepare($s2);
$sth->execute(); # doesn't work: table gone because of
above error

Turning Autocommit off doesn't seem to alter this behavior.

Also, FYI, MS temp tables and the difference between global and local
temps is described here:

http://msdn2.microsoft.com/en-us/library/ms174979.aspx

-----Original Message-----
From: Paul Gallagher [mailto:gallagher.paul@gmail.com]
Sent: Friday, May 11, 2007 7:27 AM
To: michael.peppler@bnpparibas.com
Cc: CAMPBELL, BRIAN D (BRIAN); martin.evans@easysoft.com;
dbi-users@perl.org
Subject: Re: temporary table "disapears"

An aside: Andon's report got me wondering if Oracle temp tables behave
correctly via DBI. My answer is: yes! Oracle only has the global temp
table model, but with data private to the session and may or maynot
survive a commit depending on how you have defined the temp table. I
blogged and posted my test case at
http://tardate.blogspot.com/2007/05/do-oracle-temp-tables-be have-correct
ly.html

On 5/11/07, michael.peppler@bnpparibas.com
wrote:
> You should run this with DBI->trace() turned on to see what DBD::ODBC
> actually does. The temp tables should only be dropped when the
> connection is closed.
>
> Michael
>
>
>
>
> Extranet
> campbelb@alcatel-lucent.com - 11.05.2007 00:19
>
>
> To: martin.evans, dbi-users
> cc:
> Subject: RE: temporary table "disapears"
>
> Martin, Autocommit off doesn't help local temps persist after the
> execute.
>
> Andon said that batching all the commands in the same execute is not
> an option for him, so the only working alternative so far is to
> consider global temps (##foo). They do persist after an execute and
> throughout an entire session.
>
> Consider these examples:
>
> my $s1 = 'create table #foo (a int not null)'; my $s2 = 'insert into
> #foo values (1)'; my $s3 = 'select * from #foo';
> $dbh->{AutoCommit} = 0; # trying to see if this help, but it
> doesn't
> my $sth;
> $sth = $dbh->prepare($s1);
> $sth->execute(); # works: table created
> $sth = $dbh->prepare($s1);
> $sth->execute(); # works: can recreate table because
> original is gone
> $sth = $dbh->prepare($s2);
> $sth->execute(); # doesn't work: table is gone
> $sth = $dbh->prepare($s3);
> $sth->execute(); # doesn't work: table is gone
> $sth = $dbh->prepare("$s1; $s2; $s3");
> $sth->execute(); # works: table exists across batched
> commands
>

____________________________________________________________ _________________________

The information contained in this email may be confidential and/or legally privileged. It has been sent for the sole use of the intended recipient(s). If the reader of this message is not an intended recipient, you are hereby notified that any unauthorized review, use, disclosure, dissemination, distribution, or copying of this communication, or any of its contents, is strictly prohibited. If you have received this communication in error, please contact the sender by reply email and destroy all copies of the original message. To contact our email administrator directly, send to postmaster@dlapiper.com

Thank you.
____________________________________________________________ _________________________



---------------------------------
Ready for the edge of your seat? Check out tonight's top picks on Yahoo! TV.
--0-1278426433-1179317097=:76178--