Foxpro SQL functions - sqlParameters for sqlExec()

[Zdeněk Krejčí]

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?

[robert]

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:

Code: Select all

var oParams := CLASS {CustomerId := 1, State := "NY"} // our syntax for anonymous types
SqlParameters (nHandle, oParams)

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 compatible

Code: Select all

LOCAL CustomerId := 1
LOCAL State := "NY"
SqlExec(nHandle, "Select * from customers where CustomerId = ?CustomerId and State = ?State")

In 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

Code: Select all

LOCAL CustomerId := 1
LOCAL State := "NY"
SqlParameters (nHandle, CLASS {CustomerId, State}) // no need for the names. They are derived

// note we can't/won't use the @sign for parameters like Dapper does because 
// VFP already uses that for variables passed by reference and that would create confusion

SqlExec(nHandle, "Select * from customers where CustomerId = :CustomerId and State = :State")

If you want to assign an out variable back then you do something like this

Code: Select all

 var oParams := CLASS {CustomerId := 1} 
SqlExec(nHandle, "execute SomeStoredProcedure :@CustomerId" )
CustomerId := oParams:CustomerId 

Robert

[FoxProMatt]

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.

[robert]

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

[robert]

Zdeněk, Matt,

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

Code: Select all

        // Declare local. Can be typed or untyped. A memvar would work as well.
        LOCAL CustomerId = 'ALFKI'
        // Create anonymous type. Has one property with the name CustomerId and value 'ALFKI'
        VAR oParams := CLASS{CustomerId}
        // Pass the parameters object to the backend
        SqlParameters(handle, oParams)
        // Execute a query with a parameter. We accept both a ? and a : as start of parameter name
        ? SqlExec(handle, "Select * from orders where customerId = :CustomerId","orders")
        Browse()

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

[Zdeněk Krejčí]

Robert,

https://github.com/StackExchange/Dapper ... procedures

Zdeněk

[Zdeněk Krejčí]

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

[robert]

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

[kevclark64]

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.

[robert]

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