[Solved] HSQL Table Field Properties

[Axe-Man]

Yes, I AM a noob with BASE (v5.2.6.2 on Win 10) but have (some) understanding of SQL

I have created a relational DB with several Tables that have DECIMAL fields that I want formatted as FRACTIONS. Thus far, I have defined fields as DECIMAL(n,5) and intend to represent the data as a Fraction. I know I can EDIT the Tables through the GUI, choose each Field, click the '...' and from there, set the Field type as FRACTION, set the Format to -1234 10/81, and Format Code will become '# ??/??' (exactly as I want it).

As I want to be able to set up my Database using a script file and SQL, is there a way to accomplish this with field definition parameters?

Sample SQL to create a "Parts" Table:

CREATE TABLE "Part" (
"ID-Number" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,
"Description" VARCHAR(50),
"Length" DECIMAL(2,5)
);

Any light you can shed wil be appreciated.

[eremmel]

You can execute SQL directly via menu: Tools -> 'SQL...'. You can use this for Upd/Del/Ins and Create/Drop etc.
The Base form in which the table data is represented is not part of the database. So you cannot control things like representation as Fraction within the table SQL-definition.

SQL definition language is about storage formats of data. Representation of data is at application/UI layer defined.

[Axe-Man]

Ah-ha!! Thanks so much for putting me out of my misery - lol - been chasing this for a few days now to no avail.

Foolishly, I was assuming that if parameters were available during a Table EDIT, that I should (somehow) be able to specify the same parameters in SQL syntax - not just some of them. For example, in HSQL, I can provide these "Field Properties" - Entry required (Y/N), Length, Decimal places, Default value, but (not Format example?)

Format example is expandable in Table EDIT mode and from there, I can (one field at a time) set my desired parameters for Category, Format, Language, etc. If I am not mistaken, these settings are stored with the database, are they not? Or, do I still have a lot to learn?

Thank you for your patience with me.

[eremmel]

No problem to be patience when progress is made.

Base is actual a Database frontend. For convenience is supports an embedded database (this is not so stable combination so make regular copies of your work; different files).

So when I tell you "SQL definition language is about storage formats of data. Representation of data is at application/UI layer defined". The Base document contains all just by accident because it used the embedded database and it has form definitions etc that are stored in the other Base section of the document. (Try to unzip a base document and you will discover different section/folders/files).

So never judge based on what you observe but try to find out how it works. To make it a tittle fuzzy again, you can consider a XML file that describes all forms also as a database, but that is an other one (only for UI / representation) then the one that contains your tables and data.

A good principle is to segregate responsibilities and duties, you do it at home, but also it is key in software design and usage.

[UnklDonald418]

Expanding a little on the post by eremmel.
The Embedded Base file with the obd extension is really a zip file. That's great from a portability standpoint because everything is in one file. But it is also an Achilles Heel. If something happens before the zip operation is completed it can result in a loss of data. While it can happen at any time, it is a problem particularly for laptop users who have a habit of quickly closing the cover and shutting down the operating system without exiting Base or before the zip operation has completed.
That problem can be minimized by using a split database. If you are just creating a new database then check out this link.
viewtopic.php?f=83&t=61183
Another problem with the Embedded database is that you are stuck with an obsolete version of HSQL (1.8.0). A split database can use a more recent version with better funcionality.