Link: This issue is duplicated by DNET178 [ DNET178 ]

Version: 2.5.0 [ 10170 ]

description: Hello,

Let's consider this simple table :

-------------------------------------------
CREATE TABLE DEVISE (
CODE_DEVISE Char(3) NOT NULL COLLATE ES_ES_CI_AI,
NOM_DEVISE Varchar(20) NOT NULL COLLATE ES_ES_CI_AI,
CONSTRAINT PK_DEVISE PRIMARY KEY (CODE_DEVISE)
);

INSERT INTO DEVISE (code_devise, nom_devise) VALUES (chg, "test");
-------------------------------------------

Now if you try to execute the following code in a c#⁠ program

--------------------------------------------
FbCommand fb = conex.CreateCommand();
fb.CommandText = "SELECT * FROM DEVISE WHERE DEVISE.CODE_DEVISE LIKE 'chg%'";

FbDataReader reader = fb.ExecuteReader();
reader.Read();
--------------------------------------------

It works perfectly, (1 row returned )

But now, if you try to execute the same sql statement but by using a parameter in the WHERE Clause instead of a constant value :

--------------------------------------------
FbCommand fb = conex.CreateCommand();

fb.CommandText = "SELECT * FROM DEVISE WHERE DEVISE.CODE_DEVISE LIKE @devise1";
fb.Parameters.Add("@devise1", "chg%");

FbDataReader reader = fb.ExecuteReader();
reader.Read();
--------------------------------------------

fb.ExecuteReader raises a numeric overflow / String truncation exception, most probably because the length of "chg%" (4 chars) exceeds the size of the code_devise column ( CHAR(3) ).
I think that the lenght of string parameters should not be so strictly enforced when they are used in a WHERE clause because it produces unwanted behavior and prevent execution of statements which are actually correct.

Regards

=>

Hello,

the fix for issue 124 caused unexpected results in the result set.

Let's consider this simple table as in issue 124 :

-------------------------------------------
CREATE TABLE DEVISE (
CODE_DEVISE Char(3) NOT NULL COLLATE ES_ES_CI_AI,
NOM_DEVISE Varchar(20) NOT NULL COLLATE ES_ES_CI_AI,
CONSTRAINT PK_DEVISE PRIMARY KEY (CODE_DEVISE)
);

INSERT INTO DEVISE (code_devise, nom_devise) VALUES (chg, "test");
-------------------------------------------

Now if you try to execute the following code in a c#⁠ program

--------------------------------------------
FbCommand fb = conex.CreateCommand();
fb.CommandText = "SELECT * FROM DEVISE WHERE DEVISE.CODE_DEVISE LIKE '%chg'";

FbDataReader reader = fb.ExecuteReader();
reader.Read();
--------------------------------------------

It works perfectly and finds the row which ENDS WITH 'chg', (1 row returned )

But now, if you try to execute the same sql statement but by using a parameter in the WHERE Clause instead of a constant value :

--------------------------------------------
FbCommand fb = conex.CreateCommand();

fb.CommandText = "SELECT * FROM DEVISE WHERE DEVISE.CODE_DEVISE LIKE @devise1";
fb.Parameters.Add("@devise1", "%chg");

FbDataReader reader = fb.ExecuteReader();
reader.Read();
--------------------------------------------

fb.ExecuteReader truncates the last character and the query turns to ENDS WITH 'ch' instead of ENDS WITH 'chg'. Since truncation is performed silently, neither the users of our software, nor the developers understand what is going on until we debugged .NET Provider's source code and found issue 124 and its comments.

I understand that you used MSSQL's approach and use the actual size of the column by querying the database. However getting different results from pure text query and from parametric query which use same input values is awkward.

We informed our users that this is a problem of the Firebird .NET Provider and we are expecting a quick fix, since the software is about to be delivered.

Regards

environment: WIndows XP, firebird engine 2.01, visual studio 2005 => WIndows XP, firebird engine 2.1.1, visual studio 2008

Version: 2.0.1 [ 10101 ] =>

status: Open [ 1 ] => Closed [ 6 ]

resolution: Won't Fix [ 2 ]

Fix Version: 2.5.0 Beta 1 [ 10261 ] =>