Clever Geek Handbook

JDBC Inline Functions and Prepared Statements

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

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

+1


source to share


7 replies


From what I've seen, the first parameter to the pointfromtext function is a string, and the second is a number. So try this:



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


source


Of course this should work.



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

+2


source


Question marks are not evaluated correctly because they are in between single quotes. Delete them and it should work,

+1


source


The scope of the PreparedStatement object must fulfill queries accurately. If the query contains a built-in DB function, that's fine and everything should work as long as the same query runs outside of the PreparedStatement.

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

0


source


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


source


Have you tried not to pair words in the drug? For testing, you should try to insert these parameters directly into the string, for example:

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

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

and then try updating.

0


source


You may have found an issue with the Postgresql JDBC driver, not JDBC per se. In general, what you want to achieve is working with JDBC.

0


source






More articles:


All Articles