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

 

[Olaf Doschke]

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.

 

[atlopes]

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

 

[Olaf Doschke]

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.

 

[Ris1]

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?

 

[Ris1]

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.

 

[atlopes]

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.

 

[Olaf Doschke]

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.

 

[Olaf Doschke]

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.

 

[Ris1]

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

 

[Olaf Doschke]

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:

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.

 

[Olaf Doschke]

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

#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:

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:

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.

 

[mplaza]

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



Marco Plaza
@vfp2nofox

 

[Olaf Doschke]

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.

 

[Olaf Doschke]

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:

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 
   [highlight #FCE94F]par = .null.[/highlight]

   PROCEDURE init()
      LPARAMETERS tnHandle, tcSQL, tcAlias
      
      [highlight #FCE94F]This.par = CREATEOBJECT("empty")[/highlight]
      This.DataSourceType="ODBC" 
      This.DataSource = EVL(tnHandle,SQLCONNECT(ccDefaultDSN))
      This.SelectCmd = tcSQL
      This.Alias = tcAlias
   ENDPROC 
   
[highlight #FCE94F]   PROCEDURE AddParam()
      PARAMETERS tcName, tvValue
      
      IF PCOUNT()=2
         ADDPROPERTY(This.par,tcName, tvValue)
      ELSE
         ADDPROPERTY(This.par,tcName)
      ENDIF
   ENDPROC[/highlight] 
   
   PROCEDURE Exec()
       Local llSuccess, par
       [highlight #FCE94F]par = This.par[/highlight]
       
       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.