×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

'?' Operator in SqlExec() function not working

'?' Operator in SqlExec() function not working

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

CODE -->

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

** 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.

RE: '?' Operator in SqlExec() function not working

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

RE: '?' Operator in SqlExec() function not working

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

RE: '?' Operator in SqlExec() function not working

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.

RE: '?' Operator in SqlExec() function not working

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

RE: '?' Operator in SqlExec() function not working

>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.

RE: '?' Operator in SqlExec() function not working

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

RE: '?' Operator in SqlExec() function not working

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:

CODE

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

Bye, Olaf.

RE: '?' Operator in SqlExec() function not working

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.

RE: '?' Operator in SqlExec() function not working

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

RE: '?' Operator in SqlExec() function not working

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:

CODE

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 Smileing

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.

RE: '?' Operator in SqlExec() function not working

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-Pa...

Regards

Griff
Keep Smileing

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.

RE: '?' Operator in SqlExec() function not working

(OP)

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


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.

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

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

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

No, I am not using #define lcCriteria



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

CODE

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

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

RE: '?' Operator in SqlExec() function not working

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:

CODE

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.

RE: '?' Operator in SqlExec() function not working


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

RE: '?' Operator in SqlExec() function not working

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.

RE: '?' Operator in SqlExec() function not working



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

Marco Plaza
@vfp2nofox

RE: '?' Operator in SqlExec() function not working

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.

RE: '?' Operator in SqlExec() function not working

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

RE: '?' Operator in SqlExec() function not working


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

RE: '?' Operator in SqlExec() function not working

Quote (Marco)

vfp must access that variables from the calling procedure somehow.
Yes, VFP, but not the ODBC driver or the SQL server. This happens on the way from VFP to ODBC, just like creating result cursor is done in the VFP runtime and not from any ODBC driver. If you think that SQL goes straight into the ODBC driver, you think too simple about the inner workings of SQL Passthrough. This is all happening in the SQLExec before getting to ODBC and thus can't depend on the remote backend or ODBC driver, so Oracle can't have a behavior of only working with private variables. I go out on a limb and say that's simply impossible, even without knowing the exact details about the VFP runtime C++ implementation of feeding the ODBC driver, this can't have a dependency on the remote backend or ODBC driver. The driver might have an influence about how the VFP syntax is translated. But I bet that's even not remote backend specific but ODBC specific.

And the VFP runtime has access to all variables anyway, via nametable, not only variables in scope.

I've shown what the server side sees, just look back. That has nothing to do with the VFP variables anymore anyway.
I would now bet much more on synchronous execution than any variable scope problem.

Bye, Olaf.

RE: '?' Operator in SqlExec() function not working

The only way I can get to reproduce the behavior you're observing - that is, presenting an InputBox to set the parameter value - is to force a typo on the parameter's name (for instance, 1cCriteria instead of lcCriteria).

RE: '?' Operator in SqlExec() function not working

Rs1,

if you'd still like a VFP expert to take a chance at finding out what happens here, could you specify the Oracle version and ODBC driver used for this? Atlopes has a big point about the input box behaviour, which is much in favor of a typo you don't show here. Synchronous execution also won't explain that inputbox for missing parameter coming up.

Bye, Olaf.

RE: '?' Operator in SqlExec() function not working

(OP)

Quote:

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.]

Thanks mplaza
I think this was problem. On replacing lparameters with Parameters , now both the queries are fetching records.
Also when I define local lcCrit , second query starts for asking Get Parameter value I/P box.
So the problem was in local variable with was not sensed by SQLEXEC function.

But then as Olaf code is working ,then why not mine? What might be the reason behind it?

RE: '?' Operator in SqlExec() function not working

(OP)
In My test.prg which is independent of my app, Lparameters is working fine as in Olaf's Code. Oracle version is 11g and Asynchronous mode is .F.

RE: '?' Operator in SqlExec() function not working

Quote (Ris1)

In My test.prg which is independent of my app, Lparameters is working fine as in Olaf's Code.

As it should. I think this has nothing to do with variable scoping.

Please note, in your original post, that the second SQLEXEC() may actually not be working at all, contrary to what you're thinking. Since a) you're not checking on SQLEXEC return value; and b) you're not using different cursors on both calls, the second cursor may still be the one that was generated in the first call, set up by the parameter you inserted in the InputBox.

RE: '?' Operator in SqlExec() function not working

Common, the most important thing to know is the exact ODBC driver you're using, that's the module VFP is directly involved with. Notice (once more, VFP doesn't simply send the query string with ?vfpvariablename, this is translated on the way to ODBC already. Must be.

Bye, Olaf.

RE: '?' Operator in SqlExec() function not working

atlopes, DispWarning=.t. is working out fine to error in case the second SQLExec would not work at all and didn't overwrite the SQLResult cursor. It would be worth checking whether specifying two differing cursornames would reveal both SQLExecs returning data, though it wouldn't change much in terms of the first sqlexec returning data after the input for parameter value is answered. The expected outcome would be two result cursors, that would still give no idea why the first sqlexec would prompt for parameter values, though.

The simplest solution is, the EXE running in the field is compiled from source code having a typo in the parameter name and updating it will make that work.

Bye, Olaf.

RE: '?' Operator in SqlExec() function not working

(OP)
I got where the problem was. When I was using test prg I was using SQLEXEC() function so every thing was working fine. In my app I had created a class which created connection , used sqlexec() to fetch data, provision to handle materialized views and handle errors. Now when I was passing my qry to class's SQLEXT method , my local variables were out of scope in that procedure. So the problem was coming.

So should I remove class and directly execute SQLEXEC()


RE: '?' Operator in SqlExec() function not working

Ah, that's an easy resolving of the underlying problem.

Then in fact private variables help you, because their scope is level of definition and every further call level on the stack. So parameters instead of lprameters would help. A more general solution would need multiple variables, and so overall it wold help if you use private variables for parameters and then the real execution of SQLExec can be in the next or even further down the callstack.

But then nobody can see through this, as you just didn't tell what's really happening.

I wouldn't revert from putting SQLExec into a class, there's a good reason for working that way as you can do general (for any query) but still specific (for sqlexec) error handling within a method wrapping SQLExec, whereas you need to use a few lines for that in every instance of using SQLExec.

I already mentioned Cursoradapters have a slight magic about them in a query you store into a cursoradapter SelectCmd will be able to address it, even though the execution is buried in the native behaviour of the CursorFill method. So once you have a fully prepared cursoradapter object you can do this:

CODE

Local loCA
loCA = CreateObject("yourbasecursoradapter")

Local lcCriteria
lcCriteria = "START"
loCA.Alias = "crsStartvariables"
loCA.SelectCmd = "Select * from Variables where Criteria = ?lcCriteria"
loCA.CursorFill() 

So instead of a wrapper of the SQLExec function you could do a general data access class with a CA that does a) make a connection (or uses a public handle), b) may have a prebuilt SelectCmd and result cursor name and even result cursor schema and then c) is used just by creating parameter variables and calling CursorFill(). So a CA class for every query you want under full control about the result cursor schema. A general CA for any query result without that prepopulated schema also is possible, because you can change the SelectCmd and Alias properties on the fly, which already are the main parameters of any SQLExec, you can also make them init parameters, and then you have a two line SQLExec with line 1 creating the CA object and line 2 calling CursorFill on the level of parameter variable definitions. The error handling then can be in the error method of the ca base class and with the help of inheritancce you can have a nice family of data access business objects each with specific error handling and other rules checking, too.

Alone being able to specify the result cursor schema can help when SQLExec infers types not working, eg in MSSQL varbinary(max) may not come over to VFP as blob fields, especially using Native Client drivers instead of the legacy SQL Server ODBC driver series. (Also a reason I asked for your ODBC driver, there can be very differing behavior about them).

Bye, Olaf.

RE: '?' Operator in SqlExec() function not working

Here's an idea for a general CA base class:

CODE

#Define ccDefaultDSN "yourDSN"

DEFINE CLASS sqlquery as CursorAdapter 
   PROCEDURE init()
      LPARAMETERS tnHandle, tcSQL, tcAlias
      
      This.DataSourceType="ODBC" 
      This.DataSource = EVL(tnHandle,SQLCONNECT(ccDefaultDSN))
      This.SelectCmd = tcSQL
      This.Alias = EVL(tcAlias,"SQLResult")
   ENDPROC 
ENDDEFINE 

Usage:

CODE

LOCAL loQuery, lcCriteria
lcCriteria = 'START'

loQuery = CREATEOBJECT("sqlquery",(nHandle),"Select * From Variables where Criteria = ?lcCriteria","crsVariables")
loQuery.CursorFill() 

As said Error handling can be in the query class Error method. Errors, unfortunately, do not trigger the error event, you get .F. from cursorfill and then would need to look into AERROR as after a SQLExec, any way you have lots of advances from SQLExec with a query class. You'll have 2 lines instead of one, now, but if you have a fully fledged DSN with credentials connecting you to a DB, you can pass in 0 for the handle and SQLCONNECT(ccDefaultDSN) connects you. You may alter this in any way, eg make the connection handling a separate topic and always use a globally accessible nHandle or _screen.nSQLHandle or such.

Just don't get tempted to make the CursorFill call inside the Init(), too, or you get back to the variable scoping problem.

Once you have the cursor you can also do things like:

CODE

lcCriteria = 'END'
Requery("crsVariables") && or Requery(loQuery.Alias) 
Which you can't do with SQLExec. and that's not the most impressive thing you can do, the CA class has several events SQLExec or remote or local views don't offer.

Bye, Olaf.

RE: '?' Operator in SqlExec() function not working


Hi Ris1, glad it helped you to discover and solve the problem.

Marco Plaza
@vfp2nofox

RE: '?' Operator in SqlExec() function not working

I see, but the solution should not be to have a method with private instead of local parameters. You don't want every parameterized query to a) only have one parameter and b) let that always be the same name.

Even sticking with your code, you would best remove the parameterization completely and rely on the responsibility of the caller to predefine the SQL parameters as privately scoped variables before calling your SQLEXT() method. Private variables, in general, are used where you DON'T want and can do a specific parameterization aka method signature, because you may have any number and names of parameters in a SQL query.

Bye, Olaf.

RE: '?' Operator in SqlExec() function not working

2
You could also handle parameters as other implementations handle them, e.g., see .NET SQLCommand, OledbCommand or ADODB.Command. Let them be part of the object and offer an interface to add and set them:

CODE

LOCAL loQuery
loQuery = CREATEOBJECT("sqlquery",0,"Select * From Variables where Criteria = ?par.cCriteria","crsPatient")
loQuery.AddParam("cCriteria","START")
? loQuery.Exec()
BROWSE
loQuery.par.cCriteria = "END"
? loQuery.Exec()
BROWSE

#Define ccDefaultDSN "yourDSN"

DEFINE CLASS sqlquery as CursorAdapter 
   par = .null.

   PROCEDURE init()
      LPARAMETERS tnHandle, tcSQL, tcAlias
      
      This.par = CREATEOBJECT("empty")
      This.DataSourceType="ODBC" 
      This.DataSource = EVL(tnHandle,SQLCONNECT(ccDefaultDSN))
      This.SelectCmd = tcSQL
      This.Alias = tcAlias
   ENDPROC 
   
   PROCEDURE AddParam()
      PARAMETERS tcName, tvValue
      
      IF PCOUNT()=2
         ADDPROPERTY(This.par,tcName, tvValue)
      ELSE
         ADDPROPERTY(This.par,tcName)
      ENDIF
   ENDPROC 
   
   PROCEDURE Exec()
       Local llSuccess, par
       par = This.par
       
       IF USED(This.Alias)
          llSuccess = This.CursorRefresh()
       ELSE
          llSuccess = This.CursorFill()
       ENDIF 
       
       IF NOT llSuccess
          * Do whatever general error handling
          AERROR(laError)
          SET STEP ON
       ENDIF

       RETURN llSuccess
ENDDEFINE 

For convenience the Exec Method will create a variable called par and set it to This.par to shorten the syntax for variables used in a query.

Private variables are also still possible, but even most VFP developers think PRIVATE declares private variables, which shows there isn't a deep understanding in how they work and you sooner or later get in trouble if someone is contributing to a project in ways not working. An AddParam method makes it clear what needs to be done to add a parameter and looking at existing code it's easy to grasp how to address them and use VFPs paramterization in this context with a parameter object par.

Bye, Olaf.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close