Re: temporary table "disapears"

Thu, 10 May 2007 07:40:32 -0700 

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.

Reply via email to