"MarkN" <mnuttall@nospam.com> wrote:
>
>"adam" <adam@tenwisevirgins.com> wrote:
>>
>>"MarkN" <mnuttall@nospam.com> wrote:
>>>
>>>"adam" <adam@tenwisevirgins.com> wrote:
>>>>
>>>>Hi
>>>>
>>>>I am having difficulty retrieving errors from my DB.
>>>>
>>>>I use SQL 7.0 with the Sun jdbc-odbc driver.
>>>>
>>>>When I raise an error in a stored proc:
>>>>
>>>>SQL code:
>>>>--
>>>>RAISERROR('This is a test error message',16,1)
>>>>--
>>>>
>>>>I would expect it to get picked up by my java code:
>>>>
>>>>try{
>>>> //connect to database
>>>> Connection cn = DriverManager.getConnection("jdbc:odbc:TWVPAT","",
>"");
>>>>
>>>> //create and execute sql
>>>> CallableStatement cs = cn.prepareCall("{call sp_get_test_data}");
>>>> ResultSet rs = cs.executeQuery();
>>>>
>>>> while (rs.next()){
>>>> System.out.println(rs.getString("text"));
>>>> }
>>>>
>>>>
>>>> //cleanup
>>>> rs.close();
>>>> cs.close();
>>>> cn.close();
>>>> }
>>>>
>>>> catch (SQLException se){
>>>> System.out.println("SQL exception: " + se.getMessage());
>>>> }
>>>>
>>>>
>>>>But it just doesnt get picked up. any ideas?
>>>>
>>>>adam
>>>>
>>>>
>>>
>>>By picked up do you mean an SQLException is not thrown? It has been a
>few
>>>years since I coded one of those in T-SQL and that was with VB. I think
>>>that is right levels for errors. I've thrown errors with DB2 and Java
>in
>>>triggers and it worked. Is the RAISEERROR the only thing in your proc?
>>>
>>>Why are you using a stored proc? Avoid them and use them as a last resort.
>>> The main reason people use Java(cross platform/vendor neutral) is the
>same
>>>reason you shouldn't use stored procs. Sometimes they are necessary like
>>>in triggers.
>>>
>>
>>Thanks. I do mean that no SQLEXception is thorwn, or at least if it is,
>its
>>not picked up in the catch block. I have tried it with higher levels, but
>>16-19 are errors, and 20-25 are regarded as fatal errors (according to
the
>>SQL book on line, which close the connection.
>>
>>I use stored proc's for all my data inserts - as an extra layer of data
>validation
>>and for transactions. It seems easier to me to have a transaction handled
>>at the database layer than in the middle layer if both updates are within
>>the same database.
>>
>>Sorry for the double posting - not sure who reads which newsgroups and
if
>>this was a java problem or a sql one.
>>
>>Adam
>>
>
>I understand about the double post. But you are lucky to get a Java question
>answered, let alone a Java AND SQL server one answered within these discussions.
>
>I am throwing these type of errors from DB2 and they are caught. But we
>are using a DB2 specific driver. That might be the problem. Try throwing
>the higher level error just for fun and see if it works. If you are getting
>an error the catch block should catch it or the JVM will if you don't have
>any outer catch blocks. Try using a SQL Server specific driver.
>
>Back on my Soap Box - any code that references the database is part of the
>database layer. All you really are doing is gaining some speed (how much
>really?) and making it tough to move to some other database. Yes, some
data
>validation/creation must occur within the database (i.e. Unique ID creation).
>As the man says "Pay me now, or pay me later."
>
>Let me now how it goes.
>Mark
>(Sorry about the soap box discussion - I can't control myself)
>
Thanks again. Well tried higher error levels. Also tried running the SP from
within the SQL query analyser to discover that anything higher than level
18 can only be thrown by a memeber or sysadmin and requires a write to the
log. Dont have another driver to hand at present but will play with one later.
Well maybe I made the wrong decision to use sp's but that was some time ago,
and since they exist I dont really want to have to rework it all not to.
Perhaps Ill think differently next time
Adam