Ask Your Question
0

Problem creating an auto-value field in a split database?

asked 2016-12-06 06:07:47 +0200

Quarto Die gravatar image

updated 2016-12-06 06:10:29 +0200

I have tried without success to create in Base an autovalue primary field in a split database table. It works fine with the embedded database tables. But for the split database once one creates it, it simply disappears. Issuing an sql command to insert data simply brings up a message that a null value cannot be inserted into the "ID" field (which originally was set up as autovalue and is supposed to populate itself). Likewise typing data into the table one is forced to enter the "autovalue" number. Is there any way to change this? When transferring the embedded table over to a split table this autovalue also disappears.

edit retag flag offensive close merge delete

Comments

I have noticed and reported a connected db bug with autovalue. It works fine in embedded. But in order to create a connected database (JDBC to MariaDB I think) with an autovalue, you must set autovalue and primary key the very first time you create the table. You can not go back later and set it. Hope this might help you. I wasted about a day stumbling over this as I was first learning to use LO. Sadly, my bug report was canceled by higher ups, as not important or something like that

EasyTrieve gravatar imageEasyTrieve ( 2016-12-07 07:22:19 +0200 )edit

Thanks for note, I will try to remember it.

Quarto Die gravatar imageQuarto Die ( 2016-12-09 00:24:51 +0200 )edit

2 Answers

Sort by » oldest newest most voted
1

answered 2016-12-06 17:00:33 +0200

Ratslinger gravatar image

updated 2016-12-09 03:58:08 +0200

First it's not understood why you are having a problem with creating a table with an auto-value. Over the last few days I've created dozens for date testing (as you may realize) and have no problems:

image description

However, I do see a problem when copying a table from an embedded DB to a split DB - the AutoValue changes to "No". You can fix this with two SQL statements. First change the field back to AutoValue:

ALTER TABLE YOURTABLENAME ALTER COLUMN YOURCOLUMNNAME SET GENERATED BY DEFAULT AS IDENTITY

Next get the highest value already in use in the table and add 1 to that for use in the next statement:

ALTER TABLE TOURTABLENAME ALTER COLUMN YOURCOLUMNNAME RESTART WITH XXX

where XXX = that highest value + 1.

Edit 12/08/2016: @ Quarto Die This additional info based upon your answer response.

Created Table in "embedded" DB, copied to "split" DB and executed both ALTER statements (using 0 for restart as it is new without records but 1 works also) all as stated above. This was result:

image description

Entering data, the ID always displayed 0 until the table was closed & re-opened; then existing ID's were OK but new still displayed 0.

Deleting this highlighted piece in the content.xml file:

image description

then corrected the problem and the correct auto increment value always appeared.

edit flag offensive delete link more

Comments

Thank you for the sql statements. I give a little bit more explanation of what my problem was in "my answer". I would have put it as comment but could not include the pictures that way. A picture is worth a thousand words.

Quarto Die gravatar imageQuarto Die ( 2016-12-09 00:22:04 +0200 )edit
1

This is an interesting addition about the .xml file. Thank you!

Quarto Die gravatar imageQuarto Die ( 2016-12-10 00:11:44 +0200 )edit

This two sql statements fixed my issue, thanks for this posting. I'm puzzled on the .xml file, I don't have one in my split folder. I am seeing the bug of always having a 0 in the index field until I close the db and reopen it. Perhaps you could expand on the editing of the .xml file and where it is located.

rotormansa gravatar imagerotormansa ( 2017-04-05 21:29:06 +0200 )edit

A Base .odb is really a zipped file containing different files & folders related including the data files when using an embedded DB. Changing to a split DB removes the data portion and thus greatly lessens problems of data loss due to the zip/unzip of the .odb file. To answer your question, the referred to .xml is still in the zipped portion. Open the .odb with an archive manager and then most files within can be edited with a simple text editor. Please use or keep a backup when doing this.

Ratslinger gravatar imageRatslinger ( 2017-04-05 22:32:04 +0200 )edit

Thank you for the explaination... I peeked in to the odb awhile ago and noticed it was a compressed file but never thought of renaming it to zip and pry it apart. I didn't find the 'stuff' you highlighted... the closest match was this: </db:queries><db:table-representations><db:table-representation db:name="Table1"/><db:table-representation db:name="Table12"/><db:table-representation db:name="W1NRG_ROSTER_LIST"/></db:table-representations></office:database></office:body>... bug is still there.

rotormansa gravatar imagerotormansa ( 2017-04-05 23:23:28 +0200 )edit

I can see this leading up to something other than the subject of this question. Please present this as a new question. Include in the question a link to the process which you used to split your DB (there is more than one), a reference back to this question and a copy of the "content.xml" file.

Ratslinger gravatar imageRatslinger ( 2017-04-05 23:43:02 +0200 )edit
0

answered 2016-12-09 00:19:37 +0200

Quarto Die gravatar image

updated 2016-12-09 00:27:21 +0200

If the database was originally created as a split database one effectively then one gets the screen Ratslinger pictures in the other answer to this question and the autovalue is properly created.

However if a database was originally created as an embedded database and it is opened as a split database one gets this screen when creating a new table in that database:

Test

Then when one attempts to enter data and save the first record one gets this screen (note that autovalue is not in the ID field):

Test of data entry

However running the sql commands proposed by Ratslinger in the other answer to this question, does remedy the situation, except for the problem of the 0's appearing noted already as bug.

edit flag offensive delete link more

Comments

Although I edited my answer with more info, I don't believe it addresses your comments in this answer. If I read it correctly, NEWLY created tables in this "split" DB the auto value doesn't work? If so, I've done it numerous times without a problem.

Ratslinger gravatar imageRatslinger ( 2016-12-09 04:01:52 +0200 )edit

Try another new table. This time enter all your fields as in the sample, and just before saving, go back to the "ID" field and see if "AutoValue" is Yes. If not, change that & save. Then see if table entry works.

Ratslinger gravatar imageRatslinger ( 2016-12-09 04:06:01 +0200 )edit

No it doesn't work. What happens is that I save the table with a Yes "Autovalue" and close the design mode. When I re-open the design mode to edit it or to see what is there, the "Autovalue" has changed back to no and is grayed out and cannot be changes. If I attempt to enter data I get the picture as above. This is a newly created table. The database was embedded. I followed these steps for splitting it: https://forum.openoffice.org/en/forum...

Quarto Die gravatar imageQuarto Die ( 2016-12-10 00:09:40 +0200 )edit

It appears you are using the older process and did not read all the information. In Option 1, Step 1, Note there is a discussion about the auto increment problem and a link to the fix. Also, that link contains another link which is the newer process for the split DB. This newer process contains HSQL 2.3 and the fix. This fix (whichever method chosen) is probably why you are having the problem here and in the other post.

Ratslinger gravatar imageRatslinger ( 2016-12-10 01:08:33 +0200 )edit

FYI - When I mentioned the split DB in this post, the link was to the newer process.

Ratslinger gravatar imageRatslinger ( 2016-12-10 01:20:09 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-12-06 06:07:47 +0200

Seen: 743 times

Last updated: Dec 09 '16