Solved

Error 534 detected by database DLL Execute <PEStartPrintJob>

Posted on 2004-09-15
25
3,583 Views
Last Modified: 2007-12-19
Here's my environment:

CR8.0
Delphi 6
VCL Component version 7.X.0.51
Oracle 8.1.7
Win 2K

I have (had) a report which worked fine (using the Oracle Native driver), both when run standalone and from my Delphi application.

I then added to it an SQL expression %NonWorkingDays which looks like this:

NUMOFNONWORKDAYS("LOCAL_HEADER"."Hd_DateOfArrival", TO_DATE(SUBSTR("LOCAL_HEADER"."Hd_Ref",1,6), 'DDMMYY'))

where NUMOFNONWORKDAYS is an Oracle User-Defined function which has been tried and tested elsewhere.

Finally I modified a calculated field @LodgementDay

from:

DateDiff ("d",{LOCAL_HEADER.Hd_DateOfArrival},{%LodgementDate})

to

DateDiff ("d",{LOCAL_HEADER.Hd_DateOfArrival},{%LodgementDate}) - {%NonWorkingDays}

These were the ONLY changes made to the report;  when I ran it standalone, it worked fine and gave me the answers I was expecting.  BUT when I run it from my Delphi app, it falls over with the above error message.

I've already searched both Experts Exchange and Business Objects for similar problems, and drawn a blank.

Does anybody have any suggestions?

0
Comment
Question by:Ken Turner
  • 12
  • 8
  • 3
  • +1
25 Comments
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
0
 
LVL 6

Author Comment

by:Ken Turner
Comment Utility
mlmcc:

I hadn't but I have now, thanks.

However, I couldn't find anything that throws any new light on the problem.

Regards

Ken
0
 
LVL 42

Expert Comment

by:frodoman
Comment Utility
Ken,

Are you pulling your data from an Oracle stored procedure or direct from tables?  I ask this because Crystal doesn't work well with Oracle SP's that call other SP's (or functions).  If that's your situation you might try putting the procedure inside a package - I've had better luck with that.

frodoman
0
 
LVL 6

Author Comment

by:Ken Turner
Comment Utility
frod:

I'm getting my data direct from tables - no Stored Procedures, no Database Views, just one sneaky little User-Defined function.

Ken
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
After changing the SQL expression did you VERIFY THE DATABASE?

Open the report
Click DATABASE --> VERIFY DATABASE

mlmcc
0
 
LVL 6

Author Comment

by:Ken Turner
Comment Utility
Been there, done that ...
0
 
LVL 42

Expert Comment

by:frodoman
Comment Utility
From your Delphi application, have you verified that this statement: SUBSTR("LOCAL_HEADER"."Hd_Ref",1,6)  does produce a (valid) date in the DDMMYY format?  I assume that you did, but just to be sure...

Also if you're using an odbc connection have you done an odbc trace?

0
 
LVL 6

Author Comment

by:Ken Turner
Comment Utility
To answer your first question, I've run the 'old' version of the report (i.e.the version before the inclusion of the User-Defined Function), and the new version, both from the Delphi app, using exactly the same data and exactly the same parameters.  The old runs, the new falls over.

Second question - I don't use ODBC.   Strictly Oracle Native driver only.

Thanks for your thoughts guys - don't give up on me!
0
 
LVL 42

Expert Comment

by:frodoman
Comment Utility
>>> I've run the 'old' version of the report (i.e.the version before the inclusion of the User-Defined Function)

But did the old version include the to_date function?  That's what I was getting at may be causing the problem - I don't really think so, it was just a guess...  To help narrow the possibilities you might want to try a function that doesn't require any IN parameters and just returns a static value - if the problem goes away then your parameters are likely part of the problem.

0
 
LVL 6

Author Comment

by:Ken Turner
Comment Utility
Good point. OK, I've just run a standalone query against my database which looks like this:

select "Hd_Ref", NUMOFNONWORKDAYS("LOCAL_HEADER"."Hd_DateOfArrival", TO_DATE(SUBSTR("LOCAL_HEADER"."Hd_Ref",1,6), 'DDMMYY'))
from "LOCAL_HEADER"

This runs fine, which would seem to suggest that all possible values of TO_DATE(SUBSTR("LOCAL_HEADER"."Hd_Ref",1,6), 'DDMMYY') are valid.
0
 
LVL 6

Author Comment

by:Ken Turner
Comment Utility
Aaaah - the SQL was still running as I typed the last message, but now it has fallen over with an ORA-01843: Not a valid month.

I'll get back to you.
0
 
LVL 6

Author Comment

by:Ken Turner
Comment Utility
Packing up for the day shortly - read the next thrilling installment tomorrow.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 42

Expert Comment

by:frodoman
Comment Utility
Let me know - sounds like you've got a problem in the to_date clause with some of the data but if that turns out not to be the problem (or maybe not the *only* problem), I'll keep digging.
0
 
LVL 6

Author Comment

by:Ken Turner
Comment Utility
Morning all.

A couple of developments:

I have now identified one rogue record (in a table of 130,000) which has an invalid 'month'. I therefore added a line in my record selection criteria to exclude it.  However running the report from the Delphi app still produces the same error.

Next I have discovered a column "Hd_LodgementDate" (which I didn't even know existed until today) which is a date column, and which eliminates the need for the TO_DATE() function.  My SQL expression %NonWorkingDays now looks like this:

NUMOFNONWORKDAYS("LOCAL_HEADER"."Hd_DateOfArrival", "LOCAL_HEADER"."Hd_LodgementDate")

Ran the Delphi app again: same problem :-(

Incidentally the error message comes up almost immediately, whereas the rogue record took some time to find.

So, having now eliminated bad data as a possible cause of the problem, it's back to the drawing board ...
0
 
LVL 6

Author Comment

by:Ken Turner
Comment Utility
Hello?  Is there anybody out there today? Would anybody called mlmcc or frodoman please raise their right hand ...
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Sorry ken, but I can't receive the email notifs at work and don't spend a lot of time on ee at work.

I'll look into it further.

mlmcc
0
 
LVL 42

Expert Comment

by:frodoman
Comment Utility
Ken,

Are you sure that the oracle driver can handle the NumWorkingDays call?  When it runs fine standalone, you're using the identical SQL?

frodoman
0
 
LVL 6

Author Comment

by:Ken Turner
Comment Utility
Yes, identical SQL, same database instance, same parameters, same everything as far as I can see.

When I validate the SQL expression from within the Crystal Report, it comes back OK.

What else can I tell you?

Ken
0
 
LVL 42

Expert Comment

by:frodoman
Comment Utility
Ken,

I'm out of ideas - since the only difference between working & non-working seems to be Delphi, I'm guessing that the format of the date parameter being passed is somehow altered so it isn't the expected format.  However as I know nothing about Delphi I can't even begin to point you toward a solution if that's the case.

You may want to post a link to this question in the Delphi forum: http://www.experts-exchange.com/Programming/Programming_Languages/Delphi/     Maybe someone there can read this and see the problem.?

frodoman
0
 
LVL 6

Author Comment

by:Ken Turner
Comment Utility
Hey this must be the first time I've seen a megastar such as yourself admitting defeat!

Does EE have a special "I defeated an expert" award?
0
 
LVL 42

Expert Comment

by:frodoman
Comment Utility
You aren't the first and unfortunately won't be the last :-)

0
 
LVL 6

Author Comment

by:Ken Turner
Comment Utility
OK after much brow-beating, I have a solution:

The function NUMOFNONWORKDAYS was created under an Oracle User Name called TIMS_LOCAL;  when I was running the report standalone, I was connecting as TIMS_LOCAL, but when I ran from the Delphi app I was logging in as an application user, who for reasons I haven't yet sussed out, couldn't 'see' the function.

However, when I modified the SQL expression %NonWorkingDays to read as follows:

TIMS_LOCAL.NUMOFNONWORKDAYS("LOCAL_HEADER"."Hd_DateOfArrival", TO_DATE(SUBSTR("LOCAL_HEADER"."Hd_Ref",1,6), 'DDMMYY'))

(i.e. prefixed the function call with TIMS_LOCAL), then the problem disappeared.

I think Crystal and Oracle between them have conspired to produce unhelpful error messages:  something like "Function NUMOFNONWORKDAYS not found"  might have been a bit more useful ...

I've asked for this question to be PAQ'd.  Thanks to mlmcc and frodoman for their contributions.
0
 
LVL 42

Expert Comment

by:frodoman
Comment Utility
No objections to PAQ / Refund.

Ken - Good work hunting that one down! - frodoman
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
Comment Utility
PAQed, with points refunded (125)

Computer101
E-E Admin
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now