B4J Question SQL Syntax

Declan

Well-Known Member
Licensed User
Longtime User
I am attempting to UPDATE a MySQL table with data from SigFox.
I am updating fields: "lastbeacon", "datetime" WHERE trolleyid="id"
I have the following code:

B4X:
            Dim ListOfMaps As List
            ListOfMaps.Initialize
            Dim m As Map
            m.Initialize
            m.Put("lastbeacon", beaconmac)
            m.Put("datetime", RxTimeText)
            ListOfMaps.Add(m)

            Dim WhereFields As Map
            WhereFields.Initialize
            WhereFields.Put("trolleyid", id)
            DBUtils.UpdateRecord2(sql1, "trolleypos", m, WhereFields)
            ListOfMaps.Clear

I get the following error:
B4X:
UpdateRecord: UPDATE [trolleypos] SET [lastbeacon]=?,[datetime]=? WHERE [trolleyid] = ?
Error occurred on line: 159 (DBUtils)
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[trolleypos] SET [lastbeacon]='e353382ca30c',[datetime]='08/09/2017 06:57:21' WH' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.Util.getInstance(Util.java:408)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:943)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2501)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
    at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1197)
    at anywheresoftware.b4j.objects.SQL.ExecNonQuery2(SQL.java:193)
    at b4j.example.dbutils._updaterecord2(dbutils.java:130)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:614)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:231)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:159)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:90)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:93)
    at anywheresoftware.b4a.debug.Debug.delegate(Debug.java:61)
    at b4j.example.sigfox._hnsconvertreturn(sigfox.java:242)
    at b4j.example.sigfox._handle(sigfox.java:225)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:614)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:231)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:159)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:90)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:93)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:77)
    at anywheresoftware.b4j.object.JServlet$Handle.run(JServlet.java:130)
    at anywheresoftware.b4a.keywords.SimpleMessageLoop.runMessageLoop(SimpleMessageLoop.java:30)
    at anywheresoftware.b4a.StandardBA.startMessageLoop(StandardBA.java:26)
    at anywheresoftware.b4a.ShellBA.startMessageLoop(ShellBA.java:114)
    at anywheresoftware.b4a.keywords.Common.StartMessageLoop(Common.java:146)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:303)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:159)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:90)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:93)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:77)
    at b4j.example.main.main(main.java:29)
 

DonManfred

Expert
Licensed User
Longtime User
I think @eps is right.

DBUtils are made to be used with SqLite.
You maybe need to adapt it to be compatible with MySQL.
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
Could use (Universal) DBUtils. You would still have to call
B4X:
DBUtils.SetEscapeChars("`","`")
after initializing the database to set the correct escape characters, otherwise it should be a drop in replacement. Please note that I've not caught up yet to @Erel's latest DBUtils update (July?) and therefore this may not be a drop in replacement.
 
Upvote 0

Declan

Well-Known Member
Licensed User
Longtime User
Strange.
This works:
B4X:
sql1.ExecNonQuery("UPDATE trolleypos SET lastbeacon= '"& beaconmac &"' WHERE trolleyid = '"& id &"'")
But this does not:
B4X:
sql1.ExecNonQuery2("INSERT INTO trolleyroute   trolleyid, lastbeacon, datetime VALUES ( '"& id &"', '"& beaconmac &"', '"& RxTimeText &")")
 
Upvote 0

DonManfred

Expert
Licensed User
Longtime User
But this does not
What is the errormessage?
Make sure that none of the fields is an Autoicrement field.
Maybe trolleyid... If so then ou are not allowed to set an id...

B4X:
sql1.ExecNonQuery2("INSERT INTO trolleyroute   lastbeacon, datetime VALUES ( '"& beaconmac &"', '"& RxTimeText &")")
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
Strange.
This works:
B4X:
sql1.ExecNonQuery("UPDATE trolleypos SET lastbeacon= '"& beaconmac &"' WHERE trolleyid = '"& id &"'")
But this does not:
B4X:
sql1.ExecNonQuery2("INSERT INTO trolleyroute   trolleyid, lastbeacon, datetime VALUES ( '"& id &"', '"& beaconmac &"', '"& RxTimeText &")")
1) Column names should be in parentheses.
2) You have a single quote missing after RxTimeText
B4X:
sql1.ExecNonQuery2("INSERT INTO trolleyroute  (trolleyid, lastbeacon, datetime) VALUES ( '"& id &"', '"& beaconmac &"', '"& RxTimeText &"')")

Question: Are you not using DBUTILS now and therefore switching to hand rolled SQL? DBUtils uses parameters in the background, making inserts a tad safer.
 
Upvote 0

Declan

Well-Known Member
Licensed User
Longtime User
@DM: Yes, Auto increment was a problem, but error still persisted.
I came right with this: (taken from RDC config I have for another project)
B4X:
sql1.ExecNonQuery("INSERT INTO trolleyroute VALUES ( '"& id &"', '"& beaconmac &"', '"& RxTimeText &"')")
 
Upvote 0
Top