How to disable "heading" for all firebird subrequests

Question

I have a request for firebird with concatenation and using nested select and list (), and then written this to the file. The first command is SET HEADING OFF;

SET HEADING OFF;
SELECT DISTINCT '"' || REPLACE(TRIM(COALESCE(x.column, '')), '"', '""')
|| '"; "' || REPLACE(TRIM(COALESCE(x.column2, '')), '"', '""')
|| '"; "' || REPLACE(TRIM(COALESCE(list(DISTINCT x.column3, ','), '')), '"', '""')
|| '";'
FROM (
     SELECT ycolumn AS column, ycolumn1 AS column1, ycolumn2 AS column2, list(DISTINCT ycolumn3, ',') AS column3
FROM (
     SELECT d.column AS ycolumn, c.column1 AS ycolumn1, dc.column2 AS ycolumn2, ws.column3 AS ycolumn3
     FROM ...
     )y
     GROUP BY ycolumn, ycolumn1, ycolumn2
) x
GROUP BY x.column, x.column1, x.column3
;

The problem is that the headers for the nested SELECTs are not disabled and outputs file is like this:

==============================================================================
            0:218
==============================================================================
CONCATENATION:
"field"; "field1"; "field2"; "field3";

Is it possible to disable headers for all requests?

I have try CAST(LIST(DISTINCT ycolumn3, ',') as VARCHAR(8000)), but it not help. — Alex Pen, May 28 '19 at 13:24
Could you provide a [mcve] that demonstrates this (that is provide necessary DDL, inserts and a complete select statement). I'd expect `set heading off` to suppress everything because it is only about displaying the column names and sub-selects are irrelevant in that context (ISQL doesn't know about the sub-selects, just about the columns returned by the main select query). I suspect the problem here is with displaying blobs and not so much the heading being on or off. Also note that this is technically a isql problem, and not a thing of Firebird server itself. — Mark Rotteveel, May 28 '19 at 13:34
are you sure you run that command in `isql` and not some other tool? if you connect to Firebird database by other applications, then ISQL-commands would not apply to them. Can you show your complete session, from the moment before you launch `isql` and to the moment you exit from `isql` ? https://firebirdsql.org/manual/isql-set.html#isql-set-heading <<== Unless `ISQL` application was heavily changed in FB3 this `0:218` definitely does NOT look like `ISQL` output. — Arioch 'The, May 28 '19 at 14:50
Guys, thanks a lot for comment. Problem was in bash script. I ran sql from bash and varchar(8000) maybe too big for execute. I change to varchar(3000) and it works! — Alex Pen, May 28 '19 at 15:41
@AlexPen `VarChar(8000)` should work if VarChar works at all. The most expensive, UTF-8, charset has 4 bytes for 1 letter, and FB's row is AFAIR 32KB limited (Mark below corrects this), hence a single-column query should be ably to in the worst circumstances carry a `VarChar(8192 - 1)` at least. — Arioch 'The, May 29 '19 at 09:42
Well, you problem perhaps was that you only casted `ycolumn3` rather than the whole expression. So, while the expression was growing larger and larger - Firebird had to stretch maximum-length expectations and finally it overcame VarChar limitation and then Firebird had to upgrade the whole expression to the BLOB. I overlooked your first comment, that it was about sub-sub-expression instead of all the result expression. — Arioch 'The, May 29 '19 at 09:47
Also, Alex, you first comment should not had been comment at all. It had to be part of question - you had to EDIT the question and add relevant technical info there. If you make late edits, you might put a comment alerting everyone of the edit made. Bus as a general rule, it is very unhandy to sieve comment-walls for pieces of information, so any news relevant information should be planted into the question itself rather than comments — Arioch 'The, May 29 '19 at 09:49

Mark Rotteveel · Accepted Answer · 2021-01-01T10:27:25.323

The problem is that LIST produces a BLOB SUB_TYPE TEXT, and ISQL defaults to using configuration BLOBDISPLAY set to 1 (to show BLOB SUB_TYPE TEXT). With this setting, ISQL will automatically output all text blobs inline per row, but to discern which blob is which, it will include the column alias when showing the blob content.

You could turn off blobdisplay using SET BLOBDISPLAY OFF, but then your query result will only show the blob-id and not the blob content which is probably not what you want. For the query in your question it would only show:

            0:218

The alternative is to cast the query to a VARCHAR of sufficient size:

SELECT DISTINCT cast('"' || REPLACE(TRIM(COALESCE(x.column, '')), '"', '""')
|| '"; "' || REPLACE(TRIM(COALESCE(x.column2, '')), '"', '""')
|| '"; "' || REPLACE(TRIM(COALESCE(list(DISTINCT x.column3, ','), '')), '"', '""')
|| '";' as varchar(8191))
FROM ...

Max VARCHAR size is 8191 for character set UTF8, or 32765 for a single byte character set, but there are additional constraints to row length (maximum 64KB total).

How to disable "heading" for all firebird subrequests

1 Answers1