Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2004
    Location
    Helsinki, Finland
    Posts
    4

    Unanswered: Problem with VBA query to DB2

    Hi there,

    I'm trying to get data from AS/400. When I execute this SQL line in AS/400 I'll get proper data (= proper data in both colmuns). When I execute that code from MS Access VBA the first columns shows as question marks '?????'.

    The SQL in AS/400 (STRSQL) is like this:

    SELECT DRKY, DRDL01 FROM JLSFSP73.F0005 where DRSY='58 ' AND DRRT='WS'

    The code in MS Access looks like this:

    -----<code start>-----
    Sub TestThis()

    Dim myCnn As ADODB.Connection
    Dim myRs, myRs2 As ADODB.Recordset

    Set myCnn = New ADODB.Connection
    myCnn.Open "Driver={Client Access ODBC Driver (32-bit)};System=mySystemName;UID=myUserID;PWD=myPwd"

    Set myRs = New ADODB.Recordset
    Set myRs.ActiveConnection = myCnn

    myRs.Open "SELECT DRKY, DRDL01 FROM JLSFSP73.F0005 where DRSY='58 ' AND DRRT='WS'"
    myRs.MoveFirst
    Do Until myRs.EOF
    Debug.Print myRs!drky & " " & myRs!drdl01
    myRs.MoveNext
    Loop
    myRs.Close
    Set myRs = Nothing

    myCnn.Close
    Set myCnn = Nothing

    End Sub

    -----<code end>-----

    And the result looks like this:

    ????? LAMINOITU LASI
    ????? IKKUNALASI
    ????? IKKUNALASI KEHYKSILL#
    ????? KIERR#TYSKERAMIIKKA

    (The # characters in second column are scandinavian characters which have translated into # characters)

    In DB2 field DRKY is CHAR 10 with CCSID=65535 and field DRDL01 is CHAR 30 with CCSID=37.

    I have tried changing the connection string so that it has CCSID=37 at the end (and other values too) with no help. Only the data in second column has changed.

    I figured that this is a connect string issue, am I right? Anyone?

    -V-

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

  3. #3
    Join Date
    Nov 2004
    Location
    Helsinki, Finland
    Posts
    4
    Unfortunately no help Changing CCSID only changes the data in the second field, not in the first one. They still show up as questionmarks.

  4. #4
    Join Date
    Nov 2004
    Posts
    374

    data on as/400

    This is a known problem : having this ccsid 65...
    this because db2 connect / drda does not support this ccsid translation and result data is displayed in ebcdic mode.
    the only way to correct this
    have another ccsid as default for db2 (new data)
    old data should be converted to new files, flagged with different ccsid
    and old data copy to new files...
    Not very easy but possible
    Best Regards, Guy Przytula

  5. #5
    Join Date
    Nov 2004
    Location
    Helsinki, Finland
    Posts
    4
    Problem solved! I used T-SQL. Now sql goes like this:

    "SELECT CAST(DRKY AS CHARACTER(30) CCSID 037) AS DRKY, DRDL01 FROM JLSFSP73.F0005 WHERE DRSY='58 ' AND DRRT='WS'"

    With that CAST function I can change the datatype AND the CCSID of any fields in my query. It's brilliant

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •