Replace substring in a field and update DB

[Thaddy]

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]

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

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:='Test.db';
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 := 'Changed Topic ONE';
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.
45.  

[Thaddy]

< 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]

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

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]

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

1. $ sqlite3 Test.db "select * from content"
2. 1|Changed Topic ONE
3. 2|topic 2
4. 3|topic 3

[WJSwanepoel]

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

[Thaddy]

You can battle, but that will probably not work soon.
As promised:

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

1. Program sqlite_test_tdk;
2. {$mode objfpc}{$H+}{$ifdef mswindows}{$apptype console}{$endif}
3.  
4. Uses SysUtils, sqldb, sqlite3conn;
5.  
6. Const
7.   db = 'test.db';
8.  
9. Var
10.   Conn: TSQLite3Connection;
11.   Query: TSQLQuery;
12.   Txn: TSQLTransaction;
13.   Id: integer = 2;
14.   TextField: string;
15. Begin
16. Try
17.   Conn := TSQLite3Connection.Create(Nil);
18.   Try
19.     Conn.DatabaseName := db;
20.     Try
21.       Txn := TSQLTransaction.Create(Nil);
22.       Try
23.         Conn.Transaction := Txn;
24.         If Not FileExists(db) Then
25.           Try
26.             Conn.Open;
27.             Conn.ExecuteDirect('CREATE TABLE "content" ( "topic_id" Integer NOT NULL PRIMARY KEY AUTOINCREMENT,"data" VARCHAR)');
28.             Conn.ExecuteDirect('INSERT INTO "content" VALUES (1, "DATA 1")');
29.             Conn.ExecuteDirect('INSERT INTO "content" VALUES (2, "DATA 2")');
30.             Conn.ExecuteDirect('INSERT INTO "content" VALUES (3, "DATA 3")');
31.             Txn.Commit;
32.             Conn.Close;
33.           Except
34.             On E: Exception Do
35.               writeln('Could not create database: ',db);
36.           End;
37.         Try
38.           Query := TSQLQuery.Create(Nil);
39.           Try            
40.             Query.Database := Conn;
41.             Query.SQL.Text := 'SELECT topic_id, data FROM content WHERE topic_id = :id';
42.             Query.ParamByName('Id').AsInteger := Id;
43.             Query.Open;
44.             TextField := Query.FieldByName('data').AsString;
45.             Writeln('The value of TextField is: ', TextField);
46.             // Change the value of TextField
47.             Query.Edit;
48.             Query.FieldByName('data').AsString := 'Changed Topic '+DateTimeToStr(Now);
49.             TextField := Query.FieldByName('data').AsString;
50.             Writeln('The value of TextField is now: ', TextField);
51.             // Commit the change
52.             Try
53.               Query.Post;
54.               Query.ApplyUpdates;
55.               Txn.Commit;
56.             Except
57.               // Usually EUpdateError
58.               On E: Exception Do
59.                 writeln(E.Message)
60.              End;
61.            Finally
62.              Query.Free;
63.            End;
64.          Except
65.            On E: Exception Do
66.            writeln('Could not Create Query:', E.Message);
67.          End;
68.        Finally
69.         Txn.Free;
70.        End;
71.      Except
72.         On E: Exception Do
73.           writeln('Could not create transaction: ',E.Message);
74.      End;
75.    Finally
76.      Conn.Free;
77.    End;
78. Except
79.   On E: Exception Do writeln('Could not create connection: ', E.Message);
80. End;
81.   writeln('Press any key');
82.   readln;
83. End.

Done. Maybe only specialize the exception objects a bit. (EDatabaseError or derived from EDatabase error, like EUpdateError)

[WJSwanepoel]

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

[Thaddy]

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]

Thank you, much appreciated.

[Thaddy]

Made one more small edit (formatting)

[TRon]

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.

When the dll/library is not present then fpc emits a meaningful exception:

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

1.   sqlite3conn.SQLiteLibraryName := 'unavailable_library';
2.  

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]

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.

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. )