LiveCode Forums.

I have succesfully INSERTed and UPDATEd to SQL tables from my livecode stacks.
With a table I am using now, I have two 'text' columns which don't have a fixed length.
When I put together the SQL statements the text in these two fields is truncated to 30 characters each time.

The following is the statement and the fields tnotes and tactivity are the fields that are being shortened to 30 characters as soon as the tCmd statement is put together.

put "INSERT INTO " & gTableName & \
" (" & quote & "ID" & quote & ", " & quote & "requester_user_id" & quote & ", " & quote & "assignee_user_id" \
& quote & ", " & quote & "notes" & quote & ", " & quote & "activity" & quote & ") " & \
merge("VALUES ('[[tticket]]', '[[trequester_user_id]]', '[[tassignee_user_id]]', '[[tnotes]]', '[[tactivity]]')") into tCmd

I have used the same sort of statement for another table that had one of these 'undefined length' text fields and it works fine.
Any help would be appreciated. Thanks.

Hi

no idea, if it works in other tables, but shouldn't the MERGE part read:
...
merge("VALUES ('[[tticket]]', '[[trequester_user_id]]', '[[tassignee_user_id]]', '[[the text of fld "tnotes"]]', '[[the text of fld "tactivity"]]')") into tCmd
...
Or did you put the content of these fields already into variables with the same name?


Best

Klaus

Hi,

Klaus has a good point. It isn't clear whether you're using field names or variable names.

Usually, one wouldn't put MySQL column names in standard quotation marks. Column names don't need quotation marks at all and if you use standard quotation marks then MySQL thinks you mean a value rather than a column name. Perhaps, this is a reason why your syntax doesn't work correctly. If you want to use quotation marks around column names, than use ` instead of ".

Do any of your fields/variables contain characters that need to be escaped?

Kind regards,

Mark

As mentioned by the others, first, you shouldn't need (and don't want) quotes around the field names of your table, think the other part with the merge should work fine with the '[[variablename]]' though of course any field name that is a numeric type shouldn't have the single tick quotes so the first one [[tticket]] that goes into the ID column doesn't need them unless you're using a non-numeric id of some type. Possibly the same with the user id etc.

My best guess (without changing the ' ' around things that might be numeric) is this..
As for the 30 char truncation, (if this doesn't help solve the issue) where are you getting the text from? Is it possible there is a null in there somewhere (like at char 31?) that could be causing the issue? To check, maybe set up a field, type in a few lines and use it as the source for the text in question and see if the issue goes away. If it does it is probably an issue with the text being inserted.