cocoon-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Luis Gois" <>
Subject RE : ESQL and 'lock'ing tables for exclusive access
Date Fri, 01 Jun 2001 17:13:32 GMT

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:query>lock table...</esql:query>
              <esql:query>process table...</esql:query>
  <esql:query>unlock table...</esql:query>

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


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


----- Original Message -----
From: "Luis Gois" <>
To: "cocoon-users" <>
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
> 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 --
> ---------------------------------------------------------------------
> Please check that your question has not already been answered in the
> FAQ before posting. <>
> To unsubscribe, e-mail: <>
> For additional commands, e-mail: <>

View raw message