Watch, Follow, &
Connect with Us
Public Report
Report From: InterBase/Interfaces/SQL/Stored Procedures    [ Add a report in this area ]  
Report #:  102982   Status: Reported
Invalid Data Added and Stored in Table Key Column
Project:  InterBase Build #:
Version:    10.0 Submitted By:   Patrick Moloney
Report Type:  Basic functionality failure Date Reported:  1/29/2012 7:24:18 AM
Severity:    Commonly encountered problem Last Updated: 2/8/2012 4:58:30 PM
Platform:    Windows 95/98/NT 4.0 on Intel Internal Tracking #:  
Resolution: None (Resolution Comments) Resolved in Build: : None
Duplicate of:  None
Voting and Rating
Overall Rating: (1 Total Rating)
5.00 out of 5
Total Votes: 5
This issue allowed a 7 character data item to be stored in a table Varchar(3) column, which is the primary key for the table. Further normal processing fails to access the record.

This problem was noted in an application on Windows 7, Delphi XE, Interbase Express, and Interbase XE. The table is a simple table with just a few columns and no real processing. The Delphi form is simple and similar to many other forms in the application - which is not new. The application was migrated to XE a year ago and is now defined as Unicode - UTF8.  

This problem has never been noted before, but most features also include a data check in the form and/or in the stored procedure, (which this feature does not), to catch oversize data entries so as to improve the user interface.

I am able to open the table using Database Workbench and see 7 characters stored in the key column, which is defined as Varchar(3). This does not display using IBConsole, which displays only the first 3 characters.

The serious problem is that I can not retrieve or update the record by accessing it with the first 3 characters. IB says the record does not exist.

i'm fairly certain that previous versions of IB would give an exception when a large data value was provided for a smaller column. I don't understand how it is storing this information. But it is being inconsistent in its behavior by adding it but refusing to retrieve it.

I suspect this is related to Unicode changes in recent versions. My large application is otherwise working properly, although I check most data sizes, especially on key fields. But lacking any specifically coded checking, this is not the behavior I would expect.
Steps to Reproduce:
In this case I ran the application and filled the form to Add a new record to this very inactive table. I entered a 7 character Identifier Code (All letters). The form code passes my entry to an IBX Stored Procedure component parameter as:

ParamByName('IP_CODE').AsString := inpKeyfield.text;

The form then executes the stored procedure which performs a simple Insert to the table. The "Code" column in the table is Varchar(3), and is the primary key.

When I display all records (only 3) in my application (or in IBConsole), it appears that the record was added with only the first three characters (albeit with no warning), as I would almost expect. However, I am unable to retreive or update the record accessing it by those 3 characters.

Fortunately, if I open the database and table with Database Workbench (similar to IBConsole), is displays the record with all 7 characters in the column - which is defined as Varchar(3).


Tomohiro Takahashi at 1/30/2012 4:22:56 AM -
Could you please attach simple sample project with simple database file to understand/reproduce your issue?

Patrick Moloney at 2/8/2012 1:12:54 PM -
I've investigated further, created a sample database and have attached it.

I have tested the problem on other forms and tables in my application and was able to recreate the problem, everywhere I didn't have edits that prevented entering too many characters.

I also tested this on an ansi version of the database on this same machine and was unable to recreate the problem. So this seems to be yet another Unicode related error - 5 or 6 now - since updating the application.
On this database I get an error when trying to add too many characters:
"Trying to store a string of length 18 into a field that can only contain 15".
I never get this error on the Unicode version of the database.

The problem lies in Interbase and can be recreated there, so I have not included any part of the Delphi application. The attachment contains 1 table and 2 stored procedures. The table is "States" and contains a State_Code and State_Name. Using IBConsole you can run the procedures to Add a state or Get a state. There should be 1 good state (NY) and 1 bad state (njnjnjnj) record. State_code is varchar(2).

IBConsole displays the bad record code as "nj", but will not retrieve it that way. It actually has 8 characters in the State_code. These are stored and display in another tool, Database Workbench - so they are actually stored.

I limited my testing to a primary key column. I have no reason to believe the problem is limiited to that situation. Most likely, any varchar column can be incorrectly written.

This new error under Unicode is corrupting to the database. A bad record is added to the database which the user can't remove, and the desired record is not available. Other non-key columns are likely affected as well.

Anthony Gautier at 4/24/2015 10:13:16 AM -
I've seen this issue as well, and it seems to be happening for all VARCHAR columns. I consider it to be a very serious problem because the database is storing more data than it's supposed to store, not warning me about it, and not showing it to me when I try to access it through a query via something like IBX. It can effectively break a database-driven application if greater than N characters are entered in the wrong place. It's going to cause significant changes to my application.

The problem seems to be that Interbase is allowing storage of (N * bytes per character) bytes worth of characters in a VARCHAR(N) field, rather than just N characters as expected. It then only shows up to N characters in IBConsole and IBX queries. It also only returns results when including the full field data (not just what is shown) in something like a where clause conditional expression.

I wrote a forum post on this and am awaiting a response (link below). Please fix this ASAP. Thanks.

Server Response from: ETNACODE01