Down with Assumptions! Up with Diagnostics!
November 10, 2010 Ted Holt
|
Even though assumptions have no place in proper programming, programmers sometimes infuse their code with assumptions. Such practice brings to mind something I once read: If builders built houses the way programmers write programs, the first woodpecker to come along would destroy civilization. I’ve written about the problem of assumptions before. Today is a good day to revisit the subject. Programmers who cut their teeth on native I/O (as did I) often depend on the system to alert them when something goes wrong. That is, they assume an I/O operation completed normally unless they hear differently. The bad way to hear differently is to read a message on message queue QSYSOPR. The proper way to hear differently is to trap the error (e.g., with RPG’s MONITOR op code) and do something with it. But trade schools and OJT aren’t the best teachers of error recovery. One of the most notable ways that SQL differs in many ways from native I/O is that SQL does not generate a hard error when it can’t complete an assignment. Instead, SQL updates a multitude of variables and program execution continues. Two of those variables are SQL state and SQL code. (I have also written about them.) Each time the system executes an SQL command, it updates a diagnostics area with a plethora of information. Use the GET DIAGNOSTICS statement to retrieve this information. You may retrieve three types of information:
One option is to retrieve all of the information, as this short program does.
D Data s 1049a varying
/free
*inlr = *on;
exec sql
update custcdt
set baldue = 999
where state = 'TX';
exec sql
get diagnostics
:Data = All;
return;
This impractical program decrees that all Texas customers are in debt to the tune of 999 simoleons. After the update, GET DIAGNOSTICS loads the DATA variable with a lot of information. It’s one long string, but here I show the V5R4 version wrapped 60 characters to the line (as the green-screen debugger shows it). COMMAND_FUNCTION=UPDATE WHERE;COMMAND_FUNCTION_CODE=+82;DB2_ NUMBER_CONNECTIONS=+1;DB2_SQL_ATTR_CONCURRENCY=L;DB2_SQL_ATT R_CURSOR_HOLD=N;DB2_SQL_ATTR_CURSOR_ROWSET=N;DB2_SQL_ATTR_CU RSOR_SCROLLABLE=N;DB2_SQL_ATTR_CURSOR_SENSITIVITY=S;DB2_SQL_ ATTR_CURSOR_TYPE=F;MORE=N;NUMBER=+1;ROW_COUNT=+2;CLASS_ORIGI N=ISO 9075;CONDITION_NUMBER=+1;DB2_MESSAGE_ID=SQL7957;DB2_MO DULE_DETECTING_ERROR=QSQUPDAT;DB2_ORDINAL_TOKEN_1=CUSTCDT ;DB2_ORDINAL_TOKEN_2=QTEMP ;DB2_ORDINAL_TOKEN_3=2;DB2_OR DINAL_TOKEN_4=0;DB2_TOKEN_COUNT=+4;DB2_TOKEN_STRING=CUSTCDT QTEMP 20;MESSAGE_LENGTH=+35;MESSAGE_OCTET_LENGTH=+3 5;MESSAGE_TEXT=2 rows updated in CUSTCDT in QTEMP.;RETURNED_ SQLSTATE=00000;SERVER_NAME=Z1010101;SUBCLASS_ORIGIN=ISO 9075 ;CONNECTION_NAME=Z1010101;DB2_AUTHORIZATION_ID=SMITH;DB2_CON NECTION_METHOD=D;DB2_CONNECTION_NUMBER=+1;DB2_CONNECTION_STA TE=+1;DB2_CONNECTION_TYPE=+1;DB2_PRODUCT_ID=QSQ05040;DB2_SER VER_CLASS_NAME=DB2 UDB for iSeries;DB2_SERVER_NAME=Z1010101; Notice the format of the string–keyword=value–separated by semicolons. If you don’t want all the information, there are various ways to get only part of it. Here’s the same worthless program, but this version retrieves the statement information only.
D Data s 1049a varying
/free
*inlr = *on;
exec sql
update custcdt
set baldue = 999
where state = 'TX';
exec sql
get diagnostics
:Data = All Statement;
return;
Notice there are fewer keyword/value pairs. COMMAND_FUNCTION=UPDATE WHERE;COMMAND_FUNCTION_CODE=+82;DB2_ NUMBER_CONNECTIONS=+1;DB2_SQL_ATTR_CONCURRENCY=L;DB2_SQL_ATT R_CURSOR_HOLD=N;DB2_SQL_ATTR_CURSOR_ROWSET=N;DB2_SQL_ATTR_CU RSOR_SCROLLABLE=N;DB2_SQL_ATTR_CURSOR_SENSITIVITY=S;DB2_SQL_ ATTR_CURSOR_TYPE=F;MORE=N;NUMBER=+1;ROW_COUNT=+2; A big disadvantage of retrieving values by the bunch is that you have to write code to extract the values from a long string. But if you retrieve values individually, you don’t have to go through that ordeal. In the following example, GET DIAGNOSTICS determines how many rows were updated.
D RowCount s 10i 0
/free
*inlr = *on;
exec sql
update custcdt
set baldue = 999
where state = 'TX';
exec sql
get diagnostics
:RowCount = ROW_COUNT;
return;
The system loads ROWCOUNT with the number of records that were updated. By the way, this is a more intuitive method than the one I published previously. If you need to obtain two or more values at once, you can do that too, like this:
D RowCount s 10i 0
D Command s 128a varying
/free
*inlr = *on;
exec sql
update custcdt
set baldue = 999
where state = 'TX';
exec sql
get diagnostics
:RowCount = ROW_COUNT,
:Command = COMMAND_FUNCTION;
return;
After this code runs, ROWCOUNT contains an integer value and COMMAND is UPDATE WHERE. The last two examples retrieved two of the STATEMENT information values. This one retrieves TABLE_NAME from the CONDITION information.
D TotalDue s 9p 2
D TableName s 128a varying
/free
*inlr = *on;
exec sql
select sum(baldue)
into :TotalDue
from br549;
exec sql
get diagnostics
condition 1 :TableName = TABLE_NAME;
return;
After GET DIAGNOSTICS, TABLENAME is BR549. See the SQL Reference for your release to learn more about the values in the diagnostics area. The lesson is clear: There is no need to assume anything when an SQL statement runs. SQL gives you more information than what you want or need. RELATED STORIES Error Checking and Embedded SQL
|
