PgSQL Exception: column name not found.

Question

Hi,

I am using postgresql-8.3-603.jdbc4.jar with jdk 1.6 in my application to do the db operations. I am getting the below exceptions at sometimes and doing restart helps to avoid this exceptions temporarily.

org.postgresql.util.PSQLException: The column name sender_id was not found in this ResultSet. at org.postgresql.jdbc2.AbstractJdbc2ResultSet.findColumn(AbstractJdbc2ResultSet.java:2502) at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getString(AbstractJdbc2ResultSet.java:2345) at org.apache.commons.dbcp.DelegatingResultSet.getString(DelegatingResultSet.java:225) at org.apache.commons.dbcp.DelegatingResultSet.getString(DelegatingResultSet.java:225) at com.netcore.bulkrequest.db.FeedDAO.setFeedDetails(FeedDAO.java:142) at com.netcore.bulkrequest.feed.Feed.getInstance(Feed.java:37) at com.netcore.bulkrequest.core.BulkRequestTask.(BulkRequestTask.java:86) at com.netcore.bulkrequest.core.BulkRequestValidate.getBulkRequestTaskObject(BulkRequestValidate.java:104) at com.netcore.bulkrequest.core.BulkRequestValidate.run(BulkRequestValidate.java:57) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908) at java.lang.Thread.run(Thread.java:619)

Here is the code snippet:

public class FeedDAO { /** * Database connection pool object */ private final DBContext dbc;

private final Feed feed;

public static final String SENDER_ID_ATTRIBUTE = "sender_id";

/**
 * Constructor
 * 
 * @param dbc
 * @param feed
 */
public FeedDAO(DBContext dbc, Feed feed) {
    this.dbc = dbc;
    this.feed = feed;
}

public void setFeedDetails() throws SQLException {

    String feedDetailsQuery = "SELECT a.priority, b.keyword, b.welcome " +
            "   FROM feed AS a, pub_feed_info AS b " +
            "   WHERE a.resource_id = b.resource_id AND b.resource_id = ?";

    String senderIdQuery = "SELECT b.attribute_value AS " +
            SENDER_ID_ATTRIBUTE + " FROM " +
            "attribute_master AS a, feed_attributes AS b " +
            "WHERE a.attribute_id = b.attribute " +
            "   AND a.attribute_name='" + SENDER_ID_ATTRIBUTE + "' " +
            "   AND feed_id = ?";

    Connection con = null;
    PreparedStatement fdStmt = null;
    PreparedStatement siStmt = null;

    try {
        con = dbc.getConnection();

        //Get the feed details
        fdStmt = dbc.getPreparedStatement(con, feedDetailsQuery);

        fdStmt.setInt(1, this.feed.getFeedId());
        fdStmt.execute();

        ResultSet fdResults = fdStmt.getResultSet();

        while (fdResults.next()) {
            String keyword = fdResults.getString("keyword");
            String welcomeMsg = fdResults.getString("welcome");
            int priority = fdResults.getInt("priority");

            if(null != keyword) {
                this.feed.setKeyword(keyword);
            } else {
                this.feed.setKeyword(String.valueOf(this.feed.getFeedId()));
            }
            this.feed.setWelcomeMsg(welcomeMsg);
            this.feed.setPriority(priority);
        }

        //Get the sender id
        siStmt = dbc.getPreparedStatement(con, senderIdQuery);
        siStmt.setInt(1, this.feed.getFeedId());

        if(siStmt.execute()) {
            ResultSet siResults = siStmt.getResultSet();

            while(siResults.next()) {
                String senderId = siResults.getString(SENDER_ID_ATTRIBUTE);

                this.feed.setSenderId(senderId);
            }

        } else {
            this.feed.setSenderId(Feed.DEFAULT_SENDER_ID);
        }

    } catch (SQLException ex) {
        throw ex;
    } finally {
        if (fdStmt != null) { fdStmt.close(); }
        if (siStmt != null) { siStmt.close(); }
        if (con  != null) { con.close(); }
    }
}

}

Can anyone please help me to find the permanent fix?

Thanks, Mani

Answer 1

The key part of the error is "The column name sender_id was not found in this ResultSet" -- te very first row. So, how about showing us the query that's looking for a column that's just not there, and maybe the results of executing that query interactively in pgsql, the relevant parts of your schema, etc? Surely you can't expect us to help you debug without seeing anything more than the exception traceback, with zero clues about your code and DB!