====== Tipps zu MS SQL Server und pyodbc ======
===== MSSQL-Trigger in DWB =====
=== LogUpdatedWhen wird in CollectionSpecimen nur einmal in 24 Stunden für einen Nutzer gesetzt! ===
Updates in Tabellen lösen Prozedur: ''procSetVersionCollectionSpecimen'' aus, diese schaut, wer zuletzt geändert hat und gleicht das mit eingelogtem Namen ab, Wenn aktueller Nutzer anders oder letzte Änderung mehr als 24 Stunden her, wird CollectionsSpecimen.LogUpdatedWhen auf aktuellen Zeitwert gesetzt und Versionsnummer um 1 erhöht, Wenn Bedingungen nicht zutreffen bleibt alles beim Alten. Wenn User dbo ist, verhält sich das skript scheinbar anders, da es mit User_Name()-Funktion (in Systemfunktionen/Andere Funktionen) dbo erhält, der LogUpdatedWhen //in der geänderten Tabelle// aber mit dem Namen des eingeloggten users gesetzt wird.
patch dazu in ''GBOL_Cache_Test.procSetVersionCollectionSpecimen'' zum Testen eingebaut, Markus Weiss patch geschickt
----
===== Data Types in MS SQL und Python =====
* https://github.com/mkleehammer/pyodbc/wiki/Data-Types
===== Casts für odbc =====
https://msdn.microsoft.com/en-us/library/ms191530(v=sql.110).aspx
https://msdn.microsoft.com/en-us/library/ms712514(v=vs.85).aspx
==== datetime ====
für den Transfer zwischen MSSQL-Datenbanken funktioniert CAST zu datetime:
"CAST([{0}] as datetime) as [{0}]".format(columnname)
allerdings kann pymysql das dann nicht in die MySQL Datenbank einfügen.
Deshalb muss datetime mit dem Format ''121'' gecastet werden:
"CONVERT(varchar(30), {0}, 121) as {0}".format(columnname)
Das Format ''121'' ist wichtig, um die ersten drei Stellen der Millisekunden zu erhalten, die in der Tabelle TransactionDocument für den Teilschlüssel gebraucht werden (Spalte Date). Mit deisem Cast kann also für MySQL gecastet werden (Millisekunden gehen beim import verloren) oder für das Kopieren in eine andere MSSQL-Tabelle.
==== XML ====
XML muss gecasted werden um XML-Inhalt bei ''typed xml''-Columns von verknüpften Schema-Definition in der Datenbank zu lösen:
"CAST([{0}] as xml) as [{0}]".format(columnname)
==== geography ====
geography Daten werden als Punkt oder Vektorkoordinaten in einem byte-Format abgelegt. Um diese in ein lesbares Textformat zu konvertieren, ist folgender cast notwendig:
"[{0}].STAsText() as [{0}]".format(columnname)
Beim Einfügen der Textversion in eine Spalte des Typs geography wird die Konversion in das byte-Format offensichtlich automatisch durchgeführt
==== Beispiel-Code für das Konvertieren in Select-Queries ====
def formatSelectColumns(self, columns = None):
'''wrapping column names with casts and converts for select queries'''
self.selectcols = []
if columns is None:
# get all columns in table
columns = self.columns
for column in columns:
# add cast and convert statements here
if column['coltype'] == 'geography':
self.selectcols.append("[{0}].STAsText() as [{0}]".format(column['colname']))
#raise ValueError('Geography found')
elif column['coltype'] == 'datetime':
#self.selectcols.append("CAST([{0}] as datetime) as [{0}]".format(column['colname']))
self.selectcols.append("CONVERT(varchar(30), {0}, 121) as {0}".format(column['colname']))
elif column['coltype'] == 'xml':
self.selectcols.append("CAST([{0}] as xml) as [{0}]".format(column['colname']))
else:
self.selectcols.append("[" + column['colname'] + "]")
return self.selectcols
----
===== Identity columns in temporary tables (#tables) =====
Wenn Temporär-Tabellen mit
SELECT ... INTO #tablename FROM ...
erzeugt werden, werden fast alle Attribute der Spalten übernommen, auch das Identity-flag und der Wert für nullable. Leider werden DEFAULT-Werte nicht übernommen. Um das Setzen der Identity-Columns zu verhindern gibt es verschiedene Wege, siehe
https://docs.microsoft.com/en-us/sql/t-sql/queries/select-into-clause-transact-sql#data-types
----
===== SET NOCOUNT =====
Der SQL- Befehl ''SET NOCOUNT ON;'' muss häufig am Anfang von Procedures übergeben werden, da damit Fehlermeldungen und das Zählen der Ergebnisse abgeschaltet wird und so der Meldungs-Puffer in MS SQL nicht überläuft und den Befehl blockiert. Der Cursor von pyodbc braucht aber für SELECT-Abfragen die Zahl der Ergebnisse um auch bei 0 Ergebniszeilen etwas zurück zu geben.
Deshalb: Nach jedem ''SET NOCOUNT ON;'' wieder ein ''SET NOCOUNT OFF;'' ausführen.
----
===== Multi-row inserts mit "INSERT INTO () VALUES(), (), ()" =====
Beim Einfügen von mehreren Zeilen über VALUES werden die in VALUES stehenden Listen von MS-SQL-Server als [[https://docs.microsoft.com/en-us/sql/t-sql/queries/table-value-constructor-transact-sql?view=sql-server-2017|Table Value Constructor]] behandelt. Das bedeutet, dass alle Werte einer Spalte implizit zu dem Datentyp konvertiert werden, der die höchsten Einschränkungen hat. Enthalten die Listen für eine Spalte Integer und String Werte versucht MS-SQL alle Werte der Spalte zu Integer zu konvertieren, was mit einer Fehlermeldung endet:
CREATE TABLE [#testtable] ([col1] varchar(255))
INSERT INTO [#testtable] ([col1]) VALUES ('a'), (2)
SQL Error [245] [22018]: Conversion failed when converting the varchar value 'a' to data type int.
Um das zu verhindern, müssen die Werte explizit in den Datentyp der Zieltabelle umgewandelt werden, also alle Integer-Werte in Strings verpackt, wenn die Zielspalte den Typ varchar oder text hat.
CREATE TABLE [#testtable] ([col1] varchar(255))
INSERT INTO [#testtable] ([col1]) VALUES ('a'), ('2')
In Python mit pyodbc würde ich das so lösen, allerdings müssen die None-Values dann extra behandelt werden und das Ganze ist eher ein unschöner patch.
createquery = "create table [#testtable] ([col1] varchar(255))"
cur.execute(createquery)
insertquery = "INSERT INTO [#testtable] ([col1]) VALUES (?), (?)"
values = [str(value) if value is not None else value for value in values]
cur.execute(insertquery, values)
Ich bin mir nicht sicher, ob man dieses Verhalten des MS-SQL-Servers nicht as Bug ansehen sollte, da ja nicht auf den Datentyp der Zieltabelle hin konvertiert wird, sondern auf einen Datentyp innerhalb der übergebenen Werte.
----
===== pyodbc execute mit Parametern in #Tables =====
**Problem**: Der Aufruf von ''SELECT column1, column2 INTO #TempTable FROM Table1 WHERE column1 = ?'' mit Platzhaltern führt zu einer temporären Tabelle, die nicht mehr gefunden werden kann. Queries mit Platzhaltern werden von MSSQL in einer Prozedur ausgeführt, diese legt dann auch die Tabelle in dem Scope der Prozedur an. Sie ist also außerhalb der Prozedur nicht sichtbar. https://github.com/mkleehammer/pyodbc/issues/263
**Lösung**: Erst eine Abfrage zur Erstellung der leeren temporären Tabelle: ''SELECT TOP 0 column1, column2 INTO #TempTable FROM Table1'', dann die gewünschten Daten mit Insert und Parametern einfügen: ''INSERT INTO #TempTable SELECT column1, column2 FROM Table1 WHERE column1 = ?''
----
===== SQL-Funktionen für Datenbank Schema =====
pyodbc hat ein paar Wrapper, die MS SQL-Funktionen zur Aufklärung des Datenbank-Schemas einhüllen, zB. cur.primaryKeys():
Siehe unter: https://github.com/mkleehammer/pyodbc/wiki/Cursor
Beispiel für Foreign Keys:
''rows = self.cur.foreignKeys(foreignTable = 'CollectionEventLocalisation')'' gibt die Foreign keys in CollectionEventLocalisation zurück und die Tabellen und Primärschlüssel auf die sie verweisen:
* CollectionEventLocalisation.CollectionEventID verweist auf CollectionEvent.CollectionEventID
* CollectionEventLocalisation.LocalisationSystemID verweist auf LocalisationSystem.LocalisationSystemID
''rows = self.cur.foreignKeys(table = 'CollectionEvent')'' gibt die Tabellen und Schlüssel zurück, die auf Primärschlüssel in CollectionEvent verweisen:
* CollectionEventLocalisation.CollectionEventID verweist auf CollectionEvent.CollectionEventID
* CollectionSpecimen.CollectionEventID verweist auf CollectionEvent.CollectionEventID
----