Page 1 of 1
MySQL Error
Posted: Thu May 22, 2014 5:29 pm
by 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"
Re: MySQL Error
Posted: Thu May 22, 2014 7:48 pm
by 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 :
Re: MySQL Error
Posted: Thu May 22, 2014 9:30 pm
by 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)
Re: MySQL Error
Posted: Thu May 22, 2014 9:40 pm
by 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.
Re: MySQL Error
Posted: Thu May 22, 2014 9:57 pm
by 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?
Re: MySQL Error
Posted: Thu May 22, 2014 10:17 pm
by 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
Re: MySQL Error
Posted: Thu May 22, 2014 10:48 pm
by 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...
Re: MySQL Error
Posted: Thu May 22, 2014 11:00 pm
by 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.
Re: MySQL Error
Posted: Fri May 23, 2014 7:18 am
by bangkok
DavJans wrote: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
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.
Re: MySQL Error
Posted: Fri May 23, 2014 4:48 pm
by 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