LiveCode Forums.

JackieBlue1970 wrote: ↑

Sat Feb 29, 2020 12:18 am

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

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

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

SparkOut wrote: ↑

Sat Feb 29, 2020 8:55 am

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

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

JackieBlue1970 wrote: ↑

Sat Feb 29, 2020 12:18 am

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

JackieBlue1970 wrote: ↑

Sun Mar 01, 2020 1:25 am

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

get revDataFromQuery(tab, return, tConnectionId, tQuery)

JackieBlue1970 wrote: ↑

Wed Mar 04, 2020 2:45 am

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

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 ;-)

AxWald wrote: ↑

Wed Mar 04, 2020 4:39 pm

[*]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.

JackieBlue1970 wrote: ↑

Thu Mar 05, 2020 12:03 am

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