In IBM's relational database product DB2, SQL Communication Area (SQLCA) is used to return nested SQL statement runs 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 operation efficiency, reduce system overhead and processing time. This article will give a brief introduction to this.
Structure of the SQLCA
The structure of the SQLCA is defined 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, and the functions and uses of some domains are discussed in detail in the following subsections, and some of the other domains have the following meanings:
Sqlcaid: The identity domain that contains the string "Sqlca".
SQLABC: Contains the length of the SQLCA structure.
SQLERRML: Contains the actual length of the data in the SQLERRMC field.
SQLERRMC: consists of 0 or more strings, which give a more detailed explanation of the returned values. If the returned code indicates that the table was not found, this field contains the name of the table that was not found.
SQLERRP: Contains 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 the SQLSTATE domain has the same meaning despite the different meanings of sqlcode domains in the SQLCA structure of different database products.
Calling methods
DB2 through a function: SQLAINTP, it is easy to read the results and errors of SQL statements executed in SQLCA. This function is defined in sql.h and can be defined by the following statement:
EXEC SQL INCLUDE sqlca.h
The SQLAINTP function is formatted as follows:
int SQLAINTP
(Char *buffer
Short Buffer_size
Short Line_width
struct SQLCA *sqlca)
Where buffer is the buffers that hold the SQLCA information, the length of the buffer is stored in the buffer_size, and the line_width holds the length of the characters between the two actuators. 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 specific use is as follows:
(1) When EXEC SQL whenever SQLERROR action sqlcode<0, execute action.
(2) EXEC SQL whenever sqlwarning action sqlcode>;0 but not 100 and sqlwarn[0]=w, executes the action.
(3) When EXEC SQL whenever not FOUND action sqlcode=100, the action is executed.
There are two possibilities for action:
CONTINUE: Continue executing another command in 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 accessing the Sqlcode. Many programmers even set code to check the return value of Sqlcode after each SQL statement is executed to monitor the operation of the program. When the return value is +0, the SQL statement executes successfully, and a return value of +100 indicates that the record that satisfies the search criteria was not found. In general, negative sqlcode returns negative meaning that SQL statement execution fails, and programmers can implement different program processes to implement error control based on different sqlcode return values in the program. For example, when the return value of Sqlcode is 911, it indicates that the system detects a deadlock (Dead lock), and the programmer can take the following two disposal methods for this situation:
(1) Set the loop, execute the query request repeatedly and detect the Sqlcode until the return value is +0 (successful execution).
(2) Set a counter, execute the query request to a certain number of times to terminate the program or alert the user.
It is important to note that in order to ensure the integrity of the data, it is sometimes necessary to roll back (ROLLBACK) the database against the returned error type, and the starting point of the rollback is not necessarily the state that the system is in because of an unsuccessful SQL statement. The starting point of a rollback is related to the nested type of the SQL statement (dynamic, static, composite), and the type of error. In particular, the latter, because some error types cause the system to make an implicit rollback (for example, SQLCODE-911), making it more complex to determine the starting point of a rollback.
Although in most cases we want the SQL statement to succeed, there are times when a negative sqlcode return value that is deliberately "made" can make logic judgments in the program more concise and can reduce the overhead of the system to a greater degree. We can envisage such a situation: The Post Office has two kinds of charge receipts, one for the only one phone users, and the other is for the number of users of the phone, the two bills receipt on the items are different, need to be processed by the various sub-procedures. In the Charge receipt processing program, first retrieve the corresponding phone number according to the unique ID number of each user, and set the counter to count the number of records retrieved (number of installation calls), when a user ID only corresponds to a phone number record, call a single user charge receipt processing program to the corresponding user's phone data processing When a user ID corresponds to more than one phone number record, call the multi-machine user charge receipt processing program to the corresponding user's charge data processing. In either case, the user's data needs to be re-read before it can be processed next. In other words, a user's phone data needs to be read two times to complete the corresponding data processing. This will undoubtedly greatly increase the system processing time. This can be significantly improved by the invocation of Sqlcode. First, 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, the user installs more than one phone. This can be judged by the kind of procedures to deal with user charges information. With this solution, users ' phone calls can be processed with only one read of the user's data, and almost half of the system processing time is reduced. With other methods such as changing the structure of the table, this method has the smallest changes to the system, and is simple and effective.
In a program that accesses a database, a program typically requires the following functionality: Update a record in the database and create the corresponding 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 rereading 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. Another implementation of the side of the bank according to the characteristics of data access, when the data update more than the new value is inserted, the update operation is performed directly when Sqlcode returns + 0 o'clock updates, conversely, when the return +100, you need to insert the operation; When the new value is inserted to be extra than the update operation, Insert operation First, the Sqlcode value is +0 when the insertion succeeds, otherwise return-803, indicating that the original record already exists, need to make an update operation. Obviously, in most cases, the second method only needs to operate the data once, thus improving the system's processing efficiency.
A call to Sqlwarn
Although programmers often ignore SQL warning errors, proper use can help the program detect a variety of potential errors and make programming more concise. Therefore, this kind of warning information should also be valued by programmers.
There are two representations of the warning message in SQLCA. Each sqlcode return value except +100 represents a certain warning message, and the return value of Sqlwarn[n] (n=1,2,3,4,5,6 or a) in Sqlca 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 point, the query may return some results, but at this point the results are most likely to be incorrect or incomplete. When Sqlwarn[o]=w, DB2 provides information to help users determine the type of specific warning, the corresponding value and meaning of 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
Suppose a school is to calculate the average grade of each class, section and individual in a semester. Among them, the individual average score = (individual total)/(number of subjects studied this semester). As a result of negligence of staff, 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 running. Although the user knows that it may be a data error, it is not possible to know which data error is specific. For a school with more data, finding it is a lot of trouble. The problem can be solved by the following method: When defining the ordinal principal variable that represents the result of the calculation, specify 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, and the Sqlcode return value is +802 warning message. When the program captures this information, it can provide the user with the appropriate information to help the user determine the location of the error data.
When the value of Sqlwarn[o] is W, it indicates that a warning error was detected by the system. The warning information provided by the system can also be exploited by the programmer. 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 main variable, the return value of Sqlwarn[o] and Sqlwarn[1] is w when the same error occurs, and the indicator variable is assigned the actual length of the data in the field. This method can even be used to determine 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 result of the SQL query run. The different elements in the array have different meanings. One of the more commonly used is sqlerrd[3]. 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 loop to count the number of records affected by the above operation. It is worth noting, however, that when the delete operation is performed, if the deletion type of the related field is set to cascade, the number of other records being deleted is not included, and if the Where condition is not specified in the DELETE statement, then the return value of Sqlerrd[3] is-1. The above two points should be taken into consideration when using this array. The meanings of each element in the SQLERRD and its different meanings for the composite SQL statement 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 execution, 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 (estimated) and number of records affected by insert/update or delete operations (not including records affected by throttling conditions) Total number of statements0: A segment of the underlying client commits confirmation 1: Paragraph commit 2: paragraph read-only commit 3: two-paragraph commit
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
Freq used:sqlerrd[2], such as: #define SqlRows sqlca.sqlerrd[2]
DB2 supports a variety of high-level languages, including the currently extremely popular C and C + +, and accesses 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, we can control the execution result of nested SQL statements in the program, enhance the program reliability, prevent the program from terminating unexpectedly, and make the program more concise, improve the running efficiency of the program and shorten the processing time of the system.
Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.
Nested SQL statements Access DB2 SQLCA in the call technique
