FbCommand mishandling BLOB field parameters [DNET420] #418

firebird-automations · 2012-03-07T18:34:15Z

Submitted by: Scott Morgan (blumf)

Is related to DNET124

Assume a table of form:
CREATE TABLE PHRASES ( TEXT BLOB SUB_TYPE 1 );

Then attempt the following query:

FbCommand cmd = new FbCommand("SELECT TEXT FROM PHRASES WHERE LOWER(TEXT) LIKE @search_str", conn))
cmd.Parameters.Add("@search_str", FbDbType.Text).Value = "search text"; // <-- needs to be >7 chars long
FbDataReader reader = cmd.ExecuteReader();

An exception is thrown by ExecuteReader:

Unhandled Exception: FirebirdSql.Data.FirebirdClient.FbException: arithmetic exception, numeric overflow, or string truncation
string right truncation ---> FirebirdSql.Data.Common.IscException: arithmetic exception, numeric overflow, or string truncation
string right truncation
at FirebirdSql.Data.Client.Managed.Version10.XdrStream.Write(DbField param) in C:\projects\http://fb.net\source\FirebirdSql\Data\Client\Managed\Version10\XdrStream.cs:line 695
at FirebirdSql.Data.Client.Managed.Version10.XdrStream.Write(Descriptor descriptor) in C:\projects\http://fb.net\source\FirebirdSql\Data\Client\Managed\Version10\XdrStream.cs:line 650
at FirebirdSql.Data.Client.Managed.Version10.GdsStatement.SendExecuteToBuffer() in C:\projects\http://fb.net\source\FirebirdSql\Data\Client\Managed\Version10\GdsStatement.cs:line 596
at FirebirdSql.Data.Client.Managed.Version12.GdsStatement.Execute() in C:\projects\http://fb.net\source\FirebirdSql\Data\Client\Managed\Version12\GdsStatement.cs:line 61
at FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteCommand(CommandBehavior behavior, Boolean returnsSet) in C:\projects\http://fb.net\source\FirebirdSql\Data\FirebirdClient\FbCommand.cs:line 1250
at FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteReader(CommandBehavior behavior) in C:\projects\http://fb.net\source\FirebirdSql\Data\FirebirdClient\FbCommand.cs:line 551

The problem seems to be the lib mistaking the field for a VARCHAR type.

A workaround is to explicitly cast the parameter as a BLOB:

FbCommand cmd = new FbCommand("SELECT TEXT FROM PHRASES WHERE LOWER(TEXT) LIKE CAST(@search_str AS BLOB SUB_TYPE 1)", conn))

firebird-automations · 2012-03-07T18:35:07Z

Modified by: Scott Morgan (blumf)

description: Assume a table of form:
CREATE TABLE PHRASES ( TEXT BLOB SUB_TYPE 1 );

Then attempt the following, looking for a non-existent value:

FbCommand cmd = new FbCommand("SELECT TEXT FROM PHRASES WHERE LOWER(TEXT) LIKE @search_str", conn))
cmd.Parameters.Add("@search_str", FbDbType.Text).Value = "dont exist"; // <-- needs to be >7 chars long
FbDataReader reader = cmd.ExecuteReader();

An exception is thrown by ExecuteReader:

Unhandled Exception: FirebirdSql.Data.FirebirdClient.FbException: arithmetic exception, numeric overflow, or string truncation
string right truncation ---> FirebirdSql.Data.Common.IscException: arithmetic exception, numeric overflow, or string truncation
string right truncation
at FirebirdSql.Data.Client.Managed.Version10.XdrStream.Write(DbField param) in C:\projects\http://fb.net\source\FirebirdSql\Data\Client\Managed\Version10\XdrStream.cs:line 695
at FirebirdSql.Data.Client.Managed.Version10.XdrStream.Write(Descriptor descriptor) in C:\projects\http://fb.net\source\FirebirdSql\Data\Client\Managed\Version10\XdrStream.cs:line 650
at FirebirdSql.Data.Client.Managed.Version10.GdsStatement.SendExecuteToBuffer() in C:\projects\http://fb.net\source\FirebirdSql\Data\Client\Managed\Version10\GdsStatement.cs:line 596
at FirebirdSql.Data.Client.Managed.Version12.GdsStatement.Execute() in C:\projects\http://fb.net\source\FirebirdSql\Data\Client\Managed\Version12\GdsStatement.cs:line 61
at FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteCommand(CommandBehavior behavior, Boolean returnsSet) in C:\projects\http://fb.net\source\FirebirdSql\Data\FirebirdClient\FbCommand.cs:line 1250
at FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteReader(CommandBehavior behavior) in C:\projects\http://fb.net\source\FirebirdSql\Data\FirebirdClient\FbCommand.cs:line 551

The problem seems to be the lib mistaking the field for a VARCHAR type when the resultset is empty.

A workaround is to explicitly cast the parameter as a BLOB:

FbCommand cmd = new FbCommand("SELECT TEXT FROM PHRASES WHERE LOWER(TEXT) LIKE CAST(@search_str" AS BLOB SUB_TYPE 1), conn))

=>

Assume a table of form:
CREATE TABLE PHRASES ( TEXT BLOB SUB_TYPE 1 );

Then attempt the following, looking for a non-existent value:

FbCommand cmd = new FbCommand("SELECT TEXT FROM PHRASES WHERE LOWER(TEXT) LIKE @search_str", conn))
cmd.Parameters.Add("@search_str", FbDbType.Text).Value = "dont exist"; // <-- needs to be >7 chars long
FbDataReader reader = cmd.ExecuteReader();

An exception is thrown by ExecuteReader:

Unhandled Exception: FirebirdSql.Data.FirebirdClient.FbException: arithmetic exception, numeric overflow, or string truncation
string right truncation ---> FirebirdSql.Data.Common.IscException: arithmetic exception, numeric overflow, or string truncation
string right truncation
at FirebirdSql.Data.Client.Managed.Version10.XdrStream.Write(DbField param) in C:\projects\http://fb.net\source\FirebirdSql\Data\Client\Managed\Version10\XdrStream.cs:line 695
at FirebirdSql.Data.Client.Managed.Version10.XdrStream.Write(Descriptor descriptor) in C:\projects\http://fb.net\source\FirebirdSql\Data\Client\Managed\Version10\XdrStream.cs:line 650
at FirebirdSql.Data.Client.Managed.Version10.GdsStatement.SendExecuteToBuffer() in C:\projects\http://fb.net\source\FirebirdSql\Data\Client\Managed\Version10\GdsStatement.cs:line 596
at FirebirdSql.Data.Client.Managed.Version12.GdsStatement.Execute() in C:\projects\http://fb.net\source\FirebirdSql\Data\Client\Managed\Version12\GdsStatement.cs:line 61
at FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteCommand(CommandBehavior behavior, Boolean returnsSet) in C:\projects\http://fb.net\source\FirebirdSql\Data\FirebirdClient\FbCommand.cs:line 1250
at FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteReader(CommandBehavior behavior) in C:\projects\http://fb.net\source\FirebirdSql\Data\FirebirdClient\FbCommand.cs:line 551

The problem seems to be the lib mistaking the field for a VARCHAR type when the resultset is empty.

A workaround is to explicitly cast the parameter as a BLOB:

FbCommand cmd = new FbCommand("SELECT TEXT FROM PHRASES WHERE LOWER(TEXT) LIKE CAST(@search_str AS BLOB SUB_TYPE 1)", conn))

firebird-automations · 2012-03-08T13:35:53Z

Commented by: Scott Morgan (blumf)

Confirmed that it also throws exception when resultset has data

firebird-automations · 2012-03-08T13:35:53Z

Modified by: Scott Morgan (blumf)

description: Assume a table of form:
CREATE TABLE PHRASES ( TEXT BLOB SUB_TYPE 1 );

Then attempt the following, looking for a non-existent value:

FbCommand cmd = new FbCommand("SELECT TEXT FROM PHRASES WHERE LOWER(TEXT) LIKE @search_str", conn))
cmd.Parameters.Add("@search_str", FbDbType.Text).Value = "dont exist"; // <-- needs to be >7 chars long
FbDataReader reader = cmd.ExecuteReader();

An exception is thrown by ExecuteReader:

Unhandled Exception: FirebirdSql.Data.FirebirdClient.FbException: arithmetic exception, numeric overflow, or string truncation
string right truncation ---> FirebirdSql.Data.Common.IscException: arithmetic exception, numeric overflow, or string truncation
string right truncation
at FirebirdSql.Data.Client.Managed.Version10.XdrStream.Write(DbField param) in C:\projects\http://fb.net\source\FirebirdSql\Data\Client\Managed\Version10\XdrStream.cs:line 695
at FirebirdSql.Data.Client.Managed.Version10.XdrStream.Write(Descriptor descriptor) in C:\projects\http://fb.net\source\FirebirdSql\Data\Client\Managed\Version10\XdrStream.cs:line 650
at FirebirdSql.Data.Client.Managed.Version10.GdsStatement.SendExecuteToBuffer() in C:\projects\http://fb.net\source\FirebirdSql\Data\Client\Managed\Version10\GdsStatement.cs:line 596
at FirebirdSql.Data.Client.Managed.Version12.GdsStatement.Execute() in C:\projects\http://fb.net\source\FirebirdSql\Data\Client\Managed\Version12\GdsStatement.cs:line 61
at FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteCommand(CommandBehavior behavior, Boolean returnsSet) in C:\projects\http://fb.net\source\FirebirdSql\Data\FirebirdClient\FbCommand.cs:line 1250
at FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteReader(CommandBehavior behavior) in C:\projects\http://fb.net\source\FirebirdSql\Data\FirebirdClient\FbCommand.cs:line 551

The problem seems to be the lib mistaking the field for a VARCHAR type when the resultset is empty.

A workaround is to explicitly cast the parameter as a BLOB:

FbCommand cmd = new FbCommand("SELECT TEXT FROM PHRASES WHERE LOWER(TEXT) LIKE CAST(@search_str AS BLOB SUB_TYPE 1)", conn))

=>

Assume a table of form:
CREATE TABLE PHRASES ( TEXT BLOB SUB_TYPE 1 );

Then attempt the following query:

FbCommand cmd = new FbCommand("SELECT TEXT FROM PHRASES WHERE LOWER(TEXT) LIKE @search_str", conn))
cmd.Parameters.Add("@search_str", FbDbType.Text).Value = "search text"; // <-- needs to be >7 chars long
FbDataReader reader = cmd.ExecuteReader();

An exception is thrown by ExecuteReader:

Unhandled Exception: FirebirdSql.Data.FirebirdClient.FbException: arithmetic exception, numeric overflow, or string truncation
string right truncation ---> FirebirdSql.Data.Common.IscException: arithmetic exception, numeric overflow, or string truncation
string right truncation
at FirebirdSql.Data.Client.Managed.Version10.XdrStream.Write(DbField param) in C:\projects\http://fb.net\source\FirebirdSql\Data\Client\Managed\Version10\XdrStream.cs:line 695
at FirebirdSql.Data.Client.Managed.Version10.XdrStream.Write(Descriptor descriptor) in C:\projects\http://fb.net\source\FirebirdSql\Data\Client\Managed\Version10\XdrStream.cs:line 650
at FirebirdSql.Data.Client.Managed.Version10.GdsStatement.SendExecuteToBuffer() in C:\projects\http://fb.net\source\FirebirdSql\Data\Client\Managed\Version10\GdsStatement.cs:line 596
at FirebirdSql.Data.Client.Managed.Version12.GdsStatement.Execute() in C:\projects\http://fb.net\source\FirebirdSql\Data\Client\Managed\Version12\GdsStatement.cs:line 61
at FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteCommand(CommandBehavior behavior, Boolean returnsSet) in C:\projects\http://fb.net\source\FirebirdSql\Data\FirebirdClient\FbCommand.cs:line 1250
at FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteReader(CommandBehavior behavior) in C:\projects\http://fb.net\source\FirebirdSql\Data\FirebirdClient\FbCommand.cs:line 551

The problem seems to be the lib mistaking the field for a VARCHAR type.

A workaround is to explicitly cast the parameter as a BLOB:

FbCommand cmd = new FbCommand("SELECT TEXT FROM PHRASES WHERE LOWER(TEXT) LIKE CAST(@search_str AS BLOB SUB_TYPE 1)", conn))

summary: FbCommand mishandling BLOB field parameters on empty resultsets => FbCommand mishandling BLOB field parameters

firebird-automations · 2012-08-01T13:48:17Z

Modified by: @cincuranet

Link: This issue relate to DNET124 [ DNET124 ]

firebird-automations · 2012-08-01T13:49:08Z

Modified by: @cincuranet

Link: This issue relate to DNET124 [ DNET124 ] =>

firebird-automations · 2012-08-01T13:49:18Z

Modified by: @cincuranet

Link: This issue is related to DNET124 [ DNET124 ]

firebird-automations · 2012-08-01T13:49:28Z

Modified by: @cincuranet

status: Open [ 1 ] => In Progress [ 3 ]

firebird-automations · 2012-08-01T13:50:14Z

Commented by: @cincuranet

I'm unable to reproduce this issue with version 2.7.7. Can you recheck?

firebird-automations · 2012-08-01T14:52:26Z

Commented by: Scott Morgan (blumf)

Retested with 2.7.7, still failing. Make sure the search term is over 7 chars long, it works okay if it's below that limit.

i.e.
"123456%" = works
"1234567%" = fails

Exception dump from 2.7.7:

Unhandled Exception: FirebirdSql.Data.FirebirdClient.FbException: arithmetic exception, numeric overflow, or string truncation
string right truncation ---> FirebirdSql.Data.Common.IscException: arithmetic exception, numeric overflow, or string truncation
string right truncation
at FirebirdSql.Data.Client.Managed.Version10.XdrStream.Write(DbField param) in C:\Dev\NETProvider\source\FirebirdSql\Data\Client\Managed\Version10\XdrStream.cs:line 700
at FirebirdSql.Data.Client.Managed.Version10.XdrStream.Write(Descriptor descriptor) in C:\Dev\NETProvider\source\FirebirdSql\Data\Client\Managed\Version10\XdrStream.cs:line 655
at FirebirdSql.Data.Client.Managed.Version10.GdsStatement.SendExecuteToBuffer() in C:\Dev\NETProvider\source\FirebirdSql\Data\Client\Managed\Version10\GdsStatement.cs:line 595
at FirebirdSql.Data.Client.Managed.Version12.GdsStatement.Execute() in C:\Dev\NETProvider\source\FirebirdSql\Data\Client\Managed\Version12\GdsStatement.cs:line 61
at FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteCommand(CommandBehavior behavior, Boolean returnsSet) in C:\Dev\NETProvider\source\FirebirdSql\Data\FirebirdClient\FbCommand.cs:line 1240
at FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteReader(CommandBehavior behavior) in C:\Dev\NETProvider\source\FirebirdSql\Data\FirebirdClient\FbCommand.cs:line 548
--- End of inner exception stack trace ---
at FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteReader(CommandBehavior behavior) in C:\Dev\NETProvider\source\FirebirdSql\Data\FirebirdClient\FbCommand.cs:line 554
at FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteReader() in C:\Dev\NETProvider\source\FirebirdSql\Data\FirebirdClient\FbCommand.cs:line 538
at fb.net_test001.Program.Main(String[] args) in C:\projects\VC10\tests\fb.net_test001\fb.net_test001\Program.cs:line 30

firebird-automations · 2012-08-01T15:12:59Z

Commented by: @cincuranet

This code works in 2.7.7 fine:
using (var conn = new FbConnection("database=localhost:test.fdb;user=sysdba;password=masterkey;character set=utf8"))
{
FbConnection.CreateDatabase(conn.ConnectionString, true);
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "RECREATE TABLE PHRASES ( TEXT BLOB SUB_TYPE 1 );";
cmd.ExecuteNonQuery();
}
using (FbCommand cmd = new FbCommand("SELECT TEXT FROM PHRASES WHERE LOWER(TEXT) LIKE @search_str", conn))
{
cmd.Parameters.Add("@search_str", FbDbType.Text).Value = "123456789123456789"; // <-- needs to be >7 chars long
FbDataReader reader = cmd.ExecuteReader();
}
}

Provide a reproducible test case.

firebird-automations · 2012-08-01T16:23:08Z

Commented by: Scott Morgan (blumf)

This is interesting. I was running FB 2.5.0, getting the error. Upgraded to 2.5.1 and it now works, the field type is correctly identified as DbDataType.Text instead of VarChar

Wonder if it's related to CORE3446

firebird-automations · 2012-08-01T16:39:40Z

Commented by: @cincuranet

Might be. So now it behaves OK?

firebird-automations · 2012-08-01T17:14:43Z

Commented by: Scott Morgan (blumf)

Yeah, for FB 2.5.1 at least. Don't know if you'd want to test against FB 2.1 or earlier though.

firebird-automations · 2012-08-02T09:22:40Z

Modified by: @cincuranet

status: In Progress [ 3 ] => Closed [ 6 ]

resolution: Won't Fix [ 2 ]

firebird-automations closed this as completed Aug 2, 2012

firebird-automations added affect-version: 2.6.5 resolution: not a bug/wontfix priority: major component: ado.net provider type: bug labels Apr 25, 2021

firebird-automations assigned cincuranet Apr 25, 2021

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

FbCommand mishandling BLOB field parameters [DNET420] #418

FbCommand mishandling BLOB field parameters [DNET420] #418

firebird-automations commented Mar 7, 2012

firebird-automations commented Mar 7, 2012

firebird-automations commented Mar 8, 2012

firebird-automations commented Mar 8, 2012

firebird-automations commented Aug 1, 2012

firebird-automations commented Aug 1, 2012

firebird-automations commented Aug 1, 2012

firebird-automations commented Aug 1, 2012

firebird-automations commented Aug 1, 2012

firebird-automations commented Aug 1, 2012

firebird-automations commented Aug 1, 2012

firebird-automations commented Aug 1, 2012

firebird-automations commented Aug 1, 2012

firebird-automations commented Aug 1, 2012

firebird-automations commented Aug 2, 2012

FbCommand mishandling BLOB field parameters [DNET420] #418

FbCommand mishandling BLOB field parameters [DNET420] #418

Comments

firebird-automations commented Mar 7, 2012

firebird-automations commented Mar 7, 2012

firebird-automations commented Mar 8, 2012

firebird-automations commented Mar 8, 2012

firebird-automations commented Aug 1, 2012

firebird-automations commented Aug 1, 2012

firebird-automations commented Aug 1, 2012

firebird-automations commented Aug 1, 2012

firebird-automations commented Aug 1, 2012

firebird-automations commented Aug 1, 2012

firebird-automations commented Aug 1, 2012

firebird-automations commented Aug 1, 2012

firebird-automations commented Aug 1, 2012

firebird-automations commented Aug 1, 2012

firebird-automations commented Aug 2, 2012