Forum > Databases
Replace substring in a field and update DB
cdbc:
Hi
Could be something like this:
--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---const lSql = 'Select topic_id,data from content;'; // <--- no typecast, use as blobvar BlobStream: TStream; MStream: TMemoryStream;//...begin // connect to db and attach a query Query.SQL.Text:= lSql; Query.Open; Query.First; while not Query.EOF do begin // do stuff with topic_id... BlobStream:= Query.CreateBlobStream(Query.FieldByName('data'),bmRead); BlobStream.Position:= 0; // e.g: use RichMemo to load the rtf-blob RichMemo1.LoadFromStream(BlobStream); BlobStream.Free; // now, using RichMemo1, search for '19.3.2' and replace with '19#3#2' MStream:= TMemoryStream.Create; RichMemo1.SaveToStream(MStream); MStream.Position:= 0; // now, using another query "Exec" connected to same db Exec.SQL.Text:= 'UPDATE content SET data=:pdata WHERE topic_id=:pid;'; Exec.Prepare; // write the changed data back Exec.ParamByName('pdata').LoadFromStream(MStream,ftBlob); Exec.ParamByName('pid').AsInteger:= Query.FieldByName('topic_id').AsInteger; Exec.ExecSQL; Exec.UnPrepare; MStream.Free; Query.Next; end;end;
Untested code, just put it together from fragments of my own.
Hth - Benny
WJSwanepoel:
What a mission to get even the simplest stuff working in Lazarus. It is for sure the most finicky language I have ever used, and I have used close to a dozen.
I am giving up and abandoning the project completely.
Thanks to everyone who tried to assist.
Thaddy:
What is difficult in:
- retrieve a an record from a database
- change the field you are interested in its content with replace
- post and commit the changed record to the database
I give you that some of the above replies were not very helpful, but I would not give up. It is really easy.
Actually, any novice in Lazarus should be able to program those steps without any difficulty.
WJSwanepoel:
Then show me how please.
I have a SQLite3 database with a table called content it has two fields topic_id (integer) data (BLOB - RTF format)
I need to read all records and replace certain substrings with another eg "19.3.2" must become "19#3#1"
I have these strings in another SQLite3 database called repstr - two string fields istr, ostr. I need to seach for istr in all records and replace by ostr.
Thaddy:
The way you want to make the changes is yours, but basically here's an example that you can adapt to your needs:
--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---program sqlite_test;{$mode objfpc}{$H+}{$ifdef mswindows}{$apptype console}{$endif}uses SysUtils, sqldb, sqlite3conn; var Conn: TSQLite3Connection; Query: TSQLQuery; Id, TextField: string; begin Conn := TSQLite3Connection.Create(nil); Conn.DatabaseName := 'MyDatabase.db'; // contains records with two fields: ID and a Vartext field Conn.Connected := True; Query := TSQLQuery.Create(nil); Query.Database := Conn; Query.SQL.Text := 'SELECT * FROM MyTable WHERE Id = :Id'; Id := '1'; Query.ParamByName('Id').AsString := Id; Query.Open; TextField := Query.FieldByName('TextField').AsString; Writeln('The value of TextField is: ', TextField); // Change the value of TextField Query.Edit; Query.FieldByName('TextField').AsString := 'New Text'; // Commit the change Query.Post; Query.ApplyUpdates; Query.Free; Conn.Free;end.It is scary how simple that is... Look at the time that cost me and I am not a record breaking typist.
Navigation
[0] Message Index
[#] Next page
[*] Previous page