ODBC Connection / Crystal Reports

Question

We have some VERY old Crystal 8.5 reports (slated for re-write in Crystal 2016 this year), which have stopped working yesterday. They use Crystal Query (.qry) files as their data sources running against an Oracle Database 12c (12.2.0.1.0) database.

When we run the .rpt file in Crystal, the reports run forever until we get out of memory issues albeit the data they return is gobbledygook and the number of pages (tens of thousands) bears no relation to the expected results.

When we run the .qry files in Crystal SQL Designer 8.0, we get only the first row of data returned.

Turning on tracing in ODBC Data Source Administrator, there are a couple of entries in the log file that seem to indicate a potential issue:

test            f3b4-f974   ENTER SQLSetConnectAttr 
    SQLHDBC             0x023ECB78
    SQLINTEGER                1041 <unknown>
    SQLPOINTER          [Unknown attribute 1041]
    SQLINTEGER                  -3 

test            f3b4-f974   EXIT  SQLSetConnectAttr  with return code -1 (SQL_ERROR)
    SQLHDBC             0x023ECB78
    SQLINTEGER                1041 <unknown>
    SQLPOINTER          [Unknown attribute 1041]
    SQLINTEGER                  -3 

    DIAG [S1C00] [Oracle][ODBC]Driver not capable. (0) 

test            f3b4-f974   ENTER SQLSetConnectAttr 
    SQLHDBC             0x023ECB78
    SQLINTEGER                1042 <unknown>
    SQLPOINTER          [Unknown attribute 1042]
    SQLINTEGER                  -3 

test            f3b4-f974   EXIT  SQLSetConnectAttr  with return code -1 (SQL_ERROR)
    SQLHDBC             0x023ECB78
    SQLINTEGER                1042 <unknown>
    SQLPOINTER          [Unknown attribute 1042]
    SQLINTEGER                  -3 

    DIAG [S1C00] [Oracle][ODBC]Driver not capable. (0) 

If we use a third-party viewer, we are able to generate most of the reports. If we run reports that don't use a .qry file, we don't have any problems.

Has anyone ever come across a similar issue? Or does anyone have any pointers on what could have changed that we are not aware of as a result of any patches etc?

Answer 1

We have the same problem with Crystal Reports 8.5 using qry's. Here is the journey I have been on to isolate this problem, in the hope that someone else can shed some more light on the issue.

I can generate the same error using CR Designer and if the computer date is set back to 2019 it works fine. The error generated is

"xBase error: Last file change date in error".

With the designer I was able to narrow the dll executables involved to: P2bxbse.dll(file version 8.6.0.25), P2sodbc.dll(fv 8.6.1.86) and P2lodbc.dll(fv 8.6.0.31). From observing the point of failure, one of these dll's (I suspect P2lodbc.dll) on executing the qry, reads metadata from the underlying database into a temp file, the file turns out to be a of type .dbf (dBase database file - in the \user\localdata directory). This would explain why the error has nothing to do with the ODBC driver or underlying database. This file is quickly created and is locked until the qry is terminated, making it difficult to analyse.

The file format documentation for .dbf files shows that, amongst others, there are 3 bytes for the date of last file change ( ref http://www.dbase.com/Knowledgebase/INT/db7_file_fmt.htm) . I suspect the problem lies here, as there are several explanations on the internet of how this data is interpreted and copes with the year 2020, none too convincing!

Edit, sorry I just made an account to post here and the system wont let me comment :( (only 1 reputation) so I hope you can put my input here also.

I have a very old program that has this problem when I print a .rpt , since I do not have the sourcecode to the program I can not do any of the sugested changes here, luckely I do not have to print alot, so when I need to print I now use a HEX editor (HxD) and edit bit 1,2 and 3 of the involved .dbf (xbase) database files. if I change it to 77 0C 1F (this is the code for 31/dec/2019) and save the .dbf files, I have no problem printing my reports aslong as I dont edit any database data. (actual dates in the program can be in 2020 that does not matter) a permanent fix would be if someone can edit the old crystal reports DLL files to simply ignore the file edit date in the databases :(

Anyone know of a HEX editor I can use to run batch processing of files ? :)

Answer 2

We are having the exact same issue, albeit with SQL Server 2016. However, we cannot even run the .qry files.

We are able to run the .qry files if we change the computer date to one in 2019.

Answer 3

The symptoms also include:

xbase error: last file change date in error

or

Error: Automation error

The object invoked has disconnected from its clients.

We have this issue using Crystal Report 7 / 8.5

Date error prompted when report is using query as data source.

Works normal after the system time is temporary changed to 2019.

Answer 4

A workaround for this "2020 Crystal Report bug" would be to get rid of the .qry intermediate file.

• create a database *-view on your database in place of the query you have in your .qry,
• create a new .rpt file using the Crystal Reports "tool".

But this require you to re-write your reports. Better migrate to a newer reporting tool directly.

Answer 5

In addition to this answer, Here is the solution we used to go on using the old reports : replace CR8.5 by CR11.5!

Notice: The reports are launched from the application which was developed in vb6, and I have to maintain it.

• It was necessary to install Crystal Reports 11, meaning we had to buy a new license.

• It is then pretty straight forward to replace the old vb6 CR8.5 component with the new one. Much help received from

  • some old CR8 migration document - we had to follow the steps to migrate from OCX to Report Designer Component (RDC) - including the Crystal Report 8 ActiveX Designer Runtime library (CRAXDRT),
  • Then an extended documentation on how to use this CRAXDRT helped a lot (but seems to be for earlier version, did not solve everything),

  • at the point were we needed to setup a variable in the report, I had to use the magic found here

    Private Sub setIdContext(v As String)
        Dim myParamFields As CRAXDRT.ParameterFieldDefinitions
        Dim myParamField As CRAXDRT.ParameterFieldDefinition
        Set myParamFields = CrxRep.ParameterFields
        For Each myParamField In myParamFields
            With myParamField
                Select Case .ParameterFieldName
                    Case "ID_Context"
                        .SetCurrentValue Val(v)
                End Select
            End With
        Next
    End Sub
    

Limits: ... still looking a way on how to deploy the VB6 exe on the client without having to deploy the full Crystal Report BOXI thing :(