18 Sep: How to modify this site to use a MySQL database

Stuart:

"OK. Here's a brief synopsis & code snippets of what I did - (WIN32 version for simplicity - W2K,W2KPro,ME,98,95(?), but Linux version works, just takes a bit longer + two boxes required)

1) Software Required -

a) MySQL - latest version from http://www.mysql.com/downloads
b) MySQLODBC (reqd for Access to MySQL) - http://www.mysql.com/downloads
c) MyOLEDB - http://www.mysql.com/downloads (should be MySQLProv.2.5)
d) WinMySQLAdmin - http://www.mysql.com/downloads
e) Access To MySQL - http://www.mysql.com/downloads/win32/myaccess2000_1_4.zip

2) Process -

a) Install all above & test connectivity from Access (2000 or 97) via AccesstoMysql (fairly straightforward to do) via tools, addins, MyAccess (needs DSN pointing to MySQL core database - similar(?!) to master in MSSQL)

b) Create new database in MySQL e.g. CYADB via WinMySQLAdmin easiest

c) Upsize the DB.mdb from your site code to MySQL-CYADB Database (including the data) - note - only Primary Keys are given an index - need to manually add any other indexes required.

d) Create a Data Source (easiest way) called "CYA" see below

3) Site Code Changes -

a) Config.ASP - could add new var eg sMySQL (set to "MySQLProv.2.5"). Didn't mess too much with your code & variables - just cheated & set string complete - leave you to mess with!

Whole connection string needs to add up to -

var sConnectionString = 'Provider=MySQLProv.2.5; Data Source=CYA; UID=XXXX; PWD=YYYYY';

or can use Server= , Database= depends how you want to show it.

b) Got some other errors in some functions in different scripts (can't remember exactly which ones) - strangely just cosmetic like a carriage return in a function - that sort of thing.

4) Obviously security needs to come into the frame here (like MS SQL) - UID & PWD need to be a user that only has enough rights in MySQL in this database - quite straightforward to do etc etc.

As I said before - hadn't tested all the functionality, but fundamentally this works.

Would suggest if you consider extending this support into your code base that you/we script the database creation etc & simplify the process as much as poss - i.e. reduce support.

As a supporter of MySQL vs MS SQL (I use both) would be nice to see some dedicated areas on many web sites to MySQL and ASP as its a great product & not too complex. Gives the additional upsizing dimension at far lower costs & not quite as mind-blowing to the Access Database people out there as MSSQL.

From what I have seen there is very little out there except the odd article or two.

Have fun - need anything else give us a shout."

Me:

WOW! Thanks Stuart.