ResultSetMetaData.getTableName(int column) is not working properly?
Hi all
ResultSetMetaData.getTableName(int column) is not working properly. Its returning empty string with Oracle 10g and sql server 2005 both.
I am connecting to database through DSN (for oracle 10 g for sql server 2005).I am using JDK 1.5.The code that I am using is as follows
-
import java.sql.*;
class ora_test{
public static void main (String args []) throws SQLException
{
Connection connection = null;
try {
String driverName = "sun.jdbc.odbc.JdbcOdbcDriver";
Class.forName(driverName);
System.out.println("Driver Initialized......");
String url = "jdbc:odbc:ravi2;UID=scott;PWD=tiger";
String username = "scott";
String password = "tiger";
connection = DriverManager.getConnection(url);
PreparedStatement ps=connection.prepareStatement("select * FROM emp");
ResultSetMetaData rsmt=ps.getMetaData();
for(int iCount=1;iCount<=rsmt.getColumnCount();iCount++)
{
String tempRow[]=new String [3];
tempRow[0]=rsmt.getColumnName(iCount);
tempRow[1]=rsmt.getTableName(iCount);
tempRow[2]=rsmt.getColumnTypeName(iCount);
System.out.println(tempRow[0]+"- "+tempRow[1]+"- "+tempRow[2]);
}
connection.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
-
I want to fetch the column name, type and table name without executing the query. Can any one tell ,is there any othere ways for this?
Thanks,
-Ravi
Hi Ravi,
If you have a query which by definition will always include the table name(s) then why do you need to query the ResultSet for the table name?
Assuming you really believe you need to query for the table name: If a query joined multiple tables, which table name would you expect to be returned?
Assuming that the answer to the previous question is "I don't know" (and the correct answer is no table name is associated with the composite ResultSet): If you want information on the names of tables in the database, columns of a table and type of those columns, you can use DatabaseMetaData and get that information without issuing your select query.
If you are hoping to get columns / types of a ResultSet that has not been created, I don't think you can do that (as in your example, where you only prepare the statement).
There are many examples of DatabaseMetaData within the forums if you are looking for examples. I would recommend not using ODBC / DSN for either Oracle 10g or SQL Server, you would be much better off using the vendors type 4 drivers.
I'm not certain this answers your questions, but please let me know.
Best of luck on whatever you are doing!
Hi
Hopfully this may clear my problem.
I have query that may be complex join (Only select query in this case).I want to fetch column name, column type and table name to which the column in select query belongs (if any).I want to fetch only those column and table which is related to query thats why I am using ResultSetMetadata.And I dont want to execute the query thats why I am using "PreparedStatement". The reason for using ODBC / DSN is that, with oracle jdbc driver it throws following exception when I try to get ResultSetMetadata without executing the query.
java.sql.SQLException: statement handle not executed: getMetaData
I can fetch corresponding column/table name using DatabaseMetaData and table names in FROM clause.But I want to know why ResultSetMetaData.getTableName(int column) returns empty string. Is there any problem with driver or I am doing any mistake? I have also tried with oracle jdbc driver and sql server with different driver (Inet,JTDS).
Is there any other solution for same?
Please let me know if any?
Or should I use any other driver for this?
>oracle jdbc driver it throws following exception when I try to get >ResultSetMetadata without executing the query.
>java.sql.SQLException: statement handle not executed: getMetaData
It seems that PreparedStatement.getMetaData() isn't fully supported by Oracle driver. Maybe you have to search "." in your sql, then you can get a possible table name list, then you can use DatabaseMetaData.getTables() to filter some table aliases, then you can search " as " in your sql, and filter again. It seems too complicated :(
