Params error

Discussion of open issues, suggestions and bugs regarding IBDAC (InterBase Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
malmedin
Posts: 22
Joined: Fri 26 Mar 2010 19:52

Params error

Post by malmedin » Tue 28 Jul 2020 12:03

I have a table with column NAME VARCHAR(10).

This code (q is TIBCQuery):

Code: Select all

  q.SQL.Text := 'SELECT * FROM Test WHERE Name = :value';
  q.Params.Items[0].AsString := '12345678901';
  q.Open;
produces this error message:
Dynamic SQL Error SQL error code = -303
arithmetic exception, numeric overflow, or string truncation
string right truncation
expected length 10, actual 11
Note the param size (11).

Executing

Code: Select all

SELECT * FROM Test WHERE Name = '12345678901'
gives no error.

IBDAC version 7.2.4, Delphi 7, Firebird 3

Edit:
It seems this is message from Firebird and this behavior is by design. Can IBDAC do something about it?

oleg0k
Devart Team
Posts: 190
Joined: Wed 11 Mar 2020 08:28

Re: Params error

Post by oleg0k » Wed 29 Jul 2020 08:21

Hello,
This behavior (error) is caused by the Firebird fbclient.dll client library rather than our components. The same error occurs with standard Delphi components.

wbr, Oleg
Devart Team

oleg0k
Devart Team
Posts: 190
Joined: Wed 11 Mar 2020 08:28

Re: Params error

Post by oleg0k » Wed 29 Jul 2020 11:31

Hello,
To resolve the issue, you can try to explicitly specify the parameter size:

Code: Select all

q.Params.Items[0].Size:=10;
IBDAC also has the DescribeParams property:

Code: Select all

q.Options.DescribeParams := True
See our documentation for information on DescribeParams: https://www.devart.com/ibdac/docs/devar ... params.htm

wbr, Oleg
Devart Team

malmedin
Posts: 22
Joined: Fri 26 Mar 2010 19:52

Re: Params error

Post by malmedin » Thu 30 Jul 2020 06:28

Hello oleg0k,

Thank you for answer.

DescribeParams is what I was looking for.

Setting parameter size also works but q.Prepare has to be called before.

oleg0k
Devart Team
Posts: 190
Joined: Wed 11 Mar 2020 08:28

Re: Params error

Post by oleg0k » Wed 05 Aug 2020 08:25

Hello,
Thank you for the interest to our product.
It is good to see that the problem has been solved.
If you have any questions during using our products, please don't hesitate to contact us - and we will try to help you solve them.

wbr, Oleg
Devart Team

malmedin
Posts: 22
Joined: Fri 26 Mar 2010 19:52

Re: Params error

Post by malmedin » Thu 24 Jun 2021 18:11

In 7.4.3 (and 7.4.1) problem can not be solved using this

Code: Select all

q.Options.DescribeParams := True
It works in 7.3.2.

What happened?

ViktorV
Devart Team
Posts: 3168
Joined: Wed 30 Jul 2014 07:16

Re: Params error

Post by ViktorV » Fri 25 Jun 2021 12:39

Hi there,

Unfortunately, we could not reproduce described behavior.
Please let us know the exact error message you receive when using IBDAC 7.4.3 (and 7.4.1) and specify the script for Test table creation.

Regards,
Viktor

malmedin
Posts: 22
Joined: Fri 26 Mar 2010 19:52

Re: Params error

Post by malmedin » Fri 25 Jun 2021 14:27

The problem is described in the first post.
malmedin wrote: Tue 28 Jul 2020 12:03 I have a table with column NAME VARCHAR(10).

This code (q is TIBCQuery):

Code: Select all

  q.SQL.Text := 'SELECT * FROM Test WHERE Name = :value';
  q.Params.Items[0].AsString := '12345678901';
  q.Open;
produces this error message:
Dynamic SQL Error SQL error code = -303
arithmetic exception, numeric overflow, or string truncation
string right truncation
expected length 10, actual 11
Note the param size (11).

Executing

Code: Select all

SELECT * FROM Test WHERE Name = '12345678901'
gives no error.

IBDAC version 7.2.4, Delphi 7, Firebird 3

Edit:
It seems this is message from Firebird and this behavior is by design. Can IBDAC do something about it?
I solved it using

Code: Select all

q.Options.DescribeParams := True
But, after upgrading IBDAC to 7.4.3 the problem is back.

ViktorV
Devart Team
Posts: 3168
Joined: Wed 30 Jul 2014 07:16

Re: Params error

Post by ViktorV » Fri 25 Jun 2021 15:58

Hi there,

As we mentioned in our previous reply, we could not reproduce the issue on our side. Please send us table creation script (only creation script) so we could test everything.

Thanks,
Viktor

malmedin
Posts: 22
Joined: Fri 26 Mar 2010 19:52

Re: Params error

Post by malmedin » Sat 26 Jun 2021 06:23

Table creation script

Code: Select all

CREATE TABLE TEST
(
  "NAME" VARCHAR(10)
);
SQL Query

Code: Select all

q.SQL.Text := 'SELECT * FROM Test WHERE Name = :value';
IBDAC 7.2.4

This code

Code: Select all

q.Options.DescribeParams := false;
q.Params.Items[0].AsString := '12345678901';
q.Open;
produces this error message

Code: Select all

Dynamic SQL Error
SQL error code = -303
arithmetic exception, numeric overflow, or string truncation
string right truncation
expected length 10, actual 10
This code

Code: Select all

q.Options.DescribeParams := true;
q.Params.Items[0].AsString := '12345678901';
q.Open;
produces no error.

IBDAC 7.4.3

This code

Code: Select all

q.Options.DescribeParams := false;
q.Params.Items[0].AsString := '12345678901';
q.Open;
produces this error message

Code: Select all

Dynamic SQL Error
SQL error code = -303
arithmetic exception, numeric overflow, or string truncation
string right truncation
expected length 10, actual 10
This code

Code: Select all

q.Options.DescribeParams := true;
q.Params.Items[0].AsString := '12345678901';
q.Open;
produces this error message (note the difference, actual 11)

Code: Select all

Dynamic SQL Error
SQL error code = -303
arithmetic exception, numeric overflow, or string truncation
string right truncation
expected length 10, actual 11

ViktorV
Devart Team
Posts: 3168
Joined: Wed 30 Jul 2014 07:16

Re: Params error

Post by ViktorV » Tue 29 Jun 2021 14:26

Hi there,

Thanks for the details. We investigated the behavior and got the following result.
We deliberately changed the behavior to prevent a critical bug which may corrupt the data because the parameter value in your example got cut off in IBDAC 7.2.4.
In your use case, you should values equal to or less than 10 for the parameter.

Thanks,
Viktor

Post Reply