Building a SQL statement issue

[JackieBlue1970]

Good afternoon folks.

I've been testing my application this morning. I ran into an issue on a different part of functionality that had been working find, even this morning. However, not it is displaying behavior that I cannot explain. It is pretty simple functionality. It simply creates a SQL statement calling a stored procedure on a database. The statement is built concatenating the user inputs from the interface.

Code: Select all

  put "CALL `insert_product` ('" & field "fldSupSKU" & "','" & field "fldJMSSKU" & "','" & field "fldUPC"  & "','" & label of button cboSupplier & "'," & field "fldBasePrice" & ","  & field "fldQtyBreak" & "," & field "fldBestPrice" & ",'" & label of button "cboFreight" & "')" into tSQL      
   answer ("SQL:" & tSQL)
   

This had been working fine. I was entering stuff, it was into the database, no problem. But now, it just drops pieces of the SQL, seeming randomly. In fact, using the answer dialog, it transfers some of the SQL over into the button! Result of above code:

[Klaus]

Hi Jackie,

please doublecheck the content of field "fldSupSKU"!
That seems to wreck the complete script!?


Best

Klaus

[JackieBlue1970]

Thank you Klaus. The contents of that filed are in the photo. I cleared it and added, and it worked. HOWEVER, there was nothing changed in the field. So I am a bit puzzled on how that would cause anything. The only thing I can concluded was using something off the clipboard that had hidden characters in it. Any ideas anyone?

[AxWald]

Hi,

So I am a bit puzzled on how that would cause anything. The only thing I can concluded was using something off the clipboard that had hidden characters in it. Any ideas anyone?

It's not rare that users copy something from somewhere (part numbers from a website?) & paste it into a field. Usually they don't care much what they actually copied ...

To avoid trouble, check your input! Always! A simple way to do this is to have something like this in every input field:

Code: Select all

on closeField
   if (CR is in me) then                --  no CR in me!
      answer error "Too many lines in " & the name of me & "! (Corrected)" \
            & CR & CR & "Input: " & quote & me & quote titled "Input problem!"
      put line 1 of me into me
   end if
   
   if ("," is in me) then                                 --  1,5 -> 1.5
      answer error "Use '.' for decimals in " & the name of me & "! (Repaired)" \
            & CR & CR & "Input: " & quote & me & quote titled "Input problem!"
      replace comma with "." in me
   end if
   
   if (me is not a number) then                           --  only numbers!
      answer error "Not a number in " & the name of me & "! (Content rejected)" \
            & CR & CR & "Input: " & quote & me & quote titled "Input problem!"
      put empty into me
   end if
end closeField

This is a quick & dirty version for numerics. You may want to filter for " ' & ; ´ etc. as well ...

Have fun!

[JackieBlue1970]

Thank you. I am just copying from a spreadsheet cell and something is in there that LiveCode picks up. I suspect an escape code. Although I am puzzled as this doesn’t happen in SQL and text editors. I guess I will write some functions to check it. I’m a bit puzzled why LC doesn’t include better string manipulation functions like trim, left, etc. Obviously you can write you’re own but these are usually standard in a high level language.

[bogs]

I’m a bit puzzled why LC doesn’t include better string manipulation functions like trim, left, etc.

While it might not include trim per se, it does have the ability to do this sort of work quite easily in a number of ways. Perhaps this thread will be of some use to you -
https://forums.livecode.com/viewtopic.php?t=663

[JackieBlue1970]

Thanks. I saw the post. As I said though I can write the function myself - in a variety of languages, it would be nice if it was built in. String manipulation is a standard function for most languages. That was my only point. Thanks. Jack

[bogs]

Yah, coming from other languages myself, I well understand what your saying. Some of the languages I've used create stings by drawing them as graphic objects, for instance, which I find incredibly handy for a number of reasons.

However, if every language was just like every other language, there wouldn't be much point to using language x I guess. This language has features I haven't seen elsewhere, and the ones I come across missing are usually made up pretty easily. But, I do get what your saying for sure.