UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Exceptions Reported As Generic Odbc Errors By Caller    
 
   
whdyck
post Mar 7 2018, 11:52 AM
Post#1



Posts: 255
Joined: 20-July 11
From: Winnipeg, MB, Canada


I have an MS Access application that talks to an Oracle back-end.

If Oracle code detects an error it uses the RAISE command to raise a named exception. Then in the EXCEPTION block, the named exception's code runs to inform the user.

Oracle typically appends additional text to indicate calling sequence that ended up in the code that finally reported the error. The problem is that, under certain circumstances, MS Access cannot report the error message from Oracle, instead displaying a generic ODBC error with no accompanying description to indicate the problem. This, of course, makes it difficult to troubleshoot.

If I execute the same action in TOAD as what caused the ODBC error in Access, I see an error like the attached. Code snippet generating the error message is as follows:
CODE
       IF nOverlapCount > 0 THEN
          RAISE excDateOverlap;
       END IF;
    END IF;  -- pnVoid = -1
EXCEPTION
    WHEN excDateOverlap THEN
       raise_application_error(
          -20000
        ,    CHR(10)
          || CHR(10)
          || CHR(10)
          || '                                       DATE/TIME OVERLAP ON AGREEMENTS'
          || CHR(10)
          || CHR(10)
          || 'New/updated Agreement ID '
          || pnUntAsnAgrID
          || ' has an overlap with '
          || nOverlapCount
          || ' other agreement(s) for Unit '
          || pcUnitNo
          || ' (e.g., Agr ID '
          || nMaxAgrID
          || ').'
          || CHR(10)
          || CHR(10)
          || 'Please correct and try again.'
          || CHR(10)
          || CHR(10)
          || CHR(10));
    WHEN OTHERS THEN
       raise_application_error(-20040
                             , SQLERRM);
END Check4AgrDateOverlap;


I'm not sure what accounts for this behaviour. I had thought that the additional text added by Oracle at the end might exceed the limit for ODBC error text, but I'm not sure. Any ideas on how I can fix this (or even troubleshoot it)?

Thanks for any help you can give.

Wayne

Attached File(s)
Attached File  TOAD_Error.bmp ( 615.24K )Number of downloads: 8
 
Go to the top of the page
 
rabroersma
post Mar 7 2018, 12:02 PM
Post#2


UtterAccess VIP
Posts: 1,299
Joined: 1-January 07
From: Whittier, California, USA


I'm just guessing based on the language of the error message.

It seems that there is a business rule - such that only one agreement (between two parties?) can exist at any given range of time. If there is an overlap, the overlapping agreements make conflict - so they are not allowed.

This VBA error handler seems to translate the oracle (trigger raised?) error into a human-readable format.

--------------------
Regards, Richard
Postgresql 10 Version Released! - Newly added MS-Access friendly features in this release -> More Parallel Query Execution, Declaritive Table Partitioning, SQL Compliant Identity Columns
Go to the top of the page
 
whdyck
post Mar 7 2018, 01:54 PM
Post#3



Posts: 255
Joined: 20-July 11
From: Winnipeg, MB, Canada


Correct, but here's the real problem I'm trying to solve:

QUOTE
The problem is that, under certain circumstances, MS Access cannot report the error message from Oracle, instead displaying a generic ODBC error with no accompanying description to indicate the problem. This, of course, makes it difficult to troubleshoot.


Bottom Line: The user gets no information about the error, except that it's an ODBC error.

Wayne
Go to the top of the page
 
rabroersma
post Mar 7 2018, 02:10 PM
Post#4


UtterAccess VIP
Posts: 1,299
Joined: 1-January 07
From: Whittier, California, USA


Okay, I see.

This error handler will only catch one specific of error. The potential error raisers are Oracle, ODBC driver, or MS-Access - perhaps other sources as well.

Googling the error code number will usually get you in the ball part of the error source.

I expect Oracle errors are constraint violations or custom trigger business rule violations. Oracle statement logging can be configured to capture these errors - and you can see the client statements that initiated the error. If these errors are common, you might consider adding to your error handler code block to interpret and perhaps translated these messages into laymen terms.

ODBC errors are usually caused by driver bugs or RDBMS/ODBC driver version incompatibility. I suggest verifying that you using the latest driver for your version of Oracle.

MS-Access errors are usually errors in VBA code. I agree that these can at times can be hard to diagnose.

--------------------
Regards, Richard
Postgresql 10 Version Released! - Newly added MS-Access friendly features in this release -> More Parallel Query Execution, Declaritive Table Partitioning, SQL Compliant Identity Columns
Go to the top of the page
 
BananaRepublic
post Mar 7 2018, 10:03 PM
Post#5


Dungeon Cleaner
Posts: 1,512
Joined: 16-June 07
From: Banana Republic


I don't have enough information but I'm going to go out on the limb and assume you are having this particular difficulty because it's being raised in the form's Error event, rather than within the VBA code itself.

This is described in this KB article.

An option is to use this solution here which basically hijacks the messagebox and inject custom text.

If this is in fact a stored procedure that you run in VBA, then you should be able to read the DAO.Errors collection for additional information. I'm sure the custom message will be in there.

If you are using ADO, it is also possible that the additional errors is actually a recordset and you may have to invoke NextRecordset on it to retrieve the message.

--------------------
Using Access BE over WAN is like running to your city library, ripping out pages from a book, running back home and scribble notes on it then run back and glue the pages back into the book in the library, all during a rush hour full of drunk drivers in middle of a horrible thunderstorm, and hoping nobody else has ripped out the pages you wanted.
Go to the top of the page
 
whdyck
post Mar 8 2018, 11:30 AM
Post#6



Posts: 255
Joined: 20-July 11
From: Winnipeg, MB, Canada


QUOTE
If this is in fact a stored procedure that you run in VBA, then you should be able to read the DAO.Errors collection for additional information. I'm sure the custom message will be in there.

Yes, it's a stored procedure that's invoked by a trigger.

There's a lot for me to learn here, but the solution you pointed me to looks very promising. Thanks very much for pointing it out.

Wayne
Go to the top of the page
 
whdyck
post Apr 30 2018, 10:44 AM
Post#7



Posts: 255
Joined: 20-July 11
From: Winnipeg, MB, Canada


It's taken me a while to get back to this. I'm trying to use Stiphout's approach of hijacking the error message, as suggested by BananaRepublic.

I'm able to extract the content of the message and display custom content in the error message. However, after extracting the true ODBC error message's content, I'd like to be able to immediately close that error message, log the error in a table, then display a different error message (using an Access modal form). This would give me more flexibility in what I can display. (For one thing, using Stiphout's approach, I cannot seem to inject a larger error message than the one initially inserted by default. See attached.)

Can this be done? It seems that Form_Error completes before the error message is displayed, so I'm not sure where I could put code to do this extra work of closing the original error and displaying my own. Seems that a lot of Access stuff doesn't work inside Function EnumChildProc.

Thanks.

Wayne
This post has been edited by whdyck: Apr 30 2018, 10:50 AM
Attached File(s)
Attached File  CustomError.bmp ( 262.31K )Number of downloads: 2
 
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    26th May 2018 - 07:04 AM