RE: Building Applications for multiple DB's
All I can say is... My condolensces. Making an application database independent is an extremely daunting and time-consuming task. There is no easy way to do it. Since each database engine has many different rules, procedures and operations, you basically end up having to write all of the SQL again for each DB you want to support. The only way around this is to write your original SQL to adhere to the most basic ANSI standard - which is also hard to do because you then lose the support for the some of the functionality in the DB you originally wrote it for. Have a long and serious discussion with the powers that be at your place if the massive amount of time and effort (a.k.a. money) needed to make your CMS db independent will ever be realized in profits from sales. -Original Message- From: jonese [mailto:[EMAIL PROTECTED] Sent: Monday, April 11, 2005 4:07 PM To: CF-Talk Subject: Building Applications for multiple DB's Anyone have any pointers or examples of CF or PHP application which have been constructed with the ability to work on multiple Databases? IE the application can easily be run on MSSQL, Access, MySQL etc and all the user has to do is changed a config variable or point to another DB file. I'm trying to do the leg work now for a future upgrade to our CMS. Right now it runs on MSSQL, but we want to make it so users can easily install it into mysql, etc. thanks in advance. jonese ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:202274 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Building Applications for multiple DB's
Well we did something like this and all queries are in Components so the db variable config tells which component object to invoke. But as Steve mentionned, all components of queries need to be adapted to the DB. Pat -Original Message- From: Steve Brownlee [mailto:[EMAIL PROTECTED] Sent: April 11, 2005 16:14 To: CF-Talk Subject: RE: Building Applications for multiple DB's All I can say is... My condolensces. Making an application database independent is an extremely daunting and time-consuming task. There is no easy way to do it. Since each database engine has many different rules, procedures and operations, you basically end up having to write all of the SQL again for each DB you want to support. The only way around this is to write your original SQL to adhere to the most basic ANSI standard - which is also hard to do because you then lose the support for the some of the functionality in the DB you originally wrote it for. Have a long and serious discussion with the powers that be at your place if the massive amount of time and effort (a.k.a. money) needed to make your CMS db independent will ever be realized in profits from sales. -Original Message- From: jonese [mailto:[EMAIL PROTECTED] Sent: Monday, April 11, 2005 4:07 PM To: CF-Talk Subject: Building Applications for multiple DB's Anyone have any pointers or examples of CF or PHP application which have been constructed with the ability to work on multiple Databases? IE the application can easily be run on MSSQL, Access, MySQL etc and all the user has to do is changed a config variable or point to another DB file. I'm trying to do the leg work now for a future upgrade to our CMS. Right now it runs on MSSQL, but we want to make it so users can easily install it into mysql, etc. thanks in advance. jonese ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:202277 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Building Applications for multiple DB's
Your best bet is to encapsulate all your SQL into a collection of DAOs, and have one collection for each target database. Then you have a configuration parameter that says which DB you're using, and your code then uses the appropriate collection of DAOs to do the data access. That might seem like you'll need to write every query X times, where X is the number of target databases. For some queries, that's true, but for a lot of them, standard SQL will work. So create an abstract version of each DAO that has general query implementations in them, and then only override the ones you need to in your db-specific DAOs. It's a little more complex to implement, but it'll make you job a lot easier down the road. cheers, barneyb On Apr 11, 2005 1:07 PM, jonese <[EMAIL PROTECTED]> wrote: > Anyone have any pointers or examples of CF or PHP application which have > been constructed with the ability to work on multiple Databases? IE the > application can easily be run on MSSQL, Access, MySQL etc and all the user > has to do is changed a config variable or point to another DB file. > I'm trying to do the leg work now for a future upgrade to our CMS. Right > now it runs on MSSQL, but we want to make it so users can easily install it > into mysql, etc. > thanks in advance. > jonese > -- Barney Boisvert [EMAIL PROTECTED] 360.319.6145 http://www.barneyb.com/ Got Gmail? I have 50 invites. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:202282 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Building Applications for multiple DB's
Well I have a different take on this...but it really all comes down to what kind of SQL you've got. Obviously a switch from Oracle to MS SQL Server will have some re-writes involved (PL-SQL to T-SQL), but going from Access to MS SQL Server you should just be able to point at the new DB and fire it up. As I said...it does depend on what DB features you are usingso if you have a complex DB wuith loads of SPs etc...the yes...it could be toughif it's a simple DB then the swicth could be very painless Bryan Stevenson B.Comm. VP & Director of E-Commerce Development Electric Edge Systems Group Inc. phone: 250.480.0642 fax: 250.480.1264 cell: 250.920.8830 e-mail: [EMAIL PROTECTED] web: www.electricedgesystems.com ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:202283 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Building Applications for multiple DB's
> All I can say is... My condolensces. > Making an application database independent is an > extremely daunting and time-consuming task. > There is no easy way to do it. You haven't seen the onTap framework I take it. :P I'll admit, it's not been easy to get all the pieces to fit -- some were easy, some were rather challenging... but that's why I've made it open-source, so everyone can benefit. > Since each database engine has many different > rules, procedures and operations, you basically > end up having to write all of the SQL again for > each DB you want to support. Yes and no ... When I build my applications, I generally don't write SQL -- instead I have a series of custom tags and functions which write the SQL for me. The db's don't have to be consistent because my API is consistent. > The only way around this is to write your original SQL > to adhere to the most basic ANSI standard - which is > also hard to do because you then lose the support for > the some of the functionality in the DB you originally > wrote it for. Also not entirely true... I even managed to maintain concatenation, although I haven't used it myself lately. :) > Have a long and serious discussion with the powers that > be at your place if the massive amount of time and effort > (a.k.a. money) needed to make your CMS db independent > will ever be realized in profits from sales. I think he'd be talking to himself... though I could be wrong. :) If we're talking about a large application, yes, there would be some leg-work involved in converting it, but if there's a decent API in place, it can be realized without breaking the bank. s. isaac dealey 954.522.6080 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://macromedia.breezecentral.com/p49777853/ http://www.sys-con.com/author/?id=4806 http://www.fusiontap.com ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:202285 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Building Applications for multiple DB's
> Anyone have any pointers or examples of CF or PHP > application which have > been constructed with the ability to work on multiple > Databases? IE the > application can easily be run on MSSQL, Access, MySQL etc > and all the user > has to do is changed a config variable or point to another > DB file. > I'm trying to do the leg work now for a future upgrade to > our CMS. Right > now it runs on MSSQL, but we want to make it so users can > easily install it > into mysql, etc. > thanks in advance. > jonese You might have a look at the onTap framework. I've already done a lot of this leg-work, plus it's got lots of nice dba features not available with native CF tools. Currently it supports MS SQL 7+, MS Access, MySQL 4 (not sure about older versions) and Oracle 8i and later. You have to set a canonical name for the server type (mssql,msaccess,mysql or oracle) but beyond that most of the db information is gathered directly from JDBC. And it's open-source, so of course, while I'd love for you to be involved in the framework community :) you're free to pillage if you'd prefer. s. isaac dealey 954.522.6080 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://macromedia.breezecentral.com/p49777853/ http://www.sys-con.com/author/?id=4806 http://www.fusiontap.com ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:202286 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Building Applications for multiple DB's
Make use of stored procedures only. That way your cfc code can remain the same regardless of the database platform. -Adam On Apr 11, 2005 4:32 PM, Bryan Stevenson <[EMAIL PROTECTED]> wrote: > Well I have a different take on this...but it really all comes down to what > kind of SQL you've got. > > Obviously a switch from Oracle to MS SQL Server will have some re-writes > involved (PL-SQL to T-SQL), but going from Access to MS SQL Server you > should just be able to point at the new DB and fire it up. > > As I said...it does depend on what DB features you are usingso if you > have a complex DB wuith loads of SPs etc...the yes...it could be > toughif it's a simple DB then the swicth could be very painless > > Bryan Stevenson B.Comm. > VP & Director of E-Commerce Development > Electric Edge Systems Group Inc. > phone: 250.480.0642 > fax: 250.480.1264 > cell: 250.920.8830 > e-mail: [EMAIL PROTECTED] > web: www.electricedgesystems.com > > > ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:202287 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Building Applications for multiple DB's
> From: S. Isaac Dealey [mailto:[EMAIL PROTECTED] > You haven't seen the onTap framework I take it. :P It was only a matter of time :-) ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:202291 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Building Applications for multiple DB's
I'm not using DAO's, but I actually managed to get an implementation that supports concatenation, outer joins, unions and a slew of other stuff for all 4 databases with just a small collection of tags. It even supports DDL for creating the tables during installation of an application. > Your best bet is to encapsulate all your SQL into a > collection of > DAOs, and have one collection for each target database. > Then you have > a configuration parameter that says which DB you're using, > and your > code then uses the appropriate collection of DAOs to do > the data > access. > That might seem like you'll need to write every query X > times, where X > is the number of target databases. For some queries, > that's true, but > for a lot of them, standard SQL will work. So create an > abstract > version of each DAO that has general query implementations > in them, > and then only override the ones you need to in your > db-specific DAOs. > It's a little more complex to implement, but it'll make > you job a lot > easier down the road. s. isaac dealey 954.522.6080 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://macromedia.breezecentral.com/p49777853/ http://www.sys-con.com/author/?id=4806 http://www.fusiontap.com ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:202293 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Building Applications for multiple DB's
Except that eliminates current versions of MS Access and MySQL as platform options (which is something he mentioned specifically). > Make use of stored procedures only. That way your cfc code > can remain > the same regardless of the database platform. > -Adam > On Apr 11, 2005 4:32 PM, Bryan Stevenson > <[EMAIL PROTECTED]> wrote: >> Well I have a different take on this...but it really all >> comes down to what >> kind of SQL you've got. >> >> Obviously a switch from Oracle to MS SQL Server will have >> some re-writes >> involved (PL-SQL to T-SQL), but going from Access to MS >> SQL Server you >> should just be able to point at the new DB and fire it >> up. >> >> As I said...it does depend on what DB features you are >> usingso if you >> have a complex DB wuith loads of SPs etc...the yes...it >> could be >> toughif it's a simple DB then the swicth could be >> very painless s. isaac dealey 954.522.6080 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://macromedia.breezecentral.com/p49777853/ http://www.sys-con.com/author/?id=4806 http://www.fusiontap.com ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:202295 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Building Applications for multiple DB's
> From: Adrocknaphobia [mailto:[EMAIL PROTECTED] > Make use of stored procedures only. That way your cfc code > can remain the same regardless of the database platform. Not all platforms support stored procedures. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:202294 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Building Applications for multiple DB's
Yes, that's very true. My brain always seems to come from the point of view of things that I work on. However, if 80% of your queries are simple selects and some joins, then even switching from MSSQL to Oracle wouldn't be bad. -Original Message- From: Bryan Stevenson [mailto:[EMAIL PROTECTED] Sent: Monday, April 11, 2005 4:33 PM To: CF-Talk Subject: Re: Building Applications for multiple DB's Well I have a different take on this...but it really all comes down to what kind of SQL you've got. Obviously a switch from Oracle to MS SQL Server will have some re-writes involved (PL-SQL to T-SQL), but going from Access to MS SQL Server you should just be able to point at the new DB and fire it up. As I said...it does depend on what DB features you are usingso if you have a complex DB wuith loads of SPs etc...the yes...it could be toughif it's a simple DB then the swicth could be very painless ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:202288 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Building Applications for multiple DB's
Systems that work on several different DB's: Farcry CMS, Cartweaver, Speck CMS, PHP Nuke, OS Commerce, X-Cart, Xoops, Metadot. I'm sure there are tons more, but there's a little list for you. --Ferg -Original Message- From: jonese [mailto:[EMAIL PROTECTED] Sent: Monday, April 11, 2005 3:07 PM To: CF-Talk Subject: Building Applications for multiple DB's Anyone have any pointers or examples of CF or PHP application which have been constructed with the ability to work on multiple Databases? IE the application can easily be run on MSSQL, Access, MySQL etc and all the user has to do is changed a config variable or point to another DB file. I'm trying to do the leg work now for a future upgrade to our CMS. Right now it runs on MSSQL, but we want to make it so users can easily install it into mysql, etc. thanks in advance. jonese ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:202292 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Building Applications for multiple DB's
You can also look into using some ORM framework like Hibernate. On Apr 11, 2005 4:35 PM, Adrocknaphobia <[EMAIL PROTECTED]> wrote: > Make use of stored procedures only. That way your cfc code can remain > the same regardless of the database platform. > > -Adam > > On Apr 11, 2005 4:32 PM, Bryan Stevenson <[EMAIL PROTECTED]> wrote: > > Well I have a different take on this...but it really all comes down to what > > kind of SQL you've got. > > > > Obviously a switch from Oracle to MS SQL Server will have some re-writes > > involved (PL-SQL to T-SQL), but going from Access to MS SQL Server you > > should just be able to point at the new DB and fire it up. > > > > As I said...it does depend on what DB features you are usingso if you > > have a complex DB wuith loads of SPs etc...the yes...it could be > > toughif it's a simple DB then the swicth could be very painless > > > > Bryan Stevenson B.Comm. > > VP & Director of E-Commerce Development > > Electric Edge Systems Group Inc. > > phone: 250.480.0642 > > fax: 250.480.1264 > > cell: 250.920.8830 > > e-mail: [EMAIL PROTECTED] > > web: www.electricedgesystems.com > > > > > > > > ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:202297 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Building Applications for multiple DB's
I have done this as well and found that trying to maintain A copy for the 4 major DB platforms was a nightmare. So with Slight tweaking, I was able to maintain 1 copy of the application And provide scripts for each DB platform (Other than Oracle). I provided scripts for MS-SQL, MySQL and provided the Access DB. Some things for each one are: MS-Access and MS-SQL can have Default Date fields however, MySQL wont. All 3 of those platforms can have IDENTIFY (type) Fields that auto-increment. So if you have basic queries that Are not DB dependent, you can do it. Just remember when adding date fields, you add it via query And not rely on the DB to do it by default. -Original Message- From: Bryan Stevenson [mailto:[EMAIL PROTECTED] Sent: Monday, April 11, 2005 4:33 PM To: CF-Talk Subject: Re: Building Applications for multiple DB's Well I have a different take on this...but it really all comes down to what kind of SQL you've got. Obviously a switch from Oracle to MS SQL Server will have some re-writes involved (PL-SQL to T-SQL), but going from Access to MS SQL Server you should just be able to point at the new DB and fire it up. As I said...it does depend on what DB features you are usingso if you have a complex DB wuith loads of SPs etc...the yes...it could be toughif it's a simple DB then the swicth could be very painless Bryan Stevenson B.Comm. VP & Director of E-Commerce Development Electric Edge Systems Group Inc. phone: 250.480.0642 fax: 250.480.1264 cell: 250.920.8830 e-mail: [EMAIL PROTECTED] web: www.electricedgesystems.com ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:202308 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Building Applications for multiple DB's
> Some things for each one are: > MS-Access and MS-SQL can have Default Date fields however, > MySQL wont. All 3 of those platforms can have IDENTIFY > (type) > Fields that auto-increment. So if you have basic queries > that > Are not DB dependent, you can do it. I think you mean IDENTITY ... Although I avoid those myself because they can (ymmv) cause some really challenging problems later (they have for several companies I've worked for -- I've talked to other people who've never had a problem with them). Oracle also although it doesn't support anything like an IDENTITY column, does support sequences and triggers which can be used in combination to create the same effect as an identity column. s. isaac dealey 954.522.6080 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://macromedia.breezecentral.com/p49777853/ http://www.sys-con.com/author/?id=4806 http://www.fusiontap.com ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:202310 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Building Applications for multiple DB's
I don't know how your current application is designed, but I'm using a daoFactory and gatewayFactory with abstract methods and then I can invoke the correct daos and gateways via an application property. I'm only implementing one DB as the moment, but it won't be hard to add DB support in the feature. ...pjf ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:202324 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Building Applications for multiple DB's
>>Make use of stored procedures only. That way your cfc code can remain >>the same regardless of the database platform. well; except those which don't support stored procedures ;-/ -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:202330 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Building Applications for multiple DB's
For an example of this check out the Mach-ii.info sample application. (http://mach-ii.info/index.cfm?event=resources#code) It implements the Abstract Factory pattern as described here http://java.sun.com/blueprints/corej2eepatterns/Patterns/DataAccessObject.html Using the same architecture my bug tracking app, tracking-tools.com, supports MSSQL, MySQL, Oracle and Derby. hth, Phil >Your best bet is to encapsulate all your SQL into a collection of >DAOs, and have one collection for each target database. Then you have >a configuration parameter that says which DB you're using, and your >code then uses the appropriate collection of DAOs to do the data >access. > >That might seem like you'll need to write every query X times, where X >is the number of target databases. For some queries, that's true, but >for a lot of them, standard SQL will work. So create an abstract >version of each DAO that has general query implementations in them, >and then only override the ones you need to in your db-specific DAOs. >It's a little more complex to implement, but it'll make you job a lot >easier down the road. > >cheers, >barneyb > >On Apr 11, 2005 1:07 PM, jonese <[EMAIL PROTECTED]> wrote: > >-- >Barney Boisvert >[EMAIL PROTECTED] >360.319.6145 >http://www.barneyb.com/ > >Got Gmail? I have 50 invites. ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:202381 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Building Applications for multiple DB's
Except that the differences between databases go deeper than the SQL syntax they use. Oracle uses case sensitive string comparison wheras SQLServer doesn't (as i recall... haven't worked with it since 1998) - that will trip you up on simple things like making sure all user names are unique, etc. /t >-Original Message- >From: Steve Brownlee [mailto:[EMAIL PROTECTED] >Sent: Monday, April 11, 2005 10:36 PM >To: CF-Talk >Subject: RE: Building Applications for multiple DB's > >Yes, that's very true. My brain always seems to come from the >point of view of things that I work on. However, if 80% of >your queries are simple selects and some joins, then even >switching from MSSQL to Oracle wouldn't be bad. ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:202390 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Building Applications for multiple DB's
Ray Camden's BlogCFC supports a couple... I don't recall exactly which for sure. At least Access, MSSql, and MySQL. Incidentally, I've just released version 1 of SQLSurveyor... hehe... had to plug it... which is also intended to support multiple platforms. It's also, as everyone's noted, a daunting task, esp. considering that I'm dealing with metadata and not simply DB content. Oogly. Laterz! J On Apr 11, 2005 3:07 PM, jonese <[EMAIL PROTECTED]> wrote: > Anyone have any pointers or examples of CF or PHP application which have > been constructed with the ability to work on multiple Databases? IE the > application can easily be run on MSSQL, Access, MySQL etc and all the user > has to do is changed a config variable or point to another DB file. > I'm trying to do the leg work now for a future upgrade to our CMS. Right > now it runs on MSSQL, but we want to make it so users can easily install it > into mysql, etc. > thanks in advance. > jonese -- Continuum Media Group LLC Burnsville, MN 55337 http://www.web-relevant.com http://www.web-relevant.com/blogs/cfobjective ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:202394 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Building Applications for multiple DB's
++ Just don't plan on using MySQL4 or below. Pete On Apr 11, 2005 4:35 PM, Adrocknaphobia <[EMAIL PROTECTED]> wrote: > Make use of stored procedures only. That way your cfc code can remain > the same regardless of the database platform. > > -Adam > ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:202396 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Building Applications for multiple DB's
> Except that the differences between databases go deeper than > the SQL syntax they use. Oracle uses case sensitive string > comparison wheras SQLServer doesn't (as i recall... haven't > worked with it since 1998) - that will trip you up on simple > things like making sure all user names are unique, etc. Case-sensitivity depends on how the database server (or the individual database itself) is configured. SQL Server uses case-insensitive collation by default, but you can configure individual SQL Server databases to be case-sensitive, among other things. Generally, to avoid these sorts of differences, you write SQL that will work the same way in either case. For example, you can force case-insensitive comparisons using the LIKE operator. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:202401 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Building Applications for multiple DB's
>Generally, to avoid these sorts of differences, you write SQL
>that will work the same way in either case. For example, you
>can force case-insensitive comparisons using the LIKE operator.
LIKE is case sensitive in Oracle. If you hadn't done so already you
would have to go through (all) your SQL and force case insensitivity by
coding in the form
WHERE UPPER(dbfield) LIKE UPPER('%#searchvalue#%')
/t
~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:202409
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Building Applications for multiple DB's
my CMPro cms has supported Access, mySQL, SQL Server and Oracle for a few years now, and all code uses the same SQL. Doing the job isn't as bad as you might think, but you do have to make some allowances, and in some cases dumb-down your SQL so it will work on anything, perhaps moving some things out of the db and into your code (like the case-sensitivity issue for Oracle). The only truly separate SQL I have to write is the table creators in the install routine. The difference in the syntax is significant. Access uses memo fields, mySQL uses text or mediumtext, SQL Server uses ntext and Oracle uses CLOB. Everything is different, basically. In Oracle you can only have one long text field per table, so this may impact your application design (you can break this rule by writing an ALTER TABLE cfquery that will add the second CLOB field and it will work, however). Inserts are a small challenge. All platforms support numeric PK's, although Oracle does it through sequences. My system looks at a number. db type "4" = Oracle. So an insert would look like this: INSERT INTO myfile ( ID, myfield ) VALUES ( seq_myfile.nextval, ) Note the null statement. If your app allows feeding null values you will have to use this kind of null checker to keep Oracle from blowing up. Its probably a smart idea to do on general principles. Keep your cfsqltypes simple. Universal ones are cf_sql_varchar, cf_sql_longvarchar (long text fields), cf_sql_numeric and cf_sql_decimal. You will have to either move all of your date fields out of cfqueryparam (i.e. use raw sql) or switch to text fields and handle date logic in CF to preserve your use of cfqueryparam, as everything but Access understands cf_sql_timestamp for a date/time field. Access understands nothing. Then there's the concept of assigning a pk and querying back for the thing as you continue your processing. There is only one truly cross-platform way to do this other than writing custom SQL that fires conditionally. Create a UUID in memory, put it into a field on your insert and query back for it after the insert. Its an extra indexed field but you need no locks or cftransaction blocks to get gauranteed integrity. Also a UUID can be handy for other things down the road, like salt for a hash, so I don't consider the practice inefficient in the grand scheme. And little crap is going to pop up in your code. I had some fits with some list code that turned out to be Access feeding back numeric values to me with a decimal whether I liked it or not and whether the field was formatted for decimals or not (i.e. '12.0' instead of '12'.). I couldn't get it to stop so now I use fix() anywhere that might be an issue. Once you get past the initial hump it isn't so bad. Overall it can be fairly simple to write cross-platform apps but you'll have to unlearn some things. -- --mattRobertson-- Janitor, MSB Web Systems mysecretbase.com ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:202426 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Building Applications for multiple DB's
>> From: S. Isaac Dealey [mailto:[EMAIL PROTECTED] >> You haven't seen the onTap framework I take it. :P > It was only a matter of time :-) Can you blame me? :) s. isaac dealey 954.522.6080 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://macromedia.breezecentral.com/p49777853/ http://www.sys-con.com/author/?id=4806 http://www.fusiontap.com ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:202446 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Building Applications for multiple DB's
Check out Raymod Camdens cf blog app - http://www.camdenfamily.com/morpheus/blog/ It has support for MySQL, Access, I think MSSQL, and I added postgres (I don't know if that got merged though) - anyway it's an open source example of a multi db app you can play with. On Apr 11, 2005 1:07 PM, jonese <[EMAIL PROTECTED]> wrote: > Anyone have any pointers or examples of CF or PHP application which have > been constructed with the ability to work on multiple Databases? IE the > application can easily be run on MSSQL, Access, MySQL etc and all the user > has to do is changed a config variable or point to another DB file. > I'm trying to do the leg work now for a future upgrade to our CMS. Right > now it runs on MSSQL, but we want to make it so users can easily install it > into mysql, etc. > thanks in advance. > jonese > > ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:202453 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
