by Ann Harrison
If you want to know what request handles do and why they are saved, here's the long answer.
Requests are prepared statements and they persist until they are released explicitly or the connection ends. Most of the utilities keep an array of request handles so they can reuse prepared requests. If there is no values stored for this request, the code above stores the prepared request handle in the array.
Here's a version of the GDML for the request that delete a table and detect the error case that the table did not exist:
found = FALSE; FOR R IN RDB$RELATIONS WITH R.RDB$RELATION_NAME EQ relation_name ERASE R; found = TRUE; END_FOR;
The GDML equivalent of a SELECT statement is a FOR loop. One difference is that a FOR loop (as one might expect) contains its own loop control. For example:
FOR R IN RDB$RELATIONS printf ("%sn", R.RDB$RELATION_NAME); END_FOR;
is equivalent to (approximately this SQL):
DECLARE C CURSOR FOR select rdb$relation_name from rdb$relations; OPEN C; FETCH C INTO :relation_name; while (!SQLCODE) { printf ("%sn", relation_name); FETCH C into :relation_name; } if (SQLCODE != 100) <execute error routine, also hand coded>;
I'm sure there's some reason why the SQL is enormously superior, but me, I go for short.
The basic syntax FOR loop syntax is:
FOR [<options>] [TOP <n>] <rse> <actions> END_FOR;
rse> expands to:
<table reference> [CROSS <table_reference> [OVER <conjunct>] ...] [WITH <restriction>] [REDUCED TO <projection list>] [ORDER BY <sort list>]
a table reference is:
<context variable> IN {table | view}
a conjunct is a comparison between two tables. A restriction is any comparison.
The options that come to mind are:
REQUEST_HANDLE -> use an explicit request handle that can be saved & reused.
TRANSACTION_HANDLE -> use a named transaction. Firebird allows multiple transactions per connection. In the case below, this request is run in the system transaction - not available outside the engine. The system transaction number is 0 and it is "pre-committed" meaning that its changes are immediately visible to all other transactions.
Here's the whole request. It uses both request options and saves the compiled request handle if it has not been saved before. It also checks for the existence of a non-default security class that it should delete.
found = FALSE; FOR (REQUEST_HANDLE request TRANSACTION_HANDLE gbl->gbl_transaction) R IN RDB$RELATIONS WITH R.RDB$RELATION_NAME EQ relation_name if (!DYN_REQUEST (drq_e_relation)) DYN_REQUEST (drq_e_relation) = request; if (!R.RDB$SECURITY_CLASS.NULL && !strncmp (R.RDB$SECURITY_CLASS, "SQL$", 4)) delete_security_class2 (gbl, R.RDB$SECURITY_CLASS); ERASE R; found = TRUE; END_FOR; if (!DYN_REQUEST (drq_e_relation)) DYN_REQUEST (drq_e_relation) = request;
Let me explain this request in some detail, using the preprocessed code so you see both the GDML and the calls that implement it.
The first line calls the compile module (CMP) to find the compiled request if one exists. tdbb is the thread database block. The database block carries state information about the database and connection. A tdbb is a wrapper around the dbb, making it thread specific. drq_e_relation is the name of this particular request (e_relation -> erase relation -> delete table).
DYN_REQUESTS is the name of the array of request handles for DYN. If the request has been compiled, CMP_find_request will return a non-null handle. If not, it will return a null pointer.
BLK is the ur-block - in an object system, the parent of all block types.
request = (BLK) CMP_find_request (tdbb, drq_e_relation, DYN_REQUESTS);
Id is a volatile parameter - used to hold the handle type in case of errors so the error message has some hope of matching the actual error.
id = drq_e_relation;
Found is also volatile.
found = FALSE;
request and relation_names are ordinary C variables.
/*FOR (REQUEST_HANDLE request TRANSACTION_HANDLE gbl->gbl_transaction) R IN RDB$RELATIONS WITH R.RDB$RELATION_NAME EQ relation_name*/
The preprocessor turns the GDML statements into comments and adds the API calls - or, in this case, their internal equivalents. This is the opening brace from the code in dyn_del.e
{
If CMP_find_request didn't find a request, compile the request. I've forgotten exactly why CMP_compile was replaced with CMP_compile2. That sort of thing happens. (both the forgetting and the replacing.) jrd_142 is a pointer to the blr that represents the actual request. An external request would call isc_compile_request2;
if (!request) request = (BLK) CMP_compile2 (tdbb, jrd_142, TRUE);
gds__vtov (variable to variable) copies on c-string (null terminated string) to another. I think it's completely equivalent of strncpy. relation_name is our friend from the GDML. jrd_143 is a message generated to pass input data to the request. jrd_144 is the message element that holds the relation name.
gds__vtov (relation_name, jrd_143.jrd_144, 32);
Ordinarily, the next call would be to isc_start_and send. Since this is an internal request, it is broken into its internal equivalents, EXE_start and EXE_send. (EXE-> execution). The request is started in the context of the thread database block and the global transaction. Since the first thing the request needs is the name of the table, the next call is to EXE_send, passing in the message number (0 is the first message in any request), the message length, and the address of the message).
EXE_start (tdbb, request, gbl->gbl_transaction); EXE_send (tdbb, request, 0, 32, &jrd_143);
Here's the generated loop:
while (1) {
EXE_receive accepts a message back from the server with data. It wants a specific message from a specific request. In this case it wants the second message defined for "request", which is 36 bytes long and should be stuffed in the structure jrd_145. That message contain the name of a security class, possibly null, the null flag for that value, and a flag that indicates that no data was found. Null flags are short integers. 32 (security class name + terminating null) + 2 (null flag) + 2 (data stream exhausted)=36.
EXE_receive (tdbb, request, 1, 36, &jrd_145);
First, check for data stream exhausted:
if (!jrd_145.jrd_147) break;
Now do the work of the loop (note that the request handle won't be saved here if the record stream was empty. If it contains more than one row, the check and save will be done once for each record. Seems dim to me, but since relation names are unique and this is just checking an array, well, what the hell.
if (!DYN_REQUEST (drq_e_relation)) DYN_REQUEST (drq_e_relation) = request;
The references in the loop to the field R.RDB$SECURITY_CLASS and its null flag are replaced by the equivalent elements in the message.
if (!/*R.RDB$SECURITY_CLASS.NULL*/ jrd_145.jrd_148 && !strncmp (/*R.RDB$SECURITY_CLASS*/ jrd_145.jrd_146, "SQL$", 4)) delete_security_class2 (gbl, /*R.RDB$SECURITY_CLASS*/ jrd_145.jrd_146);
The third message in the request is sent from the program to the engine and indicates that the current record should be deleted.
/*ERASE R*/ EXE_send (tdbb, request, 2, 2, &jrd_149);
If we got here, we found a record.
found = TRUE;
Tell the server that we're done and can leave the inner action loop.
/*END_FOR*/ EXE_send (tdbb, request, 3, 2, &jrd_151);
This closes the while (1) loop.
}
This closes the user braces.
};
This is the right place for this check and store.
if (!DYN_REQUEST (drq_e_relation)) DYN_REQUEST (drq_e_relation) = request;
And report an error if the table didn't exist.
if (!found) { tdbb->tdbb_setjmp = (UCHAR*) old_env; DYN_error_punt (FALSE, 61, NULL, NULL, NULL, NULL, NULL); /* msg 61: "Relation not found" */ }
Here, for the extremely hard core, is the generated BLR. I tend to think of the blr as the code the engine executes in parallel with the calling code. As long as they stay in sync, the engine will have a message to send when the calling code is looking for that message and vice-versa. When they get out of step, you get the error message: request synchronization error.
static CONST UCHAR jrd_142 [161] = { /* blr string */ blr_version4, blr_begin, blr_message, 3, 1,0, / tells the engine to break out of a loop blr_short, 0, blr_message, 2, 1,0, / tells the engine when to erase a record blr_short, 0, blr_message, 1, 3,0, / passes back the security class name & flags blr_cstring2, 3,0, 32,0, blr_short, 0, blr_short, 0, blr_message, 0, 1,0, / passes in the relation name blr_cstring2, 3,0, 32,0, blr_receive, 0, / catches the relation name coming in blr_begin, blr_for, blr_rse, 1, / a record selection expression with one input / the name table affected has 13 characters. / it is the first input stream(i.e. context 0) blr_relation, 13, 'R','D','B','$','R','E','L','A','T','I','O','N','S', 0, blr_boolean, blr_eql, / the first half of the comparison is a field / from context id 0, with a 17 character name blr_field, 0,17, 'R','D','B','$','R','E','L','A','T','I','O','N','_','N','A','M','E', blr_parameter, 0, 0,0, / the second half is the first parameter of the / first message. blr_end, / end of the rse. blr_begin, blr_send, 1, / the engine starts putting together the / second message blr_begin, blr_assignment, / it assigns a value to a parameter in the message. / the assignment is from context 0 and is a field, / with an 18 character name. blr_field, 0, 18, 'R','D','B','$','S','E','C','U','R','I','T','Y','_','C','L','A','S','S', / The target of the assignment is double, both the / data value and the null flag of the second message. blr_parameter2, 1, 0,0, 2,0, / The second assignment is the EOF flag. blr_assignment, blr_literal, blr_long, 0, 1,0,0,0, blr_parameter, 1, 1,0, / OK, message ready, end of send. blr_end, blr_label, 0, / loop control is done with a label and leave blr_loop, / the action required for this may require several / messages so do it in a loop. blr_select, / "select", confusingly, is a case statement on / message types. blr_receive, 3, /if we get a message type three, leave the loop. blr_leave, 0, blr_receive, 2, / if we get a message of type 2, erase the blr_erase, 0, / current record in context 0 blr_end, / end of loop. blr_end, / end of blr_for blr_send, 1, / build a message saying EOF. blr_assignment, blr_literal, blr_long, 0, 0,0,0,0, blr_parameter, 1, 1,0, blr_end, blr_end, / end of outer blr_begin blr_eoc / matches blr_version4 }; /* end of blr string */
Let me try to explain the label, loop, select business. Suppose that the erase were conditional:
for r in rdb$relations if r.rdb$relation_name = 'QLI$PROCEDURES' modify r using r.rdb$system_flag = 1; if r.rdb$system_flag = 1 erase r; end_for;
The result of the loop may be no action, one action, or two actions. Each action is signaled with a specific message. The engine loops, catching messages and doing things, until it gets the message saying it should stop.
This paper was written by Ann Harrison in May 2001, and is copyright Ms. Harrison and IBPhoenix Inc. You may republish it verbatim, including this notation. You may update, correct, or expand the material, provided that you include a notation that the original work was produced by Ms. Harrison and IBPhoenix Inc.