Welcome, Guest
Username: Password: Remember me
This public forum is meant for questions and discussions about Visual FoxPro
  • Page:
  • 1

TOPIC:

sqlexec parameters input / output 11 Jun 2021 17:10 #18799

  • jpmoschi


  • Posts: 49
  • good morning Forum
    In FoxPro you can pass parameters in a sqlexec function like the next example cutted and pasted from VFP9 help on line.

    Are there any equivalent support in xsharp? I recognice that it's related with sql driver and all are diferents but particulary is usefull in sql server.
    * Execute stored procedure with an INPUT parameter.
       SQLEXEC(m.lnConn, 'exec byroyalty ?lnPercent','HalfOffAuthors')
       
       * Create temp stored procedure with OUTPUT parameter and call it.
       SQLEXEC(m.lnConn, "CREATE PROCEDURE #MyProc @outparam int OUTPUT AS;
          SELECT @outparam=100")
       SQLEXEC(m.lnConn, "exec #myProc ?@lnOutput")
       ? m.lnOutput
       
       * Create a temp stored procedure with INPUT and OUTPUT parameters 
       * and call it.
        SQLEXEC(m.lnConn, "CREATE PROCEDURE #MyProc2 " + ;
                          "@inputparam INT, " + ;
                          "@outparam int OUTPUT " + ;
                          "AS SET @outparam=@inputparam*10")
        SQLEXEC(m.lnConn, "exec #myProc2 ?lnPercent, ?@lnOutput")
        ? m.lnOutput
    
    You can do something like this too:
    sqlexec(x, "select * from table where key= ?(LocalAlias.field)")

    Please Log in or Create an account to join the conversation.

    sqlexec parameters input / output 11 Jun 2021 20:29 #18800

    • robert
    • robert's Avatar


  • Posts: 2463
  • Juan,
    I have worked on this, but I am not sure how far this is at this moment.
    One of the challenge here is that the variable names lnOutPut and lnPercent are "hidden" inside the strings.
    If these variables are of type LOCAL then they are normally "hidden" to the SqlExec() function. If they are Memory variables (public / private) or fields then they could be seen by SqlExec().
    We have added a mechanism to the runtime so it can access locals inside functions such as Type().
    SqlExec() has also been marked with this special attribute, so it should see the parameters,
    I know that inside SqlExec() the code checks for the '?' and '@' characters to detect parameters and ref/out modifiers.
    I have not recently tested this, so I am not sure if it does exactly what you expect.
    I'll have a look at this asap.

    Robert
    XSharp Development Team
    The Netherlands

    Please Log in or Create an account to join the conversation.

    sqlexec parameters input / output 11 Jun 2021 23:15 #18801

    • jpmoschi


  • Posts: 49
  • Robert, i am reading now your comments in www.xsharp.eu/forum/public-vfp/2265-inpu...-local-support#16751 because it is related with this reply.
    We need to have the FoxPro visibility and the parameters inside a query string passed to SqlExec. This is one of the main reasons.
    FoxPro, inside a Function, Method or Procedure can get or assign values to declared variables in any moment. The only condition is the visibility expressed by:
    • public
    • privates in all the call stack
    • parameters in all the call stack. This hide variables declared in the call stack
    • locals in the actual procedure, function or method
    • fields: I put it here because this is a particular case of any object reference declared in the call stack or in current workarea or in any otherarea like Alias.field or Alias->field

    Verbatim quote from FoxPro's Help Online

    Private command Remarks
    Items within VarList are separated by commas. The hiding of variables created in higher-level programs enables variables of the same name as the private variables to be manipulated in the current program without affecting the values of the hidden variables. Once the program containing PRIVATE has completed execution, all variables and arrays that were declared private are again available.

    PRIVATE doesn't create variables; it simply hides variables declared in higher-level programs from the current program.

    Local command Remarks
    You can use and modify local variables and variable arrays only within the procedure or function in which they are created and cannot be accessed by higher or lower-level programs. After the procedure or function containing the local variables and arrays completes execution, local variables and arrays are released.
    Variables and arrays created with LOCAL are initialized to False (.F.).
    You must declare any variables or arrays that you want local prior to assigning values to them. If you assign a value to a variable or array in a program and later declare it as local using LOCAL, Visual FoxPro generates a syntax error.
    You can pass local variables by reference.

    Variable Declaration
    You can compare how variable declaration differs between Visual FoxPro and other programming languages. In Visual FoxPro, you do not assign a data type to a variable. However, it is recommended that you name your variable with a prefix suggesting the data type you plan to use it with. Other languages require that you assign a data type to a variable when you declare it. For more information, see Variable Naming Conventions.

    Note:When you store a value to a variable and the variable does not exist, Visual FoxPro implicitly declares it with PRIVATE scope. Other languages that require explicit declaration of variables return an error. To create variables in Visual FoxPro with PUBLIC or LOCAL scope, you must explicitly declare them with the PUBLIC or LOCAL command. For more information, see PUBLIC Command and LOCAL Command.

    Visual FoxPro
    Variables are implicitly declared with no data typing.
    BASIC
    Variables can be implicitly declared, and the variable name dictates data type.
    Pascal
    Variables must be explicitly declared and assigned a data type.
    C/C++
    Variables must be explicitly declared and assigned a data type.

    Please Log in or Create an account to join the conversation.

    sqlexec parameters input / output 12 Jun 2021 09:04 #18806

    • robert
    • robert's Avatar


  • Posts: 2463
  • Juan,

    We are aware of this.
    The biggest problem with the way FoxPro works is that it treats its own functions (like Type(), SQLExec()) different from functions created by others. The native FoxPro functions can access local variables.
    This is VERY different from other environments.
    At this moment we have implemented a way to emulate that in .Net by marking a function that needs access to local variables with a special attribute.
    The attribute is declare here:
    github.com/X-Sharp/XSharpPublic/blob/mai.../Attributes.prg#L167

    You can see that SqlExec() has already been decorated with this attribute:
    github.com/X-Sharp/XSharpPublic/blob/mai...QLFunctions.prg#L101

    When the compiler detects a call to a method that "needs access to locals" it will add some special code that will register the local variables in the calling function/method in a list inside the runtime (name/value pairs).
    If the function can update the local variables (such as the SQLExec() function can for parameters passed by reference) then after the function call the compiler will insert code check if any variables were updated. When this is the case then all the locals that were registered will be updated from the list inside the runtime.
    Finally the list will be cleared.

    Inside the code that "needs access" the local will be accessible the same way that publics and privates are accessed. This works well.

    You can see the functions that handle this in:
    github.com/X-Sharp/XSharpPublic/blob/mai...ilerSupport.prg#L407

    Please note that the comments about the /fox2 command line option are no longer valid. The compiler does not need this option anymore. At this moment when it detects a method/function with the attribute it will automatically do this when the /memvar compiler option is enabled

    The locals are registered in a special collection inside the same class where memory variables are stored:
    github.com/X-Sharp/XSharpPublic/blob/mai....RT/Types/MemVar.prg .

    Robert
    XSharp Development Team
    The Netherlands

    Please Log in or Create an account to join the conversation.

    sqlexec parameters input / output 15 Jun 2021 00:17 #18842

    • jpmoschi


  • Posts: 49
  • thanks Robert,
    The attribute NeedAccessToLocals could not be enough . We need access to all variables. This is different to another languages and is most important. I abandon the initial question because if you can resolve the next comment i will resolve the sqlexec problem with another solution used to interact with Oracle that not support parameters like anothers

    In FoxPro Environment there is something like a Table with 4 attributes with a max capability of MVCOUNT (a config number parameter <= 65000)
    • VariableName,
    • VariabieVisibility,
    • VariableValue,
    • CallStack creation id
    The variable are created in yours first assign action. The initial value of not assigned variables is by default .f.
    The variable visibility declaration only create the variable name and if a variable hasn't declared visibility (private, public or local) the default visibility is private.
    Ussing a bit of imagination, FoxPro does somethink like this

    The Public callstack id is the first id, ej 1.
    The private callstack id is the id who has private command declaration (resolved in Execution time flow )
    The locals is similar to another but it is in the table with a particular id example different for each procedure
    Then, Inside a procedure, function or method you can see variable names with the condition
    CallStack Id <= the procedure Callstack id or has local id especific


    Next a POC with a bug: parameter by ref not change private or public variables
    USING System
    USING System.Collections.Generic
    USING System.Text
    
    FUNCTION CodeFile1 (p1, p2)
        local lresult  
        private mresult
        public _result
        LOCAL localvar1, localvar2
        PRIVATE privatevar1, privatevar2
        PUBLIC publicvar1, publicvar2
        publicvar1= "p1"
        publicvar2:= "p2"
        STORE 'a' TO privatevar1
        privatevar2:= 'b'  + 'c'   
        localvar1:= 12
        localvar2:= 4
        namevar:="localvar1"
        &namevar.:= custom{}
        ? "Test macrosubstitution object assign ", Type(namevar) , iif(Type(namevar)="O", "Ok", "Fail")
        &namevar.:= 6         
        ? "Test macrosubstitution assign ",  localvar1, iif(localvar1= 6, "Ok", "Fail li must be change Before 12 After 6")  
        
        lresult:= false             
        lresult:= "asdf"            
        lresult:= datetime()        
        lresult:= 1                 
        store .f. to lresult, mresult, _result 
        //call 1
        prueba ("localvar1",localvar1,localvar2,@lresult )       
        ? "Test local variable changed ina a procedure by ref with @" , "lresult=", lresult, "Type(""lresult"")="+Type("lresult"), iif(Type("lresult")="N" and lresult=10, "OK", "Fail ""lresult"" must be changed to 10")
        // call 2
        prueba ("privatevar1",privatevar1,privatevar2,ref mresult ) 
        ? "Test private variable changed in a procedure by ref"      , "mresult=", mresult, "Type(""mresult"")="+Type("mresult"), iif(Type("mresult")="C" and mresult="abc" , "OK", "Fail ""mresult"" must be changed to ""abc"" ")
        // call 3 
        prueba ("publicvar1",publicvar1,publicvar2,@_result )    
        ? "Test public variable changed in a procedure by ref with @", "_result=", _result, "Type(""_result"")="+Type("_result"), iif(Type("_result")= "C" and _result="p1p2", "OK", "Fail ""_result"" must be ""p1p2"", not change in line before ")
        wait
        RETURN 
    //************************************************
    procedure prueba (pVarId,p1, p2, pout ref usual  )
      pout:= p1 + p2
      ? "Test visibility of variable " + pVarId + " inside a procedure" , iif(Type(pVarId)="U"," is not visible",  "is visible" )
      return

    The output is

    Hello World! Today is 14/06/2021
    Test macrosubstitution object assign O Ok
    Test macrosubstitution assign 6 Ok
    Test visibility of variable localvar1 inside a procedure is not visible
    Test local variable changed ina a procedure by ref with @ lresult= 10 Type("lresult")=N OK
    Test visibility of variable privatevar1 inside a procedure is visible
    Test private variable changed in a procedure by ref mresult= .F. Type("mresult")=L Fail "mresult" must be changed to "abc"
    Test visibility of variable publicvar1 inside a procedure is visible
    Test public variable changed in a procedure by ref with @ _result= .F. Type("_result")=L Fail "_result" must be "p1p2", not change in line before
    Press any key to continue...

    Please Log in or Create an account to join the conversation.

    sqlexec parameters input / output 15 Jun 2021 08:49 #18843

    • robert
    • robert's Avatar


  • Posts: 2463
  • Juan,
    The private and public passed by reference should have been updated. This is a compiler bug.

    Robert
    XSharp Development Team
    The Netherlands

    Please Log in or Create an account to join the conversation.

    sqlexec parameters input / output 17 Jun 2021 21:17 #18868

    • jpmoschi


  • Posts: 49
  • Robert, another comment related with the visibility of variables that complement my last reply: parameters of procedures or functions are visible too in a procedure called. It must be trated like privates variables in a called procedure. They are visibles in FoxPro
    USING System
    USING System.Collections.Generic
    USING System.Text
    
    procedure parameter_visibility_in_called_procedure AS VOID
        calledProcedure("value parameter1", "value Parameter2")
        RETURN
     Procedure calledProcedure(param1, param2) 
            ?param1
            ?param2 
            calledProcedure2()
            return 
       Procedure calledProcedure2
            ? param1      >>>>>> this two lines produce XSharp.Error: 'Variable does not exist'
            ? param2

    Please Log in or Create an account to join the conversation.

    sqlexec parameters input / output 17 Jun 2021 23:27 #18869

    • mainhatten


  • Posts: 199
  • Joshi,
    passing parameters inside function brackets is equivalent to LPararameter, not Parameter
    From vfp9 help on function:

    Creates a user-defined function in a program file. There are two versions of the syntax.
    FUNCTION FunctionName
    [ LPARAMETERS parameter1 [ ,parameter2 ] , ... ]
    Commands
    [ RETURN [ eExpression ] ]
    [ENDFUNC]

    FUNCTION FunctionName( [ parameter1 [ AS para1type ][ ,parameter2 [ AS para2type ] ],...] ) [ AS returntype ]
    Commands
    [ RETURN [ eExpression ] ]
    [ENDFUNC]

    Parameters
    FUNCTION FunctionName
    Designates the beginning of a user-defined function and specifies the name of the function. FunctionName can contain up to 254 characters.

    [ LPARAMETERS parameter1 [, parameter2] , ... ]
    Assigns data from the calling program to local variables or arrays. You can also use the PARAMETERS keyword instead of LPARAMETERS to accept privately scoped parameters. You can pass a maximum of 26 parameters to a function.

    For more information, see LPARAMETERS Command and PARAMETERS Command.

    ( [ parameter1[ AS para1type][ , parameter2[ AS para2type] ],...] )
    Assigns data from the calling program to local variables or arrays. You can use the AS para1type clause to specify the data type of the variable.

    Note
    Including the parameters inside parentheses (()) immediately following the function name indicates that the parameters are locally scoped to the function.


    run in vfp
    LOCAL lc1
    lc1 = "test"
    = withPara(lc1)
    = inBrackets (lc1)
    
    FUNCTION InBrackets ( tc1)
    ? PROGRAM()
     = testPara()
    
    FUNCTION WithPara
    PARAMETERS tc1
    ? PROGRAM()
     = testPara()
    
    FUNCTION testPara
    ? PROGRAM()
    ? tc1

    jpmoschi wrote: Robert, another comment related with the visibility of variables that complement my last reply: parameters of procedures or functions are visible too in a procedure called. It must be trated like privates variables in a called procedure. They are visibles in FoxPro

    USING System
    USING System.Collections.Generic
    USING System.Text
    
    procedure parameter_visibility_in_called_procedure AS VOID
        calledProcedure("value parameter1", "value Parameter2")
        RETURN
     Procedure calledProcedure(param1, param2) 
            ?param1
            ?param2 
            calledProcedure2()
            return 
       Procedure calledProcedure2
            ? param1      >>>>>> this two lines produce XSharp.Error: 'Variable does not exist'
            ? param2

    Please Log in or Create an account to join the conversation.

    Last edit: by mainhatten.

    sqlexec parameters input / output 18 Jun 2021 08:06 #18870

    • Karl-Heinz


  • Posts: 712
  • Hi Juan,

    i think the X# behaviour is correct.. when i use the PARAMETERS command param1 and param2 are privates, so they are visible inside the Call2 procedure. But when i use the LPARAMETERS command instead, param1 and param2 are locals, so they are not visible inside the call2 procedure.

    param1 and param2 are also locals if such a Procedure/Function header is used

    PROCEDURE call1 ( param1 , param2 )

    regards
    Karl-Heinz

    FUNCTION Start( ) AS VOID 
    	
    DO call1 WITH "value parameter1", "value Parameter2"
    // =call1 ( "value parameter1", "value Parameter2" )
    
    RETURN
    
    // PROCEDURE call1 ( param1 , param2 )  // param1, param2 are locals
    PROCEDURE call1 
    PARAMETERS param1, param2 
    // LPARAMETERS param1, param2
    
    	? param1
        ? param2 
    
        DO call2
    	// =Call2()
    
    RETURN 
    
    
    PROCEDURE call2
    
    	? param1  
    	? param2
    
    RETURN

    Please Log in or Create an account to join the conversation.

    sqlexec parameters input / output 25 Jun 2021 22:18 #18920

    • jpmoschi


  • Posts: 49
  • Rober, the next program is an *SQLEXEC proof of concept with the intention to help proggress with SQLEXEC FoxPro function with parameters referred from memory environment variables with "?"
    This test creates an stored procedure called sp_jpmoschi_xsharptest with input and output parameters
    When it run In xsharp i can find the next errors:
    1st error: TEXT ENDTEXT remove spaces at the end of the line . It is an error less important than the next because you can omit pretext clause
    2nd error: the parameter identified by ? are not recognised. Of course this is the most important error
    3th : when the after mentioned error where solved the next step is verify the output parameter resolution. But i recognise that a programmer can resolve this situation with 2 or 3 lines of code
         PRIVATE _handle
         STORE SqlConnect("Any DataBase MSSQLSERVER") to _handle
        SET DECIMALS TO 10 
        mvarParam= 123
        if sqlexec_with_parameters(@mvarParam)== (mvarParam * mvarParam)
            ? "End sqlExec Test"
        endif 
        ? SQLDISCONNECT(_handle)
        RETURN 
    
    procedure SQLEXEC_WITH_PARAMETERS (pvarParam ) 
         ? "begin SqlExec test"        
         if _handle<0
            ??">>> Fail not connected "
            return -1
         endif 
         set console off 
         local scriptSP, scriptDropSP
         
         && Error in Xsharp Text ... EndText has an small bug: remove spaces at the end of the line
         text to scriptDropSP NOSHOW  pretext 1+2+4+8
            IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME = 'sp_jpmoschi_XSharpTest' ) 
            begin
               DROP PROCEDURE dbo.sp_jpmoschi_XSharpTest
               select @@error
            end
         endtext
         text to scriptSP NOSHOW pretext 1+2+4+8
            CREATE PROCEDURE [dbo].[sp_jpmoschi_XSharpTest]( @pInt Int, @pfloat float, @pDateTime as datetime, @pChar as char(16), @pMemo as varchar(max),@outParam int=0 output, @outTextParam char(200)=' ' output ) AS
            begin
    			DECLARE @r int
                set @outParam = @pInt * @pInt
                SET @r= @@error
                SET @outTextParam= CAST(@r as char(10))
                if @r<> 0 begin
                   SET @outTextParam= 'error in sp_xsharpTest:' + @outTextParam 
                   RaisError (@outTextParam, 16, 1)
                end
                else            
                   if @pfloat= 123456.12345678
                      set @outTextParam= 'OK'
                   else 
                      set @outTextParam= 'error float type ' + CAST(@pfloat as char(30))
            end
         endText 
            
         if SqlExec(_handle, scriptDropSP)>0
            if SqlExec(_handle, scriptSP)>0   && Important Error in XSharp: Does not recognize the parameters identified with the question mark
               private mSPOutputInt, mSPOutText
    		   private mfloat
    		   private mDateTime
                mDateTime= DateTime() 
               private mVar
               mvar= "√ĆNPUT CHAR VALUE"
               private mLongVar
               mLongVar= replicate('long long really long string variable', 1000) 
               ? "Test SqlExec with ?parameters "
               if SqlExec(_handle, "select ?pvarParam + 1 as result, 123456.12345678 as precisionNumber", "sqlresult")< 0
                   ?? ">>> Fail "
                else 
                    if sqlresult.Result= 124
                        ? ">>>> OK" 
                        mfloat= precisionNumber
                    else 
                        ? ">>>> FAIL"
                    endif 
               endif 
                    
               ? "Test call sp with in/out parameters" 
               mSPOutInt= -1
               mSPOutText=""
               if sqlexec(_handle, "{call dbo.sp_jpmoschi_XSharpTest( ?pvarParam, ?mfloat, ?mDateTime, ?mVar, ?mLongVar,  ?@mSPOutInt, ?@mSPOutText  ) }")<=0
                  ?? ">>> Fail"
               else 
                  IF mSPOutInt= 123  * 123
                     ?? ">>> " + mSPOutText
                  ELSE 
                     ?? ">>> Fail ", "mSPOutInt=", mSPOutInt, "mSPOutText=", mSPOutText
                  ENDIF 
                  mSPOutText= "init value"
                  ? "Test call sp with forced ERROR(numeric overflow ) "
                  if sqlexec(_handle, "{call dbo.sp_jpmoschi_XSharpTest( 99999999, ?mfloat, ?mDateTime, ?mVar, ?mLongVar, ?@mSPOutInt, ?@mSPOutText) }")<=0
                     ?? ">>> OK", "mSPOutInt=", mSPOutInt, "mSPOutText=", mSPOutText
                  else 
                      ??">>> Fail, can't by ok", "mSPOutInt=", mSPOutInt, "mSPOutText=", mSPOutText
                  endif 
               endif 
            else 
                ? ">>>Fail sp create  "
            endif
        else 
            ? ">>>Fail sp drop only one argument  "
         ENDIF
         
         RETURN pvarparam * pvarparam

    Please Log in or Create an account to join the conversation.

    sqlexec parameters input / output 30 Jun 2021 09:02 #18926

    • robert
    • robert's Avatar


  • Posts: 2463
  • Juan,
    Thanks for the detailed example.
    I'll split it in several distinct examples for testing our compiler
    - passing mvar by reference
    - text .. endtext
    - sqlexec and parameter detection
    - sqlexec and out parameters
    - local variables as parameters for sqlexec
    - memory variables as parameters for sqlexec
    - precision of values passed and returned by sqlexec
    I'll create examples / tests for each of these and will let you know what works and what doesn't.

    Robert
    XSharp Development Team
    The Netherlands

    Please Log in or Create an account to join the conversation.

    sqlexec parameters input / output 21 Oct 2021 15:47 #20041

    • jpmoschi


  • Posts: 49
  • Good morning guys,
    I just downloaded the new version and I just want to ask you: Was this Topic revised in this version or not?
    If the answer is no, is it possible to know if it will be on the roadmap? and if possible, when it will happen?
    best regards
    Juan

    Please Log in or Create an account to join the conversation.

    sqlexec parameters input / output 21 Oct 2021 15:54 #20043

    • robert
    • robert's Avatar


  • Posts: 2463
  • Juan,
    No this was not fixed. It slipped through, since there was no issue for it in out Github issue tracker.
    I have just created the issue and will look into this asap.
    github.com/X-Sharp/XSharpPublic/issues/822

    Robert
    XSharp Development Team
    The Netherlands

    Please Log in or Create an account to join the conversation.

    • Page:
    • 1