Provider=IBMDA400 CopyFromRecordset Does not Return Text Field Values

[stones...@gmail.com]

Windows 7, Excel 2007 vba as follows:

Dim oConn As ADODB.Connection
Set oConn = New ADODB.Connection

oConn.Open "Provider=IBMDA400;Data Source=OURAS400;"";"""
rsQuery.CursorLocation = adUseServer

rsQuery.Open strQry, oConn, adOpenStatic, adLockReadOnly

For x = 0 To rsQuery.Fields.Count - 1
ActiveCell.Offset(0, x).Value = rsQuery.Fields(x).Name
Next x

ActiveCell.Offset(1, 0).Select

ActiveCell.CopyFromRecordset rsQuery

rsQuery.Close: oConn.Close

The query returns field names and 6 rows from the F4095 as expected from the SQL, but no values are returned for any of the text fields, only the first field which is numeric. No field in the F4095 contains more than 12 characters, and there are only 9 fields. Some fields do contain numbers only in text fields. If those fields are eliminated, the other text fields are still blank. The SQL works using a DSN connection.

Thanks in advance for any help!

[CRPence]

On 14-Feb-2012 10:09 , stones...@gmail.com wrote:
> The query returns field names and 6 rows from the F4095 as expected
> from the SQL, but no values are returned for any of the text fields,
> only the first field which is numeric. No field in the F4095
> contains more than 12 characters, and there are only 9 fields. Some
> fields do contain numbers only in text fields. If those fields are
> eliminated, the other text fields are still blank. The SQL works
> using a DSN connection.

When character fields are a problem, the first thing I do is review
the column definitions and the data, in order to verify that the data is
correctly stored according to the CCSID of the column. At the server:
DSPFFD of the TABLE and then DSPPFM [f10\f11] to review the hex code
points of the data [i.e. irrespective of they glyph] according to the
layout of the data in the buffer described by the prior DSPFFD.

SWAG: The functional SQL via the DSN may be using the deprecated
"force conversion" option; i.e. an effective lie being told to the
server, asking that the server should /pretend/ the CCSID of the data is
a known value, but in fact the column is defined for "no translation"
per CCSID 65535 [aka CCSID(*HEX)]. That is, if the failing connection
does not also effect that same lie, the EBCDIC data being copied may be
manifest as "blank" results. Rather than lying to the server, the CCSID
of the columns should be corrected, and the data in the columns ensured
to match the assigned CCSID.

Regards, Chuck

[stones...@gmail.com]

Thanks for your help Chuck. I didn't understand everything you posted, but it made me think the CCSID translation was the problem. I changed the following line:

oConn.Open "Provider=IBMDA400;Data Source=OURAS400;"";"""

to:
oConn.Open "Provider=IBMDA400;Data Source=OURAS400;TRANSLATE=1;"";"""

and no more problem!

Again, many thanks.

[christoph...@gmail.com]

I am having the same problem as you. When I try the TRANSLATE=1 I get an error message. Multi-step OLE DB operation generated errors. I think my connection string is correct. It works without the Translate=1.

as400cn = "Provider=IBMDA400;Data Source=***.***.*.**;translate=1;User Id=******;Password=******;"

Thank you for any help you can provide. This was so much eaiser wiht a SQL server.

[CRPence]

On 07 Dec 2012 08:33, christoph...@gmail.com wrote:

> On Friday, February 17, 2012 1:11:12 PM UTC-6, stones... wrote:
>> Thanks for your help Chuck. I didn't understand everything you
>> posted, but it made me think the CCSID translation was the
>> problem. I changed the following line
>>
>> oConn.Open "Provider=IBMDA400;Data Source=OURAS400;"";"""
>> to:
>> oConn.Open "Provider=IBMDA400;Data Source=OURAS400;TRANSLATE=1;"";"""
>>
>> and no more problem!
>>
>> Again, many thanks.
>
> I am having the same problem as you. When I try the TRANSLATE=1 I
> get an error message. Multi-step OLE DB operation generated errors.
> I think my connection string is correct. It works without the
> Translate=1.
>
> as400cn = "Provider=IBMDA400;Data Source=***.***.*.**;translate=1;User Id=******;Password=******;"
>

> Thank you for any help you can provide. This was so much easier with
> a SQL server.

The given string has a semicolon as separator character, as the last
non-delimiter, non-keyword value. Might that be an issue?

Irrespective of whatever else is done, be sure the User Profile at
the server is set to have a proper CCSID established; i.e. having set,
anything other than either of the special value *HEX or numeric value
65535. If the proper value is not known, then use the default CCSID for
the Language Identifier [LANGID] that reflects the language of the user.

Anyhow... Why not just correct the column definitions? If the column
data should not be treated as FOR BIT DATA, then do not define the
column as FOR BIT DATA. That is effectively the point I was making in
my earlier reply in the thread [of the above message by "stones"]:
https://groups.google.com/d/msg/comp.sys.ibm.as400.misc/h35ST3SPgqw/-EBYJrjfQUEJ

Use of the "Force Translate" option is effectively telling the server
to ignore the FOR BIT DATA attribute, and force translation from the
presumed EBCDIC CCSID to the encoding of the [e.g. ASCII] requester.
Why ask the server to lie about what the data is... or from another
perspective, why lie to the server about what the data is?

The IBM documentation seems to concur with the Feb-2012 message for
the setting for the translate parameter; suggesting the default is zero
and the value of one forces translation:
http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/rzatv/rzatvtra.htm
http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/rzaik/rzaikconnstrkeywordstranprop.htm

However the specification of one, as shown in the Feb-2012 message
that was quoted, seems to contradict the information in the following
online article [though the following article references "force
translate" rather than "translate"?]:
Article title: "Transylvania 65535"
http://www.itjungle.com/mgo/mgo070903-story02.html
"...
Another way to accomplish this is to set the Force Translate property of
the connection. This property can be set using the following connection
string:

Dim Con1 as new ADODB.Connection
Con1.Provider = "IBMDA400"
Con1.Properties("Force Translate")=0

Or you can do it with the connection string:

Dim Con1 as new ADODB.Connection
Con1.Open "Provider=IBMDA400;Data Source=MyAs400;Force
Translate=0",UserID,Pwd)

<<ed:> I believe there is a missing open parenthesis.? or the closing
parenthesis is extraneous, and the ending quote belongs in its place?>>

Note that setting the force translate option to zero (0) causes all
CCSID 65535 columns to be translated, so sometimes you may want to leave
this option off and use the VARCHAR scalar to translate the data only
when you need to translate. I have not used the Force Translate option
with the OLEDB provider, but it should be similar to using the Convert
Binary Data to Text translation option available in ODBC. I use the
VARCHAR scalar to convert the CCSID in many of my queries."

Like the VARCHAR scalar noted in that article quoted just above, with
the length [or DEFAULT] specified as the second argument, and with the
CCSID specified as the third argument, there is also the CAST scalar.
Each of the following expressions should be capable to effect what the
article shows for any particular column; notice that the third of the
above expressions is not only more succinct than the first, it allows
avoiding an explicit length value specification:
CAST( column_with_ccsid_65535 AS VARCHAR( len_val ) CCSID int_val )
VARCHAR( column_with_ccsid_65535 , len_val , int_val )
VARCHAR( column_with_ccsid_65535 , DEFAULT , int_val )

A very old v5r1 IBM KB article [IBM Software Technical Document],
though including newer release reference, seems to describe the "Force
Translate" similar to the above article:
Document Number: 23062121
Functional Area: Client Access
Subfunctional Area: OLE DB
Document Title
iSeries Access for Windows Custom Connection Properties for OLE DB
"...
_i Force Translate (V5R1) i_

The force translate property can be used to force data stored in the
CCSID 65535 to be translated. This is a special CCSID that indicates to
the provider that the data is to be considered as raw binary or
hexadecimal data, and should not be translated. This property will
override this default behavior and force the IBMDA400 provider to
convert the information. Prior to connecting to the server, this
property is set to a numeric value between 0 and 65535. This value
indicates which CCSID the data should be converted from when the
translation is made. The default value is 65535 and prevents
translation. If the property is set to 0, the CCSID of the operating
system job is used. The provider will assume that the data is encoded in
the CCSID specified for this property.
..."

--
Regards, Chuck