mysql database error on insert (SOLVED)

[vedus]

i am using mysql database,one is on dedicated server and the other one is shared.
in both databases i get the same error
the stack is simple populate the data from the fields to the database
i am using for this test get revOpenDatabase and the (DBLIB)library http://andregarzia.com/aux/dblibguide/ library from andre.
here is the code i use with get revOpenDatabase in the mysql save button :

Code: Select all

global gConnectionID
on mouseUp
  put "katastimata" into mytable
  
      put fld "lat" into zlat
      put fld "long"into zlong
      put fld "katigoria"into zkatigoria
      put fld "titlos"into ztitlos
      put fld "eponimia"into zeponimia
      put fld "perioxi"into zperioxi
      put fld "address"into zaddress
      put fld "tk"into ztk
      put fld "phone1"into zphone1
      put fld "phone2"into zphone2
      put fld "web"into zweb
      put fld "mail"into zmail
  put fld "faceb"into zfaceb
  ## Now database access should work as exspected:
      get revOpenDatabase("mysql", "myIP", "store", user, pass)
  if it is a number then
    dbSetDefaultConnectionID it
    put it into gConnectionID
put "INSERT INTO" && mytable && "(lat, long, katigoria, titlos, eponimia, perioxi, address, tk, phone1, phone2, web,mail,faceb)" && "VALUES (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,13,) " into tSQL

revExecuteSQL gConnectionID, tSQL, zlat, zlong, zkatigoria, ztitlos, zeponimia, zperioxi, zaddress, ztk, zphone1, zphone2, zweb,zmail,zfaceb
put the result into tRes

   if tRes is not a number then

      answer error "There was a problem saving this record: " & tRes as sheet
   end if
end if
end mouseUp

and here is the code i use with andre library in the save andre button

Code: Select all

on mouseUp
  put dbGet("katastimata") into myRec
  put dbCardToArray("katastimata") into myRec
  if field "id" is empty then
    get dbInsert ("katastimata",myRec)
    if it is a number then
      answer it &&  "New Record Saved"
    else
      answer error it
    end if
  end if
end mouseUp

to show the records from the database to the datagrid is working.
The data in the fields is on the greek language and the databases are utf-8
anyone can help on this ?

[bangkok]

Easy.

Do not use (at the beginning) the form with parameters. Use instead

Code: Select all

put "INSERT INTO katastimata (lat, long, katigoria, titlos, eponimia, perioxi, address, tk, phone1, phone2, web,mail,faceb)" && "VALUES ('"&zlat&"',"'&zkatigoria&"',..........)" into tSQL
put tSQL
revExecuteSQL gConnectionID, tSQL

Why ? In order to debug the query.

Your problem most probably comes from a var type issue : you try to insert a string into a numeric column, or vice versa.

Or a problem of special character (like single quote for instance) or even carriage return within the data.

Anyway. With the "readable" query it will be easy to find the (SQL) issue.

[vedus]

Hi bangkok.
1:) Database format columns is all in "Text" format(database structure checked many times)
2:) the data i fill in the livecode fields is simple text like (Name) No cr or anything else
3:) The data is on Greek language,but i don't think this is problem,because the same data i save it in sqlite file.
i have try with

Code: Select all

put "INSERT INTO katastimata (lat, long, katigoria, titlos, eponimia, perioxi, address, tk, phone1, phone2, web,mail,faceb)" && "VALUES ('"&zlat&"',"'&zkatigoria&"',..........)" into tSQL

the results is the same error.

[bangkok]

At least, gives us the value of tSQL. With many columns, it's easy to forget a single quote for instance.

If the query is OK from a syntax point of view, then you'll need to check columns definition (type, length) and default value.

[vedus]

At least, gives us the value of tSQL. With many columns, it's easy to forget a single quote for instance.

If the query is OK from a syntax point of view, then you'll need to check columns definition (type, length) and default value.

this is what i get from tSQL

Code: Select all

INSERT INTO katastimata (lat, long, katigoria, titlos, eponimia, perioxi, address, tk, phone1, phone2, web,mail,faceb) VALUES ('johndoe','johndoe','Music Club','Music Club','johndoe','johndoe','johndoe','johndoe','johndoe','johndoe','johndoe','johndoe','johndoe')

and in the photo the structure of database

[bangkok]

Code: Select all

INSERT INTO katastimata (lat, long, katigoria, titlos, eponimia, perioxi, address, tk, phone1, phone2, web,mail,faceb) VALUES ('johndoe','johndoe','Music Club','Music Club','johndoe','johndoe','johndoe','johndoe','johndoe','johndoe','johndoe','johndoe','johndoe')

and in the photo the structure of database

Screen Shot 2014-11-04 at 21.03.21.png

Both, query and structure, look good.

You still receive an error with this insert query ?

Which version of Livecode do you use ?

[vedus]

i am using LC 7.0.1 and 6.6.4 both of them have the same error..
i have include the test file as attach
i will have my server open so u can see it if you want

[Simon]

Hi vedus,

Phew! That was fun!

Code: Select all

    put "`lat`,`long`,`katigoria`,`titlos`,`eponimia`,`perioxi`,`address`,`tk`,`phone1`,`phone2`,`web`,`mail`,`faceb`" into tFields
    put "INSERT INTO `katastimata` ("& tFields &")VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12,:13) " into tSQL

Notice where all the back ticks go.

Crazy DB

Simon

[bangkok]

OH MY GOD !

"long" is a reserved word !


This is why using backtick is the solution, like Simon said.

Sorry we haven't seen that earlier....

Or just remove/rename this column.

I removed the column "long" (in your stack) and now it's working as it should.

Code: Select all

put "INSERT INTO katastimata (lat,katigoria,titlos,eponimia,perioxi,address,tk,phone1,phone2,web,mail,faceb) VALUES ('"&zlat&"','"&zkatigoria&"','"&ztitlos&"','"&zeponimia&"','"&zperioxi&"','"&zaddress&"','"&ztk&"','"&zphone1&"','"&zphone2&"','"&zweb&"','"&zmail&"','"&zfaceb&"')" into tSQL
         revExecuteSQL gConnectionID, tSQL

[vedus]

thank you bangkok and Simon,really appreciate your help
I did not know that this word is "reserved"
i have rename column & fields and anything working like charm