#404 DataTruncation numeric range bad behaviour in query statement

[Norbolsa]

The code introduced in the data Truncation behaviour for numeric range doesn't take into account if its a query or not. It allways throws exception instead of warning.

The expected behaviour and the documented one is that in a Numeric DataTruncation when is in a query it should post a warning no to throw an exception.

2) If numeric data is part of a query and data truncated, post warning

The method in AS400JDBConnection testDataTruncation (Line 5943), should do the check in Line 5954
'sqlStatement.isSelect() && ! sqlStatement.isSelectFromInsert()' and if the condition is true post the warning and if it is false throw the exception.

Also, please consider to add to the Data Type Mismatch Exception the index of the parameter (at least in the message) it gives a really usefull information

Thank you

[John Eberhard]

The comment for the method is incorrect. I will update the comment in the code. "Numeric truncation" is always an error because it will lose significant information. When "Numeric truncation" occurs, the value is set to the largest possible value. For example, consider the query -- select * from smi where c1=? -- where c1 is a small int field. If truncation were silently allowed, setting the parameter to 100000 would result in the value 32767 (largest possible smallint) being passed to the query -- which would then result in the wrong rows being returned.

There is a similar problem with character data where when a character string is truncation, a value matching a column in the database would be found. To overcome that problem, the connection property "query replace truncated parameter" was added.

I will also add the parameter number to the data type mismatch.

[Norbolsa]

Also you will need to update documentacion at IBM Knowledge Cente

"Data truncation"   Specifies whether truncation of character data generates attention notices and exceptions. When this property is "true", the following apply:
Writing truncated character data to the database throws an exception
Using truncated character data in a query posts an attention notice.
When this property is "false", writing truncated data to the database or using such data in a query generates no exception or attention notice.

The default value is "true".

This property does not affect numeric data. Writing truncated numeric data to the database always throws an error and using truncated numeric data in a query always posts attention notices.

With this approach we need to evaluate an SQLException when we try to run a query. I mean, in other databases if you try a query and a data truncation is performed you got an empty ResultSet. Which is the desired solution, in our opinion. Indeed, if we use an statement and not a preparedstatement we got an empty resultset without the need of 'query replace truncated parameter'.

If you throw an Exception we need to evaluate if it is a DataTruncation or DataTypeMismatch, simulate an empty Resultset to show to the application a no matches output and continue on.

Even using the 'query replace tuncated parameter' is not valid for numeric range atm. If you post a warning, you can check if there is a datatruncation before executing the stament and decide if you proceed or not. Take note, that I'm talking about queries only.

The approach you made with the parameter imho is not sufficient. As you need to guarantee that the string is not present in any table, column in all of your database. And that is quite difficult if you have free user text inserted by final user. You can use string to lower the probability but it is not perfect.

Could you reconsider it? At least make another property so we have a 'numeric range error' property for queries and other for not queroes?

Thank you

[John Eberhard]

Unfortunately, the root problem is in the base design of the JDBC driver. The driver is designed to send parameter values to the server as the same type as the parameter marker. For a smallint parameter, it means the values -32768 to 32767 can be sent. If the user provides 33000, there is no value to convert to that will make the SQL query behave properly in all cases. So for this conversion, the driver has to throw the exception at the time the parameter is set (not at the time of the query).

The JDBC driver cannot blindly follow the approach of returning an empty result set on parameter truncation or parameter conversion error because the parameter marker could have been used in an inverse expression, for example c1 <> ? .

On possible workaround is to change the query so that the conversion is done by the database instead of by the driver. You would just need to pick the appropriate type for the database to use. For example, consider the following.

java -jar ~/jt400.jar jdbc:as400:MYSYS
create table smalli(c1 smallint)
insert into smalli values(7)
!prepare select * from smalli where c1 <> ?
!setparm 1,33000

*** SQLException caught ***
Statement was 1,33000
SQLState: 07006
Message: Data type mismatch.
Vendor: -99999

!setparm 1,1
!executeQuery
C1
7
!prepare select * from smalli where c1 <> CAST(? AS VARCHAR(32000))
!setparm 1,33000
!executeQuery
C1

*** SQLException caught ***
Statement was executeQuery
SQLState: 22003
Message: [SQL0802] Data conversion or data mapping error.
Vendor: -802

In the last case, the CAST was used to force the JDBC driver to use a large character type and then the database did the conversion. In this case, the database still throws an error for the conversion.

I don't have good ideas on how to fix this from a driver perspective.

From your application perspective, you can check for the data type mismatch when the parameter is set. Then you can set the value to whatever you would like and continue on. If you look at the types, you can set the maximum value for the types -- but there is still going to be some case where you are going to get the wrong answer.

[Norbolsa]

Ok,
We will have to live with this...

From our application perspective we can't capture the exception alone as we are using a jdbc framework (Spring) and we don't have the control when setting the parameters.

As you are going to modify the message to write the parameter index, could it be possible to append that is due to a datatruncation? So we can discrimine if it is a datatruncation or a true data type mismatch

Thank you

[John Eberhard]

It actually is supposed to be a data type mismatch since the value 40000 would not fit into a SMALLINT. You will get the same eror if you try to set a string value to a SMALLINT. So we don't plan on changing that. The change to indicate the parameter number has been added to the code and will be in the next release. For example, here is the new behavior.

~> java -jar jt400.jar jdbc:as400:MYSYSTEM MYUSERID MYPASSWORD

!prepare select cast(? as SMALLINT) from sysibm.sysdummy1
!setparm 1,40000

*** SQLException caught ***
Statement was 1,40000
SQLState: 07006
Message: Data type mismatch. (P#=1)
Vendor: -99999