|
chrisredco
VP-CART Super User
282 Posts |
 Posted - July 23 2011 : 10:32:57
|
I've never run my site on anything bu Access databse. My database size is currently 100MB+ and I have concerns about reliability and stability.
I'm considering moving to MySQL, but having never used this type of database, my concern is since I'm not hosting my site (alentuis currently), if I do convert, do I somehow have a copy of MySQL on my local machine to be able to extract data, alter tables, etc like I now do with Access on my local machine.
I know we're talking a whole different language here to get info out of the databse, my having no MySQL experience, am I opening up Pandora's box as far as my learning curve here once I convert?
Redco Audio
www.redco.com |
|
|
devshb
Senior Member
United Kingdom
1904 Posts |
Posted - July 23 2011 : 16:15:39
|
There's usually 2 main ways that you can access a mysql database (ie aside from the actual site/cart accessing it through normal asp means using an ado connection etc)
1) MySQL Administrator
This is software installed on your pc (it's free to download), it's the equivalent of having Access installed on your pc, but instead of just opening a file, you specify the ip/userid/pwd of the database and it connects to it remotely (ie from your pc to the server), and it gives you a gui-style front-end so you can see the tables/data, run queries, and export them etc:
http://dev.mysql.com/downloads/gui-tools/5.0.html
However, some hosts don't allow remote connections (ie from your pc to the database/server), so sometimes you can't use it, if that's the case you'd need option 2 below.
2) Most hosts that have MySQL installed will have a web-based front-end for the database, they'll give you the url for the mysql control panel; it's like a web/browser-based version of option 1.
SQLServer has the same kind of options, ie you can download sqlserver management studio (which is free) as a pc-based front-end, or the host would supply a browser-based alternative to achieve the same kind of thing.
Not easily being able to take copies of the database so you can play around without effecting the live site (eg to setup test areas when running a vpasp upgrade) is the only downside of switching to mysql or sqlserver, but if needs be then usually the host will allow you to create a test copy for a short time without much (if any) extra charges.
You shouldn't need to make any programmatic/asp changes on the vpasp side of things, you'd just change your shop$config.asp files to point to the new database/type (if you're using vpaspv7 don't forget to change the admin shop$config.asp file as well as the main/customer side shop$config.asp file)
If you're using our addons then you'd just need to change the byz_system_config.asp file to change the database type from "ACCESS" to "MYSQL" or "SQLSERVER" as appropriate.
Whether it's MySQL or SQLServer, you don't need mysql or sqlserver itself installed on your pc in order to view the data etc. For mysql you'd just download the free administrator, and for sqlserver you'd download sqlserver management studio (or use the browser-based alternatives if the host supply one).
eg you'd never need to install full-blown sqlserver on your pc just in order to access a sqlserver database from your pc.
If you've got some customisations which are database-type dependent then you'd need to change those.
The sql query syntax between Access, MySQL, and SQLServer is mostly the same, but there are some differences (which vpasp and our addons take into account and deal with automatically).
eg access uses "UCASE", whereas mysql/sqlserver uses "UPPER" for forcing something to upper case in a query, but then again all databases by default will ignore case when you do "where columna=columnb" so it's rare that you even need to use upper/lower on the sql query side of things.
The main differences are how it deals with dates, you'd definitely want to test things out on that front once you've got a mysql instance in place and being tested (eg to make sure the day and month values aren't getting incorrectly transposed; ideally test things for a date where the day can also be a month (eg 7/8)
Simon Barnaby
Developer
[email protected]
www.BigYellowZone.com
www.BigYellowKey.com
Follow us on Twitter: http://twitter.com/bigyellowzone
Web Design, Online Marketing and VPASP addons |
Edited by - devshb on July 23 2011 16:35:49 |
 |
|
All Forums
Changing from Access to My SQL
New Topic
Printer Friendly
Topic 
TrustGuard - PCI Security Scanner
