Highlighted
Absent Member.
Absent Member.
543 views

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

Jump to solution

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

                                                                                                                                                                                                                                                                                                                                                                                                                                         

 

Linden Rowland - IT Consultant
Owner/Developer, www.SchoolReportWriter.com
IT Consultant to Tindle Newspaper Group

0 Likes
1 Solution

Accepted Solutions
Highlighted
Absent Member.
Absent Member.

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

Jump to solution

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.

Linden Rowland - IT Consultant
Owner/Developer, www.SchoolReportWriter.com
IT Consultant to Tindle Newspaper Group

View solution in original post

0 Likes
1 Reply
Highlighted
Absent Member.
Absent Member.

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

Jump to solution

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.

Linden Rowland - IT Consultant
Owner/Developer, www.SchoolReportWriter.com
IT Consultant to Tindle Newspaper Group

View solution in original post

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.