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

DataTruncation with PreparedStatements and LIKE expression on BLOB-field [JDBC132] #173

Closed
firebird-automations opened this issue Jan 15, 2009 · 13 comments

Comments

@firebird-automations
Copy link

Submitted by: Jerker Klang (conio)

Relate to CORE3353
Relate to CORE251
Relate to CORE3559

Attachments:
Main.java
JDBC132Reproduction.java

When using a query with a LIKE expression on a BLOB the matching string can't exceed a certain number of characters (28 in my testcase). According to the release notes for Firebird 2.1 "small" blobs with subtype 1 could not be treated as strings. It does however not mention if the LIKE expression should work but isql allows the query.

The table used was:
CREATE TABLE TMPTABLE (ID INTEGER NOT NULL,
MYBLOB BLOB SUB_TYPE TEXT SEGMENT SIZE 80);

The query was:
select id from tmptable where myblob like ?

When setting a string larger than 28 in this case preparedStatement.setString threw a DataTruncation:

DataTruncation: false/29/28
java.sql.DataTruncation: Data truncation
at org.firebirdsql.jdbc.field.FBWorkaroundStringField.setString(FBWorkaroundStringField.java:97)

@firebird-automations
Copy link
Author

Commented by: Jerker Klang (conio)

Reprodusable test for the issue.

@firebird-automations
Copy link
Author

Modified by: Jerker Klang (conio)

Attachment: Main.java [ 11290 ]

@firebird-automations
Copy link
Author

Modified by: Roman Rokytskyy (rrokytskyy)

Fix Version: Jaybird 2.2 [ 10053 ]

timeestimate: 0 [ 0 ]

timeoriginalestimate: 0 [ 0 ]

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

Original reproduction class did not survive server crash (or has not been restored yet);

New reproduction case added. In my reproduction it fails at a boundary of 31 characters

To reproduce:
setup: enter correct jdbc URL and credentials in source and recompile, create table in the test database
1) Run jdbc132.JDBC132Reproduction
=> Notice that it fails at 31 characters (could be database dependent?)

Maybe the sqllen field has a different meaning for blobs (this is used in org.firebirdsql.jdbc.field.FBWorkaroundStringField to throw the error)

Also interesting:
adding a '%' at the end of the string by replacing pstmt.setString(1, buf.toString()); with pstmt.setString(1, buf.toString() + '%');
makes it fail if buf contains 32 instead of 31 characters (so a total of 33 characters in the LIKE string).

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

Attachment: JDBC132Reproduction.java [ 11584 ]

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

Link: This issue relate to CORE3353 [ CORE3353 ]

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

It looks like this is caused by CORE3353 in Firebird 2.5.0 and earlier describes a blob as a VARCHAR(30) parameter for LIKE. If the parameter value exceeds 30 characters, then Jaybird throws a DataTruncation exception (in FBWorkaroundStringField) as part of a workaround for CORE251 / CORE3559.

In Firebird 2.5.1 a blob is no longer described as VARCHAR(30) so this error no longer occurs (checked with 2.5.1.26339 snapshot build).

A workaround is to explicitly cast the parameter(!) to BLOB SUB_TYPE 1, like so:
select * from BLOB_DATA_TABLE where text_data like CAST(? AS BLOB SUB_TYPE 1)

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

Link: This issue relate to CORE251 [ CORE251 ]

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

Link: This issue relate to CORE3559 [ CORE3559 ]

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

assignee: Roman Rokytskyy [ rrokytskyy ] => Mark Rotteveel [ avalanche1979 ]

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

Closed as 'Won't fix', as this will be fixed in Firebird 2.5.1 and 2.1.5 and there is unfortunately no workaround possible on Jaybird.

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

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

resolution: Won't Fix [ 2 ]

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

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