Learn how easy it is to sync an existing GitHub or Google Code repo to a SourceForge project! See Demo

Close

#1059 Cannot create / drop procedures in HSQLDB

closed-fixed
nobody
None
5
2012-11-08
2012-11-06
Stephan Zodrow
No

I run the next block:

CREATE FUNCTION an_hour_before (t TIMESTAMP)
RETURNS TIMESTAMP
RETURN t - 1 HOUR

and get
Error: Java execution
SQLState: 46000
ErrorCode: -6000
This runs e. g. in OOBase without problems
Same applies when I try to drop a procedure:
drop procedure proc_name;
gives Error:
Java execution
SQLState: 46000
ErrorCode: -6000

I tried changing the Statement delimiter to | or \ but that didn't help.
I used Squirrel 3.4.0, Java 1.6.0_05, HyperSQL version 2.2.9

Discussion

  • Rob Manning
    Rob Manning
    2012-11-06

    Seems to work for me with the in-memory driver:

    Query 1 of 1, Rows read: 0, Elapsed time (seconds) - Total: 0.001, SQL query: 0.001, Reading results: 0
    Error: SQL routine exception: routine signature already exists in statement [CREATE FUNCTION an_hour_before (t TIMESTAMP)
    RETURNS TIMESTAMP
    RETURN t - 1 HOUR]
    SQLState: 42605
    ErrorCode: -5605

    First SQL statement creates the function normally. Second attempt fails with a "already exists" error.

    Rob

     
  • Stephan Zodrow
    Stephan Zodrow
    2012-11-07

    Hi Rob,

    with in-memory driver it's OK in fact but I need concurrent access and use the server setup with HSQLDB Server driver. That setup is not working.

     
  • Rob Manning
    Rob Manning
    2012-11-07

    I don't see how this is a SQuirreL issue. SQuirreL doesn't know what type of driver it is working with (in-memory or standalone server). It executes SQL statements in the exact same way. In this case, for some reason the standalone HSQLDB server is returning an error for your DDL change. My guess is it has something to do with the way that the HSQLDB server is configured, or perhaps the credentials you are using to connect with don't have the necessary privielges to make this change. Bottom line is, SQuirreL sends your SQL statement to the server and in one case (standalone) you see an error, but in another case (in-memory) it succeeds. The difference between the two cases is HSQDLDB, not SQuirreL.

    Rob

    Rob

     
  • Stephan Zodrow
    Stephan Zodrow
    2012-11-07

    For shure it has nothing to do with HSQLDB server version. I can execute the SQL in Office Office Base under Extras, Execute SQL and then see the procedure in SQuirreL. I suspect it has to do with the Database Driver. Can you tell me the origin of "your" HSQLDB Server driver Drivers?
    Is it possible to make others JDBC drivers active?

     
  • Stephan Zodrow
    Stephan Zodrow
    2012-11-08

    • status: open --> closed-fixed
     
  • Stephan Zodrow
    Stephan Zodrow
    2012-11-08

    I tested all configurations again with more care. The driver doesn't matter.
    Crucial thing is to change the Statement Separator under Session properties/tab SQL/field "Statement Separator" to something different from semicolon (";", I tested with "|", "~", "\" and "/", all work. Strange enough "/" works even with multiline comments (/* */) inside the procedure)