Is there an, EXEC SQL SELECT column size limit? Odd prob....

Environment: Windows 10, VS15, VC2.3, MySQL5.7

Hi all,

After a bit of experimenting there seems to be a 128 byte limit on the sql code for each item 'selected' - is that right? Can it be increased?

e.g. this simple example works and returns SQLCODE zero.

01 my-host-field pic x(500).
Procedure division.
exec sql
    select blah1, concat('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
                        'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
                        'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'), blah3 
    into :blah1, :my-host-field, :blah3 from my-table limit 1
end-exec.

HOWEVER if I add one more 'x' or indeed if I use any select column code > 128 characters e.g. a long case statement, sub-query etc, it still works but I get SQLCODE=1, "[MySQL][ODBC 5.3(w) Driver][mysqld-5.7.12-log]String data, right truncated". This is not to do with the host receiving field but rather the number of characters in the column's sql statement itself, in this case my concat column.

ANY THOUGHTS?

Thanks,

Linden

                                                                                                                                                                                                                                                                                                                                                                                                                                         

 

  • Verified Answer

    Problem turns out to be that a column name is being auto generated from the SQL statement and it's the column name that's too long. I don't actually need a column name but adding one solves the problem! e.g.
    exec sql
        select blah1, concat('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
                                       'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
                                       'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' as MY_COLUMN_NAME, blah3
          into :blah1, :my-host-field, :blah3 from my-table limit 1
    end-exec.