! Copyright © Oracle Corporation 1995. All Rights Reserved. ! ABSTRACT: ! ! SQL_TERMINATE illustrates the use of SQL module language with DEC BASIC to ! perform updates to an Oracle Rdb database and also illustrates a number of ! error handling techniques. The program is used to update the PERSONNEL ! database when an employee leaves the company. The user enters the ID number ! and date of termination for the employee. In one transaction, the program ! verifies the ID number and makes the necessary changes in the EMPLOYEES, ! JOB_HISTORY, and SALARY_HISTORY tables. ! ! If verification and all changes execute successfully, the transaction is ! committed. The user may then start a transaction for another employee or ! exit the program. If an error occurs, the user receives an error message, ! a rollback is done, and the user has the option of re-entering values or ! exiting the program. ! ! Some of the error messages returned are maintained in a message file, ! SQL_PERSMSG.MSG, created for use with this program to illustrate the use ! of customized message files with SQL programs. ! ! To keep the program simple, current information for an employee is not ! brought to the screen and the characters and format of the date value input ! by the user is not checked. ! ! The name of the file containing the module language procedures is ! SQL_TERMINATE_BAS.SQLMOD. ! ! OPTION TYPE = EXPLICIT ! Force declaration of variables ! Include the definition module for VMS return status global symbols. ! Individual global symbols can be declared, for example, as follows: ! ! EXTERNAL LONG CONSTANT SS$_NORMAL ! ! but this program includes the entire definition module so that the values ! can be examined. ! %INCLUDE "$SSDEF" %FROM %LIBRARY "SYS$LIBRARY:BASIC$STARLET.TLB" ! Similarly, include the definition interface routines for system services. ! Again, a single routine could be declared, for example, as follows: ! ! EXTERNAL LONG FUNCTION SYS$BINTIM (STRING BY DESC, BASIC$QUADWORD) ! ! but this program includes the entire definition module so that the values ! can be examined. ! %INCLUDE "STARLET" %FROM %LIBRARY "SYS$LIBRARY:BASIC$STARLET.TLB" ! Declare constants and variables for error handling DECLARE LONG VMS_RETURN_STATUS,SQL_RETURN_STATUS DECLARE LONG CONSTANT ID_NOT_FOUND = 100 DECLARE LONG CONSTANT LOCK_CONFLICT = -913 DECLARE LONG CONSTANT DEADLOCK = -1003 ! Constants for program use DECLARE STRING constant UPDATE_CODE = "1" DECLARE STRING constant EXIT_CODE = "8" DECLARE STRING constant TRUE = "TRUE" DECLARE STRING constant FALSE = "FALSE" ! Variables for program use DECLARE BYTE I ! Loop counter DECLARE STRING OPTION_ENTRY,RELEASE_SCREEN DECLARE STRING EMPLOYEE_ID,STATUS_CODE DECLARE BASIC$QUADWORD JOB_END DECLARE STRING EMPLOYEE_EXISTS DECLARE STRING END_DATE,END_DATE_TIME DECLARE STRING GET_ERROR_BUFFER DECLARE LONG MSGID DECLARE WORD MSGLEN ! The buffer to receive the message text from the GETMSG call must be ! fixed-length and therefore in a COMMON, MAP or RECORD declaration MAP (GETMSG_BUF) STRING MSGTXT = 256 ! Declare the global symbol for the message file EXTERNAL LONG CONSTANT PERS_IDNOTFND ! Declare local DEF functions DECLARE STRING FUNCTION MAIN_OPTION_INPUT ! SQL module language procedure declarations EXTERNAL SUB SET_TRANSACTION (LONG) EXTERNAL SUB OPEN_CURSOR (LONG,STRING) EXTERNAL SUB FETCH_EMPLOYEES (LONG,STRING,STRING) EXTERNAL SUB UPDATE_EMPLOYEES (LONG) EXTERNAL SUB UPDATE_JOBHIST (LONG,STRING,BASIC$QUADWORD) EXTERNAL SUB UPDATE_SALHIST (LONG,STRING,BASIC$QUADWORD) EXTERNAL SUB COMMIT_TRANSACTION (LONG) EXTERNAL SUB ROLLBACK_TRANSACTION (LONG) !------------------------------------------------------------------------------- ! Functions used by the main program !------------------------------------------------------------------------------- ! ! Get user's selection DEF STRING MAIN_OPTION_INPUT ! Loop until a valid option is selected SOLICIT_ENTRY: UNTIL (OPTION_ENTRY = UPDATE_CODE OR OPTION_ENTRY = EXIT_CODE) PRINT "" FOR I = 1 TO 24 ! Clear the screen PRINT "INACTIVE STATUS UPDATE PROGRAM" PRINT "" PRINT "Please enter a selection number." PRINT "1 : to update an employee's status to inactive" PRINT "8 : to exit this program" PRINT "" PRINT "Enter option: "; INPUT OPTION_ENTRY ! Accept a selection PRINT "" SELECT_ENTRY: SELECT OPTION_ENTRY CASE = UPDATE_CODE ! For valid selections, just exit EXIT SELECT_ENTRY ! CASE = EXIT_CODE ! EXIT SELECT_ENTRY ! CASE ELSE PRINT "Invalid option. " ! Print an error message PRINT "Press to continue. "; ! and force reentry for INPUT RELEASE_SCREEN ! anything else END SELECT NEXT main_option_input = option_entry option_entry = "" END DEF ! of MAIN_OPTION_INPUT !------------------------------------------------------------------------------ ! Main program !------------------------------------------------------------------------------ ! The main program will loop until the operator requests to exit. ! The main option dialog returns an option code that controls the loop. ! If an update is requested, the employee id and termination date for the ! employee is requested. The database is checked for the employee id and ! is the employee's status and termination date are updated if it is found. SET NO PROMPT ! Disable '?' on input prompts WHILE (MAIN_OPTION_INPUT <> EXIT_CODE) GOSUB GET_EMPLOYEE_INFO GOSUB EMPLOYEE_FETCH GOSUB UPDATE_DATABASE IF (EMPLOYEE_EXISTS = TRUE) NEXT EXIT PROGRAM !------------------------------------------------------------------------------- ! Subroutines called by the main program !------------------------------------------------------------------------------- ! ! This subroutine gets the employee_id and termination date and converts the !latter ! GET_EMPLOYEE_INFO: PRINT "" FOR I = 1 TO 24 PRINT "Please enter the employee id number " & + "of the employee who is now inactive" PRINT "" PRINT "Employee ID: "; INPUT employee_id PRINT "" PRINT "Please enter the job termination date." PRINT "Use format dd-MMM-yyyy (for example 21-OCT-1986)." PRINT "" PRINT "Date: "; INPUT END_DATE PRINT "" END_DATE_TIME = END_DATE + " 00:00:00.00" ! Convert the start date to DATE datatype format using BINTIM in STARLET ! vms_return_status = SYS$BINTIM(end_date_time,job_end) CALL LIB$STOP(vms_return_status BY VALUE) IF (vms_return_status AND 1%) = 0% RETURN EMPLOYEE_FETCH: ! ! Open a cursor to contain the EMPLOYEES row with the ID entered by the user. ! If the FETCH retrieves a row (the cursor is not empty), the ID exists in the ! database. The program assumes that the retieved row is the one the user ! intends to change. ! ! The database is attached when the transaction is started. If the database ! attachment fails or a transaction cannot be started, the ROLLBACK executed in ! LOCK_ERROR_CHECK will also fail. The message vector will then reflect the ! values associated with the failure of the ROLLBACK statement and not the ! SET TRANSACTION statement. Although the program could have displayed ! messages and accepted user input to continue BEFORE executing the ROLLBACK ! statement, doing so would cause the transacttion to span terminal I/O. ! ! Other programs may require more complex code which might move the messages ! retrieved for the SET TRANSACTION statement to a buffer that is not affected ! by execution of the ROLLBACK, monitor execution of the ROLLBACK statement, ! and, if the ROLLBACK fails, display the contents of that buffer to the user ! as well. ! ! Note that a transaction is not started until after the user is prompted for ! all input and any program conversions have been done. This ensures that time ! for terminal I/O is not included in the time it takes to complete the ! transaction. Given the task being performed here (changing data to show an ! employee no longer works for the company), it is unlikely that other users ! need access to the rows locked by transactions that this program starts. ! It is still a good rule, however, to avoid transactions that span terminal ! I/O. ! employee_exists = FALSE CALL SET_TRANSACTION(sql_return_status) IF sql_return_status < 0 THEN GOSUB LOCK_ERROR_CHECK RETURN END IF ! Handle errors that may occur when the cursor is open. LOCK_EROR_CHECK ! executes a ROLLBACK in the case of every fatal error. ! CALL OPEN_CURSOR(sql_return_status,employee_id) IF sql_return_status < 0 THEN GOSUB LOCK_ERROR_CHECK RETURN END IF ! FETCH a row from the opened cursor ! Handle the not found condition and then any unexpected errors that may occur ! when a row is FETCHED from a cursor ! CALL FETCH_EMPLOYEES(sql_return_status,employee_id,status_code) IF sql_return_status = ID_NOT_FOUND THEN CALL ROLLBACK_TRANSACTION(sql_return_status) msgid = PERS_IDNOTFND GOSUB DISPLAY_ATEND_MESSAGE RETURN END IF IF sql_return_status < 0 THEN CALL ROLLBACK_TRANSACTION(sql_return_status) GOSUB LOCK_ERROR_CHECK RETURN END IF ! Success to this point means that a row was returned by the FETCH. This ! program assumes that it is the employee to be updated and checks no further. ! Simply return with the employee_exists true. ! employee_exists = TRUE RETURN UPDATE_DATABASE: ! ! UPDATE_DATABASE modifies the EMPLOYEES, JOB_HISTORY, and SALARY_HISTORY rows, ! and then, if no errors are encountered, commits the changes. Note that this ! program does not check to make sure that there are current rows for an ! employee in the JOB_HISTORY and SALARY_HISTORY tables or that STATUS_CODE in ! the EMPLOYEES table is not already 0. If there are no current rows in ! JOB_HISTORY or SALARY_HISTORY (rows where JOB_END or SALARY_END are null), no ! rows are updated in those tables. If STATUS_CODE is already 0, it is set to ! 0 again. Depending on how any database is set up and maintained, programs ! may need to perform more checks than are illustrated in this sample. ! ! UPDATE the EMPLOYEES table ! CALL UPDATE_EMPLOYEES(sql_return_status) IF sql_return_status < 0 THEN GOSUB LOCK_ERROR_CHECK RETURN END IF ! UPDATE the JOB_HISTORY table ! CALL UPDATE_JOBHIST(sql_return_status,employee_id,job_end) IF sql_return_status < 0 THEN GOSUB LOCK_ERROR_CHECK RETURN END IF ! UPDATE the SALARY_HISTORY table ! CALL UPDATE_SALHIST(sql_return_status,employee_id,job_end) IF sql_return_status < 0 THEN GOSUB LOCK_ERROR_CHECK RETURN END IF ! COMMIT the TRANSACTION ! CALL COMMIT_TRANSACTION(sql_return_status) IF sql_return_status < 0 THEN GOSUB LOCK_ERROR_CHECK RETURN END IF RETURN LOCK_ERROR_CHECK: ! ! Using equality to check the value of sql_return_status assumes that the ! severity of errors will not change. While this check is faster and more ! convenient than calling the run-time library routine LIB$MATCH_CONDITION, ! this check will fail if the severity level of the errors changes in a future ! software release. ! ! PRINT "" FOR I = 1 TO 24 IF (sql_return_status = LOCK_CONFLICT) OR (sql_return_status = DEADLOCK) THEN PRINT "A lock condition has occurred. "; ELSE CALL ROLLBACK_TRANSACTION(sql_return_status) CALL SQL$GET_ERROR_TEXT(get_error_buffer) PRINT "This condition was not expected." PRINT "" PRINT get_error_buffer END IF PRINT "" PRINT "Press to continue."; INPUT RELEASE_SCREEN RETURN DISPLAY_ATEND_MESSAGE: ! Use the system routine SYS$GETMSG to retrieve a message in a user-defined ! message file (SQL_PERSMSG). ! vms_return_status = SYS$GETMSG(msgid,msglen,msgtxt,15,) CALL LIB$STOP(vms_return_status BY VALUE) IF (vms_return_status AND 1%) = 0% PRINT "" FOR I = 1 TO 24 PRINT msgtxt PRINT "" PRINT "Error occured when attempting to fetch -- ";employee_id PRINT "" PRINT "for employee ID -- ";employee_id PRINT "" PRINT "Press to continue."; INPUT RELEASE_SCREEN RETURN END ! of SQL_TERMINATE