📜 ⬆️ ⬇️

Trim response to sql query

UPDATE!

The problem is solved after a careful study of the documentation on the idc format. The length of the column in it is by default equal to 8192 bytes, and anything less is simply truncated. It is written that way. To display information above this, there is a special key MaxFieldSize , the value of which is the maximum response length in bytes. I will give a quote, can someone come in handy:
The MaxFieldSize field specifies the maximum number of bytes allocated by the IDC for each field in the .idc file.
--------------------------------------------------------------------------------
NOTE: The default field size is 8,192 bytes. If a query results in more bytes than allocated by the IDC MaxFieldSize field, subsequent data is truncated.
--------------------------------------------------------------------------------
To specify a MaxFieldSize of 64,000 bytes, use the following syntax in the MaxFieldSize field entry:
MaxFieldSize: 64000


UPDATE!

Dear Habrahabr, I came to ask your advice. I rummaged through all that I can and cannot find a solution.
The following problem occurs:
On MS SQL 2000 server there is a database in which there is a table in which there is a text column. It stores data (news) that must be constantly shown. The user invokes the script that is handled by IIS.
The script consists of two files - script.idc and template.htx. This is an old technology that allows you to separate the query to the database and the design template in which the result of the query is issued.
Content script.idc:
DataSource: sqlserv_tcp
Username: user1
Password: pass1
Template: template.htx
SQLStatement:
+SELECT mes_text ne_text FROM news_text (nolock) WHERE mes_id = '%mes_id%' and source_code = 'Source1'


Content template.htx:
<%begindetail%><%ne_text%>
<%enddetail%>

')
This script is accessed by the client program under Windows.
The essence of the problem - the data is cut off over 8 kilobytes. It does not depend on the client, because When you open the URL through the browser, everything is exactly the same. Rechecked everything. The server gives the data completely. We are concerned about internal limitations of ODBC or IDC file technology, but I haven’t found anything like this in manuals and documentation. Moreover, it is possible to set the key in the file:
ODBCOptions: SQL_MAX_LENGTH= Integer ,
specifying the maximum length in bytes returned in response to the request, which oddly does not work.

So far I have solved the problem by creating a double query, but it is not universal and works only for news less than 16 kb, which is unacceptable. Here it is, a request:
DECLARE @len int
SELECT @len=DATALENGTH(mes_text) FROM news_text (nolock) WHERE mes_id = '%mes_id%' AND source_code = Source1'
IF @len > 6000
BEGIN
SELECT SUBSTRING(mes_text,1,6000) ne_text, 1 sort FROM news_text (nolock) WHERE mes_id = '%mes_id%' AND source_code = 'Source1'
UNION
SELECT SUBSTRING(mes_text,6001,DATALENGTH(mes_text)) ne_text2, 2 sort FROM news_text (nolock) WHERE mes_id = '%mes_id%' AND source_code = 'Source1'
ORDER BY sort
END
ELSE
SELECT mes_text ne_text FROM news_text (nolock) WHERE mes_id = '%mes_id%' AND source_code = 'Source1'


Can anyone come across, or have any ideas? For several days now I have been fighting my head at the monitor ... :)

Source: https://habr.com/ru/post/40006/


All Articles