| T.R | Title | User | Personal
Name | Date | Lines |
|---|
| 5103.1 | | NOVA::SMITHI | Don't understate or underestimate Rdb! | Wed Mar 05 1997 10:23 | 196 |
| Here is an article I wrote in 1989... Does it help?
Ian
1 Mixing Preprocessed and Callable RDO Statements in a Single
Transaction
If you want or need to include preprocessed statements and
Callable RDO statements in the same transaction, the recommended
technique is as follows:
1. Use the DATABASE statement with a database handle in the pre-
processed module to declare information to the preprocessor.
This is not an executable statement.
2. Use the DATABASE statement in the call to RDB$INTERPRET to
establish the database context and to attach to the database.
This is an executable statement and should be the first
statement to access the database. Use !VAL and have the
database handle returned in the variable specified (declared)
in Step 1.
You should not use a preprocessed READY statement, as
this will attempt to attach to an already attached (open)
database.
3. Start a transaction in the preprocessed module, if possible.
This is more efficient than calling RDB$INTERPRET, which must
parse the command string.
Be sure to save the transaction handle, because it is used to
keep the Callable RDO and preprocessed DATABASE statements
operating in the same transaction and hence on the same
database attach.
This transaction handle should be used in all transactions,
both preprocessed and Callable RDO. This includes the COMMIT
and ROLLBACK statements.
Example 1 is a simple RDML/Pascal application that demonstrates
this technique.
Example 1: Using Preprocessed and Callable RDO Statements in a
Single Transaction
program COEXIST(output);
Example 1 (continued on next page)
1
Example 1 (Cont.): Using Preprocessed and Callable RDO State-
ments in a Single Transaction
{
| This program uses RDB$INTERPRET and pre-compiled DML statements
| which share the database / transaction handle. i.e. only one database
| attach is be required for the application.
|
| This shipping database is a trivial one:
|
| define database shipping.
| define field port_num datatype signed longword.
| define relation port.
| port_num.
| end.
| store p in port using p.port_num=1 end_store
| commit
}
database
db1 = filename 'SHIPPING';
function RDB$INTERPRET
{-------------}
(command: [class_s,readonly] packed array [l1..u1:integer] of char;
%IMMED argument: [list] integer
): integer; external;
procedure RDB$SIGNAL; external;
{----------}
procedure LIB$SPAWN
{---------}
(command: [class_s] packed array [l1..u1:integer] of char
:= %IMMED 0
); external;
var
t1: RDML$HANDLE_TYPE := NIL;
sts: integer := 0;
begin
{ attach to the database using RDB$INTERPRET }
sts := RDB$INTERPRET('database !VAL = filename "SHIPPING"',
%DESCR db1::integer);
if not ODD(sts) then RDB$SIGNAL;
{ start a transaction in RDML and establish the transaction handle }
start_transaction (transaction_handle t1) read_write;
writeln('First Pass...');
for (transaction_handle t1) p in port
writeln(p.port_num);
end_for;
Example 1 (continued on next page)
2
Example 1 (Cont.): Using Preprocessed and Callable RDO State-
ments in a Single Transaction
{
| Use the transaction handle to communicate transaction and attach
| information to RDB$INTERPRET.
| This interpreted string can be an arbitrary DML command string
}
sts := RDB$INTERPRET('store (transaction_handle !VAL) ' +
'p in port using p.port_num = 25 end_store',
%DESCR t1::integer);
if not ODD(sts) then RDB$SIGNAL;
writeln('Second Pass...');
for (transaction_handle t1) p in port
writeln(p.port_num);
end_for;
{
| Spawn a process and use RMU to display the number of active users.
| There should be only one with the process id of the current user.
}
writeln('Spawn sub-process to examine database attaches');
LIB$SPAWN('RMU/DUMP/USERS SHIPPING');
rollback (transaction_handle t1);
finish db1;
end.
3
|
| 5103.2 | | ukvms3.uk.oracle.com::PJACKSON | Oracle UK Rdb Support | Wed Mar 05 1997 11:15 | 43 |
| >Here is an article I wrote in 1989... Does it help?
Well, it looks like he has got it basically right. So why does it fail?
There are two modules. In the declarations for the first is
invoke database edp1=filename 'sys$common:[syshlp.examples.rdb]personnel';
In the declarations for the second is
invoke database edp2=filename 'sys$common:[syshlp.examples.rdb]personnel';
The prgram starts in module 1.
The sequence of executable database statements follows.
n> indicates the module.
2> rdb_statement := 'invoke database !val = filename ' + sf_db_name2;
2> rdb_status := rdb$interpret_stream(rdb_statement,%descr edp2);
1> rdb_statement := 'invoke database !val = filename ' + sf_db_name1;
1> rdb_status := rdb$interpret_stream(rdb_statement,%descr edp1);
2> start_transaction (transaction_handle t2) read_only
2> reserving edp2.work_status for shared read;
2> rdo_statement := 'first 3 w in work_status';
2> rdb_statement := 'start_stream (transaction_handle !val) stream2 using '+
2> rdo_statement;
2> rdb_status := rdb$interpret_stream(rdb_statement,%descr t2);
Here it fails with
%RDB-F-REQ_WRONG_DB, database named in specified request is not a database
named in specified transaction
t2 is declared as
t2 : rdml$handle_type := nil;
In your example you say %DESC t1::integer. What is the ::integer for?
Should he say "first 3 w in edp2.work_status"?
Peter
|
| 5103.3 | | NOVA::SMITHI | Don't understate or underestimate Rdb! | Wed Mar 05 1997 16:24 | 35 |
| ~invoke database edp1=filename 'sys$common:[syshlp.examples.rdb]personnel';
~invoke database edp2=filename 'sys$common:[syshlp.examples.rdb]personnel';
Does he expect these to be the same attach? If so he needs the same dbhandle.
~ In your example you say %DESC t1::integer. What is the ::integer for?
I used RDML$HANDLE_TYPE which is defined as an UNSIGNED LONGWORD. RDO didn't
know that that was a legal descriptor for a handle. AFter I wrote this code I
fixed RDO :-)
~ Should he say "first 3 w in edp2.work_status"?
Nope. The name EDP2 is only known to RDML, not the interpreter.
2> rdo_statement := 'first 3 w in work_status';
2> rdb_statement := 'start_stream (transaction_handle !val) stream2 using '+
2> rdo_statement;
2> rdb_status := rdb$interpret_stream(rdb_statement,%descr t2);
I think you need something like:
rdo_statement := 'first 3 w in !VAL.work_status';
rdb_statement := 'start_stream (transaction_handle !val) stream2 using '+
rdo_statement;
rdb_status := rdb$interpret_stream(rdb_statement,%descr t2, %DESCR edp2);
If this works (I have no tested it) then you will need to know the tables
referenced in the RDO_STATEMENT. We will admit that RDO isn't such a hot
dynamic language.
One wonders why they are not using dynamic SQL. Which will work better, and
faster than RDO$INTERPRET
Ian
|
| 5103.4 | Thanks | ukvms3.uk.oracle.com::PJACKSON | Oracle UK Rdb Support | Thu Mar 06 1997 04:28 | 23 |
| >~invoke database edp1=filename 'sys$common:[syshlp.examples.rdb]personnel';
>~invoke database edp2=filename 'sys$common:[syshlp.examples.rdb]personnel';
>
>Does he expect these to be the same attach? If so he needs the same dbhandle.
No. This is a test program. The real one will use different databases.
>~ Should he say "first 3 w in edp2.work_status"?
>
>Nope. The name EDP2 is only known to RDML, not the interpreter.
The program did do an invoke via RDO using edp2. Doesn't that count?
>One wonders why they are not using dynamic SQL. Which will work better, and
>faster than RDO$INTERPRET
I also wonder, but he didn't say why. We have only been in contact via
email.
Anyway the latest message says the problems are fixed. It didn't say
how.
Peter
|
| 5103.5 | | NOVA::SMITHI | Don't understate or underestimate Rdb! | Thu Mar 06 1997 08:50 | 10 |
| ~>~ Should he say "first 3 w in edp2.work_status"?
~>
~>Nope. The name EDP2 is only known to RDML, not the interpreter.
~
~ The program did do an invoke via RDO using edp2. Doesn't that count?
Actually RDO saw !VAL not EDP2. The *name* EDP2 is not known to RDO just the
value of the dbhandle.
Ian
|