[Solved] Automatic timestamp assignment how?

[NiksaVel]

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...

[probe1]

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

[DrewJensen]

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

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 http://user.services.openoffice.org/en/ ... 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.

[NiksaVel]

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

[wurzel]

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

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

[DrewJensen]

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 2200 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 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

Sub onUpdateRecord( oEvent 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.updateTimeStamp( oDataForm.FindColumn( "LastEdit" ), oResultSet.getTimestamp( 1 ) )
        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

Form Navigator.png (5.29 KiB) Viewed 59554 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

[NiksaVel]

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

really appreciate it..

[Ubehage]

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.

[NiksaVel]

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

[r4zoli]

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?

[DrewJensen]

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.

[r4zoli]

I tried on example file: current time_stamp.odt.

[DrewJensen]

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.

[DrewJensen]

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.

[r4zoli]

Just working on 2.3.0, too.

[joris]

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.

[davewise]

I'm curious about how to implement the java trigger to update the timestamp field.

[TheGurkha]

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.

[DACM]

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.

[eyesopen]

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!!

[r4zoli]

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.

[eyesopen]

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

[r4zoli]

It works in HSQLDB 2.2, but OOo Base use HSQLDB 1.8.0.10, as built in database server.

[DACM]

Thank you Chris for the SQL Procedure.

Here's some considerations: http://user.services.openoffice.org/en/ ... 27#p195427
.

[myminddangerous]

OOo forum.7z

(44.41 KiB) Downloaded 319 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 319 times

[s1w_]

1) for reference activation:

in A1 formula put:

Code: Select all

=PUTTIMESTAMP("B1")

or even

Code: Select all

=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

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

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