From shavonne.marietta@studioform.it Mon Feb  11 12:04:28 2008
Received: from localhost (unknown [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 6BBEA2E009A
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon, 11 Feb 2008 08:04:28 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 21246-01 for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon, 11 Feb 2008 08:04:06 -0400 (AST)
Received: from smtpout26.attiva.biz (smtpout26.attiva.biz [85.37.16.27])
	by postgresql.org (Postfix) with ESMTP id C7F912E008F
	for <pgsql-sql@postgresql.org>; Mon, 11 Feb 2008 08:04:12 -0400 (AST)
Received: from FBCMFE02B15.fbc.local ([192.168.30.161]) by
	smtpout26.attiva.biz with Microsoft SMTPSVC(6.0.3790.1830);
	Mon, 11 Feb 2008 13:04:10 +0100
Received: from dream ([88.34.161.130]) by FBCMFE02B15.fbc.local with Microsoft
	SMTPSVC(6.0.3790.1830); Mon, 11 Feb 2008 13:04:09 +0100
Message-ID: <010901c86ca6$3b66e870$3102a8c0@dream>
Reply-To: "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it>
From: "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it>
To: <pgsql-sql@postgresql.org>
Subject: Check before INSERT INTO
Date: Mon, 11 Feb 2008 13:04:18 +0100
Organization: Studio FORM
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0106_01C86CAE.9C7D3620"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
X-OriginalArrivalTime: 11 Feb 2008 12:04:09.0698 (UTC)
	FILETIME=[35704420:01C86CA6]
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200802/76
X-Sequence-Number: 30167

This is a multi-part message in MIME format.

------=_NextPart_000_0106_01C86CAE.9C7D3620
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

My table structure

CREATE TABLE my_shevi
(
  id text,
  n_gen serial NOT NULL,
  n_sheet serial NOT NULL,
  tot_sheet serial NOT NULL,
  CONSTRAINT my_shevi_pkey PRIMARY KEY (n_gen, n_sheet, tot_sheet)
)=20
WITH OIDS;
ALTER TABLE my_shevi OWNER TO postgres;

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 =3D INSERT INTO my_shevi VALUES ('a', 4, 1, 1);

I thought of adding a test before executing the insert into.=20

Set SQLN_GEN =3D oConn.Execute("SELECT upper(N_GEN), upper(N_SHEET), =
upper(TOT_N_SHEET) FROM " & TableName & " WHERE N_GEN=3D'" & n_gen & "' =
AND N_SHEET=3D'" & n_sheet & "' AND TOT_N_SHEET=3D'" & tot_n_sheet & =
"'")

if (not SQLN_GEN.eof) then
     ***** set n_gen + 1 =20
else
     ***** leave n_gen the way it is
end if

conn.Execute strSQL

But how can i ask it to change the n_gen value??? (The part with the =
*****)=20

Thanks

Shavonne Wijesinghe

------=_NextPart_000_0106_01C86CAE.9C7D3620
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.6000.16587" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>My table structure</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3D"Courier New" size=3D2>CREATE TABLE =
my_shevi<BR>(<BR>&nbsp; id=20
text,<BR>&nbsp; n_gen serial NOT NULL,<BR>&nbsp; n_sheet serial NOT=20
NULL,<BR>&nbsp; tot_sheet serial NOT NULL,<BR>&nbsp; CONSTRAINT =
my_shevi_pkey=20
PRIMARY KEY (n_gen, n_sheet, tot_sheet)<BR>) <BR>WITH OIDS;<BR>ALTER =
TABLE=20
my_shevi OWNER TO postgres;</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>The user updates the DB via ASP. When 2 =
users click=20
on the submit button at the same time, only 1 record is inserted. =
(ERROR:&nbsp;=20
duplicate key violates unique constraint "my_shevi_pkey")</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>For example they both send a string =
like=20
below.</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>strSQL =3D INSERT INTO my_shevi =
VALUES ('a',=20
4, 1, 1);</FONT></DIV>
<DIV>&nbsp;</DIV><FONT face=3DArial size=3D2>
<DIV>I thought of adding a test before executing the&nbsp;insert=20
into.&nbsp;</DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT face=3D"Courier New">Set SQLN_GEN =3D oConn.Execute("SELECT =
upper(N_GEN),=20
upper(N_SHEET), upper(TOT_N_SHEET) FROM " &amp; TableName &amp; " WHERE =
N_GEN=3D'"=20
&amp; n_gen &amp; "' AND N_SHEET=3D'" &amp; n_sheet &amp; "' AND =
TOT_N_SHEET=3D'"=20
&amp; tot_n_sheet &amp; "'")</FONT></DIV>
<DIV><FONT face=3D"Courier New"></FONT>&nbsp;</DIV>
<DIV><FONT face=3D"Courier New">if (not SQLN_GEN.eof)=20
then</FONT><BR>&nbsp;&nbsp;&nbsp;&nbsp; ***** set n_gen + =
1&nbsp;&nbsp;<BR><FONT=20
face=3D"Courier New">else</FONT><BR>&nbsp;&nbsp;&nbsp;&nbsp; ***** leave =
n_gen the=20
way it is<BR><FONT face=3D"Courier New">end if</FONT></DIV>
<DIV><FONT face=3D"Courier New"></FONT>&nbsp;</DIV>
<DIV><FONT face=3D"Courier New">conn.Execute strSQL</FONT></DIV>
<DIV><BR>But how can i ask it to change the n_gen value??? (The part =
with the=20
*****) </DIV>
<DIV>&nbsp;</DIV>
<DIV>Thanks</DIV>
<DIV></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Shavonne =
Wijesinghe<BR></FONT></DIV></BODY></HTML>

------=_NextPart_000_0106_01C86CAE.9C7D3620--




From shavonne.marietta@studioform.it Mon Feb  11 13:14:05 2008
Received: from localhost (unknown [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id C68952E012C
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon, 11 Feb 2008 09:14:05 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 09700-05 for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon, 11 Feb 2008 09:13:54 -0400 (AST)
Received: from smtpout25.fbc.local (smtpout25.attiva.biz [85.37.16.26])
	by postgresql.org (Postfix) with ESMTP id 18EEF2E0129
	for <pgsql-sql@postgresql.org>; Mon, 11 Feb 2008 09:14:00 -0400 (AST)
Received: from FBCMFE02B19.fbc.local ([192.168.31.237]) by smtpout25.fbc.local
	with Microsoft SMTPSVC(6.0.3790.1830);
	Mon, 11 Feb 2008 14:13:56 +0100
Received: from dream ([88.34.161.130]) by FBCMFE02B19.fbc.local with Microsoft
	SMTPSVC(6.0.3790.1830); Mon, 11 Feb 2008 14:13:54 +0100
Message-ID: <014301c86caf$f3f4ea50$3102a8c0@dream>
Reply-To: "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it>
From: "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it>
To: "Kopljan Michael" <kopljan@beotel.net>
Cc: <pgsql-sql@postgresql.org>
References: <000301c86cab$f529e4b0$1100a8c0@discovery>
Subject: Re: Check before INSERT INTO
Date: Mon, 11 Feb 2008 14:13:52 +0100
Organization: Studio FORM
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0140_01C86CB8.5485B450"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
X-OriginalArrivalTime: 11 Feb 2008 13:13:54.0746 (UTC)
	FILETIME=[F3EBE9A0:01C86CAF]
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200802/77
X-Sequence-Number: 30168

This is a multi-part message in MIME format.

------=_NextPart_000_0140_01C86CB8.5485B450
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

ehmm and where is it said that N_GEN should add 1 before inserting the =
record?? sorry i don't understand the help.

Shavonne
  ----- Original Message -----=20
  From: Kopljan Michael=20
  To: 'Shavonne Marietta Wijesinghe'=20
  Sent: Monday, February 11, 2008 1:45 PM
  Subject: RE: [SQL] Check before INSERT INTO


  Try fierst to add Transaction issolation level to Connection object :

  Conn.IsolationLevel   =3D 1048576  && adXactIsolated

  =20

  Then when user submit form try something like this :

  strSQL =3D 'SELECT * FROM my_shevi ORDER BY n_gen DESC, n_sheet DESC, =
tot_sheet DESC LIMIT 1'

  Set SQLN_GEN =3D oConn.Execute(strSQL)=20

  SQLN_GEN.AddNew

  SQLN_GEN('id').Value =3D 'a'

  Conn.BeginTrans()

  SQLN_GEN.Update

  SQLN_GEN.Requery()

  Conn.CommitTrans()

  =20

  Bye Michael

  =20


-------------------------------------------------------------------------=
-----

  From: pgsql-sql-owner@postgresql.org =
[mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Shavonne Marietta =
Wijesinghe
  Sent: Monday, February 11, 2008 1:04 PM
  To: pgsql-sql@postgresql.org
  Subject: [SQL] Check before INSERT INTO

  =20

  My table structure

  =20

  CREATE TABLE my_shevi=20
  (
    id text,
    n_gen serial NOT NULL,
    n_sheet serial NOT NULL,
    tot_sheet serial NOT NULL,
    CONSTRAINT my_shevi_pkey PRIMARY KEY (n_gen, n_sheet, tot_sheet)
  )=20
  WITH OIDS;
  ALTER TABLE my_shevi OWNER TO postgres;

  =20

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

  =20

  For example they both send a string like below.

  strSQL =3D INSERT INTO my_shevi VALUES ('a', 4, 1, 1);

  =20

  I thought of adding a test before executing the insert into.=20

  =20

  Set SQLN_GEN =3D oConn.Execute("SELECT upper(N_GEN), upper(N_SHEET), =
upper(TOT_N_SHEET) FROM " & TableName & " WHERE N_GEN=3D'" & n_gen & "' =
AND N_SHEET=3D'" & n_sheet & "' AND TOT_N_SHEET=3D'" & tot_n_sheet & =
"'")

  =20

  if (not SQLN_GEN.eof) then
       ***** set n_gen + 1 =20
  else
       ***** leave n_gen the way it is
  end if

  =20

  conn.Execute strSQL


  But how can i ask it to change the n_gen value??? (The part with the =
*****)=20

  =20

  Thanks

  =20

  Shavonne Wijesinghe



  __________ Information from ESET Smart Security, version of virus =
signature database 2863 (20080211) __________

  The message was checked by ESET Smart Security.

  http://www.eset.com

------=_NextPart_000_0140_01C86CB8.5485B450
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML xmlns=3D"http://www.w3.org/TR/REC-html40" xmlns:v =3D=20
"urn:schemas-microsoft-com:vml" xmlns:o =3D=20
"urn:schemas-microsoft-com:office:office" xmlns:w =3D=20
"urn:schemas-microsoft-com:office:word"><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.6000.16587" name=3DGENERATOR><!--[if !mso]>
<STYLE>v\:* {
	BEHAVIOR: url(#default#VML)
}
o\:* {
	BEHAVIOR: url(#default#VML)
}
w\:* {
	BEHAVIOR: url(#default#VML)
}
.shape {
	BEHAVIOR: url(#default#VML)
}
</STYLE>
<![endif]-->
<STYLE>
<!--
 /* Font Definitions */
 @font-face
	{font-family:Tahoma;
	panose-1:2 11 6 4 3 5 4 4 2 4;}
 /* Style Definitions */
 p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0mm;
	margin-bottom:.0001pt;
	font-size:12.0pt;
	font-family:"Times New Roman";}
a:link, span.MsoHyperlink
	{color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{color:blue;
	text-decoration:underline;}
span.EmailStyle17
	{mso-style-type:personal-reply;
	font-family:Tahoma;
	color:blue;
	font-weight:normal;
	font-style:normal;
	text-decoration:none none;}
@page Section1
	{size:595.3pt 841.9pt;
	margin:70.85pt 20.0mm 70.85pt 30.0mm;}
div.Section1
	{page:Section1;}
-->
</STYLE>
<!--[if gte mso 9]><xml>
 <o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
 <o:shapelayout v:ext=3D"edit">
  <o:idmap v:ext=3D"edit" data=3D"1" />
 </o:shapelayout></xml><![endif]--></HEAD>
<BODY lang=3DSR vLink=3Dblue link=3Dblue bgColor=3Dwhite>
<DIV><FONT face=3DArial size=3D2>ehmm and where is it said that N_GEN =
should add 1=20
before inserting the record?? sorry i don't understand the =
help.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Shavonne</FONT></DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
  <DIV style=3D"FONT: 10pt arial">----- Original Message ----- </DIV>
  <DIV=20
  style=3D"BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: =
black"><B>From:</B>=20
  <A title=3Dkopljan@beotel.net =
href=3D"mailto:kopljan@beotel.net">Kopljan=20
  Michael</A> </DIV>
  <DIV style=3D"FONT: 10pt arial"><B>To:</B> <A=20
  title=3Dshavonne.marietta@studioform.it=20
  href=3D"mailto:shavonne.marietta@studioform.it">'Shavonne Marietta=20
  Wijesinghe'</A> </DIV>
  <DIV style=3D"FONT: 10pt arial"><B>Sent:</B> Monday, February 11, 2008 =
1:45=20
  PM</DIV>
  <DIV style=3D"FONT: 10pt arial"><B>Subject:</B> RE: [SQL] Check before =
INSERT=20
  INTO</DIV>
  <DIV><BR></DIV>
  <DIV class=3DSection1>
  <P class=3DMsoNormal><FONT face=3DTahoma color=3Dblue size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Tahoma">Try fierst =
to add=20
  Transaction issolation level to Connection object=20
  :<o:p></o:p></SPAN></FONT></P>
  <P class=3DMsoNormal><FONT face=3D"Courier New" color=3Dblack =
size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier =
New'">Conn.IsolationLevel&nbsp;&nbsp;=20
  =3D </SPAN></FONT><FONT face=3D"Courier New" color=3Dred =
size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; COLOR: red; FONT-FAMILY: 'Courier =
New'">1048576&nbsp;=20
  </SPAN></FONT><FONT face=3D"Courier New" color=3Dgreen size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; COLOR: green; FONT-FAMILY: 'Courier =
New'">&amp;&amp;=20
  adXactIsolated<o:p></o:p></SPAN></FONT></P>
  <P class=3DMsoNormal><FONT face=3D"Courier New" color=3Dgreen =
size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; COLOR: green; FONT-FAMILY: 'Courier =
New'"><o:p>&nbsp;</o:p></SPAN></FONT></P>
  <P class=3DMsoNormal><FONT face=3DTahoma color=3Dblue size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Tahoma">Then when =
user=20
  submit form try something like this :<o:p></o:p></SPAN></FONT></P>
  <P class=3DMsoNormal><FONT face=3D"Courier New" size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'">strSQL =3D =
=92SELECT * FROM=20
  my_shevi ORDER BY n_gen DESC, n_sheet DESC, tot_sheet DESC LIMIT=20
  1=92<o:p></o:p></SPAN></FONT></P>
  <P class=3DMsoNormal><FONT face=3D"Courier New" size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'">Set SQLN_GEN =3D =

  oConn.Execute(strSQL) <o:p></o:p></SPAN></FONT></P>
  <P class=3DMsoNormal><FONT face=3D"Courier New" size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; FONT-FAMILY: 'Courier =
New'">SQLN_GEN.AddNew<o:p></o:p></SPAN></FONT></P>
  <P class=3DMsoNormal><FONT face=3D"Courier New" size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; FONT-FAMILY: 'Courier =
New'">SQLN_GEN(=92id=92).Value =3D=20
  =92a=92<o:p></o:p></SPAN></FONT></P>
  <P class=3DMsoNormal><FONT face=3D"Courier New" color=3Dblack =
size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier =
New'">Conn.BeginTrans()</SPAN></FONT><FONT=20
  face=3D"Courier New" size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; FONT-FAMILY: 'Courier =
New'"><o:p></o:p></SPAN></FONT></P>
  <P class=3DMsoNormal><FONT face=3D"Courier New" size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; FONT-FAMILY: 'Courier =
New'">SQLN_GEN.Update<o:p></o:p></SPAN></FONT></P>
  <P class=3DMsoNormal><FONT face=3D"Courier New" size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'">SQLN_GEN.<FONT=20
  color=3Dblue><SPAN style=3D"COLOR: blue">Requery</SPAN></FONT><FONT=20
  color=3Dblack><SPAN=20
  style=3D"COLOR: black">()</SPAN></FONT><o:p></o:p></SPAN></FONT></P>
  <DIV>
  <P class=3DMsoNormal><FONT face=3D"Courier New" color=3Dblack =
size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier =
New'">Conn.CommitTrans()<o:p></o:p></SPAN></FONT></P>
  <P class=3DMsoNormal><FONT face=3D"Courier New" color=3Dblack =
size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier =
New'"><o:p>&nbsp;</o:p></SPAN></FONT></P>
  <P class=3DMsoNormal><FONT face=3D"Courier New" color=3Dblack =
size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier =
New'">Bye=20
  Michael<o:p></o:p></SPAN></FONT></P>
  <P class=3DMsoNormal><FONT face=3D"Courier New" color=3Dblack =
size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier =
New'"><o:p>&nbsp;</o:p></SPAN></FONT></P>
  <DIV class=3DMsoNormal style=3D"TEXT-ALIGN: center" =
align=3Dcenter><FONT=20
  face=3D"Times New Roman" size=3D3><SPAN lang=3DEN-US =
style=3D"FONT-SIZE: 12pt">
  <HR tabIndex=3D-1 align=3Dcenter width=3D"100%" SIZE=3D2>
  </SPAN></FONT></DIV>
  <P class=3DMsoNormal><B><FONT face=3DTahoma size=3D2><SPAN =
lang=3DEN-US=20
  style=3D"FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: =
Tahoma">From:</SPAN></FONT></B><FONT=20
  face=3DTahoma size=3D2><SPAN lang=3DEN-US=20
  style=3D"FONT-SIZE: 10pt; FONT-FAMILY: Tahoma"> =
pgsql-sql-owner@postgresql.org=20
  [mailto:pgsql-sql-owner@postgresql.org] <B><SPAN style=3D"FONT-WEIGHT: =
bold">On=20
  Behalf Of </SPAN></B>Shavonne Marietta Wijesinghe<BR><B><SPAN=20
  style=3D"FONT-WEIGHT: bold">Sent:</SPAN></B> Monday, February 11, 2008 =
1:04=20
  PM<BR><B><SPAN style=3D"FONT-WEIGHT: bold">To:</SPAN></B>=20
  pgsql-sql@postgresql.org<BR><B><SPAN=20
  style=3D"FONT-WEIGHT: bold">Subject:</SPAN></B> [SQL] Check before =
INSERT=20
  INTO</SPAN></FONT><SPAN lang=3DEN-US><o:p></o:p></SPAN></P></DIV>
  <P class=3DMsoNormal><FONT face=3D"Times New Roman" size=3D3><SPAN=20
  style=3D"FONT-SIZE: 12pt"><o:p>&nbsp;</o:p></SPAN></FONT></P>
  <DIV>
  <P class=3DMsoNormal><FONT face=3DArial size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; FONT-FAMILY: Arial">My table=20
  structure</SPAN></FONT><o:p></o:p></P></DIV>
  <DIV>
  <P class=3DMsoNormal><FONT face=3D"Times New Roman" size=3D3><SPAN=20
  style=3D"FONT-SIZE: 12pt">&nbsp;<o:p></o:p></SPAN></FONT></P></DIV>
  <DIV>
  <P class=3DMsoNormal><FONT face=3D"Courier New" size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'">CREATE TABLE =
my_shevi<FONT=20
  color=3Dblue><SPAN style=3D"COLOR: blue"> =
</SPAN></FONT><BR>(<BR>&nbsp; id=20
  text,<BR>&nbsp; n_gen serial NOT NULL,<BR>&nbsp; n_sheet serial NOT=20
  NULL,<BR>&nbsp; tot_sheet serial NOT NULL,<BR>&nbsp; CONSTRAINT =
my_shevi_pkey=20
  PRIMARY KEY (n_gen, n_sheet, tot_sheet)<BR>) <BR>WITH OIDS;<BR>ALTER =
TABLE=20
  my_shevi OWNER TO postgres;</SPAN></FONT><o:p></o:p></P></DIV>
  <DIV>
  <P class=3DMsoNormal><FONT face=3D"Times New Roman" size=3D3><SPAN=20
  style=3D"FONT-SIZE: 12pt">&nbsp;<o:p></o:p></SPAN></FONT></P></DIV>
  <DIV>
  <P class=3DMsoNormal><FONT face=3DArial size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; FONT-FAMILY: Arial">The user updates the DB =
via ASP.=20
  When 2 users click on the submit button at the same time, only 1 =
record is=20
  inserted. (ERROR:&nbsp; duplicate key violates unique constraint=20
  "my_shevi_pkey")</SPAN></FONT><o:p></o:p></P></DIV>
  <DIV>
  <P class=3DMsoNormal><FONT face=3D"Times New Roman" size=3D3><SPAN=20
  style=3D"FONT-SIZE: 12pt">&nbsp;<o:p></o:p></SPAN></FONT></P></DIV>
  <DIV>
  <P class=3DMsoNormal><FONT face=3DArial size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; FONT-FAMILY: Arial">For example they both =
send a=20
  string like below.</SPAN></FONT><o:p></o:p></P></DIV>
  <DIV>
  <P class=3DMsoNormal><FONT face=3D"Courier New" size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'">strSQL =3D =
INSERT INTO=20
  my_shevi VALUES ('a', 4, 1, 1);</SPAN></FONT><o:p></o:p></P></DIV>
  <DIV>
  <P class=3DMsoNormal><FONT face=3D"Times New Roman" size=3D3><SPAN=20
  style=3D"FONT-SIZE: 12pt">&nbsp;<o:p></o:p></SPAN></FONT></P></DIV>
  <DIV>
  <P class=3DMsoNormal><FONT face=3DArial size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; FONT-FAMILY: Arial">I thought of adding a =
test before=20
  executing the&nbsp;insert =
into.&nbsp;<o:p></o:p></SPAN></FONT></P></DIV>
  <DIV>
  <P class=3DMsoNormal><FONT face=3DArial size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; FONT-FAMILY: =
Arial">&nbsp;<o:p></o:p></SPAN></FONT></P></DIV>
  <DIV>
  <P class=3DMsoNormal><FONT face=3D"Courier New" size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'">Set SQLN_GEN =3D =

  oConn.Execute("SELECT upper(N_GEN), upper(N_SHEET), upper(TOT_N_SHEET) =
FROM "=20
  &amp; TableName &amp; " WHERE N_GEN=3D'" &amp; n_gen &amp; "' AND =
N_SHEET=3D'"=20
  &amp; n_sheet &amp; "' AND TOT_N_SHEET=3D'" &amp; tot_n_sheet &amp;=20
  "'")</SPAN></FONT><FONT face=3DArial size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; FONT-FAMILY: =
Arial"><o:p></o:p></SPAN></FONT></P></DIV>
  <DIV>
  <P class=3DMsoNormal><FONT face=3DArial size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; FONT-FAMILY: =
Arial">&nbsp;<o:p></o:p></SPAN></FONT></P></DIV>
  <DIV>
  <P class=3DMsoNormal><FONT face=3D"Courier New" size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'">if (not =
SQLN_GEN.eof)=20
  then</SPAN></FONT><FONT face=3DArial size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; FONT-FAMILY: =
Arial"><BR>&nbsp;&nbsp;&nbsp;&nbsp; *****=20
  set n_gen + 1&nbsp;&nbsp;<BR></SPAN></FONT><FONT face=3D"Courier New"=20
  size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; FONT-FAMILY: 'Courier =
New'">else</SPAN></FONT><FONT=20
  face=3DArial size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; FONT-FAMILY: =
Arial"><BR>&nbsp;&nbsp;&nbsp;&nbsp; *****=20
  leave n_gen the way it is<BR></SPAN></FONT><FONT face=3D"Courier New"=20
  size=3D2><SPAN style=3D"FONT-SIZE: 10pt; FONT-FAMILY: 'Courier =
New'">end=20
  if</SPAN></FONT><FONT face=3DArial size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; FONT-FAMILY: =
Arial"><o:p></o:p></SPAN></FONT></P></DIV>
  <DIV>
  <P class=3DMsoNormal><FONT face=3DArial size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; FONT-FAMILY: =
Arial">&nbsp;<o:p></o:p></SPAN></FONT></P></DIV>
  <DIV>
  <P class=3DMsoNormal><FONT face=3D"Courier New" size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'">conn.Execute=20
  strSQL</SPAN></FONT><FONT face=3DArial size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; FONT-FAMILY: =
Arial"><o:p></o:p></SPAN></FONT></P></DIV>
  <DIV>
  <P class=3DMsoNormal><FONT face=3DArial size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; FONT-FAMILY: Arial"><BR>But how can i ask it =
to change=20
  the n_gen value??? (The part with the *****)=20
  <o:p></o:p></SPAN></FONT></P></DIV>
  <DIV>
  <P class=3DMsoNormal><FONT face=3DArial size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; FONT-FAMILY: =
Arial">&nbsp;<o:p></o:p></SPAN></FONT></P></DIV>
  <DIV>
  <P class=3DMsoNormal><FONT face=3DArial size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; FONT-FAMILY: =
Arial">Thanks<o:p></o:p></SPAN></FONT></P></DIV>
  <DIV>
  <P class=3DMsoNormal><FONT face=3D"Times New Roman" size=3D3><SPAN=20
  style=3D"FONT-SIZE: 12pt">&nbsp;<o:p></o:p></SPAN></FONT></P></DIV>
  <DIV>
  <P class=3DMsoNormal><FONT face=3DArial size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; FONT-FAMILY: Arial">Shavonne=20
  Wijesinghe</SPAN></FONT><o:p></o:p></P></DIV>
  <P class=3DMsoNormal><FONT face=3D"Times New Roman" size=3D3><SPAN=20
  style=3D"FONT-SIZE: 12pt"><BR><BR>__________ Information from ESET =
Smart=20
  Security, version of virus signature database 2863 (20080211)=20
  __________<BR><BR>The message was checked by ESET Smart =
Security.<BR><BR><A=20
  =
href=3D"http://www.eset.com">http://www.eset.com</A><o:p></o:p></SPAN></F=
ONT></P></DIV></BLOCKQUOTE></BODY></HTML>

------=_NextPart_000_0140_01C86CB8.5485B450--




From stark@enterprisedb.com Mon Feb  11 16:04:16 2008
Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id B59272E02FD
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon, 11 Feb 2008 12:04:16 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 38181-01-2 for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon, 11 Feb 2008 12:04:10 -0400 (AST)
Received: from oxford.xeocode.com (unknown [62.232.55.118])
	by postgresql.org (Postfix) with ESMTP id A0F7D2E2D71
	for <pgsql-sql@postgresql.org>; Mon, 11 Feb 2008 12:03:54 -0400 (AST)
Received: from localhost ([127.0.0.1] helo=oxford.xeocode.com)
	by oxford.xeocode.com with esmtp (Exim 4.69)
	(envelope-from <stark@enterprisedb.com>)
	id 1JOb8H-0001vZ-Fx; Mon, 11 Feb 2008 16:03:49 +0000
To: "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: Check before INSERT INTO
In-Reply-To: <010901c86ca6$3b66e870$3102a8c0@dream> (Shavonne Marietta
	Wijesinghe's message of "Mon\, 11 Feb 2008 13\:04\:18 +0100")
User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.1 (gnu/linux)
X-Draft-From: ("nnimap+mail01.enterprisedb.com:INBOX.sql" 1893)
References: <010901c86ca6$3b66e870$3102a8c0@dream>
From: Gregory Stark <stark@enterprisedb.com>
Organization: EnterpriseDB
Date: Mon, 11 Feb 2008 16:03:49 +0000
Message-ID: <873arzscnu.fsf@oxford.xeocode.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200802/78
X-Sequence-Number: 30169

"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!



From shavonne.marietta@studioform.it Mon Feb  11 16:23:14 2008
Received: from localhost (unknown [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 613CA2E0093
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon, 11 Feb 2008 12:23:14 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 36434-02 for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon, 11 Feb 2008 12:22:58 -0400 (AST)
Received: from smtpout24.attiva.biz (smtpout24.attiva.biz [85.37.16.25])
	by postgresql.org (Postfix) with ESMTP id EABEF2E008E
	for <pgsql-sql@postgresql.org>; Mon, 11 Feb 2008 12:22:57 -0400 (AST)
Received: from FBCMFE02B07.fbc.local ([192.168.30.95]) by smtpout24.attiva.biz
	with Microsoft SMTPSVC(6.0.3790.1830);
	Mon, 11 Feb 2008 17:22:56 +0100
Received: from dream ([88.34.161.130]) by FBCMFE02B07.fbc.local with Microsoft
	SMTPSVC(6.0.3790.1830); Mon, 11 Feb 2008 17:22:51 +0100
Message-ID: <024301c86cca$591c9c60$3102a8c0@dream>
Reply-To: "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it>
From: "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it>
To: "Gregory Stark" <stark@enterprisedb.com>
Cc: <pgsql-sql@postgresql.org>
References: <010901c86ca6$3b66e870$3102a8c0@dream>
	<873arzscnu.fsf@oxford.xeocode.com>
Subject: Re: Check before INSERT INTO
Date: Mon, 11 Feb 2008 17:22:50 +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
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
X-OriginalArrivalTime: 11 Feb 2008 16:22:51.0596 (UTC)
	FILETIME=[5935F0C0:01C86CCA]
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200802/79
X-Sequence-Number: 30170

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! 




From jeandavid8@verizon.net Mon Feb  11 17:00:55 2008
Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id E7CA22E00A8
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon, 11 Feb 2008 13:00:55 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 63176-04 for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon, 11 Feb 2008 13:00:53 -0400 (AST)
Received: from vms173001pub.verizon.net (vms173001pub.verizon.net
	[206.46.173.1]) by postgresql.org (Postfix) with ESMTP id 66F522E038B
	for <pgsql-sql@postgresql.org>; Mon, 11 Feb 2008 13:00:48 -0400 (AST)
Received: from [192.168.0.251] ([72.76.251.23]) by vms173001.mailsrvcs.net
	(Sun Java System Messaging Server 6.2-6.01 (built Apr  3 2006))
	with ESMTPA id <0JW3002E92GHJJQA@vms173001.mailsrvcs.net> for
	pgsql-sql@postgresql.org; Mon, 11 Feb 2008 10:43:30 -0600 (CST)
Date: Mon, 11 Feb 2008 11:55:22 -0500
From: Jean-David Beyer <jeandavid8@verizon.net>
Subject: Re: Check before INSERT INTO
In-reply-to: <024301c86cca$591c9c60$3102a8c0@dream>
To: pgsql-sql@postgresql.org
Message-id: <47B07DFA.8030003@verizon.net>
Organization: Institute for Regimented Whimsey
MIME-version: 1.0
Content-type: text/plain; charset=ISO-8859-1
Content-transfer-encoding: 7bit
X-Enigmail-Version: 0.94.0.0
References: <010901c86ca6$3b66e870$3102a8c0@dream>
	<873arzscnu.fsf@oxford.xeocode.com>
	<024301c86cca$591c9c60$3102a8c0@dream>
User-Agent: Thunderbird 1.5.0.9 (X11/20070317)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200802/82
X-Sequence-Number: 30173

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



From stark@enterprisedb.com Mon Feb  11 16:56:15 2008
Received: from localhost (unknown [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 820462E00B7
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon, 11 Feb 2008 12:56:15 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 53867-04 for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon, 11 Feb 2008 12:56:06 -0400 (AST)
Received: from oxford.xeocode.com (unknown [62.232.55.118])
	by postgresql.org (Postfix) with ESMTP id B74892E0031
	for <pgsql-sql@postgresql.org>; Mon, 11 Feb 2008 12:56:06 -0400 (AST)
Received: from localhost ([127.0.0.1] helo=oxford.xeocode.com)
	by oxford.xeocode.com with esmtp (Exim 4.69)
	(envelope-from <stark@enterprisedb.com>)
	id 1JObwp-0001zg-SI; Mon, 11 Feb 2008 16:56:03 +0000
To: "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: Check before INSERT INTO
In-Reply-To: <024301c86cca$591c9c60$3102a8c0@dream> (Shavonne Marietta
	Wijesinghe's message of "Mon\, 11 Feb 2008 17\:22\:50 +0100")
User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.1 (gnu/linux)
X-Draft-From: ("nnimap+mail01.enterprisedb.com:INBOX" 63685)
References: <010901c86ca6$3b66e870$3102a8c0@dream>
	<873arzscnu.fsf@oxford.xeocode.com>
	<024301c86cca$591c9c60$3102a8c0@dream>
From: Gregory Stark <stark@enterprisedb.com>
Organization: EnterpriseDB
Date: Mon, 11 Feb 2008 16:56:03 +0000
Message-ID: <87ve4vqvoc.fsf@oxford.xeocode.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200802/80
X-Sequence-Number: 30171

"Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it> writes:

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

You'll have to explain what you want n_gen to contain.

Then you'll have to decide whether you want to do this in ASP where you can
certainly change the SQL all you like, or on the server where you can have
triggers which change the values being stored or executing additional queries.

-- 
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's PostGIS support!



From shavonne.marietta@studioform.it Tue Feb  12 10:21:57 2008
Received: from localhost (unknown [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id F30B82E2D94
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 12 Feb 2008 06:21:57 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 03537-02-3 for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 12 Feb 2008 06:21:38 -0400 (AST)
Received: from smtpout09.attiva.biz (smtpout09.attiva.biz [85.37.16.7])
	by postgresql.org (Postfix) with ESMTP id 5318D2E3019
	for <pgsql-sql@postgresql.org>; Tue, 12 Feb 2008 06:00:22 -0400 (AST)
Received: from fbcmfe02b10.fbc.local ([192.168.30.91]) by smtpout09.attiva.biz
	with Microsoft SMTPSVC(6.0.3790.1830);
	Tue, 12 Feb 2008 11:00:13 +0100
Received: from dream ([88.34.161.130]) by fbcmfe02b10.fbc.local with Microsoft
	SMTPSVC(6.0.3790.1830); Tue, 12 Feb 2008 11:00:12 +0100
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
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
X-OriginalArrivalTime: 12 Feb 2008 10:00:12.0647 (UTC)
	FILETIME=[0F061770:01C86D5E]
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200802/91
X-Sequence-Number: 30182

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 




From dev@archonet.com Tue Feb  12 10:35:53 2008
Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 4198C2E002F
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 12 Feb 2008 06:35:53 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 16634-02 for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 12 Feb 2008 06:35:49 -0400 (AST)
Received: from pih-relay06.plus.net (pih-relay06.plus.net [212.159.14.133])
	by postgresql.org (Postfix) with ESMTP id C82212E010C
	for <pgsql-sql@postgresql.org>; Tue, 12 Feb 2008 06:35:48 -0400 (AST)
Received: from [84.51.143.99] (helo=server3.office.archonet.com)
	by pih-relay06.plus.net with esmtp (Exim) id 1JOsUJ-0006j7-Vd;
	Tue, 12 Feb 2008 10:35:44 +0000
Received: from [192.168.1.36] (dell36.office.archonet.com [192.168.1.36])
	by server3.office.archonet.com (Postfix) with ESMTP id B817E274059;
	Tue, 12 Feb 2008 10:35:43 +0000 (GMT)
Message-ID: <47B1767F.3030802@archonet.com>
Date: Tue, 12 Feb 2008 10:35:43 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.9 (X11/20071031)
MIME-Version: 1.0
To: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
CC: Jean-David Beyer <jeandavid8@verizon.net>, pgsql-sql@postgresql.org
Subject: Re: Check before INSERT INTO
References: <010901c86ca6$3b66e870$3102a8c0@dream>
	<873arzscnu.fsf@oxford.xeocode.com>
	<024301c86cca$591c9c60$3102a8c0@dream>
	<47B07DFA.8030003@verizon.net>
	<00ae01c86d5e$0f2b2ba0$3102a8c0@dream>
In-Reply-To: <00ae01c86d5e$0f2b2ba0$3102a8c0@dream>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Plusnet-Relay: cd937d4fa35052ea6aca8db00bbe7636
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200802/92
X-Sequence-Number: 30183

Shavonne Marietta Wijesinghe wrote:
> 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)

Of course it is.

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

Why sadly? What do you think should happen?

> Have i set the isolation level correctly??

I think you are having problems with thinking through the concurrency of 
this problem.

Scenario 1 - will work
==========
User1: Read value 5
User1: new value = 5 + 1
User1: Commit changes
User2: Read value 6
User2: new value = 6 + 1
User2: Commit changes

Scenario 2 - will not work
==========
User1: Read value 5
User1: new value = 5 + 1
User2: Read value 5 (there is no "6" yet, it's not been committed)
User2: new value = 5 + 1
User1: Commit changes
User2: Commit changes - ERROR

There are only two alternatives in scenario #2 - block user 2 from 
reading a value until user1 commits/rolls back or give them a value that 
might be out of date. That's what the isolation level controls.

 From your original email you have n_gen defined as a serial. That's 
basically an integer column with a default value from a 
sequence-generator. I'd just let the default value be accepted when you 
want a new number, that guarantees you a different value each time 
(although you can't guarantee you'll get 1,2,3,4,5...)

-- 
   Richard Huxton
   Archonet Ltd



From shavonne.marietta@studioform.it Tue Feb  12 11:06:26 2008
Received: from localhost (unknown [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 8F27E2E0086
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 12 Feb 2008 07:06:26 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 10888-09 for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 12 Feb 2008 07:06:04 -0400 (AST)
Received: from smtpout24.attiva.biz (smtpout24.attiva.biz [85.37.16.25])
	by postgresql.org (Postfix) with ESMTP id 94DE92E0256
	for <pgsql-sql@postgresql.org>; Tue, 12 Feb 2008 07:06:18 -0400 (AST)
Received: from FBCMFE02B12.fbc.local ([192.168.30.89]) by smtpout24.attiva.biz
	with Microsoft SMTPSVC(6.0.3790.1830);
	Tue, 12 Feb 2008 12:06:13 +0100
Received: from dream ([88.34.161.130]) by FBCMFE02B12.fbc.local with Microsoft
	SMTPSVC(6.0.3790.1830); Tue, 12 Feb 2008 12:06:12 +0100
Message-ID: <00fa01c86d67$47e04bc0$3102a8c0@dream>
Reply-To: "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it>
From: "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it>
To: "Richard Huxton" <dev@archonet.com>
Cc: "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>
	<00ae01c86d5e$0f2b2ba0$3102a8c0@dream>
	<47B1767F.3030802@archonet.com>
Subject: Re: Check before INSERT INTO
Date: Tue, 12 Feb 2008 12:06:12 +0100
Organization: Studio FORM
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
	reply-type=response
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
X-OriginalArrivalTime: 12 Feb 2008 11:06:13.0127 (UTC)
	FILETIME=[47A76170:01C86D67]
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200802/93
X-Sequence-Number: 30184

Even though n_gen is defined as a serial I can't let it handle the 
progressive key by its self since there is the need that some records should 
have the same value. That's why i use 3 primary keys.

 A |  B |  C
---+---+---
 1  |  1  |  1
 2  |  1  |  3
 2  |  2  |  3
 2  |  3  |  3
 3  |  1  |  2
 3  |  2  |  2
 4  |  1  |  1

The 3 keys A, B, C are defined as Serial and Primay Keys

Anyway the other suggestion, Blocking the second user from reading the db. 
So for the second user I could give A temp key something like 0 and then do 
a select before the submit and change the value.

But how is it possible to know if some other user is reading the db??

Thanks

----- Original Message ----- 
From: "Richard Huxton" <dev@archonet.com>
To: "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it>
Cc: "Jean-David Beyer" <jeandavid8@verizon.net>; <pgsql-sql@postgresql.org>
Sent: Tuesday, February 12, 2008 11:35 AM
Subject: Re: [SQL] Check before INSERT INTO


> Shavonne Marietta Wijesinghe wrote:
>> 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)
>
> Of course it is.
>
>> 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".
>
> Why sadly? What do you think should happen?
>
>> Have i set the isolation level correctly??
>
> I think you are having problems with thinking through the concurrency of 
> this problem.
>
> Scenario 1 - will work
> ==========
> User1: Read value 5
> User1: new value = 5 + 1
> User1: Commit changes
> User2: Read value 6
> User2: new value = 6 + 1
> User2: Commit changes
>
> Scenario 2 - will not work
> ==========
> User1: Read value 5
> User1: new value = 5 + 1
> User2: Read value 5 (there is no "6" yet, it's not been committed)
> User2: new value = 5 + 1
> User1: Commit changes
> User2: Commit changes - ERROR
>
> There are only two alternatives in scenario #2 - block user 2 from reading 
> a value until user1 commits/rolls back or give them a value that might be 
> out of date. That's what the isolation level controls.
>
> From your original email you have n_gen defined as a serial. That's 
> basically an integer column with a default value from a 
> sequence-generator. I'd just let the default value be accepted when you 
> want a new number, that guarantees you a different value each time 
> (although you can't guarantee you'll get 1,2,3,4,5...)
>
> -- 
>   Richard Huxton
>   Archonet Ltd 




From dev@archonet.com Tue Feb  12 12:01:20 2008
Received: from localhost (unknown [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 410902E026C
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 12 Feb 2008 08:01:20 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 51750-08 for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 12 Feb 2008 08:00:54 -0400 (AST)
Received: from ptb-relay03.plus.net (ptb-relay03.plus.net [212.159.14.214])
	by postgresql.org (Postfix) with ESMTP id 2AB812E026A
	for <pgsql-sql@postgresql.org>; Tue, 12 Feb 2008 08:01:09 -0400 (AST)
Received: from [84.51.143.99] (helo=server3.office.archonet.com)
	by ptb-relay03.plus.net with esmtp (Exim) id 1JOtov-0000pi-IN;
	Tue, 12 Feb 2008 12:01:05 +0000
Received: from [192.168.1.36] (dell36.office.archonet.com [192.168.1.36])
	by server3.office.archonet.com (Postfix) with ESMTP id DD684274059;
	Tue, 12 Feb 2008 12:01:03 +0000 (GMT)
Message-ID: <47B18A80.4030802@archonet.com>
Date: Tue, 12 Feb 2008 12:01:04 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.9 (X11/20071031)
MIME-Version: 1.0
To: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
CC: Jean-David Beyer <jeandavid8@verizon.net>, pgsql-sql@postgresql.org
Subject: Re: Check before INSERT INTO
References: <010901c86ca6$3b66e870$3102a8c0@dream>
	<873arzscnu.fsf@oxford.xeocode.com>
	<024301c86cca$591c9c60$3102a8c0@dream>
	<47B07DFA.8030003@verizon.net>
	<00ae01c86d5e$0f2b2ba0$3102a8c0@dream>
	<47B1767F.3030802@archonet.com>
	<00fa01c86d67$47e04bc0$3102a8c0@dream>
In-Reply-To: <00fa01c86d67$47e04bc0$3102a8c0@dream>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Plusnet-Relay: 667dc96511c9e06ae11a4c17d1a3c551
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200802/94
X-Sequence-Number: 30185

Shavonne Marietta Wijesinghe wrote:
> Even though n_gen is defined as a serial I can't let it handle the 
> progressive key by its self since there is the need that some records 
> should have the same value. 

Of course you can - the default is only provided if you don't provide 
your own.


> That's why i use 3 primary keys.

No, you're not. By definition you can only have one primary key. You are 
using a 3-column primary key.

> A |  B |  C
> ---+---+---
> 1  |  1  |  1
> 2  |  1  |  3
> 2  |  2  |  3
> 2  |  3  |  3
> 3  |  1  |  2
> 3  |  2  |  2
> 4  |  1  |  1
> 
> The 3 keys A, B, C are defined as Serial and Primay Keys

You don't have 3 keys, you have 3 columns and one primary key (A,B,C).

I'm not clear why B and C are serial - I don't see what that gets you.

> Anyway the other suggestion, Blocking the second user from reading the 
> db. So for the second user I could give A temp key something like 0 and 
> then do a select before the submit and change the value.
> 
> But how is it possible to know if some other user is reading the db??

Don't try. If you want to insert some rows with a new value for A do 
something like:

INSERT INTO my_shevi (a,b,c) VALUES (DEFAULT, 1, 1);
or
INSERT INTO my_shevi (a,b,c) VALUES (nextval(<sequence-name-here>, 1, 1);

INSERT INTO my_shevi (a,b,c) VALUES (currval(<sequence-name-here>), 1, 2);
INSERT INTO my_shevi (a,b,c) VALUES (currval(<sequence-name-here>), 1, 3);
etc.

Relevant parts of the manual (in 8.3 anyway):
   9.15. Sequence Manipulation Functions
   9.22. System Information Functions
The second chapter is for pg_get_serial_sequence() which might be useful 
if you need to do this a lot.

Oh - and please try trimming unwanted parts of the message when you 
reply. There was 100 unnecessary lines below here.

-- 
   Richard Huxton
   Archonet Ltd