[
https://issues.apache.org/jira/browse/DBUTILS-153?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17938653#comment-17938653
]
Jarrad Waterloo commented on DBUTILS-153:
-----------------------------------------
"Something like this should not be done by default if at all."
It can be a configuration setting bool or a configurable callback where
[padded] actual value gets returned from a function where parameter metadata
and provided value is passed in. Something like this is being done by default
on the database server side just not on the client side of things.
"What level is your requirement coming from?
The application, the JDBC driver, or the database?"
The application, programmers shouldn't have to liter their code with padding
because the database driver isn't doing it when the prepared statement already
has the size information as part of its parameter metadata. Data direct driver
does, oracle driver doesn't.
Are you saying you want the database to store values like "NAME. " instead of
"NAME"?
~All the databases already does this on store. If a field is CHAR 15 or NCHAR
15 and if you passed in 5 characters, the other 10 will automatically be padded
by the database. Whether DbUtils does the padding before hand or the database
driver, the end result is the same. It is on SELECTing is where the problem is.
For non prepared statements,
SELECT * FROM WHATEVER ON CHARTODO = 'a'
SELECT * FROM WHATEVER ON CHARTODO = 'a '
SELECT * FROM WHATEVER ON CHARTODO = 'a '
SELECT * FROM WHATEVER ON CHARTODO = 'a '
SELECT * FROM WHATEVER ON CHARTODO = 'a '
SELECT * FROM WHATEVER ON CHARTODO = 'a '
SELECT * FROM WHATEVER ON CHARTODO = 'a '
SELECT * FROM WHATEVER ON CHARTODO = 'a '
SELECT * FROM WHATEVER ON CHARTODO = 'a '
SELECT * FROM WHATEVER ON CHARTODO = 'a '
SELECT * FROM WHATEVER ON CHARTODO = 'a '
SELECT * FROM WHATEVER ON CHARTODO = 'a '
SELECT * FROM WHATEVER ON CHARTODO = 'a '
SELECT * FROM WHATEVER ON CHARTODO = 'a '
SELECT * FROM WHATEVER ON CHARTODO = 'a '
all produces the same results
This is because the database is doing more processing on its side and it has
the CHARTODO field metadata.
However the similar query with prepared statement
SELECT * FROM WHATEVER ON CHARTODO = ?
only succeed when ? is 'a ', all others return no results
Not only is this inconsistent, it requires programmers to litter code with
padding or worse litter the SQL with TRIM and their databases with unique
indexes on the trimmed value. None of which would be necessary if the field
could be automatically padded via a configured parameter setter object or a
boolean setting.
> AutoPad PreparedStatement parameters
> ------------------------------------
>
> Key: DBUTILS-153
> URL: https://issues.apache.org/jira/browse/DBUTILS-153
> Project: Commons DbUtils
> Issue Type: Bug
> Reporter: Jarrad Waterloo
> Priority: Major
>
> Revise the following method
> void AbstractQueryRunner.fillStatement(final PreparedStatement stmt, final
> ParameterMetaData pmd, final Object... params)
>
> to either auto pad PreparedStatement parameters when the types are CHAR and
> NCHAR OR provide the ability for the AbstractQueryRunner to be configured to
> do such.
>
> He is the code which is technically being asked for.
>
> if(pmd.getParameterType(ordinal) == java.sql.Types.CHAR ||
> pmd.getParameterType(ordinal) == java.sql.Types.NCHAR)
> {
> final String paddedValue = StringUtils.rightPad(value,
> pmd.getPrecision(ordinal), ' ');
> ps.setObject(1, paddedValue);
>
> This is a reasonable thing to do at this level as database drivers may not be
> doing it at their level such as Oracle's database drivers.
>
> Padding is the default behavior of most SQL databases including Oracle,
> MySQL, Microsoft SQL Server and many others.
>
> The problem is when doing a SQL query with an unpadded value the SQL works
> but the same query and value on a prepared statement fails to return results.
> The ParameterMetaData already has the parameter size, it just needs to be
> used.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
