Hello everybody, I've a question.
A client application that I can't change, sends queries like the following:

SELECT * FROM TABLENAME WHERE TABLENAME.FIELD1>='DUMMY ' AND
TABLENAME.FIELD1<='DUMMY '

filling the string dummy with spaces as the length of the field.
The interested field is a CHAR(9) type
In this case, the DB Manager returns zero records found, but if I try to execute the query manually trimming the spaces like the query below,

SELECT * FROM TABLENAME WHERE TABLENAME.FIELD1>='DUMMY' AND
TABLENAME.FIELD1<='DUMMY'

then the DB Manager returns records working very well..
Is there a way to solve this trouble???

Unfortunately I can't change the source query because I don't have the source code of the application

------------------------------------------------------------------------

Buongiorno a tutti, avrei un quesito.
Un applicativo che non posso modificare effettua alcune query su un db MySql con condizioni del tipo:

SELECT * FROM TABELLA WHERE TABELLA.CAMPO1>='STRINGA ' AND
TABELLA.CAMPO1<='STRINGA '
completando la query con tanti spazi quanti ne servono per raggiungere la dimensione del campo di tipo CHAR

Il database manager non restituisce alcun risultato, ma se provo ad eseguire esternamente la query, togliendo gli spazi

SELECT * FROM TABELLA WHERE TABELLA.CAMPO1>='STRINGA' AND
TABELLA.CAMPO1<='STRINGA'

il Db manager mi torna il risultato atteso.
Posso agire su qualche parametro di configurazione per risolvere il mio problema'

if you cannot change it, you will have to live with it

Are you mocking me?

no, i am telling you the truth

if your application generates a query that doesn't work properly, and you cannot change the application, then you will have to live with it

You could try to change the CHAR(9) column to a VARCHAR(9) column.

But that might break other areas in the application if it relies on getting back 9 characters all the time for that column.

It works fine.

Thank you!

you say it works fine, but you need to check the whole application to make certain that this fixes the problem, but as you don't have access to the source coded thats going to be tough to prove that changing the daytype is sufficient

Either read the application's set up notes to find out if the datatype shoudl have been varchar from day one
OR
contact the application's author to find what they recommend. the problem is that you have fixed on issue, but there is no proof that you have resolved the underlying problem.

Hi, looking better to the user guide of MySql I've tried to use this

SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH'

This directive solves my problem

Have a nice day.
Gianluca