According to the HSQLDB user guide this view should be available. Doesn't this work in Base or am I dong something wrong?1: Table not found: SCHEMATA in statement [SELECT * FROM "INFORMATION_SCHEMA"."SCHEMATA"]
[Solved] INFORMATION_SCHEMA in HSQLDB?
[Solved] INFORMATION_SCHEMA in HSQLDB?
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 :
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
OO 4.1.1 on MS Windows 7 64-bit
Re: INFORMATION_SCHEMA in HSQLDB?
Hi, works
Code: Select all
SELECT * FROM INFORMATION_SCHEMA.SYSTEM_TABLES
- MMove 1.0.6
- Extension for easy, exact positioning of shapes, pictures, controls, frames ...
- my current system
- Windows 10 AOO, LOLinux Mint AOO, LO
Re: INFORMATION_SCHEMA in HSQLDB?
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:
And how does one find out which views are supported if the documentation is misleading?SCHEMATA
Information on all the SCHEMA objects in the database
Peter
OO 4.1.1 on MS Windows 7 64-bit
OO 4.1.1 on MS Windows 7 64-bit
Re: INFORMATION_SCHEMA in HSQLDB?
Hi,
if i look into the guide for HSQLDB 1.8 there is no
You'll find all available on Page 60 ff. of
R
if i look into the guide for HSQLDB 1.8 there is no
Code: Select all
SELECT * FROM INFORMATION_SCHEMA.TABLES
The guide is within the .zip-package hereHyperSQL 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.
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
Re: INFORMATION_SCHEMA in HSQLDB?
That's what I've been using - the HTML version on the HSQLDB site rather than the PDF.HyperSQL User Guide: HyperSQL Database Engine, aka HSQLDB
by The HSQL Development Group, Blaine Simpson, and Fred Toussi
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).if i look into the guide for HSQLDB 1.8 there is no
SELECT * FROM INFORMATION_SCHEMA.TABLES
Peter
OO 4.1.1 on MS Windows 7 64-bit
OO 4.1.1 on MS Windows 7 64-bit
Re: INFORMATION_SCHEMA in HSQLDB?
ptoye,
you wrote:
I also can't find INFORMATION_SCHEMA.SCHEMATA there.
You have to know, that the online
Here a picture of your desired Statement, executed within a HSQLDB 2.3.0, working well: R
you wrote:
I can't find that in the guide for HSQLDB 1.8 which is shipped with AOO/LO.ptoye wrote:As an experiment I tried the SQL SELECT * FROM INFORMATION_SCHEMA.TABLES but got the error message ...
I also can't find INFORMATION_SCHEMA.SCHEMATA there.
You have to know, that the online
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.ptoye wrote:HTML version on the HSQLDB
Here a picture of your desired Statement, executed within a HSQLDB 2.3.0, working well: 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
Re: INFORMATION_SCHEMA in HSQLDB?
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?
[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
OO 4.1.1 on MS Windows 7 64-bit
- Greengiant224
- Posts: 283
- Joined: Wed Jun 09, 2010 3:50 pm
- Location: All Over The World
Re: [Solved] INFORMATION_SCHEMA in HSQLDB?
ptoye posted:
if i look into the guide for HSQLDB 1.8 there is no
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
if i look into the guide for HSQLDB 1.8 there is no
Code: Select all
SELECT * FROM INFORMATION_SCHEMA.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)
Re: [Solved] INFORMATION_SCHEMA in HSQLDB?
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.
For example, the content of HSQL 1.8.0 INFORMATION_SCHEMA.SYSTEM_COLUMNS is:
and the content of HSQL 1.8.0 INFORMATION_SCHEMA.SYSTEM_TABLES is:
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:
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:
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.
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
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
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
Code: Select all
Select TABLE_TYPE, TABLE_NAME From INFORMATION_SCHEMA.SYSTEM_TABLES Where TABLE_SCHEM = 'PUBLIC' ORDER BY TABLE_TYPE, UPPER(TABLE_NAME)
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
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
Re: [Solved] INFORMATION_SCHEMA in HSQLDB?
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
OO 4.1.1 on MS Windows 7 64-bit