Re: insertRow and updateable resultset
От | Barry Lind |
---|---|
Тема | Re: insertRow and updateable resultset |
Дата | |
Msg-id | 3E28B2A1.7010209@xythos.com обсуждение исходный текст |
Ответ на | Re: insertRow and updateable resultset ("Joel Hock" <joel@enspire.com>) |
Список | pgsql-jdbc |
Joel, Thanks for the additional information. I wanted to point out that not all tables have an OID column, thus any general solution can't rely on the existance of an OID column. So I beleive the oid is only used if the select statement explicitly included the oid in the select list. thanks, --Barry Joel Hock wrote: > Hi, > > There was a typo in the original email; the last line should be: > getString("login_id"); > Imagine a simple schema: > CREATE SEQUENCE login_seq; > CREATE TABLE login ( > login_id INT PRIMARY KEY DEFAULT nextval('login_seq'), > email TEXT NOT NULL > ); > > I realize that the driver doesn't support the getGeneratedKeys()-type > functionality, but I was hoping a refreshRow after an insertRow would > retrieve the generated key. The reason I think it should work is that the > driver stores the oid of the inserted row. From > AbstractJdbc2ResultSet.java's insertRow() line 639: > > long insertedOID = ((AbstractJdbc2Statement) insertStatement).getLastOID(); > updateValues.put("oid", new Long(insertedOID) ); > > Furthermore, AbstractJdbc2ResultSet.java's isUpdateable(), which is called > at the beginning of most functions dealing with updateable resultsets, > should be adding the oid to the 'PrimaryKey' Vector (around line 1319). So, > I thought that would enable the refreshRow() to work, since the oid would > act as a primary key. > > Thanks, > Joel > > -----Original Message----- > From: Barry Lind [mailto:blind@xythos.com] > Sent: Friday, January 17, 2003 2:56 PM > To: Joel Hock > Cc: pgsql-jdbc@postgresql.org > Subject: Re: [JDBC] insertRow and updateable resultset > > Joel, > > To better understand your problem, can you please include your schema > definition? (what is the table structure, the PK columns, and the > column you are trying to access the generated key of). > > But in general, I don't see how this is going to work in postgres, if my > assumptions about your schema are accurate. In general the driver does > not support retrieving generated keys in an automated fashion (and the > DatabaseMetaData object correctly reports that). > > In the case at hand, the query build to refresh the row, uses the > primary key to fetch the data, but you don't have the primary key since > it is autogenerated and the driver doesn't know what the value was. So > it is going to issue the refresh using null for the primary key and thus > not get any results. > > thanks, > --Barry > > > Joel Hock wrote: > >>All of this pertains to PostgreSQL 7.1.3 and the jdbc3 development >>driver that I downloaded today and also the stable driver. >> >> >> >>I am using an updateable ResultSet and insertRow() to insert a row, >>which works fine. >> >>I then do a refreshRow() and try to retrieve the auto-generated primary >>key, which doesn't work. I just get back a null value. I am currently >>using the oid to re-select the row as a workaround, but want a >>database-independent way of getting the key. >> >> >> >>Sample code: >> >> >> >>ResultSet uprs = stmt.executeQuery("SELECT * FROM login WHERE 1=0"); >> >>uprs.moveToInsertRow(); >> >>uprs.updateObject("email", email); >> >>uprs.insertRow(); >> >>uprs.next(); >> >>uprs.refreshRow(); >> >>String loginId = uprs.getString(); // returns null >> >> >> >>Note that the same thing happens if I leave out the 'uprs.next()'. (As >>an aside, this is a bug because the refreshRow() should fail if next() >>is not called; the java docs state that refreshRow() should fail on the >>insert row). >> >> >> >> >> >>Can anyone confirm that the code I'm using should return the generated >>primary key from the database? I've looked at the driver code >>(AbstractJdbc2ResultSet.java) and couldn't see why this wasn't working. >> >> >> >>Thanks, >> >>Joel >> > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
В списке pgsql-jdbc по дате отправления:
Предыдущее
От: Barry LindДата:
Сообщение: Re: Control characters in sql statements close db connecti