Android Question JdbcSQL UPDATE & INSERT to MySQL

[atiaust]

Hi All,
I am trying to get a B4A app working with a MySQL database running on a Linux server.
I have the jDBC server running without problem but I am a little unsure on some of the MySQL commands for the config.properties & B4A app commands.

The MySQL table is "stocktake" and the fields are "stockID" as Int, "partno" as string, "count" as double, "countDate" as MYSQL DateTime & "location" as string

Sql statements in the config.properties:

#SQL COMMANDS
sql.select_items=select * from inventory
sql.insert_count=INSERT INTO stocktake (stockID, partno, count, countDate, location) VALUES(?, ?, ?, ?, ?)
sql.check_count=SELECT COUNT(*) FROM stocktake WHERE stockID = "&stockID &""
sql.update_count=UPDATE stocktake SET
#UPDATE stocktake SET count=?, countdate=?, location=? WHERE stockID=?", Array As Object(u(1), u(2), u(3), u(0)))

Code from B4A app:

Sub InsertCount
    Dim cmd As DBCommand = CreateCommand("insert_count", Array("stockID","partno", "count", "CountDateTime", "binLocation", Null))
    Dim j As HttpJob = CreateRequest.ExecuteBatch(Array(cmd), Null)
    Wait For(j) JobDone(j As HttpJob)
    If j.Success Then
        Log("Inserted successfully!")
    End If
    j.Release
End Sub

Sub updateCount
    Dim cmd As DBCommand = CreateCommand("update_count", Array("stockID", "count", "CountDateTime", "binLocation", Null))
    Dim j As HttpJob = CreateRequest.ExecuteBatch(Array(cmd), Null)
    Wait For(j) JobDone(j As HttpJob)
    If j.Success Then
        Log("Inserted successfully!")
    End If
    j.Release
End Sub

Can someone please help with the correct code for the insert & update methods.

Thanks - any help gratefully appreciated.

 

[Chris Guanzon]

You don't need to include the (#UPDATE).

you can try this

#SQL COMMANDS
sql.select_items=select * from inventory
sql.insert_count=INSERT INTO stocktake (stockID, partno, count, countDate, location) VALUES(?, ?, ?, ?, ?)
sql.check_count=SELECT COUNT(*) FROM stocktake WHERE stockID = "&stockID &""
sql.update_count=UPDATE stocktake SET count=?, countdate=?, location=? WHERE stockID=?"

 

[atiaust]

Thanks Chris,

Thank you for your reply and sorry for the delay.

I have encountered further problems with the JRDC connection which I need to fix first before I get back to the MySQL commands.

I will test your suggestions soon.