Menu
▾
▴
[Firebird-net-provider] [FB-Tracker] Created: (DNET-558) FbCommand with CommandTest containing "EXECUTE BLOCK" will not excpet input FbParamaters
|
From: Andrew (JIRA) <tr...@fi...> - 2014-07-03 06:18:53
|
FbCommand with CommandTest containing "EXECUTE BLOCK" will not excpet input FbParamaters
----------------------------------------------------------------------------------------
Key: DNET-558
URL: http://tracker.firebirdsql.org/browse/DNET-558
Project: .NET Data provider
Issue Type: Bug
Components: ADO.NET Provider
Affects Versions: 4.2.0.0
Environment: VS2013 .NET 4.0
Reporter: Andrew
Assignee: Jiri Cincura
When using a FbCommand object to execute a command which contains one or more FbParamters causes a FirebirdSql.Data.Common.IscException.
Below is details of how to reproduce as well as a fix.
Can someone please check that this problem is in fact a real problem (i.e. perhaps there is a way to already do this). IF it is a bug, is the fixed code blow (towards end of this post) OK.
Exception details:
FirebirdSql.Data.Common.IscException occurred
_HResult=-2146233088
HResult=-2146233088
IsTransient=false
Message=Dynamic SQL Error
SQL error code = -104
Token unknown - line 2, column 35
Steps to reproduce:
1) Create a table to hold the example data:
CREATE TABLE ADDRESS (ADDRESSID INTEGER NOT NULL, STREET VARCHAR(50), LAST_MOD DATE);
2) Executing the following via a FbCommand object. Note that work as expected.
EXECUTE BLOCK AS
DECLARE VARIABLE ADDRESSID INT = 8;
DECLARE VARIABLE STREET VARCHAR(50) = 'Fred';
DECLARE VARIABLE LAST_MOD DATE = '2014-07-03 13:37:59';
BEGIN
IF (EXISTS(SELECT ADDRESSID FROM ADDRESS WHERE ADDRESSID = :ADDRESSID)) THEN
UPDATE ADDRESS SET STREET = :STREET, LAST_MOD = :LAST_MOD WHERE ADDRESSID = :ADDRESSID AND LAST_MOD < :LAST_MOD;
ELSE
INSERT INTO ADDRESS(ADDRESSID, STREET, LAST_MOD) VALUES(:ADDRESSID, :STREET, :LAST_MOD);
END;
3) Executing the following via a FbCommand object and use FbParamters to pass in variables.
EXECUTE BLOCK AS
DECLARE VARIABLE ADDRESSID INT = @X;
DECLARE VARIABLE STREET VARCHAR(50) = @Y;
DECLARE VARIABLE LAST_MOD DATE = @Z;
BEGIN
IF (EXISTS(SELECT ADDRESSID FROM ADDRESS WHERE ADDRESSID = :ADDRESSID)) THEN
UPDATE ADDRESS SET STREET = :STREET, LAST_MOD = :LAST_MOD WHERE ADDRESSID = :ADDRESSID AND LAST_MOD < :LAST_MOD;
ELSE
INSERT INTO ADDRESS(ADDRESSID, STREET, LAST_MOD) VALUES(:ADDRESSID, :STREET, :LAST_MOD);
END;
C# Code below is a working example of the bug.
string connectionString = "[Insert valid connection strign here]";
connection = new FbConnection(connectionString);
connection.Open();
string sql = "EXECUTE BLOCK AS\r\n " +
"DECLARE VARIABLE ADDRESSID INT = @X;\r\n" +
"DECLARE VARIABLE STREET VARCHAR(50) = @Y;\r\n" +
"DECLARE VARIABLE LAST_MOD DATE = @Z;\r\n" +
"BEGIN\r\n" +
" IF (EXISTS(SELECT ADDRESSID FROM ADDRESS WHERE ADDRESSID = :ADDRESSID)) THEN\r\n" +
" UPDATE ADDRESS SET STREET = :STREET, LAST_MOD = :LAST_MOD WHERE ADDRESSID = :ADDRESSID AND LAST_MOD < :LAST_MOD;\r\n" +
" ELSE\r\n" +
" INSERT INTO ADDRESS(ADDRESSID, STREET, LAST_MOD) VALUES(:ADDRESSID, :STREET, :LAST_MOD);\r\n" +
"END;";
FbCommand fbCommand = new FbCommand(sql, connection);
FbParameter fbParameter = new FbParameter("@X", FbDbType.Integer);
fbParameter.Value = 5;
fbCommand.Parameters.Add(fbParameter);
fbParameter = new FbParameter("@Y", FbDbType.VarChar);
fbParameter.Size = 50;
fbParameter.Value = "Fred";
fbCommand.Parameters.Add(fbParameter);
fbParameter = new FbParameter("@Z", FbDbType.Date);
fbParameter.Value = DateTime.Now;
fbCommand.Parameters.Add(fbParameter);
fbCommand.ExecuteNonQuery();
4) The below is a fix. Can someone please check that it is OK?
Edit the following source file: $\NETProvider\source\FirebirdSql\Data\FirebirdClient\FbCommand.cs
See if block following comments "Work around for "EXECUTE BLOCK" with FbParameters."
private void Prepare(bool returnsSet)
{
LogCommand();
FbConnectionInternal innerConn = this.connection.InnerConnection;
// Check if we have a valid transaction
if (this.transaction == null)
{
if (innerConn.IsEnlisted)
{
this.transaction = innerConn.ActiveTransaction;
}
else
{
this.implicitTransaction = true;
this.transaction = new FbTransaction(this.connection, this.connection.ConnectionOptions.IsolationLevel);
this.transaction.BeginTransaction();
// Update Statement transaction
if (this.statement != null)
{
this.statement.Transaction = this.transaction.Transaction;
}
}
}
// Check if we have a valid statement handle
if (this.statement == null)
{
this.statement = innerConn.Database.CreateStatement(this.transaction.Transaction);
}
// Prepare the statement if needed
if (!this.statement.IsPrepared)
{
// Close the inner DataReader if needed
this.CloseReader();
// Reformat the SQL statement if needed
string sql = this.commandText;
if (this.commandType == CommandType.StoredProcedure)
{
sql = this.BuildStoredProcedureSql(sql, returnsSet);
}
try
{
//Work around for "EXECUTE BLOCK" with FbParameters.
if (sql.ToUpper().StartsWith("EXECUTE BLOCK") && sql.ToUpper().Contains("DECLARE VARIABLE") && this.parameters.Count > 0)
{
sql = this.SubstituteParameters(sql);
}
// Try to prepare the command
this.statement.Prepare(this.ParseNamedParameters(sql));
}
catch
{
// Release the statement and rethrow the exception
this.statement.Release();
this.statement = null;
throw;
}
// Add this command to the active command list
innerConn.AddPreparedCommand(this);
}
else
{
// Close statement for subsequently executions
this.Close();
}
}
New code...
private string SubstituteParameters(string sql)
{
string sqlCommand = sql;
for (int i = 0; i < this.parameters.Count; i++)
{
FbParameter fbParameter = this.parameters[i];
if (fbParameter.Value == null)
{
sqlCommand = sqlCommand.Replace(fbParameter.ParameterName, "NULL");
}
else
{
switch (fbParameter.FbDbType)
{
case FbDbType.BigInt:
long longValue = (long)fbParameter.Value;
sqlCommand = sqlCommand.Replace(fbParameter.ParameterName, longValue.ToString());
break;
case FbDbType.Boolean:
bool boolValue = (bool)fbParameter.Value;
string boolStringValue = "0";
if (boolValue)
{
boolStringValue = "1";
}
sqlCommand = sqlCommand.Replace(fbParameter.ParameterName, boolStringValue);
break;
case FbDbType.Char:
case FbDbType.Text:
case FbDbType.VarChar:
string strValue = (string)fbParameter.Value;
sqlCommand = sqlCommand.Replace(fbParameter.ParameterName, "'" + strValue.Replace("'", "''") + "'");
break;
case FbDbType.Date:
case FbDbType.Time:
case FbDbType.TimeStamp:
DateTime dateValue = (DateTime)fbParameter.Value;
sqlCommand = sqlCommand.Replace(fbParameter.ParameterName, "'" + dateValue.ToString("yyyy-MM-dd HH:mm:ss") + "'");
break;
case FbDbType.Decimal:
decimal decValue = (decimal)fbParameter.Value;
sqlCommand = sqlCommand.Replace(fbParameter.ParameterName, decValue.ToString());
break;
case FbDbType.Double:
double dblValue = (double)fbParameter.Value;
sqlCommand = sqlCommand.Replace(fbParameter.ParameterName, dblValue.ToString());
break;
case FbDbType.Float:
float floatValue = (float)fbParameter.Value;
sqlCommand = sqlCommand.Replace(fbParameter.ParameterName, floatValue.ToString());
break;
case FbDbType.Guid:
Guid guid = (Guid)fbParameter.Value;
string guidString = "CHAR_TO_UUID('" + guid.ToString() + "')";
sqlCommand = sqlCommand.Replace(fbParameter.ParameterName, guidString.ToString());
break;
case FbDbType.Integer:
int intValue = (int)fbParameter.Value;
sqlCommand = sqlCommand.Replace(fbParameter.ParameterName, intValue.ToString());
break;
case FbDbType.Numeric:
double numValue = (double)fbParameter.Value;
sqlCommand = sqlCommand.Replace(fbParameter.ParameterName, numValue.ToString());
break;
case FbDbType.SmallInt:
short shtValue = (short)fbParameter.Value;
sqlCommand = sqlCommand.Replace(fbParameter.ParameterName, shtValue.ToString());
break;
default:
throw new NotImplementedException("Parameter of " + fbParameter.FbDbType.ToString("G") + " not implemented in 'EXECUTE BLOCK' commands.");
}
}
}
return sqlCommand;
}
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
|
