on 2022 Aug 10 6:30 PM
Is there a way to log the response of a hosted webservice?
I have for example this block (Approach 1)
create or replace table mytable(empname varchar(10), age int); insert into mytable values ('Eva', 31), ('Anton', 34); create table if not exists mylog(myresponse long varchar); ---------------- create or replace service myservice type 'xml' authorization off user dba as call myproc(); ---------------- create or replace procedure myproc() begin --for example: --insert into mylog select * from mytable for xml raw; --but the query in the next line may deliver different results (as **mytable** changes too fast). select * from mytable; end;
I thought of something like this (Approach 2):
create or replace table mytable(empname varchar(10), age int); insert into mytable values ('Eva', 31), ('Anton', 34); create table if not exists mylog(myresponse long varchar); ---------------- create or replace service myservice type 'raw' authorization off user dba as call myproc(); ---------------- create or replace procedure myproc() begin declare @response long varchar; select * into @response from mytable for xml raw; insert into mylog values(@response ); select @response ; end;
But the response of the service is somehow different
Request clarification before answering.
What exactly is the problem you are facing?
Is is just that you cannot "repeat" the query because the first and second result sets (one for logging, one as response) might be different because of interim changes? (Usually, a fitting isolation level would prevent that...)
(If so, it would be easier/more efficient to use
DECLARE LOCAL TEMPORARY TABLE MyTmpTable... LIKE MyTable.... NOT TRANSACTIONAL
within the procedure because it will be created and dropped automatically and will not have impact on the transaction log.)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thank you, in my database I did it with a temporary not transactional table (exactly as your mentioned), but here I try to keep the block simple, so mytmptable was declared as a normal table.
But could you please give me a hint about the isolation level? can I solve it without the temporary table (with isolation level)?
Well, when you do the two queries within a separate transaction with isolation level "serializable" (or at least "repeatable read" when phantom rows are not an issue), that should guarantee that both result sets will be identical because other transactions would not be allowed to change those rows (or to insert more rows or change further rows so they would now satisfy your queries conditions).
I can't tell whether this has acceptable impact on the overall performance because particularly "serializable" mode requires many locks, so updates may be delayed or blocked.
The temporary table approach might have less impact on that.
Thanks again for the reply, the performance was from the beginning a big concern, since in my first approach I have to query *mytable twice. In addition to data consistency the performance here was also a problem.
The question is again, how can I change my second approach so that the service returns the same result as in the first approach?
So what is your second approach?
As stated in my last comment, I guess that just selecting once from mytable into an automatically created temporary table and then using that contents for logging and responding might be way easier than testing whether an increased (temporary) isolation level does impact the overall performance...
But with your second approach, you do log the same result set that is delivered to the web client, don't you? And so that should be fine... (While your first apporach might return different result sets for logging and client, which apparently is not useful for logging/auditing...)
I still don't get the point somehow.
The problem with the second approach is that the XML Structure is incorrect:
Response of Approach 1 (OK, The front end is already working with it):
<root> <row empname="Eva" age="31"/> <row empname="Anton" age="34"/> </root>
Response of Approach 2 (NOK, The front end will have problem with it, and I dont want to change the front end):
<row empname="Eva" age="31"/><row empname="Anton" age="34"/>
So, just add the root node explicitly to the XML response, say by adding an outer xmlelement() call such as
SELECT XMLELEMENT( NAME root, (SELECT * FROM MyTable FOR XML RAW) );
or use the first approach as discussed with a select into a temporary table (to make sure the result set is cached) and then use that as source data both for logging and the procedure's result set, so the service type XML does the XML formatting.
User | Count |
---|---|
82 | |
29 | |
9 | |
8 | |
7 | |
7 | |
7 | |
6 | |
6 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.