rgdawson

Correct. As described, Sqlite did remove the SQLITE_HAS_CODEC compile option after 3.31.1. So, when you statically link with FireDAC.Phys.SQLiteWrapper.FDEStat.pas, that is the version you get. I was able to quickly re-wicker my simple Sqlite3 library to be able to use the FDEStat version on encrypted databases and for the rare times I need to encrypt the data, 3.31.1 is plenty fine, I guess, and a couple thousand dollar cheaper. I don't expect to ever get a paid SEE license due to cost. I'm happy to have options (1) and (3) at this point. And I just like doing Sqlite data access using a very simple, very thin layer to its native interface. FireDAC in its full glory is sorta overkill for my needs. I am pleased with what Delphi has done with regards to Sqlite in Version 12.x.

Thank you, sir. Yes, I discovered those, too. Today's project was a console app, designed to be run from a script on some server and query data out of a massive MS Project File and sent to some other server as Json. I had not yet thought about using the TFDPhysSQLiteDriverLink component since I'm not using FireDAC, per se. Now I have something new to think about. 🙂 I did once create an app where the data needed to be encrypted and I did use FireDAC as the access layer for that. I may experiment with using FDEStat and see if I can access encrypted data without using FireDAC otherwise. Cheers

I use Sqlite in several applications. I came across a simple little interface years ago from a Yury Plashenkov (https://github.com/plashenkov/SQLite3-Delphi-FPC)) and I thought it was simple and brilliant. Awhile later, I created my own based on Yury’s style, but taking advantage of some modern Delphi language features like interfaces and anonymous methods plus other goodies. (Yah, I read Nick Hodges book and needed something to do.) My version is on GitHub at (https://github.com/rgdawson/Rgd.Sqlite3) for anyone interested. The other day, it dawned on me that I could statically link the Sqlite3 binary into the exe quite easily, which was a surprise to me. Just include the unit FireDAC.Phys.SQLiteWrapper.Stat and presto, the Sqlite3 binary is statically linked and no more need to distribute sqlite3.dll with my project. As of Delphi 12.2, the version of Sqlite is 3.46.0. (I never tried this before Delphi 12, so YMMV) I use only the Pro version of Delphi, which does not include the source code to FireDAC, but I was able to include the unit and just let the IDE’s editor hints show me the function prototypes, like so: I had gotten used to compiling my own version of Sqlite, leaving out features I didn’t need to make it smaller/faster. And yes, you lose that ability when you statically link Delphi’s FireDAC version, which is not customizable and has every feature built in it. But any speed differences appear negligible to me, and I’ll trade having a bigger exe for the simplicity of a single executable file. Anyway, I'm sure many of you already knew how to do this and are thinking "big deal" , but I didn't. (I'm not a big FireDAC user.) This sort of made my day today being able to statically link Sqlite3 without using FireDAC for my access layer. I’d thought I’d share.

I see blinking, also, when using RDP.

Thank you, Stefan. (and Dalija, and Remy. My top 3 Delphi heroes.) Anyway, I think your advice above is good advice at the end of the day. Though, other than the unexpected/broken Default(T) behavior, I was at least beginning to think they might be useful in some ways. I assumed that the purpose of CMR's Initialization and Finalization class operators was to create/free things. It is hard for me to imagine any other important benefit. I did another experiment where I replaced my CMR's regular TStringlist object with a Shared<TStringlist> using your Spring4D library, thinking that might avoid a memory leak with Default(T), and thinking the Shared<TStringlist> might free itself, but the Shared<TStringlist> does not get freed in that case either. So, at the end of the day, I'm thinking CMR's are of dubious benefit and just not compatible with the Default(T) function.

As Dalija shows above, the CMR guaranteed to already be INITIALIZED, so FINALIZE would not be called before INITIALIZE. So, it still seems to me that Default(T) should call the CMR's FINALIZE before calling the CMR's INITIALIZE.

No reason, I am experimenting. Afterall, I have done fine without Custom Managed Records forever. I just was trying to understand how Custom Managed Records work and how they might be useful, since Embarcadero saw fit to implement them. I do use string lists alot (usually small ones) and I find it nice to not have to create/free them in try/finally blocks all the time. I have tried Spring4D's smart pointers, which are also very nice (though you still have to create them). This exercise was inspired by https://github.com/toppermitz/StringVar, an older similar thing where the author uses TInterfacedStringlist = class(TStringlist, IInterface) and embeds that into a record type. So, I thought to implement the concept as a Custom Managed Record and see what happens. The full implementation is here, https://github.com/rgdawson/Rgd.Stringlist, for anyone interested and includes a demo that logs what is going on. Having done so, I really like it. The Default(T) behavior was unexpected, but not a big deal, just something to be aware of until Embarcadero fixes it.

Actually, I think that the problem is that Default(T) is not finalizing the variable using the class operator, before the first Initialize. I'm guessing that it is just doing its old default finalize operation as it always has and needs to be CMR aware. That's my guess, at least.

Yes, exactly.

I have been experimenting with Custom Managed Records (CMR) where I have a record type with defined class operators for Initialize, Finalize, and Assign. My record type contains a TStringList object, which gets created in Initialize and freed in Finalize. It all works beautifully, but I notice that it does not work as I would have expected with Default(T). For example, I have (abbreviated obviously) Stringlist = record private {Internal TStringlist...} FData: TStringList; {Getters/Setters...} {...} public class operator Initialize(out Dest: Stringlist); class operator Finalize(var Dest: Stringlist); class operator Assign(var Dest: Stringlist; const[ref] Source: Stringlist); class operator Implicit(const AValue: Stringlist): TPersistent; class operator Implicit(const AValue: Stringlist): TStrings; {"Lifted" TStringlist Methods and Properties...} {...} end; class operator Stringlist.Initialize(out Dest: Stringlist); begin Dest.FData := TStringList.Create; //Log(IntToHex(IntPtr(@Dest)) + ' Initialized'); end; class operator Stringlist.Finalize(var Dest: Stringlist); begin Dest.FData.Free; //Log(IntToHex(IntPtr(@Dest)) + ' Finalized'); end; class operator Stringlist.Assign(var Dest: Stringlist; const[ref] Source: Stringlist); begin Dest.FData.Assign(Source.FData); //Log(IntToHex(IntPtr(@Source)) + ' Assigned to ' + IntToHex(IntPtr(@Dest))); end; {...} function AllCaps(const Strings: Stringlist): StringList; begin Result := Default(TMyRecord); {Causes memory leak due to my own Finalize not getting called and A.FData not being freed before getting re-created} Result.Text := UpperCase(Strings.Text) {...} end; {...} MyStrings := AllCaps(TheirStrings); Default(Stringlist) does not Finalize the variable Result using my own Finalize class operator. Instead Initialize gets called on Result, creating a new TStringlist without freeing the previous one, and thus a memory leak. There are simple work-arounds for this, such as class function Stringlist.Default: Stringlist; var Default: Stringlist; begin Result := Default; end; function AllCaps(const Strings: Stringlist): StringList; begin Result := Stringlist.Default; Result.Text := UpperCase(Strings.Text); {...} end; or, function AllCaps(const Strings: Stringlist): StringList; begin Finalize(Result); Result := Default(Stringlist); Result.Text := UpperCase(Strings.Text); {...} end; Default(T) is a bit of a mysterious function, but it looks to me like its Finalize code ignores the CMR type's class operator Finalize. I'm just wondering about all this and if this is intended behavior for a good reason that I might be missing or a bug. Thoughts, anyone?

Thanks for the comments. After playing around with these approaches, I eventually decided I did not like patterns 2 and 3. Pattern 1 seems it would be easiest to read and understand when seeing it for the first time or coming back to it six months later. Yes, DB.Prepare creates the statement interface, which is reference counted so it will get finalized and disposed of when it goes out of scope. So I ended up going with something like this. Stmt := DB.Prepare( 'SELECT Name' + ' Age,' + ' FROM People'); while Stmt.Step = SQLITE_ROW do begin Name := Stmt.SqlColumn[0].AsText; Age := Stmt.SqlColumn[1].AsInt; {...} end; I also like doing something like with DB.Prepare( 'SELECT Name' + ' Age,' + ' FROM People') do Fetch(procedure begin Name := SqlColumn[0].AsText; Age := SqlColumn[1].AsInt; {...} end); which is exactly what you said NOT to do, so there's that, haha. I generally hate 'with' statements, but, well, I sorta like the way this reads. I should also add that the Prepare function checks the result of sqlite3_prepare_v2() and throws an exception if there is an error with an explanation of the error. I have added a link to the Rgd.SQlite3 unit here https://github.com/rgdawson/Rgd.Sqlite3/blob/main/Rgd.Sqlite3.pas

Nice, good points. After implementing a block of code all different ways and then just staring at it for awhile, I also decided Pattern 2 seems best. Sometimes, you don't really know until you try it with real code. Using the anonymous method pattern for the fetch loop code makes it easier to just plainly see that database fetching is going on and just seems more elegant. As for getting column/field data, pattern 3, using implicit, indeed, made the code harder to read and contextualize. Then, your three little lines of code there are all good ideas, too, which I intend to implement. Thanks.

Nick Hodges excellent book, "Coding in Delphi", gave some interesting ideas about creating Sqlite classes and interfaces, then anonymous methods, then enumerators. I am ever mindful of the idiom, "just because you can doesn't mean you should", but I decided to experiment. Below are some examples and what I am asking is, what looks best to you? I started with Yuri Plashenkov's simple Sqlite classes (https://github.com/plashenkov/SQLite3-Delphi-FPC) I converted the classes into interfaces which gives me Pattern 1 below. Then I added some functions that take advantage of anonymous methods and that gives me Pattern 2. Then I added enumerators and that gives me Pattern 3. {...} var SQL: string; Stmt: ISQlite3Statement; Name: string; Age: integer; begin SQL := 'SELECT Name,' + ' Age'+ ' FROM People'; {Pattern 1: using interfaces...} Stmt := DB.Prepare(SQL); while Stmt.Step = SQLITE_ROW do begin Name := Stmt.ColumnText(0); Age := Stmt.ColumnInt(1); {...} end; {Pattern 2: adding anonomous methods...} DB.FetchAll(SQL, procedure(Stmt: ISqlite3Statement) begin Name := Stmt.Column[0].AsText; Age := Stmt.Column[1].AsInt; {...} end); {Pattern 3: adding enumerator...} for Stmt in DB.Prepare(SQL) do begin Name := Stmt.Column[0]; {this uses class operator Implicit to call .AsText} Age := Stmt.Column[1]; {this uses class operator Implicit to call .AsInt} {...} end; I have also been considering the idea of style with respect to the Sqlite functions .ColumnText() and .ColumnInt() etc. For example instead of .ColumnText(0), I could have .Column[0].AsText, or using "Implicit" , just .Column[0], .Column[1] etc where .AsText, .AsInt, etc is called implicitly. Many of you folks on this forum are the most brilliant I am aware of, and I have learned a ton over the years reading your stuff here, on Stack Overflow, in your books, and in your code. Someday, I'll retire and someone will have to take over this code and I want it to be straight forward looking, easy to read and understand. Which would you say is preferable? R Greg Dawson

Thanks for that tip. I didn't know about that function. I just used MulDiv, which I guess is what that function does. Geewiz, how many times have I implemented something only because I did not know it already existed somewhere within the depths of the VCL/RTL already.

Another thing I noticed was that the Vcl StringGrids were improved to now DPI scale the gridlines thickness, so for example at 200%, your gridlines will be two pixels in width. I happened to have an app with several ownerdraw string grids that do custom stuff with column widths that assumed the gridline width was 1 and all that broke. So that might be something to look out for, cuz it was not obvious to me at first.