fbpx
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: 34
  • 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: 2222
  • 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: 34
  • 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: 2222
  • 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.

    • Page:
    • 1