ZEOS/sqlite3: Änderungen gelangen nicht in die Datenbank

[sierdolg]

Liebe Lazarus-Datenbank-Profis,

auf einem Formular befindet sich ein DBGrid und einige DBEdit-Komponenten. Ersteres ist an eine komplexere TZQuery gebunden, welche für einen Rechner (die Abfrage wird zur Laufzeit erstellt) eine Liste von Softwareprodukten anzeigt, dazu ein Zeitpunkt der letzten generellen Bereitstellung von Updates für diese Software, die lokal zuletzt installierte Version samt Installationsdatum, die auf dem Fileserver verfügbare jüngste Version sowie der daraus berechnete Status für die lokale Installation(fehlt, veraltet oder aktuell) - liest sich viel komplizierter als es ist, denn das Ganze ist letztlich nichts weiter als ein recht praktisches "Installationsprotokoll" mit zentraler Updatefunktion für den Fileserver.

Die Daten dafür stecken in einer sqlite3-Datenbank auf dem Fileserver in einigen recht simpel gehaltenen Tabellen (tblHost, tblInstallation, tblSoftware, tblVersion).

Einige Buttons unter dem Formular sollen die Suche nach Updates (auf einem in tblSoftware installierten URL), deren Ablage auf dem Fileserver oder die schnelle Installation der im Grid ausgewählten Software vom Fileserver ermöglichen.

Soweit alles prima - das ganze war früher ein in LibreOffice Base realisiertes Werkzeug, das sich monatelang bewährt hat, jedoch seit einem Versionswechsel von LO nicht mehr funktioniert und das ich daher gern in Lazarus nachbauen würde.

Der Showstopper ist allerdings seit zwei Tagen, daß es partout nicht gelingt, ein Datumsfeld in der Datenbank aus Programmcode dauerhaft zu aktualisieren.

Wie zum Hohn funktioniert es in einer stark vereinfachten Version, die ich zu Testzwecken nochmals zusammengestellt habe, um es hier zu posten. Hier ist die Select-Anweisung für das vereinfachte Grid:
:

Code: Alles auswählen

SELECT 
30 as hid, 
s.sid as sid, 
s.name as name, 
v.vid as akt_vid,
DATETIME(s.bereitgestellt) as bereitgestellt
FROM 
 (SELECT vid, software, MAX(erschienen) As Datum FROM tblVersion GROUP BY software) As v
  LEFT JOIN tblSoftware as s   
  ON v.software=s.sid  
WHERE sid > 0
 

Die DBEdit-Felder hängen an einer zweiten TZQuery, die im wesenlichen nur den Inhalt der Softwaretabelle durchreicht:

Code: Alles auswählen

SELECT sid, name, bereitgestellt FROM tblSoftware;

Die automatische Master-Detail-Darstellung wird dabei durch das Feld sid (Primärschlüssel in tblSoftware) erreicht.

"bereitgestellt" ist ein Feld, welches in sqlite als "DATETIME" eingestellt ist. Sein Name ist leider unglücklich gewählt, gemeint ist das Datum, an welchem zuletzt manuell nach Updates gesucht wurde. Es soll aus Code heraus auf das aktuelle Datum gesetzt werden, im Demo-Projekt gelingt das testweise durch einen Button:

Code: Alles auswählen

procedure TForm1.btnAktualisierenClick(Sender: TObject);
begin
  DBEditBereitstellung.DataSource.DataSet.Edit;
  DBEditBereitstellung.DataSource.DataSet.FieldByName('bereitgestellt').AsDateTime:=Now();
  DBEditBereitstellung.DataSource.DataSet.UpdateRecord;  // offenbar entbehrlich
  DBEditBereitstellung.DataSource.DataSet.Post;
 
  DBGrid1.DataSource.DataSet.Close;
  DBGrid1.Refresh;   // offenbar ebenfalls entbehrlich
  DBGrid1.DataSource.DataSet.Open;
end;
 
 

Eine analoge Anweisungsfolge im eigentlichen Projekt führt allerdings nur dazu, daß zwar im DBEdit-Feld der aktuelle Datumsstempel erscheint, nicht aber im DBGrid. Beim erneuten Start der Anwendung ist auch das DBEdit wieder mit den vorigen Werten befüllt, was m.E. nahelegt, daß hier nicht das Aktualisieren des Grids das Problem ist, sondern die Daten gar nicht bis in die Datenbank gelangen...?

Alternativ hatte ich das Ziel mit einer SQL-UPDATE-Anweisung zu erreichen versucht. Auch hier: Das DBEdit-Feld sieht aktualisiert aus, das Grid nicht, und nach dem Programmende sind die Daten nicht gespeichert worden.

Wie könnte man hier weiter systematisch vorgehen? Das Ausprobieren aller Lösungsvorschläge in den zahlreichen Postings zum sinngemäßen Thema "dbgrid aktualisiert sich nicht" hat mich nicht weitergebracht, ebensowenig das Herumspielen an diversen Properties wie AutoCommit oder CachedUpdates, die nach meinem Dafürhalten im beschriebenenen Problemkontext relevant sein könnten. Ganz augenscheinlich habe ich doch noch etwas Nichttriviales übersehen - oder sitze tatsächlich einem Bug auf? Laufzeitfehler treten jedenfalls keine auf, nur die geänderten Daten verschwinden still im Nirwana.

Die komplexe SQL-Abfrage in der ZQuery des Grids ist geschachtelt, aber das sollte für ein "Requery" (auch wenn es hier nicht so heißt) ja ohne Belang sein.


Versions-Kontext: Lazarus 1.0.14, 64-Bit-Version, auf Windows 8.1 mit Zeos 7.1

[Michl]

Leider kann ich Dir nicht die Lösung für Dein Problem sagen, vielleicht helfen Dir ja folgende Anregungen?:
- ein Minimlabsp erstellen, wo das Problem ersichtlich ist und hier posten (als Zip)
- hast Du schon mal probiert, das Ganze statt ...DataSet.Edit, ...Update, ...Post etc. mittels SQL zu lösen?!
- die gleiche Verbindung mal in einem Minimalbsp mit den Lazaruskomponenten nachbauen und schauen, ob diese den gewünschten Zugriff ermöglichen

[hde]

Eine analoge Anweisungsfolge im eigentlichen Projekt

weicht doch offensichtlich von der hier gezeigten ab, ist jedenfalls zu vermuten Ohne mehr Code kann man das so nicht lösen.

hde

[af0815]

Mir fallen zweis Sachen zu dem Thema ein.

a) Die Querie muss überhaupt das ändern zulassen. Joins sind da oft Showstopper. Da gegebenfalls das Statement für update selbst richtig setzen.
b) ApplyUpdates verwenden, damit die Änderungen auch vom geänderten Zwischenpuffer in die DB übertragen werden - kann bei Zeos unter Umständen anders gehandelt werden.

Ich mache komplexere DB ENtwicklungen gerne an einem DB Server, wo ich mir auch die Statements die vom Programm kommen ansehen kann. Damit ist es möglich sich sehr viel Ärger zu sparen. Bei Mysql sollte die Tools in der Workbech zu finden sein, da sieht man welche Statements vom Programm gekommen sind. Da wird meist schnell klar, wo das Problem liegt.

[sierdolg]

Vielen Dank für die zahlreichen Antworten und Vorschläge!

Nachfolgend die zwei relevanten Abschitte aus dem Originalprojekt. Zunächst der Button, der zum Einpflegen neuer Versionen (bzw. zum Aktualisieren des Zeitstempels, falls es nichts neues gibt; Sinn: man braucht nur bei den schon länger nicht mehr geprüften Anwendungen manuell nach Updates schauen) ausgebaut werden soll. Er soll also in beiden Fällen (Update vorhanden oder festgestellt, daß es keine gibt) den Zeitstempel aktualisieren. Im Ja-Fall versuche ich das mit DataSet.Edit, im Nein-Fall mit einer UPDATE-SQL-Anweisung; beides erfolglos: Im Ja-Fall wird wenigstens das an zqSoftware/dsSoftware gebundene DBEdit aktualisiert, im Nein-Fall geschieht gar nichts (aber eben auch keine Exception). Und nach Neustart der Anwendung sind alle Daten wieder im vorigen Zustand.

Code: Alles auswählen

 
procedure TFormLabSoft.ButtonDownloadClick(Sender: TObject);
var
   strUpdateQuery, strSQL: String;
   myBookmark : TBookmark;
begin
  Case (MessageDlg('Wurde eine neue Version bereitgestellt?',mtCustom,mbYesNoCancel,0)) of
       mrYes: begin
               DBGridUebersicht.DataSource.DataSet.Close;
               DBEditBereitgestellt.DataSource.DataSet.Edit;
               DBEditBereitgestellt.DataSource.DataSet.FieldByName('bereitgestellt').AsDateTime:=Now();
               DBEditBereitgestellt.DataSource.DataSet.UpdateRecord;
               DBEditBereitgestellt.DataSource.DataSet.Post;
               DBGridUebersicht.DataSource.DataSet.Open;
              end;
       mrNo: begin
               strUpdateQuery:='UPDATE tblSoftware '
                  + 'SET bereitgestellt=DATETIME(''now'',''localtime'') '
                  + ' WHERE sid='+dm1.zqUebersicht.FieldByName('sid').AsString+';';
               MemoLog.Append(strUpdateQuery);
               runActionQuery(strUpdateQuery);
               DBGridUebersicht.Refresh;
             end;
       mrCancel: ;
   end;
end;

Das Steuerelement DBEditBereitgestellt ist an die DataSource dsSoftware und diese an das DataSet zqSoftware gebunden, letztere hat eingestellt: CachedUpdates=False, Filtered=false, LinkedFields=sid, MasterFields=sid, ReadOnly=False, SQL='SELECT sid, name, ibs, datetime(bereitgestellt) as bereitgestellt, downloadurl, notizen FROM tblSoftware WHERE sid>0;' (also kein Join), UpdateMode=umUpdateChanged, WhereMode=wmWhereKeyOnly.

Nachfolgend die Hilfsfunktion für AKtionsabfrage im Nein-Fall:

Code: Alles auswählen

 
function TFormLabSoft.runActionQuery(strSQL: String): Integer;
var
   rwDataSet : TZQuery;
begin
   rwDataSet:= TZQuery.Create(nil);
   rwDataSet.Connection:=dm1.ZCon1;
   Result:= 0 ;
   try
     rwDataSet.SQL.Text:=strSQL;
     rwDataSet.Open;
   except     //on E: SysUtils.Exception do ShowMessage(E.Message);
     on E : Exception DO
       begin
        ShowMessage('RunActionQuery meldet Fehler: '+E.Message);
        Result := 1
       end;
   end;
   rwDataSet.Close;
   rwDataSet.Free;
end;     
 

Der ihr im Nein-Fall übergebene String lautet beispielsweise
'UPDATE tblSoftware SET bereitgestellt=DATETIME('now','localtime') WHERE sid=2;'.
Wenn ich das im SQL-Editor von http://sqlitestudio.pl/%20sqlitestudio ausführe, funktioniert sie wie beabsichtigt, und die Daten landen permanent in der db3-Datei.

Ich versuche das ganze jetzt noch über gemappte Laufwerksbuchstaben an Stelle der bisher verwendeten UNC-Pfade, die freilich der Pfiff gewesen wären. Nachtrag: Leider keine Änderung.

[sierdolg]

Mit

Code: Alles auswählen

 
             strUpdateQuery:='UPDATE tblSoftware '
                + 'SET bereitgestellt=DATETIME(''now'',''localtime'') '
                + ' WHERE sid='+dm1.zqUebersicht.FieldByName('sid').AsString+';';
             MemoLog.Append(strUpdateQuery);
             DBGridUebersicht.DataSource.DataSet.Close;
             DBEditBereitgestellt.DataSource.DataSet.Close;
             runActionQuery(strUpdateQuery);
             DBEditBereitgestellt.DataSource.DataSet.Open;
             DBGridUebersicht.DataSource.DataSet.Open;    
 

erscheinen die Änderungen sowohl im Grid als auch im DBEdit-Feld.
Umso erstaunlicher (und für mich unverständlicher), daß sie nach dem Neustart der Anwendung dennoch abermals verschwunden sind...

[af0815]

...erscheinen die Änderungen sowohl im Grid als auch im DBEdit-Feld.
Umso erstaunlicher (und für mich unverständlicher), daß sie nach dem Neustart der Anwendung dennoch abermals verschwunden sind...

Sieht so aus als wären die Daten nur im lokalen Cache gespeichert. Bei einer Query ist dann noch ein ApplyUpdates notwendig, so das die Änderungen vom lokalen Cache in die Datenbank übernommen werden.

[hde]

Spontan, ohne den code zu checken: das klingt nach Transaction-Rollback
Wie steht den AutoCommit in der TZConnection?
hde

[hde]

Irgendwie erscheint mir dein code sehr umständlich/kompliziert und agr nicht mein Stil.
warum close vorm update/execute?

DBEditBereitgestellt.DataSource.DataSet.FieldByName('bereitgestellt').AsDateTime:=Now();

geht's noch umständlicher? sorry ..
TZQuery.FieldByName('bereitgestellt').AsDateTime:=Now(); reicht aus .. oder Editfield füllen

strUpdateQuery:='UPDATE tblSoftware '
+ 'SET bereitgestellt=DATETIME(''now'',''localtime'') '
+ ' WHERE sid='+dm1.zqUebersicht.FieldByName('sid').AsString+';';
strUpdateQuery.ExecSQL; // fehlt

hde

[sierdolg]

Hallo hde,

dann sind wir uns ja einig. Ich hab mir vorhin das

Code: Alles auswählen

//grauenvoll! 

selbst verbissen.
Das Gezeigte ist lediglich das Ergebnis von mittlerweile drei Tagen erfolglosem Suchen nach einer im Zweifelsfall grauenvoll aussehenden Lösung, die aber wenigstens funktioniert, und die man später bei Zuwachs an Erkenntnis wieder eleganter kürzen kann.

Ganz nach der Bauernregel "der dümmste Lazarus-Programmierer hat die längsten Zeilen "

Das leidige Problem: Solange man nicht weiß, wie etwas heißt, kann man gar nicht danach suchen. Solange man keine Best-Practice-Beispiele hat, kann man sie auch nicht nachvollziehen. Eine große Firma jenseits des Atlantik hat dieses Problem wohl deshalb mit ihrer "Nordwind"-Datenbank ganz gut gelöst - für Lazarus (und genauer gesagt wohl auch: Zeos-Komponenten) habe ich da noch nicht viel gefunden. Das Lazarus-Buch (Von Canneyt et al.) hab ich mir gekauft, es hilft hier speziell aber auch nur begrenzt. Geradezu fantastisch sind die Beispiele auf http://lazplanet.blogspot.de/, nur leider ist dort die Kategorie Databases bis heute leer.

Nun bin ich weder Informatiker noch professioneller Entwickler noch früherer Delphi-Nutzer, so daß mir manches gelinde gesagt schon sehr spanisch vorkommt. Bei vielen Properties suggeriert der Name (und die Dokumentation im Hilfebrowser) zwar eine vage Vorstellung ihres Sinns, aber eben keine ganz präzise - und Halbwissen ist erwiesenermaßen oft schlimmer als Unwissen.
Nachdem ich gestern irgendwo las - was ja gut und gern auch falsch sein mag - daß man bei Lazarus-Datenbankanwendungen "um Himmels willen nicht auf der Ebene der GUI-Controls" arbeiten solle, sondern immer auf den darunterliegenden Datasets, entstand eben der Watt?wurm "DBEditBereitgestellt.DataSource.DataSet.FieldByName('bereitgestellt').AsDateTime:=Now();".

Früher benutzte ich gelegentlich MS Access, dort reicht es simpel, einem datengebundenen Textfeld einen String zuzuweisen und um den Rest kümmert sich zuverlässig und wirklich transparent das Steuerelement. Seit einigen Jahren bin ich auf LibreOffice umgestiegen, wo es halbwegs ähnlich funktioniert. Daher war mein erster blauäugiger Versuch auch:

Code: Alles auswählen

DBEditBereitgestellt.Field.Value:=now()

Konkret liegen meine Datasets und Datasources in einem DataModule. Ich müßte also Deinen Vorschlag

Code: Alles auswählen

dm1.zqSoftware.FieldByName('bereitgestellt').AsDateTime:=Now();

schreiben. Wäre das dann wirklich genau gleichwertig mit

Code: Alles auswählen

DBEditBereitgestellt.DataSource.DataSet.FieldByName('bereitgestellt').AsDateTime:=Now();

oder arbeite ich dann nicht sozusagen "parallel" und schlimmstenfalls noch an den DataAccess-Controls "vorbei"?

AutoCommit in der TZConnection: steht auf True.
Da ich inzwischen vor nichts mehr zurückschrecke, habe ich eben folgendes versucht:

Code: Alles auswählen

procedure TFormLabSoft.FormCloseQuery(Sender: TObject; var CanClose: boolean);
begin
  dm1.ZCon1.AutoCommit:=False;
  dm1.ZCon1.Commit;
end;

Das führt beim Beenden nach längerem Stillstand zu "SQL Error: database is locked." An dem kommt man nicht vorbei, außer mit Cancel (Kill). Dann sind erwartungsgemäß die Daten wieder weg. Aber wenn ich die Entwicklungsumgebung schließe und das Binary starte, funktioniert es! Sieht so aus, als war's das gewesen!

Nochmals danke, denn das war offenbar das relevante Stichwort.

(Wobei zwei Fragen bleiben: warum passiert das dann nicht auch in dem Demo-Beispiel, das ich gestern in der Absicht zu posten erstellt habe? Es liegt bei. Und debuggen kann man dann wohl auch nur, wenn man jedes Mal vorher ZConnection.Connected auf False schaltet?)

[hde]

OK, war auch nicht bös gemeint .. werd mir deine Sache nochmal anschaun wenn ich später mal eewas Luft habe.
Aber vorab:

"um Himmels willen nicht auf der Ebene der GUI-Controls"

das ist natürlich völliger Quatsch .. wozu sind die GUI-Controls dennn sonst da??

DBEditBereitgestellt.Field.Value:=now()

das reicht bei Lazarus (und Delphi) völlig aus

[hde]

Dein Beispiel kann ich auf die Schnelle nicht nachvollziehen weil ich erst eine entsprechende Tabelle creieren müsste.
Welche Felder sind wie definiert?
Ist bereitgestellt eine Datumsfeld? wenn ja, warum dann die Umwandlung im select?
auch bei s.sid as sid ist die Zuweisung nicht notwendig, wenn auch nicht störend.
Dein select mit dem join ist afaik auch kein sauberes SQL
SQLite kennt kein Transaction und deshalb auch keinen commit. AutoCommit im TZConnect sollte aber true sein, damit Zeos sofort wegschreibt und nichts im Cache hält. Bei einem Datenbankserver muss man aber TransactionIsolationLevel beachten und entweder AutoCommit einschalten oder den commit selbst machen, sonst kann es ggf. sein, dass alle Änderungen während der kompletten Session durch einen AutoRollback zurückgesetzt und damit u.U. die Arbeiten eines ganzen Tages vernichtet werden.
Nur nebenbei: mit Lazarus kann man auch eine einfache Datenbankanwendung ohne eine einzige Zeile Code erstellen. TZConnect, TZQuery, TDatasource, TDBGrid und TDBNavigator reichen aus
hde

[Edit]hab deine Test.db3 übersehen, schau also nochmal rein

[hde]

Also irgendwie hast du dein Projekt so "gut" konfiguriert, dass die DB test.db3 so gut wie "read-only" ist. Woran das liegt müsste man mal noch herausfinden.
Ich hab's einfach neu aufgesetzt, schau dir's an.
hde

[mse]

SQLite kennt kein Transaction und deshalb auch keinen commit.

Kleine Korrektur, Sqlite3 hat Transaktionen:
http://www.sqlite.org/lang_transaction.html

Martin

[sierdolg]

Hallo hde, erst mal tausend Dank für Deine Mühe; das war jetzt doppelt aufschlußreich!

Erstens: Dein Projekt verhält sich nämlich genauso wie meins - es aktualisiert nichts, solange die IDE im Hintergrund die sqlite-DB über ihre ZConnection "connected" hat.
Da kann man lange sonstwo suchen, aber das war wohl mein Fehler! Hätte nicht gedacht, daß man das jedes Mal von Hand abschalten muß, bevor man die eigene Anwendung startet, weil die Designvorschau doch erstens nur readonly zugreift (nehme ich an) und zweitens man nicht mal den Datenbankcursor bewegen kann (jedenfall habe ich nicht bemerkt, wie man das könnte, also z.B. in ein Grid durchbrowsen).

Zweitens: Du schreibst, das Minimalprojekt in test_dbgrid_requery.7z sei 'so gut wie "read-only"'. Dabei läuft das (ich schrieb ja auch extra "wie zum Hohn") bei mir einwandfrei, d.h. zeigt die Änderungen sofort in beiden Controls an und speichert sie auch permanent, und zwar - Hammer Nr. 2 - auch dann, wenn der Designmodus an ist und die IDE demnach auch ihre Verbindung zur Datenquelle offen hat. Gerade drum war die Schlußfolgerung nicht auf den ersten Blick zu ziehen.

Was genau den Unterschied zwischen den beiden Projekten ausmacht, sehe ich nicht. Was genau war bei Dir also an meinem Beispiel 'so gut wie "read-only"'? Eigentlich haben wir ja nur die unterschiedliche sqlite-DLLs als wesentlichen Unterschied (bei Dir offenbar 32bit, meine ist 64bit).

Ich hab mich auch schon öfters gewundert, warum Controls ihre letzten Werte beim Beenden der Anwendung manchmal in der Datenbank speichern (wie man es sich wünscht), aber manchmal doch nicht, und zwar selbst dann, wenn sie zuletzt gar nicht mehr den Fokus hatten (letzteres könnte man zur Not noch verstehen). Auch andere haben offenbar das Problem schon bemerkt und eigene Lösungen dafür gefunden (http://www.lazarusforum.de/viewtopic.php?f=55&t=7534). Es gibt also wohl - neben dem Transaktionswesen - auch noch viele Fallgrübchen im Lazarus-sqlite-Land zu entdecken... ,-)