JDBC built in functions and prepared statements

Question

Is there a way to execute a query(containing built in DB function) using PreparedStatement?

Example: insert into foo (location) values (pointfromtext('12.56666 13.67777',4130)) Here pointfromtext is a built in function.

Answer 1

Sure, that should work.

If not, what is your database system, and can you run the exact same command from the SQL command line?

Answer 2

The scope of a PreparedStatement object is exactly to execute queries. If the query contains built in DB function is ok, and everything should work if the same query works outside the PreparedStatement.

As Thilo said, test your query form SQL command line, or with the SQL graphical tool that you usually use.

Answer 3

I guess I was not clear. I can run the above SQL as a statement.

What I want to do is parametrize it and run it using a prepared statement. Something like this.

PreparedStatement bar = connection.prepareStatement("insert into foo (location) values (pointfromtext('? ?',4130)))"); bar.setDouble(1, 13.67777); bar.setDouble(2, 13.67777); bar.executeUpdate();

But the above does not work. How do I go about doing it?

Answer 4

Did you put connection.commit() after this code?

The code should be:

 PreparedStatement bar = connection.prepareStatement("insert into foo (location) values (pointfromtext('? ?',4130)))");
 bar.setDouble(1, 13.67777);
 bar.setDouble(2, 13.67777); 
 bar.executeUpdate();
 connection.commit();

Answer 5

This executes when executed through command propmpt

insert into map_address (location) values(pointfromtext('POINT(12.565 13.457)',4130))

Now the prepared statement equivalent of this would be

PreparedStatement preparedStatement = getConnection().prepareStatement("insert into map_address (location) values(pointfromtext('POINT(? ?)',4130))");
preparedStatement.setDouble(1, 12.56565665);
preparedStatement.setDouble(2, 12.57565757);
preparedStatement.executeUpdate();

When I execute this I get the following error

Exception in thread "main" 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:52)

Answer 6

Did you try to don't set doubles in the preparaedStatement? Just for testing, you should try to insert this parameters directly in the String, something like:

String sql = "insert into map_address (location) values(pointfromtext('POINT(" +  "12.56565665" + " " + "12.57565757" + ")',4130))"

PreparedStatement preparedStatement = getConnection().prepareStatement(sql);

and then try to execute the update.

Answer 7

The question marks are not being evaluated correctly because they are between simple quotes. Remove them, and it should work,

Answer 8

That is the problem. If I remove the quotes the function does not execute(Db throws error). I tried escaping and stuff like that. Did not work. Any pointers?

Answer 9

Perhaps you've found a problem with the Postgresql JDBC driver, rather than with JDBC per se. In general what you want to achieve works with JDBC.

Answer 10

By what I've seen, the first parameter on pointfromtext function is a string, and the second a number. So, try the following:

PreparedStatement preparedStatement = getConnection().prepareStatement("insert into map_address (location) values(pointfromtext('POINT(' || ? || ' ' || ? || ')',4130))");
preparedStatement.setString(1, "12.56565665");
preparedStatement.setString(2, "12.57565757");
preparedStatement.executeUpdate();