Postgre JDBC Driver Error

91 views
Skip to first unread message

Roger

unread,
Oct 26, 2014, 11:14:57 AM10/26/14
to web4j...@googlegroups.com
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

unread,
Oct 26, 2014, 12:42:28 PM10/26/14
to web4j...@googlegroups.com
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

unread,
Oct 26, 2014, 12:54:03 PM10/26/14
to web4j...@googlegroups.com
FYI - I just added a remark to the User Guide to make this point more clear.

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

Roger

unread,
Oct 26, 2014, 3:30:04 PM10/26/14
to web4j...@googlegroups.com
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

unread,
Oct 26, 2014, 6:07:40 PM10/26/14
to web4j...@googlegroups.com
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

unread,
Oct 26, 2014, 9:11:54 PM10/26/14
to web4j...@googlegroups.com
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
Reply all
Reply to author
Forward
0 new messages