Clarion Community Help

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 output parameters and return results are not available until all records have been retrieved by a SELECT statement.

PROGRAM

MAP

CheckError(STRING) !Check for errorcodes

CallProc(STRING)   !Call Stored Procedure

END

MyFile FILE,DRIVER('MSSQL')

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

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

CLOSE(MyFile)

!Open MyFile in Read-Only mode

OPEN(MyFile,10H)

!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, sets output parameter

CallProc('&Ret = CALL SFunc3(10)')

IF Ret ~= VAL('1')

 MESSAGE('Ret of 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

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

!Call Stored Procedure passing input values, return output parameter

CallProc('&Ret = CALL SFunc2(7, ))')

LOOP WHILE ~ERRORCODE()

 NEXT(MyFile)

END

IF Ret ~= VAL(')')+7

 MESSAGE('out of SFunc2 =' & out)

END

MESSAGE('Done')

!Check for errorcodes

CheckError PROCEDURE(Msg)

CODE

IF ERRORCODE()

IF ERRORCODE() = 90

 MESSAGE(Msg & ' ' & FILEERRORCODE() & ':' & FILEERROR())

ELSE

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