B4J Question DBUTILS and mysql problems

moore_it

Well-Known Member
Licensed User
Longtime User
Hi all,

i've a problem with DBUTILS library when use insertmap or deleterecord (use 2.11 version with mysql-connector-java-5.1.44-bin.jar).

for connection i do that

B4X:
Globals.DBData.Initialize2("com.mysql.jdbc.Driver","jdbc:mysql://"&m.Get("ip")&":"&m.Get("port")&"/"&m.Get("nomedb")&"?characterEncoding=UTF8&useSSL=False",m.get("user"),m.get("password"))

have this table
B4X:
Field    Type              Collation          Null    Key     Default  Extra           Privileges                       Comment 
-------  ----------------  -----------------  ------  ------  -------  --------------  -------------------------------  ---------
codice   char(10)          latin1_swedish_ci  YES             (NULL)                   select,insert,update,references           
nota     text              latin1_swedish_ci  YES             (NULL)                   select,insert,update,references           
chiave   int(10) unsigned  (NULL)             NO      PRI     (NULL)   auto_increment  select,insert,update,references           
urgente  tinyint(1)        (NULL)             YES             (NULL)                   select,insert,update,references

B4X:
Private Sub SaveToDo(p As Pane)
    Dim rec As Map
    rec.Initialize
    For Each n As Node In p.GetAllViewsRecursive
        If n Is TextField Then
            Dim tf As TextField = n
            If tf.Tag = "0" Then
                rec.Put("codice",Globals.SaveNumber(tf.Text,0))   
            else if tf.Tag = "1" Then
                rec.Put("nota",tf.Text)
            End If
        End If
        If n Is Button Then
            Dim bt As Button = n
            If bt.Tag = "03" Then
                rec.Put("urgente",0)
            else if bt.Tag = "13" Then
                rec.Put("urgente",1)
            End If
        End If
    Next
    Dim recs As List
    recs.Initialize
    recs.Add(rec)
    DBUtils.InsertMaps(Globals.DBData,"dafare",recs)
End Sub

Dbutils return this message

(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 '[dafare] ([codice], [nota], [urgente]) VALUES ('0000014000', 'Bla Bla Bla', 0)' at line 1

but if i use this sql

B4X:
INSERT INTO `dafare` ( `codice`,`nota`,`urgente` ) VALUES ( '0000014000','Bla Bla Bla',0 )

it's work fine

Why ?
Where i wrong ?

Thanks in advice
 

udg

Expert
Licensed User
Longtime User
Hi,
one thing to change is how you prepare the statement. It should be in the order:
B4X:
 Dim recs As List
 recs.Initialize
For each...
    Dim rec As Map
    rec.Initialize
   rec.Put...
    recs.add(rec)
Next
And you should anticipate what happens whn the IFs give a false result to avoid a partially valid record

As for the error, I can't look at DbUtils code right now but you could try to add the "missing" field, setting its value to Null (so the autoincrement will take place)
 
Upvote 0

moore_it

Well-Known Member
Licensed User
Longtime User
Hi UDG,
thnks for reply ,

the order of the fields is right (i use the tree view in designer to order the fields ) and the field 'chiave' is not necessary to set to null infact if i use this
sql command it's work.

B4X:
INSERT INTO `dafare` ( `codice`,`nota`,`urgente` ) VALUES ( '0000011111','Test',1 )
 
Upvote 0

moore_it

Well-Known Member
Licensed User
Longtime User
this is the rec map values

1645189994693.png
 
Upvote 0

Spavlyuk

Member
Licensed User
You could extract DBUtils and copy InsertMaps into your project to debug it. Seeing the generated query might be of help.
 
Upvote 0

udg

Expert
Licensed User
Longtime User
Hi @moore_it
I hope you solved the problem yourself while looking at DBUtils code.
I didn't but I recall an old post where I suggested how to adapt DBUtils to work with both SqLite and MySql. One of the difference layed in the use of square brackets instead of apices for items in the query.
While writing this message I see that there's a thread (in the section Similar Threads below) from @OliverA about a universal DBUtils. Maybe worth reading it.
 
Upvote 0
Top