B4J Question DBUTILS and mysql problems

[moore_it]

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

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

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

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

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

it's work fine

Why ?
Where i wrong ?

Thanks in advice

 

[udg]

Hi,
one thing to change is how you prepare the statement. It should be in the order:

 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)

 

[moore_it]

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.

INSERT INTO `dafare` ( `codice`,`nota`,`urgente` ) VALUES ( '0000011111','Test',1 )

 

[moore_it]

this is the rec map values

 

[Spavlyuk]

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

 

[moore_it]

Good idea, try now !
Thanks

 

[udg]

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.

 

[moore_it]

thanks @udg i go to see the OliverA solutions.