1. No category

SQL-Funktionalität für Server
Ecofor
SQL-Funktionalität für Server
Version 2.2
15.05.2015
1/11
SQL-Funktionalität für Server
Ecofor
INHALTSVERZEICHNIS
1
2
3
SQL aus Textdatei.......................................................................................................... 3
SQL-Statements ............................................................................................................. 4
Beispiele ........................................................................................................................ 6
3.1
Beispiel MEDFASOP.Data.txt ................................................................................. 6
3.2
LOAD DATA (T_MedicamentList)............................................................................ 7
3.3
LOAD DATA (T_ZIP) ............................................................................................... 7
3.4
SELECT .................................................................................................................. 8
3.5
TRANSFORM XML ................................................................................................. 8
3.6
UNLOAD TABLE (Archivierung), ............................................................................. 9
3.7
UNLOAD DOCUMENT (Archivierung), 5 .................................................................10
4 Datum-Funktionen .........................................................................................................11
4.1
DATEADD ..............................................................................................................11
4.2
GETDATE ..............................................................................................................11
15.05.2015
2/11
SQL-Funktionalität für Server
Ecofor
1 SQL aus Textdatei
Mit dieser Anwendung können Server-Datenbanken über SQL-Statements geändert und
abgefragt werden. Sie wurde mit Microsoft Visual Studio .NET 2003 entwickelt und setzt voraus, dass die Statements als Textdatei (*sql.txt) vorliegen. Windows stellt dazu den Editor im
Menü Zubehör zur Verfügung. Ist in einer Datei mehr als ein Statement vorhanden müssen
sie (a) immer in einer neuen Zeile beginnen und (b) mit Semikolons abgeschlossen, oder
durch eine Leerzeile voneinander getrennt werden. Alle Statements einer Datei werden als
eine Transaktion behandelt. Der Abschluss der Verarbeitung wird bestätigt, wobei jeweils
nachgefragt wird, ob Änderungen beibehalten werden sollen, oder nicht.
Mit dem Start-Knopf wird der Windows-File-Dialog zur
Auswahl der Datei mit den SQL-Statements aufgerufen.
Mit dem Document-Knopf wird das Dokument der Anwendung aufgerufen.
Der unten abgebildete Windows-File-Dialog wird situativ aufgerufen. Die Auswahl erfolgt
durch Doppelklick auf die Datei. Im Kopf des Formulars ist jeweils der Kontext ersichtlich:
Select SQL: Auswahl der Datei, welche die SQL-Statements enthält
Select Database: Auswahl der Parameter-Datei „*.Data.txt“ (Seite 6)
Select Data: Auswahl der Datei welche die Load-Daten enthält
Select XML: Auswahl der XML-Dateien welche umgeformt werden sollen1
Windows-File-Dialog
1
Für „Select XML“ ist eine Mehrfachauswahl möglich.
15.05.2015
3/11
SQL-Funktionalität für Server
Ecofor
2 SQL-Statements
Nachfolgend sind einzig Statements zur Datenänderung und proprietäre Statements aufgeführt. Für weitergehende Informationen sei auf die Referenz des Datenbank-Herstellers verwiesen. Ergebnisse werden immer im XML-Format (ADO-Funktionalität von Microsoft) geschrieben, und zwar am Ort der SQL-Datei in den Ordner Result. Zur Umwandlung in eine
Textdatei steht TRANSFORM zur Verfügung.
Elemente in geschweiften {} Klammern sind fakultativ, solche in spitzen <> Klammern sind
weiter unten definiert. Beide Klammertypen werden nie geschrieben. Tabellen- und Spaltennamen sind in eckige Klammern [] zu setzen wenn sie mit reservierten SQL-Wörtern kollidieren oder sonstigen formalen SQL-Anforderungen nicht genügen. Mit .. wird die Fortsetzung
des Prinzips signalisiert. Schlüsselwörter sind gross und fett geschrieben. Mit | werden Alternativen separiert. Hochgestellte Ziffern sind auf der nächsten Seite erklärt.
SQL-Statements
OPEN DATABASE <database>{;}1,2
DELETE FROM <table> {WHERE <clause>}{;}
INSERT INTO <table> (<columns>) VALUES (<values>){;}
UPDATE <table> SET <column>=<value> {,<column>=<value> ..} {WHERE <clause>}{;}
SELECT <columns> INTO <table> {<clause>}
LOAD DATA FROM <input>8,12 INTO <table> (<columns>) DELIMITER <delimiter>
{NEWID <column>}3 {HEADER}4{;}2
UNLOAD TABLE INTO <folder>5 {DOCUMENT <column>}13 SELECT <clause>{;}6
UNLOAD DOCUMENT INTO <folder>5 WHERE DATE <comparison> <date>{;}2
TRANSFORM XML <input>10,14 {INTO <folder>}11 DELIMITER <delimiter> {HEADER}7{;}2
<> Definition
<clause>
<column>: {[}column name{]}
<columns>: <column> {,<column> ..}
<comment>: -- [text] | --! [text] | --? [text]
<comparison>: < | > | <= | >= | =
<database>: <fullpath>
<date>
<date time>
15.05.2015
Klausel nach SQL-Syntax
Spaltenname
Für LOAD wird die Reihenfolge von den Input-Daten bestimmt. Die Spalte von NEWID
kann, muss aber nicht angegeben werden,
sie hat nie Input.
(2 x Bindestrich). Ergänzt mit ! wird der text
angezeigt. Mit ? wird der Text auch angezeigt, aber mit der Möglichkeit, die Verarbeitung abzubrechen.
Vergleichsoperatoren
Parameter-Datei „*.Data.txt“ (Beispiel Seite 6)
Datum9 als Konstante oder Funktion (Seite
11)
DatumZeit nach SQL-Syntax
4/11
SQL-Funktionalität für Server
Ecofor
<> Definition
<delimiter>: BLANK | COMMA |
TABULATOR | SEMICOLON |
QUOTE | <string>
<expression>
<file>
Leer | Komma |
Tabulator | Strichpunkt |
Hochkomma | Zeichenkette
Ausdruck nach SQL-Syntax
Dateiname mit/ohne Jokerzeichen zur Vorauswahl
<folder>
Pfad und Name des Ordners.
<fullpath>
Pfad und Dateiname
<function>
Funktion nach SQL-Syntax
<input>: SELECTFILE({<file>}) |
SelectFile() startet den File-Dialog von WinGETFILE(<fullpath>)
dows.
<number>
Numerische Werte nach SQL-Syntax.
<string>
1 bis n Zeichen
<table>: {[}table name{]}
Tabellenname
<UDL>
Eine UDL-Datei (Microsoft Datenlink) wird
folgendermassen erstellt:
(a) eine leere Datei BEISPIEL.txt erstellen
und danach txt durch udl ersetzen.
(b) Doppelklick auf BEISPIEL.udl und es öffnet sich ein Fenster, wo die Eigenschaften
eingestellt werden können.
<value>: ‘<string>‘ | <number> | <date Text-Daten sind zwischen Hochkommas zu
time> <function> | NEWID()3
setzen.
<values>: <value> {,<value> ..}
Werte in Reihenfolge der aufgeführten Spalten
1
Falls das Statement benutzt wird muss es vor allen anderen Statements stehen und darf
in einer Datei nur einmal vorkommen.
2
Statement vollständig proprietär (kein SQL).
3
Damit wird die Funktion NEWID() der entsprechenden Spalte zugeordnet.
4
HEADER überliest erste Zeile von <input>.
5
Ordner wird automatisch mit Datum und Zeit ergänzt (<folder> YYYYMMDD HHMMSS).
6
SELECT <clause> ist SQL, der Rest proprietär. Statement muss mit „SELECT * FROM
<table>“, oder dann mit „SELECT <table>.*“ beginnen, wobei <table> die zu archivierende Tabelle bezeichnet.
7
Erstellt Kopfzeile (Spaltennamen).
8
In Textspalten wird HEX „01“ in HEX „0D0A“ (neue Zeile) umgewandelt.
9
Format Länderspezifisch: DD.MM.YYYY {HH:MM{:SS}} | MM/DD/YYYY {HH:MM{:SS}}
10
XML-Datei, welche mit ADO-Funktionalität von Microsoft erstellt wurde. Mit SelectFile() ist
Mehrfachauswahl möglich.
11
Ohne Angabe wird Datei im Input-Ordner erstellt.
12
DatumZeit-Spalten dürfen Lokal-, wie auch US-Format haben.
15.05.2015
5/11
ARCHIVECONS=5
ARCHIVETREAT=12
DBDOC=MEDFASOP.Client
DBLANG=german
DBTEMP=\\Server1\data\temp
DOCUMENT=\\Server1\data\MEDFASOP\DOCUMENT
HTMLPATH=\\Server1\data\MEDFASOP\HTML
PHARMACY=\\Server1\data\MEDFASOP\Pharmacy
SQLCONN=Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MEDFASOP;Data Source=Server1\SQL2014
TARMED=\\Server1\data\MEDFASOP\TARMED
UDLCONN=\\Server1\data\MEDFASOP\MEDFASOP.UDL
USERPATH=\\Server1\data\MEDFASOP\USER
Z:\MEDFASOP\MEDFASOP.Data.txt
SQL-Funktionalität für Server
13
Bezeichnet die Spalte, an welche Dokumente gebunden sind.
14
In Textspalten wird HEX „0D0A“ (neue Zeile) in HEX „01“ umgewandelt.
15.05.2015
Ecofor
3 Beispiele
3.1 Beispiel MEDFASOP.Data.txt
6/11
SQL-Funktionalität für Server
Ecofor
3.2 LOAD DATA (T_MedicamentList)
Die Tabelle T_MedicamentList enthält BAG-Liste und manuelle Einträge. Manuelle Einträge
haben keine Swissmedic-Nr. Die unten aufgeführten SQL-Statements haben folgende Wirkung: Zuerst wird die BAG-Liste aus der Tabelle entfernt und danach aus der selektierten
Datei geladen.
MedicamentList-SQL.txt
OPEN DATABASE GETFILE(Z:\MEDFASOP\MEDFASOP.Data.txt);
--? Medikamente mit externer Nummer (Swissmedic) werden gelöscht
DELETE FROM T_MedicamentList WHERE external IS NOT NULL;
--? Medikamente werden aus Datei “MedicamentList-Data.txt” geladen
LOAD DATA FROM SelectFile(MedicamentList-Data.txt)
INTO T_MedicamentList (medicament,external) DELIMITER @ NEWID TID;
MedicamentList-Data.txt (Auszug) 2
Aldara Sach. 250 mg 12 Sach. Crème 250 [email protected]
Minitran System 36 mg 10 10 Systeme 10 36 [email protected]
Minitran System 36 mg 10 30 Systeme 10 36 [email protected]
Tambocor Compr. 100 mg 100 Compr. 100 [email protected]
Tambocor Compr. 100 mg 20 Compr. 100 [email protected]
3.3 LOAD DATA (T_ZIP)
ZIP-SQL.txt
OPEN DATABASE GETFILE(Z:\MEDFASOP\MEDFASOP.Data.txt);
DELETE FROM T_ZIP WHERE (Country='CH' OR Country='FL');
LOAD DATA FROM SelectFile(ZIP-Data.txt) INTO T_ZIP (country,zip,city,state) DELIMITER TABULATOR NEWID TID HEADER;
ZIP-Data.txt (Auszug) 3
CountryZIP
CH
1000
CH
1000
CH
1008
CH
1009
2
City
State
Lausanne 25
Lausanne 26
Prilly VD
Pully VD
VD
VD
Die aktuelle Liste enthält z.Z. mehr als 6400 Einträge, welche von der BAG-Spezialitätenliste stammen (http://www.bag.admin.ch/kv/gesetze/sl/d/index.htm).
3
Das aktuelle Verzeichnis enthält z.Z. mehr als 2200 Einträge, welche von der Post stammen
(http://www.post.ch/SiteOnLine/DE/Accueil/1,1727,5811-0,00.html – PLZ light).
15.05.2015
7/11
SQL-Funktionalität für Server
Ecofor
3.4 SELECT
SELECT-SQL.txt
OPEN DATABASE GETFILE(Z:\MEDFASOP\MEDFASOP.Data.txt);
SELECT T_Patient.*, T_Consultation.Consultation
FROM T_Patient INNER JOIN T_Consultation ON T_Patient.PID = T_Consultation.PID;
C:\ED\Projekte\SQL\Result\Admin 20050124 091235.xml (Auszug, ohne Schemadaten)
3.5 TRANSFORM XML
Transform-SQL.txt
TRANSFORM XML SelectFile(*.xml) DELIMITER TABULATOR HEADER;
C:\ED\Projekte\SQL\Result\Admin 20050124 091235.txt (Spalten rechts abgeschnitten)
PID
APID FirstName
20050246157788
15.05.2015
LastName
Marianne
DateOfBirth
Sex
Bürki 12.03.1995
Language
1
3
Allergies
8/11
SQL-Funktionalität für Server
Ecofor
3.6 UNLOAD TABLE (Archivierung)4, 5
ArchiveTreatmentTime-SQL.txt
OPEN DATABASE GETFILE(Z:\MEDFASOP\MEDFASOP.Data.txt);
UNLOAD TABLE INTO z:\temp\archiv
SELECT * FROM T_TreatmentTime WHERE Ended IS NOT NULL AND Ended< DATEADD(mm,6,GETDATE());
ArchiveConsultation-SQL.txt
OPEN DATABASE GETFILE(Z:\MEDFASOP\MEDFASOP.Data.txt);
CREATE TABLE ##ULCID (CID BIGINT);
CREATE INDEX IXCID ON ##ULCID (CID);
INSERT INTO ##ULCID
SELECT CID FROM T_Consultation WHERE Date<DATEADD(yy,-10,GETDATE());
-- Reduziert um noch aktiv verlinkte Konsultationen
DELETE FROM ##ULCID WHERE CID IN (SELECT CID FROM T_ConsultationLink WHERE LCID
NOT IN (SELECT CID FROM ##ULCID));
DELETE FROM ##ULCID WHERE CID IN (SELECT LCID FROM T_ConsultationLink WHERE CID
NOT IN (SELECT CID FROM ##ULCID));
-- Achtung: Reihenfolge berücksichtigt CASCADE DELETE, somit keinesfalls ändern
DELETE FROM T_ConsultationText WHERE Status IS NULL;
UNLOAD TABLE INTO z:\temp\archiv
SELECT * FROM T_Medicament WHERE CID IN (SELECT CID FROM ##ULCID);
UNLOAD TABLE INTO z:\temp\archiv
SELECT * FROM T_ConsultationText WHERE CID IN (SELECT CID FROM ##ULCID);
UNLOAD TABLE INTO z:\temp\archiv
SELECT * FROM T_ConsultationLink WHERE CID IN (SELECT CID FROM ##ULCID);
UNLOAD TABLE INTO z:\temp\archiv
SELECT * FROM T_Consultation WHERE CID IN (SELECT CID FROM ##ULCID);
UNLOAD DOCUMENT INTO z:\temp\archiv WHERE DATE<DATEADD(yy,-10,GETDATE());
4
6
Die Syntax der Funktionen DATEADD und GETDATE sind auf Seite 11 dokumentiert.
Beispiel ist nicht mehr aktuell. Neu stehen Spezialprogramme zur Verfügung (Patient2Archive und
Archive2Patient).
6
Dokumente können auch gesondert archiviert werden (Seite 10).
15.05.2015
9/11
5
SQL-Funktionalität für Server
Ecofor
ArchivePatient-SQL.txt
OPEN DATABASE GETFILE(Z:\MEDFASOP\MEDFASOP.Data.txt);
CREATE TABLE ##ULPID (PID BIGINT);
CREATE INDEX IXPID ON ##ULPID (PID);
INSERT INTO ##ULPID
SELECT PID FROM T_Patient
WHERE (PID NOT IN (SELECT PID FROM T_Occupancy))
AND (PID NOT IN (SELECT PID FROM T_Consultation));
-- Achtung: Reihenfolge berücksichtigt CASCADE DELETE, somit keinesfalls umstellen
UNLOAD TABLE INTO z:\temp\archiv
SELECT * FROM T_Family WHERE PID IN (SELECT PID FROM ##ULPID);
UNLOAD TABLE INTO z:\temp\archiv
SELECT * FROM T_CaseHistoryText WHERE PID IN (SELECT PID FROM ##ULPID);
UNLOAD TABLE INTO z:\temp\archiv
SELECT * FROM T_Vaccination WHERE PID IN (SELECT PID FROM ##ULPID);
UNLOAD TABLE INTO z:\temp\archiv
SELECT * FROM T_TreatmentTime WHERE PID IN (SELECT PID FROM ##ULPID);
UNLOAD TABLE INTO z:\temp\archiv
SELECT * FROM T_PatientAddition WHERE PID IN (SELECT PID FROM ##ULPID);
UNLOAD TABLE INTO z:\temp\archiv DOCUMENT pid
7
SELECT * FROM T_Patient WHERE PID IN (SELECT PID FROM ##ULPID);
3.7 UNLOAD DOCUMENT (Archivierung) 8, 5
ArchiveDocument-SQL.txt
OPEN DATABASE GETFILE(Z:\MEDFASOP\MEDFASOP.Data.txt);
UNLOAD DOCUMENT INTO c:\temp\archiv WHERE DATE < DATEADD(yy,-5,GETDATE());
7
8
Dokumente können auch gesondert archiviert werden (Seite 10)
Die Syntax der Funktionen DATEADD und GETDATE sind auf Seite 11 dokumentiert
15.05.2015
10/11
SQL-Funktionalität für Server
Ecofor
4 Datum-Funktionen
4.1 DATEADD
DATEADD(datepart, number, date) AS DATETIME
Liefert im Funktionswert DATETIME zurück, welches zuvor um „number“ erhöht, bzw. reduziert worden ist.
datepart Ausdruck bestimmt das zu addierende Zeitintervall.
dd
Tag
dw Wochentag
dy
Tag des Jahres
hh
Stunde
mi
Minute
mm Monat
ms Millisekunde
qq
Quartal
ss
Sekunde
wk Woche
yy
Jahr
number Bestimmt die Anzahl der zu addierenden Intervalle. Kann positiv (für ein zukünftiges Datum) oder negativ (für ein vergangenes Datum) sein.
date
Kann vom Typ DATETIME oder ein konstanter Datum-/Zeit-Ausdruck (in Hochkommas) sein, zu dem das Intervall hinzuaddiert wird.
4.2 GETDATE
GETDATE() AS DATETIME
Liefert im Funktionswert System-Datum und -Zeit.
15.05.2015
11/11