How to log the responses of hosted webservices

[Baron]

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

[VolkerBarth]

Just for clarity: The logging of requests and responses via dbsrvX -xs with network protocol options LogFile, LogOptions and the like is not sufficient?

[Baron]

In this case the logging will be in a file, but I want to write them in a table.

[Baron]

for the moment, I added a temporary table, and I wrote the response inside, and then used this temporary table once for logging and once for response:

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 table mytmptable (empname varchar(10), age int);
create or replace procedure myproc()
begin
truncate table mytmptable;
insert into mytmptable select * from mytable;
insert into mylog select * from mytmptable for xml raw;
select * from mytmptable;
end;

[VolkerBarth · Accepted solution]

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.)

[Baron · Accepted solution]

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)?

[VolkerBarth · Accepted solution]

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.

[Baron · Accepted solution]

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?

[VolkerBarth · Accepted solution]

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...

[Baron · Accepted solution]

I edited my question above and marked my 2 approaches.

With the second approach I meant to use a temp. variable @response and write the XML inside it instead of a temp. table.

The problem is that the response of the service in approach 2 is different from that in approach 1.

[VolkerBarth · Accepted solution]

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.

[Baron · Accepted solution]

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"/>

[VolkerBarth · Accepted solution]

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.