xsharp.eu

Code: Select all

  * 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

Code: Select all

sqlexec(x, "select * from table where key= ?(LocalAlias.field)")

Code: Select all

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

Code: Select all

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

Code: Select all

LOCAL lc1
lc1 = "test"
= withPara(lc1)
= inBrackets (lc1)

FUNCTION InBrackets ( tc1)
? PROGRAM()
 = testPara()

FUNCTION WithPara
PARAMETERS tc1
? PROGRAM()
 = testPara()

FUNCTION testPara
? PROGRAM()
? tc1

Code: Select all

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

Code: Select all

     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