Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Parametrized execute statement fails [CORE3998] #4330

Closed
firebird-automations opened this issue Nov 27, 2012 · 14 comments
Closed

Parametrized execute statement fails [CORE3998] #4330

firebird-automations opened this issue Nov 27, 2012 · 14 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Sergey (solitaire)

Is related to QA523

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

Commits: 8433046 FirebirdSQL/fbt-repository@9bd98c8

@firebird-automations
Copy link
Collaborator Author

Modified by: Sergey (solitaire)

description: 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 next query fails:

=>

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".

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

assignee: Vlad Khorsun [ hvlad ]

@firebird-automations
Copy link
Collaborator Author

Modified by: Sergey (solitaire)

Version: 2.5.1 [ 10333 ]

description: 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".

=>

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

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

It was fixed in trunk (v3) more than 3 years ago but was not backported into v2.5...

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 2.5.3 [ 10461 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

v 2.5.0 also affected

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

Version: 2.5.0 [ 10221 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Sergey (solitaire)

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: Sergey (solitaire)

Ok, now it is clear. Thank you.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to QA523 [ QA523 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pmakowski

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: Done successfully

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment