Database and java.sql.SQLException Questions

java-programmer, Database and java.sql.SQLException Questions Hello everyone!! I am back for some more much need advice and assistance! I
am creating a database for the first time. I am changing some programs I
recently wrote to work with this database that have to do with Salesmen. I
am getting the below error when I compile CreateDatabase.java. In that I am
creating the database SalesDatabase and then creating 5 tables (Users, SType,
Salesman, Sales, Product). The first thing I do is create those tables and
then insert informtion into the SType table and Product table. SType is the
SalesType of a salesman, meaning Entry, Junior or Senior that also has the
bonus in there as another field (.05, .10, .15) and then product has product
1, 2, 3, 4, and 5 with the corresponding price of each. After I do this I
need to read in a text file (SalesInformation.txt) and calculate the sales
based on the information in the database. So here are my questions:

(1) What is the below error telling me, have I not set the database up
correctly?
(2) Am I on the right track with what I have below, should I be reading the
text file as I am or should I change it to be its own method (anything you
tell me would be apprecaited!!)?
(3) Also, other than it not compiling...lol...I am unsure of the salestype
part with element 6 on line 124. I read in ENTRY, JUNIOR or SENIOR from the
salesinformation.txt file so how do I relate that with retrieving the bonus
number from the database?

Ok, I think that is enough questions for now. If anyone needs any additional
information as to what I am trying to do, just let me know and I would be
happy to fill you in!! I appreciate any hints or tips you may be able to
give and I look forward to getting this thing working :)!! Thanks everyone!

Error:
[code]
java.sql.SQLException: [Microsoft][ODBC Driver Manager] Data source name not
fou
nd and no default driver specified
at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6958)
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7115)
at sun.jdbc.odbc.JdbcOdbc.SQLDriverConnect(JdbcOdbc.java:3074)
at sun.jdbc.odbc.JdbcOdbcConnection.initialize(JdbcOdbcConnection.
java:3
23)
at sun.jdbc.odbc.JdbcOdbcDriver.connect(JdbcOdbcDriver.java:174)
at java.sql.DriverManager.getConnection(DriverManager.java:525)
at java.sql.DriverManager.getConnection(DriverManager.java:193)
at CreateDatabase.main(CreateDatabase.java:31)
[/code]

SalesInformation.txt
[code]
Jones&2&5&3&0&8&SENIOR
Smith&6&0&2&0&5&JUNIOR
Douglas&1&0&7&5&3&ENTRY
Hollins&9&6&3&7&15&ENTRY
Smythe&0&0&0&0&0&SENIOR
Billings&0&7&12&15&2&JUNIOR
Kozak&3&5&8&22&0&ENTRY
[/code]

CreateDatabase:
[code]
import java.io.*;
import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import javax.swing.*;
import sun.jdbc.odbc.*;

public class CreateDatabase
{
static String salesmanname;
int salesmanID;
static double saleslevel;
double salesbonus;
static double sales = 0.0;
double productprice;
static String prod1price;
static String prod2price;
static String prod3price;
static String prod4price;
static String prod5price;

public static void main(String args[])
{
String line;

try
{
new JdbcOdbcDriver();
String url = "jdbc:odbc:SalesDatabase";
Connection con = DriverManager.getConnection(url);
Statement stmt = con.createStatement();

stmt.executeUpdate("CREATE TABLE Users (UserName VARCHAR(25)," +
"UserPassword VARCHAR(25))");

stmt.executeUpdate("CREATE TABLE Stype (SalesType VARCHAR(25)," +
"SalesBonus CURRENCY)");

stmt.executeUpdate("CREATE TABLE Salesman (SalesmanName VARCHAR(25)," +
"SalesmanID INTEGER(10), SalesType VARCHAR(25))");

stmt.executeUpdate("CREATE TABLE Sales (SalesmanID INTEGER(10)," +
"TotalSales CURRENCY)");

stmt.executeUpdate("CREATE TABLE Product (ProductNumber INTEGER(1)," +
"ProductPrice VARCHAR(5))");

String insert1 = "INSERT INTO Stype VALUES('Entry', .05)";
String insert2 = "INSERT INTO Stype VALUES('Junior', .10)";
String insert3 = "INSERT INTO Stype VALUES('Senior', .15)";

stmt.executeUpdate(insert1);
stmt.executeUpdate(insert2);
stmt.executeUpdate(insert3);

String insert4 = "INSERT INTO Product VALUES(1, 2.98)";
String insert5 = "INSERT INTO Product VALUES(2, 4.50)";
String insert6 = "INSERT INTO Product VALUES(3, 9.98)";
String insert7 = "INSERT INTO Product VALUES(4, 4.49)";
String insert8 = "INSERT INTO Product VALUES(5, 6.87)";

stmt.executeUpdate(insert4);
stmt.executeUpdate(insert5);
stmt.executeUpdate(insert6);
stmt.executeUpdate(insert7);
stmt.executeUpdate(insert8);

String insert9 = "INSERT INTO Salesman VALUES(" +
salesmanname + "," + "''" + saleslevel +")";

stmt.executeUpdate(insert9);

String insert10 = "INSERT INTO Sales VALUES(" +
sales +")";

stmt.executeUpdate(insert10);

String query1 = "SELECT ProductPrice FROM Product WHERE ProductNumber =
1";
ResultSet rs1 = stmt.executeQuery(query1);
while(rs1.next())
prod1price = rs1.getString("ProductPrice");

String query2 = "SELECT ProductPrice FROM Product WHERE ProductNumber =
2";
ResultSet rs2 = stmt.executeQuery(query2);
while(rs2.next())
prod2price = rs2.getString("ProductPrice");

String query3 = "SELECT ProductPrice FROM Product WHERE ProductNumber =
3";
ResultSet rs3 = stmt.executeQuery(query3);
while(rs3.next())
prod3price = rs3.getString("ProductPrice");

String query4 = "SELECT ProductPrice FROM Product WHERE ProductNumber =
4";
ResultSet rs4 = stmt.executeQuery(query4);
while(rs4.next())
prod4price = rs4.getString("ProductPrice");

String query5 = "SELECT ProductPrice FROM Product WHERE ProductNumber =
5";
ResultSet rs5 = stmt.executeQuery(query5);
while(rs5.next())
prod5price = rs5.getString("ProductPrice");

FileReader file = new FileReader("SalesInformation.txt");
BufferedReader buff = new BufferedReader(file);

while ((line = buff.readLine())!= null)
{
String[] elements = line.split("&");
salesmanname = elements [0];

double sales1 = 0.0;
double sales2 = 0.0;
double sales3 = 0.0;
double sales4 = 0.0;
double sales5 = 0.0;

sales1 = Double.parseDouble(elements[1]) * Double.parseDouble(prod1price)
;
sales2 = Double.parseDouble(elements[2]) * Double.parseDouble(prod2price)
;
sales3 = Double.parseDouble(elements[3]) * Double.parseDouble(prod3price)
;
sales4 = Double.parseDouble(elements[4]) * Double.parseDouble(prod4price)
;
sales5 = Double.parseDouble(elements[5]) * Double.parseDouble(prod5price)
;

saleslevel = (elements[6]); // I am a little unsure of how to use what
is in the database
// and apply it here to calculate the saleslevel for a salesman..?
?

try
{
sales = (sales1 + sales2 + sales3 + sales4 + sales5);

if( sales == 0.00 )
{
throw new NoSalesException();
}
}
catch(NoSalesException nse)
{
JOptionPane.showMessageDialog(null, salesmanname + " has no Sales!!",
"Sales Equals Zero",
JOptionPane.WARNING_MESSAGE);
}

sales = sales + (sales * saleslevel);

}
buff.close();
}
catch(Exception e)
{
e.printStackTrace();
}
}
}
[/code]

--
Message posted via http://www.javakb.com

java-programmer >> Database and java.sql.SQLException Questions "zhah99 via JavaKB.com" wrote...

> Hello everyone!! I am back for some more
> much need advice and assistance!

To get more answers, you could try to partition your comments into more
readable chunks.

It's just a suggestion, people tend to back away from very long introductory
texts... ;-)

Anyway, I give a shot.

> (1) What is the below error telling me, have I not
> set the database up correctly?

According to the error message, you haven't set it up at all!

At least not as a dsn in your system.

Or not with the name "SalesDatabase". (could be as simple as a misspelling
when you registered the database as a dsn).

I also notice that you use system dsn's via the ODBC-JDBC bridge.

Depending on what database you use, there's much to gain on using another
JDBC-driver instead.

> (2) Am I on the right track with what I have below,
> should I be reading the text file as I am or should
> I change it to be its own method (anything you
> tell me would be apprecaited!!)?

Should and should...

I could say yes, but then I would have to explain why, and what other things
you also ought to do (such as encapsulating the fields, arrays, values into
classes for each significant thing, etc), so I wont say that, this time.

> (3) Also, other than it not compiling...lol...
> I am unsure of the salestype part with element 6
> on line 124. I read in ENTRY, JUNIOR or SENIOR from the
> salesinformation.txt file so how do I relate that with
> retrieving the bonus number from the database?

First of all, why don't you save it in the SalesMan record?

Then you could use it as a foreign key to the SType table, assuming you also
change those keys to upper case, so there's a correspondance between them.

If you do that, you can simply do a query against the database to retrieve
the bonus.

> Ok, I think that is enough questions for now.

...and enough with answer from me for now.

// Bjorn A

Inviato da X-Privat.Org - Registrazione gratuita http://www.x-privat.org/join.php

java-programmer >> Database and java.sql.SQLException Questions zhah99 via JavaKB.com wrote:
...
> am getting the below error when I compile CreateDatabase.java. In that I am
...
> (3) Also, other than it not compiling...lol...I am unsure of the salestype
...
> Error:
> [code]
...
> java.sql.SQLException: [Microsoft][ODBC Driver Manager] Data source name not

Are you really, really sure your program doens't compile? Because that
looks like a runtime exception to me ...

java-programmer >> Database and java.sql.SQLException Questions Jon Martin Solaas wrote:
>...
>> am getting the below error when I compile CreateDatabase.java. In that I am
>...
>> (3) Also, other than it not compiling...lol...I am unsure of the salestype
>...
>> Error:
>> [code]
>...
>> java.sql.SQLException: [Microsoft][ODBC Driver Manager] Data source name not
>
>Are you really, really sure your program doens't compile? Because that
>looks like a runtime exception to me ...

Ok, thank you for your replies, it is compiling now. I misspelled the
Database name...how ridiculous is that?? :) Anyway, so it is compiling but
now it is not creating the tables. I keep looking at the stmt.executeUpdate
code, but I don't see what is going on. I have been over line 45 twenty
times and I don't see it. I appreciate all of your help here, this is my
first database and so I am a little unsure. So, again thank you!!

Error:
[code]
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error
in
CREATE TABLE statement.
at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6958)
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7115)
at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(JdbcOdbc.java:3111)
at sun.jdbc.odbc.JdbcOdbcStatement.execute(JdbcOdbcStatement.java:338)

at sun.jdbc.odbc.JdbcOdbcStatement.executeUpdate(JdbcOdbcStatement.
java:
288)
at CreateDatabase.main(CreateDatabase.java:45)
[/code]

Statements in CreateDatabase.java file:
[code]
try
{
new JdbcOdbcDriver();
String url = "jdbc:odbc:SalesDatabase";
Connection con = DriverManager.getConnection(url);
Statement stmt = con.createStatement();

stmt.executeUpdate ("CREATE TABLE Users (UserName "
+ "VARCHAR(25), UserPassword VARCHAR(25))");

stmt.executeUpdate ("CREATE TABLE Stype (SalesType "
+ "VARCHAR(25), SalesBonus VARCHAR(5))");

stmt.executeUpdate ("CREATE TABLE Salesman (SalesmanName "
+ "VARCHAR(25), SalesmanID INTEGER(10), SalesType "
+ "VARCHAR(25))");

stmt.executeUpdate ("CREATE TABLE Sales (SalesmanID "
+ "INTEGER(10), TotalSales CURRENCY)");

stmt.executeUpdate ("CREATE TABLE Product (ProductNumber "
+ "INTEGER(1), ProductPrice VARCHAR(5))");

String insert1 = "INSERT INTO Stype VALUES('Entry', .05)";
String insert2 = "INSERT INTO Stype VALUES('Junior', .10)";
String insert3 = "INSERT INTO Stype VALUES('Senior', .15)";

stmt.executeUpdate(insert1);
stmt.executeUpdate(insert2);
stmt.executeUpdate(insert3);

String insert4 = "INSERT INTO Product VALUES(1, 2.98)";
String insert5 = "INSERT INTO Product VALUES(2, 4.50)";
String insert6 = "INSERT INTO Product VALUES(3, 9.98)";
String insert7 = "INSERT INTO Product VALUES(4, 4.49)";
String insert8 = "INSERT INTO Product VALUES(5, 6.87)";

stmt.executeUpdate(insert4);
stmt.executeUpdate(insert5);
stmt.executeUpdate(insert6);
stmt.executeUpdate(insert7);
stmt.executeUpdate(insert8);
[/code]

--
Message posted via JavaKB.com
http://www.javakb.com/Uwe/Forums.aspx/java-databases/200604/1

java-programmer >> Database and java.sql.SQLException Questions "zhah99 via JavaKB.com" wrote:

> Anyway, so it is compiling but
> now it is not creating the tables. I keep looking at the
> stmt.executeUpdate
> code, but I don't see what is going on. I have been over line 45 twenty
> times and I don't see it. I appreciate all of your help here, this is my
> first database and so I am a little unsure. So, again thank you!!
>
> Error:
> [code]
> java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax
> error
> in
> CREATE TABLE statement.

[snip]

> at CreateDatabase.main(CreateDatabase.java:45)

So which line *is* line 45?

As the source codes tend to wrap in the postings, it's a bit difficult for
us to spot the specific lines... ;-)

Anyway, didn't you use MSAccess?

Then it's not possible to use fixed length Integers...

E.g.:

> stmt.executeUpdate ("CREATE TABLE Salesman (SalesmanName "
> + "VARCHAR(25), SalesmanID INTEGER(10), SalesType "
> + "VARCHAR(25))");

...should then be:

stmt.executeUpdate ("CREATE TABLE Salesman (SalesmanName "
+ "VARCHAR(25), SalesmanID INTEGER, SalesType "
+ "VARCHAR(25))");

...etc...

// Bjorn A

Inviato da X-Privat.Org - Registrazione gratuita http://www.x-privat.org/join.php

java-programmer >> Database and java.sql.SQLException Questions Well Integer was definitly one of my problems, I have the CreateDatabase
running now. I am working on the SalesClient. I am getting a
NullPointerException in the addNewSalesman method below. I think it might
have to do with the way I am inserting the data into the database, is this
correct? Am I do something wrong with the quotes?

[code]
.....
public void addNewSalesman()
{
double sales = calculateNewSales(product.getSelectedIndex(), Integer.
parseInt(amtprodsold.getText()));

if (getNewSalesman(salesmanname.getText()) != null)
{
JOptionPane.showMessageDialog(this, salesmanname.getText() + " is an
existing Salesman. To update an existing Salesman," +
"\n" + "please go to Sales Data Entry - Update Current Salesman.",
"Existing Salesman", JOptionPane.INFORMATION_MESSAGE);
}
else
{
try
{
if(saleslevel.getSelectedItem().equals("Entry"))
{
level = .05;
}
else if(saleslevel.getSelectedItem().equals("Junior"))
{
level = .10;
}
else if(saleslevel.getSelectedItem().equals("Senior"))
{
level = .15;
}

String insertsalesman2 = "INSERT INTO Salesman VALUES(" + salesmanname +
"," + "''" + level + ")";
String insertsales2 = "INSERT INTO Sales VALUES(" + sales + ")";
stmt.executeUpdate(insertsalesman2);
stmt.executeUpdate(insertsales2);

System.out.println("Inside addNewSalesman Method!!");
}
catch(Exception ee)
{
ee.printStackTrace();
}
}

salesmanname.setText("");
saleslevel.setSelectedIndex(0);
product.setSelectedIndex(0);
amtprodsold.setText("");
salesmanname.requestFocus();
}
.....
[/code]

--
Message posted via http://www.javakb.com

java-programmer >> Database and java.sql.SQLException Questions
"zhah99 via JavaKB.com" wrote...

> Well Integer was definitly one of my problems, I have the CreateDatabase
> running now. I am working on the SalesClient. I am getting a
> NullPointerException in the addNewSalesman method below. I think it might
> have to do with the way I am inserting the data into the database, is this
> correct? Am I do something wrong with the quotes?

Why not just test with some values, as if..

salesmanname = "John";
level = .15;

> String insertsalesman2 = "INSERT INTO Salesman VALUES(" + salesmanname +
> "," + "''" + level + ")";

...would become:

String insertsalesman2 =
"INSERT INTO Salesman VALUES(John,''.15)";

...which probably isn't what you want...

I would suggest you start using PreparedStatements instead, which would
simplify things, and make it a bit safer. That would insert those irritating
quotes by itself, and only when needed.

If I recall, a salesman really had three fields...

I expect you have your statements as instance variables somewehere else, so
if you continue with that solution, you could have them prebuilt there as
well...

=== in the class definition =======================

CallableStatement insertSalesmanStmt = null;

=== in the initalization code of the statements ===

String insertsalesman =
"INSERT INTO Salesman VALUES(?,?,?)";

insertSalesmanStmt =
connection.prepareCall (insertSalesman);

=== in addNewSalesman =============================

String name = ...
int id = ...
String type = ...

insertSalesmanStmt.setString(1, name);
insertSalesmanStmt.setInt(2, id);
insertSalesmanStmt.setString(3, type);

insertSalesmanStmt.execute();

===================================================

...unless you have changed the declaration of tables since before.

It would anyway show how you could approach it.

// Bjorn A

Inviato da X-Privat.Org - Registrazione gratuita http://www.x-privat.org/join.php