Thread: Nextval & Currval
Hello
I'm using Nextval and Currval in my ASP programme. But everytime i run the code only the nextval record is inserted and the currval record is lost. I wrote the INSERT INTO in the log and when i tried it via pgadmin both the records were inserted correctly, so there is no syntax error.
I thought the problem would be the connection to the DB. I connect to the DB in the same ASP page where I do the INSERT INTO.
What am i doing wrong?? Any ideas anyone??
Thank you.
Shavonne Wijesinghe
am Tue, dem 10.06.2008, um 11:56:39 +0200 mailte Shavonne Marietta Wijesinghe folgendes: > Hello > > I'm using Nextval and Currval in my ASP programme. But everytime i run the code > only the nextval record is inserted and the currval record is lost. I wrote the > INSERT INTO in the log and when i tried it via pgadmin both the records were > inserted correctly, so there is no syntax error. > I thought the problem would be the connection to the DB. I connect to the DB in > the same ASP page where I do the INSERT INTO. > > What am i doing wrong?? Any ideas anyone?? I don't know your code, but nextval() returns the next sequence number from a sequence and currval() returns the last with nextval() generated sequence-number WITHIN THIS SESSION. In a new session you can't use currval() first, you need to call nextval() and later you can use currval(). Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
This is the test i did for the connection.
' Connecting to the database
WriteToFile logfilepath, date & " " & time & "isobject(conn) = " & isobject(session("connection")) & vbcrlf , True
if not isobject(session("connection")) then
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Form_Store_PSQL"
session("connection") = conn
WriteToFile logfilepath, date & " " & time & "IF " & conn & vbcrlf , True
else
set conn = session("connection")
WriteToFile logfilepath, date & " " & time & "ELSE " & conn & vbcrlf , True
end if
WriteToFile logfilepath, date & " " & time & "isobject(conn) = " & isobject(session("connection")) & vbcrlf , True
if not isobject(session("connection")) then
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Form_Store_PSQL"
session("connection") = conn
WriteToFile logfilepath, date & " " & time & "IF " & conn & vbcrlf , True
else
set conn = session("connection")
WriteToFile logfilepath, date & " " & time & "ELSE " & conn & vbcrlf , True
end if
Thank you.
Shavonne Wijesinghe
Hello Everyone, In a text-heavy database, I'm trying to make an initial design decision in the following context. There is a lot of long text that I could break down into three different categories:a. Laws i. Only 1 country per law,many laws ii. There are multiple types of laws (statutes, regulations, court cases, proposed laws, etc) iii. Eachlaw will have only one type iv. Each law may refer to many other lawsb. Treaties i. 2 countries per treaty ii. At any one time, there will only be one treaty in force between any two countries iii. There may be proposed new treatieswhich will supercede old treaties when finally ratifiedc. Commentary i. Any commentary could refer to one ormore laws or treaties ii. Any commentary may have one or more authors iii. Any commentary may refer to one or morecountries The conceptual question is what are the trade-offs between having one textual table compared with multiple text tables? Anyhelp on pointing out practical considerations would be appreciated. Thanks. Bryan
I managed to make the connection work. I tried inserting records from 2 computers. It works fine untill 2 computers insert a nextwall one after another.
Here is a small example of what i see in my DB
++++++|+++++++++++++++++|++++++++++
N ° | Session | Number
++++++|+++++++++++++++++ |++++++++++
1 | DB last record | 269
2 | PC A (nextval ) | 270
3 | PC A (nextval ) | 271
4 | PC A (currval ) | 271
5 | PC A (nextval ) | 272
6 | PC A (nextval ) | 273
7 | PC B (nextval ) | 274
8 | PC A (currval ) | 274
9 | PC B (nextval ) | 275
10 | PC A (nextval ) | 276
Isn't currval connected to its session?? I expected line N° 8 (PC A) to have the Number of the record it inserted a while ago (line N° 273).
Thank you.
Shavonne Wijesinghe
On Tue, Jun 10, 2008 at 7:14 AM, Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it> wrote: > I managed to make the connection work. I tried inserting records from 2 > computers. It works fine untill 2 computers insert a nextwall one after > another. > > Here is a small example of what i see in my DB > > ++++++|+++++++++++++++++|++++++++++ > N ° | Session | Number > ++++++|+++++++++++++++++ |++++++++++ > 1 | DB last record | 269 > 2 | PC A (nextval ) | 270 > 3 | PC A (nextval ) | 271 > 4 | PC A (currval ) | 271 > 5 | PC A (nextval ) | 272 > 6 | PC A (nextval ) | 273 > 7 | PC B (nextval ) | 274 > 8 | PC A (currval ) | 274 > 9 | PC B (nextval ) | 275 > 10 | PC A (nextval ) | 276 Are you using connection pooling? Could it be that session PC A on the client side is switching connections between 6 and 8? What to you get from pg_backend_pid() in the two instances of time 6 and 8?