SQLException : no such column

Question

I'm a new member. I have been struggling with sqlitejdbc, I thought. I made a query to sqlite database from a java program.I got above exception. My query is

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 query on navicatLite editor by placing p.p_id='1' instead of p.p_id=? , I was fine. It showed me 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'm sure there exists the person table and p_id field. I've searched this kind of question on this site but I found the one which is related to ruby on rails, not java. I have no idea what's wrong.

My java program is

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'm developing it using NetBeans 6.9.1, SQLite 3.7.8, SQLiteJDBC v056.

score 3 · Accepted Answer · answered Nov 13 '11 at 16:47

3

I'm not absolutely sure, but I think it should work if you remove 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=?;

answered Nov 13 '11 at 16:47

a1ex07

36,826
12
90
103

Great! It's now correct. Thanks for your help. Well, but why can't I add the brackets? Thanks in advance. – waiyan Nov 13 '11 at 17:32
I didn't work with sqllite, so I cannot tell you for sure. I was really surprised that it worked from some client application; in my understanding braces hide aliases within (so `p` and `c` become invisible in outer context) – a1ex07 Nov 13 '11 at 17:37
@ht3t it's the normal practice around here to mark the answer that helped solve your problem – tshepang Feb 16 '12 at 14:27

Bozho · Answer 2 · 2011-11-13T22:08:35.600

0

If the exact same query goes fine with navicat, and wrong with java, it means the driver is buggy. But make sure it is exactly the same.

Also, those brackets are suspicious - get rid of them.

edited Nov 13 '11 at 22:08

answered Nov 13 '11 at 16:49

Bozho

588,226
146
1,060
1,140

Thanks for your help. I removed brackets and it has been solved. – waiyan Nov 13 '11 at 17:33

score 0 · Answer 3 · answered Nov 13 '11 at 20:23

0

The brackets are likely interpreted as if you are beginning what is called an inline view in Oracle - basically a dynamically created view. So I don't think it is a bug in the driver.

answered Nov 13 '11 at 20:23

EdgeCase

4,719
16
45
73

SQLException : no such column

3 Answers3

Linked