How to change the default value of field in base?

English
, ,
#1

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.

Base - List box not formatted as original field in associated table
Base: Default date value shows up wrong in table -- any ideas?
#2

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

#3

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.

#4

@QuartoDie, 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.

#5

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.

#6

Alter Table “MYTABLE” Alter Column “DATEFIELD” set default ‘2016-12-31’

Edit 12/02/2016

Based upon @QuartoDie 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 the faulty date (format will look correct) and delete the date leaving the quote marks intact. Then the ALTER statement works (tested).

Edit 12/03/2016: Bug report submitted Bug 104375.

#7

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?

#8

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"
#9

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.

#10

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

#11

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

#12

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.

#13

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.

#14

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.

#15

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.

#16

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.

#17

Are the databases you have test made by this process which I used: [Tutorial] Splitting an "embedded HSQL database" (View topic) • Apache OpenOffice Community Forum . If not that is probably the difference.

#18

Please see comment in other post - click here.

#19

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.