Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3872
  • Last Modified:

Error 534 detected by database DLL Execute <PEStartPrintJob>

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
Ken Turner
Asked:
Ken Turner
  • 12
  • 8
  • 3
  • +1
1 Solution
 
Ken TurnerAuthor Commented:
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
 
frodomanCommented:
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
How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

 
Ken TurnerAuthor Commented:
frod:

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

Ken
0
 
mlmccCommented:
After changing the SQL expression did you VERIFY THE DATABASE?

Open the report
Click DATABASE --> VERIFY DATABASE

mlmcc
0
 
Ken TurnerAuthor Commented:
Been there, done that ...
0
 
frodomanCommented:
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
 
Ken TurnerAuthor Commented:
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
 
frodomanCommented:
>>> 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
 
Ken TurnerAuthor Commented:
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
 
Ken TurnerAuthor Commented:
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
 
Ken TurnerAuthor Commented:
Packing up for the day shortly - read the next thrilling installment tomorrow.
0
 
frodomanCommented:
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
 
Ken TurnerAuthor Commented:
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
 
Ken TurnerAuthor Commented:
Hello?  Is there anybody out there today? Would anybody called mlmcc or frodoman please raise their right hand ...
0
 
mlmccCommented:
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
 
frodomanCommented:
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
 
Ken TurnerAuthor Commented:
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
 
frodomanCommented:
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
 
Ken TurnerAuthor Commented:
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
 
frodomanCommented:
You aren't the first and unfortunately won't be the last :-)

0
 
Ken TurnerAuthor Commented:
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
 
frodomanCommented:
No objections to PAQ / Refund.

Ken - Good work hunting that one down! - frodoman
0
 
Computer101Commented:
PAQed, with points refunded (125)

Computer101
E-E Admin
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 12
  • 8
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now