RE: Create a missing table in SQL ?

["Walden H. Leverich III" <WaldenL@xxxxxxxxxxxxxxx>]

I've never know STRSQL to accept "programming-like" commands. I've never
tried it, but what about RUNSQLSTM? 

-Walden 


------------
Walden H Leverich III
President
Tech Software
(516) 627-3800 x11
(208) 692-3308 eFax
WaldenL@xxxxxxxxxxxxxxx
http://www.TechSoftInc.com 

Quiquid latine dictum sit altum viditur.
(Whatever is said in Latin seems profound.)
 
-----Original Message-----
From: Hall, Philip [mailto:phall@xxxxxxxx] 
Sent: Tuesday, October 07, 2003 2:38 PM
To: Midrange Systems Technical Discussion
Subject: RE: Create a missing table in SQL ?

Thanks Bill.

The problem I'm really having is that I can't seem to get the interactive
SQL tool to accept any SQL that uses DECLARE xxx

In SQLServer & Oracle, I can type SQL as below into their respective
interactive SQL tools (to test) and it gets executed.

For SQLServer the working SQL is;

if exists (select * from dbo.sysobjects where id =
object_id(N'[0a0a48cc0018cb3a
000000f6433b730e8188]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
   TRUNCATE TABLE [0a0a48cc0018cb3a000000f6433b730e8188] 
ELSE 
   CREATE TABLE "0a0a48cc0018cb3a000000f6433b730e8188" 
   (    MODEL_ID nvarchar (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, 
        ENTY_ID varchar (255) NULL,
        ...
    )

For Oracle it's;

    declare n number;
    table_not_exist exception;
    pragma exception_init(table_not_exist,-00942);
    begin 
        execute immediate 'truncate table "$user$"."$tableName$"';
        commit; 
        exception 
        when table_not_exist then
            execute immediate 'CREATE TABLE 
                $tableName$ 
                ( MODEL_ID varchar (255) NULL, 
                ENTY_ID varchar (255) NULL, 
                ...
                ) TABLESPACE "SMS_DM_CRMCAT"';
        commit;
    end;

Both work as is in the interactive SQL tool.

What's up with the UDB2 tool ?

--phil

> -----Original Message-----
> From: Bill [mailto:billzbubb@xxxxxxxxxx]
> Sent: Tuesday, October 07, 2003 1:20 PM
> To: Midrange Systems Technical Discussion
> Subject: Re: Create a missing table in SQL ?
> 
> 
> 
> Hall, Philip wrote:
> > BEGIN ATOMIC
> >     DECLARE v_rc INT DEFAULT 0;
> >     SELECT COUNT(*) INTO v_rc
> >         FROM SYSCAT.TABLES
> >         WHERE TABNAME = 'NAMES' ;
> >     IF v_rc = 0 THEN
> >         CREATE TABLE NAMES........;
> >     ELSE
> >   DROP TABEL NAMES;
> >         CREATE TABLE NAMES........;
> >     END IF;
> > END
> 
> Just a suggestion:  Why not change the If statement to be <> 
> 0 then Drop
> the table.  Then Create the table.
> 
> Bill
> 
> _______________________________________________
> This is the Midrange Systems Technical Discussion 
> (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
> 
> 

_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.