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-issue-importer opened this issue Nov 27, 2012 · 14 comments
Closed

Parametrized execute statement fails [CORE3998] #4330

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

Comments

@firebird-issue-importer

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 27, 2012

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 27, 2012

Modified by: @hvlad

assignee: Vlad Khorsun [ hvlad ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 27, 2012

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 27, 2012

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 27, 2012

Commented by: @hvlad

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 27, 2012

Modified by: @hvlad

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

resolution: Fixed [ 1 ]

Fix Version: 2.5.3 [ 10461 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 27, 2012

Commented by: @hvlad

v 2.5.0 also affected

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 27, 2012

Modified by: @hvlad

Version: 2.5.0 [ 10221 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 27, 2012

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 27, 2012

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 27, 2012

Commented by: Sergey (solitaire)

Ok, now it is clear. Thank you.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Mar 29, 2014

Modified by: @pcisar

Link: This issue is related to QA523 [ QA523 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 10, 2014

Modified by: @pmakowski

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jul 12, 2015

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
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants