[NEWSboard IBMi Forum]
Seite 1 von 2 1 2 Letzte
  1. #1
    KM is offline [professional_User]
    Registriert seit
    Apr 2003
    Beiträge
    1.005

    ADODB.Recordset in VBA und Unicode-Daten

    Hallo,

    ich versuche gerade Unicode-Daten von der AS/400 per VBA über eine QueryTable in Excel zu bringen. Die normalen Zeichen werden korrekt dargestellt. Die Sonderzeichen (wie z.B. kyrillisch) werden nur als Quadrate dargestellt. Der VBA-Code sieht folgendermaßen aus:

    Code:
        
    Set AS400 = New ADODB.Connection
    AS400.Open "ConnectionString"
    
    Dim qt As QueryTable
    Dim rs As New ADODB.Recordset
        
    ' Recordset mit Abfrage-Ergebnis füllen
    Set rs = AS400.Execute(sql)
         
    ' Recordset in Excel-Sheet ausgeben
    Set qt = Sheets(blatt).QueryTables.Add(rs, Sheets(blatt).Range(zelle))
        qt.Name = "AS400_Query"
        qt.FieldNames = False
        qt.RowNumbers = False
        qt.FillAdjacentFormulas = True
        qt.PreserveFormatting = True
        qt.RefreshOnFileOpen = False
        qt.BackgroundQuery = False
        qt.RefreshStyle = xlInsertDeleteCells
        qt.SavePassword = True
        qt.SaveData = True
        qt.AdjustColumnWidth = False
        qt.RefreshPeriod = 0
        qt.PreserveColumnInfo = True
        qt.Refresh
    Wenn ich nicht über einen ADODB.Recordset gehe, sondern beim QueryTables.Add eine neue ODBC-Verbindung angebe, funktioniert's problemlos. Nur wird hier halt immer eine neue ODBC-Verbindung aufgebaut, was ich vermeiden wollte.

    Ist der ADODB.Recordset etwa nicht unicodefähig?

    Gruß,
    KM

  2. #2
    Registriert seit
    Feb 2001
    Beiträge
    20.207
    Dieser Weg ist zu kompliziert.
    Wenn ADO, dann mach den SQL auch komplett selber (Connect, Execute, Blatt füllen).
    ADO ist natürlich Unicodefähig, der Feldtyp ist hierfür adWChar oder adVarWChar.
    Einfacher ist es erst mal, das Ganze über MS-Query zu machen.
    Die Vorschau von MS-Query kann Unicode leider nicht anzeigen, da hier kein Unicod-Font verwendet wird.
    Im Excel stehen die Daten dann aber korrekt, wenn denn die Quelle auch Unicode ist.
    Dienstleistungen? Die gibt es hier: http://www.fuerchau.de
    Das Excel-AddIn: https://www.ftsolutions.de/index.php/downloads
    BI? Da war doch noch was: http://www.ftsolutions.de

  3. #3
    KM is offline [professional_User]
    Registriert seit
    Apr 2003
    Beiträge
    1.005
    Was ist hier bitte zu kompliziert? Ich fülle das Recordset mit einem Connection.Execute und das Recordset verwende ich als Input für meine QueryTable. Einfacher geht's doch gar nicht.

    Ich hab jetzt mal von QueryTable umgestellt auf CopyFromRecordset, um die Daten des Recordset ab einer bestimmten Zelle in Excel einzufügen. Die Daten werden auch eingefügt, sie liegen aber je nach Treiber in einer unterschiedlichen Form vor.

    Wenn ich den ODBC-Treiber verwende, werden die Unicode-Sonderzeichen als kleine Quadrate eingefügt. Ein Datumsfeld wird hingegen korrekt interpretiert.

    Wenn ich den OLE-DB Treiber (IBMDA400 oder IBMDASQL) verwende, werden die Unicode-Sonderzeichen korrekt eingefügt, jedoch kommt dieser Treiber offenbar nicht mit Datumsfeldern zurecht. Stattdessen werden nur irgendwelche Zeichen eingefügt.

    Dies zeigt, dass das Recordset an sich schon unicodefähig ist.

    Wie kann ich mein Problem nun lösen und alle Daten korrekt importieren?

    Gruß,
    KM

  4. #4
    Registriert seit
    Feb 2001
    Beiträge
    20.207
    Weil du mit MS-Query (Import Daten) nichts programmieren brauchst und das QueryDef automatisch erstellst.
    Du benötigst nur ein DSN über den ODBC-Manager.
    Die restlichen Eigenschaften definierst du dann über die Query-Eigenschaften im Excel.
    Bei mir hat das bisher immer problemlos funktioniert.
    Dienstleistungen? Die gibt es hier: http://www.fuerchau.de
    Das Excel-AddIn: https://www.ftsolutions.de/index.php/downloads
    BI? Da war doch noch was: http://www.ftsolutions.de

  5. #5
    KM is offline [professional_User]
    Registriert seit
    Apr 2003
    Beiträge
    1.005
    Also erstens ist mir der Umweg über MS Query viel zu umständlich. Außerdem will ich ja nicht manuell Daten importieren, sondern die Datenübertragung soll variabel gehalten werden. Deshalb will ich diese Funktionalität über entsprechende Prozeduren in einem Excel-AddIn den Benutzern zur Verfügung stellen. Ich kann auch nicht sicher davon ausgehen, dass bei den Benutzern bereits DSNs definiert sind. Deshalb stelle ich die ODBC-Verbindung bzw. OLEDB-Verbindung über einen Connection-String her.

    Das Problem ist jetzt also, dass je nach Verbindungstyp (ODBC oder OLEDB) die Feldtypen im Resultset anders sind. Ich hatte inzwischen auch schon versucht das Resultset vorher mit den entsprechenden Felddefinitionen zu erstellen. Jedoch werden diese beim Füllen des Resultsets wieder überschrieben.

    Die einzige Methode, die für alle Feldtypen funktionierte, war als ich beim QueryTables.Add den ODBC-Connection-String direkt angegeben habe. Das hat halt zur Folge, dass jedesmal eine neue ODBC-Verbindung hergestellt wird, was ich nicht unbedingt haben möchte.

    Gibt es sonst noch irgendwelche Möglichkeiten?

    Gruß,
    KM

  6. #6
    Registriert seit
    Feb 2001
    Beiträge
    20.207
    Dann würde ich das QueryDef so füllen, wie es MS-Query auch macht und das ausführen und laden dann Excel selber machen lassen, also neben der Verbindungsfolge auch den SQL-String eintragen.
    Somit wird das AddIn nicht mehr benötigt, wenn die Daten nur aktualisiert werden sollen.
    Dienstleistungen? Die gibt es hier: http://www.fuerchau.de
    Das Excel-AddIn: https://www.ftsolutions.de/index.php/downloads
    BI? Da war doch noch was: http://www.ftsolutions.de

  7. #7
    KM is offline [professional_User]
    Registriert seit
    Apr 2003
    Beiträge
    1.005
    Nein, das bringt mir so nichts. Wie bereits erwähnt soll die Importfunktion völlig variabel gehalten werden. Das heißt es können völlig unterschiedliche SQL-Abfragen sein, deren Results an völlig unterschiedliche Positionen im jeweiligen Excel-Sheet eingefügt werden können. Diese Funktionalität soll ja nicht auf eine Excel-Tabelle beschränkt sein, sondern sie soll wiederverwendbar sein. Deshalb das AddIn.

    So wie ich das verstanden habe, nützt mir die vorherige Definition der Feldtypen im Resultset nichts, da diese beim Füllen des Resultsets sowieso wieder überschrieben werden. Ist das so korrekt? Und die Feldtypen selbst werden bestimmt durch die zugrunde liegende Engine (ODBC oder OLEDB). Das Problem ist also nur (wie oben schon beschrieben), dass der ODBC-Treiber bei Unicode-Daten den Feldtyp adVarChar anstatt adVarWChar generiert, während der OLEDB-Treiber bei Datumsfeldern den Feldtyp adChar anstatt adDate.

    Kann man das irgendwie beeinflussen bzw. ändern?

    Gruß,
    KM

  8. #8
    Registriert seit
    Feb 2001
    Beiträge
    20.207
    Das kann ich fast nicht glauben.
    Die Feldtypen eines Recordsets werden natürlich durch das Ergebnis eines SQL's gesetzt.
    Schließlich zerstörst du ja das Recordset mittels:
    set myRcd = myCnn.Execute(...)
    oder
    myRcd.Open MySql, MyCnn ...

    Bisher hatte ich mit Unicode diesbezüglich auch bei ODBC keine Probleme.
    Hast du das Recordset daraufhin mal analysiert?

    Beim OLEDB-Treiber muss man für "Datum" explizite Eigenschaften in der Verbindung setzen (siehe Doku des OLEDB-Treibers).
    Dienstleistungen? Die gibt es hier: http://www.fuerchau.de
    Das Excel-AddIn: https://www.ftsolutions.de/index.php/downloads
    BI? Da war doch noch was: http://www.ftsolutions.de

  9. #9
    Registriert seit
    Feb 2001
    Beiträge
    20.207
    Allerdings habe ich da für ODBC noch einen Hinweis gefunden:

    Unicode fields, which are the data types with a CCSID of 1200 (UTF-16) or 13488 (UCS-2), report to ODBC 2.x applications as SQL_CHAR, SQL_VARCHAR, and SQL_LONGVARCHAR instead of SQL_WCHAR, SQL_WVARCHAR, and SQL_WLONGVARCHAR.
    ODBC data types and how they correspond to DB2 UDB database types

    Woran der Treiber nun allerdings den Typ (2.x oder 3.x) erkennt weiß ich nun auch nicht.

    Zumindest bist du mit dem Problem nicht alleine (ich habe das bisher tatsächlich nicht gemerkt).

    Die Konvertierung erfolgt bereits durch den ODBC-Treiber, da der MSDASQL-OLEDBTreiber wohl noch aus ODBC 2.x-Zeiten stammt.

    Ich habe auch keine Einstellung für ODBC 3.x gefunden.

    Dir bleibt wohl nur der IBMDASQL (IBMDA400 ist der selbe Treiber).

    Excel selber meldet sich wohl als ODBC 3.x am Treiber an, so dass nicodedaten korrekt geladen werden.
    Fülle doch das QueryDef auch mit dem SQL und lass Excel die Daten selber laden.
    Dienstleistungen? Die gibt es hier: http://www.fuerchau.de
    Das Excel-AddIn: https://www.ftsolutions.de/index.php/downloads
    BI? Da war doch noch was: http://www.ftsolutions.de

  10. #10
    Registriert seit
    Feb 2001
    Beiträge
    20.207
    Und nun noch ein paar Ergänzungen:
    Der MSDASQL-OLEDB-Treiber stammt noch aus Zeiten vor ODBC 3.51. Erst ab ODBC 3.51 wird auch Unicode beim Lesen unterstützt.
    Für das Senden von Unicode ist das ja kein Problem, da du ja Parameter gezielt mit adVarWChar definieren kannst.
    Laut SQL-Trace bindet der MSDASQL leider bei einem Recordset mit adChar so dass Unicode eben verloren geht.

    Excel selber verwendet die neu Jet-Engine 4.0 mit SQL-Pasthru-Abfragen.
    Hier funktioniert das Binden also mit adVarWChar so dass Unicode funktioniert.

    Um also dein AddIn dynamisch zu machen, füllst du die Connection-Egenschaft des QueryTable-Objekts mit "ODBC;Verbindungsfolge", den CommandText und wendest die Methode Refresh an.

    Vor mehreren Verbindungen brauchst du keine Angst zu haben, da der ConnectionPool standardmäßig aktiviert ist.

    Bei der Verwendung des IBMDA400/IBMDASQL muss leider in der Verbindungsfolge die Konvertierung von DateTime gezielt ausgeschaltet werden:

    IBM i Support: Software Technical Document : 23062121

    Convert Date Time to Char = False;
    Dienstleistungen? Die gibt es hier: http://www.fuerchau.de
    Das Excel-AddIn: https://www.ftsolutions.de/index.php/downloads
    BI? Da war doch noch was: http://www.ftsolutions.de

  11. #11
    KM is offline [professional_User]
    Registriert seit
    Apr 2003
    Beiträge
    1.005
    Hallo,

    ich habe jetzt noch eine Menge Tests durchgeführt und dabei einige neue Erkenntnisse gewonnen.

    QueryTable:
    1.) Beim QueryTables.Add kann ich bekanntlich als Datenquelle ein ADODB.Recordset angeben. Wenn dieses Recordset als erste Spalte jedoch ein numerisches Feld enthält, wird die erste Spalte seltsamerweise ignoriert bzw. alle führenden numerischen Spalten werden ignoriert. Wenn das Recordset z.B. als erste Spalte ein Char-Feld enthält, funktioniert's problemlos.
    2.) Wenn ich beim QueryTables.Add direkt einen ODBC-Connectionstring angebe, passt zwar das Ergebnis. Allerdings wird jedes Mal eine neue ODBC-Verbindung hergestellt. Ich weiß nicht wie Du darauf kommst, dass Connection Pooling automatisch funktioniert. Aber das stimmt nicht. Jeder Aufruf hat bei mir 1-2 Sekunden gedauert. Connection Pooling muß man laut Microsoft erst aktivieren. das habe ich auch gemacht und würde auch funktionieren (Zeitmessung dann nur noch im Hundertstelsekunden-Bereich). Allerdings scheitert dann der QueryTables.Add wegen "allgemeinem ODBC-Fehler" ohne nähere Erläuterung.

    OLE DB:
    Bei der OLEDB-Verbindung funktionieren durch die Einstellung des Verbindungsparameters Convert Date Time To Char=FALSE nun auch die Datumsfelder richtig. Aber wie gesagt gibt es hier die Probleme mit dem Recordset im QueryTables.Add, wenn das Recordset mit numerischen Felders beginnt. Dies kann ich aber umgehen, wenn ich die Ergebnisse des Recordsets mit CopyFromRecordset statt QueryTable in die Ziel-Tabelle einfüge. Damit funktioniert's nämlich.

    Unicode-Texte:
    Bei der OLEDB-Verbindung erhalte ich die Unicode-Texte korrekt zurück. Bei der ODBC-Verbindung erhalte ich sie nur, wenn ich die ODBC-Verbindung direkt beim QueryTables.Add angebe. Wenn ich erst einen Recordset damit fülle, werden die Felder als adChar interpretiert.

    Ich hab mir mal die Treiber-Einstellungen angeschaut, konnte allerdings die Screenshots hier nicht hochladen. Auffällig war z.B., dass bei der ODBC-Verbindung die OLE-DB-Version auf 2.00 stand, während sie bei der OLEDB-Verbindung auf 2.50 stand. Das könnte die Ursache für die Unicode-Probleme sein. Die ODBC-Treiber-Version stand auf 3.51 und müsste dafür ausreichend sein.

    Um alle meine Probleme in dieser Sache zu lösen, werde ich nun also den OLEDB-Treiber mit Recordset und zum Daten einfügen den CopyFromRecordset verwenden. Somit kann ich alle Feldtypen verarbeiten, die Daten korrekt in die Ziel-Tabelle einfügen und auch aus Performance-Gründen die Connection offen halten.

    Gruß,
    KM

  12. #12
    KM is offline [professional_User]
    Registriert seit
    Apr 2003
    Beiträge
    1.005
    Hier sind jetzt doch noch die Treiber-Einstellungen:

    ODBC
    ADO Version: 2.8
    DBMS Name: DB2/400 SQL
    DBMS Version: 05.04.0014
    OLE DB Version: 02.00
    Provider Name: MSDASQL.DLL
    Provider Version: 02.81.1132.0
    Driver Name: CWBODBC.DLL
    Driver Version: 05.04.0120
    Driver ODBC Version: 03.51

    OLE-DB
    ADO Version: 2.8
    DBMS Name: DB2 UDB for iSeries
    DBMS Version: 05.04.0000 OS/400 V5R4M0
    OLE DB Version: 02.50
    Provider Name: cwbzzodb.dll
    Provider Version: 11.00.1200

    Vielleicht kann da ja noch jemand etwas "herauslesen" bzw. Vorschläge machen wie man die ODBC-Variante aktualisieren kann.

    Gruß,
    KM

Similar Threads

  1. Unicode Daten ins IFS übertragen
    By SchoberS in forum IBM i Hauptforum
    Antworten: 3
    Letzter Beitrag: 17-01-08, 10:08
  2. Antworten: 6
    Letzter Beitrag: 26-07-06, 13:22
  3. IFS Datei Daten hinzufügen
    By sim in forum NEWSboard Programmierung
    Antworten: 11
    Letzter Beitrag: 18-05-06, 09:00
  4. Antworten: 1
    Letzter Beitrag: 24-04-06, 11:37
  5. Daten Import Tool + Dublettensuche für i5 CRM
    By Heinz Molter in forum Archiv NEWSblibs
    Antworten: 0
    Letzter Beitrag: 31-08-04, 10:48

Berechtigungen

  • Neue Themen erstellen: Nein
  • Themen beantworten: Nein
  • You may not post attachments
  • You may not edit your posts
  •