[NEWSboard IBMi Forum]
  1. #1
    Registriert seit
    Jan 2018
    Beiträge
    20

    ODBC und SQL-Prozeduren

    Hallo,

    hat jemand von Euch Erfahrungen mit dem Einsatz von ODBC und SQL-Prozeduren? Einfache SQL-Abfragen ( select a,b,c from table ) sind kein Problem, aber wenn ich etwas komplexere Anfragen z.B. mit einem OUTER JOIN erstelle, dann kommt der ODBC-Treiber an seine Grenzen.

    Ich wollte das durch den Einsatz von SQL-Prozeduren umgehen, denn die können ja auch problemlos Resultsets zurückliefern. Beim Aufruf von SQL-Prozeduren erhalte ich grundsätzlich SQL-Fehler wie:

    ERROR [HY000] [IBM][System i Access ODBC-Treiber][DB2 für i5/OS]SQL0440 - Routine SP_TEST in LIB mit angegebenen Parametern nicht gefunden.

    ODBC-Text: { call LIB.SP_TEST( '001' ) }

    Die Prozedur heißt "LIB.SP_TEST" und sieht so aus (und kann über STRSQL aufgerufen werden):

    create or replace procedure lib.sp_test( in v_mandant char(10))
    language SQL resultsets:
    begin
    declare v_count int;
    declare v_cursor cursor with return to client for
    select *
    from lib.journal
    where mandant=v_mandant;

    open v_cursor;
    return;
    end


    Meine Konstellation: MS-Reporting Server (2016), Client SSDT, Zugriff per ODBC auf AS/400

    Tabellen kann ich aus der Bibliothek verwenden, somit kann es eigentlich kein Berechtigungsproblem oder Zugriffsproblem mit der Bibliothek sein.

    Vielen Dank im Voraus

    Michael

  2. #2
    Registriert seit
    Feb 2001
    Beiträge
    20.207
    Nun kann ich nicht verstehen wieso ODBC bzgl. der SQL's an Grenzen stößt.
    Ob du die SQL's per Procedure oder native ausführst macht da keinen Unterschied.
    Du hast da halt Performanceprobleme, die eine Prozedur auch nicht auflöst wenn der SQL derselbe ist.
    Die einzige Grenze die ich kenne ist die max. Länge des SQL-Strings:
    Bei SBCS = 32K, bei Unicode = 16K.

    Ansonsten gilt für Prozeduraufrufe, dass die Parameter genau zur Definition passen müssen.
    Definierst du also CHAR(10), kannst du nicht CHAR(3) übergeben sondern musst mit Leerzeichen auffüllen oder per CAST('001' as char(10)) anpassen.
    Besser wäre die Definition per VARCHAR(10), dann geht 0-10 Zeichen.

    Noch besser wäre es, wenn du per ODBC mit Parametermarkern und Command-Objekten umgehst.
    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
    Registriert seit
    Jan 2018
    Beiträge
    20
    Zitat Zitat von Fuerchau Beitrag anzeigen
    Nun kann ich nicht verstehen wieso ODBC bzgl. der SQL's an Grenzen stößt.
    Ob du die SQL's per Procedure oder native ausführst macht da keinen Unterschied.
    Du hast da halt Performanceprobleme, die eine Prozedur auch nicht auflöst wenn der SQL derselbe ist.
    Die einzige Grenze die ich kenne ist die max. Länge des SQL-Strings:
    Bei SBCS = 32K, bei Unicode = 16K.
    Vielleicht habe ich mich falsch ausgedrückt. Mit "an seine Grenzen" meine ich nicht Leistungsgrenzen, sondern die Fähigkeit, datenbankspezifische Besonderheiten zu akzeptieren.

    Beispiel:
    select rownumber() over() , table.* from lib.table

    Ergebnis:
    Fehler in der Liste der Funktionsargumente: "(" wurde nicht erkannt.
    Fehler in der Liste der Funktionsargumente: ")" wurde nicht erkannt.
    Analysieren von Abfragetext nicht möglich.

    Ist klar, liegt an "rownumber() over()" aber anders kann ich halt die Zeilennummer bei DB2 nicht ermitteln. Geht auch nicht, wenn ich eine Sortierung angebe ( rownumber() over(order by field) )

    Bei komplexen Abfragen, die diverse JOINs und Unterabfragen beinhalten, verschluckt sich der ODBC-Treiber (oder die Microsoft Data Tools ?) auch. Diese Abfragen teste ich immer mittels STRSQL, dort funktionieren sie.


    Zitat Zitat von Fuerchau Beitrag anzeigen
    Ansonsten gilt für Prozeduraufrufe, dass die Parameter genau zur Definition passen müssen.
    Definierst du also CHAR(10), kannst du nicht CHAR(3) übergeben sondern musst mit Leerzeichen auffüllen oder per CAST('001' as char(10)) anpassen.
    Besser wäre die Definition per VARCHAR(10), dann geht 0-10 Zeichen.

    Noch besser wäre es, wenn du per ODBC mit Parametermarkern und Command-Objekten umgehst.
    Wenn ich folgendes aufrufe:

    { call LIB.SP_TEST( ? ) }

    kommt die Fehlermeldung:

    Fehlerquelle: CWBODBC.DLL
    Fehlermeldung: ERROR [HY000] [IBM][SYSTEM i Access ODBC-Treiber]...
    Routine SP_TEST in LIB mit angegebenen Parametern nicht gefunden.


    Im Prinzip geht es mir auch nur darum:

    Wie kann ich per ODBC ( MS Reporting-Service 2016 ) eine SQL-Prozedur ausführen?

    Die formale Syntax lautet: { [?=] call LIB.PROC ( [? [, ? ... ]] ) }
    https://www.ibm.com/support/knowledg...z_esccall.html

    Das führt aber zu der o.a. Fehlermeldung und daher stehe ich quasi auf dem Schlauch...

  4. #4
    Registriert seit
    Feb 2001
    Beiträge
    20.207
    Ok, du hast nicht erwähnt, dass du über den SQL-Verbindungsserver gehst.
    Da liegt es nicht am ODBC-Treiber, denn der routed deine SQL's einfach durch, sondern an der MS-eigenen SQL-Syntax, die erst nach der eigenen Prüfung an den Verbindungsserver weitergereicht wird und dann dort auch scheitern kann (z.B. bei casesensitiven Namen, MS: [Name], SQL: "Name").

    Versuche es mit Passthru-Abfragen an MS vorbei:

    select * from OpenQuery('Verb.Server', 'select bla bla')

    Ähnliches erlaubt auch die EXEC/EXECUTE-Anweisung für Nicht-Select's.
    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
    Registriert seit
    Aug 2001
    Beiträge
    2.869
    select rownumber() over() , table.* from lib.table

    Die Funktion heißt ja auch ROW_NUMBER() OVER() und nicht rownumber().
    Da hat er schon recht!

    Birgitta
    Birgitta Hauser

    Anwendungsmodernisierung, Beratung, Schulungen, Programmierung im Bereich RPG, SQL und Datenbank
    IBM Champion seit 2020 - 4. Jahr in Folge
    Birgitta Hauser - Modernization - Education - Consulting on IBM i

  6. #6
    Registriert seit
    Jan 2018
    Beiträge
    20
    Zitat Zitat von B.Hauser Beitrag anzeigen
    Die Funktion heißt ja auch ROW_NUMBER() OVER() und nicht rownumber().
    Da hat er schon recht!

    Birgitta
    Da hast Du natürlich Recht, Birgitta. Handelt sich um einen klassischen Tippfehler (und STRSQL akzeptiert beide Varianten).

    Guckst Du Bild

    Click image for larger version. 

Name:	row_number.png 
Views:	7 
Size:	19,8 KB 
ID:	433

  7. #7
    Registriert seit
    Jan 2018
    Beiträge
    20
    Zitat Zitat von Fuerchau Beitrag anzeigen
    Ok, du hast nicht erwähnt, dass du über den SQL-Verbindungsserver gehst.
    Stimmt, das habe ich nicht erwähnt, weil ich nicht über einen Verbindungsserver gehe ;-).

    Ich verwende Microsoft Visual Studio 2017 in der Datatools-Variante (SSDT) als Client für den Report-Server. Und auf die AS400 greife ich per ODBC direkt ohne Umwege (Verbindungsserver) zu.

    ( https://docs.microsoft.com/en-us/sql...ata-tools-ssdt )

    Somit kann ich Deinen Tipp leider nicht anwenden.

  8. #8
    Registriert seit
    Feb 2001
    Beiträge
    20.207
    Das ist dann auch keine Fehlermeldung vom ODBC-Treiber sondern von VisualStudio SSDT.
    Und wie der Name schon sagt, SQL-Server-Data-Tools, und somit von der SQL-Syntax an SQL-Server gebunden, auch hier sollte es (vielleicht) eine Passthrough-Methode geben, bzw. eine SQL-Ansicht möglich sein, die keine Syntaxprüfung durchführt.

    Ich meine (zumindest früher) gabs sowas auch für IBM's DB2/400.

    Alternativ kannst du die SQL's aber auch native durchführen, du hast nur leider keine Design-Unterstützung.
    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
    Jan 2018
    Beiträge
    20
    Ich habe jetzt mal eine Verbindung zwischen dem SQL-Server und der AS/400-DB2 hergestellt (Linked Server) und kann nun auch über den Weg SQL ausführen. Das klappt auch prima für SELECT, aber die Ausführung einer SQL-Prozedur ist leider immer noch nicht möglich.

    Ich habe eine Prozedur ohne Parameter eingerichtet und kann diese problemlos mittels STRSQL aufrufen. Wenn ich sie per SQL verwenden will, erhalte ich die folgende Fehlermeldung:

    Aufruf mit:

    exec( 'call lib.sp_test2') at as400


    OLE DB provider "DB2OLEDB" for linked server "as400" returned message "Routine TEST2 in LIB mit angegebenen Parametern nicht gefunden. SQLSTATE: 42884, SQLCODE: -440".
    Msg 7215, Level 17, State 1, Line 1
    Could not execute statement on remote server 'as400'.

    ---

    Wenn ich meine Testprozedur mit einem Parameter benutze mit

    exec ( 'call lib.test( ?)', '001') at as400 /* Parameter ist CHAR(3) */

    oder

    exec ( 'call lib.test( ?)', 1) at as400 /* Parameter ist INT */

    dann kommt dieselbe Fehlermeldung. Also mache ich etwas grundsätzliches falsch. Nur sehe ich es nicht...

    Habt Ihr einen Tipp für mich? Muss die SQL-Prozedur irgendwelchen Kriterien entsprechen? Ich habe die SQL-Prozedur direkt auf der AS/400-Maschine eingerichtet ( qsh -> cat SQL.txt | db2 -i ). Muss die Prozedur mittels Systembefehl irgendwie "registriert" werden oder ähnliches?

    Und später möchte ich auch ein Result-Set zurückliefern, geht das in der Form überhaupt?

  10. #10
    Registriert seit
    Feb 2001
    Beiträge
    20.207
    Das Problem bei Prozeduren ist, die genaue Übergabe der Parameter passend zum Typ.
    Was immer der SQL-Server da macht, i.d.R. ist '001' nicht char(3) sondern varchar(3).
    Für SQL-Prozeduren sollte man nach Möglichkeit generischere Typen definieren um etwas allgemeiner zu arbeiten. Innerhalb der Prozedur kann man dann ggf. Prüfungen und/oder casts vornehmen.

    exec ( 'call lib.test( cast( ? as char(3)) )', '001') at as400
    exec ( 'call lib.test( cast( ? as int) )', 1) at as400

    Das Problem ist ggf., dass der SQL-Server die Parameter nicht vom Host abfragt sondern an Hand der übergebenen Werte definiert, was aber nur eine Vermutung ist.
    Problematischer sind dann noch Type wie decimal/numeric(n, m), die vom SQL-Server da wohl auch so nicht funktionieren.

    Ob die Prozedur korrekt registriert ist, kann dir ein Aufruf per STRSQL bzw. über den OpsNav mit SQL-Befehle ausführen sagen.

    Wenn dein SQL.txt entsprechenden "CREATE PROCEDURE ...." enthält sollte es funktionieren.
    Bedenke nur im Wiederholungsfall sollte vorher ebenso ein "DROP" enthalten sein oder, falls dein Release das schon zulässt ein "CREATE OR REPLACE ...".

    Möchtestet du ein Resultset von einer Prozedur, dann ist wiederum "select * from OpenQuery('Server', 'Call ...') dein Favorit.
    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

Similar Threads

  1. SQL-Prozeduren ausführen oder cwbundbs.exe
    By whitie59 in forum IBM i Hauptforum
    Antworten: 5
    Letzter Beitrag: 27-09-17, 12:47
  2. Alle Prozeduren mit Parameter auflisten
    By Gutmann in forum IBM i Hauptforum
    Antworten: 9
    Letzter Beitrag: 04-08-17, 11:32
  3. SQLRPGLE Get und Set Prozeduren
    By iseries_user in forum NEWSboard Programmierung
    Antworten: 14
    Letzter Beitrag: 11-05-16, 11:13
  4. Interne Prozeduren mit DFTACTGRP
    By DEVJO in forum NEWSboard Programmierung
    Antworten: 5
    Letzter Beitrag: 16-09-15, 14:14
  5. Probleme mit SQL-Prozeduren
    By AnjaS in forum IBM i Hauptforum
    Antworten: 1
    Letzter Beitrag: 16-01-03, 10:18

Tags for this Thread

Berechtigungen

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