Replace substring in a field and update DB

[WJSwanepoel]

I am new to Lazarus and FreePascal and need some help.

I have a small program which opens a SQLite3 database and which does a TSQLQuery on a table in the database:

    Select topic_id, cast(data as varchar) as data1  from content; [data is a rtf BLOB]

I now wish to process all records returned by the TSQLQuery where I replace a substring ("19.3.2") of data1 with "19#3#1". I then wish to update the changed record in the query results and on the physical database.

How do I achieve this?

[Nicole]

Which component do you use to connect Lazarus to the db?

[WJSwanepoel]

Currently I use TSQLConnector.
I Notice there is also one called TSQLite3Connection - is this the better one to use? If so I would still need guidance with how to process records in a query as stated in my post.

[Nicole]

Does this help you?
https://wiki.freepascal.org/TSQLite3Connection

As I am working with Firebird, I am not good at other databases.
I use IBX with much comfort, not sure, if it works with SQListe as well.

But I am sure, here are a lot of people around who do know.
In other words: If you have more than one task, perhaps you may post an request for components and tutorials.

[WJSwanepoel]

Hi Nicole,
Thanks for the link, it looks promising and I will give it a go.

I gave it a go. The code at that link (https://wiki.freepascal.org/TSQLite3Connection) does not even execute! It fails on exitcode 217.

[cdbc]

Hi
Could be something like this:

Code: Pascal  [Select][+][-]

1. const lSql = 'Select topic_id,data from content;'; // <--- no typecast, use as blob
2. var
3.   BlobStream: TStream;
4.   MStream: TMemoryStream;
5. //...
6. begin
7.   // connect to db and attach a query
8.   Query.SQL.Text:= lSql;
9.   Query.Open;
10.   Query.First;
11.   while not Query.EOF do begin
12.     // do stuff with topic_id...
13.     BlobStream:=  Query.CreateBlobStream(Query.FieldByName('data'),bmRead);
14.     BlobStream.Position:= 0;
15.     // e.g: use RichMemo to load the rtf-blob
16.     RichMemo1.LoadFromStream(BlobStream);
17.     BlobStream.Free;
18.     // now, using RichMemo1, search for '19.3.2' and replace with '19#3#2'
19.     MStream:= TMemoryStream.Create;
20.     RichMemo1.SaveToStream(MStream);
21.     MStream.Position:= 0;
22.     // now, using another query "Exec" connected to same db
23.     Exec.SQL.Text:= 'UPDATE content SET data=:pdata WHERE topic_id=:pid;';
24.     Exec.Prepare;
25.     // write the changed data back
26.     Exec.ParamByName('pdata').LoadFromStream(MStream,ftBlob);
27.     Exec.ParamByName('pid').AsInteger:= Query.FieldByName('topic_id').AsInteger;
28.     Exec.ExecSQL;
29.     Exec.UnPrepare;
30.     MStream.Free;
31.     Query.Next;
32.   end;
33. end;
34.  

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  [Select][+][-]

1. program sqlite_test;
2. {$mode objfpc}{$H+}{$ifdef mswindows}{$apptype console}{$endif}
3. uses SysUtils, sqldb, sqlite3conn;
4.  
5. var
6.   Conn: TSQLite3Connection;
7.   Query: TSQLQuery;
8.   Id, TextField: string;
9.  
10. begin
11.   Conn := TSQLite3Connection.Create(nil);
12.   Conn.DatabaseName := 'MyDatabase.db';  // contains records with two fields: ID and a Vartext field
13.   Conn.Connected := True;
14.  
15.   Query := TSQLQuery.Create(nil);
16.   Query.Database := Conn;
17.   Query.SQL.Text := 'SELECT * FROM MyTable WHERE Id = :Id';
18.  
19.   Id := '1';
20.  
21.   Query.ParamByName('Id').AsString := Id;
22.   Query.Open;
23.  
24.   TextField := Query.FieldByName('TextField').AsString;
25.   Writeln('The value of TextField is: ', TextField);
26.  
27.   // Change the value of TextField
28.   Query.Edit;
29.   Query.FieldByName('TextField').AsString := 'New Text';
30.  
31.   // Commit the change
32.   Query.Post;
33.   Query.ApplyUpdates;
34.  
35.   Query.Free;
36.   Conn.Free;
37. end.

It is scary how simple that is... Look at the time that cost me and I am not a record breaking typist.

[WJSwanepoel]

Thank you. I will give it one more try.

[Thaddy]

I made a small addition for when you are working on windows: {$apptype console}

[WJSwanepoel]

Hi Thaddy,
I tried a simple example and quess what IT FAILED!!

Code: Pascal  [Select][+][-]

1. program sqlite_test;
2. {$mode objfpc}{$H+}{$ifdef mswindows}{$apptype console}{$endif}
3. {$apptype console}
4. uses SysUtils, sqldb, sqlite3conn;
5.  
6. var
7.   Conn: TSQLite3Connection;
8.   Query: TSQLQuery;
9.   Id:integer;
10.   TextField:string;
11.  
12. begin
13.   Conn:=TSQLite3Connection.Create(nil);
14.   Conn.DatabaseName:='SQlite.gbl.twm';
15.   Conn.Connected:=True;
16.  
17.   Query:=TSQLQuery.Create(nil);
18.   Query.Database := Conn;
19.   Query.SQL.Text := 'SELECT topic_id, data FROM content WHERE topic_id = :id';
20.  
21.   Id:=2;
22.  
23.   Query.ParamByName('Id').AsInteger := Id;
24.   Query.Open;
25.  
26.   TextField := Query.FieldByName('data').AsString;
27.   Writeln('The value of TextField is: ', TextField);
28.  
29.   // Change the value of TextField
30.   Query.Edit;
31.   Query.FieldByName('data').AsString := 'Hierdie is my beste poging...';
32.  
33.   // Commit the change
34.   Query.Post;
35.   Query.ApplyUpdates;
36.  
37.   Query.Free;
38.   Conn.Free;
39. end.
40.  

It fails on:
An unhandled exception occurred at $00000000:
EAccessViolation: Access violation
  $00000000
  $0044651B
  $004475A0

Now that is really a good explanation of what went wrong! Can you explain it in plain English?

My Table on the db is defined as follows:
CREATE TABLE content(topic_id integer primary key, data BLOB)

[TRon]

@Thaddy:
Die Deutsche Bank does not compute very well without transactions...

@WJSwanepoel:

It fails on:
An unhandled exception occurred at $00000000:
EAccessViolation: Access violation
  $00000000
  $0044651B
  $004475A0

Now that is really a good explanation of what went wrong! Can you explain it in plain English?

It is nearly impossible to get that output when you have sysutils included.

You can opt for compiling your program with the option -glh (or when using lazarus: project options/compiler options/debugging/ and set -gl and -gh options)

Or better yet, have a look at https://wiki.freepascal.org/SqlDBHowto#Example:_reading_data_from_a_table

[PierceNg]

It fails on:
An unhandled exception occurred at $00000000:
EAccessViolation: Access violation
  $00000000
  $0044651B
  $004475A0

Now that is really a good explanation of what went wrong! Can you explain it in plain English?

My Table on the db is defined as follows:
CREATE TABLE content(topic_id integer primary key, data BLOB)

I got a better error message with your code:

Code: Text  [Select][+][-]

1. % ./sqlite_test        
2. An unhandled exception occurred at $000000000048F290:
3. EDatabaseError: Transaction not set
4.   $000000000048F290
5.   $0000000000455222

I fixed up your code. See variable "txn" for related changes. It works on my computer^TM:

Code: Text  [Select][+][-]

1. % ./sqlite_test
2. The value of TextField is: Content 2
3.  
4. % sqlite3 SQlite.gbl.twm "select * from content"
5. -- Loading resources from /home/pierce/.sqliterc
6. ┌──────────┬───────────────────────────────┐
7. │ topic_id │             data              │
8. ├──────────┼───────────────────────────────┤
9. │ 1        │ Content 1                     │
10. │ 2        │ Hierdie is my beste poging... │
11. │ 3        │ more content                  │
12. └──────────┴───────────────────────────────┘        
13.  

Code: Pascal  [Select][+][-]

1. program sqlite_test;
2. {$mode objfpc}{$H+}{$ifdef mswindows}{$apptype console}{$endif}
3. {$apptype console}
4. uses SysUtils, sqldb, sqlite3conn;
5.  
6. var
7.   Conn: TSQLite3Connection;
8.   Query: TSQLQuery;
9.   Txn: TSQLTransaction;
10.   Id:integer;
11.   TextField:string;
12.  
13. begin
14.   Conn:=TSQLite3Connection.Create(nil);
15.   Txn := TSQLTransaction.Create(nil);
16.   Conn.Transaction := Txn;
17.   Conn.DatabaseName:='SQlite.gbl.twm';
18.   Conn.Connected:=True;
19.  
20.   Query:=TSQLQuery.Create(nil);
21.   Query.Database := Conn;
22.   Query.SQL.Text := 'SELECT topic_id, data FROM content WHERE topic_id = :id';
23.  
24.   Id:=1;
25.  
26.   Query.ParamByName('Id').AsInteger := Id;
27.   Query.Open;
28.  
29.   TextField := Query.FieldByName('data').AsString;
30.   Writeln('The value of TextField is: ', TextField);
31.  
32.   // Change the value of TextField
33.   Query.Edit;
34.   Query.FieldByName('data').AsString := 'Hierdie is my beste poging...';
35.  
36.   // Commit the change
37.   Query.Post;
38.   Query.ApplyUpdates;
39.   Txn.Commit;
40.  
41.   Query.Free;
42.   Txn.Free;
43.   Conn.Free;
44. end.