CODEWITHSUNDEEP
javajdbc
Abhi
Abhi

Reputation: 2378

JDBC built in functions and prepared statements

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.

Upvotes: 1

Views: 3380

Answers (7)

Alexandre
Alexandre

Reputation: 1026

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();

Upvotes: 2

Steve McLeod
Steve McLeod

Reputation: 52498

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.

Upvotes: 0

Alexandre
Alexandre

Reputation: 1026

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

Upvotes: 1

alexmeia
alexmeia

Reputation: 5251

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.

Upvotes: 0

alexmeia
alexmeia

Reputation: 5251

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();

Upvotes: 0

alexmeia
alexmeia

Reputation: 5251

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.

Upvotes: 0

Thilo
Thilo

Reputation: 262850

Sure, that should work.

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

Upvotes: 2

Related Questions