1

My programm will get an input of a valid sql, and should return column names for the sql. And I want to do this with out executing the sql statement at all. I am looking for a java solution.

My dbms is oracle optimized for olap, and the tables are so big that result set restriction does not working. Actually execution time is not acceptable for my case. it takes longer than a minute

oers
  • 18,436
  • 13
  • 66
  • 75
dursun
  • 1,861
  • 2
  • 21
  • 38
  • possible duplicate: http://stackoverflow.com/questions/660609/sql-parser-library-for-java , you are looking for a sql parser, that gives you the column names from the query – oers Feb 09 '12 at 08:34
  • parsing is one option, but my main concern is not limited parsing. – dursun Feb 09 '12 at 09:24
  • I you don't want to execute the sql, you have to parse the string that represents the query. You need to interpret this string as an sql query and this is done by applying a grammar to it --> parsing. – oers Feb 09 '12 at 09:27
  • you say I dont have any option other than parsing it? How does dbms create exection plan? that maybe a solution? – dursun Feb 09 '12 at 09:33
  • The dbms has its own sql grammar parser of course. I assumed that by "not executing" you meant: I don't want to interact with the database at all. Depending on your database you could set **avoid execution** parameters, execute the query and get an empty result set. Or use some kind of **limit 1** functionality. But this is all database dependent. – oers Feb 09 '12 at 09:41
  • my dbms is oracle optimised for olap, and the tables are so big that result set restrition does not working. Actually execution time is not acceptable for my case. it takes longer than a minute. – dursun Feb 09 '12 at 09:45
  • Without a database the sql is just a string. You need to parse that. – oers Feb 09 '12 at 09:50
  • Ok I can use database, But I dont want my db to execute query. is there a solution? – dursun Feb 09 '12 at 11:08

1 Answers1

3

Prepare the query, but rather than executing it, just call getMetaData on the prepared statement. Provided your driver supports it, this should return the result set meta data with the column descriptions without executing a query.

araqnid
  • 127,052
  • 24
  • 157
  • 134
  • can you give samle code becase afaik it does not work like that. – dursun Feb 09 '12 at 12:42
  • `ResultSetMetaData rsmd = conn.prepareStatement(sql).getMetaData();` and then `rsmd.getColumnCount()`, `rsmd.getColumnName(1)`, `rsmd.getColumnTypeName(1)` etc. – araqnid Feb 09 '12 at 13:10
  • 1
    I missed your comment on driver support. I think the driver in ojdbc14.jar does not support. because I got the following exception... java.sql.SQLException: statement handle not executed: getMetaData at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146) at oracle.jdbc.driver.OracleResultSetImpl.getMetaData(OracleResultSetImpl.java:135) at oracle.jdbc.driver.OraclePreparedStatement.getMetaData(OraclePreparedStatement.java:4049) – dursun Feb 09 '12 at 13:17
  • oh, that's frustrating. In that case you might well be stuck wrapping `SELECT * FROM (....) x WHERE rownum < 1` around the query, which should have the actual execution optimised out but still produce the result set meta data when it is executed. – araqnid Feb 09 '12 at 13:22