Ask Your Question
1

How to check the underlying SQL in Base

asked 2015-01-26 19:48:25 +0200

RuthMcT gravatar image

I'm using Libre Office 4.3.5.2 Base with the inbuilt HSQL. (on Windows XP)

I am setting up a database, creating the tables in design view. But some of the commands need to be input via Tools/SQL e.g. adding table constraints such as UNIQUE or CHECK. I am naming my constraints and keeping a list of them. But what I can't see how to do is to look at the underlying SQL so that I can check up on previously entered constraints. Is there a way of doing this?

many thanks Ruth

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted
0

answered 2015-01-26 21:18:06 +0200

Regina gravatar image

updated 2015-01-26 21:19:57 +0200

Insert > Query (SQL view) with SELECT * FROM "INFORMATION_SCHEMA"."SYSTEM_TABLES" to get available system information. In your case you need probably Insert > Query (SQL view) with SELECT * FROM "INFORMATION_SCHEMA"."SYSTEM_TABLE_CONSTRAINTS".

edit flag offensive delete link more

Comments

Thank you, that has helped. It gives me the name and type of the constraint. Is it possible to get precise details of the constraint e.g. the range to which I have restricted a set of values?

RuthMcT gravatar imageRuthMcT ( 2015-01-26 22:39:39 +0200 )edit

Have you already looked at the other system informations? Perhaps SELECT * FROM "INFORMATION_SCHEMA"."SYSTEM_CHECK_CONSTRAINTS" will work for you.

Regina gravatar imageRegina ( 2015-01-27 08:42:55 +0200 )edit

Sorry about the late reply. SELECT * FROM "INFORMATION_SCHEMA"."SYSTEM_CHECK_CONSTRAINTS" is perfect. It displays the check clause for each check cosntraint. Thank you so much,

RuthMcT gravatar imageRuthMcT ( 2015-02-08 18:33:11 +0200 )edit
0

answered 2015-01-26 21:34:59 +0200

frofa gravatar image

updated 2015-01-26 21:41:54 +0200

Also, if you want to do it using an updated version of HSQLDB (version 2.3.x) then try this code (either as a saved query or execute in the Tools/SQL window with 'show output of select statements' ticked)....

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS;

The above code is a bit different to that given by Regina.

edit flag offensive delete link more

Comments

Thank you for the reply. I'm not sure how to find out which version of HSQLDB I've got, but I haven't upgraded anything from what came with LibreOffice. I tried your code, but it didn't work with my version.

RuthMcT gravatar imageRuthMcT ( 2015-01-26 22:41:42 +0200 )edit

OK. Since you are using the default Base installation (which uses the outdated HSQLDB version 1.8 as the 'engine'), the above code is not relevant. If you ever do upgrade your installation - see here - then the above code will work. TIP: Make sure you backup your database regularly, because the default 'embedded' configuration is prone to corruption.

frofa gravatar imagefrofa ( 2015-01-27 22:24:22 +0200 )edit
0

answered 2017-01-06 04:14:13 +0200

EasyTrieve gravatar image

updated 2017-01-06 04:19:09 +0200

The other answers here didn't work for me.

Instead see this question and answer..

What I learned, was that the default embedded database for LO 5.2.3.3 is the old HSQLDB, version 1.8.0.

edit flag offensive delete link more

Comments

Find the version information in the file properties in folder database in the *.odb file. Remember, that the *.odb file is a simple zip-container and you can look into it using a packer like 7-Zip.

Regina gravatar imageRegina ( 2017-01-07 19:12:17 +0200 )edit

@Regina, this is much easier. Thanks! And now not to diminish your answer, but just to note that the other answer linked to above is still worth something because it provides the version for both embedded, and split at the same time which might be helpful at times. (I guess it depends on what "underlying" means in this question). In other words, your answer is short and quick, but only seems to provide the version for the embedded, but not split db. Still thanks as it very much helps.

EasyTrieve gravatar imageEasyTrieve ( 2017-01-08 20:53:24 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-01-26 19:48:25 +0200

Seen: 957 times

Last updated: Jan 06