Cannot Alter or Delete records with date field

Creating tables and queries
Post Reply
Rogier
Posts: 6
Joined: Fri Sep 24, 2010 11:07 pm

Cannot Alter or Delete records with date field

Post by Rogier »

When I try to alter or delete a record in a table and that record contains a date field i get a java error: (see attachment)

Error updating the current record

SQL Status: 37000
Error code: -16

Wrong data type: java.lang.NumberFormatException: For input string: "2010-10-02"

This happens on older and new databases. What is going wrong?
Attachments
Screenshot Base error.png
OOo 3.2 UK
Ubuntu 10.04.1
Rogier
Posts: 6
Joined: Fri Sep 24, 2010 11:07 pm

Re: Cannot Alter or Delete records with date field

Post by Rogier »

Actually I can't alter or delete any record after upgrading to OOo3.2. ?????
Reinstall base and java, no luck.
OOo 3.2 UK
Ubuntu 10.04.1
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Cannot Alter or Delete records with date field

Post by Villeroy »

Do not use the OOo from Ubuntu. If you want to achieve anything with Base, you've got to get OOo from download.openoffice.org
Current version is 3.2.1, working beta version 3.3 is availlable.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Rogier
Posts: 6
Joined: Fri Sep 24, 2010 11:07 pm

Re: Cannot Alter or Delete records with date field

Post by Rogier »

I tried to install directly from OOo but the desktop integration didn't work. I removed all the installed packages and installed the complete suite through the Ubuntu Software Center. The installed version is 3.2.1

When trying Base: same error. I never had this problem with earlier versions. Could it be a Java problem?
OOo 3.2 UK
Ubuntu 10.04.1
Rogier
Posts: 6
Joined: Fri Sep 24, 2010 11:07 pm

Re: Cannot Alter or Delete records with date field

Post by Rogier »

Reinstalled HSQL. No effect.

This is getting bizarre.
OOo 3.2 UK
Ubuntu 10.04.1
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Cannot Alter or Delete records with date field

Post by eremmel »

1. Java got since 1.6.0_18 more strict on Date format (See here. Your case looks slightly different, but I notice that you are a Dutch language user. It might be that java is expecting a different date format, but the ansi format: YYYY-MM-DD should be the default. You might try to rollback your java version to prior 18 and see what happens.
2. You stated that you tried the official OOO version but you missed integration with desktop. Did you have been able to test this issue never the less? You might also move your database document to an other system and test it there.
3. Check your form definition and table definition and make sure the format match.
4. Try to remove a records directly with SQL (Tools->SQL...) with something like 'DELETE FROM "UrenKM_2010" WHERE "DATUM" = CAST( '2010-10-02' AS "DATE") '
5 Try to remove your user profile, there are posts out there that have 'format' issues when upgrading to 3.2.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Cannot Alter or Delete records with date field

Post by Villeroy »

Rogier wrote:Reinstalled HSQL. No effect.

This is getting bizarre.
In 8 years using Linux I never observed that reinstallation had any effect at all.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Rogier
Posts: 6
Joined: Fri Sep 24, 2010 11:07 pm

Re: Cannot Alter or Delete records with date field

Post by Rogier »

@eremmel

It's not just dates. Its any field type. I created a table in a new database. The table has an index PK and a text field. Once a record is created it cannot be altered or deleted and the java numberformat exception always refers to the first field involved in the transaction.

Wierd
OOo 3.2 UK
Ubuntu 10.04.1
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: Cannot Alter or Delete records with date field

Post by chrisb »

I’ve got the exact same problem as Rogier with exactly the same error messages i.e. can not edit then save nor delete an existing record.
Having first discovered the problem I then checked a table from a different open office database and found the same issue.
The common factor with both tables was that a date field was set as the primary key.
Incidentally the date field was not the first field in both tables.

I solved the problem like this:-

In Base window hit tables to see your tables.
Right click your table. Select ‘Copy’.
Right click below your table.
Select ‘Paste’.
Tick the box ’Create primary key’.
The default field name is ‘ID’. If you have an existing field named ‘ID’ in your table then rename the new primary key something like ’ID2’.
Hit ‘Create’.
A new table is generated with the original name + the suffix ‘2’.
Right click the new table. Select ‘Edit’.
You will see 2 primary keys the original and the one we just created which is named ‘ID’.
Right click in left margin over the new primary key ‘ID’. Select ‘Primary Key’.
If you want your primary key to auto increment then set ‘Auto Value’ to ‘Yes’.
Hit ‘Save’. Close window.

Double click your new table. Type in a new record (any garbage will do). Save and close table.
Reopen the table. Right click in left margin next to last record. Select ‘Delete’.

Hopefully this has worked for you. Now you just need to delete your original table (or possibly just rename it something like original name + ‘dodgy‘) then rename you new table as the old one so it works with forms, reports .etc.

I carried this out exactly as described without the need to refresh tables.
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Cannot Alter or Delete records with date field

Post by Villeroy »

The problem affects at least one of my example documents on this forum: http://user.services.openoffice.org/en/ ... 39&t=27581 and Chris' method does not fix it.
It is a simple list of dates with an auto-ID and a date column. Everything is possible in the table grid. The form does not allow for update/insert/delete.
Is there any issue number about this? I can not find anything.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: Cannot Alter or Delete records with date field

Post by chrisb »

I've been messing around with this and have discovered that:-

IF the first field in a table is not set as the primary key
AND the first field in a table regardless of Field Type is set to 'Entry Required'
THEN either this error is generated or the table data corrupts.

To test this copy the data between the asterisks.

*******************************************************

create table "TestTable"
(
"Int" integer,
"Txt" varchar(1),
"Date" Date,
primary key ("Date")
)

insert into "TestTable" ("Int","Txt","Date") values ('1','a','2010-01-01')
insert into "TestTable" ("Int","Txt","Date") values ('2','b','2010-01-02')
insert into "TestTable" ("Int","Txt","Date") values ('3','c','2010-01-03')
insert into "TestTable" ("Int","Txt","Date") values ('4','d','2010-01-04')
insert into "TestTable" ("Int","Txt","Date") values ('5','e','2010-01-05')
insert into "TestTable" ("Int","Txt","Date") values ('6','f','2010-01-06')
insert into "TestTable" ("Int","Txt","Date") values ('7','g','2010-01-07')
insert into "TestTable" ("Int","Txt","Date") values ('8','h','2010-01-08')
insert into "TestTable" ("Int","Txt","Date") values ('9','i','2010-01-09')

*******************************************************

From database menu select Tools>SQL.
Right click over the window that opens. Select 'Paste'. Hit execute. Close window.
Menu>View>Refresh Tables. There is a new table called 'TestTable'.
Open the table. Right click in the left hand margin next to any record and select 'Delete Rows'. Hit 'Yes' and row is deleted.
Close window.
Right click table. Select 'Edit'
Set 'Entry Required' (bottom of window) for the first field 'Int' to 'Yes'
Close window and hit 'Yes' to save changes.

Open your table and try to delete another record.
On my machine this produces the error message 'Wrong data type: java.lang.IllegalArgumentException'

SOLUTION: MAKE SURE THE PRIMARY KEY IS THE FIRST FIELD IN THE TABLE. Users usually use forms to input data anyway.
IF THE PRIMARY KEY IS NOT THE FIRST FIELD IN THE TABLE THEN ENSURE THE FIRST FIELDS 'Entry Required' IS SET TO NO.

It may be that the error is generated for other reasons but on my machine this is the cause.
It's got to be a bug.
In truth I rarely find the need to edit or delete a record and imagine that to be the case for most users.
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Cannot Alter or Delete records with date field

Post by Villeroy »

I missed that "entry required" on table level has this influence on form level.
Thank you for this valuable work around.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
r4zoli
Volunteer
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

Re: Cannot Alter or Delete records with date field

Post by r4zoli »

I tested with Villeroy data in OOo 3.2.1 and the problem occurs.
In OO330m8 development version of OOo 3.3 the problem solved in some way, I can delete records in table data view.
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
Rogier
Posts: 6
Joined: Fri Sep 24, 2010 11:07 pm

Re: Cannot Alter or Delete records with date field

Post by Rogier »

I did some tests with tables and sometimes the problem did not occur. Might have something to do with primary keys and 'nulls allowed' as chrisb stated.

At the moment I am changing to a fresh Xubuntu install. Although the program versions and files are the same the problem doesn't occur here. Apparently it is introduced with some bogus java or OOo updates.

For me it is solved now with the transition to the new system. I hope it get's really solved with an update to 3.3. (If Oracle will let it happen?)

Thanks for the support, Rogier.
OOo 3.2 UK
Ubuntu 10.04.1
Post Reply