Similarly to “How may I set up the auto-value countering for a primary key in Base?”, I want the primary key to be automatically filled. However, I want it to utilize the UNIX Epoch to reduce the potential for numerical conflict across alternatively unsynchronised databases. Is this possible? If so, how?
Hello,
.
This may be a multi part process. You need to provide the type of database you are using as this process may be different from one to another.
.
Also the is no built in method in Base for this insertion to be automated. It may require a procedure . Again dependent upon database used.
.
Did a simple test using HSQLDB v2.3.2 base upon ths post → HyperSQL Database Engine (HSQLDB) / Discussion / Help: TIMESTAMP and AT LOCAL behavior
.
Seemed to work OK:
1 Like
Wouldn’t a trigger do this for you?
This was mentioned (as a procedure) but the issue is more of what database is used. Database used may not handle Unix Epoch directly and until that is known there is no way to determine direction.
@iplaw67, I do not know. Are you able to provide anything that explains what a “procedure” is? I expect that you have used the term to mean something that is specific to database management or LibreOffice.
@BEEDELLROKEJULIANLOCKHART92300
.
Before anything can be determined, need to know what database you are using with Base.
.
This information is needed to provide an answer.
1 Like
@BEEDELLROKEJULIANLOCKHART92300
.
What you note is for embedded. Firebird would be listed there if Experimental Features were turned on.
.
Also on the screen shown, note Connect to an existing database. This selection allows for many other databases to be chosen from (externally obtained - not built into LO).
.
Using Embedded HSQLDB (it is v1.8.x) creates a problem for your request. What was shown in my first comment is from a newer HSQLDB version.
.
Do not know if this is possible with the database you use.
1 Like
I am willing to enable Experimental Features to utilize Firebird because I create decently regular backups and my database does not contain much to convert, if necessary. Is this possible if I utilize Firebird?
I will look at that but a much better approach is to actually find a database to do all that you may want and not just this one specific item. Choosing on just this one small piece may cause large headaches later.
1 Like
That is sensible, but currently my requirements that I merely want to be able to reorder fields and the basic programmatic capability that I have describes to you. My knowledge of databases is not enough that I believe that I am able to predict what I shall desire.
.
I am very thankful for all of your assistance.
Sorry, but to me reordering fields is a waste of time. And your other request of a Unix Epoch date key field presents you with now one field in the table which makes reordering fields even more senseless…
.
So will soon look at Firebird (just recently walked in from the store) but your last comment is quizzical, Surely you plan on more than a key field.
Hello,
My testing with Firebird embedded work for creating Unix Epoch key field automatically.
The SQL was obtained from this post → I want to get unix time on Firebird
You need to pay attention to the SQL as Firebird 3.x (used in LO) needs your timezone offset. In the Trigger posted below I used an offset of -7 hours. you need to modify for your offset.
With that, the main part of the SQL can be used in a trigger when adding new records:
CREATE TRIGGER my_unix_key FOR "YOUR_TABLE_NAME"
BEFORE INSERT
POSITION 0
AS
BEGIN
new."YOUR_ID_FIELD" = DATEDIFF(second, timestamp '1970-01-01 -07:00:00', current_timestamp);
END
This is entered once (after your table is created) using the menu item Tools->SQL. Run SQL command directly must be selected.
To delete the Trigger use in the same place as creating:
DROP TRIGGER my_unix_key
my_unix_key is what I designated int the creation above.
There is a lot more to SQL, Triggers and Procedures. For further information refer to the documentation here → Firebird Documentation Links. It is the Firebird 3.0 Language Reference.
Yes there is a lot to read. All database related information is a lot to read for those new. Reading continues until…
2 Likes
I add another note here. When entering records (key field entry not needed), the key field generated is not seen until a refresh is done.
I vaguely (and maybe incorrectly) remember a Bug report on this but can’t seem to find one currently.
1 Like