|
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
| 
