Re: Check before INSERT INTO

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

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

Thanks,

Shavonne

----- Original Message -----
From: "Gregory Stark" <stark(at)enterprisedb(dot)com>
To: "Shavonne Marietta Wijesinghe" <shavonne(dot)marietta(at)studioform(dot)it>
Cc: <pgsql-sql(at)postgresql(dot)org>
Sent: Monday, February 11, 2008 5:03 PM
Subject: Re: Check before INSERT INTO

> "Shavonne Marietta Wijesinghe" <shavonne(dot)marietta(at)studioform(dot)it> writes:
>
>> The user updates the DB via ASP. When 2 users click on the submit button
>> at the
>> same time, only 1 record is inserted. (ERROR: duplicate key violates
>> unique
>> constraint "my_shevi_pkey")
>>
>> For example they both send a string like below.
>> strSQL = INSERT INTO my_shevi VALUES ('a', 4, 1, 1);
>>
>> I thought of adding a test before executing the insert into.
>
> It's not clear to me what you're trying to do. If you're trying to update
> an
> existing record then you might want something like example 37-1 on this
> page:
>
> http://www.postgresql.org/docs/8.2/interactive/plpgsql-control-structures.html
>
> If you want to pick the first available n_gen then you're going to have to
> repeatedly try inserting until you don't get that error. That will perform
> quite poorly when you get to large values. You could do a "SELECT
> max(n_gen)
> WHERE..." first but even that will be quite a lot of work for your
> database.
>
> Perhaps you should rethink n_gen and use a serial column to generate your
> primary key instead.
>
>> Set SQLN_GEN = oConn.Execute("SELECT upper(N_GEN), upper(N_SHEET),
>> upper(TOT_N_SHEET) FROM " & TableName & " WHERE N_GEN='" & n_gen & "' AND
>> N_SHEET='" & n_sheet & "' AND TOT_N_SHEET='" & tot_n_sheet & "'")
>
> For what it's worth your script is a security hole. Look into using query
> parameters which in ASP will probably be represented by "?". The method
> above
> will allow hackers to get direct access to your database and do nasty
> things.
>
> --
> Gregory Stark
> EnterpriseDB http://www.enterprisedb.com
> Ask me about EnterpriseDB's RemoteDBA services!