I am in the process of converting the code to run in a mySQL database environment - actually, the mySQL is on a UNIX box and the asp code is on a separate domain, running on an NT box.
I have managed to change the connection to the database, the format of the dates (different in mySQL), changing the "Select top" to "limit", and the differences in select count(*). However, I am struggling with a final change - mySQL does not support nested select statements and the code is full of them. Once I can figure out how to change one of them, I can do them all. Here is an example of one of one of the nested select statements:
strSql = "SELECT MAX(VisitorCount) FROM (SELECT LogYear, LogMonth, LogDay, COUNT(pl_ipaddress) As VisitorCount FROM (SELECT DISTINCT
YEAR(pl_datetime) AS LogYear, MONTH(pl_datetime) AS LogMonth,
DAY(pl_datetime) AS LogDay, pl_ipaddress FROM " & strInstance & "PageLog WHERE pl_datetime BETWEEN " & FormatDatabaseDate(datReportStart & "
00:00:00") " AND " & FormatDatabaseDate(datReportEnd & " 23:59:59") & "
) DT_PageLog " "GROUP BY LogYear, LogMonth, LogDay)"
Any suggestions on how to convert these?
I will gladly send the converted class.asp to you when it is up and running.
mySQL
Good day,
Did you check on the mySQL official site/forums to see if people where having a similar situation with other applications ?
SQL statements, are varying in syntax from one DBMS to another, and may in some cases have similarities.
I personally don't know the SQL syntax rules for mySQL, however based on what I read about it on few forums, it is a tad limited, specially when dealing sub-queries and/or views.
I must admit, this is an interesting topic, and even though I'm not using mySQL, I'd be interested to know what can be done about the situation you're pointing.
You can consider to post on some web authoring programs forums, such as Adobe for instance, since I know for a fact they're quite a few people overthere using PHP and mySQL, or ASP and mySQL like you do.
Regards.
mySQL
I was able to complete this conversion - I now have Metatraffic running with a MySQL back-end. I was going to send it to Chad, but was not sure about your e-mail address.
Also, any luck on being able to archive the data? I am thinking about writing a script to archive the data every 30 days, but want to know if anyone else has made progress.
mySQL
Hi Andy,
I'd be interested in looking at your modifications to make MetaTraffic work with mySql. You can e-mail me at:
chad at metasun dot com
I don't put my e-mail anywhere because of e-mail harvesters / spam reasons. :)
Regards,
~Chad