No Question - Just a message...
I have been using Hibernate 3.2.5 with PervasiveSQL for a long time (3 years) with no real problems once I had written a custom PervasivedDalect. I've just been experimenting with Hibernate 4.1.0 and found real problems even just getting a connection using Hibernate.
Problem was due to Pervasive implementation of DatabaseMetaData not supporting supportsGetGeneratedKeys() causing an SQLException in org.hibernate.engine.jdbc.internal.JdbcServicesImpl and the connection being forcibly closed.
From comments in the JdbcServicesImpl code, Steve Ebersole is aware of the potential for problems - hopefully a future release will fix.
I have just got hibernate working though and thought I should share the solution:
I am not using hibernate.cfg.xml as this is a desktop application - but I guess the configuration settings in my HibernateUtil would translate easily.:
configuration.setProperty("hibernate.connection.driver_class", "com.pervasive.jdbc.v2.Driver");
configuration.setProperty("hibernate.connection.url",
"jdbc:pervasive://127.0.0.1:1583/DATABASENAME;encoding=cp1252");
configuration.setProperty("hibernate.dialect", "dialect.PervasiveDialect");
configuration.setProperty("hibernate.connection.username", "");
configuration.setProperty("hibernate.connection.password", "");
// pervasive does not support DatabaseMetaData.supportsGetGeneratedKeys(); so this 'magic' setting needed
configuration.setProperty("hibernate.temp.use_jdbc_metadata_defaults", "false");
package dialect;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Iterator;
import java.util.Map;
import org.hibernate.LockMode;
import org.hibernate.dialect.Dialect;
import org.hibernate.dialect.function.*;
import org.hibernate.type.StandardBasicTypes;
/**
*
* Hibernate Dialect customised to use " " as quoted identifiers; in practice
* this means using the backtick character to surround column names
* e.g. <property name="weight___kg" type="double">
* <column name="`WEIGHT_-_KG`" precision="15" scale="0" not-null="false" />
* </property>
*
* @author David
*/
public class PervasiveDialect extends Dialect {
public static final String CLOSED_QUOTE = "\"";
public static final String QUOTE = "\"";
/**
* Constructs ...
*
*/
public PervasiveDialect() {
// super();
registerColumnType(Types.BIT, "tinyint");
registerColumnType(Types.BIGINT, "numeric(19,0)");
registerColumnType(Types.SMALLINT, "smallint");
registerColumnType(Types.TINYINT, "tinyint");
registerColumnType(Types.INTEGER, "int");
registerColumnType(Types.VARCHAR, "char($l)");
registerColumnType(Types.VARCHAR, "varchar($l)");
registerColumnType(Types.FLOAT, "float");
registerColumnType(Types.DOUBLE, "double precision");
registerColumnType(Types.DATE, "datetime");
registerColumnType(Types.TIME, "datetime");
registerColumnType(Types.TIMESTAMP, "datetime");
registerColumnType(Types.VARBINARY, "varbinary($l)");
registerColumnType(Types.NUMERIC, "numeric($p,$s)");
registerColumnType(Types.BLOB, "image");
registerColumnType(Types.CLOB, "text");
// registerColumnType(1, "char");
registerFunction("ascii", new StandardSQLFunction("ascii", StandardBasicTypes.INTEGER));
registerFunction("char", new StandardSQLFunction("char", StandardBasicTypes.CHARACTER));
registerFunction("len", new StandardSQLFunction("len", StandardBasicTypes.LONG));
registerFunction("lower", new StandardSQLFunction("lower"));
registerFunction("upper", new StandardSQLFunction("upper"));
registerFunction("str", new StandardSQLFunction("str", StandardBasicTypes.STRING));
registerFunction("ltrim", new StandardSQLFunction("ltrim"));
registerFunction("rtrim", new StandardSQLFunction("rtrim"));
registerFunction("reverse", new StandardSQLFunction("reverse"));
registerFunction("space", new StandardSQLFunction("space", StandardBasicTypes.STRING));
registerFunction("user", new NoArgSQLFunction("user", StandardBasicTypes.STRING));
registerFunction("current_timestamp", new NoArgSQLFunction("getdate", StandardBasicTypes.TIMESTAMP));
registerFunction("current_time", new NoArgSQLFunction("getdate", StandardBasicTypes.TIME));
registerFunction("current_date", new NoArgSQLFunction("getdate", StandardBasicTypes.DATE));
registerFunction("getdate", new NoArgSQLFunction("getdate", StandardBasicTypes.TIMESTAMP));
registerFunction("getutcdate", new NoArgSQLFunction("getutcdate", StandardBasicTypes.TIMESTAMP));
registerFunction("day", new StandardSQLFunction("day", StandardBasicTypes.INTEGER));
registerFunction("month", new StandardSQLFunction("month", StandardBasicTypes.INTEGER));
registerFunction("year", new StandardSQLFunction("year", StandardBasicTypes.INTEGER));
registerFunction("datename", new StandardSQLFunction("datename", StandardBasicTypes.STRING));
registerFunction("abs", new StandardSQLFunction("abs"));
registerFunction("sign", new StandardSQLFunction("sign", StandardBasicTypes.INTEGER));
registerFunction("acos", new StandardSQLFunction("acos", StandardBasicTypes.DOUBLE));
registerFunction("asin", new StandardSQLFunction("asin", StandardBasicTypes.DOUBLE));
registerFunction("atan", new StandardSQLFunction("atan", StandardBasicTypes.DOUBLE));
registerFunction("cos", new StandardSQLFunction("cos", StandardBasicTypes.DOUBLE));
registerFunction("cot", new StandardSQLFunction("cot", StandardBasicTypes.DOUBLE));
registerFunction("exp", new StandardSQLFunction("exp", StandardBasicTypes.DOUBLE));
registerFunction("log", new StandardSQLFunction("log", StandardBasicTypes.DOUBLE));
registerFunction("log10", new StandardSQLFunction("log10", StandardBasicTypes.DOUBLE));
registerFunction("sin", new StandardSQLFunction("sin", StandardBasicTypes.DOUBLE));
registerFunction("sqrt", new StandardSQLFunction("sqrt", StandardBasicTypes.DOUBLE));
registerFunction("tan", new StandardSQLFunction("tan", StandardBasicTypes.DOUBLE));
registerFunction("pi", new NoArgSQLFunction("pi", StandardBasicTypes.DOUBLE));
registerFunction("square", new StandardSQLFunction("square"));
registerFunction("rand", new StandardSQLFunction("rand", StandardBasicTypes.FLOAT));
registerFunction("radians", new StandardSQLFunction("radians", StandardBasicTypes.DOUBLE));
registerFunction("degrees", new StandardSQLFunction("degrees", StandardBasicTypes.DOUBLE));
registerFunction("round", new StandardSQLFunction("round"));
registerFunction("ceiling", new StandardSQLFunction("ceiling"));
registerFunction("floor", new StandardSQLFunction("floor"));
registerFunction("isnull", new StandardSQLFunction("isnull"));
registerFunction("concat", new VarArgsSQLFunction(StandardBasicTypes.STRING, "(", "+", ")"));
registerFunction("length", new StandardSQLFunction("len", StandardBasicTypes.INTEGER));
registerFunction("trim", new SQLFunctionTemplate(StandardBasicTypes.STRING, "ltrim(rtrim(?1))"));
registerFunction("locate", new CharIndexFunction());
// getDefaultProperties().setProperty(Environment.STATEMENT_BATCH_SIZE, NO_BATCH);
}
/**
* Method description
*
*
* @return
*/
@Override
public String getAddColumnString() {
return "add";
}
/**
* Method description
*
*
* @return
*/
@Override
public String getNullColumnString() {
return " null";
}
/**
* Method description
*
*
* @return
*/
@Override
public boolean qualifyIndexName() {
return false;
}
/**
* Method description
*
*
* @return
*/
public String getForUpdateString() {
return "";
}
/**
* Method description
*
*
* @return
*/
public boolean supportsIdentityColumns() {
return true;
}
/**
* Method description
*
*
* @return
*/
public String getIdentitySelectString() {
return "select @@identity";
}
/**
* Method description
*
*
* @return
*/
public String getIdentityColumnString() {
return "identity not null"; // starts with 1, implicitly
}
/**
* Method description
*
*
* @return
*/
public boolean supportsInsertSelectIdentity() {
return true;
}
/**
* Method description
*
*
* @param insertSQL
*
* @return
*/
public String appendIdentitySelectToInsert(String insertSQL) {
return insertSQL + "\nselect @@identity";
}
/**
* Method description
*
*
* @param mode
* @param tableName
*
* @return
*/
public String appendLockHint(LockMode mode, String tableName) {
if (mode.greaterThan(LockMode.READ)) {
return tableName + " holdlock";
} else {
return tableName;
}
}
/**
* Method description
*
*
* @param sql
* @param aliasedLockModes
* @param keyColumnNames
*
* @return
*/
@SuppressWarnings("rawtypes")
public String applyLocksToSql(String sql, Map aliasedLockModes, Map keyColumnNames) {
Iterator itr = aliasedLockModes.entrySet().iterator();
StringBuffer buffer = new StringBuffer(sql);
int correction = 0;
while (itr.hasNext()) {
final Map.Entry entry = (Map.Entry) itr.next();
final LockMode lockMode = (LockMode) entry.getValue();
if (lockMode.greaterThan(LockMode.READ)) {
final String alias = (String) entry.getKey();
int start = -1,
end = -1;
if (sql.endsWith(" " + alias)) {
start = (sql.length() - alias.length()) + correction;
end = start + alias.length();
} else {
int position = sql.indexOf(" " + alias + " ");
if (position <= -1) {
position = sql.indexOf(" " + alias + ",");
}
if (position > -1) {
start = position + correction + 1;
end = start + alias.length();
}
}
if (start > -1) {
final String lockHint = appendLockHint(lockMode, alias);
buffer.replace(start, end, lockHint);
correction += (lockHint.length() - alias.length());
}
}
}
return buffer.toString();
}
/**
* Method description
*
*
* @param statement
* @param col
*
* @return
*
* @throws SQLException
*/
public int registerResultSetOutParameter(CallableStatement statement, int col) throws SQLException {
return col; // sql server just returns automatically
}
/**
* Method description
*
*
* @param ps
*
* @return
*
* @throws SQLException
*/
public ResultSet getResultSet(CallableStatement ps) throws SQLException {
boolean isResultSet = ps.execute();
// This assumes you will want to ignore any update counts
while (!isResultSet && (ps.getUpdateCount() != -1)) {
isResultSet = ps.getMoreResults();
}
// You may still have other ResultSets or update counts left to process here
// but you can't do it now or the ResultSet you just got will be closed
return ps.getResultSet();
}
/**
* Method description
*
*
* @return
*/
public boolean supportsCurrentTimestampSelection() {
return true;
}
/**
* Method description
*
*
* @return
*/
public boolean isCurrentTimestampSelectStringCallable() {
return false;
}
/**
* Method description
*
*
* @return
*/
public String getCurrentTimestampSelectString() {
return "select getdate()";
}
/**
* Method description
*
*
* @return
*/
public boolean supportsTemporaryTables() {
return true;
}
/**
* Method description
*
*
* @param baseTableName
*
* @return
*/
public String generateTemporaryTableName(String baseTableName) {
return "#" + baseTableName;
}
/**
* Method description
*
*
* @return
*/
public boolean dropTemporaryTableAfterUse() {
return true; // sql-server, at least needed this dropped after use; strange!
}
// Overridden informational metadata ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
/**
* Method description
*
*
* @return
*/
public boolean supportsEmptyInList() {
return false;
}
/**
* Method description
*
*
* @return
*/
public boolean supportsExistsInSelect() {
return false;
}
/**
* Method description
*
*
* @return
*/
public boolean doesReadCommittedCauseWritersToBlockReaders() {
return true;
}
/**
* Method description
*
*
* @return
*/
public boolean doesRepeatableReadCauseReadersToBlockWriters() {
return true;
}
}
Login
FAQ
Search
