Android Question Jrdc with ucanaccess not committing inserts (Solved)

kgf

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

jimmyF

Active Member
Licensed User
Maybe this:

B4X:
sql.insert_team=INSERT INTO tblListCurrentTeam (ID, UserName) VALUES (?,?)
and
B4X:
Dim cmd As DBCommand = CreateCommand("insert_team",Array(Null, "test name"))

I haven't tested it myself though.
 
Last edited:
Upvote 0

kgf

Member
Licensed User
Have created a new access database with a single table , it has primary key autonumber UserNameID and text UserName. Have removed constraint from Username so duplicates are allowed. Using sql.insert_team=INSERT INTO tblListCurrentTeam (UserNameID, UserName) VALUES (?,?)
CreateCommand("insert_team",Array(Null,"abcdefghijkl"))
Dim j As HttpJob = CreateRequest.ExecuteBatch(Array(cmd), Null)

From B4J - Command: batch (size=1), took: 1ms, client=192.168.100.49
From B4A - j.Success true
If I keep running the request from a button click the logs repeat as above
BUT still no records in the access database or other warnings anywhere
 
Upvote 0

jimmyF

Active Member
Licensed User
Try this:
B4X:
sql.insert_team=INSERT INTO tblListCurrentTeam (UserNameID, UserName) VALUES (null,?)

Assuming that your connection has been set up correctly. then it is probably because you are using MS Access.
I suggest that you download and install the Community Edition of MySQL.
 
Upvote 0

kgf

Member
Licensed User
Need to keep the database file based for now due to very remote locations it is used, need to move it via usb to offline laptops. Have tried using sqlite but getting odbc errors with the Access front end and a sqlite db so hoping I could just use Ucanaccess to save to the access tables. Which would save extra steps in import/exports.
 
Upvote 0

jimmyF

Active Member
Licensed User
Did you try the line of code in your config file?

I don't believe yours was correct .
 
Upvote 0

jimmyF

Active Member
Licensed User
Did you actually try it?
Your code was incorrect.
Your code:
B4X:
sql.insert_team=INSERT INTO tblListCurrentTeam (UserNameID, UserName) VALUES (?,?)
My Code:
B4X:
sql.insert_team=INSERT INTO tblListCurrentTeam (UserNameID, UserName) VALUES (null,?)
 
Upvote 0

kgf

Member
Licensed User
My code, using different names now with new test database
B4X:
sql.insert_test=INSERT INTO tblTest (UserNameID, UserName) VALUES (Null,?)

Dim cmd As DBCommand = CreateCommand("insert_test",Array("absnckdmdm"))
 
Upvote 0

jimmyF

Active Member
Licensed User
B4X:
Dim cmd As DBCommand = CreateCommand("insert_test",Array(Null,"absnckdmdm"))
 
Upvote 0

kgf

Member
Licensed User
Returns this by using the null in the array. (HsqlException) org.hsqldb.HsqlException: Invalid argument in JDBC call: parameter index out of range: 2
 
Upvote 0

jimmyF

Active Member
Licensed User
B4X:
sql.insert_test=INSERT INTO tblTest (UserNameID, UserName) VALUES (?,?)
B4X:
Dim cmd As DBCommand = CreateCommand("insert_test",Array(Null,"absnckdmdm"))
 
Upvote 0

OliverA

Expert
Licensed User
Edit: don’t include autoincrement/autonumber fields in the insert query and see what happens.
 
Last edited:
Upvote 0

kgf

Member
Licensed User
Tried that first
B4X:
INSERT INTO tblTest (UserName) VALUES (?)
and no luck with
B4X:
sql.insert_test=INSERT INTO tblTest (UserNameID, UserName) VALUES (?,?)
Dim cmd As DBCommand = CreateCommand("insert_test",Array(Null,"absnckdmdm"))
 
Upvote 0

jimmyF

Active Member
Licensed User
I give up, sorry! I have just started using jRDC2 myself and I have already ported dozens of AJAX/PHP calls over to this and I am amazed at how well it works.
I hope you can find a solution.
 
Upvote 0

OliverA

Expert
Licensed User
Thanks for your suggestions, hopefully someone has had a similar issue
What are your connection properties in the config.properties file?
 
Upvote 0

kgf

Member
Licensed User
B4X:
DriverClass=net.ucanaccess.jdbc.UcanaccessDriver
JdbcUrl=jdbc:ucanaccess://D:/TestShare/test.accdb;memory=True
ServerPort=17178
 
Upvote 0

OliverA

Expert
Licensed User
When and how are you looking at your data after the insert?
 
Upvote 0
Top