sql2o - select query removing trailing spaces from VARCHAR coulmn

Question

using sql2o (https://github.com/aaberg/sql2o)

when selecting a VARCHAR column that has trailing spaces (for example "some value ") the return value is "some value"

when selecting from mysql cli the result contains the trailing spaces

cant find any documentation how to prevent this from happening

table:

CREATE TABLE names
(
    name     VARCHAR(100),
    PRIMARY KEY (experiment_key, metric_name)
);

code example:

 Sql2o sql2o;

        String name = "some name with trailing space    ";
        try (Connection con = sql2o.open()) {
            con.createQuery("INSERT INTO names (name) VALUES(:name)")
                    .addParameter("name", name)
                    .executeUpdate();
        }        


        String nameFromDB; 
        try (Connection con = sql2o.open()) {
            nameFromDB =  con.createQuery("select name from names")
                    .executeAndFetchFirst(String.class);
        }

        if (!nameFromDB.equals(name)){
            throw new RuntimeException("where did the trailing spaces go ??? :( ");
        }

That `INSERT` query doesnt look like it would actually work. It should probably be `INSERT INTO names (name) VALUES(:name)`. — derek.wolfe, May 09 '19 at 19:31
What makes you think it's sql2o removing the trailing spaces, and not the database you're connected to? — Andreas, May 09 '19 at 19:43
from mysql cli running the query the result has trailing spaces — Nimrod007, May 09 '19 at 19:47

score 1 · Accepted Answer · answered May 09 '19 at 20:05

1

Think I found your answer in Sql2o.

I believe by using String.class, it is using the StringConverter class to convert your query output into a string. At the very bottom of the StringConverter class is this line:

return val.toString().trim();

Found here

answered May 09 '19 at 20:05

derek.wolfe

1,086
6
11

do you think i can add a custom convertor that will replace this ? why will they trim in the end ? – Nimrod007 May 09 '19 at 20:18
ended up Implementing a custom converters and removing the trim – Nimrod007 May 09 '19 at 21:58

sql2o - select query removing trailing spaces from VARCHAR coulmn

1 Answers1