Watch, Follow, &
Connect with Us

QualityCentral

Public Report
Report From: InterBase/Server    [ Add a report in this area ]  
Report #:  94455   Status: Resolved
SQL Unicode Char Type Failure
Project:  InterBase Build #:  10.0.2.474
Version:    10.0 Submitted By:   Patrick Moloney
Report Type:  Basic functionality failure Date Reported:  5/29/2011 6:02:00 AM
Severity:    Serious / Highly visible problem Last Updated: 1/18/2012 5:28:10 PM
Platform:    Windows 95/98/NT 4.0 on Intel Internal Tracking #:  
Resolution: Fixed (Resolution Comments) Resolved in Build: : Update 3
Duplicate of:  None
Voting and Rating
Overall Rating: No Ratings Yet
0.00 out of 5
Total Votes: None
Description
An existing stored procedure failed after update to IB XE and conversion to Unicode. No error message or exception was generated, but process logic no longer worked properly.

This problem was traced to a variable declared in the stored procedure and defined as Char(3). This variable is intended to hold a Currency Identifier defined in a table as Varchar(3). A simple Select retrieves the currency of an account into this variable. This conversion worked in previous versions of IB. It also works in the XE version of IB when the Character Set is ANSI. It fails when the database Character Set is UTF8.

In this application case the currency variable is used in following Selects, which failed silently. The problem was noticed in application reports with some missing data on otherwise complete reports.

This client application is not involved in this problem. The stored procedure is activated and populates a table with the processed data. The error occurs when the procedure is run without the client application.

Redefining the declared variable did correct the result, but this has worked broadly as stated above. Finding other similar problems would involve a manual analysis of every procedure. Without correction, this problem will cause data corruption in the database.

Note: This issue may be related to QC#91494, which describes a similar issue with procedure parameters passed to IBX. While that is potentially an IBX issue, this suggests the problem may be deeper within InterBase.
Steps to Reproduce:
1. create a table with  a Varchar(3) column.

2. create a procedure that declares a variable with Char(3) type.

3. use the declared variable in criteria to retrieve a record from a table (possibly the same table - or another).
Workarounds
None
Attachment
None
Comments

Tomohiro Takahashi at 5/29/2011 9:15:13 PM -
What version of InterBase do you use?
Is your issue about IBExpress which comes with Delphi XE?

and, please attach sample project(including sample database file) to reproduce your issue?

Patrick Moloney at 5/30/2011 6:11:21 AM -
Interbase version is the latest at this time, XE SP2, 10.0.2.474

IBExpress - NO.  I said "The client application is not involved in this problem. ... The error occurs when the procedure is run without the client application."

I have exceeded my budget for both debugging InterBase XE and for creating sample InterBase Development databases. And, I don't see any progress on the issues I have already submitted.  I think my description and the Steps I have already provided will be sufficient for this case. However, as time allows, I will try to create a procedure that can be used, perhaps, with the Employee database.

Tomohiro Takahashi at 10/10/2011 10:15:15 PM -
This issue should be fixed in Update3.
http://docs.embarcadero.com/products/interbase/IBXEUpdate3/InterBase_XE3_Readme.html#new00E

If possible, could you please retest your issue with Update3 ?

Patrick Moloney at 1/18/2012 9:42:02 AM -
I have confirmed that this is working properly in Update3.

I was not able to replicate the error and it may have been fully or partially corrected in Update2, which is my current environment. The issue was first noticed in Update1, so the timing would make an Update2 fix possible. This issue was seen in more than a dozen forms, which were changed at the time to correct the issue.

Tomohiro Takahashi at 5/30/2011 3:45:00 PM -
Thanks for the information.
So, if you need quick help, please contact technical support service.
http://support.embarcadero.com/

> as time allows, I will try to create a procedure that can be used, perhaps, with the Employee database.
Ok, if possible, please attach sample project. I wil check it out ASAP.

Server Response from: ETNACODE01

Copyright© 1994 - 2013 Embarcadero Technologies, Inc. All rights reserved.