[Solved] INFORMATION_SCHEMA in HSQLDB?

[ptoye]

Out of curiosity I thought I'd try to look at one of my HSQLDB databases from Base. As an experiment I tried the SQL SELECT * FROM INFORMATION_SCHEMA.TABLES but got the error message :

1: Table not found: SCHEMATA in statement [SELECT * FROM "INFORMATION_SCHEMA"."SCHEMATA"]

According to the HSQLDB user guide this view should be available. Doesn't this work in Base or am I dong something wrong?

[F3K Total]

Hi,

Code: Select all   Expand viewCollapse view

SELECT * FROM INFORMATION_SCHEMA.SYSTEM_TABLES

works

[ptoye]

Thanks - at least that doesn't give an error message. But I'm still confused - why doesn't my example work as the HSQLDB user guide explicitly states that it supports the ANSI standard:

SCHEMATA

Information on all the SCHEMA objects in the database

And how does one find out which views are supported if the documentation is misleading?

[F3K Total]

Hi,
if i look into the guide for HSQLDB 1.8 there is no

Code: Select all   Expand viewCollapse view

SELECT * FROM INFORMATION_SCHEMA.TABLES

You'll find all available on Page 60 ff. of

HyperSQL User Guide: HyperSQL Database Engine, aka HSQLDB
by The HSQL Development Group, Blaine Simpson, and Fred Toussi
$Revision: 3201 $
Published $Date: 2009-09-16 09:03:08 -0400 (Wed, 16 Sep 2009) $
Copyright 2002-2009 The HSQL Development Group. Permission is granted to distribute this document without any alteration under the terms
of the HSQLDB license.

The guide is within the .zip-package here
R

[ptoye]

HyperSQL User Guide: HyperSQL Database Engine, aka HSQLDB
by The HSQL Development Group, Blaine Simpson, and Fred Toussi

That's what I've been using - the HTML version on the HSQLDB site rather than the PDF.

if i look into the guide for HSQLDB 1.8 there is no

SELECT * FROM INFORMATION_SCHEMA.TABLES

But I was looking originally for INFORMATION_SCHEMA.SCHEMATA which is in the guide. I didn't mention the TABLES view (which as you point out doesn't exist).

[F3K Total]

ptoye,
you wrote:

As an experiment I tried the SQL SELECT * FROM INFORMATION_SCHEMA.TABLES but got the error message ...

I can't find that in the guide for HSQLDB 1.8 which is shipped with AOO/LO.
I also can't find INFORMATION_SCHEMA.SCHEMATA there.
You have to know, that the online

HTML version on the HSQLDB

is for HSQLDB 2.3, which is not shipped with AOO/LO, so it isn't valid! You have to use the version shipped with the above mentioned .zip or upgrade your DB.
Here a picture of your desired Statement, executed within a HSQLDB 2.3.0, working well:

230.png (5.92 KiB) Viewed 10405 times

R

[ptoye]

Ah! Light dawns. I'd not realised that the HSQLDB as shipped with AOO is out of date! I'll investigate the earlier version. Thanks a lot, that's really helpful.

[Later] It seems that the link you gave me isn't the same as the document you're quoting from: the download has a date of 2007/08/28 12:13:28 which is quite a bit earlier than yours. It's a shame that the user guides don't say which version they refer to, especially for a product which is in a state of flux. Or have I missed something?

[Greengiant224]

ptoye posted:
if i look into the guide for HSQLDB 1.8 there is no

Code: Select all   Expand viewCollapse view

    SELECT * FROM INFORMATION_SCHEMA.TABLES


In the vanilla hsql v1.8.10/or the newer 2.2.8/ 2.3.0 you need INFORMATION_SCHEMA.SYSTEM_TABLES.

I see the answer has already been posted by FK3 Total.

I believe there has been some changes to the "INFORMATION.SCHEMA" in the updates to the newer HSQL versions.

Hope this helps.

Greengiant224

[Sliderule]

It is very important to note, that, the information below, is for database back-end, HSQL, Version 1.8.0 . . . and . . . the INFORMATION_SCHEMA layout is different for HSQL Version 2.0 and afterwards.

Code: Select all   Expand viewCollapse view

TABLE_SCHEM         TABLE_NAME
------------------  -------------------------------------
INFORMATION_SCHEMA  SYSTEM_ALIASES
INFORMATION_SCHEMA  SYSTEM_ALLTYPEINFO
INFORMATION_SCHEMA  SYSTEM_AUTHORIZATIONS
INFORMATION_SCHEMA  SYSTEM_BESTROWIDENTIFIER
INFORMATION_SCHEMA  SYSTEM_CACHEINFO
INFORMATION_SCHEMA  SYSTEM_CATALOGS
INFORMATION_SCHEMA  SYSTEM_CHECK_COLUMN_USAGE
INFORMATION_SCHEMA  SYSTEM_CHECK_CONSTRAINTS
INFORMATION_SCHEMA  SYSTEM_CHECK_ROUTINE_USAGE
INFORMATION_SCHEMA  SYSTEM_CHECK_TABLE_USAGE
INFORMATION_SCHEMA  SYSTEM_CLASSPRIVILEGES
INFORMATION_SCHEMA  SYSTEM_COLLATIONS
INFORMATION_SCHEMA  SYSTEM_COLUMNPRIVILEGES
INFORMATION_SCHEMA  SYSTEM_COLUMNS
INFORMATION_SCHEMA  SYSTEM_CROSSREFERENCE
INFORMATION_SCHEMA  SYSTEM_INDEXINFO
INFORMATION_SCHEMA  SYSTEM_PRIMARYKEYS
INFORMATION_SCHEMA  SYSTEM_PROCEDURECOLUMNS
INFORMATION_SCHEMA  SYSTEM_PROCEDURES
INFORMATION_SCHEMA  SYSTEM_PROPERTIES
INFORMATION_SCHEMA  SYSTEM_ROLE_AUTHORIZATION_DESCRIPTORS
INFORMATION_SCHEMA  SYSTEM_SCHEMAS
INFORMATION_SCHEMA  SYSTEM_SCHEMATA
INFORMATION_SCHEMA  SYSTEM_SEQUENCES
INFORMATION_SCHEMA  SYSTEM_SESSIONINFO
INFORMATION_SCHEMA  SYSTEM_SESSIONS
INFORMATION_SCHEMA  SYSTEM_SUPERTABLES
INFORMATION_SCHEMA  SYSTEM_SUPERTYPES
INFORMATION_SCHEMA  SYSTEM_TABLE_CONSTRAINTS
INFORMATION_SCHEMA  SYSTEM_TABLEPRIVILEGES
INFORMATION_SCHEMA  SYSTEM_TABLES
INFORMATION_SCHEMA  SYSTEM_TABLETYPES
INFORMATION_SCHEMA  SYSTEM_TEXTTABLES
INFORMATION_SCHEMA  SYSTEM_TRIGGERCOLUMNS
INFORMATION_SCHEMA  SYSTEM_TRIGGERS
INFORMATION_SCHEMA  SYSTEM_TYPEINFO
INFORMATION_SCHEMA  SYSTEM_UDTATTRIBUTES
INFORMATION_SCHEMA  SYSTEM_UDTS
INFORMATION_SCHEMA  SYSTEM_USAGE_PRIVILEGES
INFORMATION_SCHEMA  SYSTEM_USERS
INFORMATION_SCHEMA  SYSTEM_VERSIONCOLUMNS
INFORMATION_SCHEMA  SYSTEM_VIEW_COLUMN_USAGE
INFORMATION_SCHEMA  SYSTEM_VIEW_ROUTINE_USAGE
INFORMATION_SCHEMA  SYSTEM_VIEW_TABLE_USAGE
INFORMATION_SCHEMA  SYSTEM_VIEWS


For example, the content of HSQL 1.8.0 INFORMATION_SCHEMA.SYSTEM_COLUMNS is:

Code: Select all   Expand viewCollapse view

NAME               DATATYPE  WIDTH  NO-NULLS   PRECISION  SCALE
-----------------  --------  -----  --------  ----------  -----
TABLE_CAT          VARCHAR   32766            2147483647
TABLE_SCHEM        VARCHAR   32766            2147483647
TABLE_NAME         VARCHAR   32766  *         2147483647
COLUMN_NAME        VARCHAR   32766  *         2147483647
DATA_TYPE          SMALLINT      6  *                  5
TYPE_NAME          VARCHAR      32  *         2147483647
COLUMN_SIZE        INTEGER      11                    10
BUFFER_LENGTH      INTEGER      11                    10
DECIMAL_DIGITS     INTEGER      11                    10
NUM_PREC_RADIX     INTEGER      11                    10
NULLABLE           INTEGER      11  *                 10
REMARKS            VARCHAR   32766            2147483647
COLUMN_DEF         VARCHAR   32766            2147483647
SQL_DATA_TYPE      INTEGER      11                    10
SQL_DATETIME_SUB   INTEGER      11                    10
CHAR_OCTET_LENGTH  INTEGER      11                    10
ORDINAL_POSITION   INTEGER      11  *                 10
IS_NULLABLE        VARCHAR       3  *         2147483647
SCOPE_CATLOG       VARCHAR   32766            2147483647
SCOPE_SCHEMA       VARCHAR   32766            2147483647
SCOPE_TABLE        VARCHAR   32766            2147483647
SOURCE_DATA_TYPE   VARCHAR   32766            2147483647
TYPE_SUB           INTEGER      11  *                 10


and the content of HSQL 1.8.0 INFORMATION_SCHEMA.SYSTEM_TABLES is:

Code: Select all   Expand viewCollapse view

NAME                       DATATYPE  WIDTH  NO-NULLS   PRECISION  SCALE
-------------------------  --------  -----  --------  ----------  -----
TABLE_CAT                  VARCHAR   32766            2147483647
TABLE_SCHEM                VARCHAR   32766            2147483647
TABLE_NAME                 VARCHAR   32766  *         2147483647
TABLE_TYPE                 VARCHAR      16  *         2147483647
REMARKS                    VARCHAR   32766            2147483647
TYPE_CAT                   VARCHAR   32766            2147483647
TYPE_SCHEM                 VARCHAR   32766            2147483647
TYPE_NAME                  VARCHAR   32766            2147483647
SELF_REFERENCING_COL_NAME  VARCHAR   32766            2147483647
REF_GENERATION             VARCHAR   32766            2147483647
HSQLDB_TYPE                VARCHAR       6            2147483647
READ_ONLY                  BOOLEAN       5  *                  1


Therefore, using the following Query ( HSQL 1.8.0 and run in DIRECT MODE ), will return a list of your table / view names, in the assigned sort order:

Code: Select all   Expand viewCollapse view

Select TABLE_TYPE, TABLE_NAME From INFORMATION_SCHEMA.SYSTEM_TABLES Where TABLE_SCHEM = 'PUBLIC' ORDER BY TABLE_TYPE, UPPER(TABLE_NAME)

and, the following Query( HSQL 1.8.0 and run in DIRECT MODE ), will return a list of table only names, and, some column information:

Code: Select all   Expand viewCollapse view

Select T.TABLE_TYPE, T.TABLE_NAME, C.ORDINAL_POSITION, C.COLUMN_NAME, C.TYPE_NAME, C.COLUMN_SIZE From INFORMATION_SCHEMA.SYSTEM_TABLES AS T, INFORMATION_SCHEMA.SYSTEM_COLUMNS as C Where T.TABLE_SCHEM = 'PUBLIC' and T.TABLE_NAME = C.TABLE_NAME and T.TABLE_TYPE = 'TABLE' ORDER BY T.TABLE_TYPE, UPPER(T.TABLE_NAME), C.ORDINAL_POSITION

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[ptoye]

Thanks both for your comments. Now I know that I've not got the latest version of HSQLDB I'll try using the old table names.