SQLException: no such column

Question

I am a new member. I think I struggled with sqlitejdbc. I made a query to the sqlite database from a java program. I got the above exception. My request

select * from ( person as p inner join company as c on p.p_id=c.p_id ) inner join contact as ct on p.p_id=ct.p_id where p.p_id=?; 

When I put the request in the navicatLite editor by placing p.p_id = '1' instead of p.p_id =?, I was fine. He showed me the correct values.

But from my java program. I got this exception.

  Exception in thread "main" java.sql.SQLException: no such column: p.p_id
         at org.sqlite.DB.throwex (DB.java:288)
         at org.sqlite.NativeDB.prepare (Native Method)
         at org.sqlite.DB.prepare (DB.java:114)
         at org.sqlite.PrepStmt. (PrepStmt.java:37)
         at org.sqlite.Conn.prepareStatement (Conn.java:231)
         at org.sqlite.Conn.prepareStatement (Conn.java:224)
         at org.sqlite.Conn.prepareStatement (Conn.java:213)
         at programTest.test.main (test.java:19)
 Java Result: 1

I am sure that there is a table person and p_id. I searched for this question on this site, but found one that is related to ruby ​​on rails, not java. I do not know what happened.

My java program

 import java.sql.*; /** * * @author Htet 101 */ public class test { public static void main(String[] args) throws Exception { Class.forName("org.sqlite.JDBC"); Connection conn = DriverManager.getConnection("jdbc:sqlite://C://Users//Htet 101//Documents//addressbook.s3db"); PreparedStatement stat = conn.prepareStatement("select * from (person as p inner join company as c on p.p_id=c.p_id) inner join contact as ct on p.p_id=ct.p_id where p.p_id=?;"); stat.setInt(1, 1); ResultSet rs = stat.executeQuery(); while (rs.next()) { System.out.print("Name : " + rs.getString("p_name") + " "); } rs.close(); conn.close(); } } 

I am developing it using NetBeans 6.9.1, SQLite 3.7.8, SQLiteJDBC v056.

Answer 1

I'm not quite sure, but I think it should work if you remove the brackets from From : select * from person as p inner join company as c on p.p_id=c.p_id
inner join contact as ct on p.p_id=ct.p_id where p.p_id=?; select * from person as p inner join company as c on p.p_id=c.p_id
inner join contact as ct on p.p_id=ct.p_id where p.p_id=?;

Answer 2

If the exact same question goes well with navicat, and wrong with java, it means the driver is faulty. But make sure it’s exactly the same.

In addition, these brackets are suspicious - get rid of them.

Answer 3

The brackets are probably interpreted as if you are starting what is called an inline view in Oracle - basically a dynamically created view. Therefore, I do not think this is a driver error.