Delphi, CR9 and InterBase - report not running problem

[bmarks]

Hi,

I hope this is the right forum (wasn't sure if it should be here, in the Interbase forum or in the Delphi forum).

Here is the situation:

I have a Crystal report (v9) that calls a stored procedure in Interbase 7.5.

If I run the stored procedure in IB Expert, it runs fine.

If I run the Crystal report in Crystal and input the parameters, it runs fine.

When trying to run the report from our software which is written in Delphi, I get an error (Crystal error of queryengineerror).

To narrow it down further, I tried hardcoding the stored procedure to return the same data and ignore the parameters and then have the report show the parameters in the report header. This runs fine from Delphi.

Then I started putting the parameters back in the sproc. I've narrowed the problem down to a single line in the sproc. It is one that takes the parameters for day, month, year and turns them into a date. The syntax seems okay as it runs fine in IBExpert and from Crystal. What we can't figure out is why it fails when called from Delphi. The line in the sproc that is causing the issue is:

DateFrom = castdatefromyear ||'-'|| :datefrommonth ||'-'|| :datefromday as date)
(If I change this to:
DateFrom = cast('2006-1-1' as date)
it works fine)

I'm using this same code in another report and sproc combination and it works fine when called from Delphi.

I'm hoping that someone might be able to shed some light on this. Any thougths?

 

[synapsevampire]

I'm not familiar with Delphi syntax, what daat types are the year, month and day?

Perhaps you're not getting the implicit conversion that you are expecting and you need to convert each to a string first.

DateYear = castdatefromyear as char)
DateMonth = castdatefrommonth as char)
DateDay = castdatefromday as char)

Or even gang them into nested CAST statements.

Another issue may be whether they are being padded with zeroes, or some other formatting concern. Try using a debug session or messagebox to display what's in the variable before running the report.

-k

 

[bmarks]

synapsevampire - thanks for the feedback. Unfortunately, tried that.
Delphi is sedning the parameters as char data type. I tried using integers and tried doing some explicit conversions as well.

I have found that even if I try:
dateyear = castdatefromyear as integer)
the error pops up. Meaning that it won't even convert the char to integer (which it should be able to as it's just '2006')

I've tried trimming in case there are extra blanks. I've displayed the parameters in a shell report to see that they don't have extra zeroes or other characters, but they look fine. The only thing I can think of is if there are some "invisible" formatting or control characters. But don't see how/why and why they don't interfere with Crystal - just in the sproc.

I'm really getting frustrated with this one and don't know what to try next. Unfortunately, I'm not familiar with Delphi myself and am relying on the Delphi developers here to verify that the code on that side is fine. I trust their skills and they feel that the code is as it should be - so we are all stumped.

 

[synapsevampire]

You didn't state what it shows when you display what's in the variable.


Here's a blurb where someone is using the cast:

http://community.borland.com/article/0,1410,25895,0.html

-k

 

[bmarks]

When I display the variable, it shows what I would expect. That is datefromyear would display 2006, datefrommonth would display 4 and datefromday would display 10.

I am using something similar to that blurb to create my date in the stored procedure:
datefrom = castdatefromyear ||'-'|| :datefrommonth ||'-'|| :datefromday as date)

This does work as I've used it other places and it works when running the report in Crystal.

 

[synapsevampire]

Post what the COMPLETE variable that is passing the entire date displays, that is what I am suspect of, and do so in the problematic environment, the others don't matter because they work.

-k

 

[bmarks]

The three parameters display as:
:datefromday = 10
:datefrommonth = 4
:datefromyear = 2006

the formula to get the date of:
datefrom = castdatefromyear ||'-'|| :datefrommonth ||'-'|| :datefromday as date) is what fails.

There isn't really a "complete variable" as passing a date variable from Delphi to Crystal does not work well, so we pass three separate variables for the date components.

 

[bmarks]

Note: The Delphi developer did run a debug to display the variables before passing them to the report and the variables look correct there.

 

[bmarks]

Update: I got it to work - but I don't like the solution.

Delphi still passes the three variables/parameters as strings, but I've declared them in Crystal as integers, in the IB sproc as integers, and left the cast statement as is (that is the variables going into the cast statement are integers rather than strings like they should be).

For some reason this works, even though it defies most standards and rules. (i.e. implicit conversions, nonmatching variable types, wrong data type as input into a function).

 

[synapsevampire]

IF datefrom = castdatefromyear ||'-'|| :datefrommonth ||'-'|| :datefromday as date) fails, it has nothing to do with Crystal, you're still in Delphi at that point.

I assumed that it was when you were passing the variable it failed.

So your issue is with the Delphi CAST statement, I'd check Delphi forums.

-k

 

[bmarks]

Actually, the cast statement is in an Interbase stored procedure. The three variables get passed as parameters from Delphi to Crystal and then from Crystal to Interbase (in a "Command" statement).

So I'm not sure where the issue is, but I found that any kind of cast (in Interbase)on any of those three variables in Interbase fails. If you do cast/convert it works. We don't do casts in Delphi as we can only pass strings from Delphi to Crystal (or so I'm told).

I think it has something to do with how Delphi passes the parameters, but don't enough on that side, so I did also post in Delphi forum, but no takers yet.

 

[synapsevampire]

Ahhh, I see.

I would check with an Interbase forum too.

If you're just setting a command, I doubt that Crystal is altering it, so it sounds like Interbase, the point being that it's unlikely that it's Crystal.

A quick check shows that Interbase supports many forms of passing a date, check under the leap year, they have an example with the year as the last item:

http://www.volny.cz/iprenosil/interbase/ip_ib_code_timestamp.htm

Perhaps you can change the order and it will pass.

-k

 

[bmarks]

Thanks. But I doubt it's a date problem. To show this:

- I did a shell report and a shell stored procedure. (That is, it returns and displays data regardless of the parameters passed).
- If I just pass the parameters and do nothing with them, it runs fine.
- If I do a datefrom = cast('2006-06-14' as date)it runs fine, so the syntax is correct (although I did try other orers as well).
- I then took the datefromday parameter which gets passed from Delphi as a string and did Dday = cast(datefromday as integer), it fails in the same way as when I was trying datefrom = castdatefromyear ||'-'|| :datefrommonth ||'-'|| :datefromday as date)

This is what is leading me to think it has to do with how the variable gets passed from Delphi - something about it does not allow me to explicitly convert it from a string to any other type.

If I don't use Delphi, but pass the variable values directly to the Crystal report (and subsequently to Interbase) it all runs fine. It is the introduction of passing the variables from Delphi that the failure starts.