CODEWITHSUNDEEP
sqloracle-databasecrystal-reportsodbccrystal-reports-8.5
SmallFry
SmallFry

Reputation: 153

ODBC Connection / Crystal Reports

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?

Upvotes: 2

Views: 3766

Answers (5)

Graham Inward
Graham Inward

Reputation: 31

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 ? :)

Upvotes: 3

J. Chomel
J. Chomel

Reputation: 8395

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 :(

Upvotes: 0

J. Chomel
J. Chomel

Reputation: 8395

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.

Upvotes: 0

HardcoreGamer
HardcoreGamer

Reputation: 1193

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.

Upvotes: 0

Meg Bullock
Meg Bullock

Reputation: 11

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.

Upvotes: 1

Related Questions