'?' Operator in SqlExec() function not working 3

[Ris1]

I am migrating VFP database to Oracle. While converting locate command into select query , I have used ? before variable name. But it is not working . Pls. refer below

Procedure Proc1
[indent]Lpara lcCriteria[/indent]
[indent]*- on running below line , i/p box opens for getting parameter value[/indent]
[indent]sqlexec(nHandle,"Select * from Variables where Criteria = ?lcCriteria")[/indent]
[indent]lcCrit = lcCriteria[/indent]
[indent]*- Below line is fetching result
[indent]sqlexec(nHandle,"Select * from Variables where Criteria = ?lcCrit")[/indent]

** I am not able to understand this strange behaviour. Query don't run for lcCriteria,when I assign value of lcCriteria to another variable lcCrit, code works fine.

 

[Mike Lewis]

You have to actually store a value in lcCriteria before you run the query. That's the whole point. How else does Oracle know what value you want to test against?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Ris1]

Value of lcCriteria is coming from the calling procedure - Do Proc1 with 'START'

 

[Olaf Doschke]

You don't specify a result cursor name, results will arrive in a cursor named "SQLRESULT". The second SQLEXEC is just overwriting the first result, you only get one result curosr. But if the second SQLExec gives you a result, the first one will, too.

Just let me try this...yes, it works.

So your whole problem is the second sqlexec overwriting the first result cursor.

Bye, Olaf.

 

[Ris1]

Olaf,
I have placed browse after each statement . 1st statement is showing I/P box asking for lcCriteria input. If I esc, simply blank cursor is browsed, while in second it is fetching records.

When I simulate situation with a simple test program , both the queries are working, so is it related something to my application or anything else.

 

[Olaf Doschke]

>When I simulate situation with a simple test program , both the queries are working,

Well, that shows that something else plays a role. I don't know what else is in your environment, I can also only make up a test program to try to reproduce the problem. So no chance to find out, unless you tell much more.

I would use a pragmatic solution. Rename your parameter tcCriteria, it would be by norms, to start a parameter variable name by t (I use the mnemonic "t for taken"). Second change, make it ?m.tcCriteria to make clear that means a memory variable.

If that works, then let it be. You might have a name overlap with a private or public variable.

Bye, Olaf.

 

[Tore Bleken]

My guess is that you have a line, #define lcCriteria, somewhere in your code.

 

[Olaf Doschke]

Are you even posting actual SQL, or is this a simplification, too? Is it at least the same query you do and redo?

You could introduce error handling of SQLExec in the pattern:

lnRes = SQLEXEC(...)
If lnRes<0
   AERROR (laError)
   suspend
Endif

Bye, Olaf.

 

[Olaf Doschke]

And last not least SQL Profiler would help you find out what query actually arrives including what param value. If Tore is right, you might be surprised about the value passed in.

Bye, Olaf.

 

[Mike Lewis]

There are various things you could try in order to help solve the problem:

- Set a breakpoint on the SQLEXEC() to confirm that lcCriteria contains the expected value.

- Execute the SQLEXEC() by itself in the command window. (You will also need to do the SQLCONNECT() and the assignment of lcCriteria in the command window.)

- Try hard-coding the value of lcCriteria (i.e. instead of ?lcCriteria, just use 'START" in your WHERE clause).

- Try naming the paramter something else. The fact that it appears to work with lcCrit but not lcCriteria might indicate some problem with that specific name (although that's unlikely).

- Use AERROR() to see if Oracle is returning any error information.

I'm not saying that any of these will provide the ultimate solution. But they are typical diagnostic techniques and could at least provide some more information.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[GriffMG]

You have to parameterize a value like that to be able to use it in a SQL statement for a remote database like Oracle.

That means you need to load it (the value of your variable) up to the SQL server.

In asp - to a VFP database I would tend to do this:

cmd.CommandText = "SELECT * from Myable where MyField$? "
cmd.Parameters.Append cmd.CreateParameter("@A", 201, 1, Len(strSelectedDocuments ), strSelectedDocuments )
SET rsRecordSet = cmd.execute()

So in the above strSelectedDocuments is a string containing a list of documents.. the Parameters.Append uploads that to the SQL server/driver

For the VFP OLEDB driver you do not need to specify the parameter name to use it - VFP works with the ORDER in which the values are loaded, I don't know about Oracle.


Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[GriffMG]

I can't be certain, but this link, for .Net, seems to use a very similar syntax for an Oracle database

https://www.codeproject.com/Tips/1076851/Oracle-Parameterized-Queries-for-the-NET-Developer

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Ris1]

Olaf, this was the very first thing which I had tested but no change in result as I thought there might be some clashing in variable names.

This is the actual query which I am running and not simplification.

No, I am not using #define lcCriteria



1 point to note is that on replacing ? with & problem gets solved.

Procedure Proc1 
[indent]Lpara lcCriteria [/indent]
[indent]** Query works fine now[/indent]
[indent]sqlexec(nHandle,"Select * from Variables where Criteria = '&lcCriteria'")[/indent]
[indent]lcCrit = lcCriteria [/indent]
[indent]*- Below line is fetching result [/indent]
[indent]sqlexec(nHandle,"Select * from Variables where Criteria = ?lcCrit")][/indent]
Endproc

** For Errors I have kept
sqlsetprop(nHandle,"DispWarning",.T.)

 

[Olaf Doschke]

With macro substitution, you're putting this into lower quality and make it vulnerable for SQL injection.

It works with lcCrit, have you made the final test to use this variable name for the param? What was the original param name you already changed?

I can't believe what you say, because in both cases variables are used and if the second works the first MUST work, too.
If you would care about what I said about a name clash, you'd check with LIST MEMORY.

Is the oracle driver ANSI or Unicode? Maybe you have a problem with codepage effects. If I do this MSSQL the VFP side of SQL Passthrough changes the SQL it sends to @P1 instead of ?var and passes in the variable separately to a adhoc statement. Something similar to SQL Profiler for Oracle should show you what happens on the Oracle side like this shows what happens on MSSQL Server:

I execute on VFP side:

public nh
nh =SQLConnect('test')
DO test With 'tempdb'
BROWSE
PROCEDURE test()
   LPARAMETERS tcDB
   SQLEXEC(nH,"use master;"+CHR(13)+CHR(10)+"Select * from sys.databases where name=?tcDB")
ENDPROC

This is what happens SQL Server side (recorded by SQL Profiler):

You see VFP SQL Passthrough translates VFPs parameter syntax to the remote database, SQL Server needs @P1 or other names, not VFP variable names. The parameter is passed in separate.
It works out and has the sys.databases record about MSSQL system database tempdb. No problem.

Are you using synchronous or asynchronous mode? That could also play the role of only getting the result with the second call.

Bye, Olaf.

 

[mplaza]

you used "lpara lcCriteria" wich makes lcCriteria local ( lparameter ) , so it's out of scope for sqlexec - that't why you get the dialog box -

Using parameter instead of lParameter will solve it.













Marco Plaza
@vfp2nofox

 

[Olaf Doschke]

What? No, see my test code, works. SQLExec doesn't need private variables, it can use anything in current scope. We're not talking Cursoradapter here, Marco. Besides, "magically" The cursoradapters FillCursor nethod can see local variables of the method calling ca.cursorfill() anyway, try it. While we're at it, reports also can see local variables of code with the REPORT FORM call, though the report runs in a separate scope of its own.

Bye, Olaf.

 

[mplaza]

I just did, but I see no other reason... why not give a try?


Marco Plaza
@vfp2nofox

 

[Olaf Doschke]

Marco, because even for Ris1, the whole thing works AFTER he copies the parameter to a local variable. Which actually only changes the variable name as lparameter variables also are local.

As that works, maybe the best thing is to do that always. It's not explainable why that should make a difference in the light of what SQL profiler records, the remote backend get's it's own variable and adhoc query anyway. And that won't be different with Oracle.

Eventually on the remote side it doesn't access VFPs memory space at all, even if the SQL server runs local and with sahred memory or named pipe, this isn't about how the remote backend has access to variables, that's all already taken care of on the VFP side by sql passthrough and with ODBC transport of separate adhoc query and parameters.

Bye, Olaf.

 

[Mike Lewis]

Marco, I can't see why the fact that the parameter is local to the procedure should explain the behaviour. The LPARAMETER construct receives the actual value of the parameter, which he is then passing to his SQLEXEC() - regardless of how the parameter is scoped within the procedure.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[mplaza]

Olaf, I agree it should work using local variables, but making the test does not hurt after he tried all w/o success and the behavior is consistent with the variable out of scope. If it works.. it would be interesting to know why. ( btw see that lcCrit is not declared local )

Mike:
sqlexec() receives only one parameter -the sql instruction with parameter names prepended with "?" - so vfp must access that variables from the calling procedure somehow.








Marco Plaza
@vfp2nofox