By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,851 Members | 1,746 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,851 IT Pros & Developers. It's quick & easy.

jdbc on db2: getMetadate().getColumns() returns empty resultset

P: n/a
Hello world,

my program connects to a db2 database and needs to find out the column
names and data types for a specific table.
The code works fine with mysql and derby, but returns an empty
resultset
for the table's metadata with db2.
SELECTs on the connection object work, so there is no problem with the
database connection.

Here's the code:

/* dbxnc is the Connection object */

ResultSet oResults = null;
oResults = dbcnx.getMetaData().getColumns(null, dbName, tableName,
"%");

if (null != oResults)
{
/* just to find out what's going on ... */
if (oResults.getType() != ResultSet.TYPE_FORWARD_ONLY)
{
if (!oResults.first())
{
throw new SQLException("Cannot get first result!");
}
}
else
{
/* the next() call fails! */
if (!oResults.next())
{
throw new SQLException("Cannot forward in result!");
}
}

for (;;)
{
int iType = oResults.getInt("DATA_TYPE");

String sThisColName = oResults.getString("COLUMN_NAME");
System.out.println(sThisColName + " =" + iType);

if (!oResults.next())
{
break;
}
}

oResults.close();
}
Has anyone a idea what I am doing wrong?

Thanks for any help!
Joerg

Mar 30 '07 #1
Share this Question
Share on Google+
6 Replies


P: n/a
joerg wrote:
my program connects to a db2 database and needs to find out the column
names and data types for a specific table.
The code works fine with mysql and derby, but returns an empty
resultset for the table's metadata with db2.
Before retrieving metadata you must perform a query to the database.

"select * from mytable fetch first row only" would do the job.

Bernd

--
Well, there's egg and bacon; egg sausage and bacon; egg and
tr**********@spamonly.de; egg bacon and spam; egg bacon sausage
and tr**********@spamonly.net; spam bacon sausage and spam; spam
egg spam spam bacon and tr**********@nixwill.de ; spam sausage
Mar 30 '07 #2

P: n/a
On Mar 30, 8:44 am, Bernd Hohmann <trap20070...@spamonly.netwrote:
joerg wrote:
my program connects to a db2 database and needs to find out the column
names and data types for a specific table.
The code works fine with mysql and derby, but returns an empty
resultset for the table's metadata with db2.

Before retrieving metadata you must perform a query to the database.

"select * from mytable fetch first row only" would do the job.

Bernd
Hi Bernd,

thanks for the fast reply.

I added a select statement before requesting the metadata.
The select request succeeded and returns the expected data, but
requesting the metadata still fails :(

Any more ideas?

Joerg

Mar 30 '07 #3

P: n/a
joerg wrote:
>Before retrieving metadata you must perform a query to the database.

"select * from mytable fetch first row only" would do the job.

thanks for the fast reply.

I added a select statement before requesting the metadata.
The select request succeeded and returns the expected data, but
requesting the metadata still fails :(
Hm... You must do a .next() on the ResultSet and then get the Metadata
from the same Resultset. From my code:

stmt = con.createStatement();
res = stmt.executeQuery("select * from "
+ strTableName
+ " fetch first row only");
res.next();
ResultSetMetaData rsmd = res.getMetaData();
for (int i = 0; i < rsmd.getColumnCount(); i++) {
[...]

If the table is empty, it will fail (as far as I can remember). In DB2
the columnnames for the ResultSetMetaData depends on the query because
you can rename the columname in the query itself like "select id as
'customer_id' from ..." and create additional return values.

I don't know of .getColumns(...) works as expected in your environment.
Try to iterate 0..getColumnCount() and use .getColumnName(i+1) (and the
others) then. Don't forget the fact that the columns start counting from
1 and not from 0.

Bernd

--
Well, there's egg and bacon; egg sausage and bacon; egg and
tr**********@spamonly.de; egg bacon and spam; egg bacon sausage
and tr**********@spamonly.net; spam bacon sausage and spam; spam
egg spam spam bacon and tr**********@nixwill.de ; spam sausage
Mar 30 '07 #4

P: n/a
Thanks Bernd,

using the resultsets metadata works. I always tried to get the column
names from the databases' metadata
which seems not to work (maybe for the reason you described).

Joerg

Mar 30 '07 #5

P: n/a
joerg wrote:
using the resultsets metadata works. I always tried to get the column
names from the databases' metadata
which seems not to work (maybe for the reason you described).
Ah... DatabaseMetaData... Thats broken since the JDBC-Driver exists. Or
at least: doesn't work as expected.

Bernd

--
Well, there's egg and bacon; egg sausage and bacon; egg and
tr**********@spamonly.de; egg bacon and spam; egg bacon sausage
and tr**********@spamonly.net; spam bacon sausage and spam; spam
egg spam spam bacon and tr**********@nixwill.de ; spam sausage
Mar 30 '07 #6

P: n/a
An alternative should be to execute the following query:
describe select * from tablename

You'll get four columns back:
sqltype A numeric code and descriptive name for the data type
sqllen The (maximum) physical length of the stored data
sqlname.data The column name
sqlname.length The length of the column name (sqlname.data)

Phil Sherman
joerg wrote:
Hello world,

my program connects to a db2 database and needs to find out the column
names and data types for a specific table.
The code works fine with mysql and derby, but returns an empty
resultset
for the table's metadata with db2.
SELECTs on the connection object work, so there is no problem with the
database connection.

Here's the code:

/* dbxnc is the Connection object */

ResultSet oResults = null;
oResults = dbcnx.getMetaData().getColumns(null, dbName, tableName,
"%");

if (null != oResults)
{
/* just to find out what's going on ... */
if (oResults.getType() != ResultSet.TYPE_FORWARD_ONLY)
{
if (!oResults.first())
{
throw new SQLException("Cannot get first result!");
}
}
else
{
/* the next() call fails! */
if (!oResults.next())
{
throw new SQLException("Cannot forward in result!");
}
}

for (;;)
{
int iType = oResults.getInt("DATA_TYPE");

String sThisColName = oResults.getString("COLUMN_NAME");
System.out.println(sThisColName + " =" + iType);

if (!oResults.next())
{
break;
}
}

oResults.close();
}
Has anyone a idea what I am doing wrong?

Thanks for any help!
Joerg
Mar 30 '07 #7

This discussion thread is closed

Replies have been disabled for this discussion.