Solved

Error 534 detected by database DLL Execute <PEStartPrintJob>

Posted on 2004-09-15
25
3,676 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 100

Expert Comment

by:mlmcc
ID: 12064318
0
 
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
Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

 
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 100

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 100

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
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…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

763 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