Android Question Jrdc with ucanaccess not committing inserts (Solved)

K

kgf

Member
Licensed User
Longtime User
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.

B4X: 
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.
 
Sort by date Sort by votes

OliverA

Expert
Licensed User
Longtime User
You may not be able to use pooling with ucanaccess. It’s more like SQLite than a sql db
 
Upvote 0
K

kgf

Member
Licensed User
Longtime User
Looking at the data by opening the test.accdb and then opening tblTest. Not sure what you mean by pooling.
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
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
 
Upvote 0
K

kgf

Member
Licensed User
Longtime User
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
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
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.
 
Upvote 0
K

kgf

Member
Licensed User
Longtime User
OliverA said:
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.
Click to expand...
http://ucanaccess.sourceforge.net/site.html
B4X: 
#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
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
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.
 
Upvote 0
K

kgf

Member
Licensed User
Longtime User
OliverA said:
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
Click to expand...
Ok will give it a go thanks. do I use it something like below?
B4X: 
    Dim cmd As DBCommand = jRDC2Utils.CreateCommand("insert_team", Array())
    Wait For (jRDC2Utils.ExecuteQuery(cmd)) complete (res As DBResult)
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
I think you just need to replace line 57 (should be an Else statement) with the following code
B4X: 
    Else If driverClass.ToLowerCase.Contains("ucanaccess") Then
       UsePool = False
       SQLite.Initialize(config.Get("DriverClass"), config.Get("JdbcUrl"))
   Else
kgf said:
Ok will give it a go thanks. do I use it something like below?
Click to expand...
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
B4X: 
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.
 
Upvote 0
K

kgf

Member
Licensed User
Longtime User
Thanks, will try as you say
 
Upvote 0
K

kgf

Member
Licensed User
Longtime User
Okay have tried it within B4J using the modded JRDC client examples
B4X: 
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.
 
Upvote 0
K

kgf

Member
Licensed User
Longtime User
OliverA said:
Maybe this?
Click to expand...
B4X: 
                    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
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
In the RDCHandler module, in the ExecuteBatch2, place the
B4X: 
con.ExecNonQuery("COMMIT;")
right AFTER
B4X: 
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.
 
Upvote 0
K

kgf

Member
Licensed User
Longtime User
OliverA said:
That does the trick for me.
Click to expand...
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.
B4X: 
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
 
Upvote 0

Mashiane

Expert
Licensed User
Longtime User
In this thread comment, Erel said, I quote

Erel said:
You can use Jackcess but not with jSQL library as Jackcess by itself doesn't implement a Jdbc driver.
Click to expand...

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
 
Upvote 0

Ryan Pigeon

Member


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!
 
Upvote 0

Ryan Pigeon

Member
Hi Oliver. Do you mind sharing the example of this?
 
Upvote 0
You must log in or register to reply here.

Similar Threads

  • Solved
Android Question (Solved) jRDC2 can someone give example on correct authentication
Replies
7
Views
2K
aeric
Z
  • Question
Android Question JRDC2 MySQL Begin Transaction, Commit, Rollback
Replies
2
Views
2K
zabayin
Z
  • Article
Android Example B4A UCanAccess Demo (works under B4J - see notes)
Replies
4
Views
7K
fernando.oliboni
F
  • Article
B4A Library [B4X] PreoptimizedCLV - Lazy loading extension for xCustomListView
2 3
Replies
53
Views
38K
Erel
  • Question
Android Question [SOLVED] jRDC2 how to insert multiple field
Replies
4
Views
2K
yfleury
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…