Re: [SQL] Check before INSERT INTO

Shavonne Marietta Wijesinghe Tue, 12 Feb 2008 02:23:42 -0800

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

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


If err <> 0 then 'If table not found
GetFieldValue = "1"

WriteToFile logfilepath, date & " " & time & " -- no table Numeroprogressivo: 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 sametime 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 logsin 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" <[EMAIL PROTECTED]>

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 tablespecifyingthe id and expecting the three serial generators to pick non-nullsequentialnumbers for the other three fields? I think you are very unclear aboutwhat

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 youhave

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



---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

              http://www.postgresql.org/docs/faq

Re: [SQL] Check before INSERT INTO

Reply via email to