TOPIC:

I know, that You have spoken to Matt about parameters like ?expression in commandtext.
Is possible to add sqlparameters collection like .Net SqlCommand.SqlParameters and use @parameter in commandtext?
Or use anonymous object with fields as @parameters like Dapper?

Zdeněk,

Our plan is to do the following:
1) Support parameters like in VFP: declare them as variable and add a reference in the SQL statement with ?VarName. The compiler will detect that and will add a call to a runtime function to pass the parameters.
At runtime we will have to extract the parameters from the SQLString and replace them with the right placeholders for the backend. That is a question mark for the ODBC and OLEDB DataProviders, @varname for SQL Server and for example :varname for Oracle.
So we will have to handle this in the Factory layer (I am sure other vendors have invented other syntaxes).
The runtime will also have to create the right DbParameter objects to pass the values to the Ado.Net dataprovider
The compiler will then automatically add a call to a function that gets the statement handle (which is already in the call to SqlExec() and SqlPrepare) and pass the list of variable names and codeblocks. In my demo yesterday I used a list of names and values, but you are right, storing the various name/value pairs in an anonymous type is probably better.
Variables passed by reference can be assigned back from the anonymous object in code that is also automatically generated by the compiler.

2) We will also add a documented interface to this, so you can call a function and explicitely pass the parameters. With your suggestion about the anonymous types that would look like this:

before you execute the query
I think we need to have a different syntax then for queries that need to be "processed" by the compiler and queries that use the explicit SqlParameters() call, so the compiler know when it has to do this for you or when you do it yourself in code. Something like this:

VFP compatibleIn this case the compiler will have to extract the parameter names from the SQLExec command and generate something like we have shown above:

X# improved
If you want to assign an out variable back then you do something like this
Robert

It's fine if you added new code constructs that can be used if someone wants to do things differently in X#, however,
the big question is will people be able run existing VFP code *as-is*?? If people have to change their current SqlExec() code calls to make it run in X#, then it will be a big pain because existing apps will have *hundreds* of SqlExec() calls and no one will want to change their current code that much; Not because it is too much work, but mostly because it is a very error-prone thing to edit that much code and its hard to test every nook and cranny when you make that many edits.

Matt,
I know and understand that. Our product if full of things that I'd rather not implement but that are needed because they work like that in for example VO and Vulcan.
So adding a few more of these things for VFP is not a problem at all.
That is why number 1) on my list was to support things "automatically".

Robert

Zdeněk, Matt,

I did some work and have now implemented the following (please note that this is work in progress):

However, an anonymous type can NOT be used for OUT parameters, because the properties of an anonymous type are read only.
Zdeněk, how does Dapper do that ? Do they require you to use an DynamicParameters collection for that ? And then read the value from the parameters collection afterward ?

Robert

Robert,

github.com/StackExchange/Dapper#stored-procedures

Zdeněk

Small notice to "?" in Foxpro sqlExec.

After ? follows expression, which is evaluated in sqlExec call.

This can be field in used cursor or expression like ?(date()-10)

Zdeněk

Zdeněk Krejčí wrote: Small notice to "?" in Foxpro sqlExec.

After ? follows expression, which is evaluated in sqlExec call.
This can be field in used cursor or expression like ?(date()-10)
Zdeněk

Really ? Can I assume that the expression is between parentheses ?
And where is that documented in the VFP Help ?

Robert

Here's another permutation on Foxpro and parameterized queries. Suppose you want to pass an integer field as parameter to a query:

=sqlexec(handle,"select * from myfile where id=?mytable.integerfield")

You would think that Foxpro passes mytable.integerfield as an integer. You would be wrong, because the value is passed as a float. If your SQL backend has an index on the integer id field it won't use the index because the wrong value type is passed. Because it doesn't use the index, the select will generally take far longer. Using postgres, to get the value converted to an integer you would need to use "?mytable.integerfield::int"

So here's one area where I think XSharp should not completely emulate Foxpro. If a parameter has been declared as a certain type then it should always be passed as that type.

So,

The parameter may be any expression, not just a local or memory variable but also a field in a cursor and any other expression ?
How does FoxPro see what the end of the expression is ?
What does it do with
=sqlexec(handle,"select * from myfile where id=?mytable.integerfield * SomeName")

Does it resolve SomeVariable to a variable or do I have to specify it like this

id=?mytable.integerfield * ?SomeName

And then this "::" syntax ?
I have seen that as "scope resolution operator". But never as type specifier.
Again, where is all of this documented ?

At least with X# all the code is on Github....

Robert

The value being passed is not required to be within parentheses. Looking through some of my code I found queries with these parameters:

?ALLTRIM(UPPER(this.Value))
?tmpTcrs(i,2)
?DATETIME()

robert wrote: And then this "::" syntax ?
I have seen that as "scope resolution operator". But never as type specifier.
Again, where is all of this documented ?

At least with X# all the code is on Github....

"4.2.9. Type Casts
A type cast specifies a conversion from one data type to another. PostgreSQL accepts two equivalent
syntaxes for type casts:
CAST ( expression AS type )
expression::type
The CAST syntax conforms to SQL; the syntax with :: is historical PostgreSQL usage.
When a cast is applied to a value expression of a known type, it represents a run-time type conversion.
The cast will succeed only if a suitable type conversion operation has been defined. Notice that this
is subtly different from the use of casts with constants, as shown in Section 4.1.2.7. A cast applied
to an unadorned string literal represents the initial assignment of a type to a literal constant value,
and so it will succeed for any type (if the contents of the string literal are acceptable input syntax for
the data type)...."

From PostgresDocs (www.postgresql.org/docs/12/index.html)
I think, PG code also is accessible at Github, never dared to look there, that's way beyond me (and it's C )

The "::int" isn't being resolved by Foxpro, but rather by the backend server, so you don't really need to be concerned with that. I just mentioned it as what needs to be done to get a parameter to be an integer in the query since Foxpro doesn't pass the value as an integer. Sorry for any confusion.

I tried various parameter permutations and here's what I found:

icust=100
=SQLEXEC(g_sqlhandle,"select * from masterli where custnum=?icust * 2","tmp")

In the query above Foxpro is resolving icust to 100 but the backend SQL is then multiplying it by 2, so it does work as one would expect.

two=2
icust=100
=SQLEXEC(g_sqlhandle,"select * from masterli where custnum=?icust * two","tmp")

In this query Foxpro is resolving icust but the backend is trying to resolve "two" which it probably views as another field. Unless there is a field called "two" in the table then the query will fail.

two=2
icust=100
=SQLEXEC(g_sqlhandle,"select * from masterli where custnum=?icust * ?two","tmp")

In this query Foxpro is resolving both "icust" and "two" so the query works as expected.

two=2
icust=100
=SQLEXEC(g_sqlhandle,"select * from masterli where custnum=?icust*two","tmp4")

In the above query you'd think Foxpro might take "icust*two" as one expression and evaluate but it does not. The query fails when no "two" field exists in the backend table.

two=2
icust=100
=SQLEXEC(g_sqlhandle,"select * from masterli where custnum=?(icust*two)","tmp4")

When the expression is put in parentheses Foxpro evaluates the whole expression so this works.

Kevins,

Thanks for the examples. This is sort of what I would expect:
1) if ? is followed by an identifier (name, or name.property) then the expression stops after the name or name.property, unless this is followed by an left parenthesis. See 2)
2) if ? is followed by a name followed by an left parameter then that is seen as a function call.
3) if ? is following by a Left Parenthesis then the expression continues until the closing parenthesis

I think this would work too: ? icust * ?two but then the query would have 2 parameters and the calculation would have been done by the backend. In the case of ?(icust*two) there is only one parameter and calculation is done by the client.

Robert

Robert, the only caveat I would make is that, assuming that at some point X# will support parentheses for arrays, then a name followed by a parenthesis could either be a function or an array value.

Robert et al. - I'm editing this because it was wrong.

robert wrote: So,

The parameter may be any expression, not just a local or memory variable but also a field in a cursor and any other expression ?
How does FoxPro see what the end of the expression is ?
What does it do with
=sqlexec(handle,"select * from myfile where id=?mytable.integerfield * SomeName")

Does it resolve SomeVariable to a variable or do I have to specify it like this

id=?mytable.integerfield * ?SomeName

And then this "::" syntax ?
I have seen that as "scope resolution operator". But never as type specifier.
Again, where is all of this documented ?

At least with X# all the code is on Github....

Robert

Robert, the parameter solves to any expression. The VFP parser detects the end of expression when it becomes invalid at parsing time.

Documented in the Help file:

The parameter you supply is evaluated as a Visual FoxPro expression, and the value is sent as part of the view's SQL statement. If the evaluation fails, Visual FoxPro prompts for the parameter value.

Meaning: not as a variable reference, or a field name, or an object member.

For instance, the following code

returns 11.

Wrong: The parser interprets m.SomeVariable + m.SecondVariable as an expression that ends at the "AS" keyword, because AS wouldn't be acceptable at this point of the expression.

This will be actually easier to implement. The expression must be enclosed in parentheses, or a reference (to a variable, an object member, or a field), or a function call. I think this covers it all, but I'll continue to check on this.