Combination View Flat View Tree View
Threads [ Previous | Next ]
toggle
Juan Fernández
[Solved] Problems With Liferay 4.2.1 and MySQL 5.0... isnull
October 2, 2008 7:04 AM
Answer

Juan Fernández

LIFERAY STAFF

Rank: Liferay Legend

Posts: 1257

Join Date: October 2, 2008

Recent Posts

Hi everyone!
I have installed Liferay 4.2.1 and MySQL 5.0 (that is the version we use at work) and I have an SQL problem.
It is about isnull function. In the portal.properties file, I have uncommented these two lines
custom.sql.function.isnull=ISNULL(?, '1') = '1'
custom.sql.function.isnotnull=ISNULL(?, '1') = '0'

But when I launch my Tomcat, all I got is this error

ERROR [hibernate.util.JDBCExceptionReporter] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near
''1') = '1') AND (lower(Group_.description) LIKE null OR ISNULL(null,'1') = '1') ' at line 1

ERROR [portal.events.ServicePreAction] com.liferay.portal.SystemException: org.hibernate.exception.SQLGrammarException: could not execute query

In my MySQL manual, I have read that this function just say if the expresion in parenthesis is null or not, but there is nothing about swapping the new value in case it is null.

The query that fails is this one

<sql id="com.liferay.portal.service.persistence.GroupFinder.findByC_N_D">
<![CDATA[
SELECT
Group_.groupId AS groupId, Group_.name AS groupName
FROM
Group_
[$JOIN$]
WHERE
[$WHERE$]
(Group_.companyId = ?) AND
(Group_.className IS NULL OR Group_.className = '') AND
(Group_.classPK IS NULL OR Group_.classPK = '') AND
(lower(Group_.name) LIKE ? OR ? IS NULL) AND
(lower(Group_.description) LIKE ? OR ? IS NULL)
]]>
</sql>

Can anybody help me?!
Thanks in advance
Juan
Juan Fernández
RE: Problems With Liferay 4.2.1 and MySQL 5.0... isnull
October 2, 2008 7:04 AM
Answer

Juan Fernández

LIFERAY STAFF

Rank: Liferay Legend

Posts: 1257

Join Date: October 2, 2008

Recent Posts

Solved!
It is a problem with MySQL version. My version does not allow this isnull version, so I have modified portal.properties with these new functions

custom.sql.function.isnull=ISNULL(?)='1'
custom.sql.function.isnotnull=ISNULL(?)='0'

And it works fine
Greetings
Juan Fernandez