OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Help ! Base Table [Date] to default to Now()?

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
jhoodsoft.org
Newbie
Newbie


Joined: 04 Oct 2005
Posts: 2

PostPosted: Tue Oct 04, 2005 2:39 pm    Post subject: Help ! Base Table [Date] to default to Now()? Reply with quote

The title says it all. I'm trying to add a default value to a Date field, something approaching the MS Access "=Now() " which drops in the current system date and time.

Have also tried Date, Time and Timestamp as field types.

Have tried, Systime() Today() Date() Time() all to no effect.

=Now() works in Calc for this, but not in Base.
Is this correct, or does anyone know a way to do this?


Build 1.9.104
Back to top
View user's profile Send private message Visit poster's website Yahoo Messenger
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Tue Oct 04, 2005 3:11 pm    Post subject: Reply with quote

If this is a OOBase native format databse then you can do this.

First- what this is, is a bug, in the table design editor. When you try to enter a default value for a time, date or timestamp column it throws it away.

So the only choice you have is to enter the CREATE TABLE command directly in the SQL window.

Say i want a tbale with ID, Somedata and EntryDateTime then my create statement would be

Code:

CREATE CACHED TABLE "Table1"("id" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"SOMETHING" VARCHAR(50),"ENTRYDATETIME" TIMESTAMP DEFAULT NOW NOT NULL)




That will do it.

For a date only column the my default woud be CURDATE
For a time only column default coud be CURTIME

I tested this all on 2.0.0 RC1. Once the table has been created directly with the DDL commands then it works exactly as expected.

Will check if there is a issue entered and if not will enter one, but wouldn't hold out for a fix prior to final release.

HTH

Drew Jensen
_________________
Blog - http://baseanswers.spaces.live.com/
Back to top
View user's profile Send private message Send e-mail Visit poster's website
jhoodsoft.org
Newbie
Newbie


Joined: 04 Oct 2005
Posts: 2

PostPosted: Tue Oct 04, 2005 6:50 pm    Post subject: That worked Reply with quote

Drew -
Many thanks, worked like a champ.

Note to anyone wanting to try this. Create as Drew specified using SQL from Tools. Either, View Refresh Tables, or Save and exit Base and relaod. The table WILL appear. and you can edit it. A little bothersome that you can't see the default value though.
Back to top
View user's profile Send private message Visit poster's website Yahoo Messenger
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Wed Oct 05, 2005 4:59 am    Post subject: Column Default Values in OOBase Reply with quote

Just a point of clarification.

I was wrong when i called the behavior a bug in the GUI table design window. This is 'As designed'.

The reasoning is as follows. (assuming that I don't mangle this in passing it along)

OOBase supports many backend database engines, and each of these handle default values on columns slightly differenty.

OOBase allows you to override the default values for columns on tables.

When you set the default property in the OOBase table design window, you are ONLY setting the default value for OOBase UI controls, NOT for the database engine.

OOBase currently supports only literals as default values.

The HSQLDB engine that OOBase uses by default supports both literals values and calls to SQL functions. To use the later, DDL commands must be issued directly to the engine.
_________________
Blog - http://baseanswers.spaces.live.com/
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group