Thread:
Such declaration like this
id int4 NOT NULL DEFAULT nextval('seq_group_id'::regclass)
and
CONSTRAINT i_group PRIMARY KEY (id)
give me the oportumity don't bother about unique value of id
and use another query.
INSERT INTO groups(g_name) VALUES("new group");
I hope to find the same easy way in using ResultSet's objects (), i.e. to
forget about id in my code until it nessesary for me and not for jdbc.
What you are looking for is in hibernate, or some other high level
persistence framework . In JDBC you have to do all the work.
Dave
On 10-Jan-07, at 8:16 AM, vasylenko@uksatse.org.ua wrote:
>
>
> Such declaration like this
>
> id int4 NOT NULL DEFAULT nextval('seq_group_id'::regclass)
> and
> CONSTRAINT i_group PRIMARY KEY (id)
>
> give me the oportumity don't bother about unique value of id
> and use another query.
>
> INSERT INTO groups(g_name) VALUES("new group");
>
> I hope to find the same easy way in using ResultSet's objects (),
> i.e. to
> forget about id in my code until it nessesary for me and not for jdbc.
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that
> your
> message can get through to the mailing list cleanly
You can do the same with JDBC. Don't set a value for your id column
in the ResultSet. The backend will do it for you.
On 1/10/07, Dave Cramer <pg@fastcrypt.com> wrote:
> What you are looking for is in hibernate, or some other high level
> persistence framework . In JDBC you have to do all the work.
>
> Dave
>
> On 10-Jan-07, at 8:16 AM, vasylenko@uksatse.org.ua wrote:
>
> >
> >
> > Such declaration like this
> >
> > id int4 NOT NULL DEFAULT nextval('seq_group_id'::regclass)
> > and
> > CONSTRAINT i_group PRIMARY KEY (id)
> >
> > give me the oportumity don't bother about unique value of id
> > and use another query.
> >
> > INSERT INTO groups(g_name) VALUES("new group");
> >
> > I hope to find the same easy way in using ResultSet's objects (),
> > i.e. to
> > forget about id in my code until it nessesary for me and not for jdbc.
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 1: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that
> > your
> > message can get through to the mailing list cleanly
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
Andres,
what he is looking for is to not have to select the id and still have
the rowset work.
Dave
On 10-Jan-07, at 9:26 AM, Andres Olarte wrote:
> You can do the same with JDBC. Don't set a value for your id column
> in the ResultSet. The backend will do it for you.
>
> On 1/10/07, Dave Cramer <pg@fastcrypt.com> wrote:
>> What you are looking for is in hibernate, or some other high level
>> persistence framework . In JDBC you have to do all the work.
>>
>> Dave
>>
>> On 10-Jan-07, at 8:16 AM, vasylenko@uksatse.org.ua wrote:
>>
>> >
>> >
>> > Such declaration like this
>> >
>> > id int4 NOT NULL DEFAULT nextval('seq_group_id'::regclass)
>> > and
>> > CONSTRAINT i_group PRIMARY KEY (id)
>> >
>> > give me the oportumity don't bother about unique value of id
>> > and use another query.
>> >
>> > INSERT INTO groups(g_name) VALUES("new group");
>> >
>> > I hope to find the same easy way in using ResultSet's objects (),
>> > i.e. to
>> > forget about id in my code until it nessesary for me and not for
>> jdbc.
>> >
>> > ---------------------------(end of
>> > broadcast)---------------------------
>> > TIP 1: if posting/reading through Usenet, please send an
>> appropriate
>> > subscribe-nomail command to majordomo@postgresql.org so that
>> > your
>> > message can get through to the mailing list cleanly
>>
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 6: explain analyze is your friend
>>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
Dave and Andre, thank for your responses!
The JDBC driver needs the Primary Key in Select query to identify the row.
I was mistaken thinking of doing without it,
ResultSet makes updates in Row executing the
java.sql.Connection.prepareStatement("UPDATE holidays SET "g_name" = ?
WHERE "id" = ?")
and executeUpdate()
when user makes call ResultSet.updateRow() and "id" - the Primary Key in
database's table. It's logicaly for me now....
The same is going on whe users call ResultSet.insertRow().
The only question presents, why the JDBCDriver developers want to find
PrimaryKey or oid field inside method "moveToInsertRow()"?
I think it mustn't be JDBCDriver problem.
It is the Postrgre server trouble or developers who use JDBCDriver.
I'm going to do in way Andre has recommended and use id field in SELECT
query as Dave has advised.
Thank!
On 11-Jan-07, at 1:42 AM, vasylenko@uksatse.org.ua wrote:
>
>
>
> Dave and Andre, thank for your responses!
>
> The JDBC driver needs the Primary Key in Select query to identify
> the row.
> I was mistaken thinking of doing without it,
>
> ResultSet makes updates in Row executing the
>
> java.sql.Connection.prepareStatement("UPDATE holidays SET "g_name"
> = ?
> WHERE "id" = ?")
> and executeUpdate()
>
> when user makes call ResultSet.updateRow() and "id" - the Primary
> Key in
> database's table. It's logicaly for me now....
>
> The same is going on whe users call ResultSet.insertRow().
> The only question presents, why the JDBCDriver developers want to find
> PrimaryKey or oid field inside method "moveToInsertRow()"?
> I think it mustn't be JDBCDriver problem.
> It is the Postrgre server trouble or developers who use JDBCDriver.
Can you explain this more, or send a small test case. There should be
no reason to get a primary key simply to move to insert row.
We do need the primary key in the dataset, but other than that we
shouldn't fail on move to insert row ?
Dave
>
>
> I'm going to do in way Andre has recommended and use id field in
> SELECT
> query as Dave has advised.
>
> Thank!
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that
> your
> message can get through to the mailing list cleanly
Ok, Dave
your question:
>Can you explain this more, or send a small test case. There should be
>no reason to get a primary key simply to move to insert row.
>We do need the primary key in the dataset, but other than that we
>shouldn't fail on move to insert row ?
I have just trased into moveToInsertRow() method step by step.
Firstly, it try to find oid field in result set
Than it looking for Primary Key in result.
That's all what I have done.
there 3 method
moveToInsertRow()
checkUpdateblet()
isUpdateable()
the exception is thrown in the last method.
public synchronized void moveToInsertRow()
throws SQLException
{
checkUpdateable();
if (insertStatement != null)
{
insertStatement = null;
}
// make sure the underlying data is null
clearRowBuffer(false);
onInsertRow = true;
doingUpdates = false;
}
private void checkUpdateable() throws SQLException
{
checkClosed();
if (!isUpdateable())
throw new PSQLException(GT.tr("ResultSet is not updateable.
The query that generated this result set must select only one table, and
must select all primary keys from that table. See the JDBC 2.1 API
Specification, section 5.6 for more details."),
PSQLState.INVALID_CURSOR_STATE);
if (updateValues == null)
{
// allow every column to be updated without a rehash.
updateValues = new HashMap((int)(fields.length / 0.75), 0.75f);
}
}
boolean isUpdateable() throws SQLException
{
checkClosed();
if (resultsetconcurrency == ResultSet.CONCUR_READ_ONLY)
throw new PSQLException(GT.tr("ResultSets with concurrency
CONCUR_READ_ONLY cannot be updated."),
PSQLState.INVALID_CURSOR_STATE);
if (updateable)
return true;
if ( Driver.logDebug )
Driver.debug("checking if rs is updateable");
parseQuery();
if ( singleTable == false )
{
if ( Driver.logDebug )
Driver.debug("not a single table");
return false;
}
if ( Driver.logDebug )
Driver.debug("getting primary keys");
//
// Contains the primary key?
//
primaryKeys = new Vector();
// this is not stricty jdbc spec, but it will make things much
faster if used
// the user has to select oid, * from table and then we will just
use oid
usingOID = false;
int oidIndex = 0;
try
{
oidIndex = findColumn( "oid" );
}
catch (SQLException l_se)
{
//Ignore if column oid isn't selected
}
int i = 0;
// if we find the oid then just use it
//oidIndex will be >0 if the oid was in the select list
if ( oidIndex > 0 )
{
i++;
primaryKeys.add( new PrimaryKey( oidIndex, "oid" ) );
usingOID = true;
}
else
{
// otherwise go and get the primary keys and create a hashtable
of keys
String[] s = quotelessTableName(tableName);
String quotelessTableName = s[0];
String quotelessSchemaName = s[1];
java.sql.ResultSet rs = ((java.sql.Connection)
connection).getMetaData().getPrimaryKeys("", quotelessSchemaName,
quotelessTableName);
for (; rs.next(); i++ )
{
String columnName = rs.getString(4); // get the columnName
int index = findColumn( columnName );
if ( index > 0 )
{
primaryKeys.add( new PrimaryKey(index, columnName ) );
// get the primary key information
}
}
rs.close();
}
if ( Driver.logDebug )
Driver.debug( "no of keys=" + i );
if ( i < 1 )
{
throw new PSQLException(GT.tr("No primary key found for table
{0}.", tableName),
PSQLState.DATA_ERROR);
}
updateable = primaryKeys.size() > 0;
if ( Driver.logDebug )
Driver.debug( "checking primary key " + updateable );
return updateable;
}
Two things that you might check for, but I think your problem is number 2.
1- Is the ResultSet updateable? Basically you create your Statement like this:
stmt=conn.createStatement
(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE)
2- Does your table have a oid and a primary key defined? Are you
selecting them in the ? You need to select those two (or at least
one, I'm not sure) in the ResultSet to be able to insert. Just do a
"SELECT * FROM table.schema LIMIT 1" to be sure
On 1/12/07, vasylenko@uksatse.org.ua <vasylenko@uksatse.org.ua> wrote:
>
>
>
> Ok, Dave
> your question:
> >Can you explain this more, or send a small test case. There should be
> >no reason to get a primary key simply to move to insert row.
> >We do need the primary key in the dataset, but other than that we
> >shouldn't fail on move to insert row ?
>
> I have just trased into moveToInsertRow() method step by step.
>
> Firstly, it try to find oid field in result set
> Than it looking for Primary Key in result.
>
> That's all what I have done.
>
> there 3 method
> moveToInsertRow()
> checkUpdateblet()
> isUpdateable()
>
> the exception is thrown in the last method.
>
>
> public synchronized void moveToInsertRow()
> throws SQLException
> {
> checkUpdateable();
>
> if (insertStatement != null)
> {
> insertStatement = null;
> }
>
>
> // make sure the underlying data is null
> clearRowBuffer(false);
>
> onInsertRow = true;
> doingUpdates = false;
>
> }
>
>
>
> private void checkUpdateable() throws SQLException
> {
> checkClosed();
>
> if (!isUpdateable())
> throw new PSQLException(GT.tr("ResultSet is not updateable.
> The query that generated this result set must select only one table, and
> must select all primary keys from that table. See the JDBC 2.1 API
> Specification, section 5.6 for more details."),
> PSQLState.INVALID_CURSOR_STATE);
>
> if (updateValues == null)
> {
> // allow every column to be updated without a rehash.
> updateValues = new HashMap((int)(fields.length / 0.75), 0.75f);
> }
> }
>
>
>
>
>
>
> boolean isUpdateable() throws SQLException
> {
> checkClosed();
>
> if (resultsetconcurrency == ResultSet.CONCUR_READ_ONLY)
> throw new PSQLException(GT.tr("ResultSets with concurrency
> CONCUR_READ_ONLY cannot be updated."),
> PSQLState.INVALID_CURSOR_STATE);
>
> if (updateable)
> return true;
>
> if ( Driver.logDebug )
> Driver.debug("checking if rs is updateable");
>
> parseQuery();
>
> if ( singleTable == false )
> {
> if ( Driver.logDebug )
> Driver.debug("not a single table");
> return false;
> }
>
> if ( Driver.logDebug )
> Driver.debug("getting primary keys");
>
> //
> // Contains the primary key?
> //
>
> primaryKeys = new Vector();
>
> // this is not stricty jdbc spec, but it will make things much
> faster if used
> // the user has to select oid, * from table and then we will just
> use oid
>
>
> usingOID = false;
> int oidIndex = 0;
> try
> {
> oidIndex = findColumn( "oid" );
> }
> catch (SQLException l_se)
> {
> //Ignore if column oid isn't selected
> }
> int i = 0;
>
>
> // if we find the oid then just use it
>
> //oidIndex will be >0 if the oid was in the select list
> if ( oidIndex > 0 )
> {
> i++;
> primaryKeys.add( new PrimaryKey( oidIndex, "oid" ) );
> usingOID = true;
> }
> else
> {
> // otherwise go and get the primary keys and create a hashtable
> of keys
> String[] s = quotelessTableName(tableName);
> String quotelessTableName = s[0];
> String quotelessSchemaName = s[1];
> java.sql.ResultSet rs = ((java.sql.Connection)
> connection).getMetaData().getPrimaryKeys("", quotelessSchemaName,
> quotelessTableName);
> for (; rs.next(); i++ )
> {
> String columnName = rs.getString(4); // get the columnName
> int index = findColumn( columnName );
>
> if ( index > 0 )
> {
> primaryKeys.add( new PrimaryKey(index, columnName ) );
> // get the primary key information
> }
> }
>
> rs.close();
> }
>
> if ( Driver.logDebug )
> Driver.debug( "no of keys=" + i );
>
> if ( i < 1 )
> {
> throw new PSQLException(GT.tr("No primary key found for table
> {0}.", tableName),
> PSQLState.DATA_ERROR);
> }
>
> updateable = primaryKeys.size() > 0;
>
> if ( Driver.logDebug )
> Driver.debug( "checking primary key " + updateable );
>
> return updateable;
> }
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>