error while connecting to MySql database

[sheeba]

hi

while i'm trying to work out the client billing application(SWT) i cant connect to the MySQL database.i have created a database names 'clients' in MYSQL and i set the jar file (mm.mysql-2.0.8-bin.jar) in the class path.is there any settings to do?i'm a beginer .please give a reply.

i got the error message as follows.

*** Error: com.mysql.jdbc.Driver
Couldn't open a connection to the database.
Exception in thread "main" java.lang.NullPointerException
at ClientBillingUI.<init>(ClientBillingUI.java:104)
at ClientBilling.main(ClientBilling.java:15)
******************************************************

//ClientBillingDB.java

import java.sql.*;
import java.util.LinkedList;

public class ClientBillingDB {

private java.sql.Connection conn;
private String url;

private static final int DUPKEY_ERRCODE = 1062;

ClientBillingDB() throws SQLException, ClassNotFoundException {
try {

//DATABASE CONNECTION

//MySQL
//Load the MySQL driver
Class.forName("com.mysql.jdbc.Driver");
url = "jdbc:mysql://localhost/clients";
//Open the connection
conn = DriverManager.getConnection(url, "root", "");
System.out.println("Opened MYSQL database."); }
catch (Exception e) {
System.out.println("*** Error: " + e.getMessage());
}
}

public void close() throws SQLException {
//Close the open database connection.
conn.close();
System.out.println("Closed the database.");
}

public Client[] retrieveClients() throws SQLException {
String query = "SELECT * FROM client;";
ResultSet rs = retrieve(query);

//Parse the ResultSet into a LinkedList of Clients
LinkedList results = new LinkedList();
while (rs.next()) {
//Create the next client object, and load the fields.
Client c = new Client();
c.setFields(
rs.getInt("clientID"),
rs.getString("firstName"),
rs.getString("lastName"),
rs.getString("phone"),
rs.getString("email"),
rs.getString("dob"),
rs.getString("address"),
rs.getString("miscInfo"));
//results.add(c);
}

Client[] retval = new Client[results.size()];
//retval = (Client[])results.toArray(retval);

return retval;
}

public Transaction[] retrieveTransactions(int clientID)
throws SQLException {
String query =
"SELECT * FROM transaction where clientID = "
+ clientID
+ " ORDER BY transactionID;";
ResultSet rs = retrieve(query);

//Build a LinkedList of the correct Transactions.
LinkedList results = new LinkedList();
while (rs.next()) {
//Create the next Transaction object, and load its fields.
Transaction t = new Transaction();
t.setFields(
rs.getInt("transactionID"),
rs.getInt("clientID"),
rs.getInt("amount"),
rs.getString("tDate"),
rs.getString("description"));
// results.add(t);
}

//Convert the LinkedList to an array, and return it.
Transaction[] retval = new Transaction[results.size()];
//retval = (Transaction[])results.toArray(retval);
return retval;
}

public boolean create(Client c) {
String update =
"INSERT INTO client(clientID, firstName, lastName, phone, email, dob, address, miscInfo) VALUES (";

update += c.clientID
+ ", "
+ "'"
+ c.firstName
+ "', '"
+ c.lastName
+ "', '"
+ c.phone
+ "', '"
+ c.email
+ "', '"
+ c.dob
+ "', '"
+ c.address
+ "', '"
+ c.miscInfo
+ "');";

try {
execute(update);
} catch (SQLException e) {
if (e.getErrorCode() == DUPKEY_ERRCODE) { //duplicate key
return false;
} else
e.printStackTrace();
}
return true;
}

public boolean create(Transaction t) {
//Build an SQL insert statement for the new Transaction.

String update =
"INSERT INTO transaction(transactionID, clientID, description, amount, tDate) VALUES (";

//Specify the fields.
update += t.transactionID
+ ", "
+ ""
+ t.clientID
+ ", '"
+ t.description
+ "', "
+ t.amount
+ ", '"
+ t.date
+ "');";

try {
execute(update);
} catch (SQLException e) {
if (e.getErrorCode() == DUPKEY_ERRCODE) { //duplicate key
return false;
} else
e.printStackTrace();
}
return true;
}

public boolean update(int oldID, Client c) {
//Build a SQL update statement for the Client.
String update =
"UPDATE client SET clientID = "
+
+ c.clientID
+ ", firstName = '"
+ c.firstName
+ "', lastName = '"
+ c.lastName
+ "', phone = '"
+ c.phone
+ "', email = '"
+ c.email
+ "', dob = '"
+ c.dob
+ "', address = '"
+ c.address
+ "', miscInfo = '"
+ c.miscInfo
+ "' WHERE clientID = '"
+ oldID
+ "';";
try {
execute(update);
} catch (SQLException e) {
if (e.getErrorCode() == DUPKEY_ERRCODE) {
return false;
} else
e.printStackTrace();
}
return true;
}

public boolean update(int oldID, Transaction t) {
//Build a SQL update statement for the Transaction.
String update =
"UPDATE transaction SET amount = "
+ t.amount
+ ", tDate = '"
+ t.date
+ "', description = '"
+ t.description
+ "', transactionID = "
+ t.transactionID
+ " WHERE transactionID = "
+ oldID
+ " and clientID = " + t.clientID + ";";
try {
execute(update);
} catch (SQLException e) {
if (e.getErrorCode() == DUPKEY_ERRCODE) {
return false;
} else
e.printStackTrace();
}
return true;
}

public void delete(Client c) throws SQLException {
//Build and execute a SQL delete statement for the Transaction.
String query =
"DELETE FROM client WHERE clientID = " + c.clientID + ";";
execute(query);
}

public void delete(Transaction t) throws SQLException {
//Build and execute a SQL delete statement for the Transaction.
String query =
"DELETE FROM transaction WHERE transactionID = "
+ t.transactionID
+ " AND clientID = "
+ t.clientID
+ ";";
execute(query);
}

public void deleteTransactionsByClientID(int clientID) throws SQLException {
//Build and execute a SQL delete statement for the Transaction.
String query =
"DELETE FROM transaction WHERE clientID = "
+ clientID + ";";
execute(query);
}

private void execute(String cmd) throws SQLException {
//Execute a SQL statement.
//If a primary key was autogenerated
//(ie. for an INSERT statement), extract it and return it.
//Otherwise return -1.

//Statement s = conn.createStatement();
//int result = s.executeUpdate(cmd, Statement.RETURN_GENERATED_KEYS);
//int result = s.executeUpdate(cmd);
//System.out.println(result);
/* Access could not handle the following statements
so they have been removed for now.
However, MYSQL was able to handle them correctly. DS

ResultSet keys = s.getGeneratedKeys();
if (keys != null && keys.getMetaData().getColumnCount() > 0) {
keys.next();
int newKey = keys.getInt("GENERATED_KEY");
System.out.println("New key is: " + newKey);
keys.close();
s.close();
return newKey;
}
else
return -1;
*/
}

private ResultSet retrieve(String query) throws SQLException {
//System.out.printn(query);

//Execute a SQL statement, usually a SELECT, which returns a ResultSet.
Statement s = conn.createStatement();
ResultSet res = s.executeQuery(query);
return res;
}

/*
//For debugging purposes only...
private void dumpResultSet(ResultSet rs) throws SQLException {
//Print a ResultSet to stdout for debugging.

ResultSetMetaData rsm = rs.getMetaData();

int max = rsm.getColumnCount();
for (int i = 1; i <= max; i++)
System.out.print(rsm.getColumnName(i) + "\t");
System.out.println();

while (rs.next()) {
for (int i = 1; i <= max; i++)
System.out.print(rs.getString(i) + "\t");
System.out.println();
}

}
*/

}


**************************************************

//ClientBilling.java

/*
* Created on 9-Apr-2003
*
* To change this generated comment go to
* Window>Preferences>Java>Code Generation>Code Template
*/

/**
* @author Christopher
*/
public class ClientBilling {

//Start the application
public static void main(String[] args) {
ClientBillingUI instance = new ClientBillingUI();
instance.run();
}

}

********************************************************

[Kelly]

I do not use MySQL, I find PostgreSQL much easier for me to use.

Off the top of my head the problem could be that you
need to supply a password in the getConnection call.

In PostgreSQL you have to tell the server you want to listen to outside connections, I use the simpler form of url in PostgreSQL
url = 'jdbc:postgresql:client";
Maybe useing the //localhost is causing you trouble???

Hope this helps you out.

Kelly

Schedule A Meeting
Meeting Management Software
http://www.sam.mmoge.com/
-----------------------------------
Tiger Paw Remote
Universal touch remote control
http://www.tigerpawremote.com/

[Marc Adams]

You're using a really old driver that isn't supported. You need to get the latest MySQL J connector at:

http://dev.mysql.com/downloads/connector/j/5.0.html

You're getting a null exception because com.mysql.jdbc.Driver is for the j connector in the above link. The connector you're using is completely different.