Ask Your Question
1

How to change the default value of field in base?

asked 2016-12-02 04:46:40 +0100

Quarto Die gravatar image

I am trying using SQL to set the default date for a new record in DATEFIELD in my database in LibreOffice Base at the in the sql command window. All efforts have failed so far:

I have tried this command 1: ALTER TABLE "MYTABLE" ALTER COLUMN "DATEFIELD" SET "DATEFIELD"= 2016-12-31

I am getting this result

1: Unexpected token: DATEFIELD in statement [ALTER TABLE "DATEFIELD" ALTER COLUMN "DATEFIELD" SET "DATEFIELD"]

Then I tried:

ALTER TABLE "MYTABLE" ALTER COLUMN "DATEFIELD" SET = 2016-12-31

2: Unexpected token: = in statement [ALTER TABLE "DATEFIELD" ALTER COLUMN "DATEFIELD" SET =]


That is just a random date, it could be the current date also.

edit retag flag offensive close merge delete

Comments

Which database are you using or connecting to, and if connecting, how? Also pls say which OS. Thanks.

EasyTrieve gravatar imageEasyTrieve ( 2016-12-02 21:58:22 +0100 )edit

I do not see how that information pertains to what LO Base does, nor to the results of the sql commands. I am using the latest version of LO Base, in which I am opening MYDATABASE.

Quarto Die gravatar imageQuarto Die ( 2016-12-03 00:34:02 +0100 )edit

@Quarto Die, LO can use different databases and different methods of connecting to the same database, and so because different databases have different SQL dialects, it matters which database you are using.

EasyTrieve gravatar imageEasyTrieve ( 2016-12-03 00:47:30 +0100 )edit
1

I don't think I understood the question about which database. I am using L.O. Base. Ratslinger seems to be providing some interesting answers in my case. See below in answers.

Quarto Die gravatar imageQuarto Die ( 2016-12-05 19:07:40 +0100 )edit

1 Answer

Sort by » oldest newest most voted
2

answered 2016-12-02 05:59:14 +0100

Ratslinger gravatar image

updated 2016-12-03 16:21:03 +0100

Alter Table "MYTABLE" Alter Column "DATEFIELD" set default '2016-12-31'

Edit 12/02/2016

Based upon @Quarto Die comment, I did some further testing. The testing was done using an embedded DB, a split DB and a MySQL DB. The only field type of concern here is a Date type.

If a table is created without a default date, the ALTER statement above works time after time.

Embedded DB - If a default date is declared, it gets screwed up unless you use the format of YYYY-MM-DD (see this post). Any other format gets really messed up. The ALTER statement executes but has no effect on the default. The answer is to delete the default by editing the table. Then the ALTER statement works (although you can't view it by editing the table - another bug).

Split DB - If a default date is declared, it gets screwed up unless you use the format of YYYY-MM-DD (same post). Any other format gets really messed up. The ALTER statement executes but has no effect on the default. The ONLY answer found is to delete the field by editing the table and adding the field again without the default(see below) Then the ALTER statement works (although you can't view it by editing the table - another bug). If the table is created outside of LO Base (in this case used SQL Workbench/J) there was NO problem with an initial default date and the ALTER statement worked.

MySQL DB - If a default date is declared, it gets screwed up unless you use the format of YYYY-MM-DD (same post). Any other format gets really messed up. The ALTER statement executes but has no effect on the default. The ONLY answer found is to delete the field by editing the table and adding the field again without the default(see below). Then the ALTER statement works (although you can't view it by editing the table - another bug). If the table is created outside of LO Base (in this case used MySQL Workbench) there was NO problem with an initial default date and the ALTER statement worked.

So the problem always occurs, and the ALTER statement has no affect (even with YYYY-MM-DD format), if the default is set in the original design of the table. The problem definitely lies within LO Base.

In the next day or two I will check bug reports and if none, submit one.

Edit #2 12/02/2016 - Have further info on default values. When using Base to create and set the default for a table field, it is stored in the content.xml file of the .odb while using an external source, the default is stored in the database itself.

What this means is with an embedded DB you can simply edit the table and remove the default. Then use the ALTER statement to set a new default.

With split or external DB, open the .odb with an archive manager, open the content.xml file with a text editor, find ... (more)

edit flag offensive delete link more

Comments

Thank you for the formula. It gives me: "Command successfully executed" in sql, but when I go to add a new record in a table view or in a form, I do not get the new default date, but the original default date I set up before. Do you know what is not working?

Quarto Die gravatar imageQuarto Die ( 2016-12-02 21:13:00 +0100 )edit

One other note on the subject. Default values set in the database (includes using ALTER statement) cannot be seen by editing the table in Base. For that use an SQL statement:

SELECT * FROM "INFORMATION_SCHEMA"."COLUMNS" WHERE "TABLE_NAME" = 'Your table name here' ORDER BY "ORDINAL_POSITION"
Ratslinger gravatar imageRatslinger ( 2016-12-03 06:18:22 +0100 )edit

Thank you for the in depth work on the this point. For the split DB when I attempt to open the DB with the default archive manager for Linux Mint 18 - Engrampa 1.14.1 it replies: Archive type not supported. Is there an alternate Archive Manager I can use to open this odb file. I see that others like unzip seem to be installed on my system, but I do not know how to access them or if they will work.

Quarto Die gravatar imageQuarto Die ( 2016-12-05 19:04:37 +0100 )edit

@Quarto_Die: since you mention ODB, you're using HSQLDB 1.8, the self-contained XML-based database engine that requires Java. All OpenDocument Format files, except those starting with 'f' (fods for Flat OpenDocument Spreadsheet, to name 1), are ZIP files containing a bunch of XML files.

rautamiekka gravatar imagerautamiekka ( 2016-12-05 19:12:12 +0100 )edit

@Quarto Die not sure why you would have problem. I'm currently on Mint 18. The difference is I'm using Cinnamon and it appears you are using Mate. Pre-installed for me is "File-roller" and it works fine. You might give that a try.

Ratslinger gravatar imageRatslinger ( 2016-12-05 20:37:59 +0100 )edit

File-roller worked fine to modify the content.xml. The ALTER statement with a new date gave the result "Command successfully executed". In the table itself however when adding data, the new date did not appear. A new entry is now blank. I suppose one could go back into the content.xml to change that. However, in editing a form based on the table, I can insert any default date into the form through the control menu. That solves my problem.

Quarto Die gravatar imageQuarto Die ( 2016-12-05 23:43:01 +0100 )edit

The new date WILL appear when you tab through that field. Again, this is part of the problem. It gets more confusing when you read the bug reports. It's a GUI/table default combination problem.

Ratslinger gravatar imageRatslinger ( 2016-12-06 00:13:34 +0100 )edit

Just tested on a form with default date set with ALTER statement. If the date field is left blank, the default date will be input.

Ratslinger gravatar imageRatslinger ( 2016-12-06 00:26:49 +0100 )edit

It does not work for me in an embedded database opened as split-database (perhaps not the correct terminology), it may work in a newly created split database for you, if so the situation is similar to my reply in my question "Problem creating an auto-value field in a split database?" Here is the message in my database when attempting to enter a record with no date in the field after running successfully that sql: Error writing to database. Input required in field "DATE" Please enter a value.

Quarto Die gravatar imageQuarto Die ( 2016-12-09 00:44:09 +0100 )edit

I've tried many ways to duplicate your last comment. I have no problems when entering data directly into the table. However, in a form I can duplicate the problem if the original "embedded" field was a required field. This sets the control property on the Data tab of the field in the form to Input required = "Yes. Change to "No" and it works fine (just no display of the date until updated & gone back to). Input is NOT required if a Default is to be used.

Ratslinger gravatar imageRatslinger ( 2016-12-09 02:45:44 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-12-02 04:46:40 +0100

Seen: 668 times

Last updated: Dec 03 '16