Nested SQL statements call tips for SQLCA in DB2

In IBM's relational database product DB2, SQL Communication Area (SQLCA) is used to return nested SQL statement execution in the program to the program.

In the program is targeted to the SQLCA implementation of the call, you can control the execution of various types of SQL statements in the program, so as to avoid unexpected termination of the program. At the same time, it can improve execution efficiency, reduce system overhead and processing time. This article will give a brief introduction to this.
Structure of the SQLCA
The structure definition for SQLCA is as follows:
Struc Sqlca
{unsigned char sqlcaid[8];
Long SQLABC;
Long Sqlcode;
Short SQLERRML;
unsigned char sqlerrnmc[10];
unsigned char sqlerrp[8];
Long sqlerrd[6];
unsigned char sqlwarn[21];
unsigned char sqlstate[5]; }
Each domain in the structure has different meanings and uses. The functions and uses of some domains are discussed in detail in the subsections below, with the meanings of some other domains such as the following:
Sqlcaid: Identity domain. Include the string "Sqlca".
SQLABC: Includes the length of the SQLCA structure.
SQLERRML: Includes the actual length of the data in the SQLERRMC field.
SQLERRMC: consists of 0 or more strings, which give a more specific explanation of the returned value. If the returned code indicates that the table was not found, the name of the table that was not found is included in this field.
SQLERRP: Includes some assertion information that is useless to the user.
Sqlstate: A string of length 5 that indicates the query result of the SQL statement.

Unlike SQLCA, it follows the ANSI/ISOSQL92 standard, so, although the Sqlcode domain has different meanings in the SQLCA structure of different database products. But the meaning of the SQLState field is the same.

Calling methods
DB2 through a function: SQLAINTP. It is easy to read the results and errors after the SQL statement in SQLCA is run. This function is defined in sql.h and can be defined by the following statement:
EXEC SQL INCLUDE sqlca.h
The SQLAINTP function format is as follows:
int SQLAINTP
(Char *buffer
Short Buffer_size
Short Line_width
struct SQLCA *sqlca)
The buffer is the one in which SQLCA information is stored, the length of buffer is stored in the buffer_size, and the line_width holds the length of the characters between two operators.

The return value of the function represents the length of the SQLCA information, and no SQLCA information is returned for the negative time table. In general, SQLCA information can be stored in a buffer of 512 bytes in length.
To handle various error situations, DB2 provides a whenever statement. Its detailed usage is as follows:
(1) When EXEC SQL whenever SQLERROR action sqlcode<0, run the action.
(2) EXEC SQL whenever sqlwarning action sqlcode>;0 but not 100 and sqlwarn[0]=w, run the action.
(3) When EXEC SQL whenever not FOUND action sqlcode=100, run the action.
There are two possibilities for action:
CONTINUE: There is one more command to continue running the program.
Go to label: goes to the label specified by the start of the statement.
It is worth noting that the whenever statement is scoped to the next whenever when it terminates.

A call to Sqlcode
In the call to Sqlca, the most common is achieved by visiting Sqlcode.

Very many program apes set code to check the return value of the sqlcode after each SQL statement is run to monitor the operation of the program. When the return value is +0. Indicates that the SQL statement ran successfully. A return value of +100 indicates that a record that satisfies the search criteria was not found.

Generally speaking. Negative Sqlcode returns a negative value means that the SQL statement fails to run, and the program ape can implement different program flows to implement error control based on different sqlcode return values in the program. Like what. When the return value of Sqlcode is 911, it indicates that the system detects a deadlock (Dead lock), and the program ape can take the following two disposal methods for this situation:
(1) Set the loop, run the query request repeatedly and check the Sqlcode until the return value is +0 (run successfully).
(2) Set a counter, run the query request to a certain number of times to terminate the program or alert the user.

It is worth noting that in order to ensure the integrity of the data. It is sometimes necessary to roll back (ROLLBACK) The database for the type of error returned, and the starting point of the rollback is not necessarily the state that the system is in because of a SQL statement that is not running successfully. The starting point of the rollback is the nested type of the SQL statement (dynamic, static. Compound) and the type of error are related.

In particular, the latter, because some of the error types cause the system to make an implicit rollback (such as SQLCODE-911), makes the rollback start inference more complex.
Although most of the time we want SQL statements to run successfully, there are times. A negative Sqlcode return value that is useful for "manufacturing" can make logic inference in the program more concise. The system overhead can also be reduced to a large extent. We can imagine a situation where there are two kinds of receipt for the post office, one for the users who only have a telephone, and one for the users with multiple telephones, and the items on the two bills receipt are not the same and need different sub-procedures for processing. In the Charge receipt processing program, the corresponding phone number is retrieved first based on the unique ID number of each user. and set the counter to count the number of records retrieved (number of installed phones) when a user ID is only corresponding to a phone number record. Call the single-machine user charge receipt processing program to the corresponding user's phone call data processing; When a user ID is only corresponding to several phone number records, calls the multi-machine user charge receipt processing program to the corresponding user's charge data processing. No matter what the situation. The user's data must be read again before the next processing. In other words, a user's telephone data must be read two times to complete the corresponding data processing. This will undoubtedly add a lot of system processing time.

Through a call to Sqlcode. Can make a significant improvement in this situation. First of all. A select operation is made for each user's ID, and then the Sqlcode return value is checked, and when the return value is +0, it indicates that the user has only one phone installed and when the return value is-811. Indicates that the user has more than one phone installed. It can be inferred from that procedure that the user charges information should be processed. With such a solution, only the user's data can be read once to complete the user's phone call processing. Almost half of the system processing time was reduced.

and other methods such as altering the structure of the table. This method minimizes modifications to the system. and simple. The effect is obvious.

In a database access program, programs often require implementations such as the following: Update a record in the database to create a record when the record does not exist.

It is common practice to make a select query first, when Sqlcode returns + 0 o'clock, indicating that the record already exists, and then re-reading the record to update it, and when Sqlcode returns +100, it indicates that the corresponding record does not exist, and then the Inster operation is performed. There is also a way to achieve the side shore data access characteristics, when the data update more than the new value is inserted. The update operation is performed directly. When Sqlcode returns + 0 o'clock the update succeeds, and vice versa. When you return +100, you need to insert the operation on it. When the new value is inserted to be extra than the update operation. Insert operations are performed first. The Sqlcode value is +0 when the insert succeeds, otherwise returns-803, indicating that the original record already exists and requires an update operation. Obviously, in most cases, the other method only needs to do one operation on the data, which can improve the system's processing efficiency.
A call to Sqlwarn
Although program apes often ignore SQL warning errors. But proper use can help the program detect a variety of potential errors, and can make programming more concise.

Therefore, this kind of warning message should also be attached importance to by the procedural ape.
There are two representations of the warning message in SQLCA.

Each Sqlcode positive return value other than +100 represents a certain warning message; At the same time, sqlwarn[n in Sqlca] (n=1,2,3,4. 5. 6 or a) The return value also represents the corresponding warning message. When the system discovers a warning error, the system notifies the program in either of these ways.

At this time The query may return some results. However, the results are most likely to be incorrect or incomplete at this time. When Sqlwarn[o]=w. DB2 provides information that helps users infer detailed warning types, with their corresponding values and meanings such as the following table:


SQLCA value return value warning content
SQLWARN[1] W Sequence main variable length is not enough to intercept
SQLWARN[2] W field function processed data with null (NULL) value
SQLWARN[3] W Returns the number of fields greater than the program-defined ordinal principal variable
SQLWARN[4] W does not specify a where condition in an update or delete operation
SQLWARN[6] W correction of time data after mathematical operation
SQLWARN[A] W Conversion error
If a school is to calculate the class of a semester. Each section. An individual's average score.

Of Average Personal score = (individual total)/(number of subjects studied this semester).

Because the staff negligence, mistakenly will a classmate's semester study subjects lost to 0.

When a program that uses nested SQL statements processes related data, the Sqlcode return value of 802 (a divisor of 0) causes the program to terminate execution. Although the user knows that it may be a data error, it is not possible to know which data error is detailed. For a school with more data, finding it is a lot of trouble.

The following approach can be very good to solve this problem: in the definition of the main variable that represents the result of the same time, give it a nullable indicator variable, such as: Avgrd:ind the main variable is AVGRD, indicating that the variable is ind. At this point, when the error is removed by 0, the indicator variable value is set to-2, at the same time the Sqlcode return value is +802 warning message.

When the program captures this information, it can provide the user with the corresponding information to help the user determine the location of the error data.
When the value of Sqlwarn[o] is W, it indicates that the system detected a warning error. The warning information provided by the system can also be exploited by the program ape. For example, a character intercept occurs when a program passes a character field value in a table to a sequence principal variable that is shorter than its length. Although Sqlcode returns +0 at this point, the data obtained by the program is incomplete. By specifying an indicator variable for the ordinal primary variable. When the same error occurs, the return value of Sqlwarn[o] and Sqlwarn[1] is W. At the same time, the indicator variable is assigned the actual length of the data in the field. This method can even be used to infer the actual length of string data in a field in a table.
Calls to the SQLERRD array
SQLERRD is an array in Sqlca that represents the results of the SQL query execution. The different elements in the array have different meanings.

More often than not, sqlerrd[3] is used. It returns the number of records affected by the last insert,update or delete operation in the program. Otherwise. The program needs to use a certain cycle to count the number of records affected by the above operation. But it's worth noting that. When you perform a delete operation. Assuming that the deletion type of the related field is set to cascade and the number of other records being deleted is not included, the return value of sqlerrd[3] is 1, assuming that the where condition is not specified in the DELETE statement. The above two points should be taken into consideration when using this array. The meaning of each element in the SQLERRD and its for composite SQL statements. The different meanings of the connection method are as follows:
SQLERRD element expression meaning compound SQL statement connection method
SQLERRD[2] Saves the number of rows processed by the current SQL statement. However, if the current SQL fails, there is no definition; If an error occurs in an array operation, the row is stopped in error, and the number of rows successfully processed is given; OPENAfter running, the Qing is 0, fecth value (the original value + the number of rows on this fetch); The number of rows successfully processed after EXECUTE, INSERT, UPDATE, Selete, and select.
SQLERRD[3] Number of records returned (expected) and number of records affected by insert/update or delete operations (excluding records affected by throttling conditions) Total number of statements0: Submitted by a paragraph of the underlying client commit acknowledgment 1: A paragraph submission 2: a paragraph is read-only commit 3: two-paragraph submission
SQLERRD[4]indicates the displacement of the error in the statement, the first character displacement is 0.

SQLERRD[5] The number of records affected by the insert/update or delete operation (including records that are affected by the throttling conditions) the number of records affected by the constraints

Often used:sqlerrd[2], such as:#define sqlrows sqlca.sqlerrd[2]

DB2 supports a variety of high-level languages (including currently extremely popular C and C + +, etc.) to access data on it through nested SQL statements. At the same time, the flexibility of access is enhanced by the support of multiple nested (dynamic, static, and composite) methods.

By calling Sqlca in the program. You can control the execution results of nested SQL statements in your program. Enhanced the reliability of the program. Prevents the program from terminating unexpectedly. It can also make the programming of the program more concise. Improve the execution efficiency of the program and shorten the processing time of the system.

Nested SQL statements call tips for SQLCA in DB2