Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: stored proc giving different result vs Same Select statement



Permlink Replies: 4 - Last Post: Jul 13, 2017 11:08 AM Last Post By: Jeff Overcash (...
Joe Sansalone

Posts: 158
Registered: 1/15/06
stored proc giving different result vs Same Select statement
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 11, 2017 2:36 PM
SOLVED .. see below.

Hi,

I have a table with a Text BLOB field.
The database is UTF8.
I connect via IBX with UTF8 and SQLDialect 3.

If I use a TIBSQL and simply execute the Select statement to return the Text BLOB (.asString),
I get the correct string.

However, if I create a stored procedure with the exact same select, and use a TIBStoredProc
to retrieve the Text BLOB (.asString), I get strange characters. It's like it's translating to UTF8 twice or something.

I noticed that the stored procedure metadata has the output type listed as
BLOB SUB_TYPE TEXT SEGMENT SIZE 4000 CHARACTER SET UTF8.

Help

Joe

Edited by: Joe Sansalone on Jul 11, 2017 3:22 PM
Joe Sansalone

Posts: 158
Registered: 1/15/06
Re: stored proc giving different result vs Same Select statement
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 11, 2017 3:17 PM   in response to: Joe Sansalone in response to: Joe Sansalone
SOLVED:

With stored proc, get BLOB as Bytes and convert to a string.

xml := TEncoding.UTF8.GetString(FStoredProc.ParamByName('xml').AsBytes;

I guess the TIBStoredProc has less automatic conversion than a TIBSQL ... makes sense.
I'm happy it works!

Joe

Joe Sansalone wrote:
Hi,

I have a table with a Text BLOB field.
The database is UTF8.
I connect via IBX with UTF8 and SQLDialect 3.

If I use a TIBSQL and simply execute the Select statement to return the Text BLOB (.asString),
I get the correct string.

However, if I create a stored procedure with the exact same select, and use a TIBStoredProc
to retrieve the Text BLOB (.asString), I get strange characters. It's like it's translating to UTF8 twice or something.

I noticed that the stored procedure metadata has the output type listed as
BLOB SUB_TYPE TEXT SEGMENT SIZE 4000 CHARACTER SET UTF8.

Help

Joe
Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: stored proc giving different result vs Same Select statement [Edit]
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 11, 2017 8:59 PM   in response to: Joe Sansalone in response to: Joe Sansalone
Joe Sansalone wrote:
SOLVED:

With stored proc, get BLOB as Bytes and convert to a string.

xml := TEncoding.UTF8.GetString(FStoredProc.ParamByName('xml').AsBytes;

I guess the TIBStoredProc has less automatic conversion than a TIBSQL ... makes sense.
I'm happy it works!

Joe

Joe Sansalone wrote:
Hi,

I have a table with a Text BLOB field.
The database is UTF8.
I connect via IBX with UTF8 and SQLDialect 3.

If I use a TIBSQL and simply execute the Select statement to return the Text BLOB (.asString),
I get the correct string.

However, if I create a stored procedure with the exact same select, and use a TIBStoredProc
to retrieve the Text BLOB (.asString), I get strange characters. It's like it's translating to UTF8 twice or something.

I noticed that the stored procedure metadata has the output type listed as
BLOB SUB_TYPE TEXT SEGMENT SIZE 4000 CHARACTER SET UTF8.

Help

Joe

The problem with Blobs in this case is TParam. AsString just returns the
contents as is. TIBStoredProc assigned the contents of blobs as is too. So
what is stored is UTF8.

All other places IBX has a better place to convert from the transmitted type
(utf8) to UTF16 which is why you only see it in the component that you access
the "field" values through a TParam.

You might try (untested as yet) to change TIBStoredProc's
FetchDataIntoOutputParams to

procedure TIBStoredProc.FetchDataIntoOutputParams;
var
i,j : Integer;
begin
   j := 0;
   for i := 0 to FParams.Count - 1 do
     if Params[I].ParamType = ptOutput then
     begin
        if QSelect.Fields[j].SQLType = SQL_BLOB then
        begin
          if QSelect.Fields[j].Data.SqlSubtype = 1 then
            Params[i].Value := QSelect.Fields[j].AsString
          else
            Params[I].Value := QSelect.Fields[j].AsBytes
        end
        else
          Params[I].Value := QSelect.Fields[j].Value;
        Inc(j);
     end;
end;


and add the unit to your project. That should do it. Blobs as input/output to
stored procedures actually is not a supported feature of IB itself.
Particularly input should not be used as you can cause data issues doing insert
updates with an input blob in some situations.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)

Joe Sansalone

Posts: 158
Registered: 1/15/06
Re: stored proc giving different result vs Same Select statement [Edit]
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 12, 2017 5:16 PM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...
Jeff Overcash (TeamB) wrote:
Joe Sansalone wrote:
SOLVED:

With stored proc, get BLOB as Bytes and convert to a string.

xml := TEncoding.UTF8.GetString(FStoredProc.ParamByName('xml').AsBytes;

I guess the TIBStoredProc has less automatic conversion than a TIBSQL ... makes sense.
I'm happy it works!

Joe

Joe Sansalone wrote:
Hi,

I have a table with a Text BLOB field.
The database is UTF8.
I connect via IBX with UTF8 and SQLDialect 3.

If I use a TIBSQL and simply execute the Select statement to return the Text BLOB (.asString),
I get the correct string.

However, if I create a stored procedure with the exact same select, and use a TIBStoredProc
to retrieve the Text BLOB (.asString), I get strange characters. It's like it's translating to UTF8 twice or something.

I noticed that the stored procedure metadata has the output type listed as
BLOB SUB_TYPE TEXT SEGMENT SIZE 4000 CHARACTER SET UTF8.

Help

Joe

The problem with Blobs in this case is TParam. AsString just returns the
contents as is. TIBStoredProc assigned the contents of blobs as is too. So
what is stored is UTF8.

All other places IBX has a better place to convert from the transmitted type
(utf8) to UTF16 which is why you only see it in the component that you access
the "field" values through a TParam.

You might try (untested as yet) to change TIBStoredProc's
FetchDataIntoOutputParams to

procedure TIBStoredProc.FetchDataIntoOutputParams;
var
i,j : Integer;
begin
   j := 0;
   for i := 0 to FParams.Count - 1 do
     if Params[I].ParamType = ptOutput then
     begin
        if QSelect.Fields[j].SQLType = SQL_BLOB then
        begin
          if QSelect.Fields[j].Data.SqlSubtype = 1 then
            Params[i].Value := QSelect.Fields[j].AsString
          else
            Params[I].Value := QSelect.Fields[j].AsBytes
        end
        else
          Params[I].Value := QSelect.Fields[j].Value;
        Inc(j);
     end;
end;


and add the unit to your project. That should do it. Blobs as input/output to
stored procedures actually is not a supported feature of IB itself.
Particularly input should not be used as you can cause data issues doing insert
updates with an input blob in some situations.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)

Thanks for the info Jeff.

Joe

Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: stored proc giving different result vs Same Select statement [Edit]
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 13, 2017 11:08 AM   in response to: Joe Sansalone in response to: Joe Sansalone
TBH I am not sure I like the proposed solution. Here is the problem. When you
access the data through a TField or a TIBSQLVar (what you get in IBSQL) you are
ultimately reading from IBX's buffer. So if you call AsBytes you get exactly
the bytes in the DB but if you call AsString IBX translates the stored string
type to a UTF16 string which is the Delphi native string type.

TParam though buffers the data so before you can access it I have to push the
data into TParam's buffer then you get to access that. This means I have to
decide how to push text blobs. If I translate it first into UTF16 AsString
works as expected, but you are now denied the original data as is from the DB.
IOW AsBytes no longer reflects what is in the DB record but the bytes of the
UTF16 string representation of that data.

As it stands right now you can always get at the data as it is in the DB, but if
you need to work with it as a string you have to do the extra step IBX does
behind the scenes with TField of encoding it to UTF16 first as you found out.

So the way it is now you get both real Data and the ability to manipulate that
data. If I manipulate it first you lose access to the real data.

It might be solved with a property at the IBStoredProc level on translate text
blobs. I'll think more on this over time, but input from the community is
always welcomed even if in the end I don't follow what some think I do listen.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02