|
1.15. SELECT, INSERT, UPDATE, DELETE will permit references to FoxPro variables.
This posed some interesting challenges, since FoxPro variables are referenced using an internal Symbol Table. For this purpose, we've included a function called E_SQL_Find_Variables, which performs the required substitions:
Example: E_SQL_Select_Convert (E_SQL_Find_Variables (select_sql_command))
The only requirement is that you precede variable names with a tilde ("~") before each FoxPro variable, e.g.:
SELECT * FROM tbl WHERE F1=~m.F1 AND F2=~m.F2
Our function will check if variables exist and replace valid references with '?'
SELECT * FROM tbl WHERE F1=?F1 AND F2=?F2
What's more, you can use the following syntax:
SELECT * FROM tbl WHERE F1=~%|%expr1%|% AND F2=~%|%expr2%|%
or (same)
SELECT * FROM tbl WHERE F1=~(expr1) AND F2=~(expr2)
to evaluate a foxpro expressions (here: expr1, expr2 ) into SQL syntax
But you will have a problem when you try to select data into remote temporary table with syntax:
SELECT * FROM tbl WHERE F1=~m.F1 AND F2=~m.F2 Into #temporary_table_name
During evaluation of a variable reference FoxPro makes temporary changes of the session of access to data and we
lose access to created #temporary_table_name which has a scope like a local variable T-SQL.
In other words - this does not work and there won't be any temporary table.
In this case the function E_SQL_Find_Variables has the second parameter. If
you pass .T. then references to variables will be replaced with their values like:
m.F1 = {^2004-01-24}
m.F2 = 12345.6789
SELECT * FROM tbl WHERE F1='2004-01-24' AND F2=12345.6789 Into #temporary_table_name
So,
send .T. as second parameter for conditions and do not send it when you send data (UPDATE ... SET ... , INSERT, ) because
problems with MEMO and GENERAL will occur.
If you refer to object property or database field with ~, for example:
SELECT * From tbl Where F1=~static_table.F1 or F1=~Object.Property.Property
then E_SQL_Find_Variables will always replace the reference with a value (constant) (not ?...)
Note! Call a function E_SQL_Remove_Directives if you access FoxPro tables. It will remove all ~ from original command.
If UsingSQL
Cmd = E_SQL_Select_Convert(E_SQL_Find_Variables(OriginalSelectCmd))
IF SQLEXEC(connection_id, cmd, alias) <= 0
* Error message display here...
ENDIF
ELSE
Cmd = E_SQL_Remove_Directives(OriginalSelectCmd)
&Cmd
ENDIF
back
to features list
|
