Message-ID: <00ae01c86d5e$0f2b2ba0$3102a8c0@dream>
Reply-To: "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it>
From: "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it>
To: "Jean-David Beyer" <jeandavid8@verizon.net>,
	<pgsql-sql@postgresql.org>
References: <010901c86ca6$3b66e870$3102a8c0@dream>
	<873arzscnu.fsf@oxford.xeocode.com>
	<024301c86cca$591c9c60$3102a8c0@dream>
	<47B07DFA.8030003@verizon.net>
Subject: Re: Check before INSERT INTO
Date: Tue, 12 Feb 2008 11:00:12 +0100
Organization: Studio FORM
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
	reply-type=original
Content-Transfer-Encoding: 7bit

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@verizon.net>
To: <pgsql-sql@postgresql.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