B4J Question MySQL syntax Error

[Declan]

I receive the following error when attempting to InsertMaps:

(MySQLSyntaxErrorException) 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 '[push_users] ([id], [last_access]) VALUES ('358726061334668', 1488201837725)' at line 1

This is my SQL statement:

DBUtils.InsertMaps(PushDB, "push_users", Array As Object(CreateMap("id": Id, "last_access": DateTime.Now)))

I have also tried the following, but receive the same error:

        Dim ListOfMaps As List
        ListOfMaps.Initialize
        ListOfMaps.Add(CreateMap("id":Id, "last_access": DateTime.Now))
      
        DBUtils.InsertMaps(PushDB, "push_users", ListOfMaps)

My MySQL/Java Connector:

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

 

[Erel]

DBUtils is only compatible with SQLite databases. You will need to make some changes if you want it to work with MySQL.

 

[Declan]

Thanks, is there a link to the changes that I need to make?

 

[Erel]

I don't think that anyone has converted it to MySQL syntax.

 

[udg]

I don't think that anyone has converted it to MySQL syntax.

I did it
There were minor changes to do. Unfortunately I will be away from my dev pc untill tomorrow evening, but if @Erel allows it I could post my version "as is" in this thread or a cleaner and commented one in the Libraries section (this will take some time more just to revise and comment).
Oh, maybe I should check the latest version available too..eheh

udg

 

[Erel]

but if @Erel allows it I could post my version "as is" in this thread or a cleaner and commented one in the Libraries section (this will take some time more just to revise and comment).

Sure. It will be useful.

 

[udg]

Hi all,
as promised I attach to this post a copy of DBUtils that time ago I modified in order to work with MySql.
I doubt it is the most recent one and a quick look at it showed some code commented out (I can't recall why).

Anyway, it is usable as is since I'm successfully using it in a working project but I hope to soon have the time to apply those same minor modifications to the most recent version of the official DBUtils module and publish it under a name like DBUMySql or similar.

udg

 

[Harris]

What WOULD be nice is a method / class to resolve syntax based on major DB types in use. MySQL, SQLite, MSSQL, PostGres and others.
Like @udg stated: There were minor changes... yet all have subtle differences. Why they are not all standardized is beyond me.

Perhaps we can call on the community to create a class / open project (based on their expertise of DB) that may address these issues.
Most of us who deal with this know the issues, particularly when we mix and match (ie. Android SQLite - backend MySQL or MSSQL).
Frustrating and annoying (needless) to say the least.

Thanks