felix wrote:Steps A-H presented no problems and I can see my tables and they all appear OK. However...
...I couldn't edit the column properties of the table...
What am I doing wrong.
The Base Graphical User Interface (GUI) works well when managing Field Properties within the context of an "Embedded database" configuration. However, this configuration is prone to data loss due to zip (.odb) file corruption upon OOo crash. The immediate solution configures Base for external database access through the built-in HyperSQL database engine (tutorial). So fortunately, Base supports alternate database configurations through compatible drivers (JDBC, ODBC, etc.). Unfortunately, many of these drivers do not integrate with the 'Field Properties' aspect of the Base GUI very well. Or at least not as well as you might expect.
In reality, the Field Properties only appear frozen. You can effectively access most Field Properties by other means through the GUI. For instance, you can Cut, Copy, Paste, Delete, Insert Rows [database columns] which then grants access to the column's Field Properties. You can also apply a Primary Key(s). Or better yet, you can Right-click > Copy > then Paste the entire table in order to access most exisiting Field Properties (perhaps repeating this twice to re-use the original table name; then issue CHECKPOINT DEFRAG to recover the minimum database size using Tools > SQL...). This last option is outlined step-by-step with graphics at in this tutorial link.
But we still need a workaround for: AutoValue and Default Value. These properties (and others) require SQL as outlined below. The last remaining field property, Format Example, is apparently hard-frozen because there's no known workaround.
AutoValue
If you try to set the AutoValue field, it will always flip to "No" -- but this can be set to 'Yes' using SQL as outlined below (keeping in mind I'm not the resident SQL expert so backup your database folder first):With an existing table:
use Tools > SQL... to add the AutoValue function to your Primary Key column as shown below...
When creating a new table: (with the Base wizard)
replacing the orange-colored Table and Column names with your own:
ALTER TABLE "TableName" ALTER COLUMN "ColumnName" INTEGER IDENTITY;
This will update the table but you'll need to click: View > Refresh Tables to see the change in the Base GUI.
Note: You'll get an error if the specified column doesn't exist OR is not already a Primary Key column.To add a new column as the Primary Key with AutoValue:
Note: INTEGER IDENTITY provides up to a couple billion (2^31) records per table. This generous limit equates to generating 100,000 records, per table, per day, for 60 years. But these values are generated in sequence without re-use so if you commonly generate millions of records per week then consider BIGINT IDENTITY which allows nine-quintillion (2^63) unique values -- which equates to generating 1 billion rows per second for 300 years.
ALTER TABLE "TableName" ADD COLUMN "ColumnName" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1)
or
To add the Primary Key constraint to an existing column with unique values:
ALTER TABLE "TableName" ADD PRIMARY KEY ("ColumnName")![]()
Note: GENERATED {BY DEFAULT | ALWAYS} AS IDENTITY syntax is supported by HSQLDB 2.x. The two options {BY DEFAULT | ALWAYS} are very similar when applied to a Primary Key or other Unique Constraint column. Prior to HSQLDB 2.2 the IDENTITY sequence generator could only be applied to the Primary Key column.Select an ID field and AutoValue = Yes,
then type IDENTITY in the resulting 'Auto-increment statement' box.
Then de-select Create a primary key on the Next page of the Table Wizard.
Default Value
A Column's Default Value can also be setup using SQL as outlined here. Or, if you have the know-how, you can use a text editor to make the changes directly in your .script file.
Simply Copy the table to a newly named table; adjust column properties as desired using the popup copy-table Wizard; after confirming the results simply delete the original table
FrankOz1959 posted:
the database URL: hsqldb:file:/users/public/databases/mydb/mydb;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false
hsqldb:file:////S:/hsqldbdata/db2/Kassenbuch;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false
hsqldb:file:////mnt/praxis/SERVER/Daten/hsqldbdata/db2/Kassenbuch;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false
hsqldb:hsql://192.168.15.1/chargen;default_schema=true
felix wrote:Am I to conclude that it is only possible to edit column properties if the modifications are made and then saved as a new column name. If this is the case this is not a viable solution as all macros accessing that table would then have to be modified to reflect the new column name.
...it's a good practice to issue the CHECKPOINT DEFRAG command using 'Tools>SQL...' after adjusting tables in this manner. And don't forget to use View > Refresh Tables to see your changes when working directly with SQL to alter table structures.
Frankoz1959 wrote:Is there possibly a step that I've missed?
FrankOz
Sub Switch2External
REM adjust the value of cURL:
Const cURL = "jdbc:hsqldb:file:/PATH/DB_NAME;default_schema=true;shutdown=true"
oDS = ThisDatabaseDocument.DataSource
oDS.URL = cURL
oDS.User = "SA"
oDS.Settings.JavaDriverClass = "org.hsqldb.jdbcDriver"
ThisDatabaseDocument.store()
End Sub
Edit: Fixed a copy&paste error: jdbcDriver |
Frankoz1959 wrote:...but now find that if i try to change the filenames in the mydb directory, <that ?> they lose their file extensions
Users browsing this forum: No registered users and 7 guests