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]

I think there is more to the warning, meaning you should see more detail about the warning. You may need to escape a column name that is using a reserved word. See https://sourceforge.net/p/ucanaccess/discussion/help/thread/ec148bf6/

 

[jimmyF]

Maybe this:

sql.insert_team=INSERT INTO tblListCurrentTeam (ID, UserName) VALUES (?,?)

and

Dim cmd As DBCommand = CreateCommand("insert_team",Array(Null, "test name"))

I haven't tested it myself though.

 

[kgf]

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

 

[jimmyF]

Try this:

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.

 

[kgf]

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.

 

[jimmyF]

Did you try the line of code in your config file?

I don't believe yours was correct .

 

[kgf]

Still the same result I'm afraid, no entries but logs all good.

 

[jimmyF]

Did you actually try it?
Your code was incorrect.
Your code:

sql.insert_team=INSERT INTO tblListCurrentTeam (UserNameID, UserName) VALUES (?,?)

My Code:

sql.insert_team=INSERT INTO tblListCurrentTeam (UserNameID, UserName) VALUES (null,?)

 

[kgf]

My code, using different names now with new test database

sql.insert_test=INSERT INTO tblTest (UserNameID, UserName) VALUES (Null,?)

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

 

[jimmyF]

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

 

[kgf]

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

 

[jimmyF]

sql.insert_test=INSERT INTO tblTest (UserNameID, UserName) VALUES (?,?)

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

 

[OliverA]

Edit: don’t include autoincrement/autonumber fields in the insert query and see what happens.

 

[kgf]

Tried that first

INSERT INTO tblTest (UserName) VALUES (?)

and no luck with

sql.insert_test=INSERT INTO tblTest (UserNameID, UserName) VALUES (?,?)
Dim cmd As DBCommand = CreateCommand("insert_test",Array(Null,"absnckdmdm"))

 

[jimmyF]

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.

 

[kgf]

Thanks for your suggestions, hopefully someone has had a similar issue

 

[OliverA]

Thanks for your suggestions, hopefully someone has had a similar issue

What are your connection properties in the config.properties file?

 

[kgf]

DriverClass=net.ucanaccess.jdbc.UcanaccessDriver
JdbcUrl=jdbc:ucanaccess://D:/TestShare/test.accdb;memory=True
ServerPort=17178

 

[OliverA]

When and how are you looking at your data after the insert?