Thread: Problem with updateRow()
I'm using PostgreSQL 7.3.1 server, and the pg73jdbc2.jar file from the
website (file dated 2/13/2003 on my machine).
I have in my code a select statement like this:
String sql = "SELECT FILENAME, BOUNDARY FROM " + tableName + "
WHERE NAME = '" + name + "' AND CONTEXT = '" + context + "'";
logger.finest("SQL: " + sql);
ResultSet result = stmt.executeQuery(sql);
Later in my code I have this:
while (result.next())
{
// ...
result.updateString("BOUNDARY", event.getBoundary());
result.updateRow();
updated = true;
}
Here's the error I get:
java.sql.SQLException: No Primary Keys
Here's what the tables look like in psql:
simpletest=# \d lfs_mappings_559
Table "lfs_mappings_559"
Attribute | Type | Modifier
----------------+------------------------+----------
name | character varying(40) | not null
context | character varying(80) | not null
filename | character varying(300) | not null
boundary | character varying(50) |
insertion_time | real | not null
can_purge | character varying(8) |
Index: lfs_mappings_559_pkey
simpletest=# \d lfs_mappings_559_pkey
Index "lfs_mappings_559_pkey"
Attribute | Type
-----------+-----------------------
name | character varying(40)
context | character varying(80)
unique btree (primary key)
simpletest=#
Why is this happening? Are updateable resultSets not supported?
Oh, I forgot to mention that I AM creating my Statement correctly I
think:
Statement stmt =
conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
-----Original Message-----
From: David Hooker
Sent: Wednesday, March 19, 2003 6:55 PM
To: pgsql-jdbc@postgresql.org
Subject: [JDBC] Problem with updateRow()
I'm using PostgreSQL 7.3.1 server, and the pg73jdbc2.jar file from the
website (file dated 2/13/2003 on my machine).
I have in my code a select statement like this:
String sql = "SELECT FILENAME, BOUNDARY FROM " + tableName + "
WHERE NAME = '" + name + "' AND CONTEXT = '" + context + "'";
logger.finest("SQL: " + sql);
ResultSet result = stmt.executeQuery(sql);
Later in my code I have this:
while (result.next())
{
// ...
result.updateString("BOUNDARY", event.getBoundary());
result.updateRow();
updated = true;
}
Here's the error I get:
java.sql.SQLException: No Primary Keys
Here's what the tables look like in psql:
simpletest=# \d lfs_mappings_559
Table "lfs_mappings_559"
Attribute | Type | Modifier
----------------+------------------------+----------
name | character varying(40) | not null
context | character varying(80) | not null
filename | character varying(300) | not null
boundary | character varying(50) |
insertion_time | real | not null
can_purge | character varying(8) |
Index: lfs_mappings_559_pkey
simpletest=# \d lfs_mappings_559_pkey
Index "lfs_mappings_559_pkey"
Attribute | Type
-----------+-----------------------
name | character varying(40)
context | character varying(80)
unique btree (primary key)
simpletest=#
Why is this happening? Are updateable resultSets not supported?
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
David,
The updateable result set is really only supported for very simple
tables, and keys, I suppose it could deal with composite keys, but this
isn't the intention AFAIK. At the very least you would have to select
both columns of the key to have it work though.
There is a simple solution for you however, add the oid to your select.
ie select oid, filename, ...
and let me know if it works.
Dave
On Wed, 2003-03-19 at 19:58, David Hooker wrote:
> Oh, I forgot to mention that I AM creating my Statement correctly I
> think:
>
> Statement stmt =
> conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
> ResultSet.CONCUR_UPDATABLE);
>
> -----Original Message-----
> From: David Hooker
> Sent: Wednesday, March 19, 2003 6:55 PM
> To: pgsql-jdbc@postgresql.org
> Subject: [JDBC] Problem with updateRow()
>
>
> I'm using PostgreSQL 7.3.1 server, and the pg73jdbc2.jar file from the
> website (file dated 2/13/2003 on my machine).
>
> I have in my code a select statement like this:
>
> String sql = "SELECT FILENAME, BOUNDARY FROM " + tableName + "
> WHERE NAME = '" + name + "' AND CONTEXT = '" + context + "'";
> logger.finest("SQL: " + sql);
> ResultSet result = stmt.executeQuery(sql);
>
> Later in my code I have this:
>
> while (result.next())
> {
> // ...
> result.updateString("BOUNDARY", event.getBoundary());
> result.updateRow();
> updated = true;
> }
>
> Here's the error I get:
>
> java.sql.SQLException: No Primary Keys
>
> Here's what the tables look like in psql:
>
> simpletest=# \d lfs_mappings_559
> Table "lfs_mappings_559"
> Attribute | Type | Modifier
> ----------------+------------------------+----------
> name | character varying(40) | not null
> context | character varying(80) | not null
> filename | character varying(300) | not null
> boundary | character varying(50) |
> insertion_time | real | not null
> can_purge | character varying(8) |
> Index: lfs_mappings_559_pkey
>
> simpletest=# \d lfs_mappings_559_pkey
> Index "lfs_mappings_559_pkey"
> Attribute | Type
> -----------+-----------------------
> name | character varying(40)
> context | character varying(80)
> unique btree (primary key)
>
> simpletest=#
>
> Why is this happening? Are updateable resultSets not supported?
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
--
Dave Cramer <Dave@micro-automation.net>
Adding columns NAME and CONTEXT (the two parts of the key) did not help.
Adding the oid column makes it work.
I have a problem with this, however, since this code has to also run
against Oracle and MSSQL.
Is there another answer?
-----Original Message-----
From: Dave Cramer [mailto:Dave@micro-automation.net]
Sent: Wednesday, March 19, 2003 7:19 PM
To: David Hooker
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Problem with updateRow()
David,
The updateable result set is really only supported for very simple
tables, and keys, I suppose it could deal with composite keys, but this
isn't the intention AFAIK. At the very least you would have to select
both columns of the key to have it work though.
There is a simple solution for you however, add the oid to your select.
ie select oid, filename, ...
and let me know if it works.
Dave
On Wed, 2003-03-19 at 19:58, David Hooker wrote:
> Oh, I forgot to mention that I AM creating my Statement correctly I
> think:
>
> Statement stmt =
> conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
> ResultSet.CONCUR_UPDATABLE);
>
> -----Original Message-----
> From: David Hooker
> Sent: Wednesday, March 19, 2003 6:55 PM
> To: pgsql-jdbc@postgresql.org
> Subject: [JDBC] Problem with updateRow()
>
>
> I'm using PostgreSQL 7.3.1 server, and the pg73jdbc2.jar file from the
> website (file dated 2/13/2003 on my machine).
>
> I have in my code a select statement like this:
>
> String sql = "SELECT FILENAME, BOUNDARY FROM " + tableName + "
> WHERE NAME = '" + name + "' AND CONTEXT = '" + context + "'";
> logger.finest("SQL: " + sql);
> ResultSet result = stmt.executeQuery(sql);
>
> Later in my code I have this:
>
> while (result.next())
> {
> // ...
> result.updateString("BOUNDARY", event.getBoundary());
> result.updateRow();
> updated = true;
> }
>
> Here's the error I get:
>
> java.sql.SQLException: No Primary Keys
>
> Here's what the tables look like in psql:
>
> simpletest=# \d lfs_mappings_559
> Table "lfs_mappings_559"
> Attribute | Type | Modifier
> ----------------+------------------------+----------
> name | character varying(40) | not null
> context | character varying(80) | not null
> filename | character varying(300) | not null
> boundary | character varying(50) |
> insertion_time | real | not null
> can_purge | character varying(8) |
> Index: lfs_mappings_559_pkey
>
> simpletest=# \d lfs_mappings_559_pkey
> Index "lfs_mappings_559_pkey"
> Attribute | Type
> -----------+-----------------------
> name | character varying(40)
> context | character varying(80)
> unique btree (primary key)
>
> simpletest=#
>
> Why is this happening? Are updateable resultSets not supported?
>
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
--
Dave Cramer <Dave@micro-automation.net>
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Update:
I updated to the development driver devpgjdbc2.jar, and the problem
seems to have gone away.
-----Original Message-----
From: David Hooker
Sent: Thursday, March 20, 2003 11:08 AM
To: Dave Cramer
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Problem with updateRow()
Adding columns NAME and CONTEXT (the two parts of the key) did not help.
Adding the oid column makes it work.
I have a problem with this, however, since this code has to also run
against Oracle and MSSQL.
Is there another answer?
-----Original Message-----
From: Dave Cramer [mailto:Dave@micro-automation.net]
Sent: Wednesday, March 19, 2003 7:19 PM
To: David Hooker
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Problem with updateRow()
David,
The updateable result set is really only supported for very simple
tables, and keys, I suppose it could deal with composite keys, but this
isn't the intention AFAIK. At the very least you would have to select
both columns of the key to have it work though.
There is a simple solution for you however, add the oid to your select.
ie select oid, filename, ...
and let me know if it works.
Dave
On Wed, 2003-03-19 at 19:58, David Hooker wrote:
> Oh, I forgot to mention that I AM creating my Statement correctly I
> think:
>
> Statement stmt =
> conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
> ResultSet.CONCUR_UPDATABLE);
>
> -----Original Message-----
> From: David Hooker
> Sent: Wednesday, March 19, 2003 6:55 PM
> To: pgsql-jdbc@postgresql.org
> Subject: [JDBC] Problem with updateRow()
>
>
> I'm using PostgreSQL 7.3.1 server, and the pg73jdbc2.jar file from the
> website (file dated 2/13/2003 on my machine).
>
> I have in my code a select statement like this:
>
> String sql = "SELECT FILENAME, BOUNDARY FROM " + tableName + "
> WHERE NAME = '" + name + "' AND CONTEXT = '" + context + "'";
> logger.finest("SQL: " + sql);
> ResultSet result = stmt.executeQuery(sql);
>
> Later in my code I have this:
>
> while (result.next())
> {
> // ...
> result.updateString("BOUNDARY", event.getBoundary());
> result.updateRow();
> updated = true;
> }
>
> Here's the error I get:
>
> java.sql.SQLException: No Primary Keys
>
> Here's what the tables look like in psql:
>
> simpletest=# \d lfs_mappings_559
> Table "lfs_mappings_559"
> Attribute | Type | Modifier
> ----------------+------------------------+----------
> name | character varying(40) | not null
> context | character varying(80) | not null
> filename | character varying(300) | not null
> boundary | character varying(50) |
> insertion_time | real | not null
> can_purge | character varying(8) |
> Index: lfs_mappings_559_pkey
>
> simpletest=# \d lfs_mappings_559_pkey
> Index "lfs_mappings_559_pkey"
> Attribute | Type
> -----------+-----------------------
> name | character varying(40)
> context | character varying(80)
> unique btree (primary key)
>
> simpletest=#
>
> Why is this happening? Are updateable resultSets not supported?
>
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
--
Dave Cramer <Dave@micro-automation.net>
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org