Re: Check before INSERT INTO

Thanks for the replies.. But my problem still continues even after setting
the isolation level.

Set oRs = oConn.Execute("SELECT N_GEN FROM MY_TABLE ORDER BY N_GEN::INT
DESC")

If err <> 0 then 'If table not found
GetFieldValue = "1"
WriteToFile logfilepath, date & " " & time & " -- no table Numero
progressivo: 1" & vbcrlf , True
else
BEGIN
SET TRANSACTION = "ISOLATION LEVEL SERIALIZABLE"
if tipo_scheda = "SINGOLA" then
GetFieldValue = oRs("N_GEN") + 1
else
GetFieldValue = oRs("N_GEN")
end if
end if
COMMIT

Debugging my code(with logs) i see that still when 2 users login at the same
time the N_GEN value is the same. (they enter the first if)
In "My_Table" the last record has the value "5" so the next user that logs
in shoul get the value "6". Sadly both the current users get the value "6".

Have i set the isolation level correctly??

Thanks

Shavonne

----- Original Message -----
From: "Jean-David Beyer" <jeandavid8(at)verizon(dot)net>
To: <pgsql-sql(at)postgresql(dot)org>
Sent: Monday, February 11, 2008 5:55 PM
Subject: Re: [SQL] Check before INSERT INTO

> Shavonne Marietta Wijesinghe wrote:
>> Thanks for the reply Grogory. I am trying to do a INSERT INTO.
>>
>> Here is my table. n_gen, n_sheet, tot_n_sheet are defined as Primary Key
>> (Serial not null)
>
> That is sure confusing. What could a DDL saying
>
> INTEGER n_gen SERIAL NOT NULL;
> INTEGER n_sheet SERIAL NOT NULL;
> INTEGER tot_n_sheet SERIAL NOT NULL;
> PRIMARY KEY (n_gen, n_sheet, tot_n_sheet)
>
> mean? Is this what your DDL says? Do you enter rows of this table
> specifying
> the id and expecting the three serial generators to pick non-null
> sequential
> numbers for the other three fields? I think you are very unclear about
> what
> is going on here. Are you perhaps saying the table has three (distinct)
> primary keys? Because if you assign them values, why would they be SERIAL?
>
>> id | n_gen | n_sheet | tot_n_sheet
>> ----------+-----------+-----------+-------------
>> a | 1 | 1 | 1
>> b | 2 | 1 | 2
>> x | 2 | 2 | 2
>> u | 3 | 1 | 1
>> r | 4 | 1 | 3
>> a | 4 | 2 | 3
>> s | 4 | 3 | 3
>>
>>
>> So there are 2 users inserting in to the db. In my ASP page i have a
>> field that shows the value of n_gen +1. So when the 2 users both login
>> at the same time, with different sessions, they both see "7" in the
>> n_gen field. But when they click on the sumbit button only one record is
>> inserted and the other is lost.
>
> Whatever you are doing that I do not understand with your keys, if you
> have
> two users doing inserts on the same table, would you not have to run this
> with Serializable Isolation Level (12.2.2. in the manual)? Would this not
> fix your problem especially if you have a SERIAL as primary key?
>>
>> I though it was possible to change the SQL string before it does the
>> update.. But i can't seem to find a solution for it.. Any idea ??
>>
>
>
>
> --
> .~. Jean-David Beyer Registered Linux User 85642.
> /V\ PGP-Key: 9A2FC99A Registered Machine 241939.
> /( )\ Shrewsbury, New Jersey http://counter.li.org
> ^^-^^ 11:35:01 up 18 days, 1:21, 1 user, load average: 4.22, 4.28, 4.27
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org