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.

 

[OliverA]

You may not be able to use pooling with ucanaccess. It’s more like SQLite than a sql db

 

[kgf]

Looking at the data by opening the test.accdb and then opening tblTest. Not sure what you mean by pooling.

 

[OliverA]

I’m not in front of a computer, but I’m certain jRDC2 uses database pooling. Also, you need to shut down the server before you open the database with another tool such as access. The driver does not support concurrency externally to itself. So fire up the server, insert the data, stop the server and then look at the data in the access database. I have a modified version of jRDC2 that works with SQLite and does not use pooling. It can be modified to wor with ucanaccess

 

[kgf]

Same result with running then closing B4A and B4J. Also have just tried changing the primary key to text and inserting that also, first result show success, second shows integrity constraint so the data is going somewhere, just not being committed to the acdb. Time to try your version

 

[OliverA]

Give me links to all the libraries that you are using to make ucanaccess work (yeah, I'm lazy). Also let me know all the AdditionalJar instructions you are using.

 

[kgf]

Give me links to all the libraries that you are using to make ucanaccess work (yeah, I'm lazy). Also let me know all the AdditionalJar instructions you are using.

http://ucanaccess.sourceforge.net/site.html

#AdditionalJar: ucanaccess-4.0.4.jar
#AdditionalJar: lib/commons-lang-2.6.jar
#AdditionalJar: lib/commons-logging-1.1.3.jar
#AdditionalJar: lib/hsqldb.jar
#AdditionalJar: lib/jackcess-2.1.11.jar

I am looking at your modded JRDC2 now, have got the server running with the above libraries. Have only found a B4J client example, will your DBRequestManager.bas and jRDC2Utils.bas code work with with B4A, or does it need some modification

 

[OliverA]

DBRequestManger should be the standard one (of the time the mod was published) that is used to access jRDC2. jRDC2Utils is very generic and should work, except maybe the TableView portion of the code.

 

[kgf]

DBRequestManger should be the standard one (of the time the mod was published) that is used to access jRDC2. jRDC2Utils is very generic and should work, except maybe the TableView portion of the code

Ok will give it a go thanks. do I use it something like below?

    Dim cmd As DBCommand = jRDC2Utils.CreateCommand("insert_team", Array())
    Wait For (jRDC2Utils.ExecuteQuery(cmd)) complete (res As DBResult)

 

[OliverA]

I think you just need to replace line 57 (should be an Else statement) with the following code

    Else If driverClass.ToLowerCase.Contains("ucanaccess") Then
       UsePool = False
       SQLite.Initialize(config.Get("DriverClass"), config.Get("JdbcUrl"))
   Else

Ok will give it a go thanks. do I use it something like below?

No. If you use INSERT/UPDATE/DELETE commands, then you need to use jRDC2Utils.ExecuteCommands. Since it expects a list and it returns an integer (the number of commands executed), the correct call would be

Wait For (jRDC2Utils.ExecuteCommands(Array(cmd))) complete (count as Int)

If you can, I would see if the B4J example runs. You may need to modify some SQL statements in the config.properties to reflect the SQL syntax of Access.

 

[kgf]

I think you just need to replace line 57 (should be an Else statement) with the following code

    Else If driverClass.ToLowerCase.Contains("ucanaccess") Then
       UsePool = False
       SQLite.Initialize(config.Get("DriverClass"), config.Get("JdbcUrl"))
   Else

No. If you use INSERT/UPDATE/DELETE commands, then you need to use jRDC2Utils.ExecuteCommands. Since it expects a list and it returns an integer (the number of commands executed), the correct call would be

Wait For (jRDC2Utils.ExecuteCommands(Array(cmd))) complete (count as Int)

If you can, I would see if the B4J example runs. You may need to modify some SQL statements in the config.properties to reflect the SQL syntax of Access.

Thanks, will try as you say

 

[kgf]

Okay have tried it within B4J using the modded JRDC client examples

Sub btnSetGrade_Action
    
    Log("button clicked")   
    Dim cmd As DBCommand = jRDC2Utils.CreateCommand("insert_test",Array("abc","absnckdmdm"))
    Wait For (jRDC2Utils.ExecuteCommands(Array(cmd))) complete (count As Int)
    
End Sub

and get the same results as before, first click ok, second integrity error but nothing in the access table. Oddly the third click is fine, and the fourth has the integrity error again. Must be a Ucanaccess issue.

 

[OliverA]

just not being committed to the acdb

Maybe this? https://www.b4x.com/android/forum/threads/jsql-and-ucanaccess.62909/#post-477390

 

[kgf]

Maybe this?

                    con.ExecNonQuery2(Main.rdcConnector1.GetCommand(cmd.Name), cmd.Parameters)
                    'added
                    con.ExecNonQuery("COMMIT;")
                    Log("committed " & cmd.Name)

Added this to the RDC Handler module, log committed shows and no errors but same results, nothing saved to table

 

[OliverA]

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.

 

[kgf]

That does the trick for me.

Does the trick for me too. Works with the B4A app now as well. Much appreciate your assistance with this OliverA

So in summary, to fix this Ucanaccess problem what was required was to edit the B4J RDC handler Sub 'ExecuteBatch2(con As SQL, in As InputStream, resp As ServletResponse) As String' and edit this part of the code to add the line con.ExecNonQuery("COMMIT;") after con.TransactionSuccessful.

If Not(errFlag) Then
            res.Rows.Add(Array As Object(0))
            con.TransactionSuccessful
            con.ExecNonQuery("COMMIT;") ' added this line
            Dim data() As Byte = ser.ConvertObjectToBytes(res)
            resp.OutputStream.WriteBytes(data, 0, data.Length)
        Else

 

[Ryan Pigeon]

Hi guys. Yeah we need an expert here. Also if u delete it does not delete from the dB.

 

[Mashiane]

In this thread comment, Erel said, I quote

You can use Jackcess but not with jSQL library as Jackcess by itself doesn't implement a Jdbc driver.

Considering that jRDC2 underneath is using jSQL, couldnt this be the issue. Well that comment was way back in 2014 and things might have changed and I could be wrong.

Source:

SQL Tutorial

B4J jSQL library is similar to Basic4android SQL library. This tutorial will cover the differences between the two libraries. You can read more about the library methods here: http://www.b4x.com/android/forum/threads/sql-tutorial.6736/#content jSQL library is not tied to any specific SQL...

www.b4x.com

 

[Mashiane]

and things might have changed

I have just added the COMMIT clause to my implementation for testing (not jRDC2 though but direct), my its kinda crazy, all CRUD functions are working.

 

[Ryan Pigeon]

In this thread comment, Erel said, I quote



Considering that jRDC2 underneath is using jSQL, couldnt this be the issue. Well that comment was way back in 2014 and things might have changed and I could be wrong.

Source:

SQL Tutorial

B4J jSQL library is similar to Basic4android SQL library. This tutorial will cover the differences between the two libraries. You can read more about the library methods here: http://www.b4x.com/android/forum/threads/sql-tutorial.6736/#content jSQL library is not tied to any specific SQL...

www.b4x.com

Thanks, brother! I will let you know once I have slain the dragon!! There is definitely a solution out there!! Thank you for all the homework so far!

 

[Ryan Pigeon]

Does the trick for me too. Works with the B4A app now as well. Much appreciate your assistance with this OliverA

So in summary, to fix this Ucanaccess problem what was required was to edit the B4J RDC handler Sub 'ExecuteBatch2(con As SQL, in As InputStream, resp As ServletResponse) As String' and edit this part of the code to add the line con.ExecNonQuery("COMMIT;") after con.TransactionSuccessful.

If Not(errFlag) Then
            res.Rows.Add(Array As Object(0))
            con.TransactionSuccessful
            con.ExecNonQuery("COMMIT;") ' added this line
            Dim data() As Byte = ser.ConvertObjectToBytes(res)
            resp.OutputStream.WriteBytes(data, 0, data.Length)
        Else

Hi Oliver. Do you mind sharing the example of this?