From: Pfrommer. Dominik (D.Pfrommerseeburger.de)
Date: Mon Mar 29 2004 - 02:47:51 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

hi again.

hrm. if i try to execute the following script, the database is crashing :( i mean, the windows service is not running anymore after executing the script. output in cmd is the following:

C:\Programme\sdb\programs\bin>sqlcli -u xxx,xxx -d myDB -i C:\temp\createMaxDB.sql
* -10807: Connection down: [4] connection broken

<sqlscript>

CREATE TABLE TESTTABLE(TESTCOL INT)
//
CREATE DBPROC MYDROP (IN TABLENAME VARCHAR(32))
AS
VAR
   dropStmt VARCHAR(100);
SELECT TABLENAME FROM DOMAIN.TABLES where owner = USER AND tablename = :TABLENAME;
IF $COUNT <> 0 THEN
BEGIN
  dropStmt = 'DROP TABLE ' || TABLENAME;
  execute dropStmt;
END;
//
CALL MYDROP('TESTTABLE')

</sqlscript>

thanks for your help!

regards,

  Dominik

-----Ursprüngliche Nachricht-----
Von: Anhaus, Thomas [mailto:thomas.anhaussap.com]
Gesendet: Montag, 29. März 2004 07:33
An: Pfrommer. Dominik; maxdblists.mysql.com
Betreff: RE: DROP TABLE IF EXISTS

Dominik Pfrommer wrote :

>hi everybody...

>i am searching for sth. equivalent like:

>MySQL:
>DROP TABLE IF EXISTS tMyTable

>M$SQL:
>if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tMyTable]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)
>drop table [dbo].[tMyTable]
>GO

>can anybody help me? as a workaround i tried to create a dbproc like:

>CREATE TABLE TESTTABLE(TESTCOL INT)
>//
>CREATE DBPROC MYDROP (IN TABLENAME VARCHAR(32))
>AS
>SELECT TABLENAME FROM DOMAIN.TABLES where tablename = :TABLENAME;
>IF $COUNT <> 0 THEN BEGIN
>DROP TABLE :TABLENAME;
>END;
>//
>CALL MYDROP('TESTTABLE')

>but that one doesn't work either, 'cause as it seems, i only can drop TEMP.xxx tables in a dbprocedure :(

>i would be very happy if anyone can help.

>thx!

Please try the following db-procedure :

CREATE DBPROC MYDROP (IN TABLENAME VARCHAR(32))
AS
VAR
   dropStmt CHAR(100);
SELECT TABLENAME FROM DOMAIN.TABLES where owner = USER AND tablename = :TABLENAME;
IF $COUNT <> 0 THEN BEGIN
dropStmt = 'DROP TABLE ' || TABLENAME;
execute dropStmt;
IF $rc <> 0
THEN
    STOP (-31001, 'unexpected error');
END;

Best Regards,
Thomas

  Dominik

--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/maxdb?unsub=thomas.anhaussap.com

--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/maxdb?unsub=d.pfrommerseeburger.de

--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]