JDBC and inet type

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general