0

I'm building a wrapper for DB access using AWS' Lambda functions Java, the issue I'm having is that Lambda parameters are JSON, thus typeless, so all incoming params are de-JSON'd into Strings, and when adding them to a PreparedStatement using the setObject(param, value) method they all get treated as if you were using setString(param, value), this is fine for Strings and numbers, but when it comes to a parameter that is a timestamp or date, we get a parameter type mismatch like this:

org.postgresql.util.PSQLException: operator does not exist: timestamp with time zone >= character varying
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.

I'm aware that passing parameter types as an extra field for the JSON might help, but I'm worried about both the extra overhead and, the need of manually adding the parameter type to the JSON, as it's currently being built "automagically" from the underlying DTO using GSON.

Can anyone figure out a different approach, perhaps on passing the parameters to the PreparedStatement.?

4

You'll need to explicitly convert your datetime strings to appropriate data types. Since you chose not to include the SQL statement in question, I can only guess what it's like, but if it looks anything like

...WHERE timestamp_column > ?...

you'll need to replace it with

...WHERE timestamp_column > TO_TIMESTAMP(?, 'YYYY-MM-DD HH24:MI:SS')...

using, of course, an appropriate format string.

  • Seems legit, but what about the overhead of using that in each SQL? Or not relevant? – Gonzalo Vasquez Aug 4 '17 at 21:28
  • 1
    Yes, what about it? How much of a problem is this "overhead" if your code currently doesn't work at all? You have to convert from character to date-time data type somewhere. – mustaccio Aug 4 '17 at 22:29
  • I mean the overhead of changing al the current SQL queries, as the wrapper is intended as a replacement of the existing one – Gonzalo Vasquez Aug 4 '17 at 22:46
  • Well, I finally ended using your solution – Gonzalo Vasquez Aug 7 '17 at 18:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.