B4J Question MySQL Insert Update of records

[TomDuncan]

Hi All,
I am just porting a program from Sqlite over to MySQL and I am having a few problems.
The reading of data is fine. My web server works like a dream reading from a MySQL database.
However, if I try and Insert or Update I get an error.

This code is fine with Sqlite

            Dim m As Map
            Dim i As Int = mt.Get("views")+1
            m.Initialize
            m.Put("views", i )
            Dim where As Map
            where.Initialize
            where.Put("id", Topic_ID)
            DBUtils.UpdateRecord2(sql1, "topic", m, where)

This is the log from UpdateRecord.

UpdateRecord: UPDATE [topic] SET [views]=? WHERE [id] = ? = (ArrayList) [71, 1]

DBUtils version 1.01 (B4J version)
However with MySQL I get this

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 '[topic] SET [views]=71 WHERE [id] = '1'' at line 1

I have tried not using a fiels called views and also made sure the the views value is sent as an integer value.

Seems strange that all reading is fine but writing to the database is not.
To update values I use a Delphi program and that is fine.

I have also tested this on a MySQl and a MariaDB databse.

The jar file is.

    #AdditionalJar: mysql-connector-java-5.1.37-bin

Tom

 

[udg]

Hi Tom,

some time ago I had a similar problem. I solved it modifying a sub in DBUtils as below:

Private Sub EscapeField(f As String) As String
   'Return "[" & f & "]"  'use for SQLite
   Return "`" & f & "`"  'use for MySql
End Sub

Eventually it will apply to your case too.
Note: if I recall it correctly there are a few other points in DBUtils that "suffer" the same problem but that don't refer the EscapeFiled sub, so you should fix them too.

udg

 

[TomDuncan]

Will test in the morning, thanks for that. Will check all areas and play.

 

[TomDuncan]

Works like a charm, thanks for that.
Also the Delete record had the same brackets.

 

[Harris]

Seems like we need a DBM function

Select SQLtype

Case SQLite
' bound brackets
Case MSSQL
' bound single quotes
Case MySQL
' bound whatever....

Else (otherwise)

' Use a standard that is common to all

End Select

ABMaterial attempts this - so many DB's - so many options.

Who shall build this routine (me, you , Erel, Tom)?

Thanks