B4J Question MySQL syntax Error

Declan · Feb 27, 2017

I receive the following error when attempting to InsertMaps:

B4X:

(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:

B4X:

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:

B4X:

        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:

B4X:

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

Erel · Feb 28, 2017

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

Declan · Feb 28, 2017

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

Erel · Feb 28, 2017

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

udg · Feb 28, 2017

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 · Feb 28, 2017

udg said:
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 · Mar 1, 2017

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 · Mar 2, 2017

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

B4J Question MySQL syntax Error

Declan

Well-Known Member

Erel

B4X founder

Declan

Well-Known Member

Erel

B4X founder

udg

Expert

Erel

B4X founder

udg

Expert

Attachments

Harris

Expert

Similar Threads

B4J Question MySQL syntax Error

Well-Known Member

B4X founder

Well-Known Member

B4X founder

Expert

B4X founder

Expert

Attachments

Expert

Similar Threads

Privacy & Transparency

Privacy & Transparency