Howto execute a SQL-statement from out a string? 2

[RedLion]

Hello,

It seems an easy thing, but it is not working.
I tried:

sql_string = "select * form club"
&sql_string into cursor myClubs

but this give's an error........

And I've tried this:

emp_cust_sql = "SELECT employee.emp_id, ;
customer.cust_id, customer.emp_id, ;
customer.contact, customer.company ;
FROM employee, customer ;
WHERE employee.emp_id = customer.emp_id"
CREATE CURSOR emp_cust_cursor AS &emp_cust

but this give's an error........

So, how should it be done?

Thanks Charl

 

[weedz]

Hi Charl,

When evaluating an SQL statement as a string it is good practice to have the entire SQL statement in the string.

F.i.

lcSQL = "SELECT * FROM mytable INTO CUROSR mycursor"

EVAL(lcSQL)

It makes it easier when your table and cursor are variables:

lcTable = "MYTABLE"
lcCursor = "TEMPCURSOR"

lcSQL = "SELECT * FROM " + lcTable + " INTO CUROSR " + lcCursor

EVAL(lcSQL)

So as you can see that you can build an SQL statement from various variables in which you can define anything, as long as it evaluates to a valid SQL statement.

HTH,

Weedz
veld4663@exact.nl

 

[RedLion]

Bedankt Weedz,

EVAL() was the statement where I was looking for!!!!!!!!

Thanks,

Charl

 

[RedLion]

Sorry Weedz, I was to fast with answering. It doesn't work, I get the error "syntax error".

lcSQL = "select * from club into cursor myCursor"
EVAL(lcSQL)

But when I try this:

lnNumber = 5
? EVAL(lnNumber)

This works, but with the sql statement it doesn't work. Even when I use lcString = "BROWSE" it doesn't work?

Any suggestions?

Thanks Charl

 

[weedz]

You can also use &lcSQl to evaluate your string. This does work.

Weedz
veld4663@exact.nl

 

[RedLion]

Indeed this works thanks again.

 

[BlindPete]

OT: EVAL()

I seem to recall reading somewhere that MS recommends using EVAL() over & (ampersand). EVAL() is supposed to be faster.

Is this true or am I not remembering correctly?

Pete
blindpete@mail.com

What your mother told you is true! You will go blind! (from moonshine anyway)

 

[jonscott8]

Pete,

I seem to recall reading somewhere that MS recommends using EVAL() over & (ampersand). EVAL() is supposed to be faster.

You recall correctly. Straight from the VFP docs:
Whenever possible, use EVALUATE( ) or a name expression to replace macro substitution using &. EVALUATE and name expressions execute faster than macro substitution.

Charl,

It doesn't work, I get the error "syntax error".

The EVALUATE() function is used to evaluate an expression and return the result.For Instance,

lnNumber = 5
? EVAL(lnNumber)

is the equivalent of this:
? 5

Here's an example of when the evaluate function is appropriate:

Table: MyTable
Fields: MyField1,MyField2,MyField3,etc..

USE MyTable
FOR X = 1 TO 3
lnValue=lnValue+EVALUATE('MyField'+TRANS(X))
ENDFOR
? lnValue Jon Hawkins
jonscott8@yahoo.com

The World Is Headed For Mutiny,
When All We Want Is Unity. - Creed

 

[jonscott8]

FWIW, a good rule of thumb on when to use (& command) macro substitution, (although not always applicable) is when the character expression contains a vfp keyword, ie, SELECT,SET,etc... Jon Hawkins
jonscott8@yahoo.com

The World Is Headed For Mutiny,
When All We Want Is Unity. - Creed

 

[tomavery]

Reading this thread is exactly what I need but Im missing something.

lcTable = "AUTHORS"
lcCursor = "TEMPCURSOR"

lcSQL = "SELECT * FROM " + lcTable + " INTO CURSOR " + lcCursor

EVAL(&lcSQL)

I keep getting a missing &quot" error, i just dont see it.
THank you.

Go Canucks! (rabid Vancouver Canucks Fan)

 

[jonscott8]

Hi Tom,

First, a brief explanation of why you are receiving a missing &quot" error.

After macro substitution is performed, this is what the compiler is evaluating:

EVAL(SELECT * FROM AUTHORS INTO CURSOR TEMPCURSOR)

The evaluate function is used to evaluate character expressions.

The EVALUATE function is not necessary in your case. Just use the & command.

In the future, use the rule of thumb I posted above. Jon Hawkins
jonscott8@yahoo.com

The World Is Headed For Mutiny,
When All We Want Is Unity. - Creed

 

[BlindPete]

JonScott8 Wrote:
...a good rule of thumb on when to use (& command) macro substitution, (although not always applicable) is when the character expression contains a vfp keyword, ie, SELECT,SET,etc...

Thanks!

Pete
blindpete@mail.com

What your mother told you is true! You will go blind! (from moonshine anyway)