MySQL: Using with ASP (after a long stint with MS SQL)

I've started moving tables from MS SQL to MySQL. I began the conversion process with the search from Benchmarks On-Line which went over without a hitch. That was to be expected, though, because that db access is read only ... no inserts/updates necessary. The only area I had to pay special attention to was the cursor type/location and the lock type. I updated these as necessary to best work with MySQL, especially considering the method the data was being used (client forward-only cursor, read-only lock).

Today I worked on moving over the survey from Blueprints On-Line. If I were going to run into any problems they would be pretty evident on this page. The table involved uses text (TEXT, VARCHAR), number (TINYINT, BIGINT), and date (DATETIME) fields. There is both a read and an insert on the table. The survey is unused, so I can test it without worrying about affecting the user experience too much.

I immediatly ran into a problem relating to permissions. For some reason MySQL kept telling me I didn't have access to the table even after I set up permissions. After talking to DP, though, I had a thought and checked out the DSN. I was using the same DSN that I used for the Benchmarks conversion (and plan to do the same for all scripts on the site). But the Benchmarks table is in a completely different database from the Blueprint table. The error message, while correct, was a bit misleading ... the table in question didn't exist in the database being accessed. The permission message was correct, however, in that I hadn't given the user permission to access the table specified. Since I didn't have permission to access the table in question MySQL didn't even bother to check if it existed. Sneaky. I've updated the DSN to have no default table and now specify my connection string as DSN=dnsname;Database=databasename;.

With this problem fixed I tried out the data read portion of the script. I immediately ran into a problem with some code that uses the data to calculate averages. It appears that in order to use the recordset value directly (i.e. not assigning it to a variable first) you have to first convert it to the appropriate type in VBScript. When using MS SQL I didn't run into this problem ... perhaps because MS SQL reports the data type assigned by the database. MySQL, however, just reports the type as field. I added a CInt() whenever the database values were used in a calculation.

The read was working correctly so I decided to try out the data insert. I ran into yet another problem, this one more serious than the first. The method I use to do the insert in the survey script is to create a recordset object, use the AddNew() function to create a new record, update the columns for the record, then use the Update() function to update the database. While updating the fields I was running into this error: "Multiple-step operation generated errors. Check each status value." I'm not sure what the reason for the error is, but I did find a solution on the MySQL forums in the thread multiple-step operation generated errors. That would be to turn on the "safe" option in MyODBC. Seems to work, but since I don't know the source of the error (or what the safe option does) it's conceivable that this solution doesn't actually solve the problem and that I'll run into further issues later on.

With these problems solved things seem to be running smoothly. I'll have to do some more research on the multi-step error and the safe option of MyODBC. In the meantime I'll start working on the other scripts.

I've already thought about one issue I might need to address at some point ... I currently have no development database. Not too happy about that.


Update 2006-08-10:
I've completed conversion of scripts from MS SQL to MySQL. In the process I found some more incompatibilities that need to be dealt with when converting.

First, MySQL does not allow the assignment of non-static values as column defaults (minus a few exceptions). The value must either be a constant or a literal value. Because of this requirement I was running into problems with the DATETIME fields I had set up because the database could not calculate the date when a new field was inserted. This issue led me to one of the exceptions ... TIMESTAMP fields can be calculated using the CURRENT_TIMESTAMP function. It looks like from now on I'll be using the TIMESTAMP data type rather than the DATETIME data type. For more interesting aspects of this data type check out TIMESTAMP Properties as of MySQL 4.1.

Second, some scripts were taking the result from a SELECT COUNT() statement and assigning it to a variable. MS SQL ODBC plays very nicely with ASP/VBScript and the variable type was automatically recognized as numeric. Since VBScript has some basic requirements with regards to using variables this made things very easy.

MyODBC does not play as nicely, however. The type returned using the same SQL statement is not recognized by VBScript. Applying a CInt() to these values corrects any type mismatch problems.

Yet another best practice that's not followed regularly: explicitly define a variable's type.

Third, I'm running into a problem where a date field with a null value causes the the recordset object to error out when trying to open the conneciton. The error message is "Data provider or other service returned an E_FAIL status." Checking for a null value in the SQL statement using the IFNULL() function seems provides somewhat of a solution to the problem, except for two issues: 1) the SQL statements involved have to be rewritten, and 2) at least one row is always returned, even if there are no matching rows in the table. The first issue isn't too much of a big deal, but the second one is a show-stopper. I'll have to do some more research.


Update to the the second issue mentioned above ... I'm running into more widespread problems with numeric types returned by MySQL. Even a regular INT data type seems to be having problems. If I have time I'll do some more research on this one as it'll no doubt be a repeating problem.