RE: Deploying to Multiple Databases
I try to avoid needing platform-specific sql during the app's initial construction.May be too late or impossible for you to go down that road. Using CF to produce things like the date value from your example instead of using the db's native functions eliminates the need to worry about db-specific functions.As a rule I just do without db functions and set values outside of cfquery. This may mean compromising performance, but generally, for my own needs, this has been extremely rare.For the exceptions where it can't be done (for example, using a sequence for an insert on Oracle) its pretty easy to write a bit of conditional sql cfquery datasource=#request.myDSN# INSERT INTO myfile (cfif not Compare(myCachedQuery.DB,2)ID,/cfif Blah) VALUES (cfif not Compare(myCachedQuery.DB,2)seq_myfile.nextval,/cfif cfqueryparam cfsqltype=CF_SQL_VARCHAR value=#form.Blah#) /cfquery In cases where there's no way around doing fully custom sql I'll put the conditional sql inside of a case statement; but for me it's a rare need. Matt Robertson [EMAIL PROTECTED] MSB Designs, Inc.http://mysecretbase.com [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Deploying to Multiple Databases
Jim - Thanks for the feedback. As far as using a Web service is concerned, I am trying to write this for the lowest common denominator and want to avoid doing anything people might be unfamilar with or not be able to use. CFMX is still a little too new for some organizations I want to target with this app. What do you think about calling all queries within a custom tag that will determine the right statement based on the db type? In other words, I would break out all the conditional SQL into separate queries that would be called within a custom tag and passed back to the application. I am concerned this method might lead to too much overhead, because I don't really know how CF handles recordsets in this situation. Does CF create a duplicate recordset in the calling page, or does the calling page reference the one in the custom tag? (Details, details...) M -Original Message- From: Jim Davis [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 29, 2003 1:24 AM To: CF-Talk Subject: RE: Deploying to Multiple Databases Using CFCs I do this: I've got a DPDataSource CFC which has four proerties: DSNName, DBType, UserName and Password. The DBType is the important bit.For example, it may be MSSQLServer2000 and determines which codebase to use. Now, I place all of my data access methods into a broker CFC which is utilized by the base class (I just do this to keep the size of the CFCs down a little).Anyway my broker handles how to call the DB-specific files.For example here is the save method from one of my brokers: !--- Create/Update Artist --- cffunctionname=save hint=Saves Artist information to the database. returntype=void access=public output=No !--- Attempt to include the relevant implementation file based on the DataSource type file --- cftry cftransaction action=""> isolation=SERIALIZABLE cfinclude template=DB_#this.DPDataSource.getType()#\ArtistBroker_save.cfm /cftransaction !--- Catch Errors --- cfcatch type=MissingInclude cfset this.DPException.new(DPEvents.Artist.Broker.MissingImplementationFile, No ArtistBroker_save implementation file for DataSource type #this.DPDataSource.getType()# exists.) cfset this.DPException.throw() /cfcatch cfcatch type=Any cfrethrow cfset this.DPException.new(DPEvents.Artist.Broker, An error occured attempting to save the Artist., cfcatch) cfset this.DPException.throw() /cfcatch /cftry /cffunction Note that the method itself doesn't have any SQL in it - rather it uses the value in the DPDataSource component to include the proper file from a directory named after the value.So my directories may look like this: Cfcs - - DB_MSSQLServer2000 - - DB_MSSQLServer65 - - DB_MySQL And so forth.The include files are as complex as the DB needs (most often just simple statements) but may contain multiple actions.The application layer may expect back a query (or queries) and do more work after the try/catch block.In effect you specify what the include needs and what it will return. There many benefits to this (I think): 1) You can add a new database very easily: a new directory with that DBs code and a very simple change to the DataSource component created. 2) The same set of components can be used for ALL supported database simultaneously (by instantiated them with different datasource components).By leveraging the components this also opens up the ability to easily create conversion tools - instantiate one component on SQL Server and another on Oracle, trade the data between the CFCs, then save.No direct DB integration needed, but full conversion compatibility. 3) As long as you defined the needed output from the includes (query objects usually) you don't NEED a database.Your datatype could be XML for example and your includes could manage a bunch of XML files: as long as you generated the proper queries to return to the application from that XML.In other words the implementation doesn't assume SQL - but it might assume it's getting back queries. You could just create multiple brokers - but dynamic CFC names are impossible in some cases and just plain hard in all others - it's just not worth it (you really have to have several sets of brokers which you swap in and out). I'm not sure if any of that rambling made sense. ask if it didn't! Jim Davis -Original Message- From: [EMAIL PROTECTED] [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 11:46 PM To: CF-Talk Subject: Deploying to Multiple Databases I have a question about deploying an application into multiple environments, and am looking for any lessons learned from people who have done this. This is almost an aesthetics question. Essentially, there is this app I built that is intended to run using either MS-SQL or MySQL, and I want to come up with the best way to package the SQL statements. Other developers will probably want to work with the code someday, so I want to make sure the code is as easy to understand as possible. Right now I have a req
RE: Deploying to Multiple Databases
Actually I haven't done anything with Web Services (yet) - if you're under the impression that CFCs are only for constructing Web Services then you're way off (and missing out on a HUGE productivity gain).;^) You could (as you suggest) mimic the behavior with CustomTags however so that's up to you.I wouldstill recommend using the same basic idea: directories of include files with your DB-specific code and a broker custom tag to call and manage them.You get pretty much all the benefits I mentioned except for persistence (of the CFC). This method will lead to MORE overhead, but I really doubt too much overhead.In general, when programming, you have to trade performance for maintainability and extensibility.The faster you make something, the less portable, upgradeable and extensible it is.By the same token the more extensible and portable you make things the slower they get. As far your specific question - it depends.By default CF isolates all CustomTag code into it's own memory space.However you can set the query directly to the caller via the caller scope to eliminate double memory usage by having a copy in the tag and a copy in the caller (although the copy in the tag will be eliminated when the tag ends in any case). Personally I would begin by designing the perfect extensibility architecture - forget completely about performance.Build a small sample using it and another using direct DB calls then pound it a little (run some tests).I think you'll be pleasantly surprised at the (generally rather small) differences in performance: they'll rarely be enough to scuttle the app.You can then focus on improving the performance of your model. This way: focusing on your problem and concepts initially but ignoring technical considerations you almost always end up with a better system (but don't forget to do the optimization phase at the end!) Jim Davis -Original Message- From: Haggerty, Mike [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 29, 2003 11:26 AM To: CF-Talk Subject: RE: Deploying to Multiple Databases Jim - Thanks for the feedback. As far as using a Web service is concerned, I am trying to write this for the lowest common denominator and want to avoid doing anything people might be unfamilar with or not be able to use. CFMX is still a little too new for some organizations I want to target with this app. What do you think about calling all queries within a custom tag that will determine the right statement based on the db type? In other words, I would break out all the conditional SQL into separate queries that would be called within a custom tag and passed back to the application. I am concerned this method might lead to too much overhead, because I don't really know how CF handles recordsets in this situation. Does CF create a duplicate recordset in the calling page, or does the calling page reference the one in the custom tag? (Details, details...) M -Original Message- From: Jim Davis [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 29, 2003 1:24 AM To: CF-Talk Subject: RE: Deploying to Multiple Databases Using CFCs I do this: I've got a DPDataSource CFC which has four proerties: DSNName, DBType, UserName and Password. The DBType is the important bit.For example, it may be MSSQLServer2000 and determines which codebase to use. Now, I place all of my data access methods into a broker CFC which is utilized by the base class (I just do this to keep the size of the CFCs down a little).Anyway my broker handles how to call the DB-specific files.For example here is the save method from one of my brokers: !--- Create/Update Artist --- cffunctionname=save hint=Saves Artist information to the database. returntype=void access=public output=No !--- Attempt to include the relevant implementation file based on the DataSource type file --- cftry cftransaction action=""> isolation=SERIALIZABLE cfinclude template=DB_#this.DPDataSource.getType()#\ArtistBroker_save.cfm /cftransaction !--- Catch Errors --- cfcatch type=MissingInclude cfset this.DPException.new(DPEvents.Artist.Broker.MissingImplementationFile, No ArtistBroker_save implementation file for DataSource type #this.DPDataSource.getType()# exists.) cfset this.DPException.throw() /cfcatch cfcatch type=Any cfrethrow cfset this.DPException.new(DPEvents.Artist.Broker, An error occured attempting to save the Artist., cfcatch) cfset this.DPException.throw() /cfcatch /cftry /cffunction Note that the method itself doesn't have any SQL in it - rather it uses the value in the DPDataSource component to include the proper file from a directory named after the value.So my directories may look like this: Cfcs - - DB_MSSQLServer2000 - - DB_MSSQLServer65 - - DB_MySQL And so forth.The include files are as complex as the DB needs (most often just simple statements) but may contain multiple actions.The application layer may expect back a query (or queries) and do more work after the try/catch block.In effec
RE: Deploying to Multiple Databases
Using CFCs I do this: I've got a DPDataSource CFC which has four proerties: DSNName, DBType, UserName and Password. The DBType is the important bit.For example, it may be MSSQLServer2000 and determines which codebase to use. Now, I place all of my data access methods into a broker CFC which is utilized by the base class (I just do this to keep the size of the CFCs down a little).Anyway my broker handles how to call the DB-specific files.For example here is the save method from one of my brokers: !--- Create/Update Artist --- cffunctionname=save hint=Saves Artist information to the database. returntype=void access=public output=No !--- Attempt to include the relevant implementation file based on the DataSource type file --- cftry cftransaction action=""> isolation=SERIALIZABLE cfinclude template=DB_#this.DPDataSource.getType()#\ArtistBroker_save.cfm /cftransaction !--- Catch Errors --- cfcatch type=MissingInclude cfset this.DPException.new(DPEvents.Artist.Broker.MissingImplementationFile, No ArtistBroker_save implementation file for DataSource type #this.DPDataSource.getType()# exists.) cfset this.DPException.throw() /cfcatch cfcatch type=Any cfrethrow cfset this.DPException.new(DPEvents.Artist.Broker, An error occured attempting to save the Artist., cfcatch) cfset this.DPException.throw() /cfcatch /cftry /cffunction Note that the method itself doesn't have any SQL in it - rather it uses the value in the DPDataSource component to include the proper file from a directory named after the value.So my directories may look like this: Cfcs - - DB_MSSQLServer2000 - - DB_MSSQLServer65 - - DB_MySQL And so forth.The include files are as complex as the DB needs (most often just simple statements) but may contain multiple actions.The application layer may expect back a query (or queries) and do more work after the try/catch block.In effect you specify what the include needs and what it will return. There many benefits to this (I think): 1) You can add a new database very easily: a new directory with that DBs code and a very simple change to the DataSource component created. 2) The same set of components can be used for ALL supported database simultaneously (by instantiated them with different datasource components).By leveraging the components this also opens up the ability to easily create conversion tools - instantiate one component on SQL Server and another on Oracle, trade the data between the CFCs, then save.No direct DB integration needed, but full conversion compatibility. 3) As long as you defined the needed output from the includes (query objects usually) you don't NEED a database.Your datatype could be XML for example and your includes could manage a bunch of XML files: as long as you generated the proper queries to return to the application from that XML.In other words the implementation doesn't assume SQL - but it might assume it's getting back queries. You could just create multiple brokers - but dynamic CFC names are impossible in some cases and just plain hard in all others - it's just not worth it (you really have to have several sets of brokers which you swap in and out). I'm not sure if any of that rambling made sense. ask if it didn't! Jim Davis -Original Message- From: [EMAIL PROTECTED] [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 11:46 PM To: CF-Talk Subject: Deploying to Multiple Databases I have a question about deploying an application into multiple environments, and am looking for any lessons learned from people who have done this. This is almost an aesthetics question. Essentially, there is this app I built that is intended to run using either MS-SQL or MySQL, and I want to come up with the best way to package the SQL statements. Other developers will probably want to work with the code someday, so I want to make sure the code is as easy to understand as possible. Right now I have a request variable in a settings file (this is a Fusebox application) that tells CF to use either the MySQL or the MSSQL version of the query language. Consequently, all of my queries look like this beast: cfquery name=qry_get_event datasource=#request.dsn# cfif request.dbtype EQ mssql SELECT TOP 1 event_title, event_desc, event_date FROM abc_event WHERE ev_active = 1 ORDER BY event_date cfelseif request.dbtype EQ mysql SELECT event_title, event_desc, event_date FROM abc_event WHERE ev_active = 1 ORDER BY event_date LIMIT 1 /cfif /cfquery Also, I set a range of variables corresponding to various functions in each db, i.e. GETDATE() and CURRENT_DATE() are output by CF throughout the code. My thoughts are that this is too convoluted, and anyone having to support the code is going to have headaches (especially after I add the PostGres, DB2 and Oracle support). So I am going to rewrite the code, I just don't know what approach to take. The idea I had was to move all the query statements into separate files based on
