Watch, Follow, &
Connect with Us
Public Report
Report From: Delphi-BCB/AddOn/Interbase components    [ Add a report in this area ]  
Report #:  91494   Status: Closed
TIBStoredProc Fails on Character column data.
Project:  Delphi Build #:  Del XE, IB XE
Version:    15.1 Submitted By:   Patrick Moloney
Report Type:  Basic functionality failure Date Reported:  2/10/2011 7:33:07 PM
Severity:    Serious / Highly visible problem Last Updated: 3/20/2012 2:24:39 AM
Platform:    All platforms Internal Tracking #:   281898
Resolution: Fixed (Resolution Comments) Resolved in Build: : 16.0.4251.43394
Duplicate of:  None
Voting and Rating
Overall Rating: No Ratings Yet
0.00 out of 5
Total Votes: 10
Description
Added by Sysop
<<<<
Please see comments of QC for more detailes.
>>>>

After upgrading to Delphi / IB XE many application forms began to fail with an indicator field on my forms showing blank.
The IB XE database is UTF8.  IB tables have a column as Char(1) which contains a "Y" or "N".  The data is arriving at my form with a trailing space or blank and is failing in my later logic.
The form retrieves data from a TIBStoredProc component that includes an output parameter which is providing the padded value.

Otherwise the form is working, as it has for a long time, including processing Varchar data, which does not have the trailing space. This form also has Add and Update features which send user data to IB using other TIBStoredProc components. These process even the character data satisfactorily. The problem only appears with Character data moving from IB to the user form.

I have added a Trim() method to the component output data which corrects the problem. But the error appears to be in TIBStoredProc.

This error is serious because it is pervasive through many forms and is corrupting of the data.
Steps to Reproduce:
Create a table with a column defined as Char(1).
Put 1 record in the table with a letter in the column.

Create a stored procedure as "Select * From Table"
Include an output Parameter as Char(1) for the column.

Create a Delphi form with Database, Transaction and TIBStoredProc components.

Add a TEdit component and a button to the form.

Execute the TIBStoredProc from the button.

Add code to the form:

TEdit.Text := ParambyName('Parametername').Asstring;


This should put a trailing blank on the database value.
I have modified as: Trim(ParambyName('Parametername').Asstring);
which removes the trailing blank. This fix should also survive the component being properly fixed.

I am actually testing the value of the data for Y/N and populating a checkbox. So my checkbox was wrong.
This kind of thing can be used in many ways.
I have many forms to review for this problem.

This should be corrected.
Workarounds
None
Attachment
DXECharTest.ZIP
Comments

Tomohiro Takahashi at 2/12/2011 12:41:52 AM -
What version of Delphi do you use?
And, could you please attach sample project(including sample database file) to reproduce your issue?

Patrick Moloney at 2/13/2011 9:10:58 AM -
I am trying to upgrade to the latest Delphi and Interbase. Delphi XE sp1 and IB XE (Developer).

I have put together a little example as an IB Table, Stored Proc and a Delphi app with just a Mainform. I will attempt to attach the necessary files.

You will have to create or add the table to a Database. The database characterset should be UTF8.

Patrick Moloney at 2/14/2011 9:59:14 AM -
Files Attached.
I searched around for which Project to use, but didn't really go down the Addons.

Tomohiro Takahashi at 2/14/2011 5:52:14 PM -
>     editETHQ.text           := ParamByName('OP_ETHQ').AsString;
I checked your stored procecure with IBConsole.
Out parameter 'OP_ETHQ' is defined as 'CHAR(1) CHARACTER SET UTF8'.
As you know, CHAR/VARCHAR columns of UTF8 uses 4-byte per 1-character. So, I think you get 'N   ' instead of 'N'.

And, even if I changed DataType of parameter from ftString to ftFixedWideChar and set Size to 1 for TIBStoredProc via ObjectInspector, but no effect...

Patrick Moloney at 2/15/2011 5:29:41 AM -
Both Char and Varchar may be stored as 4 Bytes, but I think if the Output parameter of the stored procedure is defined as Varchar the data comes out properly as 'Y', where the Char comes out as 'Y '.

I thought Unicode had to do with how data is stored, not with data values being changed. Does this mean that a statement like the following no longer works with Unicode?
   If x = 'A';

I also have a procedure that retrieves currency codes as Char 3.  These come out with, I think, 3 trailing blanks, which pushes the data left, out of the visible TEdit on my form, displaying the blanks.
Interestingly, this is a TCombobox that is initially populated with all the currency codes, thru a TQuery rather than a stored procedure - from the same table. The Char-3 codes are displayed as 3 characters with no blanks! My example project may not have a problem if it used a TQuery rather than a Storedprocedure.

Maybe the problem is with the Delphi conversion of Char data to String, where the conversion of Varchar to string is working properly.

Server Response from: ETNACODE01