#544 RexxSQL on PASE broken

[Jack J. Woehr]

Jesse Gorzinski of IBM i Rochester has successfully built Regina on the IBM i PASE environment.
I have build rexxsql which runs, but does not seem to produce correct result sets.

For instance, given the following script:

Call RXFuncAdd 'SqlLoadFuncs','REXXSQL','SqlLoadFuncs'
     Call SqlLoadFuncs
say "result of sqlconnect is" sqlconnect("opensrc",'jwoehr','********','mysystem')
query1 = "select MSGNUM,SENDER,TIMESENT,MSGTEXT from UBLUTEST.XAMPL"
say "The result of query is" sqlcommand("Q1", query1)
say "Q1.MSGNUM.0 is stem variable supposed to contain number of rows"
say "Number of rows is" Q1.MSGNUM.0
/* DO i = 1 to Q1.MSGNUM.0 */
DO i = 1 to 2
say q1.MSGNUM.i q1.SENDER.i q1.TIMESENT.i q1.MSGTEXT.i
end

and a table UBLUTEST.XAMPL which contains two rows:

1   Fred W. Foobar                                      12345678    this is a silly message                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
2   Florian Gheorgiu                                    56789012    Please don't talk about me when I'm gone                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

execution proceeds as follows (one blank row then the names of the stem vars:

$ rexxsql mytest.rex 
result of sqlconnect is 0
The result of query is 1
Q1.MSGNUM.0 is stem variable supposed to contain number of rows
Number of rows is Q1.MSGNUM.0

Q1.MSGNUM.2 Q1.SENDER.2 Q1.TIMESENT.2 Q1.MSGTEXT.2

Any tips, please?

[Mark Hessling]

I suggest you run rexxsql with full debugging flags:
rexxsql -Dvd mytest.rex
This will provide lots of infromation including setting error variables that may hint as the problem.

[Jack J. Woehr]

Hi Mark, long time, no chat. Good to hear from you.
The suspicious stuff is here:

>>>> Call SetDBError(../src/cli-odbc/rexxcli.c,1958,0,0,8011d190,8011c158) from thread 0
>>>> Call SetSQLCA(30200,22005,[IBM][System i Access ODBC Driver]Column 2: CWB0111 - A buffer passed to a system call is too small to hold return data,select MSGNUM,SENDER,TIMESENT,MSGTEXT from UBLUTEST.XAMPL) from thread 0
>>>> Call SetRexxVariable("SQLCA.SQLCODE",13,"30200",5) from thread 0
*DEBUG* Setting variable "SQLCA.SQLCODE" to "30200".
*DEBUG* NOError Setting variable "SQLCA.SQLCODE" to "30200". OK.
>>>> Call SetRexxVariable("SQLCA.SQLERRM",13,"[IBM][System i Access ODBC Driver]Column 2: CWB0111 - A buffer passed to a system call is too small to hold return data",119) from thread 0
*DEBUG* Setting variable "SQLCA.SQLERRM" to "[IBM][System i Access ODBC Driver]Column 2: CWB0111 - A buffer passed to a system call is too small to hold return data".
*DEBUG* NOError Setting variable "SQLCA.SQLERRM" to "[IBM][System i Access ODBC Driver]Column 2: CWB0111 - A buffer passed to a system call is too small to hold return data". OK.
>>>> Call SetRexxVariable("SQLCA.SQLTEXT",13,"select MSGNUM,SENDER,TIMESENT,MSGTEXT from UBLUTEST.XAMPL",57) from thread 0
*DEBUG* Setting variable "SQLCA.SQLTEXT" to "select MSGNUM,SENDER,TIMESENT,MSGTEXT from UBLUTEST.XAMPL".
*DEBUG* NOError Setting variable "SQLCA.SQLTEXT" to "select MSGNUM,SENDER,TIMESENT,MSGTEXT from UBLUTEST.XAMPL". OK.
>>>> Call SetRexxVariable("SQLCA.SQLSTATE",14,"22005",5) from thread 0
*DEBUG* Setting variable "SQLCA.SQLSTATE" to "22005".
*DEBUG* NOError Setting variable "SQLCA.SQLSTATE" to "22005". OK.
>>>> Call SetRexxVariable("SQLCA.INTCODE",13,"-1",2) from thread 0
*DEBUG* Setting variable "SQLCA.INTCODE" to "-1".
*DEBUG* NOError Setting variable "SQLCA.INTCODE" to "-1". OK.
>>>> Call SetRexxVariable("SQLCA.INTERRM",13,"REXX/SQL-1: Database Error",26) from thread 0
*DEBUG* Setting variable "SQLCA.INTERRM" to "REXX/SQL-1: Database Error".
*DEBUG* NOError Setting variable "SQLCA.INTERRM" to "REXX/SQL-1: Database Error". OK.
>>>> Call SetRexxVariable("SQLCA.SQLERRM.1",15,"[IBM][System i Access ODBC Driver]Column 2: CWB0111 - A buffer passed to a system call is too small to hold return data",119) from thread 0
*DEBUG* Setting variable "SQLCA.SQLERRM.1" to "[IBM][System i Access ODBC Driver]Column 2: CWB0111 - A buffer passed to a system call is too small to hold return data".
*DEBUG* NOError Setting variable "SQLCA.SQLERRM.1" to "[IBM][System i Access ODBC Driver]Column 2: CWB0111 - A buffer passed to a system call is too small to hold return data". OK.
>>>> Call SetRexxVariable("SQLCA.SQLSTATE.1",16,"22005",5) from thread 0
*DEBUG* Setting variable "SQLCA.SQLSTATE.1" to "22005".
*DEBUG* NOError Setting variable "SQLCA.SQLSTATE.1" to "22005". OK.
>>>> Call SetRexxVariable("SQLCA.SQLERRM.2",15,"[IBM][System i Access ODBC Driver]Column 3: CWB0111 - A buffer passed to a system call is too small to hold return data",119) from thread 0
*DEBUG* Setting variable "SQLCA.SQLERRM.2" to "[IBM][System i Access ODBC Driver]Column 3: CWB0111 - A buffer passed to a system call is too small to hold return data".
*DEBUG* NOError Setting variable "SQLCA.SQLERRM.2" to "[IBM][System i Access ODBC Driver]Column 3: CWB0111 - A buffer passed to a system call is too small to hold return data". OK.
>>>> Call SetRexxVariable("SQLCA.SQLSTATE.2",16,"22005",5) from thread 0
*DEBUG* Setting variable "SQLCA.SQLSTATE.2" to "22005".
*DEBUG* NOError Setting variable "SQLCA.SQLSTATE.2" to "22005". OK.
>>>> Call SetRexxVariable("SQLCA.SQLERRM.3",15,"[IBM][System i Access ODBC Driver]Column 4: CWB0111 - A buffer passed to a system call is too small to hold return data",119) from thread 0
*DEBUG* Setting variable "SQLCA.SQLERRM.3" to "[IBM][System i Access ODBC Driver]Column 4: CWB0111 - A buffer passed to a system call is too small to hold return data".
*DEBUG* NOError Setting variable "SQLCA.SQLERRM.3" to "[IBM][System i Access ODBC Driver]Column 4: CWB0111 - A buffer passed to a system call is too small to hold return data". OK.

[Mark Hessling]

Hi Jack. Yes been a while. I did some Googling. Have a look at this text I found, hopefully it will work for you.

After a lot of help and interesting analysis from Martin, I will try to
explain what the problem is and the solution in very simple and basic
words for anyone that could have the same problem...

The problem is "half-solved", which means we found how to make it work,
but it is a bug in IBM iSeries driver.

I created a post on IBM forums with some more details about the driver
specific informations :
https://www.ibm.com/developerworks/forums/thread.jspa?threadID=271928&tstart=0

Globally the problem is that if you have a environment variable
configured to UTF8, then the driver is working in UTF8 even if it
shouldn't (don't ask me exactly), but in a word, if you get 30
characters containing special characters, it will return 31, 32 bytes
because of utf8 but will speak about 30 bytes and the driver bugs.
(Maybe Martin may explain better or correct me if I'm wrong)

The "solution" is therefore to change the environnement variable.

Example on my server :

set | grep ^LC_CTYPE

LC_CTYPE=fr_FR.UTF-8

Therefore I did this before launching the script and it worked :
export LC_CTYPE=fr_FR

Hope it will help someone :-)

Denis

The iSeries ODBC driver obviously looks at your environment and decides
that if you have UTF-8 set it will encode data returned from the
database in UTF-8. Denis had UTF-8 set and his data contained some
accented characters. Also, Denis' data was held in char(30) columns so
trailing spaces are returned. When DBD::ODBC queries the driver about
the column it is told it is of size 30 and binds the buffer at size 31
allowing for the trailing NULL. When the data is returned it requires
more than 31 bytes now because UTF-8 encoding has increased the size in
bytes of some chrs. Now you are I know that the iSeries driver meant
that 30 to be 30 characters but ODBC defines it as bytes and even if
ODBC did not say this there are other places where returning UTF-8
encoded data in ODBC is not possible (e.g., SQLGetData provides a buffer
for the returned data and each subsequent call returns more of a columns
data SO LONG as the buffer on the previous call was filled. If you UTF-8
encode the data it may not be possible to put a full chr in it).

In Denis' case we can work around it to a point because he can rtrim the
column to take the spaces off and leave room for the encoding but this
only works because his dataset only contains a few accented chrs and
plenty of trailing spaces. Anyone doing this also needs to know the data
is UTF-8 encoded and decode it. The other alternative is to do what
Denis did and take UTF-8 out of your environment.

[Jack J. Woehr]

Aha!

$ set| grep LC
LC_ALL=EN_US.UTF-8

Will test ...

[Jack J. Woehr]

Hmmm ...

$ set | grep -i utf
$ set | grep -i LC_
LC_ALL=en_US
LC_CTYPE=en_US

But still the same result. I wonder if it's getting some system-wide setting for the ODBC driver itself.

[Jack J. Woehr]

Which is what you said, of course, per Denis

[Jack J. Woehr]

Thanks for the help, we'll test this on a test system.

[Jesse Gorzinski]

Since Jack's data set does not have expanding chars, I'm not sure the root cause is the same.

I'd recommend some next steps.
1) try this query: select TRIM(MSGNUM) as MSG, TRIM(SENDER) as SND,TRIM(TIMESENT) as TSENT,TRIM(MSGTEXT) as TEXT from UBLUTEST.XAMPL
I'm not sure what your column data types are, but this would maybe alleviate boundary conditions if you have a non-varchar string field. Maybe.

In any event, please share the column types in use.

2) Gather a CWBTRC, like so:
CWBTRC /DT:1
[recreate problem]
CWBTRC /DT:0
[send in logs, by default in ~/.iSeriesAccess

[Jack J. Woehr]

Ran the query as show above with the TRIM()s. Trace attached.

[Jesse Gorzinski]

Jack, to be clear, are you still seeing the same behavior withthe TRIM()s?

[Jack J. Woehr]

Yes, that which I uploaded was with the TRIM()s.

[Jesse Gorzinski]

Ahh yes... I see this now in the raw trace data (my analysis tool wasn't showing me the CWB0111, sorry!)

Some interesting tidbits in the repeated (at a glance, seemingly every row, every column) data.

2020-05-11;15:28:05.609;NLS;24608;1;10005;NL CONV:cvt=111 scp=37 tcp=819 siso=1 pad=0 s*=0x18012237A sl=1 t*=0x180111C60 tl=0
2020-05-11;15:28:05.609;ODBC;24608;1;10008;odbcerr.storeError Entry
2020-05-11;15:28:05.609;ODBC;24608;1;10008;odbcerr.finishAndInsertErr Entry
2020-05-11;15:28:05.609;ODBC;24608;1;10008;err: [IBM][System i Access ODBC Driver]Column 1: CWB0111 - A buffer passed to a system call is too small to hold return data dsn: opensrc sys: opensrc.rzkh.de row: 1 col: 1
2020-05-11;15:28:05.609;ODBC;24608;1;10008;odbcerr.finishAndInsertErr Exit
2020-05-11;15:28:05.609;ODBC;24608;1;10008;odbcerr.storeError Exit
2020-05-11;15:28:05.609;ODBC;24608;1;10008;odbcerr.storeError Entry
2020-05-11;15:28:05.609;ODBC;24608;1;10008;odbcerr.finishAndInsertErr Entry
2020-05-11;15:28:05.609;ODBC;24608;1;10008;err: [IBM][System i Access ODBC Driver]String data right truncation. dsn: opensrc sys: opensrc.rzkh.de row: 1 col: 1
2020-05-11;15:28:05.609;ODBC;24608;1;10008;odbcerr.finishAndInsertErr Exit
2020-05-11;15:28:05.609;ODBC;24608;1;10008;odbcerr.storeError Exit
2020-05-11;15:28:05.609;ODBC;24608;1;10008;offset: 0, bytesRead: 0, bytesWritten: 0
2020-05-11;15:28:05.609;ODBC;24608;1;10008; --ConciseType: 1 (SQL_C_CHAR), Octet Length: 1
2020-05-11;15:28:05.609;ODBC;24608;1;10008; --Target:00
2020-05-11;15:28:05.609;ODBC;24608;1;10008;

It looks like we're trying to convert to 819, so UTF8 isn't the root cause.

This line is interesting: s*=0x18012237A sl=1 t*=0x180111C60 tl=0
Those fields are src ptr, src len, tgt ptr, tgt len.

RexxSQL is passing a zero-length target buffer to the ODBC driver.

So, I propose three hypotheses:
1) a flat-out bug in RexxSQL
2) An endianness issue of some sort (I'm thinking unlikely)
3) (my #1 bet) An issue related to legacy 64bit mode and ODBC BUILD_LEGACY_64_BIT_MODE

I'd suggest checking that:
- the build is picking up the sql.h and sqltypes.h from unixODBC-devel (/QOpenSys/pkgs/include/*), and not, for instance the sqlcli-devel ones
- the build does not have BUILD_LEGACY_64_BIT_MODE defined.

Also, one could check the RexxSQL code to see what data type is used for the buffer size.

[Jesse Gorzinski]

Glancing through rexxsql's cli-odbc\rexxcli.c, I see common use of types such as SWORD and SDWORD, which I think is troublesome when dealing with a 64bit ODBC drivers that aren't built with BUILD_LEGACY_64_BIT_MODE

I suspect it's related to this issue: https://sourceforge.net/p/rexxsql/support-requests/28/

[Jesse Gorzinski]

and maybe this one https://sourceforge.net/p/rexxsql/support-requests/31/

[Jack J. Woehr]

I tried adding these two lines to the build file:

export C_INCLUDE_PATH=/QOpenSys/pkgs/include:$C_INCLUDE_PATH
export CPLUS_INCLUDE_PATH=/QOpenSys/pkgs/include:$CPLUS_INCLUDE_PATH

with no change in the outcome.

[Mark Hessling]

Thanks both for looking into this further. I haven't done much with RexxSQL, or the ODBC port for a long time. I presume you are building Rexx/SQL against unixodbc ? I've looked into the unixodbc headers and am not 100% sure how to fix the problem. My best guess at the moment would be to use the SQLINTEGER, SQLLEN etc typedefs (these are new since I wrote the ODBC port) instead of SDWORD, SWORD etc. Does these seem like it would solve the problems not only with using unixodbc, but hopefully also on 64bit windows? I have no access to a big-endian machine to be able to test if the changes work.

[Jesse Gorzinski]

@rexx, that's correct. Jack has built against unixODBC. 64-bit support in ODBC has a tumultuous history, as you may know. We've seen a lot of people run into crashes, missing data (as this issue), or overall unpredictable behavior because of the churn in the data types. There's a pretty good writeup at https://www.easysoft.com/developer/interfaces/odbc/64-bit.html#unixodbc-2-2-12 and
https://www.easysoft.com/developer/interfaces/odbc/64-bit.html#unixodbc-2-2-13

In net... I can't confidently say that using the SQL*types would fix this problem, but in my experience it could. And if it does, it is likely to fix 64bit Windows problems also.

If you are interested in pursuing, we can surely procure an IBM i system and/or help test any changes.

Thanks!

[Mark Hessling]

Thanks Jesse. I've made the changes on 64bit Windows to use SQLLEN and friends and to remove lots of warnings regarding loss of precision; mainly by adding casts :-) Runs fine via 64bit driver to SQLite3. not a perfect test, but the best I've got on that machine. I'm in the process of bringing the changes over to Linux and adding a configure check to see if SQLLEN is typedefed. Once I get that built on 64bit Linux with unixODBC I'll make a source zip file available.
Access to a big-endian sysem would be very helpful, not only for Rexx/SQL, but for all my open source software!

[Jack J. Woehr]

Mark, I can probably arrange access for this purpose. Please pm me and I'll set you up.

[Jack J. Woehr]

Everything good, @rexx? I know we had some confusion over one of your email addrs bouncing.