ODBC连接 / Crystal Reports

huangapple go评论74阅读模式
英文:

ODBC Connection / Crystal Reports

问题

我们有一些非常古老的Crystal 8.5报告(计划在今年重写为Crystal 2016),它们在昨天停止工作。它们使用Crystal查询(.qry)文件作为它们的数据源,运行在Oracle数据库12c(12.2.0.1.0)上。

当我们在Crystal中运行.rpt文件时,报告会一直运行,直到我们遇到内存问题,尽管它们返回的数据是胡言乱语,而页数(成千上万)与预期结果没有关系。

当我们在Crystal SQL Designer 8.0中运行.qry文件时,只返回第一行数据。

在ODBC数据源管理员中启用跟踪后,日志文件中有一些条目似乎表明可能存在问题:

>
test f3b4-f974 ENTER SQLSetConnectAttr
SQLHDBC 0x023ECB78
SQLINTEGER 1041 <unknown>
SQLPOINTER [Unknown attribute 1041]
SQLINTEGER -3
>
test f3b4-f974 EXIT SQLSetConnectAttr 返回码为-1(SQL_ERROR)
SQLHDBC 0x023ECB78
SQLINTEGER 1041 <unknown>
SQLPOINTER [Unknown attribute 1041]
SQLINTEGER -3
>
DIAG [S1C00] [Oracle][ODBC]驱动程序无法执行。(0)
>
test f3b4-f974 ENTER SQLSetConnectAttr
SQLHDBC 0x023ECB78
SQLINTEGER 1042 <unknown>
SQLPOINTER [Unknown attribute 1042]
SQLINTEGER -3
>
test f3b4-f974 EXIT SQLSetConnectAttr 返回码为-1(SQL_ERROR)
SQLHDBC 0x023ECB78
SQLINTEGER 1042 <unknown>
SQLPOINTER [Unknown attribute 1042]
SQLINTEGER -3
>
DIAG [S1C00] [Oracle][ODBC]驱动程序无法执行。(0)
如果我们使用第三方查看器,我们可以生成大多数报告。如果运行不使用.qry文件的报告,我们没有任何问题。

是否有人曾经遇到类似的问题?或者有人知道可能已经发生了什么变化,我们不知道是否因为任何补丁等原因?

英文:

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?

答案1

得分: 3

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 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 analyze.

The file format documentation for .dbf files shows that, amongst others, there are 3 bytes for the date of the 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 won't let me comment ODBC连接 / Crystal Reports (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 source code to the program, I cannot do any of the suggested changes here. Luckily, I do not have to print a lot, so when I need to print, I now use a HEX editor (HxD) and edit bits 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 as long as I don't 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 ODBC连接 / Crystal Reports

Anyone know of a HEX editor I can use to run batch processing of files? ODBC连接 / Crystal Reports

英文:

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
> &quot;xBase error: Last file change date in error&quot;.

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 ODBC连接 / Crystal Reports (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 ODBC连接 / Crystal Reports

Anyone know of a HEX editor I can use to run batch processing of files ? ODBC连接 / Crystal Reports

答案2

得分: 1

我们遇到了完全相同的问题,尽管使用的是 SQL Server 2016。然而,我们甚至无法运行 .qry 文件。

如果将计算机日期更改为2019年中的日期,我们就可以运行 .qry 文件。

英文:

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.

答案3

得分: 0

症状还包括:

> xbase错误:错误中的最后文件更改日期

> 错误:自动化错误
>
>
> 调用的对象已断开与其客户端的连接。

我们在使用Crystal Report 7 / 8.5时遇到了这个问题。

当报告使用查询作为数据源时,会提示日期错误。

在将系统时间临时更改为2019年后,正常工作。

英文:

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.

答案4

得分: 0

解决这个“2020年Crystal Report错误”的方法是删除.qry中间文件

  • 在数据库中创建一个数据库视图,代替您在.qry中拥有的查询,
  • 使用Crystal Reports的“工具”创建一个新的.rpt文件。

但这需要您重新编写报告。最好直接迁移到一个更新的报告工具。

英文:

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.

答案5

得分: 0

除了这个答案之外,这是我们用于继续使用旧报告的解决方案:将CR8.5替换为CR11.5

*注意:*这些报告是从使用vb6开发的应用程序中启动的,我必须维护它。

  • 必须安装Crystal Reports 11,这意味着我们必须购买新的许可证。
  • 然后,用新的vb6 CR8.5组件替换旧的组件非常简单。从以下来源获得了很多帮助:
    • 一些旧的CR8迁移文档 - 我们必须按照从OCX迁移到报告设计器组件(RDC)的步骤,包括Crystal Report 8 ActiveX Designer Runtime库(CRAXDRT),

    • 然后,关于如何使用CRAXDRT的扩展文档非常有帮助(但似乎是早期版本,没有解决所有问题),

    • 在需要设置报告中的变量时,我必须使用这里找到的方法

          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 &quot;ID_Context&quot;
                              .SetCurrentValue Val(v)
                      End Select
                  End With
              Next
          End Sub
      

限制:
... 仍在寻找在不必部署完整的Crystal Report BOXI的情况下在客户端上部署VB6 exe的方法:(

英文:

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 &quot;ID_Context&quot;
                              .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 ODBC连接 / Crystal Reports

huangapple
  • 本文由 发表于 2020年1月3日 22:56:04
  • 转载请务必保留本文链接:https://go.coder-hub.com/59580722.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定