Procedimiento para el mantenimiento preventivo de

IPF variables can appear anywhere in a SQL statement after the SQL prefix. The IPF SQL Interface scans each statement and replaces all IPF variable names (except those in the INTO clause of a FETCH statement) with the contents of the variable. (The variables of the INTO clause of a FETCH statement indicate where the results of the FETCH statement are to be placed.)

The IPF SQL Interface uses the following rules for variable substitution:

• Begin the IPF variable name with a special character followed by 1 to 32 letters, numbers, and underscores:

− The special character is % for IPF user variables and $ for IPF system variables.

− The first character following the special character must be a letter.

− You cannot have two underscore characters in a row.

• Use an escape character, which is the value of the IPF variable $SQLESCAPE whose default value is the backslash ( \ ), to remove any special meaning from a special character.

• Use a tilde ( ~ ) to mark the end of a variable name when the character that immediately follows could be part of the variable name.

For example, suppose you make the following assignments: set %v1:="variable1"

set %v2:="variable2" set %v3:="variable3"

The following table illustrates the results of various combinations before and after substitution:

Before After Substitution

%v1 %v2 variable1 variable2 %v1\%v2 variable1%v2 %v1\\%v2 variable1\variable2 %v1\%v2%v3 variable1 %v2variable3 %v1~abc variable1abc %v1\~abc variable1~abc

11.2.1.

Status Variables

The IPF SQL Interface places status information about the last SQL statement processed in the following IPF system variables:

• $SQLERROR contains the last error number reported to you by the IPF SQL Interface.

This error number is the number that appears in the prefix of an IPF SQL Interface error message.

• $SQLCODE contains the SQL standard error code number:

− 0 means that the SQL command executed normally.

− +100 means that either no data or no further data exists. This status is returned whenever the interpreter interface returns ‘6001’ or if a DELETE, INSERT, or UPDATE statement does not affect a row.

− n, where n is a negative number, is the error code that the interpreter interface would have returned ($SQLRETURNCODE).

• $SQLRETURNCODE contains the status code returned from RDMS.

• $SQLAUXILIARY contains the auxiliary status information returned by RDMS. Table 11-1 explains how to interpret the value in $SQLAUXILIARY.

Table 11–1. Error Codes and Auxiliary Status Information

$SQLRETURNCODE Contents of $SQLAUXILIARY

0000 For statement

UPDATE, the number of updated rows. INSERT, the number of inserted rows. DELETE, the number of deleted rows. UNLOAD, the number of unloaded rows.

REVOKE, the number of internal rows deleted, indicating that privileges were revoked.

1 to 1999 The column number where the syntax error was encountered in the statement.

6000 to 6999 The number

0 means that RDMS did not roll back the thread. Unless

$SQLRETURNCODE indicates an end-of-cursor condition (6001), the statement was not executed.

1 means that the statement was not executed to completion, and RDMS rolled back any changes made by the statement before execution was interrupted.

2 means that RDMS rolled back the thread. The statement was not executed. Any changes made to the database since the last rollback or commit are discarded. All cursors are closed.

8000 to 8999 Not applicable.

All other values The column number indicating how much of the statement was parsed before the error was found. Note that a column number is not always relevant.

Note: _{The IPF system variable $SQLSCREEN controls how IPF SQL Interface query}

results are displayed. It is used in conjunction with IPF screen mode. $SQLSCREEN can be set to either TRUE or FALSE, with FALSE as the default. In IPF line mode,

$SQLSCREEN is ignored. In IPF screen mode, $SQLSCREEN controls the query result display. For more information on $SQLSCREEN, see 10.4.2.

11.2.2.

Indicator Variables

Indicator variables can be used on SQL CALL, FETCH, and SET statements. An indicator variable is required by columns that can contain a null value; it is optional for columns that do not have null values. If a column in a row contains a null value, the indicator variable for that column has a negative value.

Examples

The following example illustrates a SQL FETCH statement using indicator variables (IPF indicator variables are prefaced with IND):

SQL FETCH c1 INTO %col1 %ind1, %col2 %ind2

The next example illustrates a sequence of statements that

• Creates a table

• Populates the table

• Fetches rows from the table using indicator variables

í~C~í$data:=rdms

**** Start a session with RDMS

í~C~ísql begin thread for udssrc update(deferred)

**** Create a table with 2 character columns, the first column does not **** allow NULLS and the second column does.

í~C~ísql create table example in s1.c1 columns are col1 char(10) NOT NULL, & í~C~ícol2 char(15) primary key k1 is col1

The CREATE TABLE statement completed successfully. **** Insert some records into this created table. í~C~ísql insert into example values('abc',null) 1 records were inserted.

í~C~ísql insert into example values('def','pqrstuvwxyz') 1 records were inserted.

**** Declare a cursor against this table, so that we may fetch against it. í~C~ísql declare c1 cursor select col1, col2 from example

The DECLARE CURSOR statement has completed successfully.

**** Fetch from the declared cursor. In the first row in this table, COL2 **** has a NULL, so an indicator variable must be used. The indicator **** variable returns a negative value. (Since COL1 does not allow nulls, **** an indicator variable is optional.)

í~C~ísql fetch c1 into %col1 %ind1, %col2 %ind2 The FETCH statement has completed successfully.

00

í~C~ídisplay %col2 í~C~ídisplay %ind2 -1

**** Fetch the next record. This record does not have any nulls, therefore **** indicator variables are optional.

í~C~ísql fetch c1 into %col1 %ind1, %col2 %ind2 >The FETCH statement has completed successfully. í~C~ídisplay %col1 def í~C~ídisplay %ind1 00 í~C~ídisplay %col2 pqrstuvwxyz í~C~ídisplay %ind2 00

**** Close the thread and log off from IPF. í~C~ísql end thread