Calling Stored Procedures
Stored procedures can return an output parameter and return results. These can only be returned if the file is opened in Read-Only mode (10H). The return result of a stored procedure is not available until all records have been retrieved from any SELECT statement in the stored procedure.
PROGRAM
MAP
CheckError(STRING) !Check for errorcodes
CallProc(STRING) !Call Stored Procedure
END
MyFile FILE,DRIVER('MSSQL'),NAME('SProctable')
Record RECORD
c LONG
END
END
Ret LONG
In SHORT(10)
Out STRING(10)
CreateReq BYTE(FALSE)
CODE
BIND('Ret',Ret)
CheckError('BIND Ret')
BIND('Out',Out)
CheckError('BIND Out')
BIND('In',In)
CheckError('BIND In')
MyFile{PROP:SQL} = 'DROP TABLE SProctable'
MyFile{PROP:SQL} = 'CREATE TABLE SProctable (c INT)'
!Give MyFile initial data
SHARE(MyFile)
CheckError('Open')
MyFile.c=5
ADD(MyFile)
CheckError('Add 5')
MyFile.c=7
ADD(MyFile)
CheckError('Add 7')
MyFile.c=8
ADD(MyFile)
CheckError('Add 8')
!Initialize and Create Stored Procedures
MyFile{PROP:SQL} = 'DROP PROC SProc1'
MyFile{PROP:SQL} = 'DROP PROC SProc2'
MyFile{PROP:SQL} = 'DROP PROC SFunc1'
MyFile{PROP:SQL} = 'DROP PROC SFunc2'
MyFile{PROP:SQL} = 'DROP PROC SFunc3'
CallProc('CREATE PROC SFunc1 @input VARCHAR(10),@output VARCHAR(10) OUTPUT AS ' &|
'SELECT @output = CHAR(ASCII()
)+c) FROM SProctable WHERE c=7 ' &|
'RETURN ASCII(@input) ')
CallProc('CREATE PROC SFunc2 @sin INT, @strin VARCHAR(10) AS ' & |
' SELECT c FROM SProctable RETURN @sin + ASCII(@strin)')
CallProc('CREATE PROC SFunc3 @input VARCHAR(10) AS ' & |
' RETURN ASCII(@input) ')
CallProc('CREATE PROC SProc1 @inp INT AS ' & |
' INSERT INTO SProctable values(@inp) ')
CallProc('CREATE PROC SProc2 @inp INT AS ' & |
' INSERT INTO SProctable values(@inp) ' & |
' SELECT c FROM SProctable')
!Call Stored Procedure passing input value using NORESULTCALL
!sets output parameter
CallProc('&Ret = NORESULTCALL SFunc3(1
)')
IF Ret ~= VAL('1')
MESSAGE('Ret of NORESULTCALL SFunc3 =' & Ret)
END
!Call Stored Procedure passing input value, no return values
CallProc('CALL SProc1(10)')
!Call Stored Procedure passing input value, return return code
CallProc('CALL SProc1(&in[IN])')
!Call Stored Procedure passing input value, return output parameter
ret = 0
CallProc('&Ret = CALL SFunc1(1
,&out)')
IF Ret ~= VAL('1')
MESSAGE('Ret of SFunc1 =' & Ret)
END
IF out ~= CHR(VAL(')')+7)
MESSAGE('out of SFunc1 =' & out)
END
!Call Stored Procedure passing input value, return return code
CallProc('CALL SProc2(&in[IN])')
NEXT(MyFile)
CheckError('Next SProc2')
ret = 0
!Call Stored Procedure passing input values, return output parameter
CallProc('&Ret = CALL SFunc2(7, )
)')
IF Ret ~= 0
MESSAGE('out of SFunc2 before fetches =' & Ret)
END
LOOP WHILE ~ERRORCODE()
NEXT(MyFile)
END
IF Ret ~= VAL(')')+7
MESSAGE('out of SFunc2 =' & Ret)
END
MESSAGE('Done')
!Check for errorcodes
CheckError PROCEDURE(Msg)
CODE
IF ERRORCODE()
IF ERRORCODE() = 90
HALT(1, Msg & ' ' & FILEERRORCODE() & ':' & FILEERROR())
ELSE
HALT(1, Msg & ' ' & ERRORCODE() & ':' & ERROR())
END
END
!CallProc calls the stored procedures using the PROP:SQL statement
CallProc PROCEDURE(Str)
CODE
MyFile{PROP:SQL} = Str
CheckError(Str)