VFP 9.0 Changes the rules for Views that use Macro Substitution.
If your view definition is something like:
CREATE SQL VIEW myview AS SELECT mytable.* from mydbc!mytable WHERE &cFilter
The value of the variable "cFilter" is macro-substituted as the literal "where clause" for the select statement at run-time.
You must change your definition to include the question mark:
CREATE SQL VIEW myview AS SELECT mytable.* from mydbc!mytable WHERE &?cFilter
To create/use Views that use macro-substitution, you must follow these rules:
1. You MUST put a "?" question mark on the front of your parameter (just like you did prior to VFP 8.0).
2. You MUST have defined a variable with the same name as your parameter PRIOR to defining the view (in previous version, it was the opposite).
3. Additionally, this defined variable: must be of type STRING, it must NOT be empty, it must be in SCOPE, and the VALUE of the variable must be able to be compiled without errors (like ".T.").
4. Since using macro-substitution in Views was not "intended" functionality (and is not documented), you still cannot use the much improved View/Query Designer when working with macro-substituted parameters (huge bummer), these views must be created via code (or a cool utility such as View Editor or EView).
5. At RUN-TIME a form that contains a View (with a macro-substituted parameter) in it's Data Environment, the "variable" name that is used in the View Definition must exist as a compilable non-empty string BEFORE the view is opened. This is true even if the View is opened with
NODATA. (This can be done using the Data Environments Before Open Tables() method, public variable, etc.)
6. The setting of
ENGINEBEHAVIOR has no effect on these rules.
If you attempt to use a macro-substituted view without following these rules:
At design-time or using some utilities, you could get a "Syntax Error" because the WHERE clause won't compile.
At run-time you could get the error: Error loading file - record number xxx. xxMyFormNamexx (or one of its members). Loading the form or data environment : variable 'xxxx' is not found.
CREATE SQL VIEW "v_Cust_Some" ;
AS SELECT * ;
FROM tastrade!customer ;
WHERE Customer.company_name = ?vp_cComp_Name
I am not sure if the view parameter needs the ? in front of the parameter. In fact I am not sure why you ever need the ?, unless you want to trigger the "Enter view parameter" dialog box to come up when you open the view. Is there any other reason you would want it?
It tells FoxPro that this is a parameter. If the variable exists, it will be used: if it doesn't, it will be asked for.
I have a good reason to add the ?'s: Stonefield Reports avoids views that have parameters by looking for the '?' in the SQL. So your app may run just fine, but you may run into problems when you try to add SFR afterwords. -- ?CFK
I have a better reason: Remote views requre the '?' to be before VP's. For remote views, the ? triggers something as the Sql Command is being passed to the ODBC layer. You can see this by looking at what command the Server actually recieves: the VP gets renamed to @Px and the value added to the list of parameters being passed. So always use the ?, end of story. - cfk
Question: Why is the "?" needed before "vp_cComp_Name"? I recognize that it is a parameter for the query, but if you don't put the "?" there doesn't VFP recognize that it's not a field and look at memory variables? I think I just answered my question: VFP can recognize this (and might work without the "?") but if the object(correct term?) of the query is some back end Sql Server or My Sql then the "?" tells VFP to replace this value with the memory variable's contents BEFORE sending the whole SQL statement, otherwise unparsed, to the back end. Am I correct? - ?wgcs
I had a similar question about SPT that made me think, "what parses the string and deals with the parameter?"
lcCustName = This.Value
IF !EMPTY(lcCustName) && don't need to validate empty value
* create SQL statement
* remember, string comparisons are not case-sensitive
lcCommand = "SELECT cName FROM customers WHERE cName = ?lcCustName"
* pass command to server, name resulting cursor CUSTNAME
liResult = SQLEXEC(liConnectHandle, lcCommand, "CUSTNAME")
Will this really work? -- CFK (who is to lazy to try it right now, but thinks it is worth discussing)
Yes, this really works. More, this works also for such SQL commands as INSERT, UPDATE etc. Good example: you cannot write an INSERT command using SPT to insert large memo field into database that contains text with formatting, line breaks etc. using just SQL Syntax. However, you can do this using parameters:
liResult = SQLEXEC(liConnectHandle, "UPDATE MyTable Set MyMemo=?lcMyMemo")
In above sample lcMyMemo can be any text with any size.
Note also that you can use local cursor fields in such way. Following example works too:
replace MyAlias.lcMyMemo With ... IN MyAlias
liResult = SQLEXEC(liConnectHandle, "UPDATE MyTable Set MyMemo=?MyAlias.lcMyMemo")
This way you can use to save general fields to SQL Server, accordingly to some MS articles.
Note that object properties don't work in this formulation, so you cannot use
SQLEXEC(liConnectHandle, "Update MyTable SET MyField=?THISFORM.znMyValue")
Declare the object to a local or private variable first and it will work.
myObject = THISFORM
SQLEXEC(liConnectHandle, "Update MyTable SET MyField=?myObject.znMyValue") -- Bob Murphy
What processes these question marks? Well, this is processed by VFP when working with ODBC. Who knows C++ and worked with ODBC interfaces, know what I mean. That is why any variable written after question mark is correctly processed.
-- Vlad Grynchyshyn
More specifically, after VFP initiates the ODBC request, ODBC comes back and prompts VFP for the value of each parameter. Thus, you can reduce ODBC traffic by concatenating the parameter into the string, but (IMHO) it's better to use the ? notation. This notation allows the DBMS to optimize SQL once, and subsequent parameters can simply use the previously determined access path. -- Zahid Ali
When you say "ODBC traffic", you are talking about "inter process traffic" not "network traffic", right? In other words: both VFP and the ODBC driver are on the same box, and they talk back and forth a few times to get the parameter values, then the ODBC drivers sends the parameters and parameterized command to the server. (correct me if I am wrong) (duh) -- ?CFK
I don't know for sure, but I don't think ODBC resolves the parameters and other issues before initiating traffic to the server. I think there are multiple network transactions involved in the conversation.
Thank you both for the confirmation and explanation. That is really great! -- CFK
How to use VFP's macro expansion to have a dynaminc where clause. I hear this works for any of the clauses. but... this code only works if you are using the VFP database engine (DBF's) - With SPT and some slight of hand (SqlExec(), Select view, zap, append from QueryResults, .FakeUpdate()) it will work with any back end Sql server.
* Get access to some data
OPEN DATABASE (HOME(2)+"tastrade\data\tastrade")
* home(2) = "C:\PROGRAM FILES\MICROSOFT VISUAL STUDIO\MSDN98\98VS\1033\SAMPLES\VFP98\"
* Make a new database for the view example
* it will read/write the sample data
CREATE DATABASE vfpviews102
* Define the view
* both the SQL-SELECT query to get data,
* and the set it to be updateable.
CREATE SQL VIEW "v_Cust_Any" ;
AS SELECT * ;
FROM tastrade!customer ;
WHERE &lcSqlWhere ;
ORDER BY Customer.company_name
? DBGetProp( "v_Cust_Any", "view","SQL" )
lcSqlWhere = "substr( Customer.company_name, 1, 1 ) = 'A'"
lcSqlWhere = "substr( Customer.company_name, 1, 1 ) = 'B'"
Requery( "v_Cust_Any" )
Q: Does the parameter really have to be a string? I have at least one viewed deployed and working where the parameter is an integer to match a foreign key. It seems to work well.
A: The parameter can be any datatype - and any VFP expression, including constants, variables, VFP Functions and UDF functions.
Category VFP Commands Category Data