Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Oracle describe table from jdbc

 
Venkat Nagam
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Gurus,
I need to get Table Schema from JDBC. I tried using following, but no luck.
...
stmt = conn.createStatement();
ResultSet rs1 = stmt.executeQuery("desc mytable");
while ( rs1.next() ) { //line 36
System.out.println(rs1.getString(1));
}
Here is exception:
java.lang.NullPointerException
at createScripts.main(createScripts.java:36)
Let me know if any idea what going wrong here.
Thanks.
 
Vinod Chandana
Ranch Hand
Posts: 59
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Venkat,
Use ResultSetMetaData. That is a much better option. I think the statement might be a problem (if the connection is correct). Do let me know if this helps.
[LINK]
URL: http://java.sun.com/j2se/1.4.2/docs/api/java/sql/ResultSetMetaData.html
[\LINK]
Regards,
Vinod.
Originally posted by Venkat Nagam:
Gurus,
I need to get Table Schema from JDBC. I tried using following, but no luck.
...
stmt = conn.createStatement();
ResultSet rs1 = stmt.executeQuery("desc mytable");
while ( rs1.next() ) { //line 36
System.out.println(rs1.getString(1));
}
Here is exception:
java.lang.NullPointerException
at createScripts.main(createScripts.java:36)
Let me know if any idea what going wrong here.
Thanks.
 
Venkat Nagam
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Doesn't work...:-(
I added following line before like 36
ResultSetMetaData rsmd = rs1.getMetaData();
Now exception says there is problem in above statement.
java.lang.NullPointerException
at createScripts.main(createScripts.java:35)
 
Venkat Nagam
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Found alternate solution...
select COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE from USER_TAB_COLUMNS where TABLE_NAME='mytable' order by column_id;
Above query produces pretty much same output as "desc mytable".
 
Jay Dellinger
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Venkat,
ResultSetMetaData will give you meta data about the resultset from your query. If you want information on all the columns in your table, just run a select query to grab all the fields. I don't think it even needs to actually retrieve data, so send something like this:
"select * from table_name where 0 = 1"
Then get your ResultSetMetaData and it should have some useful information for you. There may be a different (better?) query you could run, but that is the basic idea.
Jay
 
Amit Da
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Actually the ideal interface to be used here is DatabaseMetaData. You can get an object of its type from connection object.
Call method getColumns() on it. You need to pass following values
1.catalog - for Oracle pass null - because Oracle tables are not within a catalog - its a concept like package - for getting parameters of a stored proc, you'd have to pass package name here as you can have stored proc in a package.
2.schema = username value that owns table
3.tableName = exact table name or a pattern like MYTAB% (all tables starting with MYTAB)
4.columnName = if you need only certain columns matching a pattern pas pattern - e.g. COL% - all columns whose names start with COL
if you want all columns pass null or %
This getColumns() method does same thing as your "select COLUMN_NAME, DATA_LENGTH, DATA_TYPE from user_tab_columns" query (internally driver might be calling same). In short this class and its methods like getColumns() are very useful and you should use them as much as you can...
http://java.sun.com/j2se/1.3/docs/api/java/sql/DatabaseMetaData.html
Amit
 
Avi Abrami
Ranch Hand
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Venkat,
According to the information you have supplied, "rs1" is null. (That's why you are getting a "NullPointerException" ;-) But I don't understand why, since, when I run your code on SUN Solaris 7 with Oracle 8i (8.1.7.4) and J2SE 1.3.1_02 and Oracle's "thin" JDBC driver, this line (of your code):

throws the following exception:

So I'm guessing that you haven't really supplied all of your code (or you're using a totally different environment to mine). So which is it? So perhaps if you supply the missing information, I may be able to help you further.
Good Luck,
Avi.
 
Sainudheen Mydeen
Ranch Hand
Posts: 218
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi
"desc mytable" is a sql plus command. I am not sure we can use that here to get a ResultSet.

----------------
Sainudheen
 
Pradeep bhatt
Ranch Hand
Posts: 8927
Firefox Browser Java Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Sainudheen Mydeen:
Hi
"desc mytable" is a sql plus command. I am not sure we can use that here to get a ResultSet.

----------------
Sainudheen

The above is not a SQL statement so cannot be used by JDBC.
 
Pradeep bhatt
Ranch Hand
Posts: 8927
Firefox Browser Java Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Use the following method of DatabaseMetaData
public ResultSet getTables(String catalog,
String schemaPattern,
String tableNamePattern,
String[] types)
throws SQLException
[ October 07, 2003: Message edited by: Pradeep Bhat ]
 
Amit Da
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Actually getTables() method of DatabaseMetaData gives info about a table or tables matching pattern specified. But in this case since the user needs information about columns getColumns() method is more suitable.
 
Pradeep bhatt
Ranch Hand
Posts: 8927
Firefox Browser Java Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by da Am:
Actually getTables() method of DatabaseMetaData gives info about a table or tables matching pattern specified. But in this case since the user needs information about columns getColumns() method is more suitable.

You are right.
 
Wendy Gibbons
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sainudheen Mydeen wrote:Hi
"desc mytable" is a sql plus command. I am not sure we can use that here to get a ResultSet.

----------------
Sainudheen


I was getting very irate with squirrel SQL when it wouldn't run "desc mytable" till somebody explained that fact to me,
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic