JDBC - how to execute queries?
Hi aii.
I a novice regarding Java, and I guess the following question was already asked, this way or another, but unforunately the information I got so far haven't helped me to solve the problem.
I'm trying to connect to a data base, by using an Access *.mbd file. The DB USN is defined correctly, and I even managed to connect the data base by code and read data from it. the problem comes up as I try to update the DB and write to it (insert new records). The program fails and terminate, though there is no reason I can see(the order and number of the fields in the record I try to add is defined correctly, and everything looks just fine...). My IDE is intelliJ, version 4, and I'm using JDK 1.4.2
Attached the code of the function that suppose to write to the DB (the line that fails the program is marked), and the sequqnce if exceptions I get during debug session.
THIS IS EXTREMELY URJENT PROBLEM AND EVERY HELP I CAN GET WILL BE HIGJLY APPRECIATED.
The code is:
private void WriteToDB(String strMessage,boolean bServerToClient)
{//bServerToClient - is the message sent from the server to the client (true) or from the client to the server(false)?
String arStrPhoneNums[] = {"053 870530", "055 655866", "050 670741", "053 744024"};
String vec[];
//create an array of 4 options
vec = new String[4];
vec[0] = "קבלתי, מטפל.";
vec[1] = "צור קשר בהמשך.";
vec[2] = "אני לא זמין.";
vec[3] = "אחזור אליך בהקדם.";
int nYear = 0, nMonth = 0, nDay = 0, nHours = 0, nMinutes = 0, nRandomNum;
Calendar rightNow = Calendar.getInstance();
try
{
nYear= rightNow.get(Calendar.YEAR);
nMonth= rightNow.get(Calendar.MONTH) + 1;//I don't know why the addition of 1 is needed, but ugging proved it for sure.
nDay= rightNow.get(Calendar.DAY_OF_MONTH);
nHours= rightNow.get(Calendar.HOUR_OF_DAY);
nMinutes = rightNow.get(Calendar.MINUTE);
}
catch(ArrayIndexOutOfBoundsException e)
{
System.err.println(e.getMessage());
}
String strMinutes = "" + nMinutes;
if(nMinutes<10)
strMinutes = "0" + strMinutes;
String strDate = "", strTime= "", strPhoneNum = "", strMessageCode = "",strValues = "", strMessageContents;
strDate += "'" +nDay + "/" + nMonth + "/" + nYear + "'";
strTime += "'" + nHours + ":" + strMinutes + "'";
//The distinction of the direction (from client to server or from server to client)
//should be reactivated later. For now, the client sends back textual message. The finall form of the
//software should allow him to send a message code ONLY.
if(bServerToClient == false) //this ia a message from the client to the server
{//the message is actually the message code, and there is no message contents
strMessageContents = "'" + vec[Integer.parseInt(strMessage)] + "'" ;
nRandomNum =(int)(Math.random() * nRndOptions );
strPhoneNum = "'" + arStrPhoneNums[nRandomNum] + "'";
nRandomNum = 1 + (int)(Math.random() * nRndOptions ) ;
strMessageCode = "'" + strMessage + "'";
}
else //this is a message from the server to the client
{//there is no message code , and there is a message contents
strMessageCode = "'" + "SERVER " + "'";
strMessageContents = "'" + strMessage + "'";
nRandomNum =(int)(Math.random() * nRndOptions );
strPhoneNum = "'" + arStrPhoneNums[nRandomNum] + "'";
}
strValues = "VALUES(" + nRecordsNum + "," + strPhoneNum + "," + strDate + "," + strTime + "," + strMessageCode + "," + strMessageContents + ")";
//load the driver to allow connection to the database
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
DBConnection = DriverManager.getConnection("jdbc:odbc:ClientServer","myLogin","myPassword");
statement = DBConnection.createStatement();
//MESSAGE STRUCT: cell ID, phone number, date, time, message code, message contents.
String strSQL ="INSERT INTO MessagesDetails " + strValues;
///////////////////////////////////////////////////////////////////// THE FOLLOWING LINE OF CODE CAUSES THE PROBLEM///////
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
statement.executeUpdate(strSQL);
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//statement.executeUpdate("INSERT INTO MessagesDetails " + strValues);
nRecordsNum++;
statement.close();
}
catch(ClassNotFoundException cnfEx)
{
System.err.println("Failed to load JDBC/ODBC driver.");
cnfEx.printStackTrace();
System.exit(1); //terminate program.
}
catch(SQLException sqlEx)
{
System.err.println("Unable to connect to DB");
System.err.println(sqlEx.getMessage());
sqlEx.printStackTrace();
}
}
The sequence of exceptions I get (due to the call to sqlEx.printStackTrace) is:
Unable to connect to DB
General error
java.sql.SQLException: General error
at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6987)
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 ServerWithDB.WriteToDB(ServerWithDB.java:321)
at ServerWithDB.sendData(ServerWithDB.java:211)
at ServerWithDB.access$000(ServerWithDB.java:14)
at ServerWithDB$1.actionPerformed(ServerWithDB.java:47)
at javax.swing.JTextField.fireActionPerformed(JTextField.java:491)
at javax.swing.JTextField.postActionEvent(JTextField.java:672)
at javax.swing.JTextField$NotifyAction.actionPerformed(JTextField.java:786)
at javax.swing.SwingUtilities.notifyAction(SwingUtilities.java:1530)
at javax.swing.JComponent.processKeyBinding(JComponent.java:2438)
at javax.swing.JComponent.processKeyBindings(JComponent.java:2473)
at javax.swing.JComponent.processKeyEvent(JComponent.java:2401)
at java.awt.Component.processEvent(Component.java:4909)
at java.awt.Container.processEvent(Container.java:1569)
at java.awt.Component.dispatchEventImpl(Component.java:3615)
at java.awt.Container.dispatchEventImpl(Container.java:1627)
at java.awt.Component.dispatchEvent(Component.java:3477)
at java.awt.KeyboardFocusManager.redispatchEvent(KeyboardFocusManager.java:1713)
at java.awt.DefaultKeyboardFocusManager.dispatchKeyEvent(DefaultKeyboardFocusManager.java:627)
at java.awt.DefaultKeyboardFocusManager.preDispatchKeyEvent(DefaultKeyboardFocusManager.java:831)
at java.awt.DefaultKeyboardFocusManager.typeAheadAssertions(DefaultKeyboardFocusManager.java:741)
at java.awt.DefaultKeyboardFocusManager.dispatchEvent(DefaultKeyboardFocusManager.java:592)
at java.awt.Component.dispatchEventImpl(Component.java:3506)
at java.awt.Container.dispatchEventImpl(Container.java:1627)
at java.awt.Window.dispatchEventImpl(Window.java:1606)
at java.awt.Component.dispatchEvent(Component.java:3477)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:456)
at java.awt.EventDispatchThread.pumpOneEventForHierarchy(EventDispatchThread.java:201)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:151)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:145)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:137)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:100)
I've been trying to get this problem for the last few days. As I allready mentioned, it is ETREMELY urgent (it may help me get a job, finally, and it has to be done NOW, or I miss the oportunity...). PLEASE HELP!!!
