Dominik Pfrommer wrote :
>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
This is a known bug in 7.5.00.08, which is caused by the catalog select statement
inside the db-procedure (see
http://pts:1080/webpts?wptsdetail=yes&ErrorType=0&ErrorID=1126826).
In your case I recommend to do without the select statement :
CREATE DBPROC MYDROP (IN TABLENAME VARCHAR(32))
AS
VAR
dropStmt VARCHAR(100);
dropStmt = 'DROP TABLE ' || TABLENAME;
execute dropStmt;
if ($RC <> 0) AND ($rc <> -4004)
THEN
STOP (-31001, 'unexpected error ' || CHR($RC));
If the table does not exist, the error -4004 (unknown table name) is ignored by the
db-procedure.
Best Regards,
Thomas
-----Urspr�ngliche Nachricht-----
Von: Anhaus, Thomas [mailto:[EMAIL PROTECTED]
Gesendet: Montag, 29. M�rz 2004 07:33
An: Pfrommer. Dominik; [EMAIL PROTECTED]
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/[EMAIL PROTECTED]
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
