Concatenation issue

[JackieBlue1970]

Greeting and happy Friday.

I have an issue where when I build a SQL statement (yes, it should be a parameterized stored proc but this is a test) in Live Code. As most know, when searching a varchar value in a database you have to have the value surrounded by apostrophes (single quotes as some call it). I have tried this in both Live Code Community and Indy 9.51 and the same issue occurs. Here is the code:


if field "fieldUPC" is not empty then
put field("fieldUPC") & "'" into sQL
answer sQL <--just to peek
put "SELECT * FROM inventory_orders.pd_supplier_product where UPC='" & sQL into tSQL
answer tSQL <--Just to peek
put testQuery(tSQL) into rtData

Sample "fieldUPC" value: 656295060238

The first time you start live code and run, it will work fine. After that, until you restart LC, it will not add the second apostrophe at the end of the string. I find if I put a regular string (say an alpha-numeric instead of just numeric type entry), it will work fine. It seems to be a typing issue (in this untyped language). Has anyone else run into this? I could pass the value as a numeric and then CAST() the value to a string. But this seems like this is a bug in LC. Suggestions? TIA.

[FourthWorld]

(yes, it should be a parameterized stored proc but this is a test)

You are now my favorite poster here.

Should this:

Code: Select all

put "SELECT * FROM inventory_orders.pd_supplier_product where UPC='" & sQL into tSQL

be:

Code: Select all

put "SELECT * FROM inventory_orders.pd_supplier_product where UPC='" & sQL &"'" into tSQL

? (note the closing single-quote, quoted for LC-friendly concatenation).

[SparkOut]

But sQL should already have the closing quote from the earlier line where it is populated

Code: Select all

put field("fieldUPC") & "'" into sQL

The parentheses might be forcing some strange evaluation of the field name as if it were a variable but I don't really understand what's wrong. Try

Code: Select all

put field "fieldUPC" & "'" into sQL

or add the closing quote later as per Richard.

[AxWald]

Hi JackieBlue,

The parentheses might be forcing some strange evaluation of the field name [...]

This ( field("fieldUPC") ) definitively looks fishy, but in a short test gives the correct result. I'd rather not do it, you'll never know.

But it's quite easy to mess up the concatenation, especially with quotes. To avoid it, there's a technique:

Code: Select all

on mouseUp
   put fld "UPC_fld" into myUPC               --  at first, fld -> variable
   if myUPC is empty then exit mouseUp        --  then use variable
   
   put "SELECT * FROM inventory_orders.pd_supplier_product " & \
         "WHERE UPC = " & swote(myUPC) into StrSQL  --  <<== no quote chaos, use a function!
   
   put "Fld UPC: " & myUPC & CR & \           --  just to peek
   "StrSQL: " & StrSQL
   
   put testQuery(StrSQL) into fld "data_fld"  --  or whatever
   if myData begins with "revdberr" then      --  NEVER omit this!
      answer error myData & CR & \
      "StrSQL: " & StrSQL                     --  you wanna see the Error!
   end if
end mouseUp

--  If you have a library stack, these should be in it:
function kwote what  --  what -> "what", you need this all the time!
   return quote & what & quote
end kwote

function swote what  --  what -> 'what', you need this all the time!
   return "'" & what & "'"
end swote

Never handle only 1 part of pairs (brackets, quotes etc.) - it's much too easy to mess up. Use helper functions like those in the script, they save tons of trouble!

Have fun!

PS:

[...] parameterized stored proc [...]

Hehe, have fun to create & maintain your PSPs once you start to do real database work. ;-)

[JackieBlue1970]

Thank you. Done plenty of “real database” work with stored procedures, triggers, views, etc., in both MySQL, SQL Server, Oracle as well as other experience in Access, Paradox, DBase 2, DBase 4.

[JackieBlue1970]

Thanks everyone. I did try adding the apostrophe after, as that is what I did at first. I am pretty sure I tested without the parens but I’ll try it again. I tried lots of things before I posted here. If it works I’ll let you all know.

[JackieBlue1970]

*****UPDATE******
Per the recommendation of those on here, I removed the parentheses on the object field references. This did not have any effect on the concatenation of the apostrophes. However, through trial and error, I ended up removing all of the apostrophe concatenations. This works for some reason. No string enclosure's at all for UPC. I wonder if the code that executes the database automatically adds the enclosures based on the data type of the selected field. While this seems strange, it does appear to be consistent with LiveCode's mantra of making it easy for beginners. BUT - if I don't include the apostrophes in the UPC SQL, LiveCode throws a syntax (Expression Bad Factor). I hope that LiveCode behavior is more consistent than what I am finding.

Code: Select all

 
  if field "fieldUPC" is not empty then
      put field "fieldUPC" into sQL

      put "SELECT * FROM inventory_orders.pd_supplier_product where UPC=" & sQL into tSQL      
      #answer tSQL
      put testQuery(tSQL) into rtData
      
   else      
      put "SELECT * FROM inventory_orders.pd_supplier_product where JMSSKU like '" & field "fieldSKU"  & "%'"  into testSQL
#      answer testSQL
      put testQuery(testSQL) into rtData
      #Check UPC for sku
      #get UPC code for SKU if exists
      #put "SELECT UPC FROM inventory_orders.pd_jms_product where JMSSKU like '" & field("fieldSKU") & "'"  into tUPC
      #answer tUPC
      #put testQuery("SELECT * FROM inventory_orders.pd_supplier_product where UPC=" & tUPC ) into rtData
      
      
   end if

[AxWald]

Hi,

Done plenty of “real database” work with stored procedures, triggers, views, etc., in both MySQL, SQL Server, Oracle as well as other experience in Access, Paradox, DBase 2, DBase 4. :D

Plz don't get me wrong - this was in no way meant as doubting your qualification!

Maybe I just mistook your meaning - "it should be a parameterized stored proc" reminded me of the "hardcore school" that demands that you never send any SQL to the database, and that you use exclusively pre-made stored procedures & views. And to be "safe", you'd best omit the LC database library altogether, and use only POST and GET calls via a REST API.

Trying to work this way with full blown ERPs (that often come with 100rds of tables), you'll crash into the wall quickly, in my experience. I have seen such tries, and I ran screaming. And thus survived.
Sure, I myself only provide custom UIs & added functionality to off-the-shelf ERPs. But when I look at my projects I typically see at least 3 databases & ~300 different SQL statements, for each. Some are ways bigger.
Thus my definition of "real database work". Yours will vary, I'm aware of this. No offense!

Ironically, the above mentioned "hardcore school" advertises the use of a REST API, preferably in PHP, for this. And thus add other attack vectors - PHP & its notoriously sloppy coders & libraries. After what I've seen of "professional PHP code" I'd rather evaluate my users input myself - but I'm just an old, grumpy fossil anyways, maybe.


From your last post I see you still have difficulties constructing your queries. Maybe there's a misconception here. Lemme try to explain:
LC doesn't do much but takes the SQL commands you create, and sends it to the DB as they are. So when you call:

Code: Select all

get revDataFromQuery(tab, return, tConnectionId, tQuery)

"tQuery" is a string that you can run on the db via any db manager (SQLite manager, HeidiSQL, phpMyAdmin etc.).
If it runs on the db, it will run when sent from LC, too. A great way of testing!

There are some cases when LC tries to help you - notably with the ":1,:2, ..." notation, and with things like "revDatabaseColumnNumbered", "revNumberOfRecords" etc. But again, LC will translate these into basic SQL statements, and send these to the db. You can check this with a simple network sniffer.
(Hint: You can do absolutely everything with only "connect/ disconnect/ dataFromQuery/ execute"!)

So you basically use LC as your "automated & extended db manager". You use variables and concatenation to construct a SQL string, throw it at the db, and hope it will not choke.
If it does, review your "tQuery" first, you may find the mistake. Or try your db manager with it, often you'll get better error messages.

Have fun!

[JackieBlue1970]

Thanks Ax. All my queries run fine in MySQL. It is just building the query that has issues. It works, as I mentioned, without the apostrophe. I haven’t done any more on it today.

Regarding stored procedures, I just prefer them to sending SQL direct in production. Allows more flexibility for changing things without releasing a new client, although in this case I am the only one using it.

[AxWald]

Hi,

[...] Allows more flexibility for changing things without releasing a new client [...]

Valid argument.
But it can be overcome quite easily - two methods as example:

1. Put your SQL strings into a simple text file that you replace/ download again at each start of your program. Like such:

   Code: Select all

   SimplSelect » "SELECT [[myFldList]] FROM [[myTable]] WHERE [[myWhere]] [[myFinish]]"
   SimplInsVal » "INSERT INTO [[myTable]] VALUES [[myValues]]"

   (» = tab, each statement on 1 line, preceded by its designator)
   You may want to have this file encrypted. At startup: load it, decrypt it, split it by tab and CR, and write the resulting array to a global variable. Now you can use it with "merge":

   Code: Select all

      put 13 into myID                     --  an ID
      put "name, age" into myFldList          --  you need to
      put "`t_persons`" into myTable          --  load _all_ the
      put "id = " & myID into myWhere         --  vars in your   
      put empty into myFinish                 --  string for merge() tp work!
      put merge(gSQL["SimplSelect"])       --  grab the array entry from gSQL
                                           --  and merge in your data:
      --  => "SELECT name, age FROM `t_persons` WHERE id = 13 "

   This way you basically have PSPs. But you have 'em all in a single text file, and can work on them with a (powerful) text editor instead of a (more limited) db manager.
   If you need to change something, do it, encrypt it, and throw it onto your server. Next time the client starts its program, Voila, it's updated ;-)
   .
2. There's a technique where you have only a "Launcher" as Standalone, and do the actual work in plain stacks, that you can replace/ download again at each startup (if there's a newer version). In this thread they're talking about, just now.
   
   I prefer this version - my "Launcher" contains only my crypto, update and prefs functionality, and works as kind of a program menu else. When it starts, it looks for it's preferences (they may have been changed) and loads/ decrypts them, then gets its libraries and "start using" 'em, then gets its "modules" (the worker stacks). For sure, only if there are new versions.
   If I want a part of my program updated I just throw the changed library or "module" onto the server. Next time the client starts its program, Voila, it's updated ;-)
   
   At some time I saved my SQL strings in custom properties, instead of having 'em plain in the code. Sounds nice & proper, right? Was a nightmare when changing field names or debugging ;-)

Hope this helps, have fun!

[JackieBlue1970]

[*]Put your SQL strings into a simple text file that you replace/ download again at each start of your program. Like such:

Code: Select all

SimplSelect » "SELECT [[myFldList]] FROM [[myTable]] WHERE [[myWhere]] [[myFinish]]"
SimplInsVal » "INSERT INTO [[myTable]] VALUES [[myValues]]"

(» = tab, each statement on 1 line, preceded by its designator)
You may want to have this file encrypted. At startup: load it, decrypt it, split it by tab and CR, and write the resulting array to a global variable. Now you can use it with "merge":

Code: Select all

   put 13 into myID                     --  an ID
   put "name, age" into myFldList          --  you need to
   put "`t_persons`" into myTable          --  load _all_ the
   put "id = " & myID into myWhere         --  vars in your   
   put empty into myFinish                 --  string for merge() tp work!
   put merge(gSQL["SimplSelect"])       --  grab the array entry from gSQL
                                        --  and merge in your data:
   --  => "SELECT name, age FROM `t_persons` WHERE id = 13 "

This way you basically have PSPs. But you have 'em all in a single text file, and can work on them with a (powerful) text editor instead of a (more limited) db manager.

Interesting way to do things AX. I prefer to work within DB and never have issues in other languages. Not sure what you mean by a more limited db manager. I can do anything I need in the db Manager. A long time ago, when I developed in a corporate environment, I would involve a DB Manager who might set some things up but we always had development permissions just short of admin. We just had the admin set user level (execute) and had no problems.

[AxWald]

Hi,

Not sure what you mean by a more limited db manager.

Meaning was that:
In the db managers I know you typically work on a single statement, and have comparable limited editing options.

Using a good text editor and my approach above, you have much more power at your hands - selection-hiliting everywhere, RegExp, adjustable checkspelling, configurable search/ replace pairs & snippets to insert, etc. Examples:
So to change a certain field name everywhere in all your statements is done in seconds. Dbl-Click a table name & see immediately where ever this table is used.

Well, guess it depends a lot of what one is used to, and what kind of work it is.
I'm a freelancer that started with Access. It was dBase back then, later MS SQL. It got real ugly with Sage KHK OfficeLine. Only read access to the db, and to insert data I had to hack their ActiveX.
Thx heavens, this was the time when ppl started switching from noisy in-house Proliants with MS SQL to web-based systems with mySQL or Postgres at a hoster. Just in time to be able to stop at Access 2k3 (that I still use today, sometimes), and to redo my frontends with LC. Since about '14 I'm now coding nearly exclusively in LC, and after a while I happened to nudge all of my customers to providers/ ERPs that allow SSH and modifications in the db.
So I came relatively late to know the benefits of advanced SQL beyond "select, insert & update". And found my way around my restrictions, and grew comfortable with it ;-)

Have fun!