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
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
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
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
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
Please don't reply with the same nonsense answer to all my posts.. :eye:
it's not a nonsense answer
and if you post the same question in multiple forums, you must be prepared to get the same answer in all of them
have a nice day, and remember, don't shoot the messenger if you don't like the answer
What I mean is that the previous Db Manager (IMB DB2) worked perfectly so probably it's possible to manage the MySql configuration to solve the issue.
What do you think Mr. r937??
here, test for yourself...
CREATE TABLE test_trailing_spaces
( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
, fixed CHAR(9)
, variable VARCHAR(9)
INSERT INTO test_trailing_spaces ( fixed, variable ) VALUES
( 'dummy' , 'dummy' )
,( 'dummy ' , 'dummy ' )
now run each of these 4 queries separately and let me know what you get --
WHERE fixed >= 'DUMMY'
AND fixed <= 'DUMMY'
WHERE fixed >= 'DUMMY '
AND fixed <= 'DUMMY '
WHERE variable >= 'DUMMY'
AND variable <= 'DUMMY'
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.
Look at this please:
Set the SQL mode to strict
that's nonsense, you did not know that when you first replied
anyhow, good job on finding the solution
Right! but in my deep I knew about a solution around the configuration...
Anyway every day there's a new thing to learn (at least)...