Session D-QUER

Query-By-Form: Datenbankunabhängig und Flexibel suchen

Nathalie Mengel
INDISoftware GmbH


Einleitung

In Visual FoxPro lassen sich Datenzugriffe hervorragend einfach über ControlSourcen steuern, die sich auch noch einfach in der grafischen Entwicklungsumgebung zuordnen lassen. Wenn man mit Views arbeitet, statt direkt auf Tabellen aufzusetzen, lässt sich das Ganze auch noch schnell und einfach auf Client/Server-Datenbanken umstellen. Allerdings kann man zwar Views parametrisieren, aber leider die Selektionskriterien eines Views nicht zur Laufzeit ändern. Die SQL-Eigenschaft des Views ist schreibgeschützt, und somit kein late-binding der Abfrage möglich. Diese Session zeigt eine Möglichkeit, die Vorteile der grafischen Entwicklungsumgebung zu nutzen und trotzdem zur Laufzeit wirklich nur die Daten zu holen, die man benötigt. Dies funktioniert sowohl mit VFP-Datenbanken, als auch beim Zugriff auf SQL Server Datenbanken. Zur Session gibt es ein Beispielprojekt auf der Begleit-CD, in dem der Zugriff auf Remote-Datenbanken am Beispiel von Access gezeigt wird.

Query-By-Form

Was ist Query-By-Form?

Mit Query-By-Form (QBF) werden Abfragen vom Benutzer selbst zur Laufzeit grafisch gestaltet. Der Benutzer kann seine Suchkriterien direkt in die Form, mit der er gerade arbeitet, eintragen, oder über eine Suchmaske Verknüpfungen erstellen. Dabei kann er in der gewohnten Umgebung mit den gewohnten Controls arbeiten (Textfelder, Comboboxen), während der Programmierer wie gewohnt mit der VFP-Datenumgebung arbeitet. Damit hat der Benutzer die volle Kontrolle über die Daten, die er bearbeiten will. Außerdem wird tatsächlich nur die die Datenmenge gezogen, die benötigt wird. Das mag vielleicht bei einfachen Tabellenzugriffen unter VFP-Datenbanken noch nicht so notwendig erscheinen. Sobald aber große Datenmengen mehrerer Tabellen in einer Abfrage zusammengeführt werden sollen, oder sobald auf Remote-Datenbanken zugegriffen werden soll, macht sich der Geschwindigkeitsvorteil deutlich bemerkbar.

Wie funktioniert Query-By-Form?

Bei QBF wird ein View in die Datenumgebung der Form eingebunden, der die benötigten Felder selektiert, aber über keine WHERE-Klausel verfügt. Dieser View muss ohne Daten geladen werden. So sind die ControlSourcen der Eingabefelder gesetzt, ohne dass Datemengen geladen werden. Zur Laufzeit wird dann die Selektionsbedingung durch Benutzereingabe bestimmt, von einem Parser interpretiert und der View mit der entsprechenden WHERE-Klausel neu erstellt. So kann über jedes beliebige Feld selektiert werden, ohne dass die Bedingungen im Vorfeld festgelegt werden müssten.

Was wird für QBF benötigt?

Verschiedene Voraussetzungen müssen gegeben sein, wenn man QBF benutzen möchte. Diese sind:

  • Ein View, über den die Daten geholt werden
  • Eine lokale Datenbank für jeden Benutzer
  • Eine zentrale Datenbank für alle Benutzer
  • Ein SQL-Parser
  • Eine Form, in der die Suchbedingungen eingegeben werden

Warum Views benutzen?

Anfänglich mag es dem FoxPro-Programmierer etwas umständlich vorkommen, Views zu benutzen, weil doch gerade die schnellen Datenbankroutinen von FoxPro hervorragende Tabellenzugriffe bereitstellen, und weil es eben doch so bequem ist, direkt auf Tabellen zu arbeiten. Aber genau die gewohnten FoxPro-Zugriffe wie etwa LOCATE lassen sich mit Views durchführen, während Sie den Vorteil nutzen können, Programme nahezu datenbankunabhängig zu entwickeln. Die Vorteile von Views?

  • Mit Views können Sie mengenbasiert auf Ihre Daten zugreifen. Damit wird das Netzwerk entlastet, es stehen immer nur die Daten im Zugriff, die auch benötigt werden.
  • Views werden in der Datenbank gespeichert. Das bedeutet, daß sie auf mehrere Tabellen gleichzeitig (z.B. mit einer 1:n Beziehung) zugreifen können, ohne extra im Programm Relationen setzen zu müssen.
  • Sie haben mit einem View nicht nur eine Sicht über mehrere Tabellen, sondern können auch mehrere Tabellen auf einen Schlag gleichzeitig aktualisieren.
  • Views sind robust und können unabhängig vom Programm in der Entwicklungsumgebung getestet werden.
  • Views sind leicht skalierbar. Selbst wenn Sie heute noch nicht die absolute Notwendigkeit sehen wollen, morgen wird es vielleicht Ihr Kunde verlangen...

Warum eine lokale und eine zentrale Datenbank?

Für jeden Benutzer eine Datenbank? Das mag Ihnen jetzt vielleicht übertrieben vorkommen. Ist es aber nicht! Warum? Wenn ein Benutzer die Selektion durchführt, wird der entsprechende View neu erstellt. Damit wird er unter den neuen Bedingungen in der Datenbank erstellt, und ist nur noch so vorhanden. Würde nun ein anderer Benutzer auf denselben View in derselben Datenbank zugreifen, so würden die Selektionskriterien gar nicht mit dem übereinstimmen, was dieser Benutzer erwartet. Deshalb benötigt jeder seine eigene Datenbank, in welche die eigenen View-Definitionen geschrieben werden.

Und wozu dann noch die zentrale Datenbank? Schliesslich wollen wir den View nicht nur benutzen, um Daten zu holen, sondern auch, um die Daten zu aktualisieren. Dafür müssen die Aktualisierungskriterien des Views korrekt gesetzt werden. Das heißt, dass wir sowohl die Update-fähigen Tabellen und Felder, als auch sämtliche benötigten Schlüsselfelder und Update-Names festlegen müssen. Dies geschieht nicht automatisch bei der Erzeugung eines Views. Wir nutzen nun die Definition des Views, so wie er in der zentralen Datenbank vorhanden ist, als Schablone für unseren View, den wir in der lokalen Datenbank erstellen. Die Eigenschaften werden aus der Original-Viewdefinition ausgelesen und für unseren neu definierten View gesetzt. Da die lokale Datenbank bei so vielen Schreibzugriffen unter Umständen manchmal leiden kann, haben wir so die Möglichkeit, die lokale Datenbank einfach bei Programmstart wegzuwerfen, neu zu erstellen, und trotzdem auf korrekte Einstellungen über die zentrale Datenbank zugreifen zu können. Sollten dann doch einmal Änderungen an der Datenbank durchgeführt werden müssen - etwa wegen Änderung der Aktualisierungskriterien - braucht dies nur einmal zentral zu geschehen.

QBF - Das Beispiel

Die VFP-Tabellen

Wir werden auf einen Teil der Testdatenbank aus dem VFP-Tastrade-Beispiel zugreifen. Die Tabellen sind: Orders (Bestellungen), Orditems (Bestellpositionen) und Products. Aus allen Tabellen sollen Daten in einen View selektiert werden, die Daten aus Orders und Orditems sollen aktualisiert werden können, die Daten aus Products nicht.

Die Remote-Datenbank

Der Einfachheit halber arbeiten wir in diesem Beispiel mit Access als Remote-Datenbank. Grundsätzlich funktioniert das hier Gezeigte aber auch mit jeder SQL-Datenbank, auf die über ODBC zugegriffen werden kann. Die Access-Datenbank ist einfach als Export der VFP-Datenbank entstanden. Hüten Sie sich bitte deshalb davor, unseren View ohne WHERE-Bedingung auf die Access-Datenbank loszulassen. Was unter VFP fast ohne Zeitverlust möglich ist, läßt wegen der etwas größeren Datenmenge beim Access-Zugriff über ODBC jeden Geduldsfaden reißen.

Der View

Unser View heißt dquer01, sein Select sieht folgendermaßen aus:

SELECT Orders.order_id as order_id1, Orders.cust_id, Orders.to_name, Orders.order_date, Orditems.line_no, Orditems.order_id as order_id2, Orditems.product_id, Orditems.quantity, Orditems.price, Products.product_id, Products.prod_name ;
FROM  ((orders  ;
   INNER JOIN orditems ON  Orders.order_id = Orditems.order_id );
   INNER JOIN products ON  Products.product_id = Orditems.product_id  );
ORDER BY cust_id

Folgende Aktualisierungskriterien sind gesetzt:

DBSETPROP("dquer01", "view","sendupdates",.T.)
DBSETPROP("dquer01", "view","tables","Orders, Orditems, Products")
 
DBSETPROP("dquer01.order_id1" ,"field","keyfield",.T.)
DBSETPROP("dquer01.order_id1" ,"field","updatable",.T.)
DBSETPROP("dquer01.order_id1" ,"field","updatename","orders.order_id")
DBSETPROP("dquer01.order_id2" ,"field","keyfield",.T.)
DBSETPROP("dquer01.line_no"   ,"field","keyfield",.T.)
DBSETPROP("dquer01.to_name"   ,"field","updatable",.T.)
DBSETPROP("dquer01.to_name"   ,"field","updatename","orders.to_name")
DBSETPROP("dquer01.order_date","field","updatable",.T.)
DBSETPROP("dquer01.order_date","field","updatename","orders.order_date")
DBSETPROP("dquer01.quantity"  ,"field","updatable",.T.)
DBSETPROP("dquer01.quantity"  ,"field","updatename","orditems.quantity")
DBSETPROP("dquer01.price"     ,"field","updatable",.T.)
DBSETPROP("dquer01.price"     ,"field","updatename","orditems.price")

Wir können also mit diesem View Felder aus den Tabellen Orders und Orditems aktualisieren, die Tabelle Products wird nur zur Anzeige hinzugefügt.

Der View für die VFP-Datenbank wird folgendermaßen erstellt:

create view dquer01 as (hier kommt der SELECT-String hin)

Der Remote-View für die Access-Datenbank wird folgendermaßen erstellt:

create view dquer01 remote connection conn1 as (hier kommt der SELECT-String hin)

Die Connection

Für den Remote-Zugriff benötigen wir eine Connection. Diese basiert auf einer Datasource dquer01, die wir über die Systemeinstellungen - ODBC generieren:

Die Connection selbst wird entweder zur Designzeit über die grafische Datenbankbearbeitung in VFP oder zur Laufzeit wie folgt erzeugt:

CREATE CONNECTION conn01 DATASOURCE dquer01

Benutzer, Passwort und Datenbank brauchen für unser Beispiel nicht angegeben zu werden.

Die Form

In unserem Beispiel dient die Bearbeitungsform auch zur Eingabe der Selektionsbedingungen. Über einen Button wird zwischen Eingabe- und Selektionsmodus gewechselt, die Bedingungen können direkt in die Controls eingegeben werden, in denen auch die Bearbeitung stattfindet. Um dem Benutzer den Unterschied zwischen Eingabe- und Selektionsmodus besser zu verdeutlichen, werden die Felder im Seletkionsmodus andersfarbig dargestellt, wobei noch zwischen numerischen und alphanumerischen Feldern unterschieden wird. Die Form wird zunächst ohne Daten geöffnet, der Benutzer kann dann genau die Datenmenge selektieren, die er benötigt.
 

Wo wird der View gepflegt?

In unserem Beispiel wird der Select des Views im Init-Event der Form festgelegt und in einer Eigenschaft der Form gespeichert. Die Aktualisierungskriterien werden über die Datenbank ermittelt. Man könnte sich auch vorstellen, dass man den View einmalig in der Datenbank erzeugt, die Definition über GENDBC extrahieren läßt und das Ergebnis jeweils zur Laufzeit einliest.

Das SQL-Objekt

Auf der Form liegt ein SQL-Customobjekt, das die eigentlichen Datenbankzugriffe ausführt. Wir haben es im Beispiel auf die notwendigen Funktionalitäten reduziert. Im echten Projekt würde so ein Objekt sämtliche SQL-Zugriffe ausführen und als Basis für eventuell datenbankabhängige SQL-Objekte dienen (z.B. für SQL-Passthrough).

Das SQL-Objekt verfügt über die zur Erstellung des Views notwendigen Eigenschaften. Diese werden im Beispiel einfach direkt in der Entwicklungsumgebung eingetragen, wenn nicht programmatisch gefüllt. Man könnte sich auch vorstellen, diese z.B. über eine ini-Datei einzulesen. Unsere benötigten Eigenschaften sind:

Eigenschaft

Beschreibung

aFieldProp

Array für die Feldeigenschaften des Views. Programmatisch gefüllt

aViewProp

Array für die Vieweigenschaften. Programmatisch gefüllt

cConnectionName

Name der Connection.

cDatabase

Name der VFP-Datenbank

cDatabaseType

Typ der Datenbank: VFP oder ACCESS

cDatasourceName

Name der Datasource zum Erstellen der Connection

cPathDatabase

Pfad für die VFP-Datenbank

lNetzwerk

Schalter, ob es sich um eine Netzwerk- oder Einzelplatzanwendung handelt. Für Einzelplatzanwendungen wird nur eine Datenbank benötigt.

Den Suchmodus starten

Die QBF-Methode liegt auf der Form. Sie wird bei Betätigung des Such-Buttons angestoßen und ist in zwei Teile gegliedert.

Zunächst wird geprüft, ob sich die Form zur Zeit im Eingabemodus oder im Suchmodus befindet. Wenn die Form im Eingabemodus ist (Ausgangsstellung), wird die Methode QBFBefore() aufgerufen

QueryByForm()

...

IF thisform.uType # "QUERYBYFORM"

 

   lnRet = thisform.QBFBefore()

 

   *-- und beenden, weil jetzt erst die Eingabe kommt.

   thisform.lockscreen = .F.

   RETURN lnRet

ENDIF  

Die Methode QBFBefore bereitet die Selektion vor und bringt die Form in den Selektionsmodus:

QBFBefore()

Zunächst werden die Eigenschaften sämtlicher Controls, die unseren View als Controlsource haben könnten, gesichert, und zwar in dem Array aQBFFields der Form:

FOR EACH loOBj IN thisform.objects

 

   IF INLIST(UPPER(loObj.BaseClass), "TEXTBOX", "EDITBOX", "CHECKBOX",

      "GRID", "LISTBOX","COMBOBOX")

 

      lnSize = ALEN(thisform.aQBFFields,1) 

 

...Array prüfen und ggf. erweitern...

 

Array füllen:

      thisform.aQBFFields[lni,1] = loObj

      thisform.aQBFFields[lni,2] = loObj.enabled

Die Controls, in denen Suchbegriffe eingegeben werden können, werden für die Suche vorbereitet:

   IF INLIST(UPPER(loObj.BaseClass),"TEXTBOX","EDITBOX")

         lcFieldname = loObj.ControlSource

         IF !EMPTY(lcFieldName)

Das jeweilige Eingabefeld wird für die Suche gesichert...

                  thisform.aQBFFields[lni,3] = lcFieldName

                  thisform.aQBFFields[lni,4] = TYPE("&lcFieldName")

... die Signalfarbe für Sucheingabe gesetzt...

            thisform.aQBFFields[lni,5] = loObj.BackColor

            IF INLIST(thisform.aQBFFields[lni,4],"I","N","Y")

               loObj.BackColor = thisform.cQBFColorNum

            ELSE

               loObj.BackColor = thisform.cQBFColor

            ENDIF

... die Controlsource wird entzogen...

            loObj.ResetToDefault("Controlsource")

            loObj.Value = ""

... und das Control für die Eingabe enabled.

                                               loObj.Enabled = .T.

Controls, in denen keine Suchbegriffe eingegeben werden sollen und solche, die über keine Controlsource aus unserem View verfügen, werden geleert:

         ELSE

            thisform.aQBFFields[lni,3] = ""

            thisform.aQBFFields[lni,4] = ""

            thisform.aQBFFields[lni,5] = ""

            loObj.Enabled = .F.

         ...

Dann werden die Blättern-Buttons disabled und die Form in den Suchmodus gesetzt:

thisform.cmdBack.Enabled = .F.

thisform.cmdNext.Enabled = .F.

thisform.cmdQBF1.Caption = "Suche starten"

thisform.uType = "QUERYBYFORM"

Die Suche durchführen

Wenn die Form im Suchmodus ist, kann der Benutzer seine Suchbedingungen eingeben. Die erneute Betätigung des Suchbuttons löst die eigentliche Suche aus:

QueryByForm()

Für jedes Objekt der Form wird geprüft, ob der Benutzer hier einen Bedingung eingegeben hat:

FOR EACH loOBj IN thisform.objects

   IF INLIST(UPPER(loObj.BaseClass),"TEXTBOX","EDITBOX")

      lcFieldName = ""

      IF !EMPTY(loObj.Value)

         luValue = ALLTRIM(loObj.Value)

...

Das entsprechende Objekt aus dem QBF-Array wird gesucht. Wenn es gefunden wurde, werden der Feldname der Controlsource aus dem QBF-Array geholt:

   lcFieldname = thisform.aQBFFields[lni,3]

   lcTyp = thisform.aQBFFields[lni,4]

Je nachdem, welchen Typ das gesuchte Feld hat, wird der eingegebe Wert aufbereitet:

   DO CASE

      CASE lcTyp = "C"

         lcOp1 = " = '"

         lcOp2 = "'"

         IF LEFT(luValue,1) = "%" OR RIGHT(luValue,1) = "%"

            lcOp1 =  " LIKE '"

         ENDIF

(... entsprechend für numerische und Datumsfelder...)

Die Bedingung wird zusammengesetzt:

lcBed = IIF(EMPTY(lcBed)," ",lcBed + " AND ") + lcFieldName + lcOp1 + luValue + lcOp2

Dann wird die Bedingung in den SQL-String eingefügt:

l

lnWhere = ATC("ORDER BY" , this.cSelect)

IF lnWhere = 0

   lcSelect = this.cSelect + " WHERE" + lcBed

ELSE 

   lcBeforeWhere = SUBSTR(this.cSelect, 1, lnWhere-1)

   lcAfterWhere  = SUBSTR(this.cSelect, lnWhere)

   lcSelect      = lcBeforeWhere + " WHERE " + lcBed + " " +;

                   lcAfterWhere

ENDIF

Das SQL-Objekt erstellt dann den View:

thisform.oSql.DataCreateView(lcSelect,this.cViewName, this.lRemote)

DataCreateView()

Die Methode erhält als Parameter den Select-String, den Viewnamen und einen Schalter, ob ein Remote-View erstellt werden soll:

LPARAMETERS tcSelect,tcViewName,tlRemote

 

lcSelect = "CREATE SQL VIEW " + ALLTRIM(tcViewName)

Wenn ein Remote-View erstellt werden soll, wird geprüft, ob die Connection vorhanden ist, diese ggf. erstellt und der Create-String um die Remote-Informationen erweitert:

IF tlRemote

   lcSelect = lcSelect + " REMOTE CONNECTION "

   lcSelect = lcSelect + this.cConnectionName

   this.CheckConnection()

ENDIF

Der Create-View Befehl wird zusammengebaut:

lcSelect = lcSelect + " AS " + tcSelect

Die Vieweigenschaften werden sie gesichert:

this.GetViewProp(tcViewName)

Der View wird in der lokalen Datenbank erstellt:

IF this.lNetzwerk

   lcOldDBC = DBC()

   SET DATABASE TO (this.cDatabase + "LOCAL")

ENDIF  

&lcSelect

Nach dem Erzeugen des Views werden seine Eigenschaften neu gesetzt:

this.SetViewProp(tcViewName)

Der View wird geöffnet:

USE (tcViewName) IN 0 NODATA

Nach der Suche

Nachdem die Suche erfolgreich ausgeführt wurde, wird wieder der Eingabemodus der Form hergestellt.

QBFAfter()

Die Eigenschaften der Controls werden aus dem QBF-Array wieder hergestellt:

...

loObj.Enabled = thisform.aQBFFields[lni,2]

IF !EMPTY(thisform.aQBFFields[lni,3])

   loObj.Controlsource = thisform.aQBFFields[lni,3]

ENDIF  

IF !EMPTY(thisform.aQBFFields[lni,3])

   loObj.BackColor = thisform.aQBFFields[lni,5]

ENDIF

Die Buttons werden wieder zurückgesetzt und die Form auf Eingabemodus gestellt.

thisform.cmdBack.Enabled = .T.

thisform.cmdNext.Enabled = .T.

thisform.cmdQBF1.Caption = "Suchbegriff eingeben"

thisform.Refresh()

thisform.uType = "STANDARD"

From Here...

Dies war ein Beispiel, Query-By-Form zu realisieren und so die Machbarkeit einfach zu zeigen. Als Erweiterungsmöglichkeiten könnte man sich zum Beispiel vorstellen, die Suche in einem eigenen Suchformular durchzuführen, die Feldeigenschaften nicht aus den Controlsourcen zu holen, sondern eigens zu speichern oder Eingaben über Combobxen zu realisieren. Auch das SQL-Objekt könnte noch erweitert werden. Für Fragen stehe ich Ihnen gerne unter Mengel@indisoftware.de zur Verfügung. Sie erreichen mich auch auf unserer Website www.indisoftware.de.