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 --->
<cffunction name="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 their dbtype. Of course, this means there will be a lot more
files to work with, which can also be a problem. Right now there are
about 450 files total in the application, and doing this would cause the
total number to increase to about 1200.
Anyways, who has done something like this before and what are your
experiences?
M
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
