Preparing a Sybase Database for Liferay

Issue

• Are there any specific configurations needed when setting up a Sybase database to connect to a Liferay bundle?

Environment

• Sybase 15.7, 16
• Liferay Portal 6.1, 6.2
• DXP 7.0, 7.1

Resolution

• Unlike other database vendors, there are a couple of commands that must be executed on Sybase prior to connecting to Liferay to change some of the database's configuration defaults.
• Once Sybase has been installed and after the database has been created (using a command like this example):
  

  create database lportal on default=1400 with override
  go

  
  it's recommended to then perform the following commands before connecting Liferay to the database:
  

  exec sp_dboption 'lportal', 'allow nulls by default', true
  go
  
  exec sp_dboption 'lportal', 'select into/bulkcopy/pllsort', true
  go

• The first command will change the "NOT NULL" default on some tables that Liferay expects to allow nulls; while the second "select into/bulkcopy/pllsort" command allows operations to be performed that do not keep a complete record of the transaction in the log.

Additional Information

• These configurations are noted in the environment setups found in LPS-41778 and LPS-52571
• Many database vendors allow NULL values by default when not explicitly defined for a column. For example, a MySQL database connected to Liferay will, by default, allow NULL values in the ownerId column of the ResourcePermission table (see this MySQL documentation). However, if not explicitly defined in a Sybase database, the default is NOT NULL (see this Sybase documentation)
• Further documentation and portal-ext.properties suggestions for Sybase and other common databases can be found in this Help Center document

 

Example portal-ext.properties for connecting Sybase 16 to Liferay 7.1:

jdbc.default.driverClassName=com.sybase.jdbc4.jdbc.SybDriver
jdbc.default.url=jdbc:sybase:Tds:localhost:5000/lportal
jdbc.default.username=sa
jdbc.default.password=myPassword

hibernate.dialect=com.liferay.portal.dao.orm.hibernate.SybaseASE157Dialect

custom.sql.function.isnull=CONVERT(VARCHAR,?) IS NULL
custom.sql.function.isnotnull=CONVERT(VARCHAR,?) IS NOT NULL