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

Close

#445 HSQLDB->Objects->Content

Squirrel SQL
closed-fixed
Rob Manning
Core (461)
5
2015-01-08
2006-11-14
Anonymous
No

When using HSQLDB and select Table in Objects tab and
then select Content, the following exception is thrown:
java.sql.SQLException: Table not found in statement
[select tbl.* from "T_CALENDARENTRY"]
at org.hsqldb.jdbc.Util.sqlException(Unknown
Source)
at org.hsqldb.jdbc.jdbcStatement.fetchResult
(Unknown Source)
at org.hsqldb.jdbc.jdbcStatement.executeQuery
(Unknown Source)
at
net.sourceforge.squirrel_sql.client.session.mainpanel.o
bjecttree.tabs.table.ContentsTab.createDataSet
(ContentsTab.java:325)
at
net.sourceforge.squirrel_sql.client.session.mainpanel.o
bjecttree.tabs.BaseDataSetTab$1.run
(BaseDataSetTab.java:123)
at
net.sourceforge.squirrel_sql.fw.util.TaskExecuter.run
(TaskExecuter.java:82)
at java.lang.Thread.run(Thread.java:595)

And no data is shown.
The problem in this case is that the schema is not
specified and "tbl" is not recognized.

Discussion

  • Colin Bell
    Colin Bell
    2006-11-14

    • assigned_to: colbell --> nobody
     
  • Rob Manning
    Rob Manning
    2006-11-15

    Logged In: YES
    user_id=1287991
    Originator: NO

    How are you accessing your HSQLDB server (embedded, network server, etc.)? What version of the server (and driver) are you using. Can you give us a sample script that creates the table that will allows us to reproduce the problem.
    I access my hsqldb 1.8.0.2 server via the file url (jdbc:hsqldb:file:/tools/hsqldb-1_8_0_2/data/dbcopysrc) and I don't see this problem at all.

    Rob

     
  • Petar
    Petar
    2006-11-15

    Logged In: YES
    user_id=1021719
    Originator: NO

    Hi,

    I'm aslo using hsqldb 1.8.0.2 server with url: jdbc:hsqldb:hsql://localhost:9999/testdb
    I get this problem with all the tables in my DB. Here is sample script:
    CREATE TABLE "UM_ADDRESS"
    (
    GUID VARCHAR(255) PRIMARY KEY not null,
    DOMAIN VARCHAR(255),
    STREET VARCHAR(255),
    BUILDINGNUMBER VARCHAR(255),
    CITY VARCHAR(255),
    ZIPCODE VARCHAR(255),
    ZIPCODEEXTENSION VARCHAR(255),
    COUNTRY VARCHAR(255),
    REGION VARCHAR(255),
    LINE1 VARCHAR(255),
    LINE2 VARCHAR(255),
    CORPUS VARCHAR(255),
    FLAT VARCHAR(255),
    PRIME BOOLEAN,
    PERSON_ID VARCHAR(255)
    );

    It is created in schema named "USERMGNT"

    Cheers,
    Petar

     
  • Rob Manning
    Rob Manning
    2006-11-15

    Logged In: YES
    user_id=1287991
    Originator: NO

    So when you execute the following query in the SQL editor do you get an error?

    select * from T_CALENDARENTRY

    whereas

    select * from USERMGNT.T_CALENDARENTRY

    works fine - is that correct?

    If so, then my guess is that you may be able to work around this by specifying which schema use by default in the SQL editor by executing the following command:

    set schema USERMGMT

    I think the real cause of the problem is that we don't specify the schema/catalog in the query in ContentsTab and with the way you have connected to hsqldb, that is not a valid thing to do.

    If all of this is correct, then a possible fix would be to query the metadata for a flag (something like requires schema/catalog in table query) and if true, put the schema/catalog on front of the tablename.

    Rob

     
  • Petar
    Petar
    2006-11-21

    Logged In: YES
    user_id=1021719
    Originator: NO

    Yes, this workaround works, although it is not very suitable for me, because I have many schemas.
    You probably may think of a better solution if possible. With Oracle for example such problems do not exist with the same DB,

    Thanks anyway,
    Petar

     
  • Rob Manning
    Rob Manning
    2008-02-17

    • assigned_to: nobody --> manningr
    • status: open --> closed-fixed
     
  • Rob Manning
    Rob Manning
    2008-02-17

    Logged In: YES
    user_id=1287991
    Originator: NO

    Not only is this a problem for the default schema, but it also affects objects in the INFORMATION_SCHEMA as well. I noticed that HSQLDB reports "false" for supportsSchemasInDataManipulation. However, it also reports "true" for supportsSchemasInTableDefinitions. Our logic that qualifies table names with schemas uses supportsSchemasInDataManipulation, so it was not triggered. I added code to check for HSQLDB and always qualifies the name with the schema. This fix is in CVS and will be in the latest snapshot as well as 2.6.5.

    Rob