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’
now run each of these 4 queries separately and let me know what you get –
SELECT *
FROM test_trailing_spaces
WHERE fixed >= 'DUMMY'
AND fixed <= 'DUMMY'
;
SELECT *
FROM test_trailing_spaces
WHERE fixed >= 'DUMMY '
AND fixed <= 'DUMMY '
;
SELECT *
FROM test_trailing_spaces
WHERE variable >= 'DUMMY'
AND variable <= 'DUMMY'
;
SELECT *
FROM test_trailing_spaces
WHERE variable >= 'DUMMY '
AND variable <= 'DUMMY '
;
oh, and by the way, please tell me what you get from this query –
First of all, thanks for your help & time.
Your test works fine but doesn’t replicate the problem that I had so I think that the problem was born during the data migration… anyway changing the column in VARCHAR the problem seems to be solved.
The MySql version is 5.1.52
The reason why I’ve ritten that your reply was a nonsense answer is because there’s a way to solve my problem, adding a directive in the configuration file of MySql db Engine.