Mark Williams

@Yaron It's taken me a while to revisit this and as usual it has not disappointed: days thrown into the abyss trying to change the structure of my ISAPI DLL! I have implemented points 1 to 4 mentioned by Yaron. Point 5 is causing me a problem. I am having a problem with the closing down of the application pool. Yes I get a long delay and my closing section does not fire. My dpr now looks like this: library MWCWebServer; uses Winapi.ActiveX, System.Win.ComObj, System.SysUtils, Web.WebBroker, Web.Win.ISAPIApp, Web.Win.ISAPIThreadPool, WinAPI.Isapi2, WinAPI.Windows, System.SyncObjs, system.classes, WebModule in 'WebModule.pas' {WebModule1: TWebModule}; {$R *.res} function GetExtensionVersion(var Ver: THSE_VERSION_INFO): BOOL; stdcall; begin Result := Web.Win.ISAPIApp.GetExtensionVersion(Ver); CriticalSection := TCriticalSection.Create; StartServer; end; {I have removed the call to TerminateVersion as it wasn't firing function TerminateVersion(var Ver: THSE_VERSION_INFO): BOOL; stdcall; begin Result := Web.Win.ISAPIApp.GetExtensionVersion(Ver); CloseServer; CriticalSection.Free; end;} {I have added this procedure as shown in the linked post provied by Yaron} procedure DoTerminate; begin CloseServer; CriticalSection.Free; end; exports GetExtensionVersion, HttpExtensionProc, TerminateExtension; begin CoInitFlags := COINIT_MULTITHREADED; Application.Initialize; Application.WebModuleClass := WebModuleClass; Application.MaxConnections:=200; //MaxConnection=32 by default; IsMultiThread:=true; TISAPIApplication(Application).OnTerminate:=DoTerminate; {Application.CacheConnections:=true;} //NB not necessary as cached by default Application.Run; end. My call to CloseServer now looks as follows: Procedure CloseServer; begin CriticalSection.Enter; try if assigned(MimeTable) then try MimeTable.Free; except end; FDManager.Close; try FDManager.Free; except end; try AddToLog('Connection manager deactivated', leMajorEvents); except end; try AddToLog('Server Closed', leMajorEvents); except end; if assigned(FThreadFileLog) then try FThreadFileLog.Free; except end; finally CriticalSection.Leave; end; end; CloseServer gets called on shut down as expected. However, it hangs at "try FDManager.Free; except end;" It appears to execute the line of code above (ie to close FDMAnager), but it does not appear to execute the call to free FDManager not does it it get caught by the try except nor by the try finally end. It just hangs at the call to free. And this is when the application pool stalls in its shutdown process. Does anyone have any ideas why?

On intial setup I set up queries with SQL.Text and prepare them ready for use and reuse. Some of these queries use params. On first usage the queries work as expected. When reused they simply are not fired at all. As an example: with DAC.FDQueryDocCats do begin ParamByName('CASEID').AsInteger:=FProject.CurrCaseID; execute; end; Works as expected on first use. When it is run a second time using the debugger I can see that the correct CASEID value is being passed in and that the query appears to execute, however, it doesn't. The query dataset doesn't update. I have enabled tracing and on checking the trace it is clear that the query does not run at all. I have tried "EmptyDatSet", "ClearDetails", but same effect. If however, I do this: with DAC.FDQueryDocCats do begin SQL.Text:=SQL.Text; ParamByName('CASEID').AsInteger:=FProject.CurrCaseID; execute; end; all works well, however, I lose any advantage in having prepared my query in the first instance (although I have no idea just how great that advantage is). It would seem setting the SQL text clears data or resets flags that I am missing. I have professional delphi not enterprise so can't see the FireDac code to see what it is doing so I can copy.

Sorry. It should have said open not execute. I call the appropriate command in another script which handles various errors and post details to a database. Didn't want to confuse by including all that code, but managed to confuse by not doing so! I was sure I had already tried Close and caused an AV. However, it works! Thanks.

I'm trying to work out the best way to refresh data loaded from a local file with data from the server. Using FireDAC with PostgreSQL. However, solution needs to be a general one as it will eventually need to work for SQL, Oracle etc. I have a table (DOCS_TABLE) which can vary greatly in size. DOCS_TABLE has a timestamp field (LAST_UPDATED). As the name suggests this records the date on which data in the record last changed. When user's open an app if they haven't queried DOCS_TABLE previously it is loaded via the server using a fairly complicated WHERE statement(COMPLICATED_WHERE_STATEMENT) which involves a number of joins to establish which records from the table the user is permitted to access. When the user closes the app the data from DOCS_TABLE is stored locally along with a timestamp to record the date and time the data was last refreshed (STORED_TMESTAMP). Next time the app opens it loads the data from the locally stored file. It then needs to ensure the user is working with up-to-date data. At the moment I am running a refresh query SELECT [fields] FROM DOCS_TABLE WHERELAST_UPDATED >[STORED_TIMESTAMP] AND [COMPLICATED_WHERE_STATEMENT]. I use the resulting data from the refresh query to update the in memory dataset holding DOCS_TABLE. This works, although it doesn't deal with records that were available at time of last saving locally and have now been deleted or access denied. As such,within the app, I run a check to make sure the user still has access to any record before trying to do anything with it, but it's not a terribly elegant solution. It would be better if such items were removed soon after loading the locally saved data. I have some thoughts on how to deal with this, which are below. However, I am concerned I may be overcomplicating things and that there may be much simpler solutions to this problem. Load the data from the local file. Run a thread for the following: Run a query (ID_QUERY) to ascertain which rows are now available to the user: SELECT id FROM DOCS_TABLE WHERE [COMPLICATED_WHERE_STATEMENT] Check the locally saved data against the result of this query to see what rows are no longer available to the user and remove them. Build a list of ids from the locally saved data (EXISTING_ID_ARRAY). Check the locally saved data against the results from ID_QUERY to see whether there are any new records to be added and build a list of the ids (NEW_ID_ARRAY). Run the refresh query using the arrays: SELECT [fields] FROMDOCS_TABLE WHERE (id in ([NEW_ID_ARRAY])) OR (id in [EXISTING_ID_ARRAY] ANDLAST_UPDATED >[STORED_TIMESTAMP]). Subject to my whole theory being cock-eyed I am pretty sure NEW_ID_ARRAY is the way to go. The part that concerns me is EXISTING_ID_ARRAY? Whilst it will cut out the use of the COMPLICATED_WHERE_STATEMENT and enable the query to focus explicitly on a group of records clearly identified, I would think the size of the array, could become a problem. Is there a law of diminishing returns with an IN clause? For example, if there were 1M records in the table and 20 items in the array, I suppose it must be the case using EXISTING_ID_ARRAY will be quicker than using COMPLICATED_WHERE_STATEMENT. But what if the array contained 800K of ids? I guess it has to be significantly less efficient to use EXISTING_ID_ARRAY and more efficient to use COMPLICATED_WHERE_STATEMENT. I appreciate without providing full details of the structure of DOCS_TABLE and the various joined tables, the data being retrieved from it and the full nature of the COMPLICATED_WHERE_STATEMENT, I may be asking for a comparison between apples and pears. What I am really interested in is whether my logic set out above is sound or idiotic and any suggestions on how best to achieve what I am trying to achieve.

I'm not convinced I have such a major issue here, but only time will tell and I'll have to make a judgement call at that time. Give it a REST! Joking aside, I get the message. REST is the way to go. Although by the time I get round to looking at it, it will probably be out to pasture with the brontosauri.

I think there may be some confusion. DOCS_TABLE does not contain the actual documents, rather it contains only data relating to the documents (such as data, author, file type, when uploaded etc. I don't download all the documents in one hit, just the data relating to them. The documents are stored on the server as files and downloaded only when they are needed. I could (and did until recently) just load the data from the database on startup. However, this obviously gets progressively slower as the number of records increases. It also struck me as pointless downloading the data time after time where it had not changed or was little changed. So I thought it would be better to store the data locally. For a large dataset (including heavy duty encryption of the local file) I get around a 20% time saving and a lot less traffic on the server. The actual documents when downloaded are cached (if that's what the user specifies) in a temp folder. It is all server side save for some caching. Whilst there isn't a web UI it is not a path I want to go down. Have used them extensively in the past and I don't think it is appropriate for the current usage for various reasons. Quite happy with a desktop app and using internet components and/or FireDAC. It works well and I am long way down the road. That's as may be. However, I am 5 years down the road, the software works as I want it. I am thinking of changing the way I load and refresh data not thinking of throwing out baby with the bathwater!

I understand that, but I am keen to speed up user experience at client end and reduce traffic at server end and doing it by way of a local file does that. I will now demonstrated my ignorance of REST services. I don't understand how it will result in faster loading of the same data and less bandwidth.

Noted. Just working in Postgres at the moment and trying to finish an app. Offered in Postgres initially and intending to offer connectors for other dbs when requested. If at the time of doing so I experience any issues I may then opt for REST server. Yes. It's there for PostGres also. You use the PGAdvanced parameter of the connection which gives you access to Postgre's Database Connection Control Functions. They don't have to be. But with large amounts of data it I find it is much faster to load the data from a local file and refresh it from the server in the background.

I've not really looked into possibility of using REST. If I'm not mistaken I would need the enterprise edition of Delphi rather than the mere professional to implement REST services. Not a deal breaker in itself, but I am not convinced that I need to go down that route (time is a major constraint for me at the moment). FireDAC seems to make it fairly simple to change horses between different databases. Isn't it then a question of ensuring your queries etc are compliant SQL and, if not, adapted as necessary for each database you support?

I'm not sure how to measure how much time it takes to retrieve from the server and how much to locally load. However, I know that loading from a static file is significantly faster than loading from the server so I'm pretty sure the bottleneck lies with the retrieval time. I'll have a look at compression. Haven't used it so far and didn't know FireDac supported it. Although my principal goal is to minimize resources server side. Noted. I will try and do it via temp tables.

It's a system for handling documents in litigation matters. So the number of documents can range from a few hundred to hundreds of thousands. Whilst a case is in its early stages the documents will (depending on the nature of the case) be loaded in large tranches. When it gets closer to its conclusion, you will get much fewer new documents being added to the case. I guess a count of how much new data there is and then decide on whether to use where statement or pass in an array of ids. Not sure, but I will look into it. However, I need a solution that will work for Postgre, Oracle and MS SQL at the very least, so it has to be compatible across the board, although I suppose I could write a different routine for each server if needs be. As for the IN clause, if it is particularly large in relation to the total number of records I could just load the lot via a thread and then dump the static data when I have the new data. If it's not too large relative to the total number of records, but still relatively large for an IN clause I guess I could submit in batches.

Unfortunately, I need to be able to offer a range of databases.

Of course, although an ocx usually has a visual element to it rather than just a library of procedure. I thought it may have a problem with ocx because of the visual element. Always best to check I feel.

It can be a pretty massive table, which can take a long while to load. It obviously loads much quicker from local file (even though encrypted). I am keen to avoid passing unnecessary traffic to the server, hence suggestion of local file and update with only the data necessary. There could be just a handful of records to update. I really don't want to download the lot again just for that. I think this is a sensible way to approach it. I'm just not sure that the way I propose to handle it is the most sensible. But I am keen to avoid full reload of whole table.

Had a quick look. Very interesting. Looks like quite a bit of work to produce a large app and also I think it requires replacement of all visual components with their own. Couldnt't see some fast conversion script for this. Maybe it's as easy as copy and replace of pas and dfm file class names. However, I rely significantly on virtualTreeView for display of db data. I don't think the treeview they provide would be suitable. Could convert to TListView, but I couldn't find that as an option on their components page. I don't use DGGrid's for various reasons, but could always consider that. All the same, worth keeping an eye and it could well be useful for "light" version of desktop apps.

That all sounds very promising. Thanks for the comments. My app is fairly heavy duty and with a huge executable (which seems to be par for the course with Delph nowadays). It needs to access several dlls and an ocx. The ocx is mine and could be (possibly should have been) written as a dll. Are there any issues you know of with dlls? I use theming and would like to retain it, but it's clients who are asking for possibility of cloud app, so they can make the choice of living without it. I can keep in the desktop app version. I was under the impression that the license fee charged by Cybelesoft included the hosting of the app. However, the hosting shouldn't be a problem. Each client would probably set up their own internal and external servers to host it. That leads to another question and that's what sort of degradation in speed have you experienced between desktop and cloud? What is actually involved in redeveloping as a web app? Is it possible in Delphi? I haven't seen anything anywhere dealing with this.

Thanks for the link. It didn't seem to work for me. However, it gave me the idea to change my function to repaint the background of the glyph. Now works and will have to do for now. Function GetTransparentImageList(Owner:TComponent; SourceImages:TImageList; BMPIndex:Integer):TImageList; var bmp:TBitmap; begin bmp:=TBitmap.Create; try if SourceImages.GetBitmap(BMPIndex, bmp) then begin Result:=TImageList.Create(Owner); With Result do begin bmp.Canvas.Brush.Color:=StyleServices.GetSystemColor(clWindow);; bmp.Canvas.FloodFill(0, 0, bmp.canvas.Pixels[0, 0], fsSurface); AddMasked(bmp, clFuchsia); end; end; finally bmp.Free; end; end;

From within an app I save data obtained over the internet from a database to a local file so that when the user needs to reopen it can be loaded locally rather than across the net. Also, the user can save other data they are working on for later restoration. My inclination is to place these file in "C:\ProgramData" (CSIDL_COMMON_APPDATA), but I notice Embarcadero, for example, save new projects off "C:\Users\Public\Public Documents". Foxit also use it. Two questions: What is considered best practice for storing this sort of data? How do you get the "C:\Users\Public\Public Documents"? There doesn't appear to be a constant value to use with SHGetFolderPathA to get it. All I can find is CSIDL_COMMON_DOCUMENTS and that gets "C:\User\Public\Documents", which doesn't exist on my version of Windows10.

That's what I've opted for. Thanks.

I don't actually want them to know where the data is and I also want it to be shared amongst anyone else who has access to the app, but only via my app.

And TMyObject(SL[index]).Something Where SL is a TStrings. If I had a dollar for every time...

Ah yes! Thanks

Damn tricky fellas! I should have realised that! It's the third option. So I will place in Public Documents ShellGetFolderPath(CSIDL_COMMON_DOCUMENTS, Path); path := includeTrailingPathDelimiter(path); stream.saveToFile(path+'stream.txt'); Saves to C:\Users\Public\Public Documents' . So all is good. I don't actually want users to try and open the files directly from the folder. My app just needs to know where they are so it can build a list of them (FindFirst...) and offer them via the app for opening. They could be anywhere as far as the app is concerned, it just needs to know how to find them.

I make that error quite often. I've often wondered why the compiler doesn't pick it up.

Does anyone know a way of getting the above to function in 64 bit or is there an alternative?