Query capture limit of 1023 characters

Discussion of open issues, suggestions and bugs regarding usage of dbExpress drivers for SQL Server in Delphi and C++Builder
Post Reply
Albervan
Posts: 12
Joined: Mon 12 Mar 2018 16:10

Query capture limit of 1023 characters

Post by Albervan » Thu 25 Oct 2018 13:48

Great day!

We started using the Devart driver a short time ago with the objective of creating in our ERP a routine monitoring functionality.

This functionality, when enabled, has as one of the main requirements, storing all the submitted messages to the database. The main reason we start using the devart driver is to improve the capture of information, since the default driver does not provide complete information.

However, we are faced with a critical problem: the driver apparently does not provide support for fetching queries larger than 1023 characters.

We are conducting tests in two ways:
  1. With the TSQLMonitor component (dbExpress). This component uses a record (SQLTraceDesc) to capture information that is limited to 1023 characters. We made a change in this implementation, creating another component inherited from TSQLMonitor to solve this limitation, but we did not succeed;
  2. Alternatively, we try to intercept the queries by the TraceCallBack event of the connection. In this case we do not have the TSQLMonitor component limitation. But we were not successful either.
Have you ever encountered this problem? Is there any fix or solution to this?

We are using Delphi 7 + MSSQL 2008R2 + Driver Devart 7.4.6.0

Stellar
Devart Team
Posts: 164
Joined: Tue 03 Oct 2017 11:00

Re: Query capture limit of 1023 characters

Post by Stellar » Fri 26 Oct 2018 09:52

In Delphi 7, the TSQLMonitor component traces the information about calls in the SQLTRACEDesc data structure. In this structure, a message is transmitted as an array of 1024 characters. Unfortunately, this is a limitation of DBX technology that we cannot influence in any way. In later versions, Delphi developers have removed the limitation on the length of the message, using the String type to store the message.

Albervan
Posts: 12
Joined: Mon 12 Mar 2018 16:10

Re: Query capture limit of 1023 characters

Post by Albervan » Fri 26 Oct 2018 13:28

Great day, Stellar!

We figured the reason for this limitation would be something like that.
But we need a solution to work around this problem and be able to capture the complete transactions with the database. We've already done a lot of research looking for alternative implementations for this, but we did not find anything useful in this case.

Among Devart's products we find dbMonitor, which can sternly do this work, and capture the complete commands. But we need an integrated solution to our ERP that can run internally or in the background, generating a log file with the captured commands.
In dbMonitor we did not find the resources to do this kind of integration.

An alternative that we imagine would be to enable the integration of this application with our ERP.
It could be through improvements in the application allowing it to run in the background and automatically generate the log file, by passing parameters to it.
Another option would be to acquire with your team the sources of this application to include internally in the ERP.
Or, some guidance on how to develop something similar in our ERP.

Are there any of these options to help us?

Albervan
Posts: 12
Joined: Mon 12 Mar 2018 16:10

Re: Query capture limit of 1023 characters

Post by Albervan » Fri 26 Oct 2018 14:15

Stellar wrote:
Fri 26 Oct 2018 09:52
In later versions, Delphi developers have removed the limitation on the length of the message, using the String type to store the message.
We work here with BDS2006 as well. And in the structure of it also has that same limitation.
Can you tell us from which version was this improvement made available?

Stellar
Devart Team
Posts: 164
Joined: Tue 03 Oct 2017 11:00

Re: Query capture limit of 1023 characters

Post by Stellar » Thu 15 Nov 2018 10:54

dbMonitor can log application requests to the database, but cannot work as a service.
dbMonitor is a tool for visual monitoring your application requests to databases, and is an auxiliary tool for designing applications that interact with various databases.
The most correct solution for logging queries is to use the TSQLMonitor component. But it is necessary to use Delphi 2007, or a later version of Delphi, since in these versions the message length limitation was removed.
You can fully work with servers using Devart dbExpress drivers. But we recommend using our other components - SDAC: devart.com/sdac/ and UniDAC: devart.com/unidac. They are better in performance and functionality than using dbExpress technologies, they support Linux and mobile platforms, etc.

Post Reply