JDBC-ODBC Bridge, capturing Errors...

[TomaHawk]

Hey,

Basically I'm writing a program in JAVA which connects to an Access database using the JDBC-ODBC bridge. All is well, until I want to delete a record stored in the database.

I have a TRY and CATCH block within Java to catch an exception if one is returned when trying to delete a record from the database.

But what I have realised is that Java is in fact not throwing an error, but I can imagine the SQL is. As it allows me to execute an SQL query which deletes a record based on the userID, but the problem being I can type in anything for the userID and I receive no error (even is I type in a phrase or an ID which doesn't exist).

If I input a userID which exists in the database then it does the job fine! But I don't know how to capture an error with a SQL query from within JAVA. (Like in PHP, but is there an equivalent method in JAVA)?

Basically my question is how do I capture an error/exception with a SQL query using JAVA.

Any help would be great, thanks.


...I know of "e.getMessage();" but that doesn't seem to return any error with records or ID's which don't exist.

 

[Dj_Jestar]

What does the api for the odbc/jdbc say about SQL errors? Surely there will be a return value you can compare to, at least? boolean true/false? (despite that being against the grain of Java error/exception handling)

also - have you done the usual bug hunting stuff of ensuring the query is correct, and manually executing the query to see it actually performs what you want, etc. etc. etc.?

 

[TomaHawk]

What does the api for the odbc/jdbc say about SQL errors? Surely there will be a return value you can compare to, at least? boolean true/false? (despite that being against the grain of Java error/exception handling)

also - have you done the usual bug hunting stuff of ensuring the query is correct, and manually executing the query to see it actually performs what you want, etc. etc. etc.?

Yeah I've tried the usual bug hunting methods.. but can't seem to find the problem, as my other constructors/methods work fine. Plus the method does what its suppose to but doesn't throw an exception when 'extreme' or invalid data is input.

Here is the code in question: (bit hard to read with no highlighting!)..

    public void deleteEmployee(String ID)
    {//delete a record, given logon
        try
            {
                  //setup an SQL DELETE statement to delete the record
                String mySql = "DELETE FROM tblEmployees WHERE EmployeeID = '" + ID + "'";
                  //String fields need extra single quotes around them
                System.out.println(mySql);
                  //for diagnostic purposes in case of errors.
                  //execute the query, commit the changes and get records again.
                int rs;
                rs = pstmt.executeUpdate(mySql);     //use executeUpdate() method
                con.commit();
                getAllEmployeeData();   //reconnect recordSet to hold all employee details
            
            }//end of try
        catch(Exception e)
        {
            System.out.println("Error deleting record:- " + e.getMessage() );
            e.printStackTrace();
        }//end of catch
    }//end of deleteEmployee method

So what I want is when/if a user were to type in 'fish' as the userID or a number which doesn't exist, I then want to be able to return and print the error as I'm pretty sure one should be happening if it runs a DELETE statement to which there is no record that matches.

I'm using the catch to 'catch' the exception, but strangely I don't seem to be getting one when querying with invalid data. Any ideas? I thought this was mainly to do with Java not throwing an exception due to it being a problem with the SQL.

I did have a thought if there isn't a way of doing what I wanted, which was to manually check that the record exists in the database before querying it. If it does query it, if not alert user.

I was guessing there would be some way of tracking whether the database falls over when trying to execute a query matching data which doesn't exist... but maybe in Java I have to do it manually?

..Couldn't really find anything in the API, I did browse/search through.

Any ideas would be great.

 

[JIMA]

Hi,

One approach you could take would be to write a wrapper class for your JDBC routines. IMHO it's a good move to do this as you then have a consistent method throughout the app of comitting, handling errors, retrying queries due to deadlock etc.

A method on the wrapper class would be used to perform update/insert/delete SQL statements and would also accept a boolean parameter to say whether no rows being affected should be classed as an error (and maybe if it's fatal or just a warning).

In the case of executeUpdate I believe the number of rows affected is returned from the executeUpdate method. So, you'll need to create your own exception and throw this if no rows were affected and this is considered a fatal condition.

I think that exceptions are only thrown due to actual errors with the SQL, probably thrown when preparing the statement. Update/Selecting no rows isn't an error so no exception is thrown.

Hope that helps.

Jim

 

[Hades]

It's been a while since I did dome Java JDBC. But if I recall correctly, deleting a row which isn't there isn't an error condition and so won't generate an exception (the word exception indicates an unusual occurance but it's not unusual to expect a row to be missing in a lot of circumstances). Have you tried looking in the sql warnings returned on the statement object?

Take a look at Statement.getWarnings()

 

[Dj_Jestar]

challenge the number of rows affected, and if 0, throw and exception.

 

[TomaHawk]

Thanks a lot for the replies guys, much appreciated!

One approach you could take would be to write a wrapper class for your JDBC routines. IMHO it's a good move to do this as you then have a consistent method throughout the app of comitting, handling errors, retrying queries due to deadlock etc.

Creating a class to cover my needs was what I was thinking of doing but thought that may have been pointless if there were methods that covered it. So thanks for clearing that up as that route makes sense due to being able to reuse it.

But if I recall correctly, deleting a row which isn't there isn't an error condition and so won't generate an exception (the word exception indicates an unusual occurance but it's not unusual to expect a row to be missing in a lot of circumstances).

That makes sense, I had a feeling that may have been the case, but still thought something would have been returned due to not being able to execute the query. But thinking about it, it would have but just no rows would have been affected.

challenge the number of rows affected, and if 0, throw and exception.

Great idea, seems a logical way to do it. I was thinking of something similar...
--
Thanks a lot for the suggestions much appreciated, has cleared some thoughts up and given me some ideas.