Replace substring in a field and update DB
lazarus / 16-Databases

----------------------------------------------------------------------
WJSwanepoel · 2023-01-10 12:18

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 · 2023-01-10 16:56

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

----------------------------------------------------------------------
WJSwanepoel · 2023-01-11 04:17

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 · 2023-01-11 11:18

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 · 2023-01-11 11:36

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 · 2023-01-11 12:20

Hi
Could be something like this:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i 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 · 2023-01-12 14:04

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 · 2023-01-12 14:08

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 · 2023-01-12 14:12

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 · 2023-01-12 14:19

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i 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.

----------------------------------------------------------------------
WJSwanepoel · 2023-01-12 14:21

Thank you. I will give it one more try.

----------------------------------------------------------------------
Thaddy · 2023-01-12 14:28

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

----------------------------------------------------------------------
WJSwanepoel · 2023-01-13 05:20

Hi Thaddy,
I tried a simple example and quess what IT FAILED!!
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i program
sqlite_test;{$mode objfpc}{$H+}{$ifdef mswindows}{$apptype
console}{$endif}{$apptype console}uses SysUtils, sqldb,
sqlite3conn; var  Conn: TSQLite3Connection;  Query: TSQLQuery; 
Id:integer;  TextField:string; begin 
Conn:=TSQLite3Connection.Create(nil); 
Conn.DatabaseName:='SQlite.gbl.twm';  Conn.Connected:=True;  
Query:=TSQLQuery.Create(nil);  Query.Database := Conn;  Query.SQL.Text
:= 'SELECT topic_id, data FROM content WHERE topic_id = :id';  
Id:=2;   Query.ParamByName('Id').AsInteger := Id;  Query.Open;  
TextField := Query.FieldByName('data').AsString;  Writeln('The value
of TextField is: ', TextField);   // Change the value of TextField 
Query.Edit;  Query.FieldByName('data').AsString := 'Hierdie is my
beste poging...';   // Commit the change  Query.Post; 
Query.ApplyUpdates;   Query.Free;  Conn.Free;end. 
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 · 2023-01-13 05:45

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

@WJSwanepoel:
Quote from: WJSwanepoel on January 13, 2023, 06:20:31 amIt 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#E
xample:_reading_data_from_a_table

----------------------------------------------------------------------
PierceNg · 2023-01-13 06:50

Quote from: WJSwanepoel on January 13, 2023, 06:20:31 amIt 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i %
./sqlite_test         An unhandled exception occurred at
$000000000048F290:EDatabaseError: Transaction not set 
$000000000048F290  $0000000000455222
I fixed up your code. See variable "txn" for related changes. It works
on my computerTM:

Code: Text  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i %
./sqlite_testThe value of TextField is: Content 2 % sqlite3
SQlite.gbl.twm "select * from content"-- Loading resources from
/home/pierce/.sqliterc┌──────────┬───────────────────────────────┐│
topic_id │             data            
 │├──────────┼───────────────────────────────┤│ 1        │ Content 1 
                  ││ 2        │ Hierdie is my beste poging... ││ 3   
   │ more content                
 │└──────────┴───────────────────────────────┘         
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i program
sqlite_test;{$mode objfpc}{$H+}{$ifdef mswindows}{$apptype
console}{$endif}{$apptype console}uses SysUtils, sqldb,
sqlite3conn; var  Conn: TSQLite3Connection;  Query: TSQLQuery;  Txn:
TSQLTransaction;  Id:integer;  TextField:string; begin 
Conn:=TSQLite3Connection.Create(nil);  Txn :=
TSQLTransaction.Create(nil);  Conn.Transaction := Txn; 
Conn.DatabaseName:='SQlite.gbl.twm';  Conn.Connected:=True;  
Query:=TSQLQuery.Create(nil);  Query.Database := Conn;  Query.SQL.Text
:= 'SELECT topic_id, data FROM content WHERE topic_id = :id';  
Id:=1;   Query.ParamByName('Id').AsInteger := Id;  Query.Open;  
TextField := Query.FieldByName('data').AsString;  Writeln('The value
of TextField is: ', TextField);   // Change the value of TextField 
Query.Edit;  Query.FieldByName('data').AsString := 'Hierdie is my
beste poging...';   // Commit the change  Query.Post; 
Query.ApplyUpdates;  Txn.Commit;   Query.Free;  Txn.Free; 
Conn.Free;end.

----------------------------------------------------------------------
Thaddy · 2023-01-13 07:33

Yes,yes, I did not include a table/database, but it simply works when
a correct one is present.
Of course the code is brief , exception handling etc and tr/ finally
is another matter too.
Hey I wrote in minutes as a guiding example.

----------------------------------------------------------------------
WJSwanepoel · 2023-01-13 08:47

I GIVE UP!!

C:\SQLiteTest>fpc Test.pas
Free Pascal Compiler version 3.2.2 [2021/05/15] for i386
Copyright (c) 1993-2021 by Florian Klaempfl and others
Target OS: Win32 for i386
Compiling Test.pas
Linking Test.exe
43 lines compiled, 0.3 sec, 458976 bytes code, 18596 bytes data

C:\SQLiteTest>Test.exe
An unhandled exception occurred at $00000000:
EAccessViolation: Access violation
  $00000000
  $0044643B
  $004474C0



Here is my code:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i program
sqlite_test;{$mode objfpc}{$H+}{$ifdef mswindows}{$apptype
console}{$endif}{$apptype console}uses SysUtils, sqldb,
sqlite3conn; var  Conn: TSQLite3Connection;  Query: TSQLQuery;  Txn:
TSQLTransaction;  Id:integer;  TextField:string; begin 
Conn:=TSQLite3Connection.Create(nil);  Txn :=
TSQLTransaction.Create(nil);  Conn.Transaction := Txn; 
Conn.DatabaseName:='Test.db';  Conn.Connected:=True;  
Query:=TSQLQuery.Create(nil);  Query.Database := Conn;  Query.SQL.Text
:= 'SELECT topic_id, data FROM content WHERE topic_id = :id';  
Id:=1;   Query.ParamByName('Id').AsInteger := Id;  Query.Open;  
TextField := Query.FieldByName('data').AsString;  Writeln('The value
of TextField is: ', TextField);   // Change the value of TextField 
Query.Edit;  Query.FieldByName('data').AsString := 'Changed Topic
ONE';   // Commit the change  Query.Post;  Query.ApplyUpdates; 
Txn.Commit;   Query.Free;  Txn.Free;  Conn.Free;end.

----------------------------------------------------------------------
Thaddy · 2023-01-13 09:02

< Sigh>
You obviously want to pre-chew anything for you.
Just wait a short time and I write more than just the example.
That is of course never the intention for small examples.

----------------------------------------------------------------------
PierceNg · 2023-01-13 09:06

Quote from: WJSwanepoel on January 13, 2023, 09:47:10 amI GIVE UP!!

C:\SQLiteTest>fpc Test.pas
Free Pascal Compiler version 3.2.2 [2021/05/15] for i386
Copyright (c) 1993-2021 by Florian Klaempfl and others
Target OS: Win32 for i386
Compiling Test.pas
Linking Test.exe
43 lines compiled, 0.3 sec, 458976 bytes code, 18596 bytes data

C:\SQLiteTest>Test.exe
An unhandled exception occurred at $00000000:
EAccessViolation: Access violation
  $00000000
  $0044643B
  $004474C0

Could be your program cannot find the SQLite DLL. You're compiling for
i386-Win32, so your SQLite DLL should be 32bit. Copy the DLL to the
same directory as test.exe.

----------------------------------------------------------------------
PierceNg · 2023-01-13 09:35

I booted up Windows and tested. With SQLite3.dll in the same
directory, test.exe works as expected. Output looks Unixy because I'm
running in msys2.

Code: Text  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i $ sqlite3
Test.db "select * from content"1|Changed Topic ONE2|topic 23|topic 3

----------------------------------------------------------------------
WJSwanepoel · 2023-01-13 09:46

Thanks, I shall try and battle thru it on my own.

----------------------------------------------------------------------
Thaddy · 2023-01-13 10:50

You can battle, but that will probably not work soon.
As promised:Code: Pascal  [Select][+][-]window.onload = function(){var
x1 = document.getElementById("main_content_section"); if (x1) { var x
= document.getElementsByClassName("geshi");for (var i = 0; i Program
sqlite_test_tdk;{$mode objfpc}{$H+}{$ifdef mswindows}{$apptype
console}{$endif} Uses SysUtils, sqldb, sqlite3conn; Const   db =
'test.db'; Var   Conn: TSQLite3Connection;  Query: TSQLQuery;  Txn:
TSQLTransaction;  Id: integer = 2;  TextField: string;BeginTry  Conn
:= TSQLite3Connection.Create(Nil);  Try    Conn.DatabaseName := db;  
Try      Txn := TSQLTransaction.Create(Nil);      Try       
Conn.Transaction := Txn;        If Not FileExists(db) Then         
Try            Conn.Open;            Conn.ExecuteDirect('CREATE TABLE
"content" ( "topic_id" Integer NOT NULL PRIMARY KEY
AUTOINCREMENT,"data" VARCHAR)');            Conn.ExecuteDirect('INSERT
INTO "content" VALUES (1, "DATA 1")');           
Conn.ExecuteDirect('INSERT INTO "content" VALUES (2, "DATA 2")');    
      Conn.ExecuteDirect('INSERT INTO "content" VALUES (3, "DATA
3")');            Txn.Commit;            Conn.Close;          Except 
         On E: Exception Do              writeln('Could not create
database: ',db);          End;        Try          Query :=
TSQLQuery.Create(Nil);          Try                        
Query.Database := Conn;            Query.SQL.Text := 'SELECT topic_id,
data FROM content WHERE topic_id = :id';           
Query.ParamByName('Id').AsInteger := Id;            Query.Open;      
    TextField := Query.FieldByName('data').AsString;           
Writeln('The value of TextField is: ', TextField);            //
Change the value of TextField            Query.Edit;           
Query.FieldByName('data').AsString := 'Changed Topic
'+DateTimeToStr(Now);            TextField :=
Query.FieldByName('data').AsString;            Writeln('The value of
TextField is now: ', TextField);            // Commit the change     
     Try              Query.Post;              Query.ApplyUpdates;   
         Txn.Commit;            Except              // Usually
EUpdateError              On E: Exception Do               
writeln(E.Message)             End;           Finally           
 Query.Free;           End;         Except           On E: Exception
Do           writeln('Could not Create Query:', E.Message);       
 End;       Finally        Txn.Free;       End;     Except        On
E: Exception Do          writeln('Could not create transaction:
',E.Message);     End;   Finally     Conn.Free;   End;Except  On E:
Exception Do writeln('Could not create connection: ', E.Message);End; 
writeln('Press any key');  readln;End.Done. Maybe only specialize the
exception objects a bit. (EDatabaseError or derived from EDatabase
error, like EUpdateError)

----------------------------------------------------------------------
WJSwanepoel · 2023-01-13 11:18

Thank you for the help. I will try to apply myself and get it working.

----------------------------------------------------------------------
Thaddy · 2023-01-13 11:45

I editted the code somewhat because as an oversight I handled the try
finally's not correctly. Brought the creation of the class instances
outside of their subsequent try finally block. As it should be.

----------------------------------------------------------------------
WJSwanepoel · 2023-01-13 11:58

Thank you, much appreciated.

----------------------------------------------------------------------
Thaddy · 2023-01-13 12:03

Made one more small edit (formatting)

----------------------------------------------------------------------
TRon · 2023-01-14 04:43

Quote from: PierceNg on January 13, 2023, 10:06:29 amCould be your
program cannot find the SQLite DLL. You're compiling for i386-Win32,
so your SQLite DLL should be 32bit. Copy the DLL to the same directory
as test.exe.
When the dll/library is not present then fpc emits a meaningful
exception:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
sqlite3conn.SQLiteLibraryName := 'unavailable_library'; That will
throw an exception when the connected property is set to true:
Code: [Select]An unhandled exception occurred at $00000000004F5232:
EInOutError: Can not load SQLite client library "unavailable_library".
Check your installation.
  $00000000004F5232
  $0000000000460B8E

No idea what happens when you try to load a library with the wrong
bitness (on Windows) though (shouldn't the load fail, and consequently
throw the same exception ?)

----------------------------------------------------------------------
Thaddy · 2023-01-14 07:54

Quote from: PierceNg on January 13, 2023, 10:06:29 amCould be your
program cannot find the SQLite DLL. You're compiling for i386-Win32,
so your SQLite DLL should be 32bit. Copy the DLL to the same directory
as test.exe.
Although that works, it is not the proper way to do things, because
you need a copy of the dll's for every new application. This leads to
disk rot.

The recommended way is like this, read carefully because it can be
confusing:

1. On a 32bit WIN32 OS put a 32 bit Sqlite3.dll in \windows\system32
and you can pick and mix.



2. On a 64bit WIN64 OS, but your code compiled for 32 bit, the 32 bit
dll should go in \windows\sysWOW64. WOW64 represents Windows32 on
Windows 64.

3. On a 64bit WIN64 OS, with your code compiled for 64 bit, a 64bit
dll should go in \windows\system32 and that is not a typo.

Point 3 makes things a bit confusing, but yes, on WIN64 64 bit dll's
go in the \windows\system32 directory and 32 bit dll s go in sysWOW64

If you do that properly you won't have any issues with either 32 bit
or 64 bit applications. If both are installed properly you can pick
and mix between 32 and 64.

(Do this for all commonly used dll's, e.g., not only sqlite, but also
opensll etc. )