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

[rhoward]

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:

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:

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

[barrymavin]

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

[rhoward]

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:

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!

[barrymavin]

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

[barrymavin]

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.

[rhoward]

Okay, so & macros work fine inside scripts just not in the console. Got it.

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