MySQL Error

[DavJans]

I cant seem to figure this one out. It tells me that there is a problem with my syntax near ')' at line 1, it is talking about this line,
put "INSERT INTO " & tTableName & " (" & tFields & ") VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10)" into tSQL --specifically this, :10)"
I know that because if I add a ; it complains about ');'
But I dont get it, I use the same syntax in another script and it works just fine.

Code: Select all

      case movequantity < tempquantity
         ask "Where are you moving it to?"
         if it = "" then
            BREAK
         else
         put it into tLOC
         put tempquantity - movequantity into newQT
         --Change quantity in current record
         put "UPDATE yard SET quantity='" & newQT & "' WHERE id='" & tempid &"'" into tSQL
         revExecuteSQL gConnectionID, tSQL
         --Add new record with moved quantity
         put "quantity, shape, dimension, length, grade, country, heatn, pon, supplier, loc" into tFields
         put "yard" into tTableName
         put "INSERT INTO " & tTableName & " (" & tFields & ") VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10)" into tSQL
         revExecuteSQL gConnectionID, tSQL, "movequantity", "tempshape", "tempdimension", "templength", "tempgrade", "tempcountry", "tempheatn", "temppon", "tempsupplier", "tLOC"
    if the result is a number then
        answer info "Move Successfull"
    else
       answer error "There was a problem adding the record to the database:" & cr & the result
       put "UPDATE yard SET quantity='" & tempquantity & "' WHERE id='" & tempid &"'" into tSQL
       revExecuteSQL gConnectionID, tSQL
       answer "Move Unsuccessfull"
    end if
         BREAK
         end if

Here is the lines from another script that works,

Code: Select all

   put "INSERT INTO " & tTableName & " (" & tFields & ") VALUES (:1, :2)" into tSQL
   revExecuteSQL gConnectionID, tSQL, "tJobN", "tCertN"

[bangkok]

What is the error message from MySQL ?

Are you sure the data type of "loc" is compatible with your variable "tLOC" ?

In anycase, the best solution is to give us the "finished" SQL query, it's much easier to debug;

Add to your script :

Code: Select all

put tSQL

[DavJans]

Yes the data type (VARCHAR45)is compatible with the variable (1B or 10A) Loc is just a number and a letter for the location on a grid

MySQL error is:
There was a problem adding the record to the database:
You have an error in your SQL suntax; check the manual that
corresponds to your MySQL server version for the right syntax
to use near ')' at line 1

INSERT INTO yard (quantity, shape, dimension, length, grade, country, heatn, pon, supplier, loc) VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10)

[bangkok]

OK, I guess the easiest way to find out is to drop the ":variable" system, and to use the classic way, by putting your vars directly into the SQL query.

[DavJans]

You say that like you think I know what I'm doing

one question I have is there is an 11th variable id, this one is set up as Primary Key, Not Null Unique and Auto Increment. Before I tried manually creating a record like I am now, I imported 3000 records from another program. is it possible that MySQL cant figure out where to start the Auto Increment from? and if so, is there a way to fix that?

[bangkok]

You should proceed logically :
-your table structure seems to be fine no problem with ID autoincrement): because you managed to do INSERT, but with less columns, right ?

-impossible to see the exact query you send to MySQL, because of Livecode ":variable" system

-column data type and content are ok for variable 10

-so the problem could come from other variables

-in any case, the best way is to see clearly what you send to MySQL. Ergo : compose your INSERT query the classic way :

Code: Select all

 put "INSERT INTO yard (" & tFields & ") VALUES ('"&movequantity&"','"&tempshape&"','"&tempdimension&"','"&templength&"','"&tempgrade&"','"&tempcountry&"','"&tempheatn&"','"&temppon&"','"&tempsupplier&"','"&tLOC&"')" into tSQL
put tSQL
revExecuteSQL gConnectionID, tSQL

Furthermore, you can then try this query into a SQL editor (like HeidiSQL), for further debuging

Possible issue :
-data type compatibility problem with one of Livecode variable content
-or "default" value problem : if you put an empty value into a SQL column without default value, then it would cause an error

[DavJans]

Cool, OK all helpful info and I do get what you suggest trying, I think with that I will get to the bottom of it, one thing to note all of the variable content is pulled from another record in the same table, so I don't think it is possible that the data isn't compatible with the database data type, but then again what do I really know


btw in your script
put tSQL
what does that do exactly...

[DavJans]

INSERT INTO yard (quantity, shape, dimension, length, grade, country, heatn, pon, supplier, loc) VALUES ('3','C','6 x 8.2','40'-0','A36','','PL13204810','9800-14421','NUCOR STEEL-PLYMOUTH DIV.
','1B')


I'm willing to bet the problem is the ' in 40'-0

If that is the problem should I replace it with ft or is there a way to structure the syntax to allow me to insert info like that? I had a problem with commas a few months ago, I didnt know how to fix it so in my script I replaced commas with | and changed | back into a comma after I gathered data.

[bangkok]

INSERT INTO yard (quantity, shape, dimension, length, grade, country, heatn, pon, supplier, loc) VALUES ('3','C','6 x 8.2','40'-0','A36','','PL13204810','9800-14421','NUCOR STEEL-PLYMOUTH DIV.
','1B')
.

Bingo. As I told you, now the error is obvious :

you can not have :
'40'-0'

There is a single quote in excess. So MySQL doesnt understand : one column or 2? And if 2, then a single quote is missing etc.

It's important to "clean" your data before you send them to MySQL.

2 classic issues :
-single quote
> replace them with \'

Code: Select all

replace "'" with "\'" in templength

-CR, that will then be interpreted as new row by LiveCode, when you do a SELECT
> replace them with \N

Or other strategy. Play with line delimiter

Code: Select all

replace CR with "|" in tData

and then when you do your SELECT,

Code: Select all

   put  revDataFromQuery(,"|" , dbIDMBis, dbSQL) into tData

I'm talking about it because I think you might encounter this issue with :
'NUCOR STEEL-PLYMOUTH DIV.
','1B')

LiveCode seems to display this on 2 lines... Therefore you might have a CR after DIV.

Anyway. Good luck.

[DavJans]

Or other strategy. Play with line delimiter
CODE: SELECT ALL
replace CR with "|" in tData

and then when you do your SELECT,
CODE: SELECT ALL
put revDataFromQuery(,"|" , dbIDMBis, dbSQL) into tData


I'm talking about it because I think you might encounter this issue with :
'NUCOR STEEL-PLYMOUTH DIV.
','1B')

LiveCode seems to display this on 2 lines... Therefore you might have a CR after DIV.

I think this one is because my field that I copied and pasted the line from wasn't big enough, but still very good information, you have made me that much smarter.

Thank you