org.postgresql.util.PSQLException: The column index is out of range: 2, number of columns: 1

Question

I have the following code:

String insert = "INSERT INTO " + tableName +
                "(" + COLUMNS.TILE_ID + "," + COLUMNS.TILE_DATA + "," +
                COLUMNS.TILE_LEVEL + "," + COLUMNS.TILE_COLUMN +
                "," + COLUMNS.TILE_ROW +
                "," + COLUMNS.TILE_IMAGE_FORMAT + "," + COLUMNS.TILE_SOURCE +
                ")";
String values = id + ",?" + "," +
                tile.getLevel() + "," + tile.computeColumn() + "," +
                tile.computeRow() + ",\'" + tile.getFileType().toUpperCase() +
                "\'," + "\'" +
                tile.getSource() + "\');";
        String query = insert + " VALUES (" + values;
        System.out.println(query);
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setBytes(2, tile.getData());
        return this.conn.createStatement().executeUpdate(query);

The query value:

INSERT INTO level1 (TILE_ID,TILE_DATA,TILE_LEVEL,TILE_COLUMN,TILE_ROW,TILE_IMAGE_FORMAT,TILE_SOURCE) VALUES (0,?,1,0,0,'JPG','null');

The error I get:

org.postgresql.util.PSQLException: The column index is out of range: 2, number of columns: 1.

My Table:

tile_id bigint NOT NULL,
  tile_data bytea,
  tile_level smallint,
  tile_row integer,
  tile_column integer,
  tile_image_format image_format,
  tile_source character varying(30),
  CONSTRAINT level10_pkey PRIMARY KEY (tile_id)

Any ideas?

The tile_image_format is an enum type I have created myself. — Menelaos Kotsollaris
– Menelaos Kotsollaris, Commented Jun 8, 2016 at 23:47

e4c5 · Accepted Answer · 2016-06-09 02:04:22Z

4

You haven't posted your stacktrace but your error seems to appear from this:

statement.setBytes(2, tile.getData());

Which is because you have only one parameter to bind to:

INSERT INTO level1 (TILE_ID,TILE_DATA,TILE_LEVEL,TILE_COLUMN,TILE_ROW,TILE_IMAGE_FORMAT,TILE_SOURCE) VALUES (0,?,1,0,0,'JPG','null');

The fact that the parameter is at position 2 in the values list is not what counts. It's the fact that it's the first place holder that matters. So your code should be,

statement.setBytes(1, tile.getData());

answered Jun 9, 2016 at 2:04

e4c5

53.9k11 gold badges110 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Menelaos Kotsollaris · Accepted Answer · 2016-06-09 20:05:25Z

Thanks to e4c5 answer, I fixed my code as following:

String insert = "INSERT INTO " + tableName +
                "(" + COLUMNS.TILE_ID + "," + COLUMNS.TILE_DATA + "," +
                COLUMNS.TILE_LEVEL + "," + COLUMNS.TILE_COLUMN +
                "," + COLUMNS.TILE_ROW +
                "," + COLUMNS.TILE_IMAGE_FORMAT + "," + COLUMNS.TILE_SOURCE +
                ")";
String values = id + ",?" + "," +
                tile.getLevel() + "," + tile.computeColumn() + "," +
                tile.computeRow() + ",\'" + tile.getFileType().toUpperCase() +
                "\'," + "\'" +
                tile.getSource() + "\');";
String query = insert + " VALUES (" + values;
PreparedStatement statement = conn.prepareStatement(query);
statement.setBytes(1, tile.getData());
int result = statement.executeUpdate();
statement.close();
return result;

The fact that the parameter is at position 2 in the values list is not what counts. It's the fact that it's the first place holder that matters.

Collectives™ on Stack Overflow

org.postgresql.util.PSQLException: The column index is out of range: 2, number of columns: 1

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related