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

[joerg]

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

[Bernd Hohmann]

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

[joerg]

On Mar 30, 8:44 am, Bernd Hohmann <[e-mail]:

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

[Bernd Hohmann]

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

[joerg]

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

[Bernd Hohmann]

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

[Phil Sherman]

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