Given this script, why does the *DELETE FROM StudioList" not give an error 401 if the table isn't exisiting yet? It seems to return 700, but isn't very helpfull, as this a so generic error. If I use EMTPY TABLE instead DELETE FROM I get the error code 401.


   BEGIN
     EXECUTE IMMEDIATE 'DELETE FROM StudioList';
   EXCEPTION
     IF ERRORCODE() = 401 THEN     <<<< doesn't work
       EXECUTE IMMEDIATE '
         CREATE TABLE "StudioList"
         (
         "StudioName" VARCHAR(30) COLLATE "ANSI_CI",
         "StudioURL" VARCHAR(60) COLLATE "ANSI_CI",
         "LastRelease" DATE,
         "VideoCount" INTEGER,
         CONSTRAINT "PrimaryKey" PRIMARY KEY ("StudioName")
         )';
     ELSE
       RAISE;
     END IF;
   END;

Rolf

Not sure why this is returning error 700 rather than 401 ... that is more of a question for Tim. He may have a really good reason, or more likely he could fix it in the next build.

However, would it not be better to test for the existence of the table (i.e. SELECT Name FROM Information.Tables WHERE Name = 'xxx')  open the cursor & check if the Cursor.RowCount > 0?

If RowCount = 0 then
 Create Table ...

Then you would not be reliant on raising an exception, or a particular error-code, which works fine I know, but does have some in-built risks.

Rolf,

<< Given this script, why does the *DELETE FROM StudioList" not give an error 401 if the table isn't exisiting yet? It seems to return 700, but isn't very helpfull, as this a so generic error. If I use EMTPY TABLE instead DELETE FROM I get the error code 401. >>

This is just down to the differences in the statements being executed.  The DELETE statement is a DML statement, whereas the EMPTY TABLE statement is an administrative statement.  The DELETE error occurs during compilation, whereas the EMPTY TABLE, like all administrative statements, does not really have a "compilation" phase because its table bindings are not held during the prepare/unprepared cycle of the statement.

In general, all DML statements (INSERT, UPDATE, and DELETE) will have like the DELETE statement, and all non-DML statements will behave like the EMPTY TABLE statement.

Tim Young
Elevate Software
www.elevatesoft.com

OK, but how can I than get the detailed error code, as 700 is a generic error code which doesn't realy say anything about the real error.

I can see the real error code in the error message, but I should also be able to get that error code somehow in my script. Is there a way to get that 401 code in my script?

"ElevateDB Error #700 An error was found in the statement at line 6 and column 25 (ElevateDB Error #401 The table or view StudioList does not exist in the schema Default)"

Rolf


Probably not the best solution but you could do something like

  IF (ERRORCODE() = 401) OR ((ERRORCODE() = 700) AND (0<>POS('401',ERRORMSG)))  THEN

Roy Lambert

Yes this is a very ugly solution. I hope Tim will add an additional error code routine which gives the correct "sub"-error back. Something like this:

ERRORCODE() = 700
SQLERROR() = 401

Rolf,

<< OK, but how can I than get the detailed error code, as 700 is a generic error code which doesn't realy say anything about the real error. >>

Actually, it does - it's telling you that it can't compile the SQL statement because it can't bind to the StudioList table.

If you want to ensure that you create the StudioList table if it doesn't exist, then don't rely on the SQL compilation to tell you.  Instead, do a propery query on the Information.Tables system information table and see if it exists.  If it doesn't, then create it.  *Then* try to execute the SQL statement.

Tim Young
Elevate Software
www.elevatesoft.com