How can SQLs applied to database for auto-date entry ?

General discussion on OpenOffice Base and its databases.

How can SQLs applied to database for auto-date entry ?

Postby JackSMT » Tue Jun 03, 2014 8:47 am

Hi,

I'm new to Open Office and have been trying to automatically add today's date to new database records. I found the technique from this forum of using the menu option: Tools/SQL and then entering the ALTER TABLE..... command and executing it, which works OK.
My problem is that I applied it to the wrong date field, but I cannot find a method of removing or amending this command. If I return to the Tools/SQL dialog box, the command that I entered does not appear. So how can I remove or amend the previously entered command?

Thanks,
JackSMT
 
Posts: 20
Joined: Tue Mar 25, 2014 6:29 am

Re: How can SQLs applied to database for auto-date entry ?

Postby Joseph S. Klein » Tue Jun 03, 2014 10:34 am

Hi,

Simply do the same again with the right values:
ALTER TABLE "TABLE NAME" ALTER COLUMN "WRONG COLUMN" SET DEFAULT NULL;
ALTER TABLE "TABLE NAME" ALTER COLUMN "RIGHT COLUMN" SET DEFAULT CURRENT_DATE;

thanks,
Joseph S. Klein
 
Posts: 38
Joined: Mon Mar 24, 2014 6:28 am

Re: How can SQLs applied to database for auto-date entry ?

Postby JackSMT » Tue Jun 03, 2014 10:36 am

hi,

Thanks for the suggestion (ALTER TABLE "TABLE NAME" ALTER COLUMN "WRONG COLUMN" SET DEFAULT NULL;). It solves the problem.
I'm just a little curious as to where these SQL commands are stored and what is really going on. Does this mean the there are now two commands being executed, one adding the date and then one setting it to NULL or has the original one been effectively removed?
Sorry if I'm being a pain, but I always like to know how thing work.

thanks
JackSMT
 
Posts: 20
Joined: Tue Mar 25, 2014 6:29 am

Re: How can SQLs applied to database for auto-date entry ?

Postby Joseph S. Klein » Tue Jun 03, 2014 10:39 am

ok,
If the status bar of your Base document indicates "embedded HSQLDB" then http://www.hsqldb.org/doc/1.8/guide/ch09.html is the software you are working with (through Base). Null (empty, nothing) is the default value unless you specified something else.
The SQL database definition is stored in a file database/script within the zip archive that constitutes your Base document.

Thanks
Joseph S. Klein
 
Posts: 38
Joined: Mon Mar 24, 2014 6:28 am

Re: How can SQLs applied to database for auto-date entry ?

Postby JackSMT » Tue Jun 03, 2014 10:41 am

Thanks again for the prompt response.
Problem solved. :P
JackSMT
 
Posts: 20
Joined: Tue Mar 25, 2014 6:29 am


Return to OpenOffice.org Base

Who is online

Users browsing this forum: No registered users and 1 guest

cron