Re: [GENERAL] Resultset problem or BUG !

Any Help !

Elie Nacache <elie_nacache(at)yahoo(dot)com> wrote:Date: Wed, 11 Aug 2004 05:59:35 -0700 (PDT)
From: Elie Nacache
Subject: Re: [GENERAL] Resultset problem or BUG !
To: Richard Huxton
CC: pgsql-general(at)postgresql(dot)org

Hi Richard,

Some informations:
==============
OS: RedHat
DB: PostgreSQL 7.4.3
Driver: PostgreSQL 7.4.2 JDBC3 with SSL (build 213)

Here some code:
============
INFO >> sqlDatas is of type ResultSet
INFO >> Step first insert new row

this.sqlDatas.moveToInsertRow();
this.updateColumns();
this.sqlDatas.insertRow();
this.sqlLineNumber = this.sqlDatas.getRow(); // INFO >> return 0

INFO >> At this step there is no commit
INFO >> Second step update the row after collecting updated info

this.sqlDatas.absolute(this.sqlLineNumber); // INFO >> throw the exception
this.updateColumns();
this.sqlDatas.updateRow();

INFO >> If I fix the sqlLineNumber to 1 and force the absolute(1) then no Execption is thrown but the modification is not seen in the table.

Elie Nacache
RentaSoft Ltd.

Richard Huxton <dev(at)archonet(dot)com> wrote:
Elie Nacache wrote:
> Hi All,

Hi Elie, I'm afraid we'll need more details before we can help.

> A data writed in a table but not commited is not accessible in
> modification by the resultset. The error returned is "Cannot move to
> index of 0".

Is this an ODBC or JDBC error? There are specific lists for both of those.

> BTW in SQL a sequence of INSERT, UPDATE and then COMMIT work fine.

> I'm using the same code with oracle and it's working well.

What code?

> Is it a BUG !?

Can't say.

> I'm using psql 7.4.3 on Linux 9.0

psql is the command-line tool, so I'm guessing you mean PostgreSQL (or
pgsql) 7.4.3. Likewise, there is no Linux 9.0 - I'm assuming you mean
RedHat, SuSE or Mandrake.

If you can provide details on how you are accessing the database and a
sho rt example of your code, I'm sure someone will be able to help.
--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org

---------------------------------
Do you Yahoo!?
Read only the mail you want - Yahoo! Mail SpamGuard.

---------------------------------
Do you Yahoo!?
Yahoo! Mail - 50x more storage than other providers!

On Wed, 11 Aug 2004, Elie Nacache wrote:

> INFO >> sqlDatas is of type ResultSet
> INFO >> Step first insert new row
>
> this.sqlDatas.moveToInsertRow();
> this.updateColumns();
> this.sqlDatas.insertRow();
> this.sqlLineNumber = this.sqlDatas.getRow(); // INFO >> return 0
>

The problem here is that you are still on the insert row, you are not
positioned anywhere in your ResultSet so getRow returns zero. You seem to
be expecting getRow to return the newly inserted row's position which I
admit seems handly although it doesn't look to be what the spec wants.

Kris Jurka

Elie Nacache wrote:

> this.sqlDatas.moveToInsertRow();
> this.updateColumns();
> this.sqlDatas.insertRow();

The JDBC javadoc implies that calling insertRow() leaves the resultset
on the insert row. This is what the driver implements.

> this.sqlLineNumber = this.sqlDatas.getRow(); // INFO >> return 0

As the javadoc says, getRow() returns 0 if there is no current row.
You're on the insert row so there is no current row.

> INFO >> At this step there is no commit
> INFO >> Second step update the row after collecting updated info
>
> this.sqlDatas.absolute(this.sqlLineNumber); // INFO >> throw the
> exception

What is the exception you see?

The current development driver (see jdbc.postgresql.org) will treat
absolute(0) as if you'd called beforeFirst(). Older drivers might do
something different.

> this.updateColumns();
> this.sqlDatas.updateRow();
> INFO >> If I fix the sqlLineNumber to 1 and force the absolute(1)
> then no Execption is thrown but the modification is not seen in the
> table.

Looking at the driver source, it looks like the newly inserted row is
appended to the resultset, so you should find it at the end of the
resultset not the start. (I don't think making this assumption is
portable, though).

-O

Hi Oliver,

> What is the exception you see?

The exception returned is "Cannot move to index of 0".

> The current development driver (see jdbc.postgresql.org) will treat
> absolute(0) as if you'd called beforeFirst(). Older drivers might do
> something different.

This is exactly the solution. What is the due date of the current development driver ?

Elie

Oliver Jowett <oliver(at)opencloud(dot)com> wrote:
Elie Nacache wrote:

> this.sqlDatas.moveToInsertRow();
> this.updateColumns();
> this.sqlDatas.insertRow();

The JDBC javadoc implies that calling insertRow() leaves the resultset
on the insert row. This is what the driver implements.

> this.sqlLineNumber = this.sqlDatas.getRow(); // INFO >> return 0

As the javadoc says, getRow() returns 0 if there is no current row.
You're on the insert row so there is no current row.

> INFO >> At this step there is no commit
> INFO >> Second step update the row after collecting updated info
>
> this.sqlDatas.absolute(this.sqlLineNumber); // INFO >> throw the
> exception

What is the exception you see?

The current development driver (see jdbc.postgresql.org) will treat
absolute(0) as if you'd called beforeFirst(). Older drivers might do
something different.

> this.updateColumns();
> this.sqlDatas.updateRow();
> INFO >> If I fix the sqlLineNumber to 1 and force the absolute(1)
> then no Execption is thrown but the modification is not seen in the
> table.

Looking at the driver source, it looks like the newly inserted row is
appended to the resultset, so you should find it at the end of the
resultset not the start. (I don't think making this assumption is
portable, though).

-O

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

On Sun, 15 Aug 2004, Elie Nacache wrote:

> > The current development driver (see jdbc.postgresql.org) will treat
> > absolute(0) as if you'd called beforeFirst(). Older drivers might do
> > something different.
>
> This is exactly the solution.

No, I don't believe it is. If it was you could just change your code to
call beforeFirst() instead because getRow() will always return zero when
it is on the insert row. This is your real problem. You are expecting
getRow() to return the value of the newly inserted row which it does not.

Kris Jurka