[Solved] INFORMATION_SCHEMA in HSQLDB?

Discuss the database features
Post Reply
ptoye
Posts: 225
Joined: Mon Apr 21, 2008 11:07 am
Location: Reading, England

[Solved] INFORMATION_SCHEMA in HSQLDB?

Post by 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?
Last edited by ptoye on Sun Sep 22, 2013 5:19 pm, edited 1 time in total.
Peter
OO 4.1.1 on MS Windows 7 64-bit
F3K Total
Volunteer
Posts: 1038
Joined: Fri Dec 16, 2011 8:20 pm

Re: INFORMATION_SCHEMA in HSQLDB?

Post by F3K Total »

Hi,

Code: Select all

SELECT * FROM INFORMATION_SCHEMA.SYSTEM_TABLES
works
  • MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
  • my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
ptoye
Posts: 225
Joined: Mon Apr 21, 2008 11:07 am
Location: Reading, England

Re: INFORMATION_SCHEMA in HSQLDB?

Post by 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?
Peter
OO 4.1.1 on MS Windows 7 64-bit
F3K Total
Volunteer
Posts: 1038
Joined: Fri Dec 16, 2011 8:20 pm

Re: INFORMATION_SCHEMA in HSQLDB?

Post by F3K Total »

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

Code: Select all

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
  • MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
  • my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
ptoye
Posts: 225
Joined: Mon Apr 21, 2008 11:07 am
Location: Reading, England

Re: INFORMATION_SCHEMA in HSQLDB?

Post by 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).
Peter
OO 4.1.1 on MS Windows 7 64-bit
F3K Total
Volunteer
Posts: 1038
Joined: Fri Dec 16, 2011 8:20 pm

Re: INFORMATION_SCHEMA in HSQLDB?

Post by F3K Total »

ptoye,
you wrote:
ptoye 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
ptoye wrote: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
230.png (5.92 KiB) Viewed 14149 times
R
  • MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
  • my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
ptoye
Posts: 225
Joined: Mon Apr 21, 2008 11:07 am
Location: Reading, England

Re: INFORMATION_SCHEMA in HSQLDB?

Post by 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?
Last edited by ptoye on Sun Sep 22, 2013 5:48 pm, edited 1 time in total.
Peter
OO 4.1.1 on MS Windows 7 64-bit
User avatar
Greengiant224
Posts: 282
Joined: Wed Jun 09, 2010 3:50 pm
Location: All Over The World

Re: [Solved] INFORMATION_SCHEMA in HSQLDB?

Post by Greengiant224 »

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

Code: Select all

    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

Win 7, Portable AOO 4.1.14, LibreOffice 6.2, 7.4.5 & 7.6.1 (Java 1.7.81 & 1.8.0_281) utilising HSQL 1.8.10 & 2.4.*, MySQL, PostgreSQL, SQLite
+ Blood, Sweat and Tears (Application, Determination and Perseverance)
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: [Solved] INFORMATION_SCHEMA in HSQLDB?

Post by 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

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

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

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

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

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
Posts: 225
Joined: Mon Apr 21, 2008 11:07 am
Location: Reading, England

Re: [Solved] INFORMATION_SCHEMA in HSQLDB?

Post by 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.
Peter
OO 4.1.1 on MS Windows 7 64-bit
Post Reply