I've been working with getting NHibernate to work with Pervasive over the past couple of days and finally got something that works. The posted solution appears not to work due to the named prefixes option in the driver. I changed my custom driver settings as follows:
I had to set this to false, because pervasive depends on the order or parameters and doesn't support naming them in select/insert statements. From Pervasive's documentation:
I also chose to implement my own dialect instead of trying to use one of the existing ones that's "close". The main reason was so that I could specify all the propper data types to generate the db schema using something like this:
NHibernate.Tool.hbm2ddl.SchemaExport SE = new NHibernate.Tool.hbm2ddl.SchemaExport ( DbConfig );
SE.SetOutputFile ( "DbSchema.sql" );
SE.Execute ( true, true, false );
Where the DbConfig is an NHibernate Configuration object that's be configured. Here's my custom Dialect class for pervasive:
/// <summary>Specific SQL dialect for a Pervasive v10 or greater db engine</summary>
public class PsqlDialect : NHibernate.Dialect.Dialect
{
/// <summary>Default constructor</summary>
public PsqlDialect ()
{
// string types
RegisterColumnType ( DbType.AnsiStringFixedLength, "CHAR(255)" );
RegisterColumnType ( DbType.AnsiStringFixedLength, 8000, "CHAR($l)" );
RegisterColumnType ( DbType.AnsiStringFixedLength, 2147483647, "LONGVARCHAR" );
RegisterColumnType ( DbType.AnsiString, "VARCHAR(255)" );
RegisterColumnType ( DbType.AnsiString, 8000, "VARCHAR($l)" );
RegisterColumnType ( DbType.AnsiString, 2147483647, "LONGVARCHAR" );
RegisterColumnType ( DbType.StringFixedLength, "CHAR(255)" );
RegisterColumnType ( DbType.StringFixedLength, 8000, "CHAR($l)" );
RegisterColumnType ( DbType.StringFixedLength, 2147483647, "LONGVARCHAR" );
RegisterColumnType ( DbType.String, "VARCHAR(255)" );
RegisterColumnType ( DbType.String, 8000, "VARCHAR($l)" );
RegisterColumnType ( DbType.String, 2147483647, "LONGVARCHAR" );
// numeric data types
RegisterColumnType ( DbType.Boolean, "BIT" );
RegisterColumnType ( DbType.Byte, "UTINYINT" );
RegisterColumnType ( DbType.SByte, "TINYINT" );
RegisterColumnType ( DbType.UInt16, "USMALLINT" );
RegisterColumnType ( DbType.Int16, "SMALLINT" );
RegisterColumnType ( DbType.UInt32, "UINTEGER" );
RegisterColumnType ( DbType.Int32, "INTEGER" );
RegisterColumnType ( DbType.UInt64, "UBIGINT" );
RegisterColumnType ( DbType.Int64, "BIGINT" );
RegisterColumnType ( DbType.Single, "REAL" );
RegisterColumnType ( DbType.Double, "DOUBLE" );
RegisterColumnType ( DbType.Currency, "CURRENCY" );
RegisterColumnType ( DbType.Decimal, "DECIMAL(19,5)" );
RegisterColumnType ( DbType.Decimal, 19, "NUMERIC($p, $s)" );
// date/time data types
RegisterColumnType ( DbType.Date, "DATE" );
RegisterColumnType ( DbType.DateTime, "TIMESTAMP" );
RegisterColumnType ( DbType.Time, "TIME" );
// binary types
RegisterColumnType ( DbType.Binary, "LONGVARBINARY" );
RegisterColumnType ( DbType.Binary, 8000, "BINARY" );
// other data types
RegisterColumnType ( DbType.Guid, "UNIQUEIDENTIFIER" );
DefaultProperties[Environment.ConnectionDriver] = "Db.Driver.PsqlDataDriver";
}
/// <summary>(Ture) Adding support for the identity column</summary>
public override bool SupportsIdentityColumns
{
get { return true; }
}
/// <summary>Get the selection string to retrieve the last generated identity</summary>
public override string IdentitySelectString
{
get { return "SELECT @@IDENTITY"; }
}
/// <summary>Get the column data type string for an identity column</summary>
public override string IdentityColumnString
{
get { return "IDENTITY"; }
}
/// <summary>Overriden to support proper creation of identity columns</summary>
public override bool HasDataTypeInIdentityColumn
{
get { return false; }
}
/// <summary>Grammar portion in an alter table for adding a column</summary>
public override string AddColumnString
{
get { return "add column"; }
}
/// <summary>Index names don't need to be qualified</summary>
public override bool QualifyIndexName
{
get { return false; }
}
/// <summary>TODO: Need to add support for retrieving the database schema from Pervasive</summary>
/// <param name="connection"></param>
/// <returns>Null</returns>
public override IDataBaseSchema GetDataBaseSchema ( DbConnection connection )
{
throw new NotSupportedException ( "Retrieving the database schema is currently unsupported for Pervasive" );
}
/// <summary>The syntax used to drop a primary key constraint from a table.</summary>
/// <param name="constraintName">The name of the primary key to drop (unused by Pervasive).</param>
/// <returns>The SQL string "drop primary key".</returns>
public override string GetDropPrimaryKeyConstraintString ( string constraintName )
{
return " drop primary key";
}
/// <summary>(True) Support for temporary tables exists with some limitations to full tables</summary>
public override bool SupportsTemporaryTables
{
get { return true; }
}
/// <summary>Generates a temporary table by prepending a '#' symbol. This
/// represents a "local" temporary table, and global temporary tables can be
/// created using two precending '##' symbols</summary>
/// <param name="baseTableName">the base name for the table</param>
/// <returns>#(baseTableName)</returns>
public override string GenerateTemporaryTableName ( string baseTableName )
{
return "#" + baseTableName;
}
}
Login
FAQ
Search