[Solved] Automatic timestamp assignment how?

Discuss the database features

[Solved] Automatic timestamp assignment how?

Postby NiksaVel » Fri Jan 25, 2008 3:17 pm

Hey guys,

I would like to have a timestamp value in my table and I would like it to be automatically assigned value at the time of record creation and/or edit... it shouldn't even appear in the form... kinda like the primary ID key...

How would I go about acomplishing said task? Cuz I have no idea... :mrgreen:
Last edited by NiksaVel on Sat May 10, 2008 10:56 pm, edited 1 time in total.
OOo 3.1.X on Ubuntu 8.x + Linux Mint 7, Win XP, Vista
User avatar
NiksaVel
 
Posts: 91
Joined: Fri Jan 25, 2008 12:00 pm
Location: Croatia

Re: Automatic timestamp assignment how?

Postby probe1 » Fri Jan 25, 2008 3:19 pm

try the SEARCH function - there has been posts on this topic.
Cheers
Winfried

DateTime2 extension: insert date, time or timestamp, formatted to your needs
User avatar
probe1
Volunteer
 
Posts: 277
Joined: Mon Oct 08, 2007 1:34 am
Location: Chonburi Thailand

Re: Automatic timestamp assignment how?

Postby DrewJensen » Fri Jan 25, 2008 4:34 pm

Well to be fair only half of the question has been answered before.

NiksaVel wrote:Hey guys,

I would like to have a timestamp value in my table and I would like it to be automatically assigned value at the time of record creation and/or edit... it shouldn't even appear in the form... kinda like the primary ID key...



Looking at this thread viewtopic.php?f=13&t=573 you will see how to assign a default value to a timestamp field and that will take care of the "assigned value at the time of record creation" half of the question.

However that does not help with the second half of the question, " and/or edit".

There are really only two ways to go with that. The first and more common way is with a trigger. I say that is more common for database design overall, but with the Base file using HSQLdb this means you must write the trigger with Java.

The second way then, and more common way with Base, is to create a script that updates the field and call that script from any form that updates the record. This allows you to use basic to write the script - but there is a trade off. Using this script it only works when updating the record from a form and only when you remember to link it into the form. If you just open the table in a dataview window ( double click the table in the table view of the base file in other words, or run a query that displays the table and update it the query designer ) there is no way to call the script.

In other words using java to create a trigger you have the advantage that no matter how the record is updated, a dataview window, a query window, a form or even with a SQL Update statement in the SQL window the timestamp is guaranteed to be set to the current date time.

The question then is - Java or Basic? Let me know and I'll cover either one.
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
User avatar
DrewJensen
Volunteer
 
Posts: 1734
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: Automatic timestamp assignment how?

Postby NiksaVel » Fri Jan 25, 2008 8:06 pm

Thanks a lot for the informative reply! This is my first attempt at working with a database in general so I REALLY REALLY appreciate it :)


As for the second part, I never really worked with Java, and editing ONLY with forms is the way I plan to go, so if you would be so kind as to explain the Basic version that would be nice enough for me :D
OOo 3.1.X on Ubuntu 8.x + Linux Mint 7, Win XP, Vista
User avatar
NiksaVel
 
Posts: 91
Joined: Fri Jan 25, 2008 12:00 pm
Location: Croatia

Re: Automatic timestamp assignment how?

Postby wurzel » Sat Jan 26, 2008 11:32 pm

NiksaVel wrote:Thanks a lot for the informative reply! This is my first attempt at working with a database in general so I REALLY REALLY appreciate it :)


As for the second part, I never really worked with Java, and editing ONLY with forms is the way I plan to go, so if you would be so kind as to explain the Basic version that would be nice enough for me :D



With a mysql database, you can use a field definition like the following in your table creation statement that will automatically insert a timestamp value on creation of a new record, and then automatically update the timestamp to current date/time each time the record is modified :


`chg_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP

Unfortunately, HSQLDB is rather lacking in easily accessible (i.e. common or garden user accessible) date/time functions. As Drew has said, you need to be able to program in Java or OOoBasic to get this functionality which adds a level of complexity.


Alex
wurzel
Volunteer
 
Posts: 145
Joined: Sun Dec 09, 2007 10:39 am

Re: Automatic timestamp assignment how?

Postby DrewJensen » Mon Jan 28, 2008 7:27 am

Alright so the basic script for this.

You have a few choices here. Choice one include your datetime field in the query that you build your form on, either displayed or not, of course if your form is based on the actual table this is what you have.
Choice two do not include the datetime field in the query.

I never quite know where to start on this.

I suppose just jump in with an example:

Attached to this post you will find a database file named "current_timestamp.odb".
It has one table, Table1
ID IDENTITY Primary Key, VAL_1 VARCHAR(50), LastEdit Timestamp
The command ALTER TABLE "Table1" ALTER COLUMN "LastEdit" SET DEFAULT CURRENT_TIMESTAMP was applied from the SQL window.It has one form, Table1 - based on the table Table1.
The form displays a grid with 3 columns - Val_1, LastEdit(Date), LastEdit(Time)
The ID field is hidden by default, LastEdit(Date), LastEdit(Time) are read only.

At this point you can open the form add a record by entering some data in Val_1 and you will see that when the record is written to the table the LastEdit fields are automatically filled with the current date and time.
Current_timestamp.odb
Example database
(9 KiB) Downloaded 1894 times


OK - you should have grabbed a copy of that database.

Now at this point if you edit an existing record the LastEdit value does not change, so that is what you will add now.

The first thing to do is to create our script. Open the database on your desktop. From the main window select 'Tools > Macros > Organize macros > OpenOffice.org Basic'

You will want to put your script ( macro ) in the standard library under 'Your Macros'

Basic Macros.png
Basic macro dialog


I want you to select "Module1"under "Standard" and click on 'Edit'.

In the Basic IDE window that opens you copy the following script ( macro )

Code: Select all   Expand viewCollapse view
Sub onUpdateRecordoEvent as object )
    
dim oDataForm as variant
    dim oStatement 
as variant
    dim oResultSet 
as variant

    
if oEvent.Source.SupportsService"com.sun.star.form.component.DataForm" then
        
if oEvent.Action com.sun.star.sdbc.ChangeAction.UPDATE then
            oDataForm 
oEvent.Source
            oStatement 
oDataForm.ActiveConnection.CreateStatement
            oStatement
.EscapeProcessing False
            oResultSet 
oStatement.executequery"CALL CURRENT_TIMESTAMP" )
            
oResultSet.next
            oDataForm
.updateTimeStampoDataForm.FindColumn"LastEdit" ), oResultSet.getTimestamp) )
        
end if
    
end if

End Sub


 Edit: Corrected typo and added explicit call to turn escape processing off 


You can close the Basic IDE.

Now from the Base window open the form 'Table1" for editing. Right click on the form, select edit.
With the form open for edit ( design mode on ) you can assign the macro.
To do this you want to open the form navigator - this is on the toolbar 'Form Design'.
Form Navigator.png
Form Navigator
Form Navigator.png (5.29 KiB) Viewed 49679 times


Notice that I have highlighted the entry "MainForm". This is the hidden dataform that is the link between the data table ( or query ) and the form controls. It is where we want to assign the macro. So right mouse click on this and select 'Properties'.

On the property editor select the Events tab.
You will find an event 'Before Record Actrion', click on the button just to right of this field.

Now browse to 'My Macros', Standard, Module1 and select the macro onUpdateRecord.
Close the forum and save the database.
Your done.

Try it out, you should be able to add a record in the form, the timestamp is set - edit a record and it changes to the current date time also.

What does the code mean - line for line...well, first you could check out the Basic Programmers Guide. You can find a reference to that at the top of the Macros forum on this site.
or
If you are going to need to do this on more then one form, maybe I could show you how to make that macro a bit more generic and dissect the lines as we go.

Drew
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
User avatar
DrewJensen
Volunteer
 
Posts: 1734
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: Automatic timestamp assignment how?

Postby NiksaVel » Mon Jan 28, 2008 9:31 am

thanks a lot for the input! I'll play around with this when I get home from work :)

really appreciate it..
OOo 3.1.X on Ubuntu 8.x + Linux Mint 7, Win XP, Vista
User avatar
NiksaVel
 
Posts: 91
Joined: Fri Jan 25, 2008 12:00 pm
Location: Croatia

Re: Automatic timestamp assignment how?

Postby Ubehage » Mon Jan 28, 2008 11:29 am

Very nice!!
I've just started using OpenOffice 2.3.1 20 minutes ago, and this page explained exactly what I needed; the reason I switched away from M$ Access.

However, I have one more question in this matter:

How can I make Base "skip" those date- and time-fields when the user is entering data ?
I need to create an automated database. Which I find is quite easy, except for the matter of skipping those 2 fields.
The user don't need them; but they need to exist. And the user should not be bothered by having to press Enter 2 times (or down-arrow).

I need to create a table with 3 or 4 fields, plus those date/time-fields.
I want Base to automatically jump to the next record, when those 3 or 4 fields has been entered - the user should never have to bother about the date and time; not even have to skip them. This should be fully automatic.

*NOTE*: I am not experienced with SQL. I prefer to script whatever can be scripted.
Last edited by Ubehage on Mon Jan 28, 2008 11:58 am, edited 1 time in total.
Ubehage
 
Posts: 1
Joined: Mon Jan 28, 2008 11:15 am

Re: Automatic timestamp assignment how?

Postby NiksaVel » Mon Jan 28, 2008 11:40 am

I didn't even create the form entry for timestamp - after using the sql command provided there the database does that automatically as soon as the record has been entered - the user never sees anything time related, until the query/report gets generated. Very nice :)
OOo 3.1.X on Ubuntu 8.x + Linux Mint 7, Win XP, Vista
User avatar
NiksaVel
 
Posts: 91
Joined: Fri Jan 25, 2008 12:00 pm
Location: Croatia

Re: Automatic timestamp assignment how?

Postby r4zoli » Mon Jan 28, 2008 4:26 pm

Thismacro fails on my XP under 2.3.0 and OOH m4 with same error: Basic runtime error, Object variable not set, and stops on oResultSet.
I did something wrong?
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
User avatar
r4zoli
Volunteer
 
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

Re: Automatic timestamp assignment how?

Postby DrewJensen » Mon Jan 28, 2008 4:30 pm

Are you using a Base file with an embedded HSQLdb database?

The macro as it stands is using a fairly standard SQL statement and CURRENT_TIMESTAMP is part of the SQL standard, but not all databases follow the standard.

You can use a OOBasic funciton such as now() instead, but then you need to convert the basic time variable type to the Base timestamp struct type. That would work across all database types however. My preference is to ask the database engine for the timestamp but it is preference only.
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
User avatar
DrewJensen
Volunteer
 
Posts: 1734
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: Automatic timestamp assignment how?

Postby r4zoli » Mon Jan 28, 2008 4:33 pm

I tried on example file: current time_stamp.odt.
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
User avatar
r4zoli
Volunteer
 
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

Re: Automatic timestamp assignment how?

Postby DrewJensen » Mon Jan 28, 2008 4:35 pm

well - just tired it with 680M4 also - nope it fails.

It works under 2.3.1.

Anyway I know why, need to add a line to the macro to force the Base query parser off.

Give me a second and I'll update the macro in the original post.
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
User avatar
DrewJensen
Volunteer
 
Posts: 1734
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: Automatic timestamp assignment how?

Postby DrewJensen » Mon Jan 28, 2008 4:48 pm

OK - fixed the actual macro - there was a typo and I added the line to set escape processing FALSE.

Tested it under 680M4, XP no problem any longer.
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
User avatar
DrewJensen
Volunteer
 
Posts: 1734
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: Automatic timestamp assignment how?

Postby r4zoli » Mon Jan 28, 2008 5:12 pm

Just working :D on 2.3.0, too.
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
User avatar
r4zoli
Volunteer
 
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

Re: [Solved] Automatic timestamp assignment how?

Postby joris » Wed Sep 10, 2008 12:17 am

Ok, i am a newbie, so it is very well possible that i am totally wrong here, but i just tried DrewJensen example and it seems that it doesn't need a macro to work with the timestamp. Maybe applying the sql statement SET DEFAULT CURRENT_TIMESTAMP was enough to do the trick. Is this true or am i overlooking something? It worked for me without a macro...

I was looking into this, because i wanted to see how a macro would work. I am working on a base front end for a mysql database. The database is used on different places, with different front ends. Mostly PHPmyadmin. That's the reason why the guy who asked me said he would be very happy if i didn't change anything on the mysql database to get things working on openoffice base. On the mysql table there is a timestamp column with the attribute 'On update current timestamp', no null value allowed and default is current timestamp. When i try to add a new record in base i get troubles because mysql doesn't allow null values in the timestamp column. Changing the the column to allow null values solves the problem, (mysql adds the systemdate fine) but as stated above i would like to avoid changing values in mysql. So i hoped it might be possible to avoid this, when i can make base enter the system time/date, when a record is saved. That's where i thought a macro could be usefull. Any ideas on this?

BTW I am not a coder, but the code and explanations on this forum are often so good and easy to understand that i have been very successful in editing other macros and adjusting them to my own needs. Big thanks to DrewJensen and others for all their useful posts. :)
Libre Office on Ubuntu 12.10
User avatar
joris
 
Posts: 19
Joined: Mon Aug 11, 2008 10:15 am

Re: [Solved] Automatic timestamp assignment how?

Postby davewise » Sun Nov 23, 2008 6:22 pm

I'm curious about how to implement the java trigger to update the timestamp field.
OOo 3.0.X on Mac OSx Leopard + Windows XP, Ubuntu
davewise
 
Posts: 23
Joined: Sat Nov 15, 2008 2:17 am

Re: [Solved] Automatic timestamp assignment how?

Postby TheGurkha » Sun Nov 23, 2008 6:59 pm

You will get more response if you start a new thread. This thread has been marked as 'Solved' so a lot of people might not even open it to read new posts.
Ubuntu 14.10 Utopic Unicorn, LibreOffice Version: 4.3.3.2
Gurkha Welfare Trust
User avatar
TheGurkha
Volunteer
 
Posts: 6482
Joined: Thu Mar 13, 2008 12:13 pm
Location: North Wales, UK.

Re: [Solved] Automatic timestamp assignment how?

Postby DACM » Thu Apr 14, 2011 9:51 pm

Just to update this excellent example by DrewJensen (above) using the latest, macro-storage location added with OOo 3.1

Note: Moved the original code to the database document for inclusion in the .odb file.
Note: Just for variation, this implementation is actually using a second macro which takes the Timestamp from the local system 'Now()'. This requires a conversion factor (at least on my Win7 64-bit system). I used 'Now-2', which is the current timestamp (as a Double data-type) minus 2 (days) -- for whatever reason -- which results in the current day.
Attachments
Current_timestamp2.odb
uses a better Basic to SQL Timestamp conversion than the original below
(14.91 KiB) Downloaded 579 times
Current_timestamp (updates with macro).odb
(14.72 KiB) Downloaded 723 times
Last edited by DACM on Wed Nov 16, 2011 7:13 pm, edited 1 time in total.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
DACM
Volunteer
 
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: [Solved] Automatic timestamp assignment how?

Postby eyesopen » Fri Oct 21, 2011 6:59 pm

Here is a much easier method. I've tested this with HSQLDB 2.2.5. Not sure if it runs with the version of HSQLDB that comes with Base [edit: it looks like it doesn't].

(1) To update a timestamp column automatically whenever a row is updated, all you need to do is execute the following SQL statement:

CREATE TRIGGER MyTableUpdateDateTrigger BEFORE UPDATE ON "MyTable"
REFERENCING NEW ROW AS newrow OLD ROW AS oldrow
FOR EACH ROW
BEGIN ATOMIC
SET newrow."MyDateTimeColumn" = CURRENT_TIMESTAMP;
END

Just replace all the names starting with "My" with your own names. This creates a trigger that will run just before any update to your table is done and set the timestamp appropriately.

The above SQL is written in a general way so it's easy to edit: You can access the old version of the row through "oldrow" and the new version through "newrow", and you can add extra statements between BEGIN ATOMIC and END to do other stuff if desired. You can also add a WHEN clause to update only when certain conditions are met. See the Triggers section of the HyperSQL User Guide.


Some other notes:

(2) If you make a mistake and want to get rid of the trigger, run:

DROP TRIGGER MyTableUpdateDateTrigger


(3) As others have posted: to set only the default value of a timestamp column, run:

ALTER TABLE "MyTable" ALTER COLUMN "MyDateColumn" SET DEFAULT CURRENT_TIMESTAMP


If you want, you could create two columns, one for the date & time the record was created with default value as set in (3); and another with the date & time the record was last updated, updated by the trigger above in (1).


Hope this is useful!!
Last edited by eyesopen on Fri Oct 21, 2011 7:41 pm, edited 1 time in total.
LibreOffice 3.4.3, Mac OS X, Linux
HSQLDB 2.2.5
eyesopen
 
Posts: 4
Joined: Fri Oct 21, 2011 6:33 pm

Re: [Solved] Automatic timestamp assignment how?

Postby r4zoli » Fri Oct 21, 2011 7:07 pm

eyesopen wrote:Here is a much easier method. I've tested this with HSQLDB 2.2.5. Not sure if it runs with the version of HSQLDB that comes with Base.

No, OOo use HSQLDB 1.8.0.10.
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
User avatar
r4zoli
Volunteer
 
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

Re: [Solved] Automatic timestamp assignment how?

Postby eyesopen » Fri Oct 21, 2011 7:32 pm

r4zoli wrote:No, OOo use HSQLDB 1.8.0.10.


Not sure what you mean, but looking at the docs for HSQLDB 1.8 it doesn't look like you can do this. However, for 2.2 it works very well!

Chris
LibreOffice 3.4.3, Mac OS X, Linux
HSQLDB 2.2.5
eyesopen
 
Posts: 4
Joined: Fri Oct 21, 2011 6:33 pm

Re: [Solved] Automatic timestamp assignment how?

Postby r4zoli » Fri Oct 21, 2011 7:36 pm

It works in HSQLDB 2.2, but OOo Base use HSQLDB 1.8.0.10, as built in database server.
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
User avatar
r4zoli
Volunteer
 
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

Re: [Solved] Automatic timestamp assignment how?

Postby DACM » Sat Oct 22, 2011 5:50 pm

Thank you Chris for the SQL Procedure.

Here's some considerations: http://user.services.openoffice.org/en/forum/viewtopic.php?p=195427#p195427
.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
DACM
Volunteer
 
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: [Solved] Automatic timestamp assignment how?

Postby myminddangerous » Tue Jul 08, 2014 11:36 am

OOo forum.7z
(44.41 KiB) Downloaded 135 times
Hi I'm sure I'm going to here asked and answered and get links, I have been at this for 4 days now. I've taugh myself the basics of sql in a weekend. not bad for someone that works on car all day. I'm trying to build a database that will hold 5 years work of invoices from the previous owner of my shop so I can have someone start marketing to them. I have most of it written, but my only problem is when I go to set up the macros or triggers I can get the auto update on create and edit to work in the form when I have it in the list view, but when I go to use the block style in the form it takes a crap on me. I have written one table the has a unique constraint for the first and last name. now I'm trying to write the next set of lines to auto fill and update atleast the date everytime a record is changed or created. I would like the time stamp too, but I can live without. I know where to put in the macros but I don't know exactly where to put in the triggers. if anyone can help I'm going to attach what I have done so far. I made a zip file with 7zip with all the info im working with thank you
OOo forum.7z
(44.41 KiB) Downloaded 135 times
apache openoffice 4.1.0 and win 7 ultimate
myminddangerous
 
Posts: 1
Joined: Tue Jul 08, 2014 11:18 am

Re: [Solved] Automatic timestamp assignment how?

Postby s1w_ » Sun Nov 16, 2014 12:57 am

1) for reference activation:

in A1 formula put:
Code: Select all   Expand viewCollapse view
=PUTTIMESTAMP("B1")


or even
Code: Select all   Expand viewCollapse view
=IF(B1<>"";PUTTIMESTAMP();"")


then, if u modify B1 cell, A1 will get permanent timestamp

u must put also the following module into standard macro section (e.g. in document space):
Code: Select all   Expand viewCollapse view
Function putTimeStamp (Cell1 as Variant)
   putTimeStamp = Format(Now, "YY-MM-DD HH:MM")
End Function


2) for global macro triggering:

It would be better, more universal option, but there is no easy implementation of cell_onChange (as easy as in excel). It is longer, becouse it double checks if timestamp wasnt allready put

insert this into standard macro section:

Code: Select all   Expand viewCollapse view
Sub putTimeStamp
Dim Doc
Dim sourceCell
Dim destCell
Dim Cell
Dim Sheet

    Sheet = ThisComponent.CurrentController.ActiveSheet
    Cell = ThisComponent.CurrentController.Selection

    If Cell.getColumns().getCount() > 1 Or Cell.getRows().getCount() > 1 Then Exit Sub
    If Cell.getRangeAddress.StartColumn <> 1 Then Exit Sub  '1 is the column number which is being listened for changes (B)

    r =  Cell.getRangeAddress.StartRow
    destCell = Sheet.getCellByPosition( 0, r )  ' 0 is the column number where timestamp should be placed

    If destCell.Type <> com.sun.star.table.CellContentType.EMPTY Then Exit Sub

destCell.setValue(now())
End Sub


And bind it to button
LibreOffice 3.5.7.2 Ubuntu 12.04 lts
s1w_
 
Posts: 1
Joined: Sun Nov 16, 2014 12:04 am


Return to Base

Who is online

Users browsing this forum: No registered users and 4 guests