Thread: Re: [INTERFACES] Q on JDBC's resultset
Quoting Kovacs Baldvin <kb136@hszk.bme.hu>:
Redirecting to the JDBC list.
> Hello.
>
> I looked in the source and have the feeling that if I
> execute a SELECT statement, the whole resultset comes through
> the network in one large bundle.
>
> Is it true?
Currently yes (although the same goes for other interfaces as well).
> It means that I cannot enable to use simple tablemodel and
> table on top of an sql query, since it would be too slow to
> download all of the data when opening it.
Work in progress, may get into 7.1 if time allows. Basically the idea is to
have an optional ResultSet that implements the retrieve using a cursor.
>
> Anyway, how does the backend handles this? If I execute a query,
> it copies the result immediately to a separate place?? So for
> a SELECT *... query on a 2GB table results immediately in the
> occupance of an pther two gigs?
Possibly, although it may be inteligent enough to handle it differently
(somebody correct me here).
> Lastly, does anyone plans to work on updatable cursors? I think
> that would make possibel to create read/write tablemodels in
> Java.
7.1 has the beginings of an UpdateableResultSet. When I can work out how to
solve a few fundamental problems it shouldn't take long.
Here's a few questions for people to see if they can figure it out:
* How to detect what table a column belongs so. Required if the query was a
join of 2 or more tables.
* How to detect if the query was on a view. Do we support updateable view's?
(not tried it myself).
* How to detect if a column was a computed value (ie count(*) or col1+col2 etc).
There's probably more, but I can't think of them at the moment.
> p.s.: Does anyone knows if mysql supports updatable queries and
> complete jdbc?
Not sure about updateable queries, but there are not many drivers that support
the complete jdbc.
PS: Updateable ResultSet's are not actually required for JDBC Compliance.
Technically if the database or driver cannot support it, it is supposed to
though an SQLException (and also say so in DatabaseMetaData methods) that it
doesn't support it.
That's why there are methods in DatabaseMetaData that currently return false
because we don't support it (yet).
ie: DatabaseMetaData.supportsPositionedUpdate()
DatabaseMetaData.supportsSelectForUpdate()
Technically (by the specs) you should check DatabaseMetaData first to see if a
feature is implemented, and then your code should handle either senario.
Were more compliant than most of the others ;-)
Peter
--
Peter Mount peter@retep.org.uk
PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/
RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/
Quoting Greg Speegle <Greg@10happythings.com>: > > > Peter T Mount wrote: > > > 7.1 has the beginings of an UpdateableResultSet. When I can work out > how to > > solve a few fundamental problems it shouldn't take long. > > > > Here's a few questions for people to see if they can figure it out: > > > > * How to detect what table a column belongs so. Required if the query > was a > > join of 2 or more tables. > > As I understand the JDBC spec, you are not supposed to be able to > update > through a ResultSet unless several criteria are met by the query, > including that the > > query is over exactly one table (to avoid problems like these). I don't > recall if > the other cases are also prohibited, but that would make the problem > easier. Yes it would make it easier. The problem I have is how to detect when it's just a single table. Peter -- Peter Mount peter@retep.org.uk PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/ RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/
Peter T Mount wrote: > 7.1 has the beginings of an UpdateableResultSet. When I can work out how to > solve a few fundamental problems it shouldn't take long. > > Here's a few questions for people to see if they can figure it out: > > * How to detect what table a column belongs so. Required if the query was a > join of 2 or more tables. As I understand the JDBC spec, you are not supposed to be able to update through a ResultSet unless several criteria are met by the query, including that the query is over exactly one table (to avoid problems like these). I don't recall if the other cases are also prohibited, but that would make the problem easier. > * How to detect if the query was on a view. Do we support updateable view's? > (not tried it myself). > > * How to detect if a column was a computed value (ie count(*) or col1+col2 etc). Greg Speegle Baylor University
Here's the code that I'm currently using. For Postgres, since there's
no tablename I pass it as an argument to the method (you can ignore that
part), for mysql I just check and see that all the table names are the
same.
Once I figure out that they're all in one table I check to see that
the resultset includes the primary key -- no primary key, no update.
I guess, since postgress supports oids, you could just fetch those
with the resultset and let the user update the table even if doesn't
have have a primary key. It does make things quite a bit more
complicated cause you need then to create a new query and somehow
hide the oid in the resultset.
--------------------------------------------------------------------------------
for (ii = 0; ii < numberColumns; ii++)
{
if (!tableNamePassed)
{
//if we're getting it in the result set check to see all
//columns are in one table
if (tableName == "") //first one
{
tableName = selectModel.dbSelect.columns[ii].table;
if(tableName.equals("") && (!tableNamePassed))
{
//tableName not passed and not from resultset. Can't update
return (false);
}
}
else
{
//if more than one table, not updatable
if (!(tableName.equals(selectModel.dbSelect.columns[ii].table)))
{
return (false);
}
}
}
queryColumns.addElement(selectModel.dbSelect.columns[ii].name);
}
try
{
// got the names of all the columns. They're all in one table
dbTable = new DBTable(dbConnect, tableName);
if(dbTable.primaryList.isEmpty())
{
return (false);
}
DBConsole.displayMessage("Checking for primary key");
Enumeration enumeration;
for (enumeration = dbTable.primaryList.elements();
enumeration.hasMoreElements(); )
{
colPos = (Integer) enumeration.nextElement();
if(!queryColumns.contains(dbTable.columns[colPos.intValue()].name))
{
return(false);
}
}
}
catch (Exception excep)
{
DBConsole.messageDialog(excep);
return(false);
}
--------------------------------------------------------------------------------
On Wed, 7 Feb 2001, Peter T Mount wrote:
> Quoting Greg Speegle <Greg@10happythings.com>:
>
> >
> >
> > Peter T Mount wrote:
> >
> > > 7.1 has the beginings of an UpdateableResultSet. When I can work out
> > how to
> > > solve a few fundamental problems it shouldn't take long.
> > >
> > > Here's a few questions for people to see if they can figure it out:
> > >
> > > * How to detect what table a column belongs so. Required if the query
> > was a
> > > join of 2 or more tables.
> >
> > As I understand the JDBC spec, you are not supposed to be able to
> > update
> > through a ResultSet unless several criteria are met by the query,
> > including that the
> >
> > query is over exactly one table (to avoid problems like these). I don't
> > recall if
> > the other cases are also prohibited, but that would make the problem
> > easier.
>
> Yes it would make it easier. The problem I have is how to detect when it's just
> a single table.
>
> Peter
>
> --
> Peter Mount peter@retep.org.uk
> PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/
> RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/
>
>
>
Dror Matalon