Building a SQL statement issue

LiveCode is the premier environment for creating multi-platform solutions for all major operating systems - Windows, Mac OS X, Linux, the Web, Server environments and Mobile platforms. Brand new to LiveCode? Welcome!

Moderators: Klaus, FourthWorld, heatherlaine, kevinmiller, robinmiller

Post Reply
JackieBlue1970
Posts: 64
Joined: Thu Jan 16, 2020 10:28 pm
Location: Max Meadows, VA USA

Building a SQL statement issue

Post by JackieBlue1970 » Mon Jul 20, 2020 5:48 pm

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:

Image

Klaus
Posts: 12216
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: Building a SQL statement issue

Post by Klaus » Mon Jul 20, 2020 6:15 pm

Hi Jackie,

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


Best

Klaus

JackieBlue1970
Posts: 64
Joined: Thu Jan 16, 2020 10:28 pm
Location: Max Meadows, VA USA

Re: Building a SQL statement issue

Post by JackieBlue1970 » Mon Jul 20, 2020 6:49 pm

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
Posts: 493
Joined: Thu Mar 06, 2014 2:57 pm

Re: Building a SQL statement issue

Post by AxWald » Tue Jul 21, 2020 9:51 am

Hi,
JackieBlue1970 wrote:
Mon Jul 20, 2020 6:49 pm
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!
Livecode programming until the cat hits the fan ...

JackieBlue1970
Posts: 64
Joined: Thu Jan 16, 2020 10:28 pm
Location: Max Meadows, VA USA

Re: Building a SQL statement issue

Post by JackieBlue1970 » Fri Jul 24, 2020 1:53 am

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
Posts: 5152
Joined: Sat Feb 25, 2017 10:45 pm

Re: Building a SQL statement issue

Post by bogs » Fri Jul 24, 2020 3:24 am

JackieBlue1970 wrote:
Fri Jul 24, 2020 1:53 am
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
Image

JackieBlue1970
Posts: 64
Joined: Thu Jan 16, 2020 10:28 pm
Location: Max Meadows, VA USA

Re: Building a SQL statement issue

Post by JackieBlue1970 » Fri Jul 24, 2020 8:26 pm

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
Posts: 5152
Joined: Sat Feb 25, 2017 10:45 pm

Re: Building a SQL statement issue

Post by bogs » Fri Jul 24, 2020 8:56 pm

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.
Image

Post Reply

Return to “Getting Started with LiveCode - Experienced Developers”