Issue Details (XML | Word | Printable)

Key: CORE-3998
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Vlad Khorsun
Reporter: Sergey
Votes: 0
Watchers: 1
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Parametrized execute statement fails

Created: 27/Nov/12 10:26 AM   Updated: 10/Apr/14 01:14 PM
Component/s: Engine
Affects Version/s: 2.5.0, 2.5.1, 2.5.2
Fix Version/s: 2.5.3

Time Tracking:
Not Specified

Environment: Server: Firebird 2.5 WI-V2.5.2.26539
Issue Links:
Relate
 

Planning Status: Unspecified


 Description  « Hide
When excecuting parametrized execute statement, Firebird returns data convertion error.

For example:
CREATE TABLE T (
    ID INTEGER NOT NULL,
    DIR VARCHAR(100) DEFAULT '' NOT NULL,
    NOTE VARCHAR(100) DEFAULT '' NOT NULL
);
commit;
INSERT INTO T (ID, DIR, NOTE) VALUES (1, 'a', 'b');
commit;

-------------

Execution of the next query fails:
execute block
as
  declare variable DIR varchar(100);
  declare variable NOTE varchar(100);
  declare variable ID integer;
begin
  ID = 1;
  DIR = 'a';
  NOTE = 'bbbb';

  execute statement ('
    update T set
      NOTE = :NOTE
    where
      ID = :ID and DIR = :DIR
  ')
  (
    ID := :ID,
    NOTE := :NOTE,
    DIR := :DIR
  );
end

Firebird returns error:
Overflow occurred during data type conversion.
conversion error from string "a".

--------------------
In this case, probably Firebird considers that parameter DIR must be of type Integer, because the next query works:
execute block
as
  declare variable DIR varchar(100);
  declare variable NOTE varchar(100);
  declare variable ID integer;
begin
  ID = 1;
  DIR = '1';
  NOTE = 'bbbb';

  execute statement ('
    update T set
      NOTE = :NOTE
    where
      ID = :ID and DIR = :DIR
  ')
  (
    ID := :ID,
    NOTE := :NOTE,
    DIR := :DIR
  );
end

 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Vlad Khorsun added a comment - 27/Nov/12 10:52 AM
The bug is that in some cases parameters passed in wrong order by EXECUTE STATEMENT.
So far i can think about some cases of UPDATE statement only but can't guarantee it is the only case.

Vlad Khorsun added a comment - 27/Nov/12 11:17 AM
It was fixed in trunk (v3) more than 3 years ago but was not backported into v2.5...

Vlad Khorsun added a comment - 27/Nov/12 11:18 AM
v 2.5.0 also affected

Sergey added a comment - 27/Nov/12 11:18 AM
I'm not sure, because if we fill table T like this:
INSERT INTO T (ID, DIR, NOTE) VALUES (1, '2', 'b');
commit;

and then run the query:
execute block
as
  declare variable DIR varchar(100);
  declare variable NOTE varchar(100);
  declare variable ID integer;
begin
  ID = 1;
  DIR = '2';
  NOTE = 'bbbb';

  execute statement ('
    update T set
      NOTE = :NOTE
    where
      ID = :ID and DIR = :DIR
  ')
  (
    ID := :ID,
    NOTE := :NOTE,
    DIR := :DIR
  );
end

the record with ID = 1 and DIR = '2' will be updated correctly.

Vlad Khorsun added a comment - 27/Nov/12 11:34 AM
Ok, if you want more exact diagnos: the wrong order in not when parameter values passed into prepared statement but when statement's message (in internal format) is converted into blr message.
Here data types of parameters could be in wrong order.
Btw, it works correctly with external data source - it could be used as workaround.

Sergey added a comment - 27/Nov/12 11:56 AM
Ok, now it is clear. Thank you.