auto value problem

thedrone · General User Joined: 09 May 2008 Posts: 6

Hi, I'm quite new to using oo base, but have managed, not without some cursing, to make a relational database with queries forms etc. that is just about ok. However, I have completely failed to get the primary key (id number) to auto increment. I'm at the tearing my hair out stage. I've read quite a number of posts on the subject, but not really found an answer.
My version of oo is 2.4. I've made umpteen tables from scratch, even one that had nothing *but* a primary key, set INTEGER, auto value 'YES' and absolutely no data. It seems to save ok, then if I add any other fields and look at it again it has mysteriously changed to auto value 'NO'. Ok, I can add a number myself each time I enter a new record, but this seems such a fundamental thing to expect a database to do that I just don't understand what's going on. Sorry about the rant.

r4zoli · Posted: Fri May 09, 2008 10:23 am Post subject:

You connect to MySQL or similar databases, or use built in HSQLDB database?
If external it happens with my files, too. It must be set in MySQL.
In HSQLDB normally works for me.

thedrone · General User Joined: 09 May 2008 Posts: 6

Thanks for the reply. As a comparative newbie, this is something I've not really got a grasp on. I've been using Lotus Approach which pretty much does everything I want, but I want to get used to using an alternative in case Approach becomes completely obsolete. I already use oo for most other things. I started off by linking to existing databases, but am currently doing completely new databases with oo. When I looked at the connections, the current driver is set to com.sun.star.comp.sdbc.ODBCDriver. Does this mean I should probably change it?

RPG · Posted: Tue May 13, 2008 4:44 am Post subject:

Hello

About the auto value problem.
there are two solution
one it is a solution for one table. It is describe by Kabing : http://www.oooforum.org/forum/viewtopic.phtml?t=69632&highlight=identity

The other one is for all tables in the database.
Open the database. not the table
goto Edit -->Database -->Advanced click
there is opening a dialog with two tabs goto tab "Generated Values"
mark the check box
write "IDENTITY" in the first line "Instruction automatic increment"
click OK

For new tables it will work for old table you must look : http://www.oooforum.org/forum/viewtopic.phtml?t=69759&highlight=identity

It is working for the HSQLDB for other databases i don't know.
I don't know the exact terms in English it is not my language

Romke

thedrone · General User Joined: 09 May 2008 Posts: 6

Thank you for the suggestions but I'm still having problems. I'm even wondering if I should try and completely uninstall oo and do a fresh install (XP). It doesn't help that I'm completely out of my depth with SQL.
When I looked at edit-database-advanced I got two choices, "end text lines with CTRL+LF" and "form data input checks for required fields" and that is all
Also, as soon as I save a table in design view, I can't edit the field properties at all, only add new fields. This means that if I choose to save a table and have the primary key added automatically, I can't go back and alter the properties.
I tried using the table wizard as a test and even though I selected auto value YES on that, it was all greyed out and set to NO in the design view after saving.
With all the time I'm spending on it, I may as well just add the numbers myself, but I'm stubborn and want to know why it does this!

RPG · Posted: Tue May 13, 2008 12:44 pm Post subject:

Hello thedrone

It looks that you have not activated JAVA.
Look at this link for activating JAVA
http://www.oooforum.org/forum/viewtopic.phtml?t=71036&highlight=instal+java
Be sure you have activated java

thedrone · General User Joined: 09 May 2008 Posts: 6

many thanks for your time - I really hoped this would be the answer when I found I had Java installed but it was not set to the latest version. Unfortunately, even after a complete restart to make sure oo wasn't hanging on to any old settings, I still have the same problem. The table wizard works fine right up until saving then the auto value is suddenly switched from yes to no.

RPG · Posted: Tue May 13, 2008 2:09 pm Post subject:

thedrone

Sorry I can not help you but give not up. I have try to installate a linux on a 200 mhz pentium with 68 MByte. Time over time the CD would not contimue with booting . The solotion was to write the word addswap correct with double D and not one

Romke

thedrone · General User Joined: 09 May 2008 Posts: 6

yes, it usually turns out to be something simple - I don't know if that makes you feel better or worse..

thedrone · General User Joined: 09 May 2008 Posts: 6

Suddenly it works! Thanks Romke for pointing out that I need put IDENTITY in as the auto increment statement as this seems to have cured the problem - I would never have guessed that this was necessary. I did try your suggestion straight away but it made no difference and is only working after the computer was shut down overnight. Coincidence?
To sum up. I made a simple test table using the wizard, adding only the fields AccountID and Description. The AccountID field (Integer) was set to auto value, with the word IDENTITY in the box underneath. Next, I chose to set the existing field AccountID as the primary key, and noticed that the auto value check box was selected automatically. When I finished and opened the table in data input view, <auto-field> was in the AccountID column and this changed to incremental numbers as I added records. Thanks again!

RPG · Posted: Thu May 15, 2008 12:37 am Post subject:

Hello thedrone

I'm glad you have succeed. I remember me I have look for month how to do it. I have read the manual from HSQLDB you can find here
http://hsqldb.org/web/hsqlDocsFrame.html

I would make it complete for other readers.
You have to repeat this for every other database if you want have auto increment.
Identity is only for HSQLDB for other systems I don't know. Look in the documents.for a statement

Romke

keithclark1966 · OOo Enthusiast Joined: 18 Jan 2007 Posts: 189

Tamblyne · General User Joined: 07 May 2006 Posts: 26

RPG · Posted: Sat Jul 05, 2008 2:39 pm Post subject:

Hello

What kind of DB engine you are using? The instruction are special for HSQLDB.
If you are using an other db engine, it is maybe possible that there is no autoincrement.
Check also if java is installed on your machine and I think you have the good tab (page)

Romke

RPG · Posted: Tue Jul 08, 2008 5:44 pm Post subject:

autovalue problem
howto make hsqldb autovalue

I did discover to day this possibilitie for making autovalue in this way.

It looks me only possible with the HSQLDB engine

This can be used special if you don't get the tabpage.

1 Edit the table where you want have the aututo value
2 edit the field where you have auto value
3 choose for the fieldtype BIGINT
4 choose for the autovalue Yes

If you use a wizard you must also choose BIGINT in the autovalue field

I hope it helps
Romke