SQL Programming Manual for TAL
Examples of Dynamic NonStop SQL Programs
HP NonStop SQL Programming Manual for TAL—527887-001
C-35
Detailed Dynamic SQL Program
Page 26 [1] $VOL1.S04.TALDYN 1991-10-15 13:42:28
dyn^tal main procedure
982. 000000 0 0
983. 000000 0 0 ! Declare WHENEVER clauses for error checking on SQL
statements.
984. 000000 0 0 ! Note: Code that allocates memory and checks the input
query must
985. 000000 0 0 ! do separate error checking, because such code does not
involve
986. 000000 0 0 ! statements that set the SQLCODE variable.
987. 000000 0 0
988. 000000 0 0 ! Errors are handled at a label within the main
procedure because
989. 000000 0 0 ! the error handling code itself uses GO TO to return to
990. 000000 0 0 ! enter^input.
991. 000000 0 0 ! We use GO TO for errors because it makes the example
992. 000000 0 0 ! easier to read than the alternative (boolean variable
with
993. 000000 0 0 ! IF checking); however, using GO TO makes it necessary
to
994. 000000 0 0 ! put the WHENEVER clause here instead of in the global
variable
995. 000000 0 0 ! declarations, to avoid inserting an implicit GO TO in
code that
996. 000000 0 0 ! appears before sql^error^handler.
997. 000000 0 0
998. 000000 0 0 EXEC SQL WHENEVER SQLERROR GO TO :sql^error^handler;
999. 000000 0 0
1000. 000000 0 0 PROC dyn^tal MAIN;
1001. 000000 1 0
1002. 000000 1 0 !*****************************************************!
1003. 000000 1 0 ! PROC dyn^tal MAIN !
1004. 000000 1 0 ! Interactively reads SQL statements and dynamically
processes !
1005. 000000 1 0 ! them. Result is sent to the home terminal. !
1006. 000000 1 0 !*****************************************************!
1007. 000000 1 0
1008. 000000 1 0 BEGIN
1009. 000000 1 1 INT in^numvars; ! Number of parameters
1010. 000000 1 1 INT in^nameslen; ! Size of input names buffer
1011. 000000 1 1 INT out^numvars; ! Number of output columns
1012. 000000 1 1 INT out^nameslen; ! Size of output names buffer
1013. 000000 1 1 INT(32) num^fetches; ! count number of fetches per
query
1014. 000000 1 1 INT status;
1015. 000000 1 1 INT temp^sqlcode; ! Saves the value of SQLCODE for
later
1016. 000000 1 1 ! in cases where SQLCODE is set
to 0.
1017. 000000 1 1 ! See sql^error^handler at the end
1018. 000000 1 1 ! of this main procedure
1019. 000000 1 1
1020. 000000 1 1 INT rollback^flag; ! boolean for passing to memory-
1021. 000000 1 1 ! allocation error handler --
decides
1022. 000000 1 1 ! whether we want to re-enter the
1023. 000000 1 1 ! input loop or terminate the
program
1024. 000000 1 1
1025. 000000 1 1 rollback^flag := 0;! initialize to FALSE
1026. 000003 1 1
1027. 000003 1 1 CALL startup^and^define^pool;
1028. 000004 1 1
1029. 000004 1 1 @sbuf := $XADR(buf);
1030. 000010 1 1 @sda^i := @sda^o := NULL^ADDR;
1031. 000015 1 1 @cname^i := @cname^o := NULL^ADDR;
1032. 000022 1 1