Session D-ISQL

SQL via Internet

Torsten Weggen
INDISoftware GmbH


Vorbemerkung

Jeder stößt früher oder später auf das Problem, von mehreren Stellen aus auf den selben Datenbestand zugreifen zu müssen (z.B.: Zentrale mit Außenstellen). Dies lässt sich natürlich mit bekannten Mechanismen wie dem automatischen Datenabgleich in der Nacht oder eine Standleitung zum zentralen Rechner verwirklichen. Der Nachteil dieser Lösungen liegt in den hohen Kosten bzw. großen Programmieraufwand, der betrieben werden muss, um Daten-Inkonsistenzen zu vermeiden. (Vorteile sind z.B. Sicherheitsaspekte)

Man kann aber auch einen anderen Weg gehen: Warum legt man seine zentralen Daten nicht einfach auf einem Webserver ab und schreibt einen COM-Server dazu, der SQL-Anfragen aus dem Web verarbeitet und einen Ergebniscursor zurücksendet ? Die Kommunikationskosten pro Außenstelle sind im Gegensatz zu einer Standleitung absolut vernachlässigbar und die Geschwindigkeiten sind absolut akzeptabel.

Die Server-Seite

Beschäftigen wir uns zunächst mit dem Server. Wie ein Webserver grundsätzlich funktioniert, ist Thema der anderen Session (D-RAWD). Wir betrachten an dieser Stelle nur die für die SQL-Verarbeitung relevanten Aspekte. Zunächst einmal eine kleine Begriffsklärung.

Der Webserver erhält die Anfrage über einen Aufruf der Form:

http://www.meinedomain.de/scripts/foxisapi.dll/webserverexe.klasse.methode?param1=wert1¶m2=wert2
...

Es wird also in unserem COM-Server "Webserverexe" die Methode "methode" aus der Klasse "Klasse" mit dem Parameter "param1=wert1¶m2=wert2" angesprochen.

Unser COM-Server splittet zunächst den Parameter-String auf und speichert die einzelnen Param / Wert -Paare in dem Array aFormVars (Eigenschaft des Webservers) ab. Auf dieses Array kann mittels der Methode "GetFormVars" dann zugegriffen werden.

Ein "Snapin" ist ein Begriff von mir und ist in etwa ein "Modul". Jedes Modul hat seinen eigenen Datenpfad und damit ein Verzeichnis, in dem sich die zugehörigen Tabellen befinden.

Die Parameter

Wir benötigen 5 Parameter für unseren Webserver. Dies wären
Name Bedeutung
SQL Der zu verarbeitende SQL-Befehl als String
Username Name des Benutzers (minimale Sicherheitsfunktion)
Password Passwort des Benutzers (minimale Sicherheitsfunktion)
MaxBufferSize Maximale Größe der zu sendenden Ergebnisdatei in Byte
Snapin Modulname

Diese Parameter übergeben wir wie oben beschrieben von der Client - Applikation (VFP-Programm !! Dies könnte auch ein Browser sein, was aber in unserem Fall keinen Sinn macht, da unser COM-Server binäre Daten und kein HTML zurückliefert) http://localhost/scripts/devcon.dll/devcon.devcon.process?method=ipsql~ipsqlexec&SQL=Select+%2A+
FROM+veranstaltungen&Username=MeinName&Password=devcon&Snapin=veranstaltungen

Dieser String ist URL-Encoded. D.h., alle Leerzeichen werden durch ein "+" ersetzt und alle Zeichen ungleich A-Z oder Ziffer durch "%", gefolgt vom Hex-ASCII-Wert des Zeichens (zB: %2A für "*").

Die Process-Methode ist in meiner Klassenbibliothek die allgemeine Einsprungmethode, die z.B. das Array aFormVars füllt. Der erste Parameter "Method" beinhaltet den Aufruf der SQL-Verarbeitung (Methode ipsqlexec im Modul ipsql).

Die Methode Ipsqlexec (Server)

Diese Methode übernimmt die Verarbeitung des SQL-Befehles:

Zunächst lesen wir die übergebenen Parameter aus

    ...
    WITH oWebApp
       lcFullSQL   = .GetFormVar("Sql")
       lcSQL       = LOWER(LEFT(lcFullSQL,10))
       lnMaxLength = VAL(.GetFormVar("MaxBufferSize"))
       lcUsername  = ALLTRIM(.GetFormVar("Username"))
       lcPassWord  = ALLTRIM(.GetFormVar("Password"))
       lcSnapin    = ALLTRIM(.GetFormVar("Snapin"))
    ENDWITH

Wir überprüfen, ob das übergebene Modul vorhanden ist und ermitteln den Tabellenpfad. Treten Fehler auf, geben wir diese als Klartext "Fehler:" + Fehlermeldung zurück

      loSnapIn = EVAL("oWebApp.o"+lcSnapin)
      IF TYPE("loSnapin") #"O"
         oWebApp.oHtml.AppendHtml("Fehler: Snapin nicht vorhanden.")
         oWebApp.lError = .T.
         RETURN
      ENDIF
      lcDataPath = loSnapin.cDataPath
      IF EMPTY(lcDataPath)
         oWebApp.oHtml.AppendHtml("Fehler: Datenverzeichnis "+lcApp ;
                                 +" ist nicht vorhanden.")
         oWebApp.lError = .T.
         RETURN
      ELSE
         THIS.cDataPath = lcDataPath
      ENDIF
 

Als nächstes überprüfen wir anhand der übergebenen Usernamen und Passwort in der Usertabelle, ob die Berechtigung zur SQL-Verarbeitung besteht

      *-- Überprüfung des Usernamen/Passwort
      lcUserFile = ADDBS(oWebApp.oIpSql.cDataPath) + "user.dbf"
      lcSelect = "SELECT * FROM " + lcUserFile + ;
                  " WHERE UPPER(ALLTRIM(User)) =='" + ;
                  UPPER(ALLTRIM(lcUserName)) +"' INTO CURSOR tmpUser"
      &lcSelect
      IF _TALLY > 0
         IF ALLTRIM(tmpUser.password) # lcPassword
            oWebApp.oHtml.AppendHtml("Fehler: User "+lcUsername+;
                                    " ist nicht autorisiert.")
            oWebApp.lError = .T.
            RETURN
         ENDIF
      ELSE
         oWebApp.oHtml.AppendHtml("Fehler: User "+lcUsername+;
                                 " ist dem System nicht bekannt.")
         oWebApp.lError = .T.
         RETURN
      ENDIF
      IF USED("tmpUser")
         USE IN tmpUser
      ENDIF
       

Nun checken wir, ob überhaupt ein SQL-Befehl übergeben wurde, und ob dieser erlaubt ist

      IF EMPTY(lcSQL)
         oWebApp.oHtml.AppendHtml("Fehler: Kein SQL-Kommando zu verarbeiten.")
         oWebApp.lError = .T.
         RETURN
      ENDIF
       
      lnAt = AT(" ",lcSQL)
      lcCommand = LEFT(lcSQL,lnAt - 1)
       
      IF AT(UPPER(lcCommand),UPPER(THIS.cAllowedCommands)) = 0
         oWebApp.oHtml.AppendHtml("Fehler: " + lcCommand + ;
                                 " ist nicht erlaubt oder falsch.")
         oWebApp.lError = .T.
         RETURN
      ENDIF
       
      IF lcSQL = "select"
         lcFullSQL = lcFullSQL + " INTO TABLE " + lcOrigFilename
      ENDIF
       
      IF lcSQL # "select" AND lcSQL # "insert" AND lcSQL # "update" AND ;
         lcSQL # "delete" AND lcSQL # "create"
         oWebApp.oHtml.AppendHtml("Fehler: Nur SQL-Kommandos sind erlaubt.")
         oWebApp.lError = .T.
         RETURN
      ENDIF
       
      lcOrigAlias = "wm_"+SYS(2015)
      lcOrigFileName = oWebApp.cTempPath + lcOrigAlias
      lcOldDir = ADDBS(SYS(5)+CURDIR())

Jetzt wird es spannend ! Wir wechseln in das Datenverzeichnis und führen den SQL-Befehl aus. Kommt es zu einem Fehler, so wird eine Fehlermeldung erzeugt. Anschließend wechseln wir in das Applikationsverzeichnis zurück

      CD &lcDataPath
      ON ERROR THIS.SqlError(lcFullSql,MESSAGE())
      &lcFullSql
      lcReccount = ALLTRIM(STR(_TALLY))
      ON ERROR
      CD &lcOldDir

Alle SQL-Befehle außer SELECT und CREATE liefern keinen Cursor zurück. Hier geben wir "OK: ", gefolgt von der Anzahl der bearbeiteten Datensätze zurück

      if lcSQL # "select" and lcSQL # "create"
         oWebApp.oHtml.AppendHtml("OK: "+lcReccount)
         RETURN
      ENDIF

Zum Schluss nehmen wir unseren Cursor, schreiben ihn als DBF in das temporäre Verzeichnis, bilden aus DBF und FPT eine Datei (siehe nächstes Kapitel : EncodeDBF) und geben diese als Ergebnis zurück.

Tip: Bei größeren Datenmengen empfiehlt es sich, die Ergebnis-Datei per ZIP (z.B. Dynazip) 
zu packen ! Dies führt zu einer erheblichen Reduzierung der Übertragungszeiten !!

      lcResultAlias = Alias()
      lcNewName = "wm_"+SYS(3)
      lcFileName = oWebApp.cTempPath + lcNewName + ".dbf"
       
      SELECT * from (lcOrigAlias) INTO DBF (lcFileName)
       
      USE IN (lcNewName)
      USE IN (lcResultAlias)
       
      lcFileText = THIS.EncodeDBF(lcFileName,IIF(FILE(ForceExt(lcFileName,".FPT"),.T.,.F.))
       
      ERASE (ForceExt(lcFilename,"*"))
      ERASE (ForceExt(lcOrigFilename,"*"))
       
      IF EMPTY(lcFileText)
         oWebApp.oHtml.AppendHtml(;
               "Fehler: Datei konnte nicht verpackt werden .")
         oWebApp.lError = .T.
         RETURN
      ENDIF
      IF LEN(lcFileText) >= lnMaxLength
         oWebApp.oHtml.AppendHtml("Fehler: Datei ist zu gross zum senden.")
         oWebApp.lError = .T.
         RETURN
      ENDIF
       
      oWebApp.oHtml.AppendHtml(lcFileText)

EncodeDBF + DecodeDBF

Da es bei unserem Ergebnis zwei Dateien geben kann (DBF + FPT), wir aber nur ein Ergebnis zurückliefern können, müssen wir diese zusammenfassen zu einem (binären) String. Hierzu benutzen wir folgende Konvention:

Ergebnisstring = Header + FILETOSTR(dbf-Datei) + FILETOSTR(fpt-Datei)

Der Header setzt sich wie folgt zusammen:

Header = 5 Byte Kennzeichen ("wmDBF") + 40 Byte Name der DBF + 10 Byte Länge der DBF +
40 Byte Name der FPT + 10 Byte Länge der FPT

Gibt es keine FPT-Datei, so werden hier 50 Leerzeichen angefügt

Dieser Ergebnisstring wird vom Webserver an die Clientapplikation zurückgesendet, welcher dann mit der entsprechenden "DecodeDBF"-Methode aus diesem String wieder 2 Dateien im temporären Verzeichnis macht

Die Client-Komponente

Für unseren Client (VFP-Programm) benötigen wir eine eigene Klasse, die die Methoden zum Senden und Empfangen von Daten über die Windows API-Funktionen zur Verfügung stellen. An dieser Stelle gehört der besondere Dank Rick Strahl, der in seinem Buch "Internet Applications with VFP 6.0" diese Routinen beschreibt. Sie ermöglicht es genau wie ein Browser einen http-Request abzusetzen und das Ergebnis in einem String wieder auszulesen.

Damit können wir unsere SQL-Abfrage an den Webserver senden (http://localhost/scripts/devcon.dll .....) und wir erhalten die vom Webserver zusammengepackten Daten in unserem Puffer wieder zurück. Anschließend brauchen wir nur noch DBF und FPT wieder extrahieren und die Tabelle öffnen. Das ist schon alles !!!!

Die Methode ipSqlExec (Client)

Diese Methode führt das Senden der SQL-Anfrage, und das Auspacken des Ergebnisses aus.

      LPARAMETER tcSql
      # define AMPH '&'
      LOCAL lnSize, lnBuffer, lnResult

Initalisierung einiger Variablen und Löschen des alten Ergebnisses

      IF FILE(THIS.cSqlCursor)
         ERASE (forceext(THIS.cSqlCursor,"*"))
      ENDIF
       
      THIS.lError = .F.
       
      tcSql=IIF(type("tcSql")="C",tcSql,THIS.cSQL)

Gibt es einen Ergebniscursor ?

      IF !INLIST(UPPER(tcSql),"SELECT","CREATE")
         llNoResultSet = .T.
      ELSE
         llNoResultSet = .F.
      ENDIF

Wir verbinden uns mit dem Web-Server ....

      lnResult = THIS.HTTPConnect(THIS.cServer,THIS.cUsername,THIS.cPassWord)
       
      IF lnResult # 0
         THIS.nError = lnResult
         THIS.lError = .T.
         RETURN -1
      ENDIF  

Hier wird der Parameter-String zusammengebaut und der Ergebnis-Pufferbereich initialisiert

      lcParams = "Method=ipsql~ipsqlexec" + AMPH +;
               "SQL=" + THIS.UrlEncode(tcSql) + AMPH +;
               "MaxBufferSize=" + LTRIM(STR(THIS.nMaxBufferSize)) + AMPH +;
               "Username=" + THIS.UrlEncode(ALLTRIM(THIS.cUsername)) + AMPH +;
               "Password=" + THIS.UrlEncode(ALLTRIM(THIS.cPassword)) + AMPH +;
               "Snapin=" + THIS.UrlEncode(ALLTRIM(THIS.cSnapin))
              
      lcbuffer = SPACE(THIS.nMaxBufferSize)
      lnSize = 0

Jetzt schicken wir die Abfrage ab und überprüfen das Ergebnis

      lnResult = THIS.HTTPGetEx(lcParams,THIS.cIsApiPath,@lcBuffer,@lnSize)
       
      *-- Fehler, wenn nichts zurückgekommen ist
      IF lnResult # 0
         THIS.nError = lnResult
         THIS.lError = .T.
         RETURN -2
      ENDIF  
       
      *-- Fehler auf dem Server aufgetreten
      IF lcBuffer = "Fehler"
         THIS.cErrorMsg = ALLTRIM(SUBSTR(lcBuffer,8))
         THIS.nError = -1
         THIS.lError = .T.
         RETURN -5
      ENDIF

UPDATE und INSERT und DELETE liefern keine Datensätze zurück, geben aber Informationen über die Anzahl der betroffenen Datensätze zurück

      IF llNoResultSet
         DO CASE
               CASE EMPTY(lcBuffer)
                  RETURN 0
               CASE ATC("OK:",lcBuffer) > 0
                  RETURN VAL(SUBSTR(lcBuffer,4))
               OTHERWISE
                  RETURN -3
            ENDCASE
      ELSE
         *-- Keine Daten trotz SELECT oder CREATE
         IF EMPTY(lcBuffer)
            THIS.cErrorMsg = "Keine Daten zurückerhalten..."
            THIS.nError = -1
            THIS.lError = .T.
            RETURN -4
         ENDIF
      ENDIF

Die im Buffer enthaltene Datei(en) werden ausgepackt und die Anzahl der selektierten Daten zurückgegeben

      lcFileName = ForceExt(ADDBS(ALLTRIM(THIS.cTempPath))+;
                   ALLTRIM(SUBSTR(lcBuffer,6,40)),"dbf")
       
      IF !THIS.DecodeDbf( lcBuffer, lcFileName)
         THIS.cErrorMsg = "Konnte heruntergeladene Datei nicht dekodieren"
         THIS.nError = -1
         THIS.lError = .T.
         RETURN -6
      ENDIF
       
      *-- Tabelle unter dem Namen cSQLCursor öffnen
      USE (lcFileName) ALIAS vImportCursor
      SELECT * FROM vImportCursor INTO TABLE (THIS.cSQLCursor)
      lnRecCount = _TALLY
       
      USE IN vImportCursor
      ERASE (forceext(lcFileName,"*"))
       
      RETURN lnRecCount

vorheriger Vortrag D-RAWD

zur Übersicht der Gruppe WEB

nächster Vortrag D-BIZT

 

dFPUG c/o ISYS GmbH

Frankfurter Str. 21 b

 

D-61476 Kronberg

per Fax an:

+49-6173-950903

oder per e-Mail an:

konferenz@dfpug.de

Texte, Grafiken und Inhalt: ISYS GmbH