Macro Substitution with SQLEXEC 1

[ariftvt]

what is the best practice to do a Macro Substitution with SQLEXEC.
for example:-
bbb=100

sqlexec(handle,"insert into table1(fld1,fld2) values(value1,value2)"

in this case how can i substitute value1 with the variable 'bbb'

pls help

 

[bborissov]

Use parameters:

bbb=100
sqlexec(handle,"insert into table1(fld1,fld2) values(?m.bbb, value2)")

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[JRB-Bldr]

Another method is to build the entire command, inclusive of the variables, and then put it into the SQLEXEC() line.

bbb=100
value2 = "ABC"
mcCommand = "insert into table1(fld1,fld2) values(" + ALLTRIM(STR(bbb)) + ", '" + Value2 + "')"

SQLEXEC(mnConnectionHandle, + mcCommand)

Good Luck,
JRB-Bldr

 

[Mike Lewis]

Ariftvt,

You've received two excellent suggestions. Of the two, I'd favour Borislav's solution, mainly because it will take care of serveral server-specific issues automatically.

For example, if your back-end is SQL Server, the query could fail if you send a string containing an apostrophe. By using the "?" syntax, issues like that are taken care of.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[ariftvt]

Great!!...thanks Borislav and all others.