B4J Question SQL Syntax

[Declan]

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:

            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:

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)

 

[rboeck]

You see datetime is made blue - its a reserved word in sql: https://www.drupal.org/docs/develop/coding-standards/list-of-sql-reserved-words
Maybe it works if you write [datetime], but better change the field name.

 

[Declan]

Thanks, but that was not the problem

 

[eps]

Hmm... using this https://www.piliapp.com/mysql-syntax-check/ it doesn't seem to like the square brackets... where did they come from? Does DBUtils 'do' mysql? I don't use it myself.

 

[DonManfred]

I think @eps is right.

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

 

[keirS]

MySQL uses the backtick (`) character to escape reserved words.

 

[OliverA]

Could use (Universal) DBUtils. You would still have to call

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.

 

[Declan]

Strange.
This works:

sql1.ExecNonQuery("UPDATE trolleypos SET lastbeacon= '"& beaconmac &"' WHERE trolleyid = '"& id &"'")

But this does not:

sql1.ExecNonQuery2("INSERT INTO trolleyroute   trolleyid, lastbeacon, datetime VALUES ( '"& id &"', '"& beaconmac &"', '"& RxTimeText &")")

 

[DonManfred]

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

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

 

[OliverA]

Strange.
This works:

sql1.ExecNonQuery("UPDATE trolleypos SET lastbeacon= '"& beaconmac &"' WHERE trolleyid = '"& id &"'")

But this does not:

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

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.

 

[Declan]

@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)

sql1.ExecNonQuery("INSERT INTO trolleyroute VALUES ( '"& id &"', '"& beaconmac &"', '"& RxTimeText &"')")

 

[Erel]

Why aren't you use ExecNonQuery2 with a parameterized query?