Modul DB

DB

Im Modul DB werden die Datenbank-Routinen zusammengefasst.

• #sql fügt dem SQL-Statement eine Zeile hinzu
• #sql_clear löscht das SQL-Statement
• #sql_exec führt SQL-Statement aus
• #sql_open führt ein select-Statement aus und ruft für jede Zeile der Datenmenge das angegebene Kommando aus.
• #sql_openval führt eine select-Statement aus und schreibt das Ergebnis der ersten (ind er Regel der einzigen) Datenzeile in lokale oder globale Variable
• #sql_openvalrows führt eine select-Statement aus und schreibt das Ergebnis in lokale oder globale Variablen. Die Werte der verschiedenen Datenzeilen werden durch ein Trennzeichen getrennt.
• #sql_intransaction führt ein Kommando in einer eigens gestarteten Transaktion aus.
• #sql_upsert schreibt eine Zeile in eine Datenbanktabelle.

• $SQL() führt eine SQL-Anweisung aus und gibt die erste Spalte der ersten Datenreihe als Ergebnis zurück.
• $SQLTEXT() gibt den Inhalt eines SQL-Statements zurück.
• $DATA() gibt den Inhalt eines Felder der aktuellen Datenzeile einer mit #sql_open geöffneten Datenmenge zurück.
• $ISNULL() gibt Y zurück, wenn der Inhalt des Feldes NULL ist oder das Feld nicht existiert.

#sql

Die Prozedur #sql fügt dem SQL-Statement eine Zeile hinzu.

Es handelt sich dabei um eine nummerierte Prozedur. #sql fügt dem ersten SQL-Statement eine Zeile hinzu, #sql2 fügt dem zweiten SQL-Statement eine Zeile hinzu, und so weiter.

Parameter

#sql hat keine benannten Parameter. Die ganze Zeile nach dem #text und dem trennenden Leerzeichen wird hinzugefügt.

Funktionen werden ersetzt.

Hinweis

Durch den Aufruf einer der folgenden Prozeduren wird das SQL-Statement (nach der Ausführung) wieder geleert:

• #sql_open
• #sql_openval
• #sql_openvalrows
• #sql_exec

Beispiel

#sql  select count(*) as cnt
#sql    from user_user
#opensqlval   f_cnt=1

#sql_line

Die Prozedur #sql fügt dem SQL-Statement den Text im Parameter z als Zeile hinzu.

Es handelt sich dabei um eine nummerierte Prozedur. #sql_line fügt dem ersten SQL-Statement eine Zeile hinzu, #sql_line2 fügt dem zweiten SQL-Statement eine Zeile hinzu, und so weiter.

Parameter

• cnd ("condition") - Wenn Y, wird die Prozedur ausgeführt; default Y, Funktionen werden ersetzt
• z - Name des Eintrags in den Entwicklertexten, üblicherweise unterhalb von SQL; Funktionen werden ersetzt.

Beispiel

#sql_line z="    and status = 51" cnd=$ICP(0,rü)

#sql_text

Holt ein SQL-Statement aus den Entwicklertexten (xdevtext). Diese Möglichkeit wird gerne für SQL-Statements verwendet, die auf verschiedene Datenbanksysteme angepasst werden müssen (zum Beispiel wegen des Einsatzes von NVL/ifnull/coalesce).

Parameter

• cnd ("condition") - Wenn Y, wird die Prozedur ausgeführt; default Y, Funktionen werden ersetzt
• n - Name des Eintrags in den Entwicklertexten, üblicherweise unterhalb von SQL; Funktionen werden ersetzt.
• d ("driver") - Name des Datenbanktreibers. Wird in der Baumhierarchie direkt unter dem mit n bezeichneten Statement ein Eintrag mit dem Name des Datenbanktreibers gefunden, so wird dieses SQL-Statement statt dem mit n bezeichneten Statements verwendet. Auf diese Weise lassen sich übersichtlich Statements für alle eingesetzten Datenbanktreiber in der Datenbank speichern. Bleibt d leer (was üblicherweise der Fall ist), so wird der Treibername der aktuellen Primärdatenbank verwendet. Funktionen werden ersetzt.

Beispiele

#sqltext   n=_system_lookup_202  
#cout   c=$SQLTEXT(1)

#sqltext   n=_system_lookup_202   d=firebird
#cout   c=$SQLTEXT(1)

#sql_clear

Die Prozedur #sql_clear löscht das SQL-Statement.

Es handelt sich dabei um eine nummerierte Prozedur. #sql_clear löscht das erste SQL-Statement, #sql_clear2 löscht das zweite SQL-Statement, und so weiter.

Parameter

• cnd ("condition") - Wenn Y, wird die Prozedur ausgeführt; default Y, Funktionen werden ersetzt

Hinweis

Der Aufruf von #sql_clear ist im Regelfall nicht erforderlich, da durch den Aufruf einer der folgenden Prozeduren das SQL-Statement (nach der Ausführung) wieder geleert wird:

• #sql_open
• #sql_openval
• #sql_openvalrows
• #sql_exec

Allerdings kann es bei einem Fehler vorkommen, dass das SQL-Statement nicht geleert wird und beim Aufbau eines neuen Statements Zeilen der vorherigen Statements zurückgeblieben sind. Das kann mit #sql_clear verhindert werden. Die Prozedur wird üblicherweise vor den Beginn eines neuen Statements gesetzt, siehe Beispiel.

Beispiel

#sql_clear
#sql  select count(*) as cnt
#sql    from user_user
#sql_openval   f_cnt=1

#sql_exec

Die Prozedur #sql_exec führt ein SQL-Statement.

Es handelt sich dabei um eine nummerierte Prozedur. #sql_exec führt das erste SQL-Statement aus, #sql_exec2 führt das zweite SQL-Statement aus, und so weiter.

Parameter

• cnd ("condition") - Die Prozedur wird nur dann ausgeführt, wenn das Statement in cnd Y ergibt. Default ist Y.
• db - ("database") Name der Datenbank, auf die sich die Abfrage bezieht
• k ("key") - Um Namenskonflikte zu vermeiden, beginnen Parameter im SQL-Statement entsprechend den Konventionen mit einem k. Bei allen Parametern werden Funktionen ersetzt. Siehe auch das Beispiel.
• srv_ops ("Server Operations") - Wenn Y, wird die Zahl der betroffenen Datensätze den Server Operations hinzugefügt. Wird im Server-Betrieb verwendet. Default N, Funktionen werden ersetzt.
• z - Nummer des Values, in das die Zahl der betroffenen Datensätze gespeichert werden kann

Beispiel

#sql update tbl_test set status = 7 where tbl_test_id = :kid
#sql_exec  :kid=$FND(s,tbl_test_id)   z=1
#message   c="Es wurden $VAR(1) Datensätze deaktiviert."

#sql_open

Die Prozedur #sql_open führt ein select-Statement aus und ruft für jede Zeile der Datenmenge das angegebene Kommando aus.

Es handelt sich dabei um eine nummerierte Prozedur. #sql_open ruft das erste SQL-Statement auf, #sql_open2 ruft das zweite SQL-Statement auf, und so weiter.

Parameter

• acnd - ("abort condition") wenn Y, wird die Verarbeitung abgebrochen; wird nach jedem Aufruf von er geprüft; Default N, Funktionen werden ersetzt
• cnd - ("condition") Die Prozedur wird nur dann ausgeführt, wenn das Statement in cnd Y ergibt. Default ist Y; Funktionen werden ersetzt
• db - ("database") Name der Datenbank, auf die sich die Abfrage bezieht; Funktionen werden ersetzt
• er - ("each row") Das Kommando, das für jede Zeile der Datenmenge aufgerufen wird. Funktionen werden ersetzt.
• ern - ("each row no") Das Kommando, das für jede Zeile der Datenmenge aufgerufen wird. Funktionen werden nicht ersetzt. Wenn ern einen Wert hat, bleibt er unberücksichtigt. Üblicherweise wird er verwendet.
• ert - ("each row transaction") Wenn Y, wird für jede Zeile der Datenmenge eine eigene Transaktion gestartet und nach der Abarbeitung des Kommandos wieder geschlossen; Funktionen werden ersetzt
• k - ("key") Um Namenskonflikte zu vermeiden, beginnen Parameter im SQL-Statement entsprechend den Konventionen mit einem k. Bei allen Parametern werden Funktionen ersetzt. Siehe auch das Beispiel.
• m - ("maximum") Es wird maximal für die Anzahl der angegebenen Zeilen das in er angegebene Kommando ausgeführt. Dieser Parameter wird häufig dazu verwendet, während der Entwicklung mit einer geringen Zahl von Datensätzen zu arbeiten; Funktionen werden ersetzt
• nex ("no exception") - Wenn Y, wird bei Exceptions in er nicht abgebrochen, sondern mit dem nächsten Datensatz fortgesetzt. Default N; Funktionen werden ersetzt.
• n - Name der Datenmenge, wird benötigt, um mit $DATA() auf die einzelnen Felder zugreifen zu können.

Beispiel

#sql  select * 
#sql    from user_user
#sql    where upper(lastname) like upper(:kname)
#sql_open  n=test  er=test_line  kname=$EDT(edt1)%

#sql_openval

Die Prozedur #sql_openval führt eine select-Statement aus und schreibt das Ergebnis der ersten (ind er Regel der einzigen) Datenzeile in lokale oder globale Variable.

Es handelt sich dabei um eine nummerierte Prozedur. #sql_openval ruft das erste SQL-Statement auf, #sql_openval2 ruft das zweite SQL-Statement auf, und so weiter.

Parameter

• cnd ("condition") - Wenn Y, wird die Prozedur ausgeführt; default Y, Funktionen werden ersetzt
• cnt - ("count") Ermittelt die Anzahl der Datensätze, die das SQL-Statement zurück gibt, und schreibt diese in die mit diesem Parameter bezeichneten Variablen oder Value
• db - ("database") Name der Datenbank, auf die sich die Abfrage bezieht
• f_ - ("Field") Alle Felder der Datenmenge, die in lokale oder globale Variable geschrieben werden sollen, werden mit einem Prefix aufgeführt. Nach dem Gleichheitszeichen folgt die Nummer der globalen Variable ("Value") oder der Name der globalen Variable.
• k - ("key") Um Namenskonflikte zu vermeiden, beginnen Parameter im SQL-Statement entsprechend den Konventionen mit einem k. Bei allen Parametern werden Funktionen ersetzt. Siehe auch das Beispiel.

Beispiel

#sql2  select * 
#sql2    from user_user  
#sql2    where user_user_id = :kid 
#sql_openval2  kid=$FND(s,user_user_id)   f_firstname=1   f_lastname=2   f_ldap=ldap

Die Felder firstname und lastname werden in die lokalen Variable ("Values") 1 und 2 geschrieben, das Feld ldap in die globale Variable ldap.

#sql_openvalrows

Die Prozedur #sql_openval führt eine select-Statement aus und schreibt das Ergebnis in lokale oder globale Variablen. Die Werte der verschiedenen Datenzeilen werden durch ein Trennzeichen getrennt.

Die Prozedur #sql_openval wird häufig dazu verwendet, um das Ergebnis mit dem IN-Operator in einer WHERE-Klausel zu verwenden.

Es handelt sich dabei um eine nummerierte Prozedur. #sql_openvalrows ruft das erste SQL-Statement auf, #sql_openvalrows2 ruft das zweite SQL-Statement auf, und so weiter.

Parameter

• cnd ("condition") - Wenn Y, wird die Prozedur ausgeführt; default Y, Funktionen werden ersetzt
• db - ("database") Name der Datenbank, auf die sich die Abfrage bezieht
• f_ - ("Field") Alle Felder der Datenmenge, die in lokale oder globale Variable geschrieben werden sollen, werden mit einem Prefix aufgeführt. Nach dem Gleichheitszeichen folgt die Nummer der globalen Variable ("Value") oder der Name der globalen Variable.
• k - ("key") Um Namenskonflikte zu vermeiden, beginnen Parameter im SQL-Statement entsprechend den Konventionen mit einem k. Bei allen Parametern werden Funktionen ersetzt. Siehe auch das Beispiel.
• sep - ("separator") Trennzeichen, default ist ein Komma.

Beispiel

#sql2  select * 
#sql2    from user_user  
#sql2    where status = 7
#sql_openvalrows2  f_login=1

#sql_openvallist

Die Prozedur #sql_openvallist führt eine select-Statement aus und ergänzt den mit n angegebenen Text als Liste mit den Werten aus der Spalte f

Parameter

• cnd - ("condition") Wenn Y, wird das Statement ausgeführt; default Y, Funktionen werden ersetzt
• db - ("database") Name der Datenbank, auf die sich die Abfrage bezieht
• f - ("Field") Der Name des Feldes, dessen Werte hinzugefügt werden sollen
• k - ("key") Um Namenskonflikte zu vermeiden, beginnen Parameter im SQL-Statement entsprechend den Konventionen mit einem k. Bei allen Parametern werden Funktionen ersetzt.
• n - ("nummer") Nummer des Textes, der als Liste ergänzt wird; default 1, Funktionen werden ersetzt

Beispiel

#sql_opentvl

Die Prozedur #sql_opentvl macht aus dem Ergebnis eine SQL-Abfrage eine Text-ValueList (das kann für Nachschlagelisten verwendet wqerden). Die Abfrage benötigt die Spalten ckey und cvalue.

Parameter

• clr - ("clear") Wenn Y wird der Text am Anfang geleert; default Y, Funktionen werden ersetzt
• cnd - ("condition") Wenn Y, wird das Statement ausgeführt; default Y, Funktionen werden ersetzt
• db - ("database") Name der Datenbank, auf die sich die Abfrage bezieht
• n - ("nummer") Nummer des Textes, in den die Daten geschrieben werden

Beispiel

#sql select nrdv as ckey, name as cvalue from neuland.stamm_laender order by 2
#sql_opentvl   n=2   db=ora_prod

#sql_intransaction

Führt das Kommando in einer eigens gestarteten Transaktion aus, die beim Auftreten eines Fehlers mit Rollback zurückgenommen wird.

Parameter

Bei allen Parameters werden die Funktionen ersetzt.

• c ("Caption") - Text der Meldung, wenn die Transaktion zurückgenommen wird
• cmd ("Command") - Kommando, das innerhalb der Transaktion ausgeführt wird.
• cnd ("Condition") - Wenn Y, wird die Prozedur ausgeführt; default Y.
• ex ("Exception") - Kommando, das im Falle eines Fehlers nach dem Rollback ausgeführt wird.
• nex ("NoException") - Wenn Y, wird nach einem Fehler die Ausführung des Kommandos fortgesetzt; default N.

Beispiel

#sql_intransaction   cmd=test_line   c="Fehler beim Schreiben der Daten"

#sql_upsert

Die Prozedur #sql_upsert schreibt einen Datensatz in eine Datenbanktabelle. Je nach Parameter y wird ein UPDATE- oder ein INSERT-Statement ausgeführt.

Parameter

• cnd ("condition") - Die Prozedur wird nur dann ausgeführt, wenn das Statement in cnd Y ergibt. Default ist Y. Funktionen werden ersetzt.
• db - ("database") Name der Datenbank, auf die sich die Abfrage bezieht
• f_ ("fields") - Die Werte für die einzelnen Spalten werden mit f_-Parametern gesetzt, dem f_ wird jeweils der Spaltenbezeichner angefügt. Funktionen werden ersetzt. Siehe Beispiel.
• hst ("history") - Wenn Y, wird auch ein Datensatz in die History-Tabelle geschrieben. In den meisten Fällen bedeutungslos, da die History-Tabellen über einen Datenbanktrigger geschrieben werden. Default ist Y.
• k ("key") - Primärschlüsselspalte der Tabelle. Per default wird der Wert aus dem Tabellennamen abgeleitet. Funktionen werden ersetzt.
• n ("name / number") - Name der Variable oder Nummer des Values, in welche(n) die Anzahl der geänderten Datensätze geschrieben wird
• t ("table") - Name der Tabelle, in die geschrieben wird. Funktionen werden ersetzt.
• y ("type") - je nach Existenz eines Datensatzes mit dem entsprechenden Primärschlüssel wird UPDATE oder INSERT ausgeführt. Default i. Funktionen werden ersetzt.
  • a - automatic: es wird geprüft, ob bereits ein Datensatz mit diesem Primätschlüsselwert vorhanden ist; wenn ja, UPDATE, ansonsten INSERT
  • c - check: es wird geprüft, ob bereits ein Datensatz mit allen übergebenen Feldern vorhanden ist; wenn ja, passiert nichts, ansonsten UPDATE
  • i - INSERT
  • u - UPDATE

Hinweise

• die Prozedur #sql_upsert braucht zwingend einen Wert für die Primärschlüsselspalte; gegebenenfalls einen Dummy-Wert einfügen.
• Beim Typ a (automatic) wird erst per SELECT-Statement geprüft, ob ein Datensatz mit dem entsprechenden Primärschlüsselwert vorhanden ist. Dies verlangsamt die Ausführung. Setzen Sie diesen Typ nur dann ein, wenn es erforderlich ist.

Beispiel

#upsert  y=i   t=user_group   f_user_group_id=$GUID()   f_type=I   f_path="ig.$DATA(igs,login)"   f_user_user_id=$DATA(igs,user_user_id)   f_status=1

#sql_migrate

Die Prozedur #sql_migrate dient dazu, das Ergebnis eines Select-Statements in einer Tabelle zu migrieren, häufig auch in einer anderen Datenbank.

HINWEIS: Für Parameter wie t_dest oder k_dest keine Command-Parameter verwenden, sondern ggf. die Command-Parameter in Variable speichern und diese verwenden.

Parameter

• cnd ("condition") - Die Prozedur wird nur dann ausgeführt, wenn das Statement in cnd Y ergibt. Default ist Y. Funktionen werden ersetzt.
• db ("database") - Die Datenbank, für die das SQL-Statement ausgeführt wird; Funktionen werden ersetzt
• db_dest ("database destination") - Die Datenbank, in welche die Daten migriert werden
• hst_dest ("history destination") - Wenn Y, werden die History-Felder ergänzt. Default Y, Funktionen werden ersetzt
• k_dest ("key destination") - Der Name der Primärschlüsselspalte; sofern nicht gesetzt, wird sie aus dem Tabellennamen t_dest abgeleitet; Funktionen werden ersetzt (Funktionen $VAL und $CP() nicht verwenden).
• m ("max") - Maximale Anzahl von Datensätzen, die in die Zieltabelle eingefügt werden; Funktionen werden ersetzt
• nex ("no exception") - Wenn Y, wird im Falle einer Exception die Bearbeitung nicht abgebrochen, sondern lediglich Warnungen geloggt; Default N, Funktionen werden ersetzt
• t_dest ("table destination") - Name der Tabelle, in der die Daten eingefügt werden; Funktionen werden ersetzt (Funktionen $VAL und $CP() nicht verwenden).
• y_dest ("type destination") - i - INSERT, u - UPDATE, a - automatic, je nach Existenz eines Datensatzes mit dem entsprechenden Primärschlüssel wird UPDATE oder INSERT ausgeführt. Default i. Funktionen werden ersetzt.

Beispiel

#sql select translate_word_id as translate_word2_id, word, datechg, usrchg, progchg from translate_word
#sql_migrate   t_dest=translate_word2   nex=Y   db=test

$SQL()

Führt eine SQL-Anweisung aus und gibt die erste Spalte der ersten Datenreihe als Ergebnis zurück.

Parameter

1. Nummer des SQL-Statements oder SQL-Statement, siehe Beispiele

Beispiele

#message c="$SQL(select count from user_user)"

#sql3 select count from user_user
#message c=$SQL(3)

$SQL_CONCAT()

Die unterschiedlichen SQL-Dialekte kennen unterschiedliche Methoden, Strings zusammenzufügen. Das kann bei SQL-Anweisungen, die auf unterschiedlichen Datenbanksystemen verwendet werden soll, zu Problemen führen. In einem solchen Fall wird $SQL_CONCAT() eingesetzt, was dann in das SQL-Statement die korrekte Concatenation-Anweisung einfügt.

Hinweis: Kommata und Klammern dürfen in den Statements nicht direkt eingesetzt werden, sondern müssen mit $CHR-Funktionen eingefügt werden.

Concatenation-Anweisungen

• sqlite: ||
• firebird: ||
• postgres: ||
• oracle: ||
• mysql: concat
• mssql: +

Parameter

1. Name des Datenbanktreibers oder default (also der Treiber des aktuellen Datenbanksystems)
2. und weitere: Strings, die zusammengefügt werden sollen

Beispiele

select user_user_id as ckey, $SQL_CONCAT(default,login,' $CHR(bro)',firstname,' ',lastname,'$CHR(brc)') as cvalue   
  from user_user   
  order by login

$SQL_NVL()

Die unterschiedlichen SQL-Dialekte kennen unterschiedliche Methoden, um leere Werte zu ersetzen. Das kann bei SQL-Anweisungen, die auf unterschiedlichen Datenbanksystemen verwendet werden soll, zu Problemen führen. In einem solchen Fall wird $SQL_NVL() eingesetzt, was dann in das SQL-Statement die korrekte Ersetzungs-Anweisung einfügt.

$SQL_NVL-Funktionen können geschachtelt werden, siehe Beispiel.

Hinweis: Kommata und Klammern dürfen in den Statements nicht direkt eingesetzt werden, sondern müssen mit $CHR-Funktionen eingefügt werden.

Ersetzungs-Anweisungen

• sqlite: ifnull
• firebird: coalesce
• postgres: coalesce
• oracle: NVL
• mysql: ifnull
• mssql: isnull

Parameter

1. Name des Datenbanktreibers oder default (also der Treiber des aktuellen Datenbanksystems)
2. Wert, der ersetzt werden soll, wenn er leer ist
3. Wert, mit dem ersetzt wird, wenn der Wert des 2. Parameters leer ist

Beispiele

select i.ckey, $SQL_NVL(default,t.cvalue, $SQL_NVL(default,t2.cvalue, i.cvalue)) as cvalue 
  from data_list l 
    inner join data_list_item i on i.data_list_id = l.data_list_id  and i.status < 7 
    left outer join translate_list_item t on t.data_list_item_id = i.data_list_item_id and t.translate_language_id = :klangid
    left outer join translate_list_item t2 on t2.data_list_item_id = i.data_list_item_id and t2.translate_language_id = :klangid2
  where l.name = :kname 
  order by i.csort, i.ckey

$SQLTEXT()

Gibt den Inhalt eines SQL-Statements zurück. Kann mal beim Debuggen hilfreich sein, mit den Debug-Tools sollte es einfacher gehen.

Parameter

1. Nummer des SQL-Statements

Beispiel

#clipboard   z=$SQLTEXT(3)

$DATA()

Gibt den Inhalt eines Felder der aktuellen Datenzeile einer mit #sql_open geöffneten Datenmenge zurück.

Parameter

1. Name der Datenmenge (muss als Parameter n in #sql_open angegeben sein)
2. Feldname in der Datenmenge
3. optional: NULL-Value-Wert, also Ergebniswert, wenn der Inhalt des Feldes leer sein sollte
4. optional: Wenn der vierte Prameter auf accnd ("accept no data"), dann kommt es zu keinem Fehler, wenn es das Feld und/oder die Datenmenge nicht gibt. In einem solchen Fall wird dann der NULL-Value-Wert ausgegeben; siehe das zweite Beispiel.
5. optional: Name der Datenbankverbindung, wenn nicht default

Beispiele

#text $DATA(test,firstname) $DATA(test,lastname)
#cout  c="$DATA(dat,test,this default is displayd without any dataset,accnd)"

$ISNULL()

Gibt Y zurück, wenn der Inhalt des Feldes NULL ist oder das Feld nicht existiert.

Parameter

1. Name der Datenmenge (muss als Parameter n in #sql_open angegeben sein)
2. Feldname in der Datenmenge

Beispiel

~ $ISNULL(test,firstname)

$DATAXML()

Parameter

Beispiel

$DDEF()

Parameter

Beispiel