David Schwartz

We've got a ton of SQL queries embedded in a Delphi app that we've decided to move to the server where they belong. Around 700 or so of them. Does anybody have any ideas about how to most easily extract the SQL from Delphi code that generates them at run-time with a bunch of string catenation before stuffing them into the .SQL.Text property, or even via repetitive SQL.Add(...) methods? Also, what's the best way to extract them from SQL properties in DFMs where they're defined at design-time? The "obvious" approach in my mind is to use some kind of editing, like regular expressions, to remove common syntactic sugar needed to manipulate a bunch of string constants used to build up a big string. Another thought was to use some kind of scripter to actually process the Delphi code in a way that generates the strings dynamically, then grab them from the buffer that way. (ie., use a run-time interpreter to process limited lines of code that produce longer strings, rather than at run-time.) Any ideas?

Thanks. I actually got something working that uses the old TRegExpr component and a few simple re's to extract the Object and SQL.String data from my DFMs. Then I wrote a simple parser routine to take the DFM's representation of the SQL.String and restore it as a "clean" SQL expression. function postincr( var i : integer ) : integer; begin Result := i; i := i+1; end; . . . procedure GetDatasetInfo( aMatchNum : integer ); var p : integer; s0, s1, s2 : string; dataset_nm, dataset_type : string; re2 : TRegExpr; naked_query : string; begin s0 := WholeObject_re.Match[0]; // this is the whole object definition if (Pos('SQL.Strings',s0) < 1) then // If there's no SQL.Strings property, we aren't interested Exit(); s1 := WholeObject_re.Match[1]; // just [name: type] p := Pos( ':', s1 ); dataset_nm := Copy( s1, 1, p-1 ); dataset_type := Trim(Copy( s1, p+1, 99 )); if (s0 <> '') and (s1 <> '') then begin re2 := TRegExpr.Create( 'SQL.Strings = \(.*\)$', 'ims-xg' ); try s2 := ''; if re2.Exec(s0) then s2 := re2.Match[0]; // matches the whole SQL.Strings expression naked_query := ExtractSQL( s2 ); query_sl.Text := naked_query; . . . // do something with the above data . . . finally re2.Free(); end; end; end; //*********************************************************************** // this RE finds object definitions in the DFM that contain 'dataset' in the type name. // subexpression #1 is the object's definition; eg: "qry: TAstaClientDataset" WholeObject_re := TRegExpr.Create( 'object *(\S*: *\S*dataset)\s*$.*?end\s*$', 'ims-xgr' ); if WholeObject_re.Exec(ss.DataString) then begin // NOTE: there's only ONE SQL.Strings property per dataset / WholeObject GetDatasetInfo(postincr(n)); while WholeObject_re.ExecNext() do GetDatasetInfo(postincr(n)); end; Here's the one for extracting SQL.Strings from a string consisting of just an Object: with TRegExpr.Create( 'SQL.Strings = \(.*\)$', 'ims-xg' ) do begin if Exec(dm1.Datasets_tblWholeObject.AsString) then aMemo.Lines.Text := Match[0]; Free(); end; And here's the ExtractSQL parser: // This function extracts raw SQL from a SQL.Strings property function Tdm1.ExtractSQL( aSQL_String : string ) : string; //----------------------------------------- function incr( var n : integer ) : integer; begin n := n+1; Result := n; end; //----------------------------------------- var ln, n : integer; trimln, t2 : string; add_next_line : Boolean; rslt : TStringList; SQL_String : TStringList; begin rslt := TStringList.Create(); SQL_String := TStringList.Create(); try SQL_String.Text := aSQL_String; add_next_line := False; t2 := ''; ln := 0; repeat trimln := Trim(SQL_String[ln]); if (ln = 0) and (CompareText( trimln, 'SQL.Strings = (' ) = 0) then Continue; if (trimln = '''''') then // it's just a blank line begin rslt.Add(''); Continue; end; if (trimln = '') then Continue; if (trimln[1] = '''') then // a single leading quote begin Delete( trimln, 1, 1 ); //trimln := Trim(trimln); end; n := Length(trimln); repeat if (trimln = '') then Break; case trimln[n] of '''': begin // ' Where bill_order in (1,2,3)' // ' and pti.pi_id = rfl.pi_id(+) ' if (trimln[n-1] = ')') then // ....)' begin Delete( trimln, n, 1 ); Break; end; Delete( trimln, n, 1 ); trimln := TrimRight(trimln); n := Length(trimln); end; ')': begin // ' and en_id = :pENID') if (trimln[n-1] = '''') then // ....') begin Delete( trimln, n-1, 2 ); // remove ') Break; end; // ' and pti.pi_id = rfl.pi_id(+) ' with [_'] at end removed if ((trimln[n-2] = '(') and (trimln[n-1] = '+')) then Break; // nothing needs to be done here Delete( trimln, n, 1 ); trimln := TrimRight(trimln); n := Length(trimln); end; '+': begin // this is the most common pattern that's used for line-splitting: // the ' is typically in col 74 and + in col 76 // ...' + if (trimln[n-2] = '''') and (trimln[n-1] = ' ') then begin Delete( trimln, n-2, 3 ); add_next_line := True; end // ' Where udf.active = '#39'T'#39 // ' AND udf.ud_type = '#39'E'#39) else if (Copy(trimln, n-4, 3) = '#39') then begin Delete( trimln, n-1, 2 ); // we don't want to delete the #39 add_next_line := True; end end; else break; end; until (1 = 2); if add_next_line then begin t2 := t2 + trimln; add_next_line := False; end else begin if (t2 <> '') then trimln := t2+trimln; rslt.Add( trimln ); t2 := ''; end; until (incr(ln) >= SQL_String.Count); finally Result := rslt.Text; rslt.Free(); SQL_String.Free(); end; end; If the program ever hangs while parsing this stuff, it's probably because I missed something in the handling of a trailing single-quote or '+' that deals with undoing the word-wrapping. (This code could probably be simplified. There may even be better code hiding somewhere in the VCL source.)

D10.2.3 ... GetIt package manager problems ... when I try to open GetIt in the IDE, I get an error: "invalid type cast". It's in GetIt250.bpl. Is there any way to access the files and download them directly? I'm looking for Raize Components. (Konopka)

As I said, this code has been working "as designed" for over a decade, long before I came on the scene, and I have not touched it. The problem existed before I arrived. How can I be doing something wrong? I don't know how many customers are complaining or are unhappy with it. I've only been told about two who have voiced their complaints. Personally, if a form took over one minute to load up, I'd ditch the software. But these folks seem to be much more tolerant than I am. The architectural defect I'm talking about has been pointed at several times above: we have datasets that have to run SQL queries phrased as "select * from ..." because that's how things were written in D7 10 years ago, and there's no paging going on anywhere. These queries ran very quickly when the file server was in the same building and they only had a year or two of historical data present. But today they literally take several minutes (some up to 15!) to run on the remote server, processing over a decade of data (they're really hairy queries that apparently aren't time-boxed) and many return over 100,000 rows of data to the client before the user is allowed to view the first 10 and do anything. As I said, this is working 100% as-designed! Nobody considers it a "bug" in any traditional sense. But clearly, 10-15 minute response times -- while not a "bug" -- are extremely "unfriendly" from the users' standpoint. Explaining this in terms of Delphi's DACs or the server are fruitless -- it's the lack of paging in the queries that's causing the problems. This is an architectural defect, not a "bug" in anything, since it's all working perfectly correctly and "as-designed".

My company has some clients who are complaining that a few of our screens are taking over 10 minutes to load up. This code has been around for over 10 years, far longer than I've been working there, so I know it's not me. (It's not code I've ever touched.) In fact, it's working perfectly "as designed" and there are no bugs present. It is actually an "architectural defect". Regardless, can I have them give you a call so you can tell them that this is a non-existent problem?

I'm constantly seeing little red lines show up in perfectly good code in 10.2.3, even right after a clean compile. It's pretty damn worthless a lot of the time.

I don't think anybody is getting my point. I've seen this problem arise in numerous projects over the past decade that use virtually every DAC and DB server there is. THEY ARE NOT THE PROBLEM. What I have NOT seen is any sort of coherent discussion or approach that anybody took, nor have I seen anything cited anywhere that helps Management get their arms around the REAL problem. As I said at the very beginning, IT'S A GENERAL QUESTION! People keep trying to make it specific to my current situation or this or that component lib. It's NOT! Well, if nobody can address it BROADLY, and instead they can only speak to how to deal with this DAC or that DB, then there shouldn't be much surprize over the fact that Management isn't getting any useful input to justify anything else. Management does not seem to understand the impacts that fundamental architectural differences have going from a traditional Client/Server to a server-based "cloud" solution. And if we as "technical experts" cannot shed light on this issue without bringing up obscure details rooted in what specific DAC or DB is involved, then there's a big disconnect happening and nothing useful being communicated to fill the gap. All I can point to is a bunch of projects I've seen that were supposed to take "only 18 months" and not one of them was even close to functional after 24 months. Meanwhile, migrating the Delphi app would have taking way less than 18 months. But again, the perception on the part of Management has usually been that "the problem is Delphi" where the REAL TRUTH is "the problem is the architectural change that made the current app unfit for the job". Delphi is irrelevant. If all you can do is talk about the DACs -- an inherently Delphi-specific technology -- how in the heck can you convince anybody that the problem is NOT Delphi? Any app written in Visual Studio or any other platform that adheres to a classic Client/Server architecture is going to have the exact same architectural problems. But nobody is talking about THAT! Is anybody starting to get my point now?

Yes, it was. I posted my reply, then realized I wanted to quote the message it was replying to. When you hit the Quote button, it opens a new reply. It's not clear how to copy that into an existing reply. So you end up with a duplicate post and no way to delete one of them. I just don't get the point of a policy that simply causes a lot of needless clutter and additional work for people who are already over-worked (Ie., the moderators/admins)

My question refers to a use-case that's similar to thousands of others like it: suppose you have an app built in, say, D7 that was originally designed as a client/server app, using standard Delphi components available at the time, and it's now exhibiting poor performance after replacing the local file server with a DB server (MS SQL Server, Oracle, MySQL, whatever) because the 243 SQL queries on the 178 forms are all set to do "select * from xxx" in order to populate the controls on the form. So loading up some forms can literally take 10-15 minutes. It's a very general question: how does one usually deal with this? (In my experience, Management usually says, "We're going to rebuild the app in C#/.NET because Delphi clearly can't handle this workload." But I'm asking for a more TECHNICAL explanation that doesn't just throw the baby out with the bathwater and start over from scratch.)

It's a more general question than that. I mean, what do you do with apps that migrated from client/server to DB servers and now they have performance issues because of this? It's not like we can go through and replace every DB control that resembles a list with a VirtualTreeView. Maybe they need to be replaced with something, but surely not all with the same component.

yes, I've got one of these with 7500 elements in it, It takes a couple of minutes to build, and once it's loaded-up, it never changes. So I want to save it to disk or a BLOB in a DB and reload it later instead of having to rebuild it each time. All I need is .SaveToStream and .LoadFromStream. It doesn't need to be human readable; faster is better.

Are the GetIt packages on the ISO distro?

"Free" doesn't equate to "open source". For example, I bet none of the Konopka / Raize stuff is going to be found on any github sites. That doesn't mean it would unreasonable to have a DL page that requires you to long to your back-end (like "Registered User Downloads")

Advocating cutting off one's nose to spite their face seems to be a popular past-time among some Delphi users. If it's not one thing it's another. I've been using 10.2.x for over a year now, and I'm quite happy with it. But it's annoying to see something broken in 10.2.3 that was working fine in earlier versions. GetIt has never occurred to me as well thought out or nicely implemented. It's like a prototype that someone showed to upper management who said, "Great! Ship it!" It has a bug in 10.2.3 that renders it useless. There's no workaround, and it's super-low priority, so who knows when it will ever get fixed (in 10.2.3, anyway). All of these files are free, so this should be regarded as "a NICER way of installing" rather than"the ONLY way". Which means I have to load up a VM from last year with an older version of Delphi on it just to get something off of GetIt for use in 10.2.3.

It doesn't work now. It generates a run-time error. Did you read my original post? I'm getting "invalid type cast". It's in GetIt250.bpl, and apparently others are as well. And there does not seem to be any workaround other than going back to an earlier version.

That seems to only suggest how to install things that have already been downloaded. GetIt launches, but there's nothing displayed. No way to DL anything. Isn't there some way to actually grab the installation files and install them manually?

D10.2.3 does this by default, as Dennis07 says. Rather a PITA for my present needs.

We use a very old version of TFS. I hate it. We're switching over to git, but one of our devs insists on using git like TFS, and he refuses to do any sort of branching. Perhaps the newer versions of TFS support branching. But the only way we have of interacting with TFS is through VS. For git, there are tons of different interfaces, even VS. Delphi's IDE interface with git isn't very flexible.

Since you're just learning, you should try out different options. What will work for a prototype is not likely to scale-up very well if you don't know what you're doing. You need to walk before you run, and while I applaud your questions, you need to just play around and see how everything works. You've said you're using a REST service, then you ask about connecting to the DB with Delphi. The client side won't talk to the DB, the REST endpoint logic will do that. And if you're using an integrated ORM, there's really very little to worry about regardless of what DB you choose -- it becomes almost invisible if it's supported by the ORM.

The built-in FastMM isn't very useful as far as gathering diagnostic info goes. You pretty much need the enhanced version with the debug DLL.

I've never found the status reporting by FastMM to be very useful. It logs a ton of stuff that's all nearly identical and hard to distinguish what varies. I built a tool that does sort of a "cross-tab" on the data (like in Excel) and lets you view the data in a more useful way -- it lists things by object rather than by individual references to unfreed memory blocks. The thing is, most of the references that show up are for down-stream errors that are not related to the root cause. For example, if you fail to free a TStringList that's got a bunch of objects attached, you'll get a bazillion errors on unfreed strings and other crap contained in the stringlist and classes attached to the Objects array, and only one or two hard-to-distinguish references to the stringlist itself. In other situations, it will NEVER show the list responsible for the leaks! However, it does kindly shows you exactly where all of these things were all allocated, but that's often 100% irrelevant -- so you end up on a meaningless wild goose chase. The truth is, 99% of the stuff that FastMM reports is simply NOISE. I just spent 2 hrs the other night trying to track down a group of orphaned objects that I was absolutely certain were being freed. I looked at all of this code, wrote a bunch of tests, stepped through the Free/Destroy code and checked everything I could think of. Finally, I looked at the number of objects being allocated, and then at the number being freed. All of the structures I was looking at that I _thought_ were responsible for the leak showed they were actually freeing up exactly the same number as were being allocated. So I started wondering, "Where else am I allocating things that aren't in these two lists?" Well, it turns out I had a loop that created an object at the top and added it to a list. Then it does some checking, and if it doesn't find anything interesting, it removes the object from the list. At the end, that list's contents were being added to another list where I thought the leaks were coming from. The difference was, the first list was not set to own its objects, while the second list was. This was by design. What I overlooked was the Remove operation in the first list did NOT free the attached objects! Nor did my code. So FastMM generated SEVERAL HUNDRED references to orphaned objects that it found due to 65 objects I removed from a list but forgot to free. And because it was pointing to the orphaned objects, I had no hints as to which list they were from. In actuality, the Remove operation took them OFF of the list, so when FastMM found them, they were just orphaned objects outside of ANY container, located miles away from the last place they were referenced! The only way I was able to track this down was comparing the #allocated objects vs #freed objects on each list, and that led me to one list that allocated more objects than were freed, even though they had been removed from the list. I'm mentioning this because this is probably a very good heuristic to measure right up front that might help track down the source of many leaks way faster than anything else I can think of.

I don't need an executable. It's just that posting the sources here directly take up most of the page and distract from an otherwise useful discussion.

Thanks, I do appreciate this, although a zip file attachment would be a lot easier to work with 😉 That's a LOT of text to scroll through here. I noticed that the DWS project has migrated to BitBucket. Is there anything to be aware of when using it?

@Dany Marmur Thanks! We have DevEx ... what's RTC?

They're scattered across a few hundred source files. It's probably worth taking a look. Do you want to post it here and I can grab it, then you can leave it or delete it later?