[IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0104

[bwmarrin]

I'm trying to use this library with unixodbc on Debian 9 (testing) to connect to an IBM iSeries database.

I've configured unixodbc and the IBM i Series Access driver and I am able to query the database without error using the isql command line tool. But when I try to do this using simple test program in Go, I'm getting the following error.

[IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0104 - Error message text unavailable.  Message can not be translated successfully.

I noticed someone else had this problem, long ago, here : https://stackoverflow.com/questions/25715581/go-odbc-to-an-iseries-sql0104-message-can-not-be-translated-successfully

Any ideas on what I can look at to help narrow down this problem?

[alexbrainman]

Luckily for me I have access to some old computer and Windows XP with old ODBC driver. This program:

package main

import (
    "database/sql"
    "fmt"
    "log"

    _ "github.com/alexbrainman/odbc"
)

func main() {
    db, err := sql.Open("odbc", `
        Driver={Client Access ODBC Driver (32-bit)};
        System=my_old_computer;
        Uid=myuserid;
        Pwd=mypassword`)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    rows, err := db.Query("SELECT 1 FROM sysibm.sysdummy1")
    if err != nil {
        log.Fatal(err)
    }

    var number int
    for rows.Next() {
        rows.Scan(&number)
    }
    fmt.Println(number)
}

prints 1 on my Windows XP.

I do not have any good suggestions about your problem. Maybe you could turn some logging inside your driver.

Also maybe you could rewrite the program above into C. The program above fails when it calls SQLPrepare ODBC API. Maybe you could try and do the same from C and see what happens.

My gut feeling is that there is some string encoding mismatch somewhere. Go driver uses utf16 strings (as per ODBC spec). Is this acceptable for your ODBC driver / database? Do you need some global setting changed to support that? But I was wrong before many times ...

Alex

[bwmarrin]

Interesting :)

So, just to check. You're also connecting to an IBM i Series AS/400 database? That's neat that you have that handy on a Windows XP computer.. :)

I'm testing this on Linux (Debian 9) with unixodbc instead of Windows. I can run the above SQL you're using with the command line tool isql that comes with the unixodbc interface and it works great. But, I still get the above error when I run it through Go.

So, I enabled some debugging for unixodbc, and the only major difference I see in the log is when using the isql tool it sets the encoding to UTF8 and when running my test Go program the encoding is set to ANSI_X3.4-1968. Does that help any? Is there a setting or place I can configure the encoding used?

I have no idea how to rewrite anything in C :) I'd be lucky to make a hello world program there.

[bwmarrin]

Not sure if this helps, at all.. But just throwing it out here. I was just toying with the Julia Language and installed the ODBC driver for it (https://github.com/JuliaDB/ODBC.jl) and was able to connect and execute the SQL statement in your example without any errors using the exact same unixodbc DSN that isn't working in my Go app.

So, that's awesome on one hand to have it working there. But, that doesn't help me solve the Go issue much :(

[alexbrainman]

I have no bright ideas. If we are to advance this, I need to be able to reproduce this myself. What are the instructions? I need all details of your system and how you installed the software, so I can reproduce it here.

Thank you

Alex

[bwmarrin]

The system I am connecting to is a V7R1 IBM i Series Power7 AS/400 that has what they call the UDB (Universal Database). It's similar to IBM DB2 but there's a lot of differences.

The system where I'm creating the Go app is:

• Debian v9 64bit (current testing nightly from a couple days ago)
• unixodbc v2.3.4
• IBM i Access Client Solutions Linux AP_LCD8_2012_07 (latest version) ODBC drivers
• Google Go v1.8.3 (installed from tarball from Go download page)

NOTE: In the above, I installed only the 64bit ODBC driver

unixodbc config files

odbcinst.ini file

[ODBC]
Trace = yes
TraceFile = /tmp/odbc.log

[IBM i Access ODBC Driver]
Description=IBM i Access for Linux ODBC Driver
Driver=/opt/ibm/iaccess/lib64/libcwbodbc.so
Setup=/opt/ibm/iaccess/lib64/libcwbodbcs.so
Driver64=/opt/ibm/iaccess/lib64/libcwbodbc.so
Setup64=/opt/ibm/iaccess/lib64/libcwbodbcs.so
Threading=2
DontDLClose=1
UsageCount=1

odbc.ini file

[AS400]
Description = System i Access ODBC
Driver = IBM i Access ODBC Driver
System = IP.ADD.RE.SS
UserID = USERNAME
Password = PASSWORD
ForceTranslation = 1

With the above configuration, I am able to use the isql command line tool to connect to the DSN and issue a SQL statement. For a double-check I am also able to do this in a Julia language test program.

isql -v AS400
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> SELECT 1 FROM sysibm.sysdummy1
+------------+
| 00001      |
+------------+
| 1          |
+------------+
SQLRowCount returns -1
1 rows fetched
SQL> quit

For extra reference, here's an example from the Julia REPL

julia> using ODBC
julia> as400 = ODBC.DSN("AS400","USER", "PASS")
julia> ODBC.query(as400,"SELECT 1 FROM sysibm.sysdummy1")
1×1 DataFrames.DataFrame
│ Row │ 00001 │
├─────┼───────┤
│ 1   │ 1     │

Here is the Go test program I'm using.

package main

import (
        "database/sql"
        "log"

        _ "github.com/alexbrainman/odbc"
)

func main() {
        db, err := sql.Open("odbc", `DSN=AS400`)
        if err != nil {
                log.Fatal(err)
        }

        rows, err := db.Query("SELECT 1 FROM sysibm.sysdummy1")
        if err != nil {
                log.Fatal(err)
        }

        rows.Close()
        db.Close()
}

And this is what happens when I run it.

 go run -v main.go
command-line-arguments
2017/06/14 08:40:57 SQLPrepare: {42000} [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0104 - Error message text unavailable.  Message can not be translated successfully.
exit status 1

As you saw above, I've included a debug trace setting for unixodbc that outputs a log to /tmp/odbc.log. The only standout difference I see is the character set.

When using the isql tool I see: UNICODE Using encoding ASCII 'UTF-8' and UNICODE 'UCS-2LE'

When using Julia I see: UNICODE Using encoding ASCII 'UTF-8' and UNICODE 'UCS-2LE'

When using Go I see: UNICODE Using encoding ASCII 'ANSI_X3.4-1968' and UNICODE 'UCS-2LE'

That might just be a red herring but it does make me suspicious :)

Is there any debugging or code I can try on the Go side that would help narrow it down? I could add some lines to print out exta information in the odbc package or anywhere else that you think could help.

[alexbrainman]

Debian v9 64bit (current testing nightly from a couple days ago)

I only have ubuntu.

unixodbc v2.3.4

I have "unixodbc-2.2.14p2-5ub". That is what my OS have. Hopefully it is enough.

IBM i Access Client Solutions Linux AP_LCD8_2012_07 (latest version) ODBC drivers

How do I install that?

Is there any debugging or code I can try on the Go side that would help narrow it down? I could add some lines to print out exta information in the odbc package or anywhere else that you think could help.

I do not have any good suggestions. The error happens pretty early in the program. And the error message ODBC returns is not clear. So the only suggestion I have at this moment is to start with a similar C program that works for you, and then adjust C program to make it as similar as possible to your Go program and see when C program breaks. I suggest to use C, because that is the language I know. Also C is very popular, and all ODBC examples are written in C. I suppose you can do that yourself. I can, probably, send you a small C program to try, if you like.

Alex

[bwmarrin]

Hmn, well it's been years since I wrote C code, I'm not sure how successful I would be there.

The IBM I Access software is not free software and can only be downloaded from IBM to customers of their IBM mainframes, or maybe under some other conditions I don't know :) Anyhow, it would be mostly useless to download unless you happen to have an IBM i Series AS/400 mainframe around to connect to?

One thing we could possible do, if you think it would help, is setup some screen sharing of some sort so you can access a test environment I setup.

If you think you can create some basic C code to help test, I'll gladly review and run it.

I'll keep googling and digging on my side as best as I can and maybe I'll uncover something too. Worse case I guess I'll just have to use a different language to handle my current task, but that's a bit sad, I do really love using Go!

[alexbrainman]

Can you try adding DriverUnicodeType=1 into your odbc.ini (or to your sql.Open string if it works there)

https://stackoverflow.com/questions/19311687/unicode-converter-buffer-over-flow

? Thank you.

Alex

[bwmarrin]

I gave that a try, and no luck.

I found a way to enable a history log and debug log for the IBM driver using the following command (in case someone else stumbles on this)

/opt/ibm/iSeriesAccess/bin/cwbtrc /DT:1
/opt/ibm/iSeriesAccess/bin/cwbtrc /HL:1

So now I am getting some more information. In the history log I'm getting.

CWBNL0107 - Converted 94 bytes, 1 errors found beginning at offset 6 (scp=37 tcp=819 siso=1 pad=3 sl=94 tl=452)

The debug file is pretty massive and I'm still digging through it to see if I can find something that really helps us. But so far, it looks like the error message it was unable to translate back is..

Token  .  was not valid.  Valid tokens: FOR USE SKIP WAIT WITH FETCH ORDER UNION EXCEPT OPTIMIZE.

So those tokens are things that would be at the end of our test SQL statement. I have a feeling there's a character at the end of the string, a null maybe?, that it is having a problem with.

[alexbrainman]

I still think that either your unixodb or driver do not understand utf16 that this package use.

I just pushed 0ee187a7ebce8a842bf4abf1c6df7ad69f84d5e7 into new for_issue_88 branch of this repo. The change calls ANSI versions of ODBC API instead of Unicode. @bwmarrin can you try my change, please.

I also found this https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_71/rzatv/rzatvfaqodbc.htm it says:

Linux ODBC

The driver is an ODBC 3.5 ANSI driver with the ability to store and process Unicode data. An ANSI driver does not support Unicode strings passed as arguments to the APIs. Applications passing Unicode strings on APIs will work because the unixODBC driver manager maps calls these calls to the ANSI driver's narrow interfaces.

So the driver is ANSI only. unixodbc should be able to translate my unicode calls into ANSI, but maybe that is not happening.

Alex

[bwmarrin]

 go run main.go
1

Well, Alex :) I think that fixed it. But, hmn. What to do? Not a change you would want for everyone. Do you think it would be reasonable to have a configurable flag or something to set the encoding? Keeping your existing UTF16 as the default and maybe adding a ANSI option?

I'll keep digging around and maybe I'll find a way to get UTF16 to work still, if I do, I'll definitely report back here.

Hmn, I wonder what the isql and Julia code does in this regard. I tried to dig around in the Julia ODBC code yesterday but I just started looking at Julia code 3 days ago so I'm not sure I follow exactly what it does.

[alexbrainman]

I think that fixed it.

Good. I am glad it works for you.

Not a change you would want for everyone.

Definitely not. Whole world moved from ASCII.

Do you think it would be reasonable to have a configurable flag or something to set the encoding? Keeping your existing UTF16 as the default and maybe adding a ANSI option?

Anything is possible. But I would rather not do it, if it can be avoided. I do not want to complicate things unless there is some payback. I still suspect it is possible to configure something either in unixodbc or in your IBM driver so the ODBC API will accept utf16 (or maybe utf8).

I'll keep digging around and maybe I'll find a way to get UTF16 to work still, if I do, I'll definitely report back here.

Please, do.

Hmn, I wonder what the isql and Julia code does in this regard. I tried to dig around in the Julia ODBC code yesterday but I just started looking at Julia code 3 days ago so I'm not sure I follow exactly what it does.

I am not familiar enough with Julia language to be able to make judgement.

Alex

[bwmarrin]

Good. I am glad it works for you.

Thanks! I really do appreciate the help :)

I still suspect it is possible to configure something either in unixodbc or in your IBM driver so the ODBC API will accept utf16 (or maybe utf8).

Maybe, I'll keep digging. Both the Julia and iSQL tools are working with UTF8 from reading the unixODBC log files. Maybe it's just UTF16 it doesn't handle correctly. If you look at my issue on the Julia driver the maintainer there says that when connecting the driver reports what it supports and then that's what his package uses. If I can learn enough about the unixODBC interface to find out how that works - maybe that would be something useful here too. Then there's no API change to the end user.

But, for now I think we can close this issue, if you want, unless I come up with something else to help out.

[alexbrainman]

If you look at my issue on the Julia driver the maintainer there says that when connecting the driver reports what it supports and then that's what his package uses.

I did read the comments there. But the information is too general. We need to know what to do exactly.

But, for now I think we can close this issue, if you want, unless I come up with something else to help out.

I am happy to leave issue opened. Perhaps you will find more. I won't be looking myself - I cannot do that without having reproducible example here. But feel free to close this issue yourself, if what you have is good enough for you.

Alex

[souvrard]

Hello, I get the same problem than you ("Error message text unavailable.....") and I solved it (after googling a lot) by putting CCSID=xxx in the connexion string, just after uid and pwd (to get your CCSID, connect to the command line of the AS400 and run: DSPSYSVAL QCCSID).

But, I have now another problem... SQLPrepare {42000} (syntax error or access violation...)

When I execute the same query with isql, I get no error. (it is a very simple SELECT).

I applied your "patch" ( 0ee187a seen above) because I have the same info in the logs of unixodbc: UNICODE Using encoding ASCII 'ANSI_X3.4-1968' and UNICODE 'UCS-2LE'

But it did'nt solve my problem....

Here are the logs just after the line "UNICODE Using encoding ASCI ...."

[ODBC][10231][1520612100.212352][SQLDriverConnect.c][1697] Exit:[SQL_SUCCESS] Connection Out [DRIVER={IBM i Access ODBC Driver};SYSTEM=192.168.1.10;UID=XXXXXX...] [ODBC][10231][1520612100.212435][SQLAllocHandle.c][540] Entry: Handle Type = 3 Input Handle = 0x1b39ed0 [ODBC][10231][1520612100.212479][SQLAllocHandle.c][1085] Exit:[SQL_SUCCESS] Output Handle = 0x1b63da0 [ODBC][10231][1520612100.212500][SQLPrepare.c][196] Entry: Statement = 0x1b63da0 SQL = [SELECT XXXX FROM YYYYY WHERE ZZZZ='ABCD'][length = 51 (SQL_NTS)] [ODBC][10231][1520612100.220485][SQLPrepare.c][377] Exit:[SQL_ERROR] DIAG [42000]

            DIAG [42000] 

Could you help me ?

Best regards, stephane

[souvrard]

I forgot to mention another diff between go query and isql query:

The sql query, when I use isql, is passed to the driver as a string with the length,

The same query in Go is passed to the driver as a string but without the length, so the driver "calculate" it and report : [length xx SQL_NTS] in the logs (sql_nts means Null Terminated String...).

As I understand how the unixodbc work: it calculate the length of the query and add a null character at the end ?

Perhaps the problem is that the go program is not passing the length of the string (query) to the driver ??

I am very new to Go and I am french, sorry for my bad english. Best regards, stephane

[alexbrainman]

Could you help me ?

I googled for "42000 odbc ibm" and I get

https://support.microsoft.com/en-us/help/966260/select-error-40002-42000-ibm-client-access-express-odbc-driver-32-bit

are you sure your account has enough authority to run your SQL ?

Alex

[souvrard]

Hello, Thank you for your reply and your attention to my problem.

Yes, my account has enough authority to run this sql query... I run it without problem with isql in a bash console and I have a php script that is using the same ODBC connection string. This php script can run select, update, delete ..... sql queries without problems...

When I look in the unixodbc debug log file, the only difference I see between the isql query and the Go query is SQL = [SELECT XXXX FROM YYYYY WHERE ZZZZ='ABCD'][length = 51 (SQL_NTS)] => GO SQL = [SELECT XXXX FROM YYYYY WHERE ZZZZ='ABCD'][length = 51] => ISQL

SQL_NTS = Null Terminated String. As I understand the unixODBC documentation, when the length of the string (sql query) is not provided to sqlprepare, it is calculated and logged as SQL_NTS...

Can this info help you ? what can I do to "debug more" ?

Stephane

[alexbrainman]

Yes, my account has enough authority to run this sql query... I run it without problem with isql in a bash console and I have a php script that is using the same ODBC connection string. This php script can run select, update, delete ..... sql queries without problems...

Please show me small program that demonstrate the problem. And show program output and the error. What commit of github.com/alexbrainman/odbc/ do you use? What is your client OS? What ODBC driver do you use? What is your database / sever OS?

SQL = [SELECT XXXX FROM YYYYY WHERE ZZZZ='ABCD'][length = 51 (SQL_NTS)] => GO SQL = [SELECT XXXX FROM YYYYY WHERE ZZZZ='ABCD'][length = 51] => ISQL

We use SQL_NTS const to tell SQLPrepare ODBC function that SQL string has 0 at the end. I don't see why that would be inappropriate.

Alex

[souvrard]

Hello,

The example program (with XXX in UID and PWD):

package mainimport (        _ "github.com/alexbrainman/odbc"        "database/sql"        "fmt"        "log" ) func main() { //    db, err := sql.Open("odbc", //        Driver=IBM i Access ODBC Driver; //        System=; //        Uid=; //        Pwd=)       

db, err := sql.Open("odbc", "DRIVER={IBM i Access ODBC Driver};SYSTEM=192.168.1.10;UID=XXX;PWD=XXX;CCSID=297;CMT=0;NAM=1;DFT=1")    if err != nil {        log.Fatal(err)    }    defer db.Close()    rows, err := db.Query("SELECT B2BTYPU FROM B2BUTIPF WHERE B2BIDE='XXX'")    if err != nil {        log.Fatal(err)    }    var  pass string    for rows.Next() {        rows.Scan(&pass)    }    fmt.Println(pass) }

The output of the command "go run hello.go": 2018/03/13 10:49:54 SQLPrepare: {42000}  {42000}  exit status 1

The commit of alexbrainman/odbc is the master branch with the patch 0ee187a (but it is the same result with the master branch without the patch)

My client OS is Linux Debian (Linux INFO-4 3.16.0-4-amd64 #1 SMP Debian 3.16.43-2+deb8u5 (2017-09-19) x86_64 GNU/Linux)

The ODBC Driver is the official IBM i Access ODBC DriverThe Database is DB2 on AS400 (iSeries)

Do you want the entire log from unixODBC when I run this query in Go or in ISQL ?

Stéphane

[souvrard]

Hey Alex, 

I get it working by changing the CCSID from 297 (FRENCH EBCDIC) to 1208 (UTF-8 with IBM PUA)....

The 297 CCSID was the value AS400 give me (DSPSYSVAL QCCSID)....

I tried several other CCSID (UTF-8, UTF-16...) and by chance I found this working !!!

I cannot explain why it works in Go with this CSSID ???And I cannot explain why, in php with Laravel, I have to put CCSID 819 (ISO 8859-1 ASCII) ??? ISQL seems to connect in UTF-8, the default charset of my OS :/

[souvrard]

Ohh it is not as simple as I thought .....

The example sql given above was returning: "REP" wich is the expectated result.... (with CCSID 1208)...

I run the same query but with another "where" argument, that must return a word with a "é" in it.... and the result from my program is:

panic: runtime error: slice bounds out of range

goroutine 1 [running]: github.com/alexbrainman/odbc.(BindableColumn).Value(0xc420060180, 0x208bda0, 0x0, 0xc420055dc8, 0x43eb97, 0x10, 0x4d3260) /usr/local/go/src/github.com/alexbrainman/odbc/column.go:239 +0x2ec github.com/alexbrainman/odbc.(Rows).Next(0xc42000e040, 0xc4200102e0, 0x1, 0x1, 0x1, 0x1) /usr/local/go/src/github.com/alexbrainman/odbc/rows.go:35 +0xbf database/sql.(Rows).nextLocked(0xc42009e080, 0xc420060000) /usr/local/go/src/database/sql/sql.go:2622 +0xc4 database/sql.(Rows).Next.func1() /usr/local/go/src/database/sql/sql.go:2600 +0x3c database/sql.withLock(0x5075c0, 0xc42009e0b0, 0xc420055e98) /usr/local/go/src/database/sql/sql.go:3032 +0x63 database/sql.(*Rows).Next(0xc42009e080, 0xc4200102c0) /usr/local/go/src/database/sql/sql.go:2599 +0x7a main.main() /home/stephane/goproj/src/hello/hello.go:31 +0x1e7 exit status 2

And the same query with CCSID=819 (like in php) give me the result but with a "?" in place of the "é"... It doesn't work with others CCSID.

It seems that there are problems with encoding in Go ... (the same query in php or isql works perfectly without changing CCSID)

[souvrard]

Ohhh I am disappointed.....

It is now working with CCSID 819 !! (but with the "?" in place of "é", like in php !!)

I think the AS400 is "keeping" the connection for a certain time... that is why it works now but not at the first time (with CCSID 297).

I think now that the problem is not related to the "alexbrainman/odbc" but probably to the charset conversion between my linux system and the AS400...

sorry for all these messages.... and thank you a lot for your attention...

[alexbrainman]

@souvrard I don't have any good suggestions. Reading https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_71/rzatv/rzatvfaqodbc.htm again:

The driver is an ODBC 3.5 ANSI driver

And https://docs.microsoft.com/en-us/sql/odbc/microsoft/desktop-database-driver-compatibility explains what that means:

It also supports the use of an ANSI driver with an ANSI application. The Driver Manager provides limited Unicode-to-ANSI mapping for a Unicode application working with an ANSI driver

You are trying to use Unicode application with ANSI driver. I don't think that is supported by your driver.

You could use ANSI application with ANSI driver (code in https://github.com/alexbrainman/odbc/commit/0ee187a7ebce8a842bf4abf1c6df7ad69f84d5e7) but that won't let you do any characters outside of ASCII.

Please let me know, if you find a way to do it.

Thank you.

Alex

[souvrard]

Thank you alex for your response that let me understand where is the problem.

To solve my problem, I do a char conversion in my Go program with "golang.org/x/text/encoding/charmap" : charmap.Windows1250.NewDecoder()

Connecting to AS400/DB2 via ODBC on Linux is a temporary solution for me...

Thanks a lot for your help.

Stephane

[finalight]

sorry to revive the post. However I have a question regarding AS400 I Series DB2 as well.

May i know how to setup the drivers for DB2 on top of ODBC?

[alexbrainman]

May i know how to setup the drivers for DB2 on top of ODBC?

I do not know answer to your question.

Alex

[anuradhaindika83]

Hi, I was trying to use the same driver to call an iSeries stored procedure with IN/OUT parameter (Second parameter is IN and OUT).It calls fine. But I do not get the return data from the OUT parameter. Any idea on this? I tried googling for days and I could not find anything relates to this. Any help would be much appreciated

package main

import (
    "database/sql"
    "log"

    _ "github.com/alexbrainman/odbc"
)

var buffer string

func main() {

    db, err := sql.Open("odbc", "Driver={Client Access ODBC Driver (32-bit)}; Connect Timeout=120; System=ip; UID=username;Password=password; DBQ=SCDB")
    if err != nil {
        log.Fatal(err)
    }
    // Calling stored procedure
    buffer = ""
    stdname = "API_CALC"
    result, rowerr := db.Exec("CALL SCDB.STD_CALCAPP(?,?)", &stdname, &buffer)
    if rowerr != nil {
        log.Fatal(rowerr)
    }
    log.Println(result)
    log.Println(buffer)
    log.Println(stdname)
    defer db.Close()
}

[alexbrainman]

@indikaanu83 I do not know. Maybe you can write code like

https://github.com/alexbrainman/odbc/blob/dcf9c6f96c0914c139310f860ad640e571fe3e63/mssql_test.go#L1376-L1406

Alex

[anuradhaindika83]

That might work!!!!. I'll check it and get back to you. Thanks

[igremmerlb]

Just chiming in. I am having the same issue. We have been running CentOS7 for a long time (many programs) communicating to IBM iSeries using the go odbc driver. I recently installed Ubuntu 18.04 on my machine with the same IBM adapter, and it's getting the "SQLPrepare: {42000} [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0104 - Error message text unavailable. Message can not be translated successfully" error. I will poke around a bit more and try your patched version.

[igremmerlb]

I was able to get our test suite working with your 0ee187a branch. Would it be possible to get this in master with a configuration setting or something like that? I understand most people don't want ASCII at this point, but it seems to be the only option with the IBM iSeries we have. (We are US/English, ASCII only is OK). I'm also wondering if there is some setting somewhere that has changed between Ubuntu 16.04 and 18.04. The same setup seems to work fine on 16.04 (as well as Centos 7), granted with older unixodbc versions. I did recompile several unixodbc versions without any success.

[igremmerlb]

Nevermind, I was mixing up ASCII and ANSI. We are 99% ASCII, but we have a few other field types. I'll see if I can make any progress on this. I suppose it's better in the long run to get the ANSI / Unicode version working rather than limiting to ASCII if possible.

[igremmerlb]

OK, at this point I am stuck. I have tried everything I know. I am trying to connect my Go program to the IBM iSeries. We have ~20 programs in production using this already in Docker containers based on CentOS 7.

Everything works fine on Centos 7 (Unixodbc 2.3.1, Go 1.9, current alexbrainman/odbc). I get the issue in this thread of "SQLPrepare: {42000} [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0104 - Error message text unavailable. Message can not be translated successfully" when I am on Ubuntu 18.04 (Unixodbc 2.3.4, but I tried several versions, Go 1.10, current alexbrainman/odbc). ODBC.ini is the same. I can't find any UTF settings for the ODBC.ini.

See: https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_71/rzaik/connectkeywords.htm

The alexbrainman/odbc 0ee187a branch does work just fine on Ubuntu 18.04. I'm not sure if some setting silently got flipped between the OS versions. I believe based on this thread that the IBM iSeries adapter really only works with ASCII.

Also, Python3 works just fine on 18.04 using the same IBM ODBC driver.

Any thoughts or help are appreciated. Thank you.

[alexbrainman]

I was able to get our test suite working with your 0ee187a branch. Would it be possible to get this in master with a configuration setting or something like that?

Sorry, but no. This driver does not have any configuration. Why start now? Majority of users are not affected by this, so why make them suffer with some configuration. You can always make a copy of whatever code you want and keep it in the shape you want.

Python3 works just fine on 18.04 using the same IBM ODBC driver.

Maybe it uses ASCII version of ODBC interface (similarly to https://github.com/alexbrainman/odbc/commit/0ee187a7ebce8a842bf4abf1c6df7ad69f84d5e7). I would not know. I also don't know how python driver works, and I have no time to learn.

Any thoughts or help are appreciated.

Sorry, but I don't have any good suggestions.

Alex

[haynesherway]

iSeries Access works with my version, I had to create my own fork to fix it. github.com/haynesherway/odbc

[henryjw]

I had this issue in .NET Core running on Debian 8 (Jessie). I tried everything here, but what finally worked for me was downgrading the version of unixodbc from 2.3.4 to 2.3.1-3, which is the version I was running on my other service that was working.

[worksofliam]

This problem has come up again.

Getting this issue on Debian 10 under .NET Core 3.1. The driver works fine with unixODBC@2.3.2 but versions about that don't seem to work.

ERROR [42000] [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0104 - Error message text unavailable. Message can not be translated successfully

I wonder what changed in newer unixODBC versions for the Unicode to ANSI conversion to stop working. From this thread, I see they are using odbcapi=ansi;charset=utf8 in the connection string so maybe I will give this a try with a newer version of unixODBC to see if that will fix it again. I guess the real problem here is the driver not being unicode.

[richardschoen]

This problem has come up again.

Getting this issue on Debian 10 under .NET Core 3.1. The driver works fine with unixODBC@2.3.2 but versions about that don't seem to work.

ERROR [42000] [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0104 - Error message text unavailable. Message can not be translated successfully

I wonder what changed in newer unixODBC versions for the Unicode to ANSI conversion to stop working. From this thread, I see they are using odbcapi=ansi;charset=utf8 in the connection string so maybe I will give this a try with a newer version of unixODBC to see if that will fix it again. I guess the real problem here is the driver not being unicode.

Liam, you ever resolve this issue ? Same problem testing basic query on Linux Mint 19.2 with .Net Core.

[stefanbildl]

Hi, I have the same issue. neither the 0ee187a fix, nor any other proposed solution worked for me. German umlauts are not read correctly. I even tried many different CCSIDs, but with no success

[stefanbildl]

iSeries Access works with my version, I had to create my own fork to fix it.

github.com/haynesherway/odbc

I think it doesn't work anymore.

[datapopcorn]

Hi @igremmerlb,

You mentioned that Python3 works just fine on Ubuntu 18.04 using the same IBM ODBC driver. Could you please provide some guidance on how to set up the IBM i Access ODBC Driver for Linux for Python3 to make it work? I'm encountering issues with my Python 3 application.

Specifically:

Did you use pyodbc or sqlalchemy-ibmi? If so, which version? Did you install unixODBC-devel as suggested on this site: https://ibmi-oss-docs.readthedocs.io/en/latest/odbc/installation.html? Which version of the IBM i Access ODBC Driver for Linux did you use? Thank you!