We help IT Professionals succeed at work.

MySQL ODBC 3.51 Driver Database Connection

579 Views
Last Modified: 2012-06-22
Hi,

I want to connect to my MySQL database and display all records in my database.
Can anyone help me.

<cfscript>

classLoader = createObject("java", "java.lang.Class");
classLoader.forName("sun.jdbc.odbc.JdbcOdbcDriver");
dm = createObject("java","java.sql.DriverManager");

con = dm.getConnection("jdbc:odbc:DRIVER={MySQL ODBC 3.51 Driver}; SERVER=xxx; PORT=xxx; DATABASE=xxx; USER=xxx; PASSWORD=xxx; OPTION=16;");

st = con.createStatement();
 
</cfscript>

<cfquery name="getFromDynamicConnection" datasource="st">
  Select * FROM CustLogin
</cfquery>

I am not sure how to do this!!!!!!


Thanks,
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
<cfquery name="getFromDynamicConnection" datasource="st">

The best way is to create a datasource in the CF Administrator first. Then you can run any query by using the datasource name in your cfquery tags.


classLoader = createObject("java", "java.lang.Class");

CF does not support dynamic datasources. While you can connect to a db *without* creating a  datasource .. I wouldn't recommend it.  It's more complex and error prone. Also, JDBC (not ODBC) is generally the preferred connection type.

CERTIFIED EXPERT
Most Valuable Expert 2015
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
lulu50Web application

Author

Commented:
agx,
I already setup my ODBC from the host control panel. the connection is fine right now but I
want to dump or display my data from my table.
how would I do this.


<cfscript>

classLoader = createObject("java", "java.lang.Class");
classLoader.forName("sun.jdbc.odbc.JdbcOdbcDriver");
dm = createObject("java","java.sql.DriverManager");

con = dm.getConnection("jdbc:odbc:DRIVER={MySQL ODBC 3.51 Driver}; SERVER=xxx; PORT=xxx; DATABASE=xxx; USER=xxx; PASSWORD=xxx; OPTION=16;");

st = con.createStatement();
rs = st.ExecuteQuery("Select * FROM CustLogin");
q = createObject("java", "coldfusion.sql.QueryTable").init(rs);

how can I do this I need to dump my data
dump(var=#q#);

rs.close();
st.close();
con.close();

</cfscript>
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
> I already setup my ODBC from the host control panel

I guess what I'm asking is - is there a reason why are you not using a CF datasource? The normal way of connecting to a db in CF is a lot simpler than what you're doing.  With a CF datasource you don't need any of that code. Just use your datasource name in a cfquery tag.

ie
<cfquery name="getData" datasource="yourCFDatasourceName">
     SELECT ColA, ColB FROM SomeTableName
</cfquery>

Then to output the information, use <cfoutput>

ie
<cfoutput query="getData">
    #ColA#  |  #ColB# <br>
</cfoutput>

Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.