I am working on a legacy SQLBase database and am trying to set up a new project with NHibernate.
A big problem is the format SQLBase expects the bind variables in the SQL to be. The format has to be explicitly
INSERT INTO ... VALUES (:1,:2,:3);
SELECT ... FROM TABLE WHERE ID=:1 AND NAME=:2;
Now NHibernate uses a ":p0,:p1,..." format for bind variables which gives a "invalid program bind variable" SQL exception in SQLBase.
The problem is the "p" before the number, and also that the parameters start at 0 - SQLBase must have parameters starting with 1.
Is there any way I can configure/change NHibernate to drop the "p" in the bind variables, and also start with 1 instead of 0? I was able to change the format of the parameters for INSERTs, UPDATEs and DELETEs with sql-insert, sql-update and sql-delete mappings in my class mappings, but the only way I found to change the SELECT parameters was to write lots of named queries into my mappings.
Obviously this isn't the preferred way. Is there any better way to change how the bind variables are generated? I already made my own Driver (inherited from OleDbDriver) and Dialect (from GenericDialect) and changed a few things to make SQLBase work int the first place.
I also tried setting
private static string ToParameterName(int index)
{
return "p" + index;
}
to return String.Empty, but to no avail. But even if that'd remove the "p" prefix from the parameters I'd still have the problem of them starting at 0 instead of 1.
Any chance to change this behaviour or NHibernate?
EDIT: I now also tried changing a few other parameter functions:
string ISqlParameterFormatter.GetParameterName(int index)
{
int ret = index + 1;
return (NamedPrefix + ret);
}
private void SetCommandParameters(IDbCommand cmd, SqlType[] sqlTypes)
{
for (int i = 0; i < sqlTypes.Length; i++)
{
int ret = i + 1;
string paramName = ret.ToString();
IDbDataParameter dbParam = GenerateParameter(cmd, paramName, sqlTypes[i]);
cmd.Parameters.Add(dbParam);
}
}
protected override void InitializeParameter(IDbDataParameter dbParam, string name, SqlType sqlType)
{
if (sqlType == null)
{
throw new QueryException(String.Format("No type assigned to parameter '{0}'", name));
}
name = name.Remove(0, 1);
dbParam.ParameterName = (Int32.Parse(name) + 1).ToString();
dbParam.DbType = sqlType.DbType;
}
With these changes the SQL parameters are ":1, :2" and so on, but it also broke the bindings - now the parameters aren't appended to the queries at all :(
Edit2: Here's the complete driver and dialect code:
namespace NHSQLBase
{
public class SQLBaseDriver : OleDbDriver, ISqlParameterFormatter
{
public override bool UseNamedPrefixInSql
{
get
{
return true;
}
}
public override bool UseNamedPrefixInParameter
{
get
{
return false;
}
}
public override string NamedPrefix
{
get
{
return ":";
}
}
private static string ToParameterName(int index)
{
return (index + 1).ToString();
}
string ISqlParameterFormatter.GetParameterName(int index)
{
int ret = index + 1;
return (NamedPrefix + ret);
}
private void SetCommandParameters(IDbCommand cmd, SqlType[] sqlTypes)
{
for (int i = 0; i < sqlTypes.Length; i++)
{
int ret = i + 1;
string paramName = ret.ToString();
IDbDataParameter dbParam = GenerateParameter(cmd, paramName, sqlTypes[i]);
cmd.Parameters.Add(dbParam);
}
}
protected override void InitializeParameter(IDbDataParameter dbParam, string name, SqlType sqlType)
{
if (sqlType == null)
{
throw new QueryException(String.Format("No type assigned to parameter '{0}'", name));
}
name = name.Remove(0, 1);
dbParam.ParameterName = (Int32.Parse(name) + 1).ToString();
dbParam.DbType = sqlType.DbType;
}
}
public class SQLBaseDialect : GenericDialect
{
public override string ForUpdateString
{
get
{
return " ";
}
}
public override bool ForUpdateOfColumns
{
get
{
return true;
}
}
public override string GetForUpdateString(string aliases)
{
return " for update of " + aliases;
}
public override bool SupportsOuterJoinForUpdate
{
get
{
return false;
}
}
public override bool SupportsParametersInInsertSelect
{
get
{
return false;
}
}
}
}
