You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by "William B." <wi...@yahoo.com> on 2006/12/13 22:59:20 UTC
Columns of type 'REAL' cannot hold values of type 'CHAR'
I keep receiving the error message above when I try to add a salary. Please help
import java.sql.*;
import java.awt.*;
import java.awt.event.*;
import java.util.*;
import javax.swing.*;
public class Workers extends JFrame {
private Connection connection;
private Statement statement;
private ResultSet resultSet;
private ResultSetMetaData rsMetaData;
private Container container;
private JTable table;
private JTextField input;
private JButton addSalariedWorker, addCommissionWorker,
addBasePlusCommissionWorker, addHourlyWorker;
// constructor Workers
public Workers()
{
super( "Add Staff Members" );
// The URL specifying the workers database to which this program
// connects to using JDBC
String url = "jdbc:derby:workers";
// Load the driver to allow connection to the database
try {
Class.forName( "org.apache.derby.jdbc.EmbeddedDriver" );
connection = DriverManager.getConnection( url );
}
catch ( ClassNotFoundException cnfex ) {
System.err.println( "Failed to load JDBC driver." );
cnfex.printStackTrace();
System.exit( 1 ); // terminate program
}
catch ( SQLException sqlex ) {
System.err.println( "Unable to connect" );
sqlex.printStackTrace();
System.exit( 1 ); // terminate program
}
// if connected to database, set up GUI
JPanel topPanel = new JPanel();
topPanel.setLayout( new FlowLayout() );
topPanel.add( new JLabel( "Enter query to insert workers:" ) );
input = new JTextField( 50 );
topPanel.add( input );
input.addActionListener(
new ActionListener() {
public void actionPerformed( ActionEvent e )
{
addWorker( input.getText() );
}
}
);
// create four buttons that allow user to add specific employee
JPanel centerPanel = new JPanel();
centerPanel.setLayout( new FlowLayout() );
addSalariedWorker = new JButton( "Add Salaried Worker" );
addSalariedWorker.addActionListener( new ButtonHandler() );
addCommissionWorker = new JButton( "Add Commission Worker" );
addCommissionWorker.addActionListener( new ButtonHandler() );
addBasePlusCommissionWorker =
new JButton( "Add Base Plus Commission Worker" );
addBasePlusCommissionWorker.addActionListener(
new ButtonHandler() );
addHourlyWorker = new JButton( "Add Hourly Worker" );
addHourlyWorker.addActionListener( new ButtonHandler() );
// add four buttons to centerPanel
centerPanel.add( addSalariedWorker );
centerPanel.add( addCommissionWorker );
centerPanel.add( addBasePlusCommissionWorker );
centerPanel.add( addHourlyWorker );
JPanel inputPanel = new JPanel();
inputPanel.setLayout( new BorderLayout() );
inputPanel.add( topPanel, BorderLayout.NORTH );
inputPanel.add( centerPanel, BorderLayout.CENTER );
table = new JTable( 4, 4 );
container = getContentPane();
container.setLayout( new BorderLayout() );
container.add( inputPanel, BorderLayout.NORTH );
container.add( table, BorderLayout.CENTER );
getTable();
setSize( 800, 300 );
setVisible( true );
} // end constructor Workers
private void getTable()
{
try {
statement = connection.createStatement();
resultSet = statement.executeQuery( "SELECT * FROM workers" );
displayResultSet( resultSet );
}
catch ( SQLException sqlex ) {
sqlex.printStackTrace();
}
}
private void addWorker( String query )
{
try {
statement = connection.createStatement();
statement.executeUpdate( query );
getTable();
}
catch ( SQLException sqlex ) {
sqlex.printStackTrace();
}
}
private void displayResultSet( ResultSet rs ) throws SQLException
{
// position to first record
boolean moreRecords = rs.next();
// if there are no records, display a message
if ( !moreRecords ) {
JOptionPane.showMessageDialog( this,
"ResultSet contained no records" );
return;
}
Vector<Object> columnHeads = new Vector<Object>();
Vector<Object> rows = new Vector<Object>();
try {
// get column heads
ResultSetMetaData rsmd = rs.getMetaData();
for ( int i = 1; i <= rsmd.getColumnCount(); ++i )
columnHeads.addElement( rsmd.getColumnName( i ) );
// get row data
do {
rows.addElement( getNextRow( rs, rsmd ) );
} while ( rs.next() );
// display table with ResultSet contents
table = new JTable( rows, columnHeads );
JScrollPane scroller = new JScrollPane( table );
container.remove( 1 );
container.add( scroller, BorderLayout.CENTER );
container.validate();
} // end try
catch ( SQLException sqlex ) {
sqlex.printStackTrace();
}
} // end method displayResultSet
private Vector getNextRow( ResultSet rs,
ResultSetMetaData rsmd ) throws SQLException
{
Vector<Object> currentRow = new Vector<Object>();
for ( int i = 1; i <= rsmd.getColumnCount(); ++i )
switch( rsmd.getColumnType( i ) ) {
case Types.VARCHAR:
case Types.LONGVARCHAR:
currentRow.addElement( rs.getString( i ) );
break;
case Types.INTEGER:
currentRow.addElement( new Long( rs.getLong( i ) ) );
break;
case Types.REAL:
currentRow.addElement( new Float( rs.getDouble( i ) ) );
break;
case Types.DATE:
currentRow.addElement( rs.getDate( i ) );
break;
default:
System.out.println( "Type was: " +
rsmd.getColumnTypeName( i ) );
}
return currentRow;
} // end method getNextRow
public void shutDown()
{
try {
connection.close();
}
catch ( SQLException sqlex ) {
System.err.println( "Unable to disconnect" );
sqlex.printStackTrace();
}
}
public static void main(String[] args)
{
final Workers application = new Workers();
application.addWindowListener(
new WindowAdapter() {
public void windowClosing( WindowEvent e )
{
application.shutDown();
System.exit( 0 );
}
}
);
}
// inner class ButtonHandler handles button event
private class ButtonHandler implements ActionListener {
public void actionPerformed( ActionEvent event )
{
String socialSecurityNumber = JOptionPane.showInputDialog(
"Employee Social Security Number" );
String insertQuery = "", displayQuery = "";
// add salaried employee to table salariedWorkers
if ( event.getSource() == addSalariedWorker ) {
double weeklySalary = Double.parseDouble(
JOptionPane.showInputDialog( "Weekly Salary:" ) );
insertQuery = "INSERT INTO salariedWorkers VALUES ( '" +
socialSecurityNumber + "', '" + weeklySalary + "', '0' )";
displayQuery = "SELECT workers.socialSecurityNumber, " +
"workers.firstName, workers.lastName, " +
"workers.employeeType, salariedWorkers.weeklySalary" +
" FROM workers, salariedWorkers WHERE " +
"workers.socialSecurityNumber = " +
"salariedWorkers.socialSecurityNumber";
}
// add commission employee to table commissionWorker
else if ( event.getSource() == addCommissionWorker ) {
int grossSales = Integer.parseInt(
JOptionPane.showInputDialog( "Gross Sales:" ) );
double commissionRate = Double.parseDouble(
JOptionPane.showInputDialog( "Commission Rate:" ) );
insertQuery = "INSERT INTO commissionWorkers VALUES ( '" +
socialSecurityNumber + "', '" + grossSales + "', '" +
commissionRate + "', '0' )";
displayQuery = "SELECT workers.socialSecurityNumber, " +
"workers.firstName, workers.lastName, " +
"workers.employeeType, commissionWorkers.grossSales," +
" commissionWorkers.commissionRate FROM workers, " +
"commissionWorkers WHERE workers.socialSecurityNumber="
+ "commissionWorkers.socialSecurityNumber";
}
// add base plus commission employee to table
// basePlusCommissionEmployee
else if ( event.getSource() == addBasePlusCommissionWorker ) {
int grossSales = Integer.parseInt(
JOptionPane.showInputDialog( "Gross Sales:" ) );
double commissionRate = Double.parseDouble(
JOptionPane.showInputDialog( "Commission Rate:" ) );
double baseSalary = Double.parseDouble(
JOptionPane.showInputDialog( "Base Salary:" ) );
insertQuery = "INSERT INTO basePlusCommissionWorkers " +
"VALUES ( '" + socialSecurityNumber + "', '" + grossSales +
"', '" + commissionRate + "', '" + baseSalary + "', '0' )";
displayQuery = "SELECT workers.socialSecurityNumber, " +
"workers.firstName, workers.lastName, workers." +
"employeeType, basePlusCommissionWorkers.baseSalary, " +
"basePlusCommissionWorkers.grossSales, basePlus" +
"CommissionWorkers.commissionRate FROM workers, " +
"basePlusCommissionWorkers WHERE " +
"workers.socialSecurityNumber = " +
"basePlusCommissionWorkers.socialSecurityNumber";
}
// add hourly employee to table hourlyEmployee
else {
int hours = Integer.parseInt(
JOptionPane.showInputDialog( "Hours:" ) );
double wage = Double.parseDouble(
JOptionPane.showInputDialog( "Wage:" ) );
insertQuery = "INSERT INTO hourlyWorkers VALUES ( '" +
socialSecurityNumber + "', '" + hours + "', '" + wage +
"', '0' )";
displayQuery = "SELECT workers.socialSecurityNumber, " +
"workers.firstName, workers.lastName, " +
"workers.employeeType, hourlyWorkers.hours, " +
"hourlyWorkers.wage FROM workers, hourlyWorkers " +
"WHERE workers.socialSecurityNumber = " +
"hourlyWorkers.socialSecurityNumber";
}
// execute insert query and display employee info
try {
statement = connection.createStatement();
statement.executeUpdate( insertQuery );
// display the employee info
statement = connection.createStatement();
resultSet = statement.executeQuery( displayQuery );
displayResultSet( resultSet );
}
catch ( SQLException exception ) {
exception.printStackTrace();
}
} // end method actionPerformed
} // end inner class ButtonHandler
} // end class Workers
SQL file
connect 'jdbc:derby:workers;create=true'
;
drop table workers
;
drop table salariedWorkers
;
drop table commissionWorkers
;
drop table basePlusCommissionWorkers
;
drop table hourlyWorkers
;
create table workers (
socialSecurityNumber varchar (30) NOT NULL,
firstName varchar (30) NOT NULL,
lastName varchar (30) NOT NULL,
birthday date NOT NULL,
employeeType varchar (30) NOT NULL,
departmentName varchar (30) NOT NULL,
constraint pk_workers primary key (socialSecurityNumber)
)
;
create table salariedWorkers (
socialsecurityNumber varchar (30) NOT NULL,
weeklySalary real NOT NULL,
bonus real,
constraint fk_salariedWorkers foreign key (socialSecurityNumber)
references workers (socialSecurityNumber)
)
;
create table commissionWorkers (
socialSecurityNumber varchar (30) NOT NULL,
grossSales int NOT NULL,
commissionRate real NOT NULL,
bonus real,
constraint fk_commissionWorkers foreign key (socialSecurityNumber)
references workers (socialSecurityNumber)
)
;
create table basePlusCommissionWorkers (
socialSecurityNumber varchar (30) NOT NULL,
grossSales int NOT NULL,
commissionRate real NOT NULL,
baseSalary real NOT NULL,
bonus real,
constraint fk_basePlusCommissionWorkers foreign key (socialSecurityNumber)
references workers (socialSecurityNumber)
)
;
create table hourlyWorkers (
socialSecurityNumber varchar (30) NOT NULL,
hours int NOT NULL,
wage real NOT NULL,
bonus real,
constraint fk_hourlyWorkers foreign key (socialSecurityNumber)
references workers (socialSecurityNumber)
)
;
---------------------------------
Want to start your own business? Learn how on Yahoo! Small Business.
Re: Columns of type 'REAL' cannot hold values of type 'CHAR'
Posted by Stanley Bradbury <St...@gmail.com>.
William B. wrote:
> I keep receiving the error message above when I try to add a salary.
> Please help
>
> import java.sql.*;
> import java.awt.*;
> import java.awt.event.*;
> import java.util.*;
> import javax.swing.*;
>
> public class Workers extends JFrame {
> private Connection connection;
> private Statement statement;
> private ResultSet resultSet;
> private ResultSetMetaData rsMetaData;
> private Container container;
> private JTable table;
> private JTextField input;
> private JButton addSalariedWorker, addCommissionWorker,
> addBasePlusCommissionWorker, addHourlyWorker;
> // constructor Workers
> public Workers()
> {
> super( "Add Staff Members" );
> // The URL specifying the workers database to which this program
> // connects to using JDBC
>
> String url = "jdbc:derby:workers";
> // Load the driver to allow connection to the database
> try {
> Class.forName( "org.apache.derby.jdbc.EmbeddedDriver" );
> connection = DriverManager.getConnection( url );
> }
> catch ( ClassNotFoundException cnfex ) {
> System.err.println( "Failed to load JDBC driver." );
> cnfex.printStackTrace();
> System.exit( 1 ); // terminate program
> }
> catch ( SQLException sqlex ) {
> System.err.println( "Unable to connect" );
> sqlex.printStackTrace();
> System.exit( 1 ); // terminate program
> }
> // if connected to database, set up GUI
> JPanel topPanel = new JPanel();
> topPanel.setLayout( new FlowLayout() );
> topPanel.add( new JLabel( "Enter query to insert workers:" ) );
> input = new JTextField( 50 );
> topPanel.add( input );
> input.addActionListener(
> new ActionListener() {
>
> public void actionPerformed( ActionEvent e )
> {
> addWorker( input.getText() );
> }
> }
> );
> // create four buttons that allow user to add specific employee
> JPanel centerPanel = new JPanel();
> centerPanel.setLayout( new FlowLayout() );
>
> addSalariedWorker = new JButton( "Add Salaried Worker" );
> addSalariedWorker.addActionListener( new ButtonHandler() );
> addCommissionWorker = new JButton( "Add Commission Worker" );
> addCommissionWorker.addActionListener( new ButtonHandler() );
> addBasePlusCommissionWorker =
> new JButton( "Add Base Plus Commission Worker" );
> addBasePlusCommissionWorker.addActionListener(
> new ButtonHandler() );
> addHourlyWorker = new JButton( "Add Hourly Worker" );
> addHourlyWorker.addActionListener( new ButtonHandler() );
> // add four buttons to centerPanel
> centerPanel.add( addSalariedWorker );
> centerPanel.add( addCommissionWorker );
> centerPanel.add( addBasePlusCommissionWorker );
> centerPanel.add( addHourlyWorker );
> JPanel inputPanel = new JPanel();
> inputPanel.setLayout( new BorderLayout() );
> inputPanel.add( topPanel, BorderLayout.NORTH );
> inputPanel.add( centerPanel, BorderLayout.CENTER );
> table = new JTable( 4, 4 );
> container = getContentPane();
> container.setLayout( new BorderLayout() );
> container.add( inputPanel, BorderLayout.NORTH );
> container.add( table, BorderLayout.CENTER );
> getTable();
> setSize( 800, 300 );
> setVisible( true );
> } // end constructor Workers
> private void getTable()
> {
> try {
> statement = connection.createStatement();
> resultSet = statement.executeQuery( "SELECT * FROM workers"
> );
> displayResultSet( resultSet );
> }
> catch ( SQLException sqlex ) {
> sqlex.printStackTrace();
> }
> }
> private void addWorker( String query )
> {
> try {
> statement = connection.createStatement();
> statement.executeUpdate( query );
> getTable();
> }
> catch ( SQLException sqlex ) {
> sqlex.printStackTrace();
> }
> }
> private void displayResultSet( ResultSet rs ) throws SQLException
> {
> // position to first record
> boolean moreRecords = rs.next();
> // if there are no records, display a message
> if ( !moreRecords ) {
> JOptionPane.showMessageDialog( this,
> "ResultSet contained no records" );
> return;
> }
> Vector<Object> columnHeads = new Vector<Object>();
> Vector<Object> rows = new Vector<Object>();
> try {
> // get column heads
> ResultSetMetaData rsmd = rs.getMetaData();
>
> for ( int i = 1; i <= rsmd.getColumnCount(); ++i )
> columnHeads.addElement( rsmd.getColumnName( i ) );
> // get row data
> do {
> rows.addElement( getNextRow( rs, rsmd ) );
> } while ( rs.next() );
> // display table with ResultSet contents
> table = new JTable( rows, columnHeads );
> JScrollPane scroller = new JScrollPane( table );
> container.remove( 1 );
> container.add( scroller, BorderLayout.CENTER );
> container.validate();
> } // end try
> catch ( SQLException sqlex ) {
> sqlex.printStackTrace();
> }
> } // end method displayResultSet
> private Vector getNextRow( ResultSet rs,
> ResultSetMetaData rsmd ) throws SQLException
> {
> Vector<Object> currentRow = new Vector<Object>();
>
> for ( int i = 1; i <= rsmd.getColumnCount(); ++i )
> switch( rsmd.getColumnType( i ) ) {
> case Types.VARCHAR:
> case Types.LONGVARCHAR:
> currentRow.addElement( rs.getString( i ) );
> break;
> case Types.INTEGER:
> currentRow.addElement( new Long( rs.getLong( i ) ) );
> break;
> case Types.REAL:
> currentRow.addElement( new Float( rs.getDouble( i ) ) );
> break;
> case Types.DATE:
> currentRow.addElement( rs.getDate( i ) );
> break;
> default:
> System.out.println( "Type was: " +
> rsmd.getColumnTypeName( i ) );
> }
>
> return currentRow;
> } // end method getNextRow
> public void shutDown()
> {
> try {
> connection.close();
> }
> catch ( SQLException sqlex ) {
> System.err.println( "Unable to disconnect" );
> sqlex.printStackTrace();
> }
> }
> public static void main(String[] args)
> {
> final Workers application = new Workers();
> application.addWindowListener(
> new WindowAdapter() {
> public void windowClosing( WindowEvent e )
> {
> application.shutDown();
> System.exit( 0 );
> }
> }
> );
> }
>
> // inner class ButtonHandler handles button event
> private class ButtonHandler implements ActionListener {
> public void actionPerformed( ActionEvent event )
> {
> String socialSecurityNumber = JOptionPane.showInputDialog(
> "Employee Social Security Number" );
> String insertQuery = "", displayQuery = "";
> // add salaried employee to table salariedWorkers
> if ( event.getSource() == addSalariedWorker ) {
> double weeklySalary = Double.parseDouble(
> JOptionPane.showInputDialog( "Weekly Salary:" ) );
> insertQuery = "INSERT INTO salariedWorkers VALUES ( '" +
> socialSecurityNumber + "', '" + weeklySalary + "', '0' )";
> displayQuery = "SELECT workers.socialSecurityNumber, " +
> "workers.firstName, workers.lastName, " +
> "workers.employeeType, salariedWorkers.weeklySalary" +
> " FROM workers, salariedWorkers WHERE " +
> "workers.socialSecurityNumber = " +
> "salariedWorkers.socialSecurityNumber";
> }
> // add commission employee to table commissionWorker
> else if ( event.getSource() == addCommissionWorker ) {
> int grossSales = Integer.parseInt(
> JOptionPane.showInputDialog( "Gross Sales:" ) );
> double commissionRate = Double.parseDouble(
> JOptionPane.showInputDialog( "Commission Rate:" ) );
> insertQuery = "INSERT INTO commissionWorkers VALUES ( '" +
> socialSecurityNumber + "', '" + grossSales + "', '" +
> commissionRate + "', '0' )";
> displayQuery = "SELECT workers.socialSecurityNumber, " +
> "workers.firstName, workers.lastName, " +
> "workers.employeeType, commissionWorkers.grossSales," +
> " commissionWorkers.commissionRate FROM workers, " +
> "commissionWorkers WHERE workers.socialSecurityNumber="
> + "commissionWorkers.socialSecurityNumber";
> }
> // add base plus commission employee to table
> // basePlusCommissionEmployee
> else if ( event.getSource() == addBasePlusCommissionWorker ) {
> int grossSales = Integer.parseInt(
> JOptionPane.showInputDialog( "Gross Sales:" ) );
> double commissionRate = Double.parseDouble(
> JOptionPane.showInputDialog( "Commission Rate:" ) );
> double baseSalary = Double.parseDouble(
> JOptionPane.showInputDialog( "Base Salary:" ) );
> insertQuery = "INSERT INTO basePlusCommissionWorkers " +
> "VALUES ( '" + socialSecurityNumber + "', '" +
> grossSales +
> "', '" + commissionRate + "', '" + baseSalary + "', '0' )";
> displayQuery = "SELECT workers.socialSecurityNumber, " +
> "workers.firstName, workers.lastName, workers." +
> "employeeType, basePlusCommissionWorkers.baseSalary, " +
> "basePlusCommissionWorkers.grossSales, basePlus" +
> "CommissionWorkers.commissionRate FROM workers, " +
> "basePlusCommissionWorkers WHERE " +
> "workers.socialSecurityNumber = " +
> "basePlusCommissionWorkers.socialSecurityNumber";
> }
> // add hourly employee to table hourlyEmployee
> else {
> int hours = Integer.parseInt(
> JOptionPane.showInputDialog( "Hours:" ) );
> double wage = Double.parseDouble(
> JOptionPane.showInputDialog( "Wage:" ) );
> insertQuery = "INSERT INTO hourlyWorkers VALUES ( '" +
> socialSecurityNumber + "', '" + hours + "', '" + wage +
> "', '0' )";
> displayQuery = "SELECT workers.socialSecurityNumber, " +
> "workers.firstName, workers.lastName, " +
> "workers.employeeType, hourlyWorkers.hours, " +
> "hourlyWorkers.wage FROM workers, hourlyWorkers " +
> "WHERE workers.socialSecurityNumber = " +
> "hourlyWorkers.socialSecurityNumber";
> }
> // execute insert query and display employee info
> try {
> statement = connection.createStatement();
> statement.executeUpdate( insertQuery );
>
> // display the employee info
> statement = connection.createStatement();
> resultSet = statement.executeQuery( displayQuery );
> displayResultSet( resultSet );
> }
> catch ( SQLException exception ) {
> exception.printStackTrace();
> }
>
> } // end method actionPerformed
> } // end inner class ButtonHandler
> } // end class Workers
>
>
> SQL file
> connect 'jdbc:derby:workers;create=true'
> ;
> drop table workers
> ;
> drop table salariedWorkers
> ;
> drop table commissionWorkers
> ;
> drop table basePlusCommissionWorkers
> ;
> drop table hourlyWorkers
> ;
> create table workers (
> socialSecurityNumber varchar (30) NOT NULL,
> firstName varchar (30) NOT NULL,
> lastName varchar (30) NOT NULL,
> birthday date NOT NULL,
> employeeType varchar (30) NOT NULL,
> departmentName varchar (30) NOT NULL,
> constraint pk_workers primary key (socialSecurityNumber)
> )
> ;
> create table salariedWorkers (
> socialsecurityNumber varchar (30) NOT NULL,
> weeklySalary real NOT NULL,
> bonus real,
> constraint fk_salariedWorkers foreign key (socialSecurityNumber)
> references workers (socialSecurityNumber)
> )
> ;
> create table commissionWorkers (
> socialSecurityNumber varchar (30) NOT NULL,
> grossSales int NOT NULL,
> commissionRate real NOT NULL,
> bonus real,
> constraint fk_commissionWorkers foreign key (socialSecurityNumber)
> references workers (socialSecurityNumber)
> )
> ;
> create table basePlusCommissionWorkers (
> socialSecurityNumber varchar (30) NOT NULL,
> grossSales int NOT NULL,
> commissionRate real NOT NULL,
> baseSalary real NOT NULL,
> bonus real,
> constraint fk_basePlusCommissionWorkers foreign key
> (socialSecurityNumber)
> references workers (socialSecurityNumber)
> )
> ;
> create table hourlyWorkers (
> socialSecurityNumber varchar (30) NOT NULL,
> hours int NOT NULL,
> wage real NOT NULL,
> bonus real,
> constraint fk_hourlyWorkers foreign key (socialSecurityNumber)
> references workers (socialSecurityNumber)
> )
> ;
>
>
>
>
>
> ------------------------------------------------------------------------
> Want to start your own business? Learn how on Yahoo! Small Business.
> <http://us.rd.yahoo.com/evt=41244/*http://smallbusiness.yahoo.com/r-index>
Hi William -
So I guess this would be the query associated with the error (see the
derby.log file to see for sure):
"INSERT INTO salariedWorkers VALUES ( '" +
socialSecurityNumber + "', '" + weeklySalary + "', '0' )";
and weeklySalary and bonus are both datatype: real so should not be
quoted in your VALUES clause.
Re: Columns of type 'REAL' cannot hold values of type 'CHAR'
Posted by Dan Scott <de...@gmail.com>.
If I'm reading this correctly, you're putting single-quote delimiters
around the REAL value:
insertQuery = "INSERT INTO salariedWorkers VALUES ( '" +
socialSecurityNumber + "', '" + weeklySalary + "', '0' )";
MySQL would accept that, but Derby is a lot stricter. Drop the
single-quotes if you want to stick with the concatenated string
approach to creating an SQL statement -- but better yet, use a
prepared statement with parameter markers, something like:
PreparedStatement insertQuery = con.prepareStatement("INSERT INTO
salariedWorkers VALUES ( ?, ?, ?)");
insertQuery.setString(1, socialSecurityNumber);
insertQuery.setFloat(2, salary);
insertQuery.setFloat(3, 0);
insertQuery.executeUpdate();
(might need to adjust the data types slightly here, but you get the idea)
Dan
On 13/12/06, William B. <wi...@yahoo.com> wrote:
> I keep receiving the error message above when I try to add a salary. Please
> help
>
> import java.sql.*;
> import java.awt.*;
> import java.awt.event.*;
> import java.util.*;
> import javax.swing.*;
>
> public class Workers extends JFrame {
> private Connection connection;
> private Statement statement;
> private ResultSet resultSet;
> private ResultSetMetaData rsMetaData;
> private Container container;
> private JTable table;
> private JTextField input;
> private JButton addSalariedWorker, addCommissionWorker,
> addBasePlusCommissionWorker, addHourlyWorker;
> // constructor Workers
> public Workers()
> {
> super( "Add Staff Members" );
> // The URL specifying the workers database to which this program
> // connects to using JDBC
>
> String url = "jdbc:derby:workers";
> // Load the driver to allow connection to the database
> try {
> Class.forName(
> "org.apache.derby.jdbc.EmbeddedDriver" );
> connection = DriverManager.getConnection( url );
> }
> catch ( ClassNotFoundException cnfex ) {
> System.err.println( "Failed to load JDBC driver." );
> cnfex.printStackTrace();
> System.exit( 1 ); // terminate program
> }
> catch ( SQLException sqlex ) {
> System.err.println( "Unable to connect" );
> sqlex.printStackTrace();
> System.exit( 1 ); // terminate program
> }
> // if connected to database, set up GUI
> JPanel topPanel = new JPanel();
> topPanel.setLayout( new FlowLayout() );
> topPanel.add( new JLabel( "Enter query to insert workers:" ) );
> input = new JTextField( 50 );
> topPanel.add( input );
> input.addActionListener(
> new ActionListener() {
>
> public void actionPerformed( ActionEvent e )
> {
> addWorker( input.getText() );
> }
> }
> );
> // create four buttons that allow user to add specific employee
> JPanel centerPanel = new JPanel();
> centerPanel.setLayout( new FlowLayout() );
>
> addSalariedWorker = new JButton( "Add Salaried Worker" );
> addSalariedWorker.addActionListener( new
> ButtonHandler() );
> addCommissionWorker = new JButton( "Add Commission Worker" );
> addCommissionWorker.addActionListener( new
> ButtonHandler() );
> addBasePlusCommissionWorker =
> new JButton( "Add Base Plus Commission Worker" );
> addBasePlusCommissionWorker.addActionListener(
> new ButtonHandler() );
> addHourlyWorker = new JButton( "Add Hourly Worker" );
> addHourlyWorker.addActionListener( new
> ButtonHandler() );
> // add four buttons to centerPanel
> centerPanel.add( addSalariedWorker );
> centerPanel.add( addCommissionWorker );
> centerPanel.add( addBasePlusCommissionWorker );
> centerPanel.add( addHourlyWorker );
> JPanel inputPanel = new JPanel();
> inputPanel.setLayout( new BorderLayout() );
> inputPanel.add( topPanel, BorderLayout.NORTH );
> inputPanel.add( centerPanel, BorderLayout.CENTER );
> table = new JTable( 4, 4 );
> container = getContentPane();
> container.setLayout( new BorderLayout() );
> container.add( inputPanel, BorderLayout.NORTH );
> container.add( table, BorderLayout.CENTER );
> getTable();
> setSize( 800, 300 );
> setVisible( true );
> } // end constructor Workers
> private void getTable()
> {
> try {
> statement = connection.createStatement();
> resultSet = statement.executeQuery( "SELECT * FROM workers" );
>
> displayResultSet( resultSet );
> }
> catch ( SQLException sqlex ) {
> sqlex.printStackTrace();
> }
> }
> private void addWorker( String query )
> {
> try {
> statement = connection.createStatement();
> statement.executeUpdate( query );
> getTable();
> }
> catch ( SQLException sqlex ) {
> sqlex.printStackTrace();
> }
> }
> private void displayResultSet( ResultSet rs ) throws SQLException
> {
> // position to first record
> boolean moreRecords = rs.next();
> // if there are no records, display a message
> if ( !moreRecords ) {
> JOptionPane.showMessageDialog( this,
> "ResultSet contained no records" );
> return;
> }
> Vector<Object> columnHeads = new Vector<Object>();
> Vector<Object> rows = new Vector<Object>();
> try {
> // get column heads
> ResultSetMetaData rsmd = rs.getMetaData();
>
> for ( int i = 1; i <= rsmd.getColumnCount(); ++i )
> columnHeads.addElement( rsmd.getColumnName( i ) );
> // get row data
> do {
> rows.addElement( getNextRow( rs, rsmd ) );
> } while ( rs.next() );
> // display table with ResultSet contents
> table = new JTable( rows, columnHeads );
> JScrollPane scroller = new JScrollPane( table );
> container.remove( 1 );
> container.add( scroller, BorderLayout.CENTER );
> container.validate();
> } // end try
> catch ( SQLException sqlex ) {
> sqlex.printStackTrace();
> }
> } // end method displayResultSet
> private Vector getNextRow( ResultSet rs,
> ResultSetMetaData rsmd ) throws SQLException
> {
> Vector<Object> currentRow = new Vector<Object>();
>
> for ( int i = 1; i <= rsmd.getColumnCount(); ++i )
> switch( rsmd.getColumnType( i ) ) {
> case Types.VARCHAR:
> case Types.LONGVARCHAR:
> currentRow.addElement( rs.getString( i ) );
> break;
> case Types.INTEGER:
> currentRow.addElement( new Long( rs.getLong( i ) ) );
> break;
> case Types.REAL:
> currentRow.addElement( new Float( rs.getDouble( i ) ) );
> break;
> case Types.DATE:
> currentRow.addElement( rs.getDate( i ) );
> break;
> default:
> System.out.println( "Type was: " +
> rsmd.getColumnTypeName( i ) );
> }
>
> return currentRow;
> } // end method getNextRow
> public void shutDown()
> {
> try {
> connection.close();
> }
> catch ( SQLException sqlex ) {
> System.err.println( "Unable to disconnect" );
> sqlex.printStackTrace();
> }
> }
> public static void main(String[] args)
> {
> final Workers application = new Workers();
> application.addWindowListener(
> new WindowAdapter() {
> public void windowClosing( WindowEvent e )
> {
> application.shutDown();
> System.exit( 0 );
> }
> }
> );
> }
>
> // inner class ButtonHandler handles button event
> private class ButtonHandler implements ActionListener {
> public void actionPerformed( ActionEvent event )
> {
> String socialSecurityNumber = JOptionPane.showInputDialog(
> "Employee Social Security Number" );
> String insertQuery = "", displayQuery = "";
> // add salaried employee to table salariedWorkers
> if ( event.getSource() == addSalariedWorker ) {
> double weeklySalary = Double.parseDouble(
> JOptionPane.showInputDialog( "Weekly Salary:" ) );
> insertQuery = "INSERT INTO salariedWorkers VALUES ( '" +
> socialSecurityNumber + "', '" + weeklySalary + "', '0' )";
> displayQuery = "SELECT workers.socialSecurityNumber, " +
> "workers.firstName, workers.lastName, " +
> "workers.employeeType, salariedWorkers.weeklySalary" +
> " FROM workers, salariedWorkers WHERE " +
> "workers.socialSecurityNumber = " +
> "salariedWorkers.socialSecurityNumber";
> }
> // add commission employee to table commissionWorker
> else if ( event.getSource() == addCommissionWorker ) {
> int grossSales = Integer.parseInt(
> JOptionPane.showInputDialog( "Gross Sales:" ) );
> double commissionRate = Double.parseDouble(
> JOptionPane.showInputDialog( "Commission Rate:" ) );
> insertQuery = "INSERT INTO commissionWorkers VALUES ( '" +
> socialSecurityNumber + "', '" + grossSales + "', '" +
> commissionRate + "', '0' )";
> displayQuery = "SELECT workers.socialSecurityNumber, " +
> "workers.firstName, workers.lastName, " +
> "workers.employeeType, commissionWorkers.grossSales," +
> " commissionWorkers.commissionRate FROM
> workers, " +
> "commissionWorkers WHERE workers.socialSecurityNumber="
> + "commissionWorkers.socialSecurityNumber";
> }
> // add base plus commission employee to table
> // basePlusCommissionEmployee
> else if ( event.getSource() == addBasePlusCommissionWorker ) {
> int grossSales = Integer.parseInt(
> JOptionPane.showInputDialog( "Gross Sales:" ) );
> double commissionRate = Double.parseDouble(
> JOptionPane.showInputDialog( "Commission Rate:" ) );
> double baseSalary = Double.parseDouble(
> JOptionPane.showInputDialog( "Base Salary:" ) );
> insertQuery = "INSERT INTO basePlusCommissionWorkers " +
> "VALUES ( '" + socialSecurityNumber + "', '" + grossSales +
> "', '" + commissionRate + "', '" + baseSalary + "', '0' )";
> displayQuery = "SELECT workers.socialSecurityNumber, " +
> "workers.firstName, workers.lastName, workers." +
> "employeeType,
> basePlusCommissionWorkers.baseSalary, " +
> "basePlusCommissionWorkers.grossSales,
> basePlus" +
> "CommissionWorkers.commissionRate FROM
> workers, " +
> "basePlusCommissionWorkers WHERE " +
> "workers.socialSecurityNumber = " +
>
> "basePlusCommissionWorkers.socialSecurityNumber";
> }
> // add hourly employee to table hourlyEmployee
> else {
> int hours = Integer.parseInt(
> JOptionPane.showInputDialog( "Hours:" ) );
> double wage = Double.parseDouble(
> JOptionPane.showInputDialog( "Wage:" ) );
> insertQuery = "INSERT INTO hourlyWorkers VALUES ( '" +
> socialSecurityNumber + "', '" + hours + "', '" + wage +
> "', '0' )";
> displayQuery = "SELECT workers.socialSecurityNumber, " +
> "workers.firstName, workers.lastName, " +
> "workers.employeeType, hourlyWorkers.hours, " +
> "hourlyWorkers.wage FROM workers, hourlyWorkers " +
> "WHERE workers.socialSecurityNumber = " +
> "hourlyWorkers.socialSecurityNumber";
> }
> // execute insert query and display employee info
> try {
> statement = connection.createStatement();
> statement.executeUpdate( insertQuery );
>
> // display the employee info
> statement = connection.createStatement();
> resultSet = statement.executeQuery( displayQuery );
> displayResultSet( resultSet );
> }
> catch ( SQLException exception ) {
> exception.printStackTrace();
> }
>
> } // end method actionPerformed
> } // end inner class ButtonHandler
> } // end class Workers
>
>
> SQL file
> connect 'jdbc:derby:workers;create=true'
> ;
> drop table workers
> ;
> drop table salariedWorkers
> ;
> drop table commissionWorkers
> ;
> drop table basePlusCommissionWorkers
> ;
> drop table hourlyWorkers
> ;
> create table workers (
> socialSecurityNumber varchar (30) NOT NULL,
> firstName varchar (30) NOT NULL,
> lastName varchar (30) NOT NULL,
> birthday date NOT NULL,
> employeeType varchar (30) NOT NULL,
> departmentName varchar (30) NOT NULL,
> constraint pk_workers primary key (socialSecurityNumber)
> )
> ;
> create table salariedWorkers (
> socialsecurityNumber varchar (30) NOT NULL,
> weeklySalary real NOT NULL,
> bonus real,
> constraint fk_salariedWorkers foreign key (socialSecurityNumber)
> references workers (socialSecurityNumber)
> )
> ;
> create table commissionWorkers (
> socialSecurityNumber varchar (30) NOT NULL,
> grossSales int NOT NULL,
> commissionRate real NOT NULL,
> bonus real,
> constraint fk_commissionWorkers foreign key (socialSecurityNumber)
> references workers (socialSecurityNumber)
> )
> ;
> create table basePlusCommissionWorkers (
> socialSecurityNumber varchar (30) NOT NULL,
> grossSales int NOT NULL,
> commissionRate real NOT NULL,
> baseSalary real NOT NULL,
> bonus real,
> constraint fk_basePlusCommissionWorkers foreign key (socialSecurityNumber)
> references workers (socialSecurityNumber)
> )
> ;
> create table hourlyWorkers (
> socialSecurityNumber varchar (30) NOT NULL,
> hours int NOT NULL,
> wage real NOT NULL,
> bonus real,
> constraint fk_hourlyWorkers foreign key (socialSecurityNumber)
> references workers (socialSecurityNumber)
> )
> ;
>
>
>
>
>
> ________________________________
> Want to start your own business? Learn how on Yahoo! Small Business.
>
>
Re: Columns of type 'REAL' cannot hold values of type 'CHAR'
Posted by Craig L Russell <Cr...@Sun.COM>.
Hi,
I'd like to suggest that you avoid the pattern of dynamic
construction of SQL, as in:
> insertQuery = "INSERT INTO basePlusCommissionWorkers " +
> "VALUES ( '" + socialSecurityNumber + "', '" +
> grossSales +
> "', '" + commissionRate + "', '" + baseSalary + "',
> '0' )";
>
Instead, use prepared statements and bind values from your GUI into
the statements. For example,
> insertQuery = "INSERT INTO basePlusCommissionWorkers " +
> "VALUES ( ?, ?, ?, ?, '0')";
The application will run much faster (and you will avoid the kinds of
issues you are having here).
Craig
On Dec 13, 2006, at 1:59 PM, William B. wrote:
> I keep receiving the error message above when I try to add a
> salary. Please help
>
> import java.sql.*;
> import java.awt.*;
> import java.awt.event.*;
> import java.util.*;
> import javax.swing.*;
>
> public class Workers extends JFrame {
> private Connection connection;
> private Statement statement;
> private ResultSet resultSet;
> private ResultSetMetaData rsMetaData;
> private Container container;
> private JTable table;
> private JTextField input;
> private JButton addSalariedWorker, addCommissionWorker,
> addBasePlusCommissionWorker, addHourlyWorker;
> // constructor Workers
> public Workers()
> {
> super( "Add Staff Members" );
> // The URL specifying the workers database to which this program
> // connects to using JDBC
>
> String url = "jdbc:derby:workers";
> // Load the driver to allow connection to the database
> try {
> Class.forName( "org.apache.derby.jdbc.EmbeddedDriver" );
> connection = DriverManager.getConnection( url );
> }
> catch ( ClassNotFoundException cnfex ) {
> System.err.println( "Failed to load JDBC driver." );
> cnfex.printStackTrace();
> System.exit( 1 ); // terminate program
> }
> catch ( SQLException sqlex ) {
> System.err.println( "Unable to connect" );
> sqlex.printStackTrace();
> System.exit( 1 ); // terminate program
> }
> // if connected to database, set up GUI
> JPanel topPanel = new JPanel();
> topPanel.setLayout( new FlowLayout() );
> topPanel.add( new JLabel( "Enter query to insert workers:" ) );
> input = new JTextField( 50 );
> topPanel.add( input );
> input.addActionListener(
> new ActionListener() {
>
> public void actionPerformed( ActionEvent e )
> {
> addWorker( input.getText() );
> }
> }
> );
> // create four buttons that allow user to add specific employee
> JPanel centerPanel = new JPanel();
> centerPanel.setLayout( new FlowLayout() );
>
> addSalariedWorker = new JButton( "Add Salaried Worker" );
> addSalariedWorker.addActionListener( new ButtonHandler() );
> addCommissionWorker = new JButton( "Add Commission Worker" );
> addCommissionWorker.addActionListener( new ButtonHandler() );
> addBasePlusCommissionWorker =
> new JButton( "Add Base Plus Commission Worker" );
> addBasePlusCommissionWorker.addActionListener(
> new ButtonHandler() );
> addHourlyWorker = new JButton( "Add Hourly Worker" );
> addHourlyWorker.addActionListener( new ButtonHandler() );
> // add four buttons to centerPanel
> centerPanel.add( addSalariedWorker );
> centerPanel.add( addCommissionWorker );
> centerPanel.add( addBasePlusCommissionWorker );
> centerPanel.add( addHourlyWorker );
> JPanel inputPanel = new JPanel();
> inputPanel.setLayout( new BorderLayout() );
> inputPanel.add( topPanel, BorderLayout.NORTH );
> inputPanel.add( centerPanel, BorderLayout.CENTER );
> table = new JTable( 4, 4 );
> container = getContentPane();
> container.setLayout( new BorderLayout() );
> container.add( inputPanel, BorderLayout.NORTH );
> container.add( table, BorderLayout.CENTER );
> getTable();
> setSize( 800, 300 );
> setVisible( true );
> } // end constructor Workers
> private void getTable()
> {
> try {
> statement = connection.createStatement();
> resultSet = statement.executeQuery( "SELECT * FROM
> workers" );
> displayResultSet( resultSet );
> }
> catch ( SQLException sqlex ) {
> sqlex.printStackTrace();
> }
> }
> private void addWorker( String query )
> {
> try {
> statement = connection.createStatement();
> statement.executeUpdate( query );
> getTable();
> }
> catch ( SQLException sqlex ) {
> sqlex.printStackTrace();
> }
> }
> private void displayResultSet( ResultSet rs ) throws SQLException
> {
> // position to first record
> boolean moreRecords = rs.next();
> // if there are no records, display a message
> if ( !moreRecords ) {
> JOptionPane.showMessageDialog( this,
> "ResultSet contained no records" );
> return;
> }
> Vector<Object> columnHeads = new Vector<Object>();
> Vector<Object> rows = new Vector<Object>();
> try {
> // get column heads
> ResultSetMetaData rsmd = rs.getMetaData();
>
> for ( int i = 1; i <= rsmd.getColumnCount(); ++i )
> columnHeads.addElement( rsmd.getColumnName( i ) );
> // get row data
> do {
> rows.addElement( getNextRow( rs, rsmd ) );
> } while ( rs.next() );
> // display table with ResultSet contents
> table = new JTable( rows, columnHeads );
> JScrollPane scroller = new JScrollPane( table );
> container.remove( 1 );
> container.add( scroller, BorderLayout.CENTER );
> container.validate();
> } // end try
> catch ( SQLException sqlex ) {
> sqlex.printStackTrace();
> }
> } // end method displayResultSet
> private Vector getNextRow( ResultSet rs,
> ResultSetMetaData rsmd ) throws SQLException
> {
> Vector<Object> currentRow = new Vector<Object>();
>
> for ( int i = 1; i <= rsmd.getColumnCount(); ++i )
> switch( rsmd.getColumnType( i ) ) {
> case Types.VARCHAR:
> case Types.LONGVARCHAR:
> currentRow.addElement( rs.getString( i ) );
> break;
> case Types.INTEGER:
> currentRow.addElement( new Long( rs.getLong( i ) ) );
> break;
> case Types.REAL:
> currentRow.addElement( new Float( rs.getDouble
> ( i ) ) );
> break;
> case Types.DATE:
> currentRow.addElement( rs.getDate( i ) );
> break;
> default:
> System.out.println( "Type was: " +
> rsmd.getColumnTypeName( i ) );
> }
>
> return currentRow;
> } // end method getNextRow
> public void shutDown()
> {
> try {
> connection.close();
> }
> catch ( SQLException sqlex ) {
> System.err.println( "Unable to disconnect" );
> sqlex.printStackTrace();
> }
> }
> public static void main(String[] args)
> {
> final Workers application = new Workers();
> application.addWindowListener(
> new WindowAdapter() {
> public void windowClosing( WindowEvent e )
> {
> application.shutDown();
> System.exit( 0 );
> }
> }
> );
> }
>
> // inner class ButtonHandler handles button event
> private class ButtonHandler implements ActionListener {
> public void actionPerformed( ActionEvent event )
> {
> String socialSecurityNumber = JOptionPane.showInputDialog(
> "Employee Social Security Number" );
> String insertQuery = "", displayQuery = "";
> // add salaried employee to table salariedWorkers
> if ( event.getSource() == addSalariedWorker ) {
> double weeklySalary = Double.parseDouble(
> JOptionPane.showInputDialog( "Weekly Salary:" ) );
> insertQuery = "INSERT INTO salariedWorkers VALUES ( '" +
> socialSecurityNumber + "', '" + weeklySalary + "',
> '0' )";
> displayQuery = "SELECT workers.socialSecurityNumber, " +
> "workers.firstName, workers.lastName, " +
> "workers.employeeType, salariedWorkers.weeklySalary" +
> " FROM workers, salariedWorkers WHERE " +
> "workers.socialSecurityNumber = " +
> "salariedWorkers.socialSecurityNumber";
> }
> // add commission employee to table commissionWorker
> else if ( event.getSource() == addCommissionWorker ) {
> int grossSales = Integer.parseInt(
> JOptionPane.showInputDialog( "Gross Sales:" ) );
> double commissionRate = Double.parseDouble(
> JOptionPane.showInputDialog( "Commission Rate:" ) );
> insertQuery = "INSERT INTO commissionWorkers VALUES ( '" +
> socialSecurityNumber + "', '" + grossSales + "', '" +
> commissionRate + "', '0' )";
> displayQuery = "SELECT workers.socialSecurityNumber, " +
> "workers.firstName, workers.lastName, " +
> "workers.employeeType, commissionWorkers.grossSales," +
> " commissionWorkers.commissionRate FROM workers, " +
> "commissionWorkers WHERE workers.socialSecurityNumber="
> + "commissionWorkers.socialSecurityNumber";
> }
> // add base plus commission employee to table
> // basePlusCommissionEmployee
> else if ( event.getSource() ==
> addBasePlusCommissionWorker ) {
> int grossSales = Integer.parseInt(
> JOptionPane.showInputDialog( "Gross Sales:" ) );
> double commissionRate = Double.parseDouble(
> JOptionPane.showInputDialog( "Commission Rate:" ) );
> double baseSalary = Double.parseDouble(
> JOptionPane.showInputDialog( "Base Salary:" ) );
> insertQuery = "INSERT INTO basePlusCommissionWorkers " +
> "VALUES ( '" + socialSecurityNumber + "', '" +
> grossSales +
> "', '" + commissionRate + "', '" + baseSalary + "',
> '0' )";
> displayQuery = "SELECT workers.socialSecurityNumber, " +
> "workers.firstName, workers.lastName, workers." +
> "employeeType, basePlusCommissionWorkers.baseSalary,
> " +
> "basePlusCommissionWorkers.grossSales, basePlus" +
> "CommissionWorkers.commissionRate FROM workers, " +
> "basePlusCommissionWorkers WHERE " +
> "workers.socialSecurityNumber = " +
> "basePlusCommissionWorkers.socialSecurityNumber";
> }
> // add hourly employee to table hourlyEmployee
> else {
> int hours = Integer.parseInt(
> JOptionPane.showInputDialog( "Hours:" ) );
> double wage = Double.parseDouble(
> JOptionPane.showInputDialog( "Wage:" ) );
> insertQuery = "INSERT INTO hourlyWorkers VALUES ( '" +
> socialSecurityNumber + "', '" + hours + "', '" + wage +
> "', '0' )";
> displayQuery = "SELECT workers.socialSecurityNumber, " +
> "workers.firstName, workers.lastName, " +
> "workers.employeeType, hourlyWorkers.hours, " +
> "hourlyWorkers.wage FROM workers, hourlyWorkers " +
> "WHERE workers.socialSecurityNumber = " +
> "hourlyWorkers.socialSecurityNumber";
> }
> // execute insert query and display employee info
> try {
> statement = connection.createStatement();
> statement.executeUpdate( insertQuery );
>
> // display the employee info
> statement = connection.createStatement();
> resultSet = statement.executeQuery( displayQuery );
> displayResultSet( resultSet );
> }
> catch ( SQLException exception ) {
> exception.printStackTrace();
> }
>
> } // end method actionPerformed
> } // end inner class ButtonHandler
> } // end class Workers
>
>
> SQL file
> connect 'jdbc:derby:workers;create=true'
> ;
> drop table workers
> ;
> drop table salariedWorkers
> ;
> drop table commissionWorkers
> ;
> drop table basePlusCommissionWorkers
> ;
> drop table hourlyWorkers
> ;
> create table workers (
> socialSecurityNumber varchar (30) NOT NULL,
> firstName varchar (30) NOT NULL,
> lastName varchar (30) NOT NULL,
> birthday date NOT NULL,
> employeeType varchar (30) NOT NULL,
> departmentName varchar (30) NOT NULL,
> constraint pk_workers primary key (socialSecurityNumber)
> )
> ;
> create table salariedWorkers (
> socialsecurityNumber varchar (30) NOT NULL,
> weeklySalary real NOT NULL,
> bonus real,
> constraint fk_salariedWorkers foreign key (socialSecurityNumber)
> references workers (socialSecurityNumber)
> )
> ;
> create table commissionWorkers (
> socialSecurityNumber varchar (30) NOT NULL,
> grossSales int NOT NULL,
> commissionRate real NOT NULL,
> bonus real,
> constraint fk_commissionWorkers foreign key (socialSecurityNumber)
> references workers (socialSecurityNumber)
> )
> ;
> create table basePlusCommissionWorkers (
> socialSecurityNumber varchar (30) NOT NULL,
> grossSales int NOT NULL,
> commissionRate real NOT NULL,
> baseSalary real NOT NULL,
> bonus real,
> constraint fk_basePlusCommissionWorkers foreign key
> (socialSecurityNumber)
> references workers (socialSecurityNumber)
> )
> ;
> create table hourlyWorkers (
> socialSecurityNumber varchar (30) NOT NULL,
> hours int NOT NULL,
> wage real NOT NULL,
> bonus real,
> constraint fk_hourlyWorkers foreign key (socialSecurityNumber)
> references workers (socialSecurityNumber)
> )
> ;
>
>
>
>
>
> Want to start your own business? Learn how on Yahoo! Small Business.
Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!