org.postgresql.util.PSQLException: ERROR: syntax error at or near "("

Question

I have craeted a Web Application that can be deployed on Heroku by maven eclipse.

Group Id: org.glassfish.jersey.archetypes

Artifact Id: jersey-heroku-webapp

version: 2.17

I tested the appication on the localhost and POSTMAN and it works fine. I pushed it to heroku to test it on servlet container but I am getting 520 OK 520 it is just a number that I return in the SQLEXCEPTION. IN the Heroku log I found this error:

2015-05-13T13:10:37.364388+00:00 app[web.1]:    at java.lang.Thread.run(Thread.j
ava:745)
2015-05-13T13:10:37.389547+00:00 app[web.1]: org.postgresql.util.PSQLException:
ERROR: syntax error at or near "("
2015-05-13T13:10:37.389560+00:00 app[web.1]:   Position: 45
2015-05-13T13:10:37.389740+00:00 app[web.1]:    at org.postgresql.core.v3.QueryE
xecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)

Database class:

public class Database {


    public Database() {

    }

    public void drivercConnection() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("jar works :) ");

        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    private static Connection getConnection() throws URISyntaxException, SQLException {

        URI dbUri = new URI(System.getenv("DATABASE_URL"));
        String username = dbUri.getUserInfo().split(":")[0];
        String password = dbUri.getUserInfo().split(":")[1];
        String dbUrl = "jdbc:postgresql://" + dbUri.getHost() + ':'
                + dbUri.getPort() + dbUri.getPath();

        Connection con = DriverManager.getConnection(dbUrl, username, password);
        return con;
    }

    public int insertData(String mac, int route, double latD, double longD) {
        int status = 201;

        drivercConnection();

        try {
            Connection con = null;
            try {
                con = getConnection();
            } catch (URISyntaxException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }


            // Create a statement
            Statement stt = con.createStatement();

            DatabaseMetaData dbm = con.getMetaData();

            ResultSet tables = dbm.getTables(null, null, "bus", null);

            if (tables.next()) {
                // stt.execute("ALTER TABLE bus AUTO_INCREMENT = 1");

                return insertRecord(mac, route, latD, longD, status, con);

            } else {
                // Create bus table
                stt.execute("CREATE TABLE IF NOT EXISTS bus"
                        + "(id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,"
                        + "mac VARCHAR(30) NOT NULL UNIQUE,"
                        + "route int(11) NOT NULL,"
                        + "latitude FLOAT(10,6) NOT NULL,"
                        + "longitude FLOAT(10,6) NOT NULL,"
                        + "created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)");

                stt.execute("CREATE EVENT IF NOT EXISTS  AutoDelete "
                        + "ON SCHEDULE EVERY 3 MINUTE "
                        + "DO "
                        + "DELETE FROM bus WHERE created_at < (NOW() - INTERVAL 3 MINUTE)");

                stt.execute("SET GLOBAL event_scheduler = ON");

                first_data_insert(mac, route, latD, longD, con);

            }
            return status;
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            return status = 520;

        }
    }

Answer 1

that indicate the sql query is not right. you might want to change it into something like this.

DROP TABLE IF EXISTS bus;

CREATE TABLE bus( 
id SERIAL PRIMARY KEY,
mac VARCHAR(30) NOT NULL UNIQUE,
route int NOT NULL,
latitude numeric(10,6) NOT NULL,
longitude numeric(10,6) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

Note that create statement (afaik) is not a sql standard command. So, because you're using postgresql, you need to change it into postgresql create statement.