Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2011
    Posts
    4

    Unanswered: Query with >= doesn't work as I expect

    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'

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Digitstudios View Post
    A client application that I can't change...
    if you cannot change it, you will have to live with it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2011
    Posts
    4
    Quote Originally Posted by r937 View Post
    if you cannot change it, you will have to live with it
    Are you mocking me?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    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.

  6. #6
    Join Date
    Aug 2011
    Posts
    4
    Quote Originally Posted by shammat View Post
    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!

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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.
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Aug 2011
    Posts
    4
    Quote Originally Posted by r937 View Post
    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
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •