PRogram stops on simple update DML

[forwww]

Hi,

I'm using UCanAccess with newest version and it works fine.
Except when I want to make an simple one row Update on a Table with ca. 12K Rows.

I do this command:

sqlstatment.execupteUpdate("UPDATE tb_projectstates SET id_project=1 WHERE id_pk=1");

Tablestructure:
id_pk Number (primarykey)
id_project Number
timestamp_change Date/Time
id_phase Number

I tried a simple program witch starts, connects to access db and calls only this command. But the program halts when calling this update command. I have waited 15min but no going on.

This table tb_projectstates is used within a pivot-accessquery. Normally created within access and saved.
If i delete this query in access, the update dml works perfect!

What is the reason??

Thans for your help!

Regards

[Gord Thompson]

I am unable to recreate your issue using console.bat with the attached test database. There must be something else going on in your database that causes the issue.

Can you try modifying my sample database to have it demonstrate your problem? Or, can you provide a sample of your database that will reproduce the behaviour you decribe?

[forwww]

Hi Gord,

thank you for your fast answear and sorry for my late! I was on holidays :-)

With your testdb it worked.
But then I copied my tables and queries in your db file and then I have the same problem like in my db. I have attached the modified file. When I delete the saved access query "Projekte_Phasen_Gruppen_Zeit" it works normally.

My Connectionparams are:
Sys.setDbDriver("net.ucanaccess.jdbc.UcanaccessDriver");
Sys.setDbUrlSchema("jdbc:ucanaccess://c:/work/xtabtest.accdb;");

My Update DML:
UPDATE tb_projectstates SET id_phase=1 WHERE id_pk=1;

Thanks for your help!

Best Regards,
Martin

[Gord Thompson]

I am able to reproduce the issue under UCanAccess 3.0.6 using your sample database file. When I try to load it with console.bat I see the message

Cannot load view Projekte_Phasen_Gruppen_Zeit cannot load this query

When I try running your UPDATE query it hangs, and Task Manager shows that java.exe is not using any CPU. Something about processing the UPDATE query must have implications for the database object(s) that UCanAccess creates to support the [Projekte_Phasen_Gruppen_Zeit] crosstab query, and those object(s) either do not exist or are in an unusable state.

I notice that the [Projekte_Phasen_Gruppen_Zeit] query is based on [Projekte_Phasen_Gruppen], which is a UNION query.

Perhaps this information will help Marco track down the cause.

[Marco Amadei]

yes, something very strange in this db, I'm analyzing...

[Marco Amadei]

Gord, you were right, some hsqldb object is locked because of the very particular loading sequence, even if I haven't identified the root cause yet. No matter if you're doing an update or something else.
I'll let you know here once I've done. The current bugs backlog deserves a fix release before the 4.0.0.

[Marco Amadei]

Okay, found.
The qury Projekte_Phasen_Gruppen is using the first(datetime) aggregate function, that is implemented by UCanAccess with the same java routine used for first(text), first(number) and so on.
While first(text), first(number) work nicely, first(datetime) used in a view may lead hsqldb in an inconsistent state if another view references it.
The bug has been in svn by implementing both the first(datetime) and last(datetime) routines using the hsqldb native language instead.
The fix will be in the 2.0.7.
This has been a tricky task...shit happens.
Cheers, Marco

[forwww]

Hello Marco,

thanks! Great that you find the problem :-)

When do you think you release the 3.07 version?

Martin

[Marco Amadei]

I'm going to release it next week, likely on freeday.
Cheers, Marco

[forwww]

Great, thanks

Martin