ALL Java - MySQL rs.update at the same time error HELP...

[jeffbroodwar]

Hi all !

I'm currently working on a project that involves webservices..... the problem is with my database, i can't make it process two rs.updates at the same time. it throws an sqlexception.... i can see that the problem is updating at the same time... how can i fix this? you see.... my program allows multiple users to insert data in mysql database.the first solution that i have thought is with the use of a file that saves all the queries.... then make the program execute these queries one by one.... in that way, even if we have 10 users inserting records at the same time, it'll just save it to a txt file then rs.update will be done one at a time... but i hope this problem can be solved by just adding few lines..... is there any technique to avoid this problem? i tried to use setautocommit(false) ,connection.commit(), to control concurrency but it still generates error.... i hope i can make this thing work..... even if it slows down when adding 100 records at the same time, the important thing is it doesn't miss even 1 record from that 100 rs.updates.... i hope you guys can help. please post some codes too.... i'm using netbeans 5.0,Mysql 5.0... any help will be much appreciated... thanx in advance ! ^^

[r035198x]

Hi all !

I'm currently working on a project that involves webservices..... the problem is with my database, i can't make it process two rs.updates at the same time. it throws an sqlexception.... i can see that the problem is updating at the same time... how can i fix this? you see.... my program allows multiple users to insert data in mysql database.the first solution that i have thought is with the use of a file that saves all the queries.... then make the program execute these queries one by one.... in that way, even if we have 10 users inserting records at the same time, it'll just save it to a txt file then rs.update will be done one at a time... but i hope this problem can be solved by just adding few lines..... is there any technique to avoid this problem? i tried to use setautocommit(false) ,connection.commit(), to control concurrency but it still generates error.... i hope i can make this thing work..... even if it slows down when adding 100 records at the same time, the important thing is it doesn't miss even 1 record from that 100 rs.updates.... i hope you guys can help. please post some codes too.... i'm using netbeans 5.0,Mysql 5.0... any help will be much appreciated... thanx in advance ! ^^

It would have been better to see the code that's trying to do the updates at the same time or at least if you can tell us the specific sql exception that is being thrown. A simple solution is to use an arraylist to hold the sql statements as strings when they come and have a loop that retrieves the statements and executes them using one statement object

[jeffbroodwar]

Here's the code...

 ------------------------------ 
try
{ 
 
// CHECK IF CUSTOMER EXISTS THEN CHECK IF CUSTOMER ACCOUNT IS STILL ACTIVE //
cmd = "Select * from customer where customerCode = " + SQuote(customerCode);
rs = statement.executeQuery(cmd);
 
if (!rs.next()) // customer not found
{ 
lastErrorNumber = -2;
lastErrorMessage = "Customer not found";
return -2;
}
 
 
// CHECK IF CUSTOMER IS STILL ACTIVE, MARK E IF CUSTOMER ACCOUNT IS EXPIRED THEN CHECK ALL TRANSACTION'S EXPIRY DATE UNDER THAT CUSTOMER CODE //
cmd = "Select * from customer where expiryDate < " + SQuote(getDateTime()) + "and customerCode = " + SQuote(customerCode);
rs = statement.executeQuery(cmd);
 
if (rs.next()) // customer account expired
{ 
rs.updateString("status","E");
rs.updateRow();
lastErrorNumber = -6;
lastErrorMessage = "Customer account expired";
return -6;
}
// SELECT CUSTOMER IN CUSTOMER TABLE THEN PASS CUSTOMER CODE VALUE TO CUSTOMERCODE FIELD //
cmd = "Select * from customer where customerCode = " + SQuote(customerCode);
rs = statement.executeQuery(cmd);
 
// IF CUSTOMER FOUND RETURN CUSTOMER CODE PROCEED NEXT//
rs.absolute(1);
customerCode = rs.getString("customerCode");
 
 
// CHECK IF PROGRAM EXIST THEN PROCEED NEXT //
cmd = "Select * from program where programCode = " + SQuote(programCode);
rs = statement.executeQuery(cmd);
 
 
// IF PROGRAM NOT FOUND RETURN A VALUE //
if (!rs.next()) 
{ 
lastErrorNumber = -7;
lastErrorMessage = "Program not found";
return -7;
}
 
// CHECK IF PROGRAM IS EXPIRED //
rs.absolute(1);
programStatus = rs.getString("status");
 
if (!programStatus.equalsIgnoreCase("A")) // program not active
{ 
lastErrorNumber = -8; 
lastErrorMessage = "program not active";
return -8;
}
 
 
// IF PROGRAM FOUND RETURN PROGRAM CODE AND CONVERSION FACTOR PROCEED NEXT //
programCode = rs.getString("programCode");
conversionFactor = rs.getDouble("conversionFactor");
 
 
// CHECK IF TERMINAL EXISTS THEN PROCEED NEXT //
cmd = "Select * from terminal where terminalCode = " + SQuote(terminalCode);
rs = statement.executeQuery(cmd); 
 
// PASS VALUE TO TRANSACTION DATE //
transactionDate = getDateTime();
 
 
// PASS EXPIRY DATE VALUE
cmdDate = "Select " + SQuote(transactionDate) + " + 1 as newYear"; // process of getting new year value
 
rs = statement.executeQuery(cmdDate);
rs.absolute(1);
newExpiryYear = rs.getString("newYear");
 
cmdDate = "Select right(" + SQuote(transactionDate) + ",15) as newDate"; // process of getting new date value
 
rs = statement.executeQuery(cmdDate);
rs.absolute(1);
newExpiryDate = rs.getString("newDate");
 
expiryDate = newExpiryYear + newExpiryDate; // expiryDate = expiryYear(+1) + expiryDate
 
 
// PASS VALUE FOR CLAIMED POINTS //
claimedPoints = 0.00;
 
// IF PROGRAM CODE = BLANK THEN SET DEFAULT //
if(programCode == "")
{
programCode = "DEFAULT";
} 
 
// START COMPUTING FOR POINTS //
totalPoints = transactionAmount / conversionFactor;
remainingPoints = totalPoints;
 
 
// INSERT AND INCREMENT COUNTER VALUE FOR REGISTER CUSTOMER TABLE //
cmd = "Select * from counter where counterCode = 'transaction'";
rs = statement.executeQuery(cmd);
 
rs.absolute(1);
transactionId = rs.getInt("counterValue") + 1;
rs.updateInt("counterValue",transactionId);
rs.updateRow();
connection.commit();
 
// INPUT TRANSACTION RECORD // 
try 
{
// ADD NEW TRANSACTION RECORD //
cmd = "SELECT * FROM transaction";
rs = statement.executeQuery(cmd);
 
rs.moveToInsertRow();
rs.updateInt("transactionId", transactionId);
rs.updateString("customerCode", customerCode);
rs.updateString("programCode", programCode);
rs.updateString("transactionDate", transactionDate);
rs.updateDouble("transactionAmount", transactionAmount);
rs.updateDouble("conversionFactor", conversionFactor);
rs.updateDouble("transactionQuantity", transactionQuantity);
rs.updateDouble("totalPoints", totalPoints);
rs.updateString("expiryDate", expiryDate);
rs.updateDouble("remainingPoints", remainingPoints);
rs.updateString("status", status);
rs.updateString("terminalCode", terminalCode);
rs.updateString("referenceCode", referenceCode);
rs.updateString("transactionRemark", transactionRemark);
rs.updateString("remark", remark);
rs.updateString("customerCode", customerCode);
rs.updateDouble("claimedPoints", claimedPoints);
rs.insertRow();
rs.first();
connection.commit();
}
 
// IF ERROR OCCURS CATCH HERE. EX: SQL ERROR FROM TRY BLOCK ABOVE //
catch(SQLException e1) 
{
lastErrorNumber = -30;
lastErrorMessage = "Database error occured : " + e1.getMessage();
return lastErrorNumber;
} 
// IF ERROR ABOUT INVALID INPUT (NUMBERS) CATCH HERE. EX: INVALID TRANSACTION AMOUNT/QUANTITY VALUE //
catch(NumberFormatException e3)
{
lastErrorNumber = -31;
lastErrorMessage = "Invalid value for transaction amount / transaction quantity";
return lastErrorNumber;
}
 
 
// IF ERROR OCCURS CATCH HERE. EX: OTHER EX: DATABASE CONNECTION PROBLEM //
catch(Exception e2)
{
lastErrorNumber = -1;
lastErrorMessage = "Error1: " + e2.getMessage();
return lastErrorNumber;
}
 
 
return 0; // No error code
}
 
 
// IF ERROR OCCURS CATCH HERE. EX: SQL ERROR FROM TRY BLOCK ABOVE //
catch(SQLException e1) 
{
lastErrorNumber = -30;
lastErrorMessage = "Database error occured : " + e1.getMessage();
return lastErrorNumber;
}
 
// IF OTHER ERROR OCCURS, CATCH HERE. EX: DATABASE CONNECTION PROBLEM //
catch(Exception e2)
{
lastErrorNumber = -1;
lastErrorMessage = "Error2: " + e2.getMessage();
return lastErrorNumber;
}
--------------------
 

sorry i can't paste the whole code, i've omitted some database queriess that will make the process slower. the code posted will show you an idea of how this program works...to start this explanation. my program is a reward server, the process starts when a customer purchases from the pos... this pos will then give the webservice the needed parameters: customercode,programcode,etc.(check the rs.update codes...) my boss wants me to initialize a connection once and leave it open.... i guess if i'll do an open - close database connection it'll avoid this problem that i'm facing. anyway, he wants me to find away to avoid this errors using his prefered way. the service returns both -1 and -30 whenever i try to send information to the server at the same time. -30 + getMessage(None); -1 + getMessage(Null);..... i can't see the specific message that i need... but my assumption is that it has a problem with the same time update... so i tried to put the connection.commit(); in this way all database updates will not be done at the same time, instead the memory will store all queries and will wait for the commit to happen. since the commit allows multiple records to be added in just one call i thought it'll solve the problem... after i added this connection.commit() i only receive the -1 error(Null). is there any other way to solve this? i mean do i lack some code that will trigger database update settings.... or do i have to try a newer version of Mysql driver? i have downloaded version 5.0 (latest) if this will not work.... i'm really lost... thanx for showing interest.. ^^