BorlandTalk.com Borland discussion newsgroups   Archives   FAQ   Search   Memberlist   Usergroups   Register   Profile   Log in to check your private messages   Log in  Success! A working D7+WebBroker+dbExpress+MySQL ISAPI appli          BorlandTalk.com Forum Index -> Delphi Internet Isapi-WebBroker View previous topic :: View next topic   Author Message Dan Ridenhour Guest Posted: Thu Sep 11, 2003 7:12 pm    Post subject: Success! A working D7+WebBroker+dbExpress+MySQL ISAPI appli All, Ive finally hit a bit of success in my quest to convert an ISAPI+DirectOracleAccess+Oracle to use mysql with dbexpress. I have a test ISAPI which includes an action which uses 4 queries and generates a list of records (<100) using page producers which get their templates from text files on the webserver. Ive now successfully load tested this client to 64 simultanious ISAPI requests!!!! It slows down a bit (which is to be expected) but it doesnt fail or throw exception. Finally! Heres the model for anyone else working on something similar: - Use a single TSQLConnection component per webmodule. - Set its autoclone=false, connected=true within a critical section in the WebModules OnCreate event. - Use TSQLQuery components connected to TDatasetProvider and TClientDataset to retrieve data. - After each query executes call the TSQLConnection's CloseDatasets method to assure there are no open queries before opening another query. This assures that there is one TSQLConnection per webmodule instance, and this connection is used for all queries. I havent tried updates yet, thats next... but stable queries are finally a reality. ;-) If anyone has any questions fire away. ;-) Thanks, Dan Back to top Shiv Kumar Guest Posted: Thu Sep 11, 2003 9:13 pm    Post subject: Re: Success! A working D7+WebBroker+dbExpress+MySQL ISAPI a Dan, I've read your other posts so I'll answer/ask here. The lessons you've learnt are obviously "hard earned" . Quote: - Use a single TSQLConnection component per webmodule. I do this all the time. Irrespective of database or data access method. Quote: - Set its autoclone=false, connected=true within a critical section in the WebModules OnCreate event. Didn't have to do the autoclone thing, but I guess this is a MySQL issue only. Quote: - Use TSQLQuery components connected to TDatasetProvider and TClientDataset to retrieve data. I use only TSQLQuery and TSQLStoredProc. I personally don't see the need for a client dataset unless you use it purely as an in memory dataset. What I do is I get the data from dbExpress and move it into an in memory dataset and close the dbExpress query. As a result, I never have more than one query open within a WebModule. I've actually been doing things like this since (well I'd like to say since day one, but I'll say..) a very long time. I guess, since I don't use data aware controls etc. even in my GUI applications, I'm used to making methods calls to get/insert/update/delete data. Quote: - After each query executes call the TSQLConnection's CloseDatasets method to assure there are no open queries before opening another query. As mentioned above... Well, so the way I see it, besides the autoclone issue, that rest are "best practices" . But you live and learn right ? I'm sure you're over the hump now and it's smooth sailing from here on in. Good job by the way!. -- Shiv R. Kumar The Delphi Apostle http://www.matlus.com Back to top Dan Ridenhour Guest Posted: Fri Sep 12, 2003 5:03 pm    Post subject: Re: Success! A working D7+WebBroker+dbExpress+MySQL ISAPI a "Shiv Kumar" <shivk (AT) erols (DOT) com> wrote: Quote: Dan, I've read your other posts so I'll answer/ask here. The lessons you've learnt are obviously "hard earned" . - Use a single TSQLConnection component per webmodule. I do this all the time. Irrespective of database or data access method. Well, thats what I always do as well... the problem comes in with dbexpress+mysql. It autoclones additional connections for each additional query... so while I only explicitly created one connection... there were actually alot of temporarly connections being created. And MySQL has a limitation of all connections being made serially... so all the connections happening automatically by dbexress autoclone were a big part of the problem... since they could happen simultaniously in different threads. So to correct that problem i had to disable cloning, open the connection in a criticalsection, and make changes to the app so it never needed more than one query/connection open at a time. Quote: - Set its autoclone=false, connected=true within a critical section in the WebModules OnCreate event. Didn't have to do the autoclone thing, but I guess this is a MySQL issue only. Yep, and really the biggest part of the problem, as noted above... MySQL needs a separate connection for each query so if you have one connection in your webmodule it autoclones additional connections as needed unless you disable autoclone and assure that your app will never need more than one connection per web module. Quote: - Use TSQLQuery components connected to TDatasetProvider and TClientDataset to retrieve data. I use only TSQLQuery and TSQLStoredProc. I personally don't see the need for a client dataset unless you use it purely as an in memory dataset. What I do is I get the data from dbExpress and move it into an in memory dataset and close the dbExpress query. As a result, I never have more than one query open within a WebModule. I've actually been doing things like this since (well I'd like to say since day one, but I'll say..) a very long time. I guess, since I don't use data aware controls etc. even in my GUI applications, I'm used to making methods calls to get/insert/update/delete data. Yep, im using the clientdatasets as inmemory datasets... that lets me open the query, and get the data... then close the query so i can reuse the connection... then the rest of my code just references the clientdatasets as it used to access the queries directly from the db. Quote: - After each query executes call the TSQLConnection's CloseDatasets method to assure there are no open queries before opening another query. As mentioned above... Well, so the way I see it, besides the autoclone issue, that rest are "best practices" . But you live and learn right ? Yep, the problem is that looking at my app... I was using best practices... single connection per webmodule, etc... its just that dbexpress does some things automatically behind the scenes that cause alot of problems when multiple threads come into play. for example, the app had a single connection component but because of the autoclone i estimate that under a load the application was generating about 100 or more connections per minute. Quote: I'm sure you're over the hump now and it's smooth sailing from here on in. Good job by the way!. Thanks... as far as i can tell just about all the gotchas with dbexpress+mysql are undocumented or at least buried pretty deep... without your help and the help of dmitry over in the dbexpress forum id still be banging my head against the wall. Ive got about 10% of my live app coverted to using this way of doing things, and have stressed it to 64 users without problems... so ill be continuing with the rest of the changes today. The one gotcha is i havent reworked any actions which save data yet... so still gotta play with that... not sure if its ok to use TClientDataset.applyupdates or if i need to format my own inserts/updates and send those. The second is probably more stable but also a pain. Thanks again Dan Ridenhour [email]dridenhour (AT) stltoday (DOT) com[/email] Quote: -- Shiv R. Kumar The Delphi Apostle http://www.matlus.com Back to top Shiv Kumar Guest Posted: Fri Sep 12, 2003 5:15 pm    Post subject: Re: Success! A working D7+WebBroker+dbExpress+MySQL ISAPI a And while you're at it remove the try - excepts from all your actions and use the OnExcpetion event. :) -- Shiv R. Kumar The Delphi Apostle http://www.matlus.com Back to top Dan Ridenhour Guest Posted: Fri Sep 12, 2003 7:16 pm    Post subject: Re: Success! A working D7+WebBroker+dbExpress+MySQL ISAPI a "Shiv Kumar" <shivk (AT) erols (DOT) com> wrote: Quote: And while you're at it remove the try - excepts from all your actions and use the OnExcpetion event. Hmm... is there a reason to do this? I kinda like having the ability to vary my exception messages by action... is it hurting anything to have the exceptions at that level? Also, btw... using the ApplyUpdates of a clientdataset-->dataprovider-->mysqlquery isnt applying the updates back to mysql.. its wierd it does it in a standalone app... but is failing in the isapi... oh well something new to play with. ;-) Dan Quote: -- Shiv R. Kumar The Delphi Apostle http://www.matlus.com Back to top Shiv Kumar Guest Posted: Fri Sep 12, 2003 8:51 pm    Post subject: Re: Success! A working D7+WebBroker+dbExpress+MySQL ISAPI a The reasons... 1. Each try except as code over head. 2. WebBroker already has a "catch all" exception handler that spits out an html page. 3. D6 and above have a centralized OnException event. By adding a try except in every action, you're simply adding to the overhead. There is no added value. Except... Quote: I kinda like having the ability to vary my exception messages by action I'll give you a possible solution later... The way I write my apps, a WebModule is simply a presentation layer. It knows only how to present the data. Anything it does, does not produce exceptions. I then have a business module and a separate data access module. Each of these *may* raise exceptions. But I don't ever have try-except handlers in my code. Exceptions raised in my code are then trapped by the OnExcpetion event and displayed in a nice user friendly manner. Any exception that is not raised by you intentionall is to be treated *very* differently. These are things you need to account for, or program around. Once you've "taken care" of these you're left with, what I call "business exceptions". In other words, if a user calls and tells me she sees an access violation (I don't ever get these by the way ). I know there is something I missed, didn't test or issues of the kind you were having with multiple connections etc. There is absolutely nothing you can do about these, other than fix them. Once fixed and tested, they should never show up again. So at the end of it all, you're left with the need to handle "business exceptions". For this ============================================================================ ====== { TISAPIExceptionInfo } TISAPIExceptionInfo = class(TPersistent) private FRedirectPathInfo: string; FPathInfo: string; FModuleMethodName: string; FAdditionalMessage: string; FModule: string; FMessage: string; public procedure Assign(Source: TPersistent); override; property Message: string read FMessage write FMessage; property AdditionalMessage: string read FAdditionalMessage write FAdditionalMessage; property Module: string read FModule write FModule; property ModuleMethodName: string read FModuleMethodName write FModuleMethodName; property PathInfo: string read FPathInfo write FPathInfo; property RedirectPathInfo: string read FRedirectPathInfo write FRedirectPathInfo; end; { EISAPIApplicationException } EISAPIApplicationException = class(Exception) private FHTMLFormat: Boolean; FISAPIExceptionInfo: TISAPIExceptionInfo; protected function FormatExceptionMessage: string; virtual; public constructor Create(const Msg, AAdditionalMessage, AModule, AModuleMethodName, APathInfo, ARedirectPathInfo: string; AHTMLFormat: Boolean = True); overload; destructor Destroy; override; property ISAPIExceptionInfo: TISAPIExceptionInfo read FISAPIExceptionInfo; property HTMLFormat: Boolean read FHTMLFormat write FHTMLFormat; end; EPredefinedMessageException = class(EISAPIApplicationException) public constructor Create(const MessageResource, AAdditionalMessage, AModule, AModuleMethodName, APathInfo, ARedirectPathInfo: string; AHTMLFormat: Boolean = True); overload; end; ESecurityException = class(EPredefinedMessageException) public constructor Create(const AAdditionalMessage, AModule, AModuleMethodName, APathInfo, ARedirectPathInfo: string; AHTMLFormat: Boolean = True); overload; end; EISAPISessionInvalid = class(EPredefinedMessageException) public constructor Create(const AAdditionalMessage, AModule, AModuleMethodName, APathInfo, ARedirectPathInfo: string; AHTMLFormat: Boolean = True); overload; end; EISAPISessionExpired = class(EPredefinedMessageException) public constructor Create(const AAdditionalMessage, AModule, AModuleMethodName, APathInfo, ARedirectPathInfo: string; AHTMLFormat: Boolean = True); overload; end; ============================================================================ ======================= What really happens is that in my code I raise one of these exceptions. The OnException event translates this into a "dialog box" kind of page with an "ok" button. The click of this ok button redirects the user using the RedirectPathInfo information. The other information is used during debugging, where in you see the module, method and any additional information you might want to see. But all this is set up at the time you raise the exception so that when the exception is "handled" you have it there. So you get: >I kinda like having the ability to vary my exception messages by action Does all of this make sense? -- Shiv R. Kumar The Delphi Apostle http://www.matlus.com Back to top Dan Ridenhour Guest Posted: Fri Sep 12, 2003 9:12 pm    Post subject: Re: Success! A working D7+WebBroker+dbExpress+MySQL ISAPI a Shiv, All the below, makes sense more or less and ill likely convert to something along those lines once i have the app functional... deadlines ya know. . I did notice one thing with the model im using now under mysql... I cant use ApplyUpdates anymore to post changes back to the database in my post actions. Ive worked around this creating the individual queries in the action and using Connection.ExecuteDirect('sqlqueryhere') and this works fine. I cant use prefab TSQLQuery updates and inserts because I wont know ahead of time exactly what fields are on the form being submitted (since the ui is changable without recompilation). In the past id loop thru the fields in the form being submitted and assign the values to the appropriate field in the underlying dataset then post the changes with applyupdates. Now Im looping thru the fields and concating a query together then executing it to post the changes. Is there an easier way to do this? I figure i have about 30-40 form posts to rework so im looking for shortcuts. ;-) Thanks again, Dan "Shiv Kumar" <shivk (AT) erols (DOT) com> wrote: Quote: The reasons... 1. Each try except as code over head. 2. WebBroker already has a "catch all" exception handler that spits out an html page. 3. D6 and above have a centralized OnException event. By adding a try except in every action, you're simply adding to the overhead. There is no added value. Except... I kinda like having the ability to vary my exception messages by action I'll give you a possible solution later... The way I write my apps, a WebModule is simply a presentation layer. It knows only how to present the data. Anything it does, does not produce exceptions. I then have a business module and a separate data access module. Each of these *may* raise exceptions. But I don't ever have try-except handlers in my code. Exceptions raised in my code are then trapped by the OnExcpetion event and displayed in a nice user friendly manner. Any exception that is not raised by you intentionall is to be treated *very* differently. These are things you need to account for, or program around. Once you've "taken care" of these you're left with, what I call "business exceptions". In other words, if a user calls and tells me she sees an access violation (I don't ever get these by the way ). I know there is something I missed, didn't test or issues of the kind you were having with multiple connections etc. There is absolutely nothing you can do about these, other than fix them. Once fixed and tested, they should never show up again. So at the end of it all, you're left with the need to handle "business exceptions". For this ============================================================================ ====== { TISAPIExceptionInfo } TISAPIExceptionInfo = class(TPersistent) private FRedirectPathInfo: string; FPathInfo: string; FModuleMethodName: string; FAdditionalMessage: string; FModule: string; FMessage: string; public procedure Assign(Source: TPersistent); override; property Message: string read FMessage write FMessage; property AdditionalMessage: string read FAdditionalMessage write FAdditionalMessage; property Module: string read FModule write FModule; property ModuleMethodName: string read FModuleMethodName write FModuleMethodName; property PathInfo: string read FPathInfo write FPathInfo; property RedirectPathInfo: string read FRedirectPathInfo write FRedirectPathInfo; end; { EISAPIApplicationException } EISAPIApplicationException = class(Exception) private FHTMLFormat: Boolean; FISAPIExceptionInfo: TISAPIExceptionInfo; protected function FormatExceptionMessage: string; virtual; public constructor Create(const Msg, AAdditionalMessage, AModule, AModuleMethodName, APathInfo, ARedirectPathInfo: string; AHTMLFormat: Boolean = True); overload; destructor Destroy; override; property ISAPIExceptionInfo: TISAPIExceptionInfo read FISAPIExceptionInfo; property HTMLFormat: Boolean read FHTMLFormat write FHTMLFormat; end; EPredefinedMessageException = class(EISAPIApplicationException) public constructor Create(const MessageResource, AAdditionalMessage, AModule, AModuleMethodName, APathInfo, ARedirectPathInfo: string; AHTMLFormat: Boolean = True); overload; end; ESecurityException = class(EPredefinedMessageException) public constructor Create(const AAdditionalMessage, AModule, AModuleMethodName, APathInfo, ARedirectPathInfo: string; AHTMLFormat: Boolean = True); overload; end; EISAPISessionInvalid = class(EPredefinedMessageException) public constructor Create(const AAdditionalMessage, AModule, AModuleMethodName, APathInfo, ARedirectPathInfo: string; AHTMLFormat: Boolean = True); overload; end; EISAPISessionExpired = class(EPredefinedMessageException) public constructor Create(const AAdditionalMessage, AModule, AModuleMethodName, APathInfo, ARedirectPathInfo: string; AHTMLFormat: Boolean = True); overload; end; ============================================================================ ======================= What really happens is that in my code I raise one of these exceptions. The OnException event translates this into a "dialog box" kind of page with an "ok" button. The click of this ok button redirects the user using the RedirectPathInfo information. The other information is used during debugging, where in you see the module, method and any additional information you might want to see. But all this is set up at the time you raise the exception so that when the exception is "handled" you have it there. So you get: >I kinda like having the ability to vary my exception messages by action Does all of this make sense? -- Shiv R. Kumar The Delphi Apostle http://www.matlus.com Back to top Shiv Kumar Guest Posted: Sat Sep 13, 2003 7:51 am    Post subject: Re: Success! A working D7+WebBroker+dbExpress+MySQL ISAPI a Quote: once i have the app functional... deadlines ya know. . Yes, of course. That's what I meant too. :) Quote: I cant use prefab TSQLQuery updates and inserts because I wont know ahead of time exactly what fields are on the form being submitted (since the ui is <snip> I need to understand your "requirements". Let say you have a table with 10 fields. The form posts back 5. What do you do for the other 5? Do you make them NULL, or do these five then go into another table? What I do (if I understand you correctly), is, I use an object that gets it properties assigned from the Request object (using RTTI). This object then generates SQL INSERT/UPDATE/DELETE queries that include only fields that have a value. There is a catch here, since properties in objects can't be "nothing", so integer, float, datetime properties default to 0 and strings default to blank (not NULL). So the object needs to be smart enough to exclude (in my case) datetimes that are 0 and strings that are blank. dbExpress (at least the MS SQL and Oracle drivers don't like NULL values when used in such queries (i.e. you can't set a field value to NULL). So I had to build another set of objects (they are really part of a framework) that could handle dbExpress peculiarities. If there is only a small number of permutations (of chosen fields) possible then I'd use separate query objects for each and decide which one to use. Quote: Now Im looping thru the fields and concating a query together Concatenation of strings is *very* slow so you might want create prefabricated queries (depending on permutations possible) instead. One thing I always do is I keep the field names of the form the same as the table field names. Besides the advantages this has in maintenance, you'll be able to work wonders by building (generic) routines or objects that can take values from a Request object and assign them to query parameters etc. By the way, Dan, it is customary to trim your quotes -- Shiv R. Kumar The Delphi Apostle http://www.matlus.com Back to top Marius Popescu Guest Posted: Fri Sep 19, 2003 8:32 pm    Post subject: Re: Success! A working D7+WebBroker+dbExpress+MySQL ISAPI a "Dan Ridenhour" <dridenhour (AT) stltoday (DOT) com> wrote Quote: Also, btw... using the ApplyUpdates of a clientdataset-->dataprovider-- mysqlquery isnt applying the updates back to mysql.. its wierd it does it in a standalone app... but is failing in the isapi... oh well something new to play with. Quote: Dan Hi Dan It may happen because your query gets its data from more than one table. On the provider OnGetTablename event (I hope i'm correct about the name) you can set the table where you want to submit your data. It helps too if you set the ProviderFlags for all the query's not-to-be-submitted fields to []. You can do it dynamically, no need of persistent fields. Hope that helps. Marius Popescu Back to top Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First         BorlandTalk.com Forum Index -> Delphi Internet Isapi-WebBroker All times are GMT Page 1 of 1   Jump to: Select a forum C++ Builder Newsgroups----------------C++ BuilderC++ Builder (ActiveX)C++ Builder (Announcements)C++ Builder (Command Line Tools)C++ Builder DatabasesC++ Builder Databases (ADO)C++ Builder Databases (Desktop)C++ Builder Databases (InterBase Express)C++ Builder Databases (Multi-Tier)C++ Builder Databases (SQL Servers)C++ Builder (Graphics)C++ Builder (IDE)C++ Builder (Internet)C++ Builder (Internet Socket)C++ Builder (Internet Web)C++ Builder (Jobs)C++ Builder (Language)C++ Builder (Language C++)C++ Builder (Compatibility with MFC)C++ Builder (Native API)C++ Builder (Non-Technical)C++ Builder (Students)C++ Builder (Third Party Tools)C++ Builder (Upgrade)C++ Builder (VCL)C++ Builder (VCL Components Usage)C++ Builder (VCL Components Development)C++ Builder (VCL Usage)C++ Builder (WinAPI)C++ Builder Installation Delphi Newsgroups----------------Delphi (General)Delphi ActiveX Controls UsageDelphi ActiveX Controls DevelopmentDelphi As400Delphi DatabasesDelphi Databases (ADO)Delphi Databases (dbExpress)Delphi Databases (Desktop)Delphi Databases (InterBase Express)Delphi Databases (Multi-Tier)Delphi Databases (SQL Servers)Delphi DeploymentDelphi GraphicsDelphi IDEDelphi InternationalizationDelphi InternetDelphi Internet Isapi-WebBrokerDelphi Internet WebSnapDelphi Internet WinsockDelphi JobsDelphi Language BASMDelphi Language ObjectPascalDelphi Model Driven Architecture (General)Delphi Model Driven Architecture (Third Party)Delphi MultimediaDelphi Native APIDelphi Non-TechnicalDelphi ObjectPascalDelphi OLE AutomationDelphi OO designDelphi OpenToolsAPIDelphi Reporting-ChartingDelphi RTLDelphi StudentsDelphi TeamSourceDelphi Thirdparty ToolsDelphi Thirdparty Tools (General)Delphi Thirdparty Tools (IntraWeb)Delphi Thirdparty Tools (RaveReports)Delphi UpgradeDelphi VCLDelphi VCL ComponentsDelphi VCL Components UsingDelphi VCL Components WritingDelphi WebServices SOAPDelphi WinAPIDelphi XMLDelphi Installationcomp.lang.pascal.delphi.components.misccomp.lang.pascal.delphi.components.usagecomp.lang.pascal.delphi.components.writingcomp.lang.pascal.delphi.databasescomp.lang.pascal.delphi.misccomp.sources.delphi JBuilder Newsgroups----------------JBuilderJBuilder AnnouncementsJBuilder Applet IssuesJBuilder As400JBuilder CompilerJBuilder CORBA/RMIJBuilder DatabasesJBuilder DB SwingJBuilder DebuggerJBuilder DeploymentJBuilder DocumentationJBuilder EnterpriseJBuilder HandheldJBuilder IDEJBuilder JavaJBuilder JavaAPIJBuilder Java LanguageJBuilder Java SwingJBuilder Java BeansJBuilder Java Beans UsageJBuilder Java Beans DevelopmentJBuilder JBCLJBuilder JDataStoreJBuilder JobsJBuilder LinuxJBuilder MacintoshJBuilder & MobileSetJBuilder MultiLingual AppsJBuilder Non-TechnicalJBuilder OpenToolsJBuilder Servlets (JSP)JBuilder StudentsJBuilder Team DevelopmentJBuilder Thirdparty ToolsJBuilder UpgradeJBuilder Installation InterBase Newsgroups----------------InterBaseInterBase Bug DiscussionInterBase GeneralInterBase IBConsoleInterBase OpenSourceInterBase ReplicationInterBase SQLInterBase ThirdpartyInterBase Installation Miscellaneous Borland Newsgroups----------------AppCenterAppServerEnteraVisiBroker  You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum Powered by phpBB © 2001, 2006 phpBB Group SEO toolkit © 2004-2006 webmedic.