Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

does a temp table exist?

4 posts in General Discussion Last posting was on 2009-05-18 06:39:59.0Z
Robin Posted on 2009-05-06 10:26:10.0Z
From: "robin" <.@iol.it>
Newsgroups: ianywhere.public.general
Subject: does a temp table exist?
Lines: 8
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a0165c2@forums-1-dub>
Date: 6 May 2009 03:26:10 -0700
X-Trace: forums-1-dub 1241605570 10.22.241.152 (6 May 2009 03:26:10 -0700)
X-Original-Trace: 6 May 2009 03:26:10 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7539
Article PK: 5948

How can I check if a temp table exists?

Within systable it seems not present.

Thanks,
Robin


Robin Posted on 2009-05-06 10:38:32.0Z
From: "robin" <.@iol.it>
Newsgroups: ianywhere.public.general
References: <4a0165c2@forums-1-dub>
Subject: Re: does a temp table exist?
Lines: 22
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a0168a8@forums-1-dub>
Date: 6 May 2009 03:38:32 -0700
X-Trace: forums-1-dub 1241606312 10.22.241.152 (6 May 2009 03:38:32 -0700)
X-Original-Trace: 6 May 2009 03:38:32 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7540
Article PK: 5947

Solved using:

begin
drop table MyTempTable;
EXCEPTION WHEN OTHERS THEN
end;

Found here:
http://sqlanywhere.blogspot.com/2008/02/tip-all-encompassing-try-catch.html

Robin

"robin" <.@iol.it> ha scritto nel messaggio news:4a0165c2@forums-1-dub...

> How can I check if a temp table exists?
>
> Within systable it seems not present.
>
> Thanks,
> Robin
>


Breck Carter [TeamSybase] Posted on 2009-05-06 11:47:32.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__breck.carter@gmail.com>
Newsgroups: ianywhere.public.general
Subject: Re: does a temp table exist?
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__breck.carter@gmail.com
Message-ID: <met2051h4na510p9teh0l39o38dh9vasro@4ax.com>
References: <4a0165c2@forums-1-dub> <4a0168a8@forums-1-dub>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 6 May 2009 04:47:32 -0700
X-Trace: forums-1-dub 1241610452 10.22.241.152 (6 May 2009 04:47:32 -0700)
X-Original-Trace: 6 May 2009 04:47:32 -0700, vip152.sybase.com
Lines: 67
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7541
Article PK: 5949

Cool! I saw your question, said "I know the answer, if I could only
remember what it is..." then saw you found it :)

BUT... you found the answer to a different question (probably the
question that you were really asking): "how do I drop a temp table
without getting an error if it doesn't exist?"

That's the more popular question, but sometimes folks actually do want
an answer to the exact question "does temp table t exist or not?"

CREATE FUNCTION f_table_is_ok
( IN @table_name VARCHAR ( 128 ) )
RETURNS INTEGER
ON EXCEPTION RESUME
BEGIN
DECLARE @dummy INTEGER;
EXECUTE IMMEDIATE STRING (
'SELECT TOP 1 1 INTO @dummy FROM ',
@table_name,
' ORDER BY 1' );
IF SQLSTATE IN ( '00000', '02000' ) THEN
RETURN 1
ELSE
RETURN 0
END IF;
END;

BEGIN
DECLARE LOCAL TEMPORARY TABLE tt ( c INTEGER );
DECLARE LOCAL TEMPORARY TABLE "t t" ( c INTEGER );
SELECT f_table_is_ok ( 'asdf' );
SELECT f_table_is_ok ( 'tt' );
SELECT f_table_is_ok ( '"t t"' );
SELECT f_table_is_ok ( '"SYS"."SYSTABLE"' );
END;

Breck

On 6 May 2009 03:38:32 -0700, "robin" <.@iol.it> wrote:

>Solved using:
>
>begin
> drop table MyTempTable;
> EXCEPTION WHEN OTHERS THEN
>end;
>
>Found here:
>http://sqlanywhere.blogspot.com/2008/02/tip-all-encompassing-try-catch.html
>
>Robin
>
>"robin" <.@iol.it> ha scritto nel messaggio news:4a0165c2@forums-1-dub...
>> How can I check if a temp table exists?
>>
>> Within systable it seems not present.
>>
>> Thanks,
>> Robin
>>
>

--
Breck Carter http://sqlanywhere.blogspot.com/

RisingRoad SQL Anywhere and MobiLink Professional Services
breck.carter@risingroad.com


Robin Posted on 2009-05-18 06:39:59.0Z
From: "robin" <.@iol.it>
Newsgroups: ianywhere.public.general
References: <4a0165c2@forums-1-dub> <4a0168a8@forums-1-dub> <met2051h4na510p9teh0l39o38dh9vasro@4ax.com>
Subject: Re: does a temp table exist?
Lines: 77
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a1102bf@forums-1-dub>
Date: 17 May 2009 23:39:59 -0700
X-Trace: forums-1-dub 1242628799 10.22.241.152 (17 May 2009 23:39:59 -0700)
X-Original-Trace: 17 May 2009 23:39:59 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7563
Article PK: 5969

Yes Breck, it seems exactly as you said :)

Thank you very much for your useful sample.

robin

"Breck Carter [TeamSybase]" <NOSPAM__breck.carter@gmail.com> ha scritto nel
messaggio news:met2051h4na510p9teh0l39o38dh9vasro@4ax.com...

> Cool! I saw your question, said "I know the answer, if I could only
> remember what it is..." then saw you found it :)
>
> BUT... you found the answer to a different question (probably the
> question that you were really asking): "how do I drop a temp table
> without getting an error if it doesn't exist?"
>
> That's the more popular question, but sometimes folks actually do want
> an answer to the exact question "does temp table t exist or not?"
>
> CREATE FUNCTION f_table_is_ok
> ( IN @table_name VARCHAR ( 128 ) )
> RETURNS INTEGER
> ON EXCEPTION RESUME
> BEGIN
> DECLARE @dummy INTEGER;
> EXECUTE IMMEDIATE STRING (
> 'SELECT TOP 1 1 INTO @dummy FROM ',
> @table_name,
> ' ORDER BY 1' );
> IF SQLSTATE IN ( '00000', '02000' ) THEN
> RETURN 1
> ELSE
> RETURN 0
> END IF;
> END;
>
> BEGIN
> DECLARE LOCAL TEMPORARY TABLE tt ( c INTEGER );
> DECLARE LOCAL TEMPORARY TABLE "t t" ( c INTEGER );
> SELECT f_table_is_ok ( 'asdf' );
> SELECT f_table_is_ok ( 'tt' );
> SELECT f_table_is_ok ( '"t t"' );
> SELECT f_table_is_ok ( '"SYS"."SYSTABLE"' );
> END;
>
> Breck
>
> On 6 May 2009 03:38:32 -0700, "robin" <.@iol.it> wrote:
>
>>Solved using:
>>
>>begin
>> drop table MyTempTable;
>> EXCEPTION WHEN OTHERS THEN
>>end;
>>
>>Found here:
>>http://sqlanywhere.blogspot.com/2008/02/tip-all-encompassing-try-catch.html
>>
>>Robin
>>
>>"robin" <.@iol.it> ha scritto nel messaggio news:4a0165c2@forums-1-dub...
>>> How can I check if a temp table exists?
>>>
>>> Within systable it seems not present.
>>>
>>> Thanks,
>>> Robin
>>>
>>
>
> --
> Breck Carter http://sqlanywhere.blogspot.com/
>
> RisingRoad SQL Anywhere and MobiLink Professional Services
> breck.carter@risingroad.com