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=HASH(0x19b444c)~0x19b4740
'create table #foo (a int not null)') thr#2344ac
SQLPrepare returned 0
dbd_st_prepare'd sql f28456320, ExecDirect=0
create table #foo (a int not null)
<- prepare= DBI::st=HASH(0x19b4a4c) at odbc6.pl line 13
-----------During table create execute:
-> execute for DBD::ODBC::st (DBI::st=HASH(0x19b4a4c)~0x19b4980)
thr#2344ac
dbd_st_execute (for hstmt 28456320 before)...
dbd_describe sql 28456320: num_fields=0
dbd_describe skipped (no result cols) (sql f28456320)
dbd_st_execute got no rows: resetting ACTIVE, moreResults
<- execute= '0E0' at odbc6.pl line 15
-----------During table insert prepare:
-> prepare for DBD::ODBC::db (DBI::db=HASH(0x19b444c)~0x19b4740
'insert into #foo values (1)') thr#2344ac
SQLPrepare returned 0
dbd_st_prepare'd sql f28457400, ExecDirect=0
insert into #foo values (1)
<- prepare= DBI::st=HASH(0x19b4b00) at odbc6.pl line 17
-> DESTROY for DBD::ODBC::st (DBI::st=HASH(0x19b4980)~INNER)
thr#2344ac
<- DESTROY= undef at odbc6.pl line 18
-----------During table insert execute:
-> execute for DBD::ODBC::st (DBI::st=HASH(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=-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=-1)' (err#0)
<- execute= 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=-1) at odbc6.pl
line 19.
________________________________
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
Sent: Thursday, May 10, 2007 10:40 PM
To: CAMPBELL, BRIAN D (BRIAN)
Cc: [EMAIL PROTECTED]; dbi-users@perl.org
Subject: RE: temporary table "disapears"
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
[EMAIL PROTECTED] - 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:[EMAIL PROTECTED]
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: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, May 09, 2007 10:49 PM
> To: CAMPBELL, BRIAN D (BRIAN)
> Cc: [EMAIL PROTECTED]; 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
> [EMAIL PROTECTED] - 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:[EMAIL PROTECTED]
> 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.
>
>
>
