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

2.1.4 regression : starting with [blob field] using parameters [CORE3408] #3772

Closed
firebird-automations opened this issue Mar 24, 2011 · 10 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Pierre Yager (pierrey)

Is related to CORE3353
Is replaced by CORE3446

Consider a table with a blob text field :

create table BUTTONS (
id INTEGER PRIMARY KEY,
window VARCHAR(50),
macro BLOB SUBTYPE 1
);

This query works : select count(*) from BUTTONS where (WINDOW='window') and (MACRO starting with 'some code')

But this one don't work : select count(*) from BUTTONS where (WINDOW=?) and (MACRO starting with ?)

It fails with different errors :

1)
with TUIBQuery.Create(nil) do
begin
Transaction := T;

SQL\.Text := 'select count\(\*\) from BUTTONS where \(WINDOW=?\) and \(MACRO starting with ?\)';

try
  Params\.AsString\[0\] := WindowName;
  Params\.AsString\[1\] := Macro;
  Open;
  Result := \(Fields\.AsInteger\[0\] \> 0\);
finally
  Close\(etmCommit\);
  Free;
  T\.Free;
end;

end;

Dynamic SQL Error
SQL error code = -303
feature is not supported
BLOB and array data types are not supported for move operation
Incompatible column/host variable data type
GDS Code: 335544569
Error Code: 249

2) If I describe parameters before assignation :

with TUIBQuery.Create(nil) do
begin
Transaction := T;

SQL\.Text := 'select count\(\*\) from BUTTONS where \(WINDOW=?\) and \(MACRO starting with ?\)';

try
  Prepare\(True\); // describe
  Params\.AsString\[0\] := WindowName;
  Params\.AsString\[1\] := Macro;
  Open;
  Result := \(Fields\.AsInteger\[0\] \> 0\);
finally
  Close\(etmCommit\);
  Free;
  T\.Free;
end;

end;

The error is with the UIB components : incorrect typecast here : Params.AsString[1] := Macro;

It seems that the parameter is described as a blob and so cannot be affected as a varchar.

There is a workaround, this code works as expected :

with TUIBQuery.Create(nil) do
begin
Transaction := T;

SQL\.Text := 'select count\(\*\) from BUTTONS where \(WINDOW=?\) and \(MACRO starting with ?\)';

try
  Prepare\(True\); // describe, mandatory
  Params\.AsString\[0\] := WindowName;
  ParamsSetBlob\(1, Macro\); // send param as a blob
  Open;
  Result := \(Fields\.AsInteger\[0\] \> 0\);
finally
  Close\(etmCommit\);
  Free;
  T\.Free;
end;

end;

I think this is a serious regression in Firebird.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

In fact this is a bugfix, see CORE3353. With prior code, you cannot search with arguments longer than 30 characters. Now the argument is correctly described as blob and all you need to do is to pass it as a blob.

Instead, you (or UIB) tries to pass a string there and it fails. Does UIB support blobs? How to you workaround this issue when using MACRO = :param?

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue is related to CORE3353 [ CORE3353 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Pierre Yager (pierrey)

UIB support blobs (and arrays... and blob filters... UIB supports quite everything that is exposed by the Firebird API)

The workaround with named params would be : ParamsSetBlobByName('param', Macro);

The problem here is that every UIB user will have to change all their code to pass params as blobs instead of Varchars.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

I understand. My point was that they already had to do exactly the same for equality comparisons, so the STARTING WITH issue doesn't count as a bug, it just extends the already existing behavior. Although you're correct that it may look like a regression for this particular case.

Perhaps a solution that would make both sides happy would be to implicitly convert the passed string into a described blob inside the server. This looks doable for v2.5, but I foresee problems for v2.1.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

priority: Blocker [ 1 ] => Major [ 3 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue is replaced by CORE3446 [ CORE3446 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

As indicated priorly, this isn't going to be changed in v2.1. As for the later versions, CORE3446 is already implemented.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

resolution: Won't Fix [ 2 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

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

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

No branches or pull requests

2 participants