Foxpro SQL functions - sqlParameters for sqlExec()

[kevclark64]

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()

[FFF]

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 (https://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 )

[kevclark64]

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.

[kevclark64]

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.

[robert]

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

[kevclark64]

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.

[atlopes]

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

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. [strike]The VFP parser detects the end of expression when it becomes invalid at parsing time.[/strike]

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

Code: Select all

LOCAL ODBCHandle AS Integer

m.ODBCHandle = SQLCONNECT()

LOCAL SomeVariable AS Integer
LOCAL SecondVariable AS Integer

m.SomeVariable = 10
m.SecondVariable = 1

* edit: this does not work: SQLEXEC(m.ODBCHandle, "SELECT ?m.SomeVariable + m.SecondVariable AS ResultName", "Result")

SQLEXEC(m.ODBCHandle, "SELECT ?(m.SomeVariable + m.SecondVariable) AS ResultName", "Result")

BROWSE

SQLDISCONNECT(m.ODBCHandle)
 

returns 11.

Wrong: [strike]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.[/strike]

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.