RE: [Firebird-odbc-devel] Crystal Reports 8.5

[Dmitry Y. <di...@us...>]

Hi Paul,

> Could you send me your source, and let me take a look
> at it, sometimes another pair of eyes can help.  I
> have an old version of Crystal (4 actually -- planning
> to upgrade RSN), and the driver seems to work as is,
> except that on a Dialect 3 database dates and times
> look really wierd, unless I do really oddball stuff,
> like use a view to turn a time into a string field, I
> would hate to do that with dates as well, then again
> Crystal 4 doesn't support time fields (kind of dumb
> IMNSHO).  The main CVS tree is downright ancient, I
> was thinking it had actually been abandoned.

Yes, the only problems I've got are with date/time fields also. All kinds of
them in both dialect 1 and 3 databases are recognized as strings by Crystal.
I believe it cannot understand what the data type really is and supposes
string by default in this situation. Aside from being simply not working,
such a supposal often causes it to die. AFAIU, the reason is obvious -
Crystal is not ODBC 3.x conformant.

Since I definitely don't want to do a lot of tricky things (like the stuff
you described), I'd very much like to fix it. This mentioned problem has
been solved by changing the internally used JDBC type mapping from
SQL_TYPE_* types to SQL_* types, thus downgrading the driver to ODBC 2.x:

diff -r1.3 IscDbc.h
76,78c76,78
< #define JDBC_DATE 		  91
< #define JDBC_TIME 		  92
< #define JDBC_TIMESTAMP 	  93
---
> #define JDBC_DATE 		  9 // was: 91
> #define JDBC_TIME 		  10 // was: 92
> #define JDBC_TIMESTAMP 	  11 // was: 93

diff -r1.1.1.1 Sqlda.cpp
382c382
< 			return TIME;
---
> 			return JDBC_TIME;
385c385
< 			return jdbcDATE;
---
> 			return JDBC_DATE;

After that, Crystal has started to recognize all date/time fields in any
dialect properly and the problem seemed to be gone. But then I've
encountered an interesting effect while was trying to use those fields in
the selection formula. It's easy to reproduce. Let's say I have MY_TABLE
table with only one MY_MONTH field, declared as DATE (you may use either
dialect 1 or 3). This table has 24 rows, one per month, with field values
from 01-JAN-2001 to 01-DEC-2002.

Now, with the following formula:

{MY_TABLE.MY_MONTH} >= date('01.01.2002')

I see the expected 12 rows, but just after a very minor change:

{MY_TABLE.MY_MONTH} < date('01.01.2002')

I see nothing. The first fetch returns EOF. Where is the another half of my
data? So it seems to be either Crystal's or driver's problem. Note also
that:

a) the same query succeeds when Crystal is connected through the Oracle ODBC
driver
b) the same query succeeds with this driver when I override selection
formula with direct SQL and use a date literal, or when I use it as a
constant query (without parameters) and run it from any other application

AFAIK, any time a constant is used in the selection formula, Crystal uses
parametrized query (for ODBC data sources), so it may be a part of the
problem. Unfortunately, I don't have any program that allows query execution
via ODBC and always uses parameter binding. All my debug attempts were
unsuccessful - both statements are passed and prepared correctly, in both
cases parameters has the same value. Since I don't believe that I've found
another bug in the engine ;-), I prefer to consider it a driver problem that
I just cannot figure out.

Could you test my example and let me know the results? Maybe I have a
poltergeist in my computer...


Cheers,
Dmitry