updating multiple tables with sql processor

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

updating multiple tables with sql processor

Alexander Žaťko
Context of the issue:
PresentationServer Release 3.0.beta2.200507191809
JTDS JDBC driver

Is it possible to insert records into more than one table within one
invocation of the oxf:sql processor?

When I construct the config file like this...

<sql:config xmlns:sql="http://orbeon.org/oxf/xml/sql">
     <sql:connection>
         <sql:execute>
             <sql:update>Insert into table1 (col1, col2,) values
('foo',bar')</sql:update>
         </sql:execute>
         <sql:execute>
             <sql:update>Insert into table2 (col1, col2,) values
('foo',bar')</sql:update>
         </sql:execute>
         <sql:execute>
             <sql:update>Insert into table3 (col1, col2,) values
('foo',bar')</sql:update>
         </sql:execute>
     </sql:connection>
</sql:config>

...only the "table3" table gets updated.

I also tried:

<sql:config xmlns:sql="http://orbeon.org/oxf/xml/sql">
     <sql:connection>
         <sql:execute>
             <sql:update>Insert into table1 (col1, col2,) values
('foo',bar')</sql:update>
         </sql:execute>
     </sql:connection>
     <sql:connection>
         <sql:execute>
             <sql:update>Insert into table2 (col1, col2,) values
('foo',bar')</sql:update>
         </sql:execute>
     </sql:connection>
     <sql:connection>
         <sql:execute>
             <sql:update>Insert into table3 (col1, col2,) values
('foo',bar')</sql:update>
         </sql:execute>
     </sql:connection>
</sql:config>

...with the same result.




--
You receive this message as a subscriber of the [hidden email] mailing list.
To unsubscribe: mailto:[hidden email]
For general help: mailto:[hidden email]?subject=help
ObjectWeb mailing lists service home page: http://www.objectweb.org/wws
Reply | Threaded
Open this post in threaded view
|

Re: updating multiple tables with sql processor

Alexander Žaťko
I have made several more attempts to move forward with developing my
app:

1) divide the updates of individual tables into separate sql
processors. This did not help - still the only table affected is the
one targetted with the last sql processor.

2) upgrade to the newest ops beta (3). No effect - same behavior.

I used the MS SQL Server Profiler to trace the request issued by OPS.
Below is a typical trace. The only thing that I can think of is that
the "COMMIT" command at the end of the listing applies only to the last
insert? If this is the case then this would be pointing to a bug in OPS
which should either group the individual inserts somehow, or issue a
commit after each insert. I do not know SQL enough to determine what
the problem is.

Event Class Text Start Time
  Connect 10:43:51.737
  ExistingConnection 10:43:51.737
+SQL:BatchStarting SELECT @@MAX_PRECISION SET TRANSACTION ISOLATION
LEVEL READ COMMITTED SET IMPLICIT_TRANSACTIONS OFF SET
QUOTED_IDENTIFIER ON SET TEXTSIZE 2147483647 10:43:51.777
+SQL:BatchStarting SET IMPLICIT_TRANSACTIONS ON 10:43:51.927
+RPC:Starting sp_prepare @P1 output, , N'Insert into table1 (col1,col2)
values (1,20050917)', 1 select @P1 10:43:51.957
+SQL:BatchStarting Insert into table1 (col1,col2) values
(1,20050917) 10:43:52.107
+RPC:Starting sp_prepare @P1 output, , N'Insert into table2 (col1,col2)
values (1,"A"), 1 select @P1 10:43:52.357
+SQL:BatchStarting Insert into table2 (col1,col2) values (1,'A')
10:43:52.387
+RPC:Starting sp_prepare @P1 output, , N'Insert into table3 (col1,col2)
values (1,20050917)', 1 select @P1 10:43:52.447
+SQL:BatchStarting Insert into table3 (col1,col2) values
(1,20050917) 10:43:52.467
+SQL:BatchStarting IF @@TRANCOUNT > 0 COMMIT TRAN 10:43:53.760
  Disconnect 10:43:51.737

thanks

A.

On Sep 14, 2005, at 9:39 AM, Alexander Zatko wrote:

> Context of the issue:
> PresentationServer Release 3.0.beta2.200507191809
> JTDS JDBC driver
>
> Is it possible to insert records into more than one table within one
> invocation of the oxf:sql processor?
>
> When I construct the config file like this...
>
> <sql:config xmlns:sql="http://orbeon.org/oxf/xml/sql">
>     <sql:connection>
>         <sql:execute>
>             <sql:update>Insert into table1 (col1, col2,) values
> ('foo',bar')</sql:update>
>         </sql:execute>
>         <sql:execute>
>             <sql:update>Insert into table2 (col1, col2,) values
> ('foo',bar')</sql:update>
>         </sql:execute>
>         <sql:execute>
>             <sql:update>Insert into table3 (col1, col2,) values
> ('foo',bar')</sql:update>
>         </sql:execute>
>     </sql:connection>
> </sql:config>
>
> ...only the "table3" table gets updated.
>
> I also tried:
>
> <sql:config xmlns:sql="http://orbeon.org/oxf/xml/sql">
>     <sql:connection>
>         <sql:execute>
>             <sql:update>Insert into table1 (col1, col2,) values
> ('foo',bar')</sql:update>
>         </sql:execute>
>     </sql:connection>
>     <sql:connection>
>         <sql:execute>
>             <sql:update>Insert into table2 (col1, col2,) values
> ('foo',bar')</sql:update>
>         </sql:execute>
>     </sql:connection>
>     <sql:connection>
>         <sql:execute>
>             <sql:update>Insert into table3 (col1, col2,) values
> ('foo',bar')</sql:update>
>         </sql:execute>
>     </sql:connection>
> </sql:config>
>
> ...with the same result.
>



--
You receive this message as a subscriber of the [hidden email] mailing list.
To unsubscribe: mailto:[hidden email]
For general help: mailto:[hidden email]?subject=help
ObjectWeb mailing lists service home page: http://www.objectweb.org/wws
Reply | Threaded
Open this post in threaded view
|

Re: updating multiple tables with sql processor

Erik Bruchez
Administrator
In reply to this post by Alexander Žaťko
> I have made several more attempts to move forward with developing my
> app:
>
> 1) divide the updates of individual tables into separate sql
> processors. This did not help - still the only table affected is the
> one targetted with the last sql processor.

Yes, whether the updates are done in the same SQL processor or
different ones does not matter.

>
> 2) upgrade to the newest ops beta (3). No effect - same behavior.

Correct, nothing has change in this respect in beta 3.

> I used the MS SQL Server Profiler to trace the request issued by OPS.
> Below is a typical trace. The only thing that I can think of is that
> the "COMMIT" command at the end of the listing applies only to the last
> insert? If this is the case then this would be pointing to a bug in OPS
> which should either group the individual inserts somehow, or issue a
> commit after each insert. I do not know SQL enough to determine what
> the problem is.

The commit or rollback is done at the end of the HTTP request. You can
try to manually issue a "commit" command in SQL from the SQL processor
and see if that change the behavior.

You may also want to check what JDBC driver you are using with SQL
Server. MS has a new beta driver on their web site which supports JDBC
much better. You .
may also want to check the open sourd jTDS (if memory serves).

This BTW should work, as we really don't do anything fancy: just
execute statements on a JDBC connection, and then rollback or commit
the connection in the end.

-Erik

>
> Event Class     Text    Start Time
>  Connect               10:43:51.737
>  ExistingConnection            10:43:51.737
> +SQL:BatchStarting      SELECT @@MAX_PRECISION SET TRANSACTION ISOLATION
> LEVEL READ COMMITTED SET IMPLICIT_TRANSACTIONS OFF SET
> QUOTED_IDENTIFIER ON SET TEXTSIZE 2147483647    10:43:51.777
> +SQL:BatchStarting      SET IMPLICIT_TRANSACTIONS ON    10:43:51.927
> +RPC:Starting   sp_prepare @P1 output, , N'Insert into table1 (col1,col2)
> values (1,20050917)', 1 select @P1      10:43:51.957
> +SQL:BatchStarting      Insert into table1 (col1,col2) values
> (1,20050917)    10:43:52.107
> +RPC:Starting   sp_prepare @P1 output, , N'Insert into table2 (col1,col2)
> values (1,"A"), 1 select @P1    10:43:52.357
> +SQL:BatchStarting      Insert into table2 (col1,col2) values (1,'A')
> 10:43:52.387
> +RPC:Starting   sp_prepare @P1 output, , N'Insert into table3 (col1,col2)
> values (1,20050917)', 1 select @P1      10:43:52.447
> +SQL:BatchStarting      Insert into table3 (col1,col2) values
> (1,20050917)    10:43:52.467
> +SQL:BatchStarting      IF @@TRANCOUNT > 0 COMMIT TRAN  10:43:53.760
>  Disconnect            10:43:51.737
>
> thanks
>
> A.
>
> On Sep 14, 2005, at 9:39 AM, Alexander Zatko wrote:
>
> > Context of the issue:
> > PresentationServer Release 3.0.beta2.200507191809
> > JTDS JDBC driver
> >
> > Is it possible to insert records into more than one table within one
> > invocation of the oxf:sql processor?
> >
> > When I construct the config file like this...
> >
> > <sql:config xmlns:sql="http://orbeon.org/oxf/xml/sql">
> >     <sql:connection>
> >         <sql:execute>
> >             <sql:update>Insert into table1 (col1, col2,) values
> > ('foo',bar')</sql:update>
> >         </sql:execute>
> >         <sql:execute>
> >             <sql:update>Insert into table2 (col1, col2,) values
> > ('foo',bar')</sql:update>
> >         </sql:execute>
> >         <sql:execute>
> >             <sql:update>Insert into table3 (col1, col2,) values
> > ('foo',bar')</sql:update>
> >         </sql:execute>
> >     </sql:connection>
> > </sql:config>
> >
> > ...only the "table3" table gets updated.
> >
> > I also tried:
> >
> > <sql:config xmlns:sql="http://orbeon.org/oxf/xml/sql">
> >     <sql:connection>
> >         <sql:execute>
> >             <sql:update>Insert into table1 (col1, col2,) values
> > ('foo',bar')</sql:update>
> >         </sql:execute>
> >     </sql:connection>
> >     <sql:connection>
> >         <sql:execute>
> >             <sql:update>Insert into table2 (col1, col2,) values
> > ('foo',bar')</sql:update>
> >         </sql:execute>
> >     </sql:connection>
> >     <sql:connection>
> >         <sql:execute>
> >             <sql:update>Insert into table3 (col1, col2,) values
> > ('foo',bar')</sql:update>
> >         </sql:execute>
> >     </sql:connection>
> > </sql:config>
> >
> > ...with the same result.
> >
>
>
>
>
> ----- End forwarded message -----
>
>
>
> --
> You receive this message as a subscriber of the [hidden email] mailing list.
> To unsubscribe: mailto:[hidden email]
> For general help: mailto:[hidden email]?subject=help
> ObjectWeb mailing lists service home page: http://www.objectweb.org/wws
>
>
>


--
You receive this message as a subscriber of the [hidden email] mailing list.
To unsubscribe: mailto:[hidden email]
For general help: mailto:[hidden email]?subject=help
ObjectWeb mailing lists service home page: http://www.objectweb.org/wws
Reply | Threaded
Open this post in threaded view
|

Re: Re: updating multiple tables with sql processor

Alexander Žaťko
Erik,

Adding a commit command after each insert solved the problem - thanks.

I am using jtds JDBC driver.

Alex.

On Sep 17, 2005, at 2:46 PM, Erik Bruchez wrote:

>> I have made several more attempts to move forward with developing my
>> app:
>>
>> 1) divide the updates of individual tables into separate sql
>> processors. This did not help - still the only table affected is the
>> one targetted with the last sql processor.
>
> Yes, whether the updates are done in the same SQL processor or
> different ones does not matter.
>
>>
>> 2) upgrade to the newest ops beta (3). No effect - same behavior.
>
> Correct, nothing has change in this respect in beta 3.
>
>> I used the MS SQL Server Profiler to trace the request issued by OPS.
>> Below is a typical trace. The only thing that I can think of is that
>> the "COMMIT" command at the end of the listing applies only to the
>> last
>> insert? If this is the case then this would be pointing to a bug in
>> OPS
>> which should either group the individual inserts somehow, or issue a
>> commit after each insert. I do not know SQL enough to determine what
>> the problem is.
>
> The commit or rollback is done at the end of the HTTP request. You can
> try to manually issue a "commit" command in SQL from the SQL processor
> and see if that change the behavior.
>
> You may also want to check what JDBC driver you are using with SQL
> Server. MS has a new beta driver on their web site which supports JDBC
> much better. You .
> may also want to check the open sourd jTDS (if memory serves).
>
> This BTW should work, as we really don't do anything fancy: just
> execute statements on a JDBC connection, and then rollback or commit
> the connection in the end.
>
> -Erik
>
>>
>> Event Class     Text    Start Time
>>  Connect               10:43:51.737
>>  ExistingConnection            10:43:51.737
>> +SQL:BatchStarting      SELECT @@MAX_PRECISION SET TRANSACTION
>> ISOLATION
>> LEVEL READ COMMITTED SET IMPLICIT_TRANSACTIONS OFF SET
>> QUOTED_IDENTIFIER ON SET TEXTSIZE 2147483647    10:43:51.777
>> +SQL:BatchStarting      SET IMPLICIT_TRANSACTIONS ON    10:43:51.927
>> +RPC:Starting   sp_prepare @P1 output, , N'Insert into table1
>> (col1,col2)
>> values (1,20050917)', 1 select @P1      10:43:51.957
>> +SQL:BatchStarting      Insert into table1 (col1,col2) values
>> (1,20050917)    10:43:52.107
>> +RPC:Starting   sp_prepare @P1 output, , N'Insert into table2
>> (col1,col2)
>> values (1,"A"), 1 select @P1    10:43:52.357
>> +SQL:BatchStarting      Insert into table2 (col1,col2) values (1,'A')
>> 10:43:52.387
>> +RPC:Starting   sp_prepare @P1 output, , N'Insert into table3
>> (col1,col2)
>> values (1,20050917)', 1 select @P1      10:43:52.447
>> +SQL:BatchStarting      Insert into table3 (col1,col2) values
>> (1,20050917)    10:43:52.467
>> +SQL:BatchStarting      IF @@TRANCOUNT > 0 COMMIT TRAN  10:43:53.760
>>  Disconnect            10:43:51.737
>>
>> thanks
>>
>> A.
>>
>> On Sep 14, 2005, at 9:39 AM, Alexander Zatko wrote:
>>
>>> Context of the issue:
>>> PresentationServer Release 3.0.beta2.200507191809
>>> JTDS JDBC driver
>>>
>>> Is it possible to insert records into more than one table within one
>>> invocation of the oxf:sql processor?
>>>
>>> When I construct the config file like this...
>>>
>>> <sql:config xmlns:sql="http://orbeon.org/oxf/xml/sql">
>>>     <sql:connection>
>>>         <sql:execute>
>>>             <sql:update>Insert into table1 (col1, col2,) values
>>> ('foo',bar')</sql:update>
>>>         </sql:execute>
>>>         <sql:execute>
>>>             <sql:update>Insert into table2 (col1, col2,) values
>>> ('foo',bar')</sql:update>
>>>         </sql:execute>
>>>         <sql:execute>
>>>             <sql:update>Insert into table3 (col1, col2,) values
>>> ('foo',bar')</sql:update>
>>>         </sql:execute>
>>>     </sql:connection>
>>> </sql:config>
>>>
>>> ...only the "table3" table gets updated.
>>>
>>> I also tried:
>>>
>>> <sql:config xmlns:sql="http://orbeon.org/oxf/xml/sql">
>>>     <sql:connection>
>>>         <sql:execute>
>>>             <sql:update>Insert into table1 (col1, col2,) values
>>> ('foo',bar')</sql:update>
>>>         </sql:execute>
>>>     </sql:connection>
>>>     <sql:connection>
>>>         <sql:execute>
>>>             <sql:update>Insert into table2 (col1, col2,) values
>>> ('foo',bar')</sql:update>
>>>         </sql:execute>
>>>     </sql:connection>
>>>     <sql:connection>
>>>         <sql:execute>
>>>             <sql:update>Insert into table3 (col1, col2,) values
>>> ('foo',bar')</sql:update>
>>>         </sql:execute>
>>>     </sql:connection>
>>> </sql:config>
>>>
>>> ...with the same result.
>>>
>>
>>
>>
>>
>> ----- End forwarded message -----
>>
>>
>>
>> --
>> You receive this message as a subscriber of the
>> [hidden email] mailing list.
>> To unsubscribe: mailto:[hidden email]
>> For general help: mailto:[hidden email]?subject=help
>> ObjectWeb mailing lists service home page:
>> http://www.objectweb.org/wws
>>
>>
>>
>
>
> --
> You receive this message as a subscriber of the
> [hidden email] mailing list.
> To unsubscribe: mailto:[hidden email]
> For general help: mailto:[hidden email]?subject=help
> ObjectWeb mailing lists service home page: http://www.objectweb.org/wws



--
You receive this message as a subscriber of the [hidden email] mailing list.
To unsubscribe: mailto:[hidden email]
For general help: mailto:[hidden email]?subject=help
ObjectWeb mailing lists service home page: http://www.objectweb.org/wws
Reply | Threaded
Open this post in threaded view
|

RE: Re: updating multiple tables with sql processor

Stephen Bayliss
In reply to this post by Alexander Žaťko
It might be worth trying with
SET IMPLICIT_TRANSACTIONS OFF
(instead of on)
Then explicitly doing a BEGIN TRAN at the beginning and a COMMIT TRAN at
the end.


-----Original Message-----
From: Alexander Zatko [mailto:[hidden email]]
Sent: 18 September 2005 11:49
To: [hidden email]
Subject: Re: [ops-users] Re: updating multiple tables with sql processor

Erik,

Adding a commit command after each insert solved the problem - thanks.

I am using jtds JDBC driver.

Alex.

On Sep 17, 2005, at 2:46 PM, Erik Bruchez wrote:

>> I have made several more attempts to move forward with developing my
>> app:
>>
>> 1) divide the updates of individual tables into separate sql
>> processors. This did not help - still the only table affected is the
>> one targetted with the last sql processor.
>
> Yes, whether the updates are done in the same SQL processor or
> different ones does not matter.
>
>>
>> 2) upgrade to the newest ops beta (3). No effect - same behavior.
>
> Correct, nothing has change in this respect in beta 3.
>
>> I used the MS SQL Server Profiler to trace the request issued by OPS.
>> Below is a typical trace. The only thing that I can think of is that
>> the "COMMIT" command at the end of the listing applies only to the
>> last
>> insert? If this is the case then this would be pointing to a bug in
>> OPS
>> which should either group the individual inserts somehow, or issue a
>> commit after each insert. I do not know SQL enough to determine what
>> the problem is.
>
> The commit or rollback is done at the end of the HTTP request. You can
> try to manually issue a "commit" command in SQL from the SQL processor
> and see if that change the behavior.
>
> You may also want to check what JDBC driver you are using with SQL
> Server. MS has a new beta driver on their web site which supports JDBC
> much better. You .
> may also want to check the open sourd jTDS (if memory serves).
>
> This BTW should work, as we really don't do anything fancy: just
> execute statements on a JDBC connection, and then rollback or commit
> the connection in the end.
>
> -Erik
>
>>
>> Event Class     Text    Start Time
>>  Connect               10:43:51.737
>>  ExistingConnection            10:43:51.737
>> +SQL:BatchStarting      SELECT @@MAX_PRECISION SET TRANSACTION
>> ISOLATION
>> LEVEL READ COMMITTED SET IMPLICIT_TRANSACTIONS OFF SET
>> QUOTED_IDENTIFIER ON SET TEXTSIZE 2147483647    10:43:51.777
>> +SQL:BatchStarting      SET IMPLICIT_TRANSACTIONS ON    10:43:51.927
>> +RPC:Starting   sp_prepare @P1 output, , N'Insert into table1
>> (col1,col2)
>> values (1,20050917)', 1 select @P1      10:43:51.957
>> +SQL:BatchStarting      Insert into table1 (col1,col2) values
>> (1,20050917)    10:43:52.107
>> +RPC:Starting   sp_prepare @P1 output, , N'Insert into table2
>> (col1,col2)
>> values (1,"A"), 1 select @P1    10:43:52.357
>> +SQL:BatchStarting      Insert into table2 (col1,col2) values (1,'A')
>> 10:43:52.387
>> +RPC:Starting   sp_prepare @P1 output, , N'Insert into table3
>> (col1,col2)
>> values (1,20050917)', 1 select @P1      10:43:52.447
>> +SQL:BatchStarting      Insert into table3 (col1,col2) values
>> (1,20050917)    10:43:52.467
>> +SQL:BatchStarting      IF @@TRANCOUNT > 0 COMMIT TRAN  10:43:53.760
>>  Disconnect            10:43:51.737
>>
>> thanks
>>
>> A.
>>
>> On Sep 14, 2005, at 9:39 AM, Alexander Zatko wrote:
>>
>>> Context of the issue:
>>> PresentationServer Release 3.0.beta2.200507191809
>>> JTDS JDBC driver
>>>
>>> Is it possible to insert records into more than one table within one
>>> invocation of the oxf:sql processor?
>>>
>>> When I construct the config file like this...
>>>
>>> <sql:config xmlns:sql="http://orbeon.org/oxf/xml/sql">
>>>     <sql:connection>
>>>         <sql:execute>
>>>             <sql:update>Insert into table1 (col1, col2,) values
>>> ('foo',bar')</sql:update>
>>>         </sql:execute>
>>>         <sql:execute>
>>>             <sql:update>Insert into table2 (col1, col2,) values
>>> ('foo',bar')</sql:update>
>>>         </sql:execute>
>>>         <sql:execute>
>>>             <sql:update>Insert into table3 (col1, col2,) values
>>> ('foo',bar')</sql:update>
>>>         </sql:execute>
>>>     </sql:connection>
>>> </sql:config>
>>>
>>> ...only the "table3" table gets updated.
>>>
>>> I also tried:
>>>
>>> <sql:config xmlns:sql="http://orbeon.org/oxf/xml/sql">
>>>     <sql:connection>
>>>         <sql:execute>
>>>             <sql:update>Insert into table1 (col1, col2,) values
>>> ('foo',bar')</sql:update>
>>>         </sql:execute>
>>>     </sql:connection>
>>>     <sql:connection>
>>>         <sql:execute>
>>>             <sql:update>Insert into table2 (col1, col2,) values
>>> ('foo',bar')</sql:update>
>>>         </sql:execute>
>>>     </sql:connection>
>>>     <sql:connection>
>>>         <sql:execute>
>>>             <sql:update>Insert into table3 (col1, col2,) values
>>> ('foo',bar')</sql:update>
>>>         </sql:execute>
>>>     </sql:connection>
>>> </sql:config>
>>>
>>> ...with the same result.
>>>
>>
>>
>>
>>
>> ----- End forwarded message -----
>>
>>
>>
>> --
>> You receive this message as a subscriber of the
>> [hidden email] mailing list.
>> To unsubscribe: mailto:[hidden email]
>> For general help: mailto:[hidden email]?subject=help
>> ObjectWeb mailing lists service home page:
>> http://www.objectweb.org/wws
>>
>>
>>
>
>
> --
> You receive this message as a subscriber of the
> [hidden email] mailing list.
> To unsubscribe: mailto:[hidden email]
> For general help: mailto:[hidden email]?subject=help
> ObjectWeb mailing lists service home page:
http://www.objectweb.org/wws






--
You receive this message as a subscriber of the [hidden email] mailing list.
To unsubscribe: mailto:[hidden email]
For general help: mailto:[hidden email]?subject=help
ObjectWeb mailing lists service home page: http://www.objectweb.org/wws
Reply | Threaded
Open this post in threaded view
|

Re: Re: updating multiple tables with sql processor

Alexander Žaťko
That crossed my mind and I might do that eventually, but it looks like
in this particular case I do not need to worry about wrapping the
inserts into one transaction.

Thanks anyway.

A.


On Sep 20, 2005, at 3:35 AM, Stephen Bayliss wrote:

> It might be worth trying with
> SET IMPLICIT_TRANSACTIONS OFF
> (instead of on)
> Then explicitly doing a BEGIN TRAN at the beginning and a COMMIT TRAN
> at
> the end.
>
> -----Original Message-----
> From: Alexander Zatko [mailto:[hidden email]]
> Sent: 18 September 2005 11:49
> To: [hidden email]
> Subject: Re: [ops-users] Re: updating multiple tables with sql
> processor
>
> Erik,
>
> Adding a commit command after each insert solved the problem - thanks.
>
> I am using jtds JDBC driver.
>
> Alex.
>
> On Sep 17, 2005, at 2:46 PM, Erik Bruchez wrote:
>
>>> I have made several more attempts to move forward with developing my
>>> app:
>>>
>>> 1) divide the updates of individual tables into separate sql
>>> processors. This did not help - still the only table affected is the
>>> one targetted with the last sql processor.
>>
>> Yes, whether the updates are done in the same SQL processor or
>> different ones does not matter.
>>
>>>
>>> 2) upgrade to the newest ops beta (3). No effect - same behavior.
>>
>> Correct, nothing has change in this respect in beta 3.
>>
>>> I used the MS SQL Server Profiler to trace the request issued by OPS.
>>> Below is a typical trace. The only thing that I can think of is that
>>> the "COMMIT" command at the end of the listing applies only to the
>>> last
>>> insert? If this is the case then this would be pointing to a bug in
>>> OPS
>>> which should either group the individual inserts somehow, or issue a
>>> commit after each insert. I do not know SQL enough to determine what
>>> the problem is.
>>
>> The commit or rollback is done at the end of the HTTP request. You can
>> try to manually issue a "commit" command in SQL from the SQL processor
>> and see if that change the behavior.
>>
>> You may also want to check what JDBC driver you are using with SQL
>> Server. MS has a new beta driver on their web site which supports JDBC
>> much better. You .
>> may also want to check the open sourd jTDS (if memory serves).
>>
>> This BTW should work, as we really don't do anything fancy: just
>> execute statements on a JDBC connection, and then rollback or commit
>> the connection in the end.
>>
>> -Erik
>>
>>>
>>> Event Class     Text    Start Time
>>>  Connect               10:43:51.737
>>>  ExistingConnection            10:43:51.737
>>> +SQL:BatchStarting      SELECT @@MAX_PRECISION SET TRANSACTION
>>> ISOLATION
>>> LEVEL READ COMMITTED SET IMPLICIT_TRANSACTIONS OFF SET
>>> QUOTED_IDENTIFIER ON SET TEXTSIZE 2147483647    10:43:51.777
>>> +SQL:BatchStarting      SET IMPLICIT_TRANSACTIONS ON    10:43:51.927
>>> +RPC:Starting   sp_prepare @P1 output, , N'Insert into table1
>>> (col1,col2)
>>> values (1,20050917)', 1 select @P1      10:43:51.957
>>> +SQL:BatchStarting      Insert into table1 (col1,col2) values
>>> (1,20050917)    10:43:52.107
>>> +RPC:Starting   sp_prepare @P1 output, , N'Insert into table2
>>> (col1,col2)
>>> values (1,"A"), 1 select @P1    10:43:52.357
>>> +SQL:BatchStarting      Insert into table2 (col1,col2) values (1,'A')
>>> 10:43:52.387
>>> +RPC:Starting   sp_prepare @P1 output, , N'Insert into table3
>>> (col1,col2)
>>> values (1,20050917)', 1 select @P1      10:43:52.447
>>> +SQL:BatchStarting      Insert into table3 (col1,col2) values
>>> (1,20050917)    10:43:52.467
>>> +SQL:BatchStarting      IF @@TRANCOUNT > 0 COMMIT TRAN  10:43:53.760
>>>  Disconnect            10:43:51.737
>>>
>>> thanks
>>>
>>> A.
>>>
>>> On Sep 14, 2005, at 9:39 AM, Alexander Zatko wrote:
>>>
>>>> Context of the issue:
>>>> PresentationServer Release 3.0.beta2.200507191809
>>>> JTDS JDBC driver
>>>>
>>>> Is it possible to insert records into more than one table within one
>>>> invocation of the oxf:sql processor?
>>>>
>>>> When I construct the config file like this...
>>>>
>>>> <sql:config xmlns:sql="http://orbeon.org/oxf/xml/sql">
>>>>     <sql:connection>
>>>>         <sql:execute>
>>>>             <sql:update>Insert into table1 (col1, col2,) values
>>>> ('foo',bar')</sql:update>
>>>>         </sql:execute>
>>>>         <sql:execute>
>>>>             <sql:update>Insert into table2 (col1, col2,) values
>>>> ('foo',bar')</sql:update>
>>>>         </sql:execute>
>>>>         <sql:execute>
>>>>             <sql:update>Insert into table3 (col1, col2,) values
>>>> ('foo',bar')</sql:update>
>>>>         </sql:execute>
>>>>     </sql:connection>
>>>> </sql:config>
>>>>
>>>> ...only the "table3" table gets updated.
>>>>
>>>> I also tried:
>>>>
>>>> <sql:config xmlns:sql="http://orbeon.org/oxf/xml/sql">
>>>>     <sql:connection>
>>>>         <sql:execute>
>>>>             <sql:update>Insert into table1 (col1, col2,) values
>>>> ('foo',bar')</sql:update>
>>>>         </sql:execute>
>>>>     </sql:connection>
>>>>     <sql:connection>
>>>>         <sql:execute>
>>>>             <sql:update>Insert into table2 (col1, col2,) values
>>>> ('foo',bar')</sql:update>
>>>>         </sql:execute>
>>>>     </sql:connection>
>>>>     <sql:connection>
>>>>         <sql:execute>
>>>>             <sql:update>Insert into table3 (col1, col2,) values
>>>> ('foo',bar')</sql:update>
>>>>         </sql:execute>
>>>>     </sql:connection>
>>>> </sql:config>
>>>>
>>>> ...with the same result.
>>>>
>>>
>>>
>>>
>>>
>>> ----- End forwarded message -----
>>>
>>>
>>>
>>> --
>>> You receive this message as a subscriber of the
>>> [hidden email] mailing list.
>>> To unsubscribe: mailto:[hidden email]
>>> For general help: mailto:[hidden email]?subject=help
>>> ObjectWeb mailing lists service home page:
>>> http://www.objectweb.org/wws
>>>
>>>
>>>
>>
>>
>> --
>> You receive this message as a subscriber of the
>> [hidden email] mailing list.
>> To unsubscribe: mailto:[hidden email]
>> For general help: mailto:[hidden email]?subject=help
>> ObjectWeb mailing lists service home page:
> http://www.objectweb.org/wws
>
>
> --
> You receive this message as a subscriber of the
> [hidden email] mailing list.
> To unsubscribe: mailto:[hidden email]
> For general help: mailto:[hidden email]?subject=help
> ObjectWeb mailing lists service home page: http://www.objectweb.org/wws



--
You receive this message as a subscriber of the [hidden email] mailing list.
To unsubscribe: mailto:[hidden email]
For general help: mailto:[hidden email]?subject=help
ObjectWeb mailing lists service home page: http://www.objectweb.org/wws