Обсуждение: JDBC and inet type
Hi, When I use "preparedStatement.setString(5,ip);" to send values to a stored function, it obviously gets sent to postgres as "character varying". Postgres obviously complains loudly and says " Hint: No function matches the given name and argument types. You might need to add explicit type casts.". What is the appropriate workaround ? Thanks! Tim
On Fri, 2015-12-04 at 09:41 +0000, Tim Smith wrote: > Hi, > > When I use "preparedStatement.setString(5,ip);" to send values to a > stored function, it obviously gets sent to postgres as "character > varying". > > Postgres obviously complains loudly and says " Hint: No function > matches the given name and argument types. You might need to add > explicit type casts.". > > What is the appropriate workaround ? > > Thanks! > > Tim > > "preparedStatement.setString(5,ip);" will set parameter number five to the string held in the variable. That's what setString does. It expects a string to be passed. Your function's fifth IN argument needs to be defined as a "string". Within the function you will have to cast it to inet. HTH, Rob
On Fri, 4 Dec 2015 09:41:24 +0000 Tim Smith <randomdev4+postgres@gmail.com> wrote: > When I use "preparedStatement.setString(5,ip);" to send values to a > stored function, it obviously gets sent to postgres as "character > varying". > > Postgres obviously complains loudly and says " Hint: No function > matches the given name and argument types. You might need to add > explicit type casts.". > > What is the appropriate workaround ? You can define param 5 as varchar in your query, as Rob suggests: CREATE FUNCTION some_function(int, int, int, int, int, varchar) ... Then cast the 5th parameter to INET within your function. You can also cast the value in your SQL. sql = "SELECT some_function($, $, $, $, $::INET)"; ... You could also create an Inet class in Java and implement the SQLData interface, then use setObject() instead of setString(). It doesn't appear as if anyone has done this yet, but it would be nice if it were incluced in the JDBC driver. The first answer is probably best for stored procedures, as it simplifies things down the road. The second solution is more universal, as it works for non-function-calling SQL as well. The third solution is probably _really_ the correct one, from a pedantic standpoint, but it's a bit more work to implement. -- Bill Moran
Great, thanks! On 4 December 2015 at 12:17, Bill Moran <wmoran@potentialtech.com> wrote: > On Fri, 4 Dec 2015 09:41:24 +0000 > Tim Smith <randomdev4+postgres@gmail.com> wrote: > >> When I use "preparedStatement.setString(5,ip);" to send values to a >> stored function, it obviously gets sent to postgres as "character >> varying". >> >> Postgres obviously complains loudly and says " Hint: No function >> matches the given name and argument types. You might need to add >> explicit type casts.". >> >> What is the appropriate workaround ? > > You can define param 5 as varchar in your query, as Rob suggests: > > CREATE FUNCTION some_function(int, int, int, int, int, varchar) ... > > Then cast the 5th parameter to INET within your function. > > You can also cast the value in your SQL. > > sql = "SELECT some_function($, $, $, $, $::INET)"; ... > > You could also create an Inet class in Java and implement the > SQLData interface, then use setObject() instead of setString(). > It doesn't appear as if anyone has done this yet, but it would > be nice if it were incluced in the JDBC driver. > > The first answer is probably best for stored procedures, as it > simplifies things down the road. The second solution is more > universal, as it works for non-function-calling SQL as well. > The third solution is probably _really_ the correct one, from > a pedantic standpoint, but it's a bit more work to implement. > > -- > Bill Moran