KENNETH TURNER
asked on
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_HE
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_Date
to
DateDiff ("d",{LOCAL_HEADER.Hd_Date
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?
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_HE
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_Date
to
DateDiff ("d",{LOCAL_HEADER.Hd_Date
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?
ASKER
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
I hadn't but I have now, thanks.
However, I couldn't find anything that throws any new light on the problem.
Regards
Ken
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
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
ASKER
frod:
I'm getting my data direct from tables - no Stored Procedures, no Database Views, just one sneaky little User-Defined function.
Ken
I'm getting my data direct from tables - no Stored Procedures, no Database Views, just one sneaky little User-Defined function.
Ken
After changing the SQL expression did you VERIFY THE DATABASE?
Open the report
Click DATABASE --> VERIFY DATABASE
mlmcc
Open the report
Click DATABASE --> VERIFY DATABASE
mlmcc
ASKER
Been there, done that ...
From your Delphi application, have you verified that this statement: SUBSTR("LOCAL_HEADER"."Hd_
Also if you're using an odbc connection have you done an odbc trace?
Also if you're using an odbc connection have you done an odbc trace?
ASKER
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!
Second question - I don't use ODBC. Strictly Oracle Native driver only.
Thanks for your thoughts guys - don't give up on me!
>>> 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.
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.
ASKER
Good point. OK, I've just run a standalone query against my database which looks like this:
select "Hd_Ref", NUMOFNONWORKDAYS("LOCAL_HE
from "LOCAL_HEADER"
This runs fine, which would seem to suggest that all possible values of TO_DATE(SUBSTR("LOCAL_HEAD
select "Hd_Ref", NUMOFNONWORKDAYS("LOCAL_HE
from "LOCAL_HEADER"
This runs fine, which would seem to suggest that all possible values of TO_DATE(SUBSTR("LOCAL_HEAD
ASKER
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.
I'll get back to you.
ASKER
Packing up for the day shortly - read the next thrilling installment tomorrow.
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.
ASKER
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_HE
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 ...
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_HE
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 ...
ASKER
Hello? Is there anybody out there today? Would anybody called mlmcc or frodoman please raise their right hand ...
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
I'll look into it further.
mlmcc
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
Are you sure that the oracle driver can handle the NumWorkingDays call? When it runs fine standalone, you're using the identical SQL?
frodoman
ASKER
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
When I validate the SQL expression from within the Crystal Report, it comes back OK.
What else can I tell you?
Ken
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: https://www.experts-exchange.com/Programming/Programming_Languages/Delphi/ Maybe someone there can read this and see the problem.?
frodoman
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: https://www.experts-exchange.com/Programming/Programming_Languages/Delphi/ Maybe someone there can read this and see the problem.?
frodoman
ASKER
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?
Does EE have a special "I defeated an expert" award?
You aren't the first and unfortunately won't be the last :-)
ASKER
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.NUMOFNONWORKDAY
(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.
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.NUMOFNONWORKDAY
(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.
No objections to PAQ / Refund.
Ken - Good work hunting that one down! - frodoman
Ken - Good work hunting that one down! - frodoman
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
http://support.businessobjects.com/communityCS/TechnicalPapers/cr_dbconn_troubleshooting.pdf.asp
http://support.businessobjects.com/communityCS/TechnicalPapers/cr_error_detected_by_db_dll.pdf.asp
mlmcc