MySQL connectivity thru Odbc

melikai · 10-14-2006, 01:08 AM

Does any one have the beginnings as to how to setup and ODBC drive for a java application to MySQL? How would I make that call. I cant get JDBC to work under any circumstances. I have my unixODBC gui install but how would implemnt that in my stand-alone. Thank you in advance

xhi · 10-14-2006, 06:15 PM

what is the problem that you are having with the mysql jdbc driver?

melikai · 10-15-2006, 12:55 AM

I installed MySQL thru my package manager for Suse 10.1 and I have no idea as to how to connect using my jdbc. I couldn't even do practice examples, I always got an error saying no suitable driver. So now Im trying to use the unixODBC driver and I was trying to see if any one has pointers taking this approach

mrcheeks · 10-15-2006, 07:17 AM

Make sure mysql is listening on the network.
Try

Code:

netstat -an

Code:

telnet localhost 3306

If it doesn't work try commenting out the line skip-networking in the configuration file of mysql my.cnf.

The jdbc driver is the way to go...

xhi · 10-15-2006, 10:34 AM

Quote:

I always got an error saying no suitable driver..

that means that either the connector-j is not found, or you are not initializing the driver correctly in your code.. to use jdbc and mysql you need the mysql jdbc driver, connector-j. goto mysql.com and get it. put that jar in your projects classpath.. show an example of the code you are using to load the driver and establish a connection..

melikai · 10-15-2006, 07:09 PM

This is what happend when i tried to telnet
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
8
5.0.18-MaxBG|_EySrg+?;H:'UV3:fConnection closed by foreign host.

This my example program to connect:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class Connect
{
public static void main (String[] args)
{
Connection conn = null;

try
{
Class.forName("com.mysql.jdbc.Driver");
String connection = "jdbc

dbc:mysql://127.0.0.1:3306/webserver?user=root&password=garfield01";
Connection conn = DriverManager.getConnection(connection);
System.out.println ("Database connection established");
}
catch (Exception e)
{
System.err.println ("Cannot connect to database server");
}
finally
{
if (conn != null)
{
try
{
conn.close ();
System.out.println ("Database connection terminated");
}
catch (Exception e) { /* ignore close errors */ }
}
}
}
}

After I get this Error:
Cannot connect to database server:

I don't know how to set the classpath under Suse Linux and when I look I dont have a MySQL Server but I hav the gui tools to MySQL stuff. I been workin on this for about a month and a half, and I am totally stumped as to what else I can do or where Im going wrong.

xhi · 10-15-2006, 07:22 PM

what ide are you using? you can add connector-j to your project.. if it is not on the class path it obviously will not work.. when you get that squared away here is a snipped sample of connecting to a mysql db.. notice how odbc is not mentioned.

Code:

Connection db;
try{
	Class.forName ("com.mysql.jdbc.Driver").newInstance();
	db = DriverManager.getConnection ("jdbc:mysql://" + 
					<HOSTNAME> +
					"/" + 
					<DATABASE>, 
				        <USERNAME>, 
					<PASSWORD>);
	if(!db.isClosed())
		System.out.println("[DBI] MySQL_DB Opened Successfully");
} 
catch(Exception e){ 
	System.out.println("[DBI] MySQL_DB Initialization Error: " + e.getMessage()); 
}

melikai · 10-15-2006, 08:21 PM

The class path, is that a file I jyst need to configure or is more like a directory that I need to put a file. Thats one of things about Suse I didn't like it seems standard from all the others

xhi · 10-15-2006, 08:35 PM

no by classpath i mean the java classpath. this is the path that java looks for classes. so if you are using mysql connector-j classes from the connector-j jar, then that jar needs to be on the classpath, so that java can find it when it tries to run..

what ide are you using? normally you can just add it to your project..

melikai · 10-15-2006, 10:15 PM

Im not using and IDE at all, Im using Kate, I have Netbeans and Eclipse but I don't know how to use them, not too mention I been Studying for the Java 5 cert class so by studying with notepad my skill level will go I know where that is so I just put the jar file in that directory and thats it?

mrcheeks · 10-16-2006, 06:31 AM

Read a little bit and ask questions later??

xhi · 10-16-2006, 08:03 AM

well the first thing to do is to get rid of the ridiculous notion that not using an ide is going to make you learn faster, or make you a better programmer.. it wont, example.. how has learning jdbc been going? exactly..besides if you ever plan on doing anything with that java cert you will need to know how to use an ide well to get/keep a job..

if you insist on using a text editor you are going to need to get the connector-j jar on your classpath when you run.. try `man java` to figure out how to do that.

melikai · 10-16-2006, 10:32 AM

No, your right, but some of the things I been learing using a text pad has helped me grasped the concept OOP what not. The ide I will be using will be Netbeans tho. Right now I'm tryin set the classpath. I appreciat all the advice

xhi · 10-16-2006, 12:31 PM

yeh, all of that is just my opinion, so take from it what you will..

in netbeans you should be able to add the connector-j jar to your project in a similar fashion that you would in eclipse.. in eclipse it would be done like so..

project options -> java build path -> libraries tab -> add external jars..

when you do that it puts that jar on the classpath for you..

good luck

melikai · 10-16-2006, 01:21 PM

I got DB Connectivity:
I finally figure out the .jar goes in where you compile and execute you code. The only thing is Im unsure if it was right to jar or unjar the File. After that, it only Connects and then Disconnects. real basic but my foot is in the door. Can you kind of walk me threw exactly what happeing in this code, I dont fully inderstand so i can replicate this later.
heres the code I used:

Code:

import java.sql.*;
public class Connecter {
	public static void main (String[] args) {
		Connection conn = null;
		try {
			String userName = "root";
			String password = "********";
			String url = "jdbc:mysql://127.0.0.1/movies";
			Class.forName ("com.mysql.jdbc.Driver").newInstance ();
			conn = DriverManager.getConnection (url, userName, password);
			System.out.println ("Database connection established");
		} catch (Exception e) {
			System.err.println ("getConnection: " + e);
		} finally {
			if (conn != null) {
				try {
					conn.close ();
					System.out.println ("Database connection terminated");
				} catch (Exception e) {
					/* ignore close errors */
				}
			}
		}
	}
}

how would I go about expanding on this for queries?