Hi, I use David Cornelius's function and tweaked it a bit to run in 2.04, but it seems that the result is always false

Can someone tell me where did I go wrong ?

Regards,


Surjanto

CREATE FUNCTION "TableExists" (IN "TableName" VARCHAR(40) COLLATE ANSI)
BEGIN
 DECLARE TblCursor CURSOR FOR TblStmt;
 DECLARE TblName VARCHAR;

 PREPARE TblStmt FROM
   'SELECT * FROM Information.Tables
    WHERE Name = ''+ TableName + ''';

 OPEN TblCursor;
 FETCH FIRST FROM TblCursor ('Name') INTO TblName;
 CLOSE TblCursor;
 UNPREPARE TblStmt;

 RETURN COALESCE(TblName, '''') = TableName;
END

Surjanto,

> Hi, I use David Cornelius's function and tweaked it a bit to run in 2.04, but it seems that the result is always false
>
> Can someone tell me where did I go wrong ?

The quotes seem to be wrong. Try this:

PREPARE TblStmt FROM
    'SELECT * FROM Information.Tables
     WHERE Name = ''' + Tablename + '''';

....


RETURN COALESCE(TblName, '') = TableName;

Regards Uli

Good catch, Uli.  Yes, it needed some embedded quotes.

Glad someone is finding the script useful.  

--
David Cornelius
Cornelius Concepts

Thanks Uli and David,

I changed it a little bit to allow case insensitive

ALTER FUNCTION "TableExists" (IN "TableName" VARCHAR(40) COLLATE ANSI_CI)
RETURNS BOOLEAN
BEGIN
 DECLARE TblCursor CURSOR FOR TblStmt;
 DECLARE TblName VARCHAR;

 PREPARE TblStmt FROM
   'SELECT * FROM Information.Tables
    WHERE Name = '''+ TableName + '''';

 OPEN TblCursor;
 FETCH FIRST FROM TblCursor ('Name') INTO TblName;
 CLOSE TblCursor;
 UNPREPARE TblStmt;

 RETURN COALESCE(UPPER(TblName1), '''') = UPPER(TableName);
END

Sorry the correct one should be

CREATE FUNCTION "TableExists" (IN "TableName" VARCHAR(40) COLLATE ANSI_CI)
RETURNS BOOLEAN
BEGIN
 DECLARE TblCursor CURSOR FOR TblStmt;
 DECLARE TblName VARCHAR;

 PREPARE TblStmt FROM
   'SELECT * FROM Information.Tables
    WHERE Name = '''+ TableName + '''';

 OPEN TblCursor;
 FETCH FIRST FROM TblCursor ('Name') INTO TblName;
 CLOSE TblCursor;
 UNPREPARE TblStmt;

 RETURN COALESCE(UPPER(TblName), '''') = UPPER(TableName);
END