Solved

Error 534 detected by database DLL Execute <PEStartPrintJob>

Posted on 2004-09-15
25
3,746 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 8
  • 3
  • +1
25 Comments
 
LVL 6

Author Comment

by:Ken Turner
ID: 12064529
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
ID: 12064672
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
[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

 
LVL 6

Author Comment

by:Ken Turner
ID: 12064744
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 101

Expert Comment

by:mlmcc
ID: 12064789
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
ID: 12064832
Been there, done that ...
0
 
LVL 42

Expert Comment

by:frodoman
ID: 12065041
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
ID: 12065147
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
ID: 12065209
>>> 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
ID: 12065472
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
ID: 12065557
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
ID: 12065962
Packing up for the day shortly - read the next thrilling installment tomorrow.
0
 
LVL 42

Expert Comment

by:frodoman
ID: 12066021
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
ID: 12072975
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
ID: 12075811
Hello?  Is there anybody out there today? Would anybody called mlmcc or frodoman please raise their right hand ...
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 12080080
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
ID: 12083948
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
ID: 12084004
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
ID: 12084328
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
ID: 12084390
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
ID: 12084459
You aren't the first and unfortunately won't be the last :-)

0
 
LVL 6

Author Comment

by:Ken Turner
ID: 12121875
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
ID: 12136246
No objections to PAQ / Refund.

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

Accepted Solution

by:
Computer101 earned 0 total points
ID: 12155027
PAQed, with points refunded (125)

Computer101
E-E Admin
0

Featured Post

Ready to get started with anonymous questions?

It's easy! Check out this step-by-step guide for asking an anonymous question on Experts Exchange.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

617 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