Die SOGWT-Generic-SQL Sprache bietet die Möglichkeit, SQL-Scripte zu schreiben, die auf allen unterstützten Datenbanken ablauffähig sind. Unterstützt werden z. Z.:
Microsoft SQL-Server 7.0 und höher
Microsoft SQL-Server CE
Die SOGWT-Generic-SQL Sytnax wird z. Z. von den folgenden Programmen verarbeitet:
hhsqldb, sogsql
Dabei sollte das Programm "sogsql" verwendet werden, um z. B. testweise eine SQL-Anweisung für eine bestimmte Datenbank zu erzeugen.
Die Erstellung der SQL-Befehle erfolgt in der normalen "Standard-SQL-Syntax". Es sind jedoch Erweiterungen Möglich. Diese Erweiterungen beginnen immer mit den Buchstaben "hh". Im folgenden sind diese Erweiterungen im einzelnen beschrieben:
Wird dem SQL-Commando ein '@'-Zeichen vorangestellt, werden auftretende Fehler zwar angezeigt, führen aber nicht zu einem Abbruch innerhalb einer SQL-Verarbeitung.
Dadurch können z. B. Tabellen oder Views "auf Verdacht" gelöscht werden, auch wenn nicht sicher ist, ob sie überhaupt existieren.
Steht das '@' Zeichen direkt vor einer insert-Anweisung, werden nur SQL-Fehler ignoriert, die durch ein Duplikat auf eindeutigen Keys entstehen.
Dadurch können Datensätze "auf Verdacht" eingefügt werden. Der SQL läuft dabei auch dann weiter, wenn der Datensatz schon existiert hat.
Kommentarzeilen können wahlweise mit folgenden Zeichen begonnen werden:
# Kommentarzeile ...
Das Ende einer SQL-Anweisung wird mit dem Zeichen ";" am Ende einer Zeile, oder mit der Buchstabenfolge "go" als einziger Inhalt einer Zeile definiert.
Beispiel 1:
select * from f010
where key_1>"01";
Beispiel 2:
select * from f010
where key_1>"01"
go
Innerhalb der GenericSql werden einfache und doppelte Anführungszeichen als Textkonstanten unterstützt. Soll ein Anführungszeichen selbst inhalt einer Textkonstanten sein, so muss es doppelt geschrieben werden.
Verwendet die Datenbank Unicode und enthält die SQL-Anweisung Unicodezeichen in einer Textkonstanten, wird die Textkonstante automatisch um den notwendigen N-Prefix erweitert.
Enthält die SQL-Anweisung die Zeichenfolge /*HHNOQ*/ wird die Umsetzung von Anführungszeichen und N-Prefixierung abgeschaltet.
Beipsiele:
-- einfache Anführungszeichen suchen
select * from dbcheck where txt1 = ''''
select * from dbcheck where txt1 = "'"
-- doppelte Anführungszeichen suchen
select * from dbcheck where txt1 = """"
select * from dbcheck where txt1 = '"'
In diesen Beispielen wird jeweils nach Datensätzen gesucht, die ein einfaches oder ein doppeltes Anführungszeichen im Feld txt1 enthalten.
/*HHNOQ*/ select "name1" from "f010" where "match" = 'AUTBREMEN'
In diesem Beispiel wird die Anführungszeichenbehandlung abgeschaltet. Dadurch können doppelte Anfphrungszeichen für die Maskierung von Feldnamen und einfache Anführungszeichen für die Definition von Textkonstanten verwendet werden.
Bitte bachten Sie, dass in diesem Fall auch die Definition und Unicode-Textkonstanten mittels N'xxx' manuell erfolgen muss.
hhsubstr(Feld,Start,Textlänge)
Die Anweisung hhsubstr liefert einen Teil eines Strings. Die Parameter definieren das Feld, die Startposition innerhalb dieses Feldes, und die Anzahl Stellen, die geliefert werden sollen.
Beispiel 1:
select hhsubstr(key_1,1,2) from f010;
hhcompare(Feld,Textlänge,Operator,Operant)
Die Anweisung hhcompare ermöglicht die effiziente Ausnutzung von Keys, wenn nur auf den ersten Teil eines Datenfeldes verglichen werden soll. Beispiel: Liefere alle Datensätze der Firma 1, und Nutze den Schlüssels auf Key_1:
Beispiel 1:
select * from f010 where hhcompare(key_1,2,=,"01");
Als Operant kann auch wieder z. B. ein hhsubstr verwendet werden.
hhset(Feld,Feldlänge,Start,Textlänge,Wert)
Die Anweisung hhset dient zur Veränderung von Teilen eines Datenfeldes innerhalb einer SQL Updateanweisung.
Beispiel 1:
update f010 set hhset(name1,30,1,2,"01"), hhupd=dbo.vacos_hhupd() where hhcompare(key_1,2,=,"01");
Wird "Feldlänge" verwendet, sollte "Feld" im Format "Tabelle.Feld" angegeben werden. Bei der Umsetzung wird dann geprüft, ob die angegebene Feldlänge auch tatsächlich mit der in der Datenbank für das Datenfeld definiert Feldlänge übereinstimmt.
Allerdings kann "Feldlänge" nun auch auf "0" gesetzt werden, da die SQL-Umsetzung diese Feldlänge nicht mehr verwendet.
Ist "Wert" als Textkonstante angegeben und ist die Länge der angegebenen Textkonstanten kleiner als "Textlänge", wird "Wert" auf "Textlänge" mit Blanks aufgefüllt.
Ist "Wert" als Textkonstante angegeben und ist die Länge der angegebenen Textkonstanten größer als "Textlänge", wird "Wert" auf "Textlänge" abgeschnitten.
Fpr SQL-Server wird für "hhset" die Funktion "Stuff" verwendet. Durch direkte Verwendung von "Stuff" können auch Zeichenketten unterschiedlicher Längen eingefügt werden.
Wirkt z.Z. nur unter Informix.
hhtabparam fügt die Datenbank zur Erstellung der Tabelle notwendigen Parameter aus der Steuerdatei ${PROJ}\etc\hhtabparam.txt ein. Die Steuerdatei hat folgenden Aufbau:
Tabelle AnzahlStartDatensätze AnzahlErwarteterDatensätzeTotal LockMode DBSpace
Mit der Anzahl der Datensätze, und der Informationen aus der Datei ${PROJ}\file\${Tabelle}_size.txt und ${PROJ}\file\${Tabelle}_size_indi.txt werden die Größen für die Tabelle, und den Erweiterungsbereich berechnet.
In der Datei ${PROJ}\file\${Tabelle}_size_indi.txt können Datenfelder und Keystrukturen beschrieben werden, die nicht im DD enthalten sind.
Lockmode kann "row" oder "page" sein. Leer oder "-" bedeutet: Nutze den Initialwert. (z.Z. "row").
DBSpace gibt den Namen des Informix-DBSpaces an, in dem die Tabelle angelegt werden soll. Leer oder "-" bedeutet: Nutze den Initialwert (gleiches DBSpace wie die Datenbank).
Beispiel 1:
hhtabparam(f003)
In etc\hhtabparam:
f003 100 200 row datadbs
Erzeugt unter Informix:
in datadbs
extent size XXX next size YYY lock mode row
Die Werte XXX und YYY errechnen sich aus dem Aufbau der Tabelle, über die Steuerdatei ${PROJ}\file\${Tabelle}_size.txt und ${PROJ}\file\${Tabelle}_size_indi.txt.
hhcreateindex( Tabelle, IndexNr, IndexFelder )
Erzeugt einen Index. Index Nummer 1 wird eindeutig erzeugt. Alle anderen Indices erlauben Duplikate.
Beispiel 1:
hhcreateindex( f010, 1, key_1 )
hhcreateindex( f010, 2, key_2 )
hhcreateindex( f010, 101, (firma,sa,konto) )
Erzeugt unter SQL-Server "null". Unter Informix nichts. Kann verwendet werden, um neue Tabellenfelder zu erzeugen.
Beispiel 1:
alter table f033 add anf_menge decimal(12,4) hhnull;
Datenbankspezifischer Datenfeldtyp für Datumsfelder. Unter Informix: "date" unter SQL-Server: "datetime".
Beispiel 1:
create table ...
verkaufs_datum hhdate,...
Datenbankspezifischer Datenfeldtyp für Textfelder. Standard: "char". Unter SQL-Server CE: "nvarchar"
Beispiel 1:
create table ...
name1 hhchar(10),...
Datenbankspezifischer Datenfeldtyp für Textfelder variabler Länge. Standard: "varchar". Unter SQL-Server CE: "nvarchar"
Beispiel 1:
create table ...
txt hhvarchar(200),...
Für SQL-Server wird hier die Anweisung "order by Feld" erzeugt. Für Informix nicht. Informix sortiert automatisch nach einem Key, wenn dieser in der where-Klausel angegeben wird. SQL-Server nicht. Umgekehrt dauert die Verarbeitung bei Informix länger, wenn "order by" angegeben wird. Bei SQL-Server nicht.
Beispiel 1:
select * from f010 where key_1 >= "0200000000" and key_1 <= "02999999999" hhorder(key_1);
hhrenametable( TableAlt, TableNeu )
Mit hhrenametable kann eine Tabelle umbenannt werden.
Beispiel 1:
hhrenametable( f010neu, f010 );
hhrenamecolumn( Table, ColumnAlt, ColumnNeu )
Mit hhrenamecoulmn werden Spalten innerhalb einer Tabelle umbenannt..
Beispiel 1:
hhrenamecolumn( f010, pz, plz );
hhstrtodec kann verwendet werden um einen Alphanumerischen Text in eine Zahl mit Nachkommastellen zu wandeln. Der Parameter len gibt die Anzahl der numerischen Stellen (incl. Nachkommastellen) an, der Parameter nk die Nachkommastellen.
Beispiel 1:
select daten1[2,5] / 100 prozent from f070 where ...
Kann allgemeingültig geschrieben werde:
select hhstrtodec( hhsubstr( daten1,2,4) , 4, 2 ) prozent from f070 where ...
hhintotemp leitet die Ausgabe eines selects in eine temporäre Tabelle um. hhintotemp muss direkt nach der Feldliste geschrieben werden.
Beispiel 1:
select key_1 hhintotemp(t1) from f010;
select * from hhtemptable(t1);
Mit hhtemptable kann auf eine temporäre Tabelle Bezug genommen werden.
Beispiel 1:
select key_1 hhintotemp(t1) from f010;
select * from hhtemptable(t1);
hhfield( Feld, Text ) oder hhfield( Feld )
Mit hhfield wird ein SQL-Einfügetext definiert. Auf diesen Text läst sich später Bezug nehmen, ohne ihn noch einmal schreiben zu müssen.
Beispiel 1:
select hhfield( konto, hhsubstr( key_1, 3, 8 ) from f010
group by hhfield(konto)
order by hhfield(konto)
Mit dieser Anweisung werden innerhalb einer "modify table" - Aneisung benötigte Klammern gesetzt.
Beispiel 1:
modify table t1 add hh( f2 char(10), f3 char(20) hh);
hhaltertable( Tabelle, Opcode und Parameter ... )
Mit der Anweisung hhaltertable kann der Aufbau einer Tabelle verändert werden. hhaltertable kann dabei sowohl Datenfelder zu der Tabelle hinzufügen, als auch verändern und entfernen.
Bietet die Datenbank keine entsprechende Operation, wird die jeweils im datenbankabhängigen Teil von hhsqldb implementierte Funktion "hhmodtab" aufgerufen, die die entsprechende Operation durchführt. Hier wird dann z. B. eine neue Tabelle angelegt, und die Daten von der original Tabelle in die neue Tabelle umgeladen. Es wird dabei versucht, alle alten Indices und Initialwert-Werte wieder herzustellen.
Folgende Opcodes sind möglich:
Legt ein neues Feld an. Alternativ kann auch "dbadd" oder "hhadd" verwendet werden.
addwithdef Feld Datentyp Initialwert
Legt ein neues Feld mit einem Initialwert Datenwert an.Alternativ kann auch "dbaddwithdefault" oder "hhaddwithdefault" verwendet werden.
Entfernt ein Feld aus der Tabelle. Alternativ kann auch "dbdrop" oder "hhdrop" verwendet werden.
Setzt den neuen Datentype eines Feldes aus der Tabelle. Alternativ kann auch "dbmodify" oder "hhmodify" verwendet werden.
Setzt den neuen Datentype eines Feldes aus der Tabelle.
Beispiel1:
hhaltertable( f010, add, f1, char(10), drop, f2, drop, f3, modify, f4, char(5), addwithdef, f5, char(1), "b")
hhgrant( Type [,Tabelle [,Option]] )
Mit der Anweisung hhgrant werden die Rechte auf der Datenbank, oder die Rechte auf den Tabellen so eingestellt, das die SOGWT-Anwendungen darauf zugreifen können.
Sind keine Datenbankpasswörter über das Programm hhdbpwd hinterlegt, erfolgt eine Rechteszuweisung auf "public", wodurch alle Benutzer Zugriffsrechte erhalten. Sind Passwörter hinterlegt, werden die Rechte so eingestellt, das nur die über hhdbpwd hinterlegten Accounts Zugang zur Datenbank bekommen. Dadurch ist gewährleistet, das z. B. über ODBC nur lesende Zugriffe möglich sind, über die SOGWT-Programme aber jede Veränderung durchgeführt werden kann.
Ist als Type "db" angegeben, werden die Datenbankrechte zugewiesen.
Ist "tab" angegeben, werden die Tabellenrechte für die als Parameter anzugebende Tabelle/View eingestellt. Bei "tab" kann Zusätzlich die Option "ro" angegeben werden. Dies sorgt dafür, das alle Benutzer nur lesenden Zugriff auf die Tabelle/View erhalten.
Beispiel:
hhgrant(db);
hhgrant(tab,f010);
hhgrant(tab,f910,ro);
Mit der Anweisung hh+ können alphanumerische Teilfelder aneinandergehängt werden..
Beispiel:
select feld1 hh+ feld2 as feld3 where ...;
Unter SQL-Server wird das Zeichen "+" generiert, unter Informix "||".
Mit "hhsqgen" können Anteile von SQL-Kommandos über Konfigurationsdateien erzeugt werden. Dies wird zur Zeit dafür genutzt, die bei einem Client-Update zu übertragenden Datenmenger festzulegen.
Der Aufruf sieht wie folgt aus:
hhsqgen( {Tabelle}, {Client} [, {SqlPrefix} ] );
Beispiel:
select count(*) from f070 where hhsqgen( f070, nbsto );
Als 3. Parameter kann noch ein SQL-Prefix angegeben werden, der erzeugt wird, wenn keine leere Anweisung ausgegeben wird:
select count(*) from f070 where firma=1 hhsqgen( f070, nbsto, and );
Die Konfiguration der Funktion erfolgt in 2 Stufen.
Es ist eine Konfigurationsdatei erforderlich, in der für den angegebenen Client (hier "nbsto") hinterlegt wird, welche Tabellen zu übertragen sind.
Die Konfigurationsdatei wird über die Variable HHSQGENPATH gesucht. Ist die Variable nicht gesetzt, erfolgt eine entsprechende Fehlermeldung.
Die Variable kann auch auf mehr als ein Verzeichnis gesetzt werden.
Der Name der Konfigurationsdatei in dem durch "HHSQGENPATH" definierten Verzeichnis muss dann lauten: "{Client}_cfg.txt", also z. B. "nbsto_cfg.txt", und hat folgenden Inhalt:
# Kommentar
{Tabelle}: {Ja/Nein}
Beispiel:
land:ja
pp: ja
pp.prog(104): nein
Hinweise: Bei der Festlegung von Tabellen kann es "Haupteinträge" und "Nebeneinträge" geben. In der Client-Konfigurationsdatei sollte nur entweder mit positiven, oder mit negative Haupteinträgen gearbeitet werden.
"Nebeneinträge", zu erkennen an dem "Punkt" in dem Namen, ermöglichen eine Unterstrukturierung eines Haupteintrages. So kann man z. B. dafür sorgen, dass generell alle Programmparameter übertragen werden, jedoch nicht der des "p104" (siehe Beispiel oben).
Naturgemäß sind "Nebeneinträge" zu ihrem Haupteintrag "entgegengesetzt" zu formulieren. Also "Haupteintrag = positiv", "Nebeneintrag = negativ" oder umgekehrt.
Die Nutzung eines "Nebeneintrags" ohne Erwähnung des "Haupteintrages" ist nicht möglich.
Also zweites wird eine Konfiguration der Tabellen und SQL-Anweisungen benötigt. Diese Konfiguration wird über die Variable "FRMCFGPATH" gesucht, und träge die festen Namen "hhsqgen_base.xml", "hhsqgen_proj.xml" und "hhsqgen_vorort.xml", Die Base-Variante wird von der Entwicklung im SOG ERP-Basisprojekt gepflegt, die "Proj" Variante enthält die Projektspezifischen Erweiterungen, die "Vorort" Variante enthält die relevanten Tabellen, die erst "Vorort" entstanden sind.
Aufbau dieser Dateien:
<Config>
<hhsqgen Name="drucker" Table="f070" Sql="sa=30" />
<hhsqgen Name="land" Table="f370" Sql="sa=9" />
<hhsqgen Name="pp" Table="f070" Sql="sa=1 and tab_x='020' and us_x='00'" />
<hhsqgen Name="pp.prog(p1)" Table="f070" Sql="sa=1 and tab_x='020' and us_x='00' and lfn_x='${p1}'" />
</Config>
Unter einem "Namen" kann nur genau ein Eintrag exsitieren. Er muss auch über die unterschiedlichen Konfigurationsdateien hinweg (Base, Proj und VorOrt) eindeutig sein. Im Zweifel gilt der zuerst gefundene Eintrag, also der Standard.
"Tabelle" legt fest, auf welche Datenbanktabelle sich die SQL-Anweisungen beziehen. "Tabelle" korrespondiert dabei mit dem Tabellen-Eintrag der "Generic-SQL-Anweisung" "hhsqgen".
Bei der Definition von "Nebeneinträgen" hier am Beispiel "pp" und "pp.prog(p1)" gezeigt, ist die Verwendung von Parametern möglich. Diese Parameter sollten dann in der SQL-Anweisung im Format "${Parametername}" Verwendung finden. Da SQL-Anweisungen erzeugt werden, ist nur die Verwendung von SQL-Datenfeldern möglich!
Die Funktionalität kann auch über das Programm "sogsqgen" einzeln aufgerufen werden.
Hintergrund:
Durch die Verwendung von "hhsqgen" kann die Konfiguration einer Client-Übertragung auf eine Art und Weise erfolgen, die auch eine nachträgliche Änderung der Tabellen und Datenstrukturen erlaubt.
Bitte beachten Sie, dass die Zeichen "<" und ">" innerhalb von XML als "<" und ">" geschrieben werden müssen.
Das Kommando hhdeletelocalfile wird als "internes Kommando" innerhalb von sogsql oder hhsqldb ausgeführt, und kann im Ablauf einer SQL-Abfrage, lokale Dateien löschen.
Für das Löschen von Dateien sind die normalen Benutzerrechte des aktuellen Benutzers notwendig.
hhdeletelocalfile( ${PROJ}\temp\x.txt );
Das Kommando hhfieldsoftable erzeugt eine sortierte Liste von Feldnamen einer Tabelle oder View.
hhfieldsoftable( OpCode, Tabelle );
Folgende OpCodes sind möglich:
Liste aller Felder erzeugen.
Liste aller Felder, die zu einem beliebigen Index gehören, erzeugen.
Es wird versucht, die Felder in einer vernüpftigen Reihenfolge zurückzugeben. So werden key_x Felder oder Primary-Index Felder an den Anfang der Reihenfolge gestellt. Wird eine SOG ERP Tabelle angezeigt wird versucht, Einzelfelder von Keys den key_x Feldern zuzuordnen.
Die Funktion hhmaxchar() liefert eine 10 Zeichen lange Zeichenkette von MaxChar-Zeichen der aktuellen Datenbank.
Dabei ist sichergestellt, dass "MaxChar" das größtmögliche Zeichen bei einem alphanumerischen Vergleich ist.
select
hhfield( Abteilung, hhsubstr(daten1,1,30) ),
hhfield( Nummer, hhsubstr(key_1,9,2) ),
hhfield( Unix, hhsubstr(key_1,9,2) )
from
f070
where
key_1 >= "${TBR}010120"
and key_1 <= "${TBR}01012099999" + hhmaxchar()
and hhsubstr(key_1,11,2) = "00"
Liefert den Namen des Datenbankbenutzers, der für den SOG ERP Programmzugriff verwendet wird oder einen leeren string, wenn kein Benutzer hinterlegt ist. Der Name wird in einfache Hochkommata gesetzt.
Steht in Auswahllisten SQL's zur Verfügung, um die abgefragte Sprache in den SQL einfließen lassen zu können. Möglicher Rückgabewert: 'ger'
Die Funktion kann also z. B. zum Deklarieren einer entsprechenden TSQL Variablen verwendet werden.
Einbinden weiterer Quelldateien über includes
Mit der Anweisung
#include VollständigerDateiName
können weitere Quelldateien als Include in die aktuelle Quelle eingebunden werden. Als "VollständigerDateiName" muss der vollständige Name der Datei angegeben werden. Es können hier Umgebungsvariable in der Form "${Variable}" verwendet werden. Die Nutzung von Script-Variablen ist nicht gestattet.
Formulierung von TSQL-Kommandobatches
Um mehrere TSQL-Kommandos zu einem Kommandobatch zusammenzufassen, müssen die Zeilen der Anweisungen mit der Zeichenfolge ";--" beendet werden.
DECLARE @VALUE VARCHAR(300);--
DECLARE @sql NVARCHAR(1000);--
SET @VALUE=(select name from sysobjects where xtype = 'PK' and parent_obj = (object_id('dbo.f010')));--
set @sql = 'ALTER TABLE dbo.f010 DROP CONSTRAINT ' + @VALUE;--
exec sp_executesql @sql;
Dieses Beispiel löscht den Primary Key auf der Tabelle "dbo.f010". Dabei wird der aktuelle Name des Constrain dynamisch ermittelt.
Die Generic-SQL-Syntax funktioniert auch in derartig verbundenen TSQL-Blöcken.
Formulierung von TSQL-Kommandobatches mittels begin und end
Über die nachfolgende Syntax können größere, zusammenhängende Blöcke mit TSQL-Anweisungen formuliert werden.
-- begin tsql
declare @cur int;
exec sp_cursoropen @cur output, "select * from f003";
select @cur;
exec sp_cursorfetch @cur;
exec sp_cursorfetch @cur;
exec sp_cursorclose @cur;
-- end tsql
Zwischen "-- begin tsql" und "-- end tsql" können beliebige TSQL-Anweisungen stehen.
Innerhalb dieser Blöcke, steht keine SOG Generic-SQL Syntax zur Verfügung.