« Back

Writing custom SQL queries

Company Blogs December 13, 2011 By Miguel Ángel Pastor Olivar Staff

Sometimes there is need of writing custom SQL queries in order to obtain the data acording to our needs. And, as all of you know, Liferay can run on top of the most popular databases so writing this queries should be written "carefully" to prevent unpleasant surprises in the future. 

The following lines summarize some of the most common tips that we must pay attention:

  • Using the "&" bitwise operator. Many databases, as Oracle, does not support using the keyword "&" as an operator, using a function instead. If you want to write an "bitwise and" operation in your custom SQL queries you will need to type something like BITAND(X,Y) and Liferay will translate your code according the underlaying database.  
  • Achieving casts in text columns. Another common difference between databases are casting text columns. You can solve this problem using the CAST_TEXT(text_column) function. So, every time you need to do a cast with text columns use this function and your cast operation will be working on the different databases.
  • Integer division. Some databases as MySql have an specific way on doing integer divisions. If you want to the integer result you should write A DIV B in MySql or TRUNC(A, B) in Oracle. If you want to make your query agnostic from the database use the function INTEGER_DIV(A, B).
  • Related with the first item, some databases as SyBase does not support using decimals columns in the bitwise operator. Liferay comes to the rescue again and offer you a CAST_LONG(x) function that will translate your sql code acording the current database.

There are some more tips but the previous ones, IMHO,  are the most common in the custom sql queries.

One simple advice: be careful when writing your custom SQL queries ;)

Cheers,

Migue

PD: I need to create a wiki page will all the technical details. When the page is available i will update this entry with the corresponding link.

PD2: I have a few pending blog entries related with Liferay and Cloud Computing. Hope I can write this posts as soon as possible :)

Threaded Replies Author Date
Hey Miguel, I'm looking forward for your Cloud... Thiago Leão Moreira December 14, 2011 8:13 AM
Very useful tips which most of the community... Nagendra Kumar Busam December 15, 2011 2:39 AM
Hello Dear How are you today,i hope you are... anita mulbah mulbah January 5, 2012 8:23 AM

Hey Miguel, I'm looking forward for your Cloud Computing stuff.

Un saludo!
Posted on 12/14/11 8:13 AM.
Very useful tips which most of the community members are not aware of, Thank you emoticon
Posted on 12/15/11 2:39 AM.
Hello Dear
How are you today,i hope you are fine. my name is miss Anita a single girl I saw your profile on(.liferay.com)and became interested in you,please contact me on my prevet ID (anitamulbah@yahoo.in) I have something important to tell you. hope to hear from you soon Your.s friend miss Anita.
Posted on 1/5/12 8:23 AM.