can I use asp and ODBC driver to connect to MySql database

MAZER · #1 (**permalink**) 03-25-06, 06:32

I'm totally new to programming and could really do with some help, I just migrated my access db to mysql and I'm trying to figure out how change my connections and code for mysql, I was told that I would have to use php, but I also read that it can be done with asp using an ODBC driver which I have installed on my server. If I use the ODBC driver would I be able use the same SQL that I used to connect to the access DB? what other issues would there be?

healdem · #2 (**permalink**) 03-25-06, 07:45

although I have never used MySQL in ASP I can see no reason why you can't use it.

I'm not sure that you will neccesarily be able to use the same SQL as you used in JET - there are differences bewteen all implementations of SQL, JET is probably more quirky than most.

the main area you are liable to hit problems is in string matching, wildcards and string delimiters.

just switching to MySQL (or any server is going to give a significant performance enhancement, to get the best you need to optimise your SQL to meet the servers requirements. With the introduction of V5 MySQL (and its support for stroed procedures, functions and triggers) there is little to distinguish it from db's like SQL server). If you have Version 5 of mYSQL then you should consider shifting mopst if not all of your SQL into the db as stored procs/functions.

HTH

MAZER · #3 (**permalink**) 03-26-06, 15:41

Thanks for the reply healdem, I had a few problems with it but I eventually got it, only needed to make minor changes to the code. I'm really jumping in at the deep end here because I know very little about asp, sql, and databases, so I'm learning as I go.
I tried around 7 different odbc connections, this is the one that worked (just in case someone else is trying to do it)

Code:

<%  @LANGUAGE = VBScript %>
<% Option Explicit %>
<% 
Dim sConnection, objConn , objRS 

'sConnection = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;PORT=3306;DATABASE=DB NAME; USER=USER ID;PASSWORD=USER PASSWORD;OPTION=3;"); 


    sConnection = "Driver={MySQL ODBC 3.51 Driver};" & _ 
        "Server=mysql.hosting365.ie;" & _ 
        "Database=DB NAME;" & _ 
        "Uid=USER ID;" & _ 
        "Pwd=USER PASSWORD;" 


Set objConn = Server.CreateObject("ADODB.Connection") 

objConn.Open(sConnection) 

Set objRS = objConn.Execute("SELECT ProductName, Grade FROM comics")


While Not objRS.EOF
Response.Write objRS.Fields("ProductName") & ", " & objRS.Fields("Grade") & "<br>"
'Response.Write & " "
objRS.MoveNext
Wend 

objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
%>

I really got stuck on the ODBC connection, I kept on putting the address of my server in

Code:

'sConnection = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=mysql.hosting365.ie;PORT=3306;DATABASE=DB NAME; USER=USER ID;PASSWORD=USER PASSWORD;OPTION=3;");

but apparently thats how you do it if your accessing a database remotely,
since my database was on the same server as my site SERVER=localhost; is the correct one.

I would like someone else to confirm this, I'm very new mysql and odbc so I could be wrong

jfulton · #4 (**permalink**) 03-26-06, 23:28

Just as a heads up, using asp and mysql can be kind of quirky. This link does a decent job explaining and solving a huge problem I ran into a while ago using COUNT(). You also might want to check out the mysql docs on the subject.

humber · #5 (**permalink**) 04-24-06, 08:15

well, but, TO DO THIS, must have a drive odbc in pc.
anyone has it?

Quote:

Originally Posted by MAZER

Thanks for the reply healdem, I had a few problems with it but I eventually got it, only needed to make minor changes to the code. I'm really jumping in at the deep end here because I know very little about asp, sql, and databases, so I'm learning as I go.
I tried around 7 different odbc connections, this is the one that worked (just in case someone else is trying to do it)

Code:

<%  @LANGUAGE = VBScript %>
<% Option Explicit %>
<% 
Dim sConnection, objConn , objRS 

'sConnection = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;PORT=3306;DATABASE=DB NAME; USER=USER ID;PASSWORD=USER PASSWORD;OPTION=3;"); 


    sConnection = "Driver={MySQL ODBC 3.51 Driver};" & _ 
        "Server=mysql.hosting365.ie;" & _ 
        "Database=DB NAME;" & _ 
        "Uid=USER ID;" & _ 
        "Pwd=USER PASSWORD;" 


Set objConn = Server.CreateObject("ADODB.Connection") 

objConn.Open(sConnection) 

Set objRS = objConn.Execute("SELECT ProductName, Grade FROM comics")


While Not objRS.EOF
Response.Write objRS.Fields("ProductName") & ", " & objRS.Fields("Grade") & "<br>"
'Response.Write & " "
objRS.MoveNext
Wend 

objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
%>

I really got stuck on the ODBC connection, I kept on putting the address of my server in

Code:

'sConnection = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=mysql.hosting365.ie;PORT=3306;DATABASE=DB NAME; USER=USER ID;PASSWORD=USER PASSWORD;OPTION=3;");

but apparently thats how you do it if your accessing a database remotely,
since my database was on the same server as my site SERVER=localhost; is the correct one.

I would like someone else to confirm this, I'm very new mysql and odbc so I could be wrong

dbmab · #6 (**permalink**) 04-24-06, 14:54

See this link - http://www.mysql.com/products/connector/odbc/

liakristine · #7 (**permalink**) 05-17-06, 01:30

hi there.. im lia we have connected to our MySQL server, however we encounter a problem while opening our MySQL database, according to the msg. box appeared, the connection to MySQL server failed.. then we tried to connect it again, and we successfully did.. however when i tried to open the database again its the same msg. box i encountered.. can you please help me out with this?.. thanks in advance..

Internet Services

Web Development

Digital Marketing

Consumer Tech