problem regarding DatabaseMetaData.....

[jayander kumar]

hi ,

I am facing a unique problem..
I want to get the primary key of a table..i have written the following code.


DatabaseMetaData md = null;

try {
md=con.getMetaData();

ResultSet rs=md.getPrimaryKeys(null,null,"student");


rs.next();

System.out.println(rs);

System.out.println(rs.getString(1));
}
catch (SQLException e) {
e.printStackTrace();
}

Everything is fine.I am using IBM DB2 8.1 version.
I have a table named student.I have a primary key studentid in the table.but when i am running this program i am getting this Exception.

DB2ResultSet
{
Statement -> DB2Statement
Statement -> {
Statement -> Connection -> DB2Connection
Statement -> Connection -> {
Statement -> Connection -> connectionHandle = 1
Statement -> Connection -> SPConnected = false
Statement -> Connection -> source = sample
Statement -> Connection -> user = db2admin
Statement -> Connection -> conArgs =
Statement -> Connection -> closed = false
Statement -> Connection -> describeCached = false
Statement -> Connection -> describeParam = true
Statement -> Connection -> isReadOnly = false
Statement -> Connection -> autoClose = false
Statement -> Connection -> LONGDATA compat = false
Statement -> Connection -> }

Statement -> statementHandle = 1:1
Statement -> SQL = null
Statement -> maxRows = 0
Statement -> maxFieldSize = 0
Statement -> rowCount = 0
Statement -> colCount = 0
Statement -> closed = false
Statement -> internalStmt = true
Statement -> returnCode = 0
Statement -> }

numCols = 6
mappedRS = false
nullTag = true
closed = false
maxFieldSize = 0
returnCode = 0
returnLen = 0
colTypes[0] = 0
colSizes[0] = 0
colTypes[1] = 0
colSizes[1] = 0
colTypes[2] = 0
colSizes[2] = 0
colTypes[3] = 0
colSizes[3] = 0
colTypes[4] = 0
colSizes[4] = 0
colTypes[5] = 0
colSizes[5] = 0
}
COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver] CLI0115E Invalid cursor state. SQLSTATE=24000
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(Unknown Source)
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(Unknown Source)
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_return_code(Unknown Source)
at COM.ibm.db2.jdbc.app.DB2ResultSet.getString2(Unknown Source)
at COM.ibm.db2.jdbc.app.DB2ResultSet.getString(Unknown Source)
at DatabaseMetaDataExample.runExample(DatabaseMetaDataExample.java:96)
at DatabaseMetaDataExample.main(DatabaseMetaDataExample.java:138)


kindly help...
thanx

[Shailesh Chandra]

modify your code as

  
 
ResultSet rs=md.getPrimaryKeys(null,null,"student");
 
if(rs.next()) // or while(rs.next()) if your expect more than one primary keys
{
   System.out.println(rs.getString(1));
}

seems your resultsent doesn't have any row
and you are performing rs.next() on resultset and afterwards rs.getString(1) is causing exception

thanks
[ March 03, 2005: Message edited by: Shailesh Chandra ]

[jayander kumar]

hi shailesh,
thanx for reply....but
I know that my resultset in empty...but why???
I have only one primary key in the table, thats why i am not using while loop.

why getPrimaryKeys() method is not able to retrieve the primary key?? Do we need to set something for using DatabaseMetaData.

Also one thing... i am able to get database name , version nos , name all tables from the database using DatabaseMetaDate. but i am not able to retrieve
primary key of a particular table.

kindly help...

thanx.

[Shailesh Chandra]

There can be a issue with table name.Since it is in lower case.

with example of oracle:

Let say I have a table named "mytable" in oracle.

and If I excute a query

select * from mytable --- it will work

but if I excute following query

SELECT * FROM USER_OBJECTS WHERE OBJECT_NAME='mytable'

It will not work because object name are stored in upper case .

try with

ResultSet rs=md.getPrimaryKeys(null,null,"STUDENT");

also try with adding the "table schema" in your code
[ March 03, 2005: Message edited by: Shailesh Chandra ]

[jayander kumar]

hi shailesh,
Thanx....
It worked ....I just changed "student" to "STUDENT"

thank once again.

[Shailesh Chandra]

cheers

shailesh