Postgre JDBC Driver Error

[Roger]

I read through the electricity tutorial and ran the application locally with Postgre as the backend database. I was trying to save an electricity spending but ran into this error:

ERROR: column "isestimated" is of type smallint but expression is of type boolean. Hint: You will need to rewrite or cast the expression

I understand that Postgres requires stringtype="unspecified" for internal conversions according to the documentation in web4j. So, I changed the electricity.xml:

<Resource url="jdbc:postgresql://localhost:5432/electricity?stringtype=unspecified"...>

But I still got the same error after adding the extra parameter. After doing more research, it looks like I need to explicitly define get and set methods in JDBC. Does web4j exposes PreparedStatment so I can do a SetInt() in order to explicitly cast data type?

Thanks,

Roger

[John O'Hanley]

Hello Roger,

Yes, stringtype="unspecified" has to be added, but not in the manner you describe. It's done at a lower level.

Typically, your web app will configure a DataSource in its container - a connection pool. The stringtype="unspecified" should be part of your datasource configuration. That way, it's defined and used in only one place.

For the tutorial, a file named 'WEB-INF/tomcat/electricity.xml' is supplied. That file will need to be edited. In this case, I think you will just need to add
stringtype=unspecified
to the URL of the connection to your postgres database.

- John O'Hanley

[John O'Hanley]

FYI - I just added a remark to the User Guide to make this point more clear.

http://www.web4j.com/UserGuide.jsp#Postgres

[Roger]

That's what I did. Here is my electricity.xml

<Context docBase="C:\Projects\JavaPractices\electricity" reloadable="true">

 <Resource 

   name="jdbc/electricity" 

   auth="Container" 

   type="javax.sql.DataSource" 

   username="postgres"

   password="xxxxxx"

   driverClassName="org.postgresql.Driver"

   url="jdbc:postgresql://localhost:5432/electricity?stringtype=unspecified"

   maxActive="10"

   maxIdle="5"

 />

</Context>

When you mentioned the connection pool and lower level configuration, is there another file I need to create or edit in addition to electricity.xml?

Thanks,

Roger

[John O'Hanley]

No, there's no other file. That was just me being stupid, and not reading your post closely enough. Sorry about that...

This is the MySQL definition of the column:
  IsEstimated TINYINT UNSIGNED DEFAULT 0 NOT NULL, /* Pseudo-boolean 1-true, 0-false. */

Can't you use 'boolean' as the postgres data-type here? The error message says you are using smallint, if I'm not mistaken.
What if you use boolean as the column type?

http://www.postgresql.org/docs/9.1/static/datatype-boolean.html

Web4j doesn't expose its internals to let you do the cast manually, no. That shouldn't be needed, though.

- John

[Roger]

Yes, I can use boolean as the column type. I based on your MySQL script to create the Postgre SQL script. This is the change that is needed to make the script works.

Yes, there is no needed to expose explicit casting in Web4j because the data type should be the same end-to-end.

Thanks for pointing that out.

Roger