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
https://msdn.microsoft.com/en-us/library/ms191530(v=sql.110).aspx
https://msdn.microsoft.com/en-us/library/ms712514(v=vs.85).aspx
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 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 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
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
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
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.
Beim Einfügen von mehreren Zeilen über VALUES werden die in VALUES stehenden Listen von MS-SQL-Server als 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.
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 = ?
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:
rows = self.cur.foreignKeys(table = 'CollectionEvent') gibt die Tabellen und Schlüssel zurück, die auf Primärschlüssel in CollectionEvent verweisen: