Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I am using JDBC to query a PostgreSQL database. And I want to populate a column called wiki_url based on the value of another column, level which will determine which value of 5 columns to concatenate to a value in wiki_url. I think the big issue is setting null values to something other than null programmatically without postgresql as the null value expression to not null was a common problem that caused my same error. From print statements I also figured out that the handleEntries function seems to be stuck on name3 and continually repeating.

However I still don't want to use another sql query to check values because it might cause a transaction lock.

I am trying to perform batch updates without using a connection pool to prevent deadlocks so I am using only 1 prepared statement. The control flow will be first checking if the wiki_url doesn't have anything after a slash and then populating it, else checking for duplicate entries after that slash and making them unique.

I am fairly certain that I probably don't have the batch updates correctly coded, but I would like to resolve the error I stated in the question first. I have tried changing the columns to lower case, but it doesn't seem to work. The error and my code is below. The only relevant debug assisting statement I get is:

UPDATE adminboundaries SET WIKI_URL = 'http://127.0.0.1/mediawiki/index.php/null'

Which continues over and over again till it hits a certain number of rows. Its not supposed to have null at the end.

SQLState C: 42703 java.util.logging.Logger@28ca1c
VendorError C: 0
Dec 18, 2012 1:16:11 PM Shapefile_Repair main
SEVERE: Batch entry 0 UPDATE adminboundaries SET WIKI_URL = 'http://127.0.0.1/mediawiki/index.php/null' was aborted.  Call getNextException to see the  cause.org.postgresql.util.PSQLException: ERROR: column "wiki_url" of relation "adminboundaries" does not exist
Position: 28

public static void handleEntries(Long level, PreparedStatement w_ustmt,
        String base_url, String name5, String name4, String name3,
        String name2, String name1, String name0) throws SQLException {
    String wiki_url_entry = "";
    try {
        if (level.equals((long) 5)) {
            // wiki_url = wiki_url + name5;
            wiki_url_entry = base_url + name5;
            w_ustmt.setString(1, wiki_url_entry);
            w_ustmt.addBatch();
            System.out.println(w_ustmt.toString());
        } else if (level.equals((long) 4)) {
            // wiki_url = wiki_url + name4;
            wiki_url_entry = base_url + name4;
            w_ustmt.setString(1, wiki_url_entry);
            w_ustmt.addBatch();
            System.out.println(w_ustmt.toString());
        } else if (level.equals((long) 3)) {
            // wiki_url = wiki_url + name3;
            wiki_url_entry = base_url + name3;
            w_ustmt.setString(1, wiki_url_entry);
            w_ustmt.addBatch();
            System.out.println(w_ustmt.toString());
        } else if (level.equals((long) 2)) {
            // wiki_url = wiki_url + name2;
            wiki_url_entry = base_url + name2;
            w_ustmt.setString(1, wiki_url_entry);
            w_ustmt.addBatch();
            System.out.println(w_ustmt.toString());
        } else if (level.equals((long) 1)) {
            // wiki_url = wiki_url + name1;
            wiki_url_entry = base_url + name1;
            w_ustmt.setString(1, wiki_url_entry);
            w_ustmt.addBatch();
            System.out.println(w_ustmt.toString());
        } else {
            // wiki_url = wiki_url + name0;
            wiki_url_entry = base_url + name0;
            w_ustmt.setString(1, wiki_url_entry);
            w_ustmt.addBatch();
            System.out.println(w_ustmt.toString());
        }
        w_ustmt.addBatch();
    } catch (SQLException ex) {
        Logger lgr = Logger.getLogger(Shapefile_Repair.class.getName());
        lgr.log(Level.SEVERE, ex.getMessage());
        while (ex != null) {
            System.out.println(ex.getNextException());
        }
        // System.out.println("SQLState: A " + ex.getSQLState());
        // System.out.println("VendorError A: " + ex.getErrorCode());
    }
}

/* this function will handle all the urls which have same entries */
public static void handleDups(String wiki_url, String base_url,
        String name5, String name4, String name3, String name2,
        String name1, String name0, PreparedStatement w_ustmt) {
    String wiki_url_entry = "";
    try {
        if (wiki_url.toString().equals(base_url + name5)) {
            wiki_url_entry = wiki_url + " (" + name0 + "." + name1 + "."
                    + name2 + "." + name3 + "." + name4 + ")";
            // rewriting here, yet it is only printing ?, ?
            w_ustmt.setString(1, wiki_url_entry);
            w_ustmt.addBatch();
            System.out.println(w_ustmt.toString());
        } else if (wiki_url.toString().equals(base_url + name4)) {
            wiki_url_entry = wiki_url + " (" + name0 + "." + name1 + "."
                    + name2 + "." + name3 + ")";
            w_ustmt.setString(1, wiki_url_entry);
            w_ustmt.addBatch();
            System.out.println(w_ustmt.toString());
        } else if (wiki_url.toString().equals(base_url + name3)) {
            wiki_url_entry = wiki_url + " (" + name0 + "." + name1 + "."
                    + name2 + ")";
            w_ustmt.setString(1, wiki_url_entry);
            w_ustmt.addBatch();
            System.out.println(w_ustmt.toString());
        } else if (wiki_url.toString().equals(base_url + name2)) {
            wiki_url_entry = wiki_url + " (" + name0 + "." + name1 + ")";
            w_ustmt.setString(1, wiki_url_entry);
            w_ustmt.addBatch();
            System.out.println(w_ustmt.toString());
        } else if (wiki_url.toString().equals(base_url + name1)) {
            wiki_url_entry = wiki_url + " (" + name0 + ")";
            w_ustmt.setString(1, wiki_url_entry);
            w_ustmt.addBatch();
            System.out.println(w_ustmt.toString());
        }
        w_ustmt.addBatch();
        // System.out.println(w_ustmt.toString());
    } catch (SQLException ex) {
        Logger lgr = Logger.getLogger(Shapefile_Repair.class.getName());
        lgr.log(Level.SEVERE, ex.getMessage());
        System.out.println("SQLState: B " + ex.getSQLState());
        System.out.println("VendorError B :" + ex.getErrorCode());
    }
}
public static void main(String[] args) throws InstantiationException,
        IllegalAccessException, SQLException {
    // TODO Auto-generated method stub
    Connection conn = null;
    // use the log4jdbc4 wrapper for the connection object
    // conn = new net.sf.log4jdbc.ConnectionSpy(conn);
    Statement stmt = null;
    ResultSet rs = null;
    try {
        // conn = makeConnection(args[0], args[1], args[2], args[3], "");
        String host = args[0];
        String port = args[1];
        String database = args[2];
        String user = args[3];
        String password = args[4];
        String sql_query = "SELECT \"NAME_0\", \"NAME_1\", \"NAME_2\", \"NAME_3\", \"NAME_4\", \"NAME_5\", \"WIKI_URL\", \"LEVEL_DEPT\" FROM"
                + " AdminBoundaries WHERE \"WIKI_URL\" IN(SELECT \"WIKI_URL\" FROM AdminBoundaries"
                + " GROUP By \"WIKI_URL\" HAVING (count (\"WIKI_URL\") > 1)) ORDER BY \"WIKI_URL\";";
        Class.forName("org.postgresql.Driver").newInstance();

        String url = "jdbc:postgresql://" + host + ":" + port + "/"
                + database;
        conn = DriverManager.getConnection(url, user, password);
        // if(conn.equals(null)){
        // System.err.println("Connection complete");
        // }
        DatabaseMetaData meta;
        try {
            if (conn.isClosed()) {
                System.out.println("closed");
            }
            System.out.println(conn.getWarnings());
            meta = conn.getMetaData();
            System.out.println("Does database support batch processes?:"
                    + meta.supportsBatchUpdates());
            // after checking the table, adminboundaries was in fact
            // evident, so the issue now is changing the sql queries to
            // lower case
            /*
             * ResultSet tables = meta.getTables(null, null, "%", null);
             * while (tables.next()){
             * System.out.println(tables.getString(3)); }
             */
            // trying to check if column exists, which it turns out does
            // not?
            ResultSet columns = meta.getColumns(null, null,
                    "adminboundaries", "%");
            while (columns.next()) {
                System.out.println(columns.getString(4));
            }
            ResultSetMetaData rsmd = columns.getMetaData();
            int numCol = rsmd.getColumnCount();
            for (int i = 1; i < numCol + 1; i++) {
                while (columns.next()) {
                    if (rsmd.getColumnName(i).equals("WIKI_URL")
                            || (rsmd.getColumnName(i).equals("NAME_0"))) {
                        System.out.println(columns.getString(4));
                    } else {
                        System.out.println("Did not find column");
                    }
                }
            }
        } finally {
            System.out.println(conn.getTransactionIsolation());
            System.out.println(sql_query);
            stmt = conn.createStatement();
            System.out.println(stmt.getFetchSize());
            stmt.setMaxRows(555);
            System.out.println(conn.getTypeMap());
            // stmt.addBatch(sql_query);
            rs = stmt.executeQuery(sql_query);
            // batch_counter is for the row count of the SQL statements
            // executed
            // int batch_counter = pstmt.executeUpdate();
            // update dup_sql's arguments being set as it will vary
            // String wiki_sql =
            // "UPDATE AdminBoundaries SET \"WIKI_URL\" = ?";
            PreparedStatement w_ustmt = conn
                    .prepareStatement("UPDATE adminboundaries SET WIKI_URL = ?");
            // int counter = 0;
            // for (counter = 0; counter < 1000; counter++) {
            conn.setAutoCommit(false);
            // stmt.addBatch(sql_query);
            // stmt.setQueryTimeout(30);
            System.out.println(rs.getFetchSize());
            SQLWarning resultsetWarning = rs.getWarnings();
            System.out.println(resultsetWarning);
            // consider changing tables/columns to lower case as sources say
            // to do
            while (rs.next()) {
                String base_url = "http://127.0.0.1/mediawiki/index.php/";
                String wiki_url = rs.getString("WIKI_URL");
                Long level = rs.getLong("LEVEL_DEPT");
                String name0 = rs.getString("NAME_0");
                String name1 = rs.getString("NAME_1");
                String name2 = rs.getString("NAME_2");
                String name3 = rs.getString("NAME_3");
                String name4 = rs.getString("NAME_4");
                String name5 = rs.getString("NAME_5");
                // use 127.0.0.1, not ncsirad-pc b/c wiki_urls are coming
                // back 127.0.0.1

                // TO DO: rethink this if statement to include in function
                // and execute
                // both functions without conditionals above. So it will run
                // through first
                // then run through second.

                if (wiki_url.toString().equals(
                        base_url)) {
                    // Savepoint savepoint1 =
                    // conn.setSavepoint("wiki_entry");
                    // w_ustmt.setQueryTimeout(30);
                    // System.out.println("Getting into duplicates loop");
                    handleEntries(level, w_ustmt, base_url, name5, name4,
                            name3, name2, name1, name0);

                    // add to batch
                    // w_ustmt.addBatch(wiki_sql);
                    // int counts2[] = w_ustmt.executeBatch();
                    // make sure to try the below
                    // w_ustmt.executeUpdate();
                    // conn.rollback(savepoint1);
                } else {
                    handleDups(wiki_url, base_url, name5, name4, name3,
                            name2, name1, name0, w_ustmt);
                    w_ustmt.setQueryTimeout(30);
                    // dup_pstmt.addBatch(dup_sql);
                    // int count3[] = dup_pstmt.executeBatch();
                }

            }
            int counts[] = w_ustmt.executeBatch();
            System.out.println("here is " + counts);
            conn.commit();
            conn.setAutoCommit(true);
            // counter = 0;
            // }
        }
        System.out.println("finished queries");
        rs.close();

    } catch (ClassNotFoundException e) {
        e.printStackTrace();
        // System.exit(1);
    } catch (SQLException ex) {
        Logger lgr = Logger.getLogger(Shapefile_Repair.class.getName());
        lgr.log(Level.SEVERE, ex.getMessage());
        System.out.println("SQLState C: " + ex.getSQLState() + " " + lgr);
        System.out.println("VendorError C: " + ex.getErrorCode());
        if (ex != null) {
            System.out.println(ex.getNextException());
        }
    } finally {
        try {
            if (rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
        }
    }
    // conn.close();
}

}

share|improve this question

1 Answer 1

up vote 2 down vote accepted

Attention to what Postgres is saying:

42703 undefined_column

Open your database tool and check that table column is not written in uppercase characters. It it is then you going need to quote the column:

Fix your code at

PreparedStatement w_ustmt = conn
                    .prepareStatement("UPDATE adminboundaries SET WIKI_URL = ?");

replace with

PreparedStatement w_ustmt = conn
                    .prepareStatement("UPDATE adminboundaries SET \"WIKI_URL\" = ?");
  • Also you should rewrite your switch code that has repeated statements over all branches
  • If not using old java remove the old Class.forName and use the new DataSource implementation PGSimpleDataSource

Notes from DriverManager docs:

The use of a DataSource object is the preferred means of connecting to a data source.

share|improve this answer
    
Thanks, it was in fact a simple escape quotes error. –  Carl Carlson Dec 20 '12 at 18:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.