sql2k vs mySQL vs postgresql (for win2k)
How do you folks handle the access relationships when changing to mySQL? From the manual it looks like the equivalent functionality is covered using foreign key constraints. Is this right? The foreign key constraints require that both sides of the relationship have the foreign key be the first record of the table. How do you do this when one table has relationships with multiple tables? Only one foreign key can be the first, so it sounds like each table can only have one foreign key constraint. Is this right? Thanks for your help. ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: sql2k vs mySQL vs postgresql (for win2k)
MySQL does NOT enforce foriegn key relationships, at least with MyISAM tables, which are the default. Other tables types might be different. Relationships are used by Access to make the query constructor work more easily, but are pretty irrelevant to actual use of the DB. They do provide an index on that column, which is important, so make sure you do that manually with MySQL: create table person ( personID int not null auto_increment primary key, name varchar(50), ... ); create table car ( carID int not null auto_increment primary key, personID int not null, make varchar(50), ... foreign key fk_person references person(personID), key k_personID (personID) ); The foreign key line in the 'car' table will be ignored by MySQL. The line below it will construct an index on the personID column to facilitate fast searching. Note that I didn't use the first column as the foreign key. The biggest thing that FKs provide (in my experience) is the ability to easily do cascading deletes (deleting a person automaticlaly deletes all his/her cars). That type of thing will have to be done manually with MySQL, while SQL Server (and perhaps Access) can be configured to do that for you. barneyb -Original Message- From: dan martin [mailto:[EMAIL PROTECTED] Sent: Monday, March 17, 2003 8:50 AM To: CF-Talk Subject: sql2k vs mySQL vs postgresql (for win2k) How do you folks handle the access relationships when changing to mySQL? From the manual it looks like the equivalent functionality is covered using foreign key constraints. Is this right? The foreign key constraints require that both sides of the relationship have the foreign key be the first record of the table. How do you do this when one table has relationships with multiple tables? Only one foreign key can be the first, so it sounds like each table can only have one foreign key constraint. Is this right? Thanks for your help. ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
sql2k vs mySQL vs postgresql (for win2k)
Thanks guys. This is great information. I am excited to try it out. ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: sql2k vs mySQL vs postgresql (for win2k)
Dan, I'll add a few comments as well, focusing on MySQL. I've transitioned a number of clients from Access to MySQL, and more than one from MS SQL Server to MySQL for parts of their infrastructure. MySQL is fast, especially for reads (e.g. decision support systems, datawarehousing), but it's missing a ton of features higher-end dbs have. Most of those features are getting addressed in 4.1, but some (stored procs, views) have to wait until version 5.0 of MySQL or later. Of course Access is missing a ton of features too, so no big loss in moving from one to the other. I've used CF4.5 and 5 extensively w/ MySQL -- no production experience with it on CFMX yet. I've used the free version of AccessDUMP, the scripts at the MySQL site, and even Microsoft's DTS in SQL Server to convert Access databases to MySQL. I've had the most problems converting boolean fields (0/-1 in Access, tinyint in MySQL) and some poorly formatted date fields when using the automatic tools, but nothing too challenging to overcome. But only trivial databases have converted perfectly. The simply aren't a lot of SQL functions in Access, so that helps the transition, though Access does support subselects and MySQL won't for a couple more weeks (4.1 is likely going to be released at the developer's conference). You can often rewrite subselects as self- or outer-joins though, so there's always alternatives. If you use UNIONs, you're stuck since MySQL doesn't support them until 5.0, though as I write this, I can't recall if Access supports them either... MySQL also has a LOT of date data types when compared to Access, so you might want to adjust some of the date data types so your application can take advantage of them. I've generally found that my conversion process involves a test conversion, some data comparison of key fields (particularly boolean and date datatypes), and then a little scripting of the Access database to get the data in a better format for the conversion (DTS is pretty adept at this sort of thing -- nothing like using SQL Server tools to convert Access to MySQL!) It's also worth a sweep through the SQL code, since Access is pretty forgiving about date comparisons and MySQL is stricter -- I've found code that was pretty abysmal to start with that worked in Access but broke in MySQL. Forced the developers to fix code that was crappy to start with. As far as macroscopic issues go, MS-SQL has many more tuning options, extensive replication options, stronger supporting services (scheduling, etc), clustering, etc. Most folks don't need any of that and the crucial services (e.g. scheduling) can be provided through other tools (Windows scheduling, PHP, CF itself, etc) or have some basic support in MySQL. MySQL is IMHO as fast or faster for most folks in this regime (moving from Access to something else) I really like it for DSS, less thrilled for OLTP apps if usage is *truly* large, though plenty of folks do use MySQL that way. And of course you can't beat the price! Regards, John Paul Ashenfelter CTO/Transitionpoint [EMAIL PROTECTED] - Original Message - From: dan martin [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Sunday, March 09, 2003 9:31 PM Subject: sql2k vs mySQL vs postgresql (for win2k) Hi, I am currently using ms access with a growing 20mb database that I need to upgrade. My initial plan was to migrate over to SQL 2000 because that seems to be the most painless (except for price). I am wondering if it would make more sense to migrate to an open source db: mySQL or postgresql. I am running win2k with cf5 and would like to keep the db on the same server. Does anyone have experience or comparison information between the 3 choices running on win2k? How much work is it to port access to mysql or postgresql? For sql2k the upgrade tool does most of the work for you. Are there obvious benefits performance or feature wise between the choices? Any info would be appreciated. - Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, and more ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: sql2k vs mySQL vs postgresql (for win2k)
Howdy Dan, Sunday, March 9, 2003, 8:31:54 PM, dan martin wrote: ... I am wondering if it would make more sense to migrate to an open source db: mySQL or postgresql. I am running win2k with cf5 and would like to keep the db on the same server. One thing you didn't mention was your environment - will this be only for development, or for production, or both? If production, will this be on your own server(s), or third-party shared hosting? Does anyone have experience or comparison information between the 3 choices running on win2k? How much work is it to port access to mysql or postgresql? For sql2k the upgrade tool does most of the work for you. Are there obvious benefits performance or feature wise between the choices? I'm a newbie myself to MySQL (v. 3.23.54 on Win2k) and am still learning my way around. With MyODBC I've found it's pretty easy to get Access to talk to MySQL, so it should be fairly easy to import your Access db to MySQL (I've pulled MySQL tables into Access, just as a test, but haven't gone the other way around...yet). There are several free front-end tools that I've been playing with that you might find worth checking out. They have varying capacities for export/import: - DBTools/DBManager: http://www.dbtools.com.br/EN/ - MySQL Control Center: http://www.mysql.com/products/mysqlcc/index.html - MySQL-Front: http://mysqlfront.venturemedia.de/index.php?act=STf=1t=2s=1a695d95d6c1514352366168370a801d (this tool is no longer being developed, but it still works well and can be downloaded at this URL) - SQLYog: http://www.webyog.com/sqlyog/index.html - eSQLManager: http://www.errorsoft.nl/index.jsp ...and here's a commercial tool (I haven't used this, but you might be interested anyway): - Navicat: http://www.mysqlstudio.com/index.php MySQL should be releasing version 4.1 real soon (no hard date announced on this yet, but what I've picked up from the MySql list, it would appear to be coming very soon). Version 4.1 will support sub-selects. Here's an article covering it: http://www.mysql.com/press/release_2003_05.html Might be worth the wait. Just for grins, here's a few more MySQL related links that may be of interest to you: - PHP MySQL Tips and Tutorials: http://www.sitepoint.com/subcat/98 - MySQL articles on Database Journal: http://www.databasejournal.com/features/mysql/ - Oh, my--MySQL!: http://builder.com.com/article.jhtml?id=u00320020523dol01.htmfromtm=e602 - MySQL articles: http://www.kitebird.com/articles/ - Migrating from Microsoft Access to MySQL: http://www.kitebird.com/articles/access-migrate.html - A collection of Articles about MySQL: http://www.mysql.com/articles/index.html - and of course, the big kahuna, MySQL Reference Manual: http://www.mysql.com/doc/en/index.html A couple more comparisons: - PostgreSQL vs. MySQL: http://www.webtechniques.com/archives/2001/09/jepson/ - MySQL vs. PostgreSQL: http://builder.com.com/article.jhtml?id=u00320020624gcn01.htmfromtm=e602 Hope you get something useful out of all the above. -- Chris Montgomery Airtight Web Services http://www.airtightweb.com Web Development, Web Project Management, Software Sales 210-490-2415 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: sql2k vs mySQL vs postgresql (for win2k)
Answer to the original question: yes it makes sense to ditch Access. I moved from Access to mySQL maybe two years ago and never looked back. The only compatibility issues I faced related to problems with cfinsert and cfupdate, which I used extensively at the time. I have since reformed my slacker ways and write real SQL. When I write stuff that has to work on both mySQL and Access, most of what I do is fix() numeric variable output. Access has an annoying habit of outputting a numeric value of 123 as 123.0 which can be trouble under some circumstances. Not much else that I can think of offhand... In other words, its Access that's the problem child in the equation, so if you're used to working with it you should be nothing but happy with mySQL, on balance. FYI I have a server running CF 4.5 and mySQL 4.0.x with about 40 small/medium, heavily db-driven CF domains on it. mySQL eats around 15mb of RAM, never coughs or hiccups. That wasn't the case when I was using Access on the same box. Got another one running CFMX and mysql 4.0.x and it runs just dandy as well. Matt Robertson [EMAIL PROTECTED] MSB Designs, Inc. http://mysecretbase.com - - - - - - - - - - - - - - Site Design and ColdFusion Developer Tools -Original Message- From: Chris Montgomery [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 12, 2003 8:21 PM To: CF-Talk Subject: Re: sql2k vs mySQL vs postgresql (for win2k) Howdy Dan, Sunday, March 9, 2003, 8:31:54 PM, dan martin wrote: ... I am wondering if it would make more sense to migrate to an open source db: mySQL or postgresql. I am running win2k with cf5 and would like to keep the db on the same server. One thing you didn't mention was your environment - will this be only for development, or for production, or both? If production, will this be on your own server(s), or third-party shared hosting? Does anyone have experience or comparison information between the 3 choices running on win2k? How much work is it to port access to mysql or postgresql? For sql2k the upgrade tool does most of the work for you. Are there obvious benefits performance or feature wise between the choices? I'm a newbie myself to MySQL (v. 3.23.54 on Win2k) and am still learning my way around. With MyODBC I've found it's pretty easy to get Access to talk to MySQL, so it should be fairly easy to import your Access db to MySQL (I've pulled MySQL tables into Access, just as a test, but haven't gone the other way around...yet). There are several free front-end tools that I've been playing with that you might find worth checking out. They have varying capacities for export/import: - DBTools/DBManager: http://www.dbtools.com.br/EN/ - MySQL Control Center: http://www.mysql.com/products/mysqlcc/index.html - MySQL-Front: http://mysqlfront.venturemedia.de/index.php?act=STf=1t=2s=1a695d95d6c 1514352366168370a801d (this tool is no longer being developed, but it still works well and can be downloaded at this URL) - SQLYog: http://www.webyog.com/sqlyog/index.html - eSQLManager: http://www.errorsoft.nl/index.jsp ...and here's a commercial tool (I haven't used this, but you might be interested anyway): - Navicat: http://www.mysqlstudio.com/index.php MySQL should be releasing version 4.1 real soon (no hard date announced on this yet, but what I've picked up from the MySql list, it would appear to be coming very soon). Version 4.1 will support sub-selects. Here's an article covering it: http://www.mysql.com/press/release_2003_05.html Might be worth the wait. Just for grins, here's a few more MySQL related links that may be of interest to you: - PHP MySQL Tips and Tutorials: http://www.sitepoint.com/subcat/98 - MySQL articles on Database Journal: http://www.databasejournal.com/features/mysql/ - Oh, my--MySQL!: http://builder.com.com/article.jhtml?id=u00320020523dol01.htmfromtm=e60 2 - MySQL articles: http://www.kitebird.com/articles/ - Migrating from Microsoft Access to MySQL: http://www.kitebird.com/articles/access-migrate.html - A collection of Articles about MySQL: http://www.mysql.com/articles/index.html - and of course, the big kahuna, MySQL Reference Manual: http://www.mysql.com/doc/en/index.html A couple more comparisons: - PostgreSQL vs. MySQL: http://www.webtechniques.com/archives/2001/09/jepson/ - MySQL vs. PostgreSQL: http://builder.com.com/article.jhtml?id=u00320020624gcn01.htmfromtm=e60 2 Hope you get something useful out of all the above. -- Chris Montgomery Airtight Web Services http://www.airtightweb.com Web Development, Web Project Management, Software Sales 210-490-2415 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could
Re: sql2k vs mySQL vs postgresql (for win2k)
dan martin wrote: I am currently using ms access with a growing 20mb database that I need to upgrade. My initial plan was to migrate over to SQL 2000 because that seems to be the most painless (except for price). I am wondering if it would make more sense to migrate to an open source db: mySQL or postgresql. I am running win2k with cf5 and would like to keep the db on the same server. Does anyone have experience or comparison information between the 3 choices running on win2k? How much work is it to port access to mysql or postgresql? Access to PostgreSQL is very little work. Make sure you read the chapter on datatypes and you are set to go. After you have converted your data you need to look over your queries. PostgreSQL supports more join/subquery constructs as Access, so that is no problem. But you might need to rewrite some of your queries because functions like Year() and DatePart() are not present in PostgreSQL, PostgreSQL uses the standard Extract() for that (writing your own Year() and DatePart() functions is trivial). With MySQL data conversion is similar, but rewriting your queries is a little bit the other way around. Subqueries are not supported so you would need to rewrite them, but there are more functions that behave the same betweeen Access and MySQL. But don't forget that query syntax between Access and MS SQL Server requires some changes too. Are there obvious benefits performance or feature wise between the choices? Featurewise MS SQL Server has advantages over PostgreSQL, which in turn has many advantages over MySQL. I don't think performance matters much for such a small database, except in some rare scenario's. For example, if you disable connection pooling, PostgreSQL will take a much heavier performance hit (it uses one process for each connection). Or if you have some extremely long running requests MS SQL Server will only have limited CALs left to serve other requests. The biggest disadvantage of PostgreSQL, and the one reason I not outright recommending it, is that all the native versions for Windows are still in Beta. You can run a Cygwin version, but it has implications for performance and there might be compatibilities issues if you run other Cygwin applications. I am happily running the beta for a few months now, but that is development only. BTW, one database you didn't mention was Firebird. I hear it is almost as capable as PostgreSQL and it does have a native Windows version. Unfortunately, I have spent too little time working with it to say more. Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: sql2k vs mySQL vs postgresql (for win2k)
Hello! mysql has a very good performance but if you need certain features like views or subselect this database is out of the game. Postgresql fullfills almost all standard criterias for a real database engine but it is a little bit more complex. Running Postgresql on Windows isn't that funny. Some more hints: http://phd.pp.ru/Software/SQL/PostgreSQL-vs-MySQL.html http://www.google.com/search?q=mysql+versus+postgresql Best regards, Peter Orginale Nachricht Von: dan martin [EMAIL PROTECTED] Betreff: sql2k vs mySQL vs postgresql (for win2k) Datum/Zeit: Montag, 10. März 2003 03:39:36 Hi, I am currently using ms access with a growing 20mb database that I need to upgrade. My initial plan was to migrate over to SQL 2000 because that seems to be the most painless (except for price). I am wondering if it would make more sense to migrate to an open source db: mySQL or postgresql. I am running win2k with cf5 and would like to keep the db on the same server. Does anyone have experience or comparison information between the 3 choices running on win2k? How much work is it to port access to mysql or postgresql? For sql2k the upgrade tool does most of the work for you. Are there obvious benefits performance or feature wise between the choices? Any info would be appreciated. ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: sql2k vs mySQL vs postgresql (for win2k)
BTW, one database you didn't mention was Firebird. I hear it is almost as capable as PostgreSQL and it does have a native Windows version. firebird's not quite as standard as postgreSQL (it offers many ANSI SQL-92 features), has some syntax differences from sql server/access. it has some nice features like UDFs, identity-like values before INSERTs, 64 bit IO, both odbc jdbc drivers (and for those you into this sort of thing there's a .NET provider in beta now), and of course transactions, sub queries, stored procedures, etc.. its got a fairly active user community. firebird's unicode support is kind of iffy which to me is important. Unfortunately, I have spent too little time working with it to say more. i've played around with it a bit but never in production. ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: sql2k vs mySQL vs postgresql (for win2k)
Peter Mayer wrote: Hello! mysql has a very good performance but if you need certain features like views or subselect this database is out of the game. Postgresql fullfills almost all standard criterias for a real database engine but it is a little bit more complex. Running Postgresql on Windows isn't that funny. Some more hints: http://phd.pp.ru/Software/SQL/PostgreSQL-vs-MySQL.html Last modified: 14 May 2001. And even then it claimed to be outdated already. http://www.geocities.com/mailsoftware42/db/dbs.html is more up to date. Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
sql2k vs mySQL vs postgresql (for win2k)
Thanks very much for the information. It looks like it is definitely worth more investigation. Both alternatives to sql2k look very promising for my purposes. ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
sql2k vs mySQL vs postgresql (for win2k)
Hi, I am currently using ms access with a growing 20mb database that I need to upgrade. My initial plan was to migrate over to SQL 2000 because that seems to be the most painless (except for price). I am wondering if it would make more sense to migrate to an open source db: mySQL or postgresql. I am running win2k with cf5 and would like to keep the db on the same server. Does anyone have experience or comparison information between the 3 choices running on win2k? How much work is it to port access to mysql or postgresql? For sql2k the upgrade tool does most of the work for you. Are there obvious benefits performance or feature wise between the choices? Any info would be appreciated. - Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, and more ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
