Re: Check before INSERT INTO - Mailing list pgsql-sql

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@enterprisedb.com>
To: "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it>
Cc: <pgsql-sql@postgresql.org>
Sent: Monday, February 11, 2008 5:03 PM
Subject: Re: Check before INSERT INTO


> "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.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!