Android Question Jrdc with ucanaccess not committing inserts (Solved)

[kgf]

I have Jrdc connecting with ms access ( would prefer not to use access but stuck with it for now). Its working fine for select queries but having trouble with inserts.

I am testing on a simple table with an auto 'id' column and 'UserName' text column with unique constraint. I have the config sql as sql.insert_team=INSERT INTO tblListCurrentTeam (UserName) VALUES (?) and using the commands below.

Dim cmd As DBCommand = CreateCommand("insert_team",Array("test name"))
        Dim j As HttpJob = CreateRequest.ExecuteBatch(Array(cmd), Null)
        Wait For(j) JobDone(j As HttpJob)

In both the B4A and B4J logs the first time I run it, it returns success, if I repeat it, it then shows a unique constraint violation, (HsqlException) org.hsqldb.HsqlException: integrity constraint violation: unique constraint or index violation; TBLLISTCURRENTTEAM_USERNAME table: TBLLISTCURRENTTEAM which is to be expected as the UserNames hould be unique. So it seems to have added a row, BUT when I go to the access table it hasnt been inserted.

I do get this in B4J logs for each table in the database when Jrdc starts but dont know if it is part of the problem. WARNING:Unknown expression: GenUniqueID() (default value of column ...

Tried running both B4J and B4a in release mode and no change, anyone had a similar issue.

 

[Ryan Pigeon]

In the RDCHandler module, in the ExecuteBatch2, place the

con.ExecNonQuery("COMMIT;")

right AFTER

con.TransactionSuccessful

That does the trick for me. Please note that this is definitely an issue with ucanacess, not jRDC2. I almost gave up, since I could not even create a db without errors. I just did not follow your instructions to the letter (yeah) as to how to do the AdditionalJar directives (I placed all the jars in the Additional Libraries folder (adjusting AdditionaJar directives accordingly), instead of only putting the ucanaccess jar in there and the other four in the lib sub under the Additional Libraries folder). Another post reinforced this point (https://www.b4x.com/android/forum/threads/sql-tutorial.35185/page-2#post-247083) and then at least I could start some testing.

This worked for the first edit, but when I try edit record again, it bombs with an error!

 

[Ryan Pigeon]

In the RDCHandler module, in the ExecuteBatch2, place the

con.ExecNonQuery("COMMIT;")

right AFTER

con.TransactionSuccessful

That does the trick for me. Please note that this is definitely an issue with ucanacess, not jRDC2. I almost gave up, since I could not even create a db without errors. I just did not follow your instructions to the letter (yeah) as to how to do the AdditionalJar directives (I placed all the jars in the Additional Libraries folder (adjusting AdditionaJar directives accordingly), instead of only putting the ucanaccess jar in there and the other four in the lib sub under the Additional Libraries folder). Another post reinforced this point (https://www.b4x.com/android/forum/threads/sql-tutorial.35185/page-2#post-247083) and then at least I could start some testing.

I think it's working - entered con.close after the commit

 

[OliverA]

I think it's working - entered con.close after the commit

Strange, since con.close is called in the Handle sub, the sub that makes the ExecuteBatch2 call.

 

[Ryan Pigeon]

Strange, since con.close is called in the Handle sub, the sub that makes the ExecuteBatch2 call.

I noticed that, but I am almost there, Oliver! I can read records from db, update records, can delete from db, cannot add more records to db. If I add more records it throws exception.

 

[Ryan Pigeon]

OK FOUND THE ISSUE WITH NOT BEING ABLE TO ADD:

Dim Paremeters() As String = Array As String(B4XPages.MainPage.KVS.Get("id_user"), Data.Get("month_c"), Data.Get("event_type"), Data.Get("description"), Data.Get("value_c"))

Is not getting the correct values

 

[Ryan Pigeon]

SOLVED!!!!!!!!!!!! YIPEEEE!!!!!!.

Now on to test all scenarios. Damn! What a journey. LOL!