Gary Mugford

Vincent, I could see the issue being paging IF THE PROCESS FIELD in the child table wasn't RENUMBERED!?!?! And, while BDE did something to live with the data, NexusDB, an honest to goodness Database Server wanted NOTHING to do with displaying that relationship, crashing out with an AV as soon as the relationship was hit. I've already decided to change 30+ years of relationship and create a GROUP designation and connect it to a database with the sub-parts, while removing it from the child table. The particular part is handled as a difference kind of business model, so trying to push those sub-parts into the old model was and is a lesson in frustration. By creating a new workflow model, I can accommodate the customer that has requested the client work to a certain way that LOOKS like the normal model, but is not. Never great when you have to write alternative workflow streams, but modern businesses adapt. And so must I. Thanks for your thoughtful question. GM

Although the BDE display chopping off 200 child records AND renumbering a field is bad, turns out I forgot to include some OTHER critical info. I actually export the Master and Child table data to NexusDB, recreate the index based on the PartNumber and then run an analysis report. It hits the problematic part and crashes with a Memory Access Violation error. Just touching it bombs the program 11 minutes into the run and after the transfer took 37 minutes. I TRIED to test for the PartNumber and if it was the problem part, continued in the interior loop. Not a solution. Neither was testing for the PartNumber BEFORE the problem part and then doing a double NEXT;. That touches the part, so I should have expected the issue. The CURRENT WORKING SOLUTION, is that I transfer the data, including the problem part and it's monstrous child data. I get to the part BEFORE that part in the internal loop, but then do a findkey for the PartNumber AFTER the partnumber that causes the problem. It's ... working ... but it's TERRIBLE programming and I've been told to expect another part with a massive child presence this month. I DO turn 65 in July and retirement appeals more and more every day. I will pursue this as a NexusDB issue. There is no question that something is malformed in the child data in BDE, but the data is not corrupted. And when it gets moved to NexusDB, the tolerance BDE has for the situation is NOT shared in NexusDB.

While I haven't figured out whether this is an issue ONLY for Woll to Woll database components or for any of my BDE equivalents from other vendors/open source yet, I do have one of those chill to the bone issues I feel very uncomfortable about. I have a main table connected to a child table through the one-to-one connection of a PartNumber. Using wwDBGrid connected to the corresponding tables, I show the child table as having 62 records with a field called Process (AND YES, I know that's a horrible name but this is legacy 1988 database design) which counts from 1 to 62 as it is shown. The field is an integer field and the numbers are IN the database, not calculated. The issue that I have JUST discovered is that there are 262 records in the child database and the 62 that are showing in the application are the LAST 62 of the child table records. That noted, I have two PartNumbers with 80 records in the child database, and all 80 show in each case. So the PROBLEM PartNumber is singular at this point, although I understand my snapshot of the data is far enough out of date that another PartNumber with a HUGE (300+) number of child record entries now exists. I do a LOT of analysing the data for various needs where I loop through the main table and then loop through the child table. If the inner loop is only going 62 records at runtime I've got a problem. The really odd part is the 'renumbering' of the Process field in the wwDBgrid to show numbers 1-62 instead of 201-262. So, I have TWO issues, the reduction of showing only 62 records AND the renumbering. ONE more oddity, in a totally different part with only 15 child records, somebody managed to enter in the data with a SPACE before the name of the PartNumber in the PartNumber field, then later re-entered the child records with the proper PartNumber in the key field. That still leaves those space-PartNumber records atop the child database when you look at it directly via Database Desktop. The data is NOT corrupted (proven multiple times through Rebuilder), it's just that somebody managed to enter in via a method I haven't discovered, all those Space-PartNumbers. And the extra 200 child parts for my main problem. This is BDE, Paradox, Delphi 7, Woll to Woll InfoPower components. I'll eventually ferret out how the data got INTO the child table. I've left a back door somewhere. But the display and renumbering issue is beyond frightening for me. ANY IDEAS???

Dany, Towards the end of MMX development before it went open source, I was an active 'suggester' of features. I might have been the first to suggest a tool breaking apart the With statement. I do use MMX for precisely the task you are talking about. Too bad active development in MMX no longer includes Delphi 7, but I'm such a small niche I can hardly expect to be catered to. I've read the preferences here regarding SQL code keeping and I accept that my style is ... not that of the community. But the efforts of all of you are appreciated. I'll probably REMAIN a stick in the mud. Given my templates and leading /// notation atop query construction and execution, I find looking for SQL code in my .PAS files to be fairly easy. It's less fidgety than searching from DFMs. At least to me, that's the case. I'm a LITTLE intrigued by the DIMOMA SQL resource creator. It has the charm of having a default test SQL version that creates code usable by my report generator and then having a V2 that has query variables in it to use in Run Time work. I'm going to at least look at it. But the single DBMS is where I might run afoul of the licence since I want to convert Paradox to NexusDB and will need both in action for some time. Won't know whether what I do is actually within the licence parameters until I give it a test. Thanks all. GM

Fr0st.Brutal, I admit, most of the world does exactly as you suggest, although there might be a few who use the BeginUpdate and EndUpdate lines. For me, line-by-line SQL is more readable than the format you suggest. When I have errors in my SQL, and I DO have errors, it's better for the line number identification when I try the code directly into the SQL.lines of the BDE SQL query object. Since I am still a learner here in my dotage, directing me to the direct line of the error is of measurable value. Thank you for your suggestion, though. GM

Dany, Last century, well back into last century, I devised an audit trail feature for my applications. All changes in the data were noted and documented in such a way that WHAT the changes were, were easily accessible and thus it mitigated what I called the "NotMe" problem (this plays off a regular feature in Bil Keane comic strips). The NotMe problem was the almost herd-like autonomic response to problems. The History Notes simply eliminated the issue since it was clear who, when and what. This was back in the early 80's and was a feature very valued by my customers. It didn't allow for rollback, but it did leave a change trail to be manually reversed. And it eliminated SOOOOO much angst in the office(s) over what happened. And that SINGLE feature probably kept me employed for as long as I have been, despite no academic background (well, ALMOST no school training) in computer programming and a stick in the mud adherence to not change with the times and keep current with language and database development. However, as I pass officially into senior citizen-hood, I've changed my mind and decided to try and be more au courant. And with people such as yourself, who hold out helping hands, I MIGHT just stretch out my usefulness and put off the switch over to writing books JUST a little longer. Thanks again.

Dany, The SQL in BDE for Paradox is a sub-set of, IIRC, SQL-97. The important limitation is no sub-clauses. In fact, there's even a missing Parameters setting that I see in most Parameterization examples. It simply throws an error and leaving it out doesn't seem to affect things. (Params.ParamCheck := true; specifically). The template I'm using as I add the Params code to my code looks like this: //Params.ParamCheck := true; // DOES NOT WORK IN D7!! Might in XE7?? Params.ParamByName('eDF').asDataType := ftDateTime; Params.ParamByName('eDF').asDate := eDateFrom.asDate; // value can also be used Params.ParamByName('eDT').asDataType := ftDateTime; Params.ParamByName('eDT').asDate := eDateTo.asDate; // value can also be used I have a composite component that is built of a panel, a menu button and two date components from the indispensable ESB library. I call it DateRanger and the menu button in the middle provides quick resetting of the two dates to Last, Current and Next; Week, Month, Year and Quarter, as well as some occasionally asked for ranges like YearToDate, Last365Days and AllDates. Since I use this panel in a great many forms, I can afford a template to include, as examples, the use of the data from eDateFrom and eDateTo. There is nothing to do but change the the query variables to :qvZZ as theorized by François, the DataType ftString and then the value from the proposed TEdit. Then delete the next two lines. At SOME point in time, I will end up working with NexusDB seriously. At that point, a fulll panoply of SQL language features will be made available. THEN, some of your code becomes available for me to experiment with. IN REVIEW: Changing my code to use Parameters in the On The Fly creation is a good idea. Nobody has raised the specter of problems with query variable reuse, although I will be shying away from changing DataTypes whilst doing that. And I can continue to use On The Fly style. The likelihood that anybody would want to disassemble my code as some start to cracking it for nefarious purposes is slim, maybe exceedingly slim, and hard to reach since the server it sits on is not accessible via the Internet. Strictly sneaker net. At least for most of it. Thanks all for the help. GM

And as I hope I clarified, there was no string constant involved. Your warning of using a string value from a TEdit IS IN FACT a good thing to know, since it legitimizes the work in switching to Parameters, since in OTHER cases, I have done EXACTLY what you are warning me about. So, thanks for pointing out the risks my habits have exposed me to. GM

First of all, thanks all for clearing up some of my misunderstandings. Let me clarify my code better than I did in just plopping it into the code brackets. ZZ is NOT a variable, but a checkbox object CALLED xZZ. And because it might be true or false, the SQL code changes somewhat considerably for such a short piece of code. That is in BDE and thus doesn't get to use things like cast. I assume NexusDB won't have the issue once I transform the megalines of code, using a closer to current IDE like XE7 ... which is what, eight generations old at this point?? In fact, this procedure dates back to 1998. FPiette: "If - as an example - the constant "ZZ" must be changed by something coming from the user interface, then use a parameter. You'll have to make the same condition where you'll give a value to the parameter to avoid giving a parameter if the parameter doesn't exists because it is not part of the query because of the condition evaluated to FALSE. Using the same condition around parameter value assignment will solve your problem." Because of the ZZ confusion, some of what you say is based on a misapprehension my writing caused. I THINK you were trying to answer my first issue about reusing parameter NAMES. Especially if different dataTypes. As such, I'm NOT sure what you are saying and what I'm asking are the same problem. If you could clarify I would appreciate it. I'm VERY much trying to use parameters, but it takes time to fix LOTS and LOTS of OLD code with on-the-fly generation that has become my habit. Thanks. Dany, the contained code won't work in BDE. I have been programming simple small internal server apps that began as peer-to-peer apps using Lantastic which began as standalone apps using just Paradox, not even Delphi. Been going almost strong for close to four decades now. I'll probably be retired when my successor takes a look at my code, tosses it almost immediately and starts programming a 21st century interface for the IoT that I have nothing to do with. Thanks for clearing up my confusion about using parameters as a competing concept vs On the Fly SQL Code creation. I really thought the latter was actually code injection. Old dog, new tricks. Stano, always appreciate anybody spending their time on my behalf. Thanks.

I have come to understand that SQL code injection is a bad, bad, BAD thing for we programmers to do. BUT I've come up with two questions regarding switching to parameterization in my code. I'm using Delphi 7 and BDE as my bog standard and have XE7 so if there are differences in your answer(s) between the two, I'd appreciate the extra paragraph. My first question is the regular use of the same 'variable' name in the SQL lines in the query object. IF I include: DateExpected between :eDF and :eDT Do I run any risks using EXACTLY the same variables, albeit maintaining dataType, possibly applied to a different field in a different query, so that I can standardize on the lines of codes dealing with the Parameters that I use in the code before issuing the .Active := true or ExecSQL lines? I am TRYING to remain consistent that eDF is a DateTime variable, but what if I do something stupid like use :A for the variable and make it a string variable in one query and an integer variable in a second query somewhere in proximity in the code to the first?? Next, I have a question where I see no way AROUND using code injection. Say I have a filter I want to apply to the query in the query construction stage. Is that not a case where I NEED injection?? Simple example case in point: procedure TForm1.Btn1Click(Sender: TObject); begin with s do begin active := false; Sql.Clear; sql.BeginUpdate; sql.add('SELECT distinct'); sql.add(' s.PartNum, s.RAF'); sql.add('FROM'); if xZZ.checked then sql.add(' "SHIPPING.DB" s, "CATALOG.DB" c') else sql.add(' "SHIPPING.DB" s'); sql.add('WHERE'); sql.add(' (s.DateDidShip > "' + dateToStr(date-366) +'")'); /// CHANGE to a parameter if xZZ.checked then begin SQL.add(' and (s.PartNum=c.PartNum) '); SQL.add(' and (c.Revision <> "ZZ")'); end; sql.EndUpdate; active := true; end; // end sql query for s pip.Print; ppFooterBand1.visible := false; exporttoExcel(pip,'c:\data\Active.XLS', false, true, false); ShowMessage('Done! Saved to: c:\data\Active.XLS'); close; end; I see no way to turn this into a set of strings I can call from the original query object. I have a condition (a checkbox on the form) that is used twice, which changes the FROM clause and then the WHERE clause. I CAN and will change the DateDidShip comparison to a parameter. But the other two IF sections?? And remember, this is D7 and BDE. I know this probably is handled easily in newer versions and different databases. But I'm dancing with the same old partner as I have forever. This example might be more easily handled with two SQL.text strings using the dating parameter. But what if there are a half-dozen or even more, switches/filters that are applied to the SQL object's SQL text?? There has to be a complexity line SOMEWHERE, where constructing as above does actually make sense. Any and all guidance appreciated. Updating software is not a currently realistic option. Thanks, GM

I could use that. Orange and Brick Red are playmates for green out in the wild.

Honestly, I've NEVER heard of that particular impairment for THAT colour. We have had a fellow with Red-Green Blindness. Good guy, so I created a toolbar that would be toggled in as a replacement for the normal toolbar and was strictly words. Had to be two layers, but he didn't mind losing the grid rows. He could see what he was doing and that was a good thing. I'm not sure I can work in Yellow. But if he's going to stay, not much of a choice.

As I mentioned, not everybody has my taste in colour. My house is decorated in green and brown. This is a logical continuation. And yes, It DOES work better with a dark-themed IDE outside of the editor. So I completely understand Lars. As for Mike and Stano and any others, maybe try a darker green. Really, it IS easier on your eyes. Limit the variation between the various sections ... the light green ones are areas I don't get too all that often being a basic stick in the mud procedural programmer. So, there is less striations in real time use for me. Again, depending on your complexity level necessitating more of the various coding sections, well complexity has a price. There MIGHT be a compromise hue of Green between mine and black, which is what you are going to default to with Dark Theme. I have NOT had to involve myself with Styles but it is rearing it's potentially pretty little head now that I've experienced a fellow who decided he wanted a YELLOW BACKGROUND as the Win 10 default. Not gold, not amber, not honey, not parchment, YELLOW. And he's asked if he could personalize his interface. Ahhhhhh, no, not right now. But maybe in a month I'll revisit it ... hoping a month is 14 days more than his career at that company. But if he sticks, then I'll be looking strongly at Almediadev. YELLOW!

I use the Delphi IDE Theme Editor to apply the base colour scheme, which was green-apache. Then I went about making the system greener as far as backgrounds, choosing in most cases to make the background a shade of green enough different that your eye could spot the difference while still maintaining the whole GREEN is good for the eyes sort of thing. I also made the default font 14pt because, as a senior citizen, BIGGER is better. As mentioned, Consolas is the choice of fonts. I like the ones and the slashed zeroes. Once you are finished playing with the editor, save your theme to a name of your choosing and then apply your master work. As mentioned, I like the background, but there are days where I go to a second theme that is darker green in the background with the slightly lighter background of this theme being swapped for it. Look at the enabled breakpoint line to get what I'm saying. Again, I understand that this is not everybody's cup of tea. Some will have a preference for bold and non-bold. Yes, some will actually want italics in the comments just for familiarity's sake. But RRuz's work providing a quick library of starting points, allowing you to build up a set of themes for specific work ... I can see having a theme for debugging. I forgot that I DID have to do some cleaning up IN Delphi. The comment block was that pinkish red ON A BLACK BACKGROUND. I THINK that was the only post-IDE Theme Editor change. The marked block was money green on navy blue. Yeah, those are the differences. Sorry about that. GM

I've worked on black backgrounds it seems like forever, until I remembered reading a study about how dark grass green is psychologically best for human eyes. We like grass. I remember the underside of our ball caps were always dark green to help cut down on glare. So, I gave a dark green background a try, turning this and that different colours to make them pop out. I do so little work with assembler blocks and IF defined blocks that I was able to repurpose Pinkish Red for comments, losing the italics in the process. Non-Bold white for the majority of the code, bold white for keywords, etc. I found that I preferred it to the black background. The one thing I don't think I could go for at all, is a lightish background that isn't pale, pale grey or pale, pale parchment yellow. White just feels like a flashlight to the eyeballs at this stage in my life. Others are certainly allowed to differ. But give that Grass Green background a try and see if your eyestrain isn't soothed just a tad bit more than normal. And for fonts, Consolas does it for me.