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.
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
Thanks for note, I will try to remember it.
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:
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:
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:
then corrected the problem and the correct auto increment value always appeared.
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.
This is an interesting addition about the .xml file. Thank you!
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.
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.
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.
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.
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:
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):
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.
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.
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.
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: [Tutorial] Splitting an "embedded HSQL database" (View topic) • Apache OpenOffice Community Forum
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.