Clarion Community Help

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)