Results 1 to 6 of 6

Thread: Best way to execute commands server-side with variables in them?

  1. #1
    Junior Member
    Join Date
    Feb 2016
    Posts
    13

    Best way to execute commands server-side with variables in them?

    Hello everyone,

    I'm writing a little server-side function that searches a table for a given value in a given field, and returns a different given field in the same record. For instance, you could tell it to return the name of a customer with ID=5. I've gotten it working (muliple ways, actually) on a desktop app but can't seem to do it on a web/mobile app. The original basic logic was building a string out of my different variables and then run Lianja.execute on that string. Lianja.execute doesn't work on web/mobile clients so I needed another way.

    I found the PREPARE/EXECUTE commands in the documentation and tried them. I couldn't work out how it handled different types of parameters but found a way to get it to work on desktop. What I thought should work was:
    Code:
    local mystring = "select ? from ? where ? = ?"
    local selectfield = "total"
    local tablename = "expenseclaim"
    local searchfield = "claimid"
    local searchvalue = 5
    prepare stmt from :mystring
    execute stmt using :selectfield, :tablename, :searchfield, :searchvalue
    This seemed to behave oddly with respect to data types. For example, it accepted expenseclaim as the name of a table, but literally output the string "total" instead of the value in the field named total.

    What I got to work on a desktop app was building "mystring" by placing the variables in it directly, then calling PREPARE/EXECUTE on it. Since EXECUTE needs at least one parameter and the table name seemed to behave properly, I left that in the string as a parameter:
    Code:
    local selectfield = "total"
    local tablename = "expenseclaim"
    local searchfield = "claimid"
    local searchvalue = 5
    local mystring = "select " + selectfield
    mystring += " from ?"
    mystring += " where " + searchfield + " = " + searchvalue
    PREPARE stmt from :mystring
    EXECUTE stmt USING :tablename
    This works perfectly fine on desktop, but on web/mobile clients it breaks on the EXECUTE line.

    That might be a bug , it might not be implemented on web/mobile or I might even be making some silly mistake, but even then it feels like a rather convoluted way of going about things. Is there a way to just execute a string within a Lianja/VFP script on a server? Or to just place variables into a Lianja/VFP command?

    Thanks for the help,
    Ryan
    Last edited by rhoward; 2016-02-09 at 22:45.

  2. #2
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    6,729
    Blog Entries
    18
    It's far simpler than all that.

    just create a statement as a string using &macro substitution or use sprintf() if you find that easier.

    Commands stored in character variables can be executed dynamically

    cmd = "select * from " + p_tablename + " where id=&p_id"
    &cmd
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

  3. #3
    Junior Member
    Join Date
    Feb 2016
    Posts
    13
    I thought it would be simple!

    Unfortunately, it's still not working for me. I just ran some commands using southwind in the lianja console to test and the "&" command doesn't seem to work. Everything in my console so far is:
    Code:
    open database southwind
    p_table = "customers"
    p_id = "alfki"
    mystring = sprintf("select * from %s where customerid = '%s'",p_table,p_id)
    &mystring
    At the end it just says unrecognised command (pointing at the & symbol). I'm sure I'm missing something really obvious!

  4. #4
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    6,729
    Blog Entries
    18
    You can't use &macros in the console. Just type

    ed ryan

    and put your code in that file then from the console issue

    do ryan
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

  5. #5
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    6,729
    Blog Entries
    18
    Incidentally, you can call your server side proc from the client using

    var result = Lianja.evaluate("ryan()");

    to substitute parameters you can use {...} macros on the client too.
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

  6. #6
    Junior Member
    Join Date
    Feb 2016
    Posts
    13
    Okay, so & macros work fine inside scripts just not in the console. Got it.

    That's exactly what I was looking for. Thanks Barry!

Bookmarks

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Journey into the Cloud
Join us