1

I have a bit of a problem with inserting some data in the database. The data is being read by an CSV parser and changed to data besides that, I continue to get this error message:

Connected to the PostgreSQL server successfully.
Naam van de garage: P_Erasmusbrug, Longditude: 4.482313155, Latitude: 51.91024645
org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0.
at org.postgresql.core.v3.SimpleParameterList.bind(SimpleParameterList.java:65)
at org.postgresql.core.v3.SimpleParameterList.setStringParameter(SimpleParameterList.java:128)
at org.postgresql.jdbc.PgPreparedStatement.bindString(PgPreparedStatement.java:1023)
at org.postgresql.jdbc.PgPreparedStatement.setString(PgPreparedStatement.java:374)
at org.postgresql.jdbc.PgPreparedStatement.setString(PgPreparedStatement.java:358)
at Database.ConnectDatabase.parser(ConnectDatabase.java:80)
at Events.CSVReader.main(CSVReader.java:40)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:147)
Thank you for your service.
Naam van de garage: P_St.Jacobsplaats, Longditude: 4.482054381, Latitude: 51.92410235
Thank you for your service.
Naam van de garage: P_Schouwburgplein, Longditude: 4.473618335, Latitude: 51.92102728
org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0.

Which continues for all the other lines of data. Is there maybe a way to fix this as I don't really understand what the error message includes.. The a, b2, and c2 are variables for the ''name'' , ''londitude'' and ''latitude''.

package Database;

import java.io.*;
import java.sql.*;
import java.util.HashMap;
import java.sql.SQLException;

public class ConnectDatabase {
private final String url = "jdbc:postgresql://localhost/Project3";
private final String user = "postgres";
private final String password = "kaas123";
private Connection conn;

public Connection connect() {
    Connection conn = null;
    try {
        conn = DriverManager.getConnection(url, user, password);
        System.out.println("Connected to the PostgreSQL server successfully.");
    } catch (SQLException exception) {
        System.out.println(exception.getMessage());
    }

    this.conn = conn;
    return conn;
}

public HashMap getGarages() {
    HashMap<String, Double> newHashMap = new HashMap<String, Double>();
    try {
        Statement stmt = conn.createStatement();
        ResultSet rs;

        rs = stmt.executeQuery("SELECT deelgemeente, COUNT(garagenaam) FROM garages GROUP BY deelgemeente");
        while (rs.next()) {
            String deelGemeenteNaam = rs.getString("deelgemeente");
            double garageNaamCount = rs.getDouble("COUNT");
            newHashMap.put(deelGemeenteNaam, garageNaamCount);
        }
    } catch (Exception e) {
        System.err.println("Got an exception!");
        System.err.println(e.getMessage());
    }
    return newHashMap;
}

public HashMap getTheftYear(int year) {
    HashMap<String, Double> newHashMap = new HashMap<String, Double>();
    try {
        Statement stmt = conn.createStatement();
        ResultSet rs;

        rs = stmt.executeQuery("SELECT deelgemeente, percentagediefstal FROM autodiefstal WHERE jaar = " + year);
        while (rs.next()) {
            String deelGemeenteNaam = rs.getString("deelgemeente");
            double deelPercentage = rs.getDouble("percentagediefstal");
            newHashMap.put(deelGemeenteNaam, deelPercentage);
        }
    } catch (Exception e) {
        System.err.println("Got an exception!");
        System.err.println(e.getMessage());
    }
    return newHashMap;
}

public int parser(String a, float b2, float c2) {
    int updated = 0;
    Connection conn = null;
    PreparedStatement stmt = null;
    try {

        conn = DriverManager.getConnection(url, user, password);

        String insertSQL = "INSERT INTO testparser(garagenaam, xpos, ypos) VALUES(" + a + "," + b2 + "," + c2 + ")";
        stmt = conn.prepareStatement(insertSQL);

        stmt.setString(1, a);
        stmt.setFloat(2, b2);
        stmt.setFloat(3, c2);

        System.out.println("Inserted data into the database...");
        updated = stmt.executeUpdate();



    } catch (SQLException se) {
        se.printStackTrace();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            if (stmt != null)
                conn.close();
        } catch (SQLException se) {
        }
        try {
            if (conn != null)
                conn.close();
        } catch (SQLException se) {
            se.printStackTrace();
        }
    }
    System.out.println("Thank you for your service.");
    this.conn = conn;
    return updated;

    }
}
1
  • removed the incompatible mysql tag Commented Apr 20, 2017 at 9:57

1 Answer 1

4

You aren't correctly using the ? placeholders system, replace :

String insertSQL = "INSERT INTO testparser(garagenaam, xpos, ypos) VALUES(" + a + "," + b2 + "," + c2 + ")";

with

String insertSQL = "INSERT INTO testparser(garagenaam, xpos, ypos) VALUES(?,?,?)";
Sign up to request clarification or add additional context in comments.

2 Comments

Thank you so much! :D The ''???'' was all I had to change, I forgot it replaces the variables immediately with mine
I'll accept your answer in the next 9 minutes, thanks for the help =)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.