Oracle describe table from jdbc

[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.

[Vinod Chandana]

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]

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]

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]

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]

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]

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):

ResultSet rs1 = stmt.executeQuery("desc mytable");

throws the following exception:

java.sql.SQLException: ORA-00900: invalid SQL statement

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]

Hi
"desc mytable" is a sql plus command. I am not sure we can use that here to get a ResultSet.

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

[Pradeep bhatt]

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]

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]

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]

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 L Gibbons]

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,