Using MyODBC with MySQL hosted on Namesco
CIW Course in a Nutshell
Connection issues with myODBC
I may be particularly dense, but I don't believe so. I have spent weeks trying to configure MyODBC to connect to a MySQL Server hosted by Names.co.uk. This was not aided by the fact that Names.co do not provide support for MySQL connection issues.
I have pored through the mySQL reference documents, scoured the Web and spoken to database administrators without much luck. The only indicators I could get were that DNS plays an important role and it is also necessary to ensure the correct version of MyODBC to work with the chosen version of MySQL. The confusion was compounded by the fact that MySQL Administrator would connect without ant trouble, using the same credentials.
Just the other day I discovered that I did indeed have a DNS issue as I was getting excessive delays logging on to the my Windows 2003 domain from a Windows XP client. It was necessary for me to set DNS on the clients to point to my domain controller first for domain name resolution. This solved the log on problem, so now I was hopeful that the MyODBC issue was also sorted. No luck! I still get a user validation error when trying to connect.
Next step was to check versions. At the time of writing names.co are using MySQL V4.0.21 and I was using MyODBC V3.51.10 so I checked on the MySQL website to see if there was a later client. I found V3.51.11-2 which I downloaded and installed. Hey Presto, we are in business! I am also using MySQL Administrator V1.0.16 as a convenient way to create the SQL tables.
So far so good, but all was not quite finished. My development tool of choice is Microsoft's Visual Basic 6.0 so now I need to determine the connection string required, which turned out to be quite straightforward. I did get an error when trying to open a recordset, but this was quickly resolved after a quick consultation with Google's SERPs. The recordset used with MyODBC must use adUseClient as the CursorLocation property as MyODBC does not support Server Cursors.
So the code I used is:
Dim SQLString As String
Set cnMain = New ADODB.Connection
SQLString = "ODBC;Driver={MySQL ODBC 3.51}; DSN=Test"
cnMain.Open SQLString
Dim rsTemp As ADODB.Recordset
Dim SQLString As String, Temp As String
Set rsTemp = New ADODB.Recordset
rsTemp.CursorLocation = adUseClient
SQLString = "SELECT * FROM Test1"
rsTemp.Open SQLString, cnMain, adOpenKeyset, adLockPessimistic, adCmdText
This now, as far as I have tested it, allows me to read and write to the web hosted MySQL database.
Namesco Specific Issues
To access any of Namesco's MySQL databases remotely, you must have a fixed IP to connect from, and you must request technical support to add this IP to their firewall white list. This must be done for each database you wish to access.
Namesco run two database servers: MySQL1 and MySQL2 strangely enough. MySQL1 runs MySQL V4 while MySQL2 runs MySQL V5. I was unable to connect to MySQL2.
Furthermore, if you have more than one hosted domain, you can use only one MySQL database from all of them. My initial expectation was that each hosted domain would need it's own database, but this does not appear to be the case.