Posted Thursday, June 3, 2004 @ 20:38:03
I have converted an MSAccess database to MySQL and wish to use QuickTable to edit the Tables. I get a list of tables in the database from mySql and create a tree and then by clicking on on the node create a quicktable which I may wish to edit, add, delete etc. I send SELECT * FROM tablename. So I run through the columns to create the appropriate strings to send to addUpdateSql() as I don't know beforehand what changes might be required and I end up with something like:
UPDATE accounts SET Account_No=?, Company_Name=?, Company_Address=?, City=?, State=?, Postcode=?, Phone_Number=?, Fax_Number=?, Contact_Name=?, Pricing_Structure=?, Tax_Exemption=?, Credit_OK=?,1,2,3,4,5,6,7,8,9,10,11,12
but when I edit a field quicktable wants to change every column in every row in the table and will if there are no unique indexes to prevent it. Is it possible to do what I want to do which is just update the one field in the one row and if so what else is required?
public class QuickTableFrame extends JFrame
{
public QuickTableFrame(String sql, String tableName) {
//set Frame properties
pack();
Dimension screenSize = Toolkit.getDefaultToolkit().getScreenSize();
setBounds(0, 0, screenSize.width, screenSize.height - 32);
setVisible(true);
//create a new quicktable
// quick.dbtable.DBTable dBTable1 = new quick.dbtable.DBTable();
DBTable dBTable1 = new DBTable();
//add to frame
getContentPane().add(dBTable1);
//set the database driver to be used, we are using jdbc-odbc driver com.mysql.jdbc.Driver
// dBTable1.setDatabaseDriver("sun.jdbc.odbc.JdbcOdbcDriver");
dBTable1.setDatabaseDriver("com.mysql.jdbc.Driver");
dBTable1.setJdbcUrl(
"jdbc:mysql://xxxxx:3306/xxx?user=xxxx&password=xxxxxxx");
dBTable1.setSelectSql(sql); // for example: SELECT * FROM ACCOUNTS
dBTable1.setRowCountSql("SELECT count(*) from "+tableName);
dBTable1.debug = true;
dBTable1.createControlPanel();
try {
//connect to database & create a connection
dBTable1.connectDatabase();
//fetch the data from database to fill the table
dBTable1.refresh();
String uString = "";
String pString = "UPDATE " + tableName + " SET ";
if (tableName.length() != 0) {
// it's a table so work out field names
int numColumns = dBTable1.getColumnCount();
for (int n = 0; n < numColumns; n++) {
pString += dBTable1.getColumn(n).getColumnName() + "=?";
if (n != numColumns - 1)
pString += ", ";
}
for (int n = 1; n <= numColumns; n++) {
uString += n;
if (n != numColumns) uString += ",";
}
}
dBTable1.addUpdateSql(pString, uString);
}
catch (SQLException e) {
e.printStackTrace();
}
catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
QuickTable
