RE : ESQL and 'lock'ing tables for exclusive access

[Luis Gois <lu...@netc.pt>]

Matt :

I'm using Informix as my DBMS, but I'm stuck with the format of a database already created and populated, in which we have a column with a unique identifier, which gets incremented before each row insertion ( 'MAX(column-to-be-inc) + 1' on my INSERT statement). So those autoincrement features must be excluded from my range of possibilities. 

Donald :

    One of my coleagues tried something similar to what you've sugested, but the connection was getting closed after the lock statement, therefore loosing the lock! But looking more carefully at your code, I think he was doing something like this :

<esql:connection>
 <esql:execute-query>
  <esql:query>lock table...</esql:query>
 </esql:execute-query>
<esql:results>
    <esql:connection>
         <esql:execute-query>
              <esql:query>process table...</esql:query>
          </esql:execute-query>
    </esql:connection>
</esql:results>
 <esql:execute-query>
  <esql:query>unlock table...</esql:query>
 </esql:execute-query>
</esql:connection>

    Could this be the reason for a closed connection after the 'lock tables' stat?

Thanks!!!!


>Hi, are you doing this to have a unique key for each row?  If so I'd bet
>your db has some sort of functionality for this, be it sequences (oracle,
>postgres), or auto-increment fields (mysql). I'd be surprised if any major
>db does not have some sort of atomic numbering stystem like these.

>If this doesn't help, can you perhaps explain exactly what you want (and
>what db) as locking the entire table seems drastic and most likely will
>perform poorly.

>-matt

----- Original Message -----
From: "Luis Gois" <lg...@despodata.pt>
To: "cocoon-users" <co...@xml.apache.org>
Sent: Wednesday, May 23, 2001 1:40 PM
Subject: ESQL and 'lock'ing tables for exclusive access


>
> Hello!
>
> I need to have exclusive (read/wite) access to a table for selecting a
> MAX() value of a column, then I must insert a new row with value MAX()+1
> in the column from where the MAX() was obtained. How can I use locks for
> this?
>
> I tried
> SET LOCK MODE TO WAIT
> LOCK TABLE table IN EXCLUSIVE MODE
>
> and then
> SELECT MAX(column_name) FROM table
>
> and finally
> INSERT INTO table (column_name) VALUES (<xsp:expr>max+1</xsp:expr>)
>
> All of this using ESQL in a way that can be found at the cocoon samples
> :
>
> <esql:connection> (...)
> <esql:results>
> <esql:connection> (...)
> <esql:results>
> <esql:connection> (...)
>
> This looks really ugly for me (but it seemed logical for the guy who
> did it ... and I understand why he did it this way ... I could also make
> a logicsheet for a simpler use of this ESQL structure, as I did for a
> pageable index generation of results), and also doesn't work. Is there a
> more simple way to do it. Couldn't I just modify this to a single or
> prepared query to get advantage of SQL atomic operation for each query?
> (I'm using Informix DBMS, though I also use MySQL and I know that  a
> SELECT 'inside' another SELECT isn't suported - at least in the MySQL
> version I have installed)
>
>
> I really apreciate any hint you may give me!
> Thanks in advance!
> --
> Luis A. Gois
> ---
> Projecto WAP -- DESPODATA/INESC --
> http://theclints.inesc.pt/~jts/wap.html
>
> ---------------------------------------------------------------------
> Please check that your question has not already been answered in the
> FAQ before posting. <http://xml.apache.org/cocoon/faqs.html>
>
> To unsubscribe, e-mail: <co...@xml.apache.org>
> For additional commands, e-mail: <co...@xml.apache.org>
>

Re: RE : ESQL and 'lock'ing tables for exclusive access

[Donald Ball <ba...@webslingerZ.com>]

On Fri, 1 Jun 2001, Luis Gois wrote:

> Donald :
>
>     One of my coleagues tried something similar to what you've
> sugested, but the connection was getting closed after the lock
> statement, therefore loosing the lock! But looking more carefully at
> your code, I think he was doing something like this :
>
> <esql:connection>
>  <esql:execute-query>
>   <esql:query>lock table...</esql:query>
>  </esql:execute-query>
> <esql:results>
>     <esql:connection>
>          <esql:execute-query>
>               <esql:query>process table...</esql:query>
>           </esql:execute-query>
>     </esql:connection>
> </esql:results>
>  <esql:execute-query>
>   <esql:query>unlock table...</esql:query>
>  </esql:execute-query>
> </esql:connection>

that's invalid esql. the esql:results element must appear underneath an
esql:execute-query element.

- donald


---------------------------------------------------------------------
Please check that your question has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faqs.html>

To unsubscribe, e-mail: <co...@xml.apache.org>
For additional commands, e-mail: <co...@xml.apache.org>