From: Gregory Stark <stark@enterprisedb.com>
To: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
Cc: pgsql-sql@postgresql.org
Subject: Re: Check before INSERT INTO
Date: Mon, 11 Feb 2008 16:03:49 +0000
Message-ID: <873arzscnu.fsf@oxford.xeocode.com> (raw)
In-Reply-To: <010901c86ca6$3b66e870$3102a8c0@dream>
References: <010901c86ca6$3b66e870$3102a8c0@dream>
"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!
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: stark@enterprisedb.com, shavonne.marietta@studioform.it
Subject: Re: Check before INSERT INTO
In-Reply-To: <873arzscnu.fsf@oxford.xeocode.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox