Hello,
This is my first post to the mailing list. I joined because I'm having an
issue working with PostgreSQL in ASP. I hope this is the right place to
discuss this issue. I'm confident of my code and have the same approach
working in MySQL, SQL Server, Oracle and Access which leads me to believe
the problem may be due to a bug in the ODBC driver.
The problem deals with sequence numbers in new records not being returned to
ASP after an update. I ran the test with two tables, the first using a
serial column, the second using an integer column manually attached to a
sequence:
Table One
CREATE TABLE test (pid serial, one int)
Table Two
CREATE TABLE test (pid int, one int)
CREATE SEQUENCE test_seq
CREATE FUNCTION test_fun() RETURNS TRIGGER AS 'BEGIN NEW.PID :=
NEXTVAL(''test_seq''); RETURN NEW; END; 'LANGUAGE PLPGSQL;
CREATE TRIGGER test_trg BEFORE INSERT ON test FOR EACH ROW EXECUTE PROCEDURE
test_fun();
ASP
Dim conn, rs
Set conn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.recordset")
conn.Open "Driver={PostgreSQL};Server=localhost;Database=db;UID=uid;PWD=pwd"
rs.Open "SELECT * FROM test WHERE False", conn, adOpenUnspecified,
adLockPessimistic
rs.AddNew
rs("one") = 1
rs.Update
Response.Write TypeName(rs("pid").Value) & "<br>"
In both cases the output is "Empty". I used TypeName in the Response.Write
simply to illustrate that the value is empty; Response.Write rs("pid").Value
displays nothing in the browser.
I understand that I can use the currval to determine the value but for the
purposes of the project I'm working on I don't want to get into needing to
know the name of the sequence attached to the column. Also, this problem
has bigger implications - while this simple test has only the one stored
procedure modifying the one field, a more complex scenario may alter many
fields upon insert. These changes should be passed back to ASP when the
Update method is called.
