How to return empty record from a Select?

Question

I’m creating a system in PHP and in it has a Script which selects the data of the module requested by the user. The Script creates the form to view and edit the data according to the information of the table fields. The problem I’m facing is that when the request is an inclusion of record, still need to be made a Select in the table so that the form fields are displayed correctly, which is not happening. I need the query to return an empty record so that the Script can read the information of the fields.

The Select is as follows:

select ID, DATA, DESCRICAO from FERIADOS where (ID = 0)

I’ve tried it many ways, but when it doesn’t come back, it all comes back:

select null, ID, DATA, DESCRICAO from FERIADOS where (ID = 0) //Não retorna nada
select ID, DATA, DESCRICAO from FERIADOS where (ID = 0) or (1=1) //Retorna todos os registros
select ID, DATA, DESCRICAO from FERIADOS where (ID = 0) and (1=1) //Não retorna nada
select coalesce(ID,''), coalesce(DATA,''), coalesce(DESCRICAO,'') from FERIADOS where (ID = 0) //Não retorna nada
select first 1 ID, DATA, DESCRICAO from FERIADOS //Eu poderia utilizar esse, mas há casos de tabelas vazias e ainda terei que zerar os valores dos campos

I thought of creating Stored Procedure to empty each field if the query was empty, but it is impossible to maintain the code, because I would have to create a SP for each table and the idea of the project is to use only one Script for each type of request.

Which function I could add in Select for the query to show the fields even if it returns no record?

Answer 1

Edited, In detail.

With this Voce version you get the detailed information about the fields:

SELECT 
    r.RDB$FIELD_NAME AS field_name,
    r.RDB$DESCRIPTION AS field_description,
    r.RDB$DEFAULT_VALUE AS field_default_value,
    r.RDB$NULL_FLAG AS field_not_null_constraint,
    f.RDB$FIELD_LENGTH AS field_length,
    f.RDB$FIELD_PRECISION AS field_precision,
    f.RDB$FIELD_SCALE AS field_scale,
CASE f.RDB$FIELD_TYPE
    WHEN 261 THEN 'BLOB'
    WHEN 14 THEN 'CHAR'
    WHEN 40 THEN 'CSTRING'
    WHEN 11 THEN 'D_FLOAT'
    WHEN 27 THEN 'DOUBLE'
    WHEN 10 THEN 'FLOAT'
    WHEN 16 THEN 'INT64'
    WHEN 8 THEN 'INTEGER'
    WHEN 9 THEN 'QUAD'
    WHEN 7 THEN 'SMALLINT'
    WHEN 12 THEN 'DATE'
    WHEN 13 THEN 'TIME'
    WHEN 35 THEN 'TIMESTAMP'
    WHEN 37 THEN 'VARCHAR'
    ELSE 'UNKNOWN'
END AS field_type,
    f.RDB$FIELD_SUB_TYPE AS field_subtype,
    coll.RDB$COLLATION_NAME AS field_collation,
    cset.RDB$CHARACTER_SET_NAME AS field_charset
FROM RDB$RELATION_FIELDS r
    LEFT JOIN RDB$FIELDS f ON r.RDB$FIELD_SOURCE = f.RDB$FIELD_NAME
    LEFT JOIN RDB$COLLATIONS coll ON f.RDB$COLLATION_ID = coll.RDB$COLLATION_ID
    LEFT JOIN RDB$CHARACTER_SETS cset ON f.RDB$CHARACTER_SET_ID = cset.RDB$CHARACTER_SET_ID
WHERE 
    r.RDB$RELATION_NAME='NOME_TABELA'  
ORDER BY 
    r.RDB$FIELD_POSITION;

To select only the field names of an SQL table, do:

SELECT 
    RDB$FIELD_NAME
FROM 
    RDB$RELATION_FIELDS
WHERE 
    RDB$RELATION_NAME='NOME_TABELA'

Observing:

Used this in FB 2.1, it’s been a long time, but should work in current versions.

Answer 2

Trying here, I remembered the UNION I’ve used in some cases. I made an adaptation in Select and ended up working, but still I consider the answer of @Sidon the most appropriate, I only decided to use this for adaptation to my code, as it will be less work in maintaining it.

select ID, DATA, DESCRICAO from FERIADOS where (ID = 0)
union all
select NULL, NULL, NULL from rdb$database

The first Select will not return anything because of the 0 value purposely passed to the ID field. The second Select after the union all will return an empty record. In this case, the amount of columns must be equal in both Selects.

Answer 3

Dude, I think you’re doing it wrong. you do not need to know has the fields but if the number of rows that returned is greater than zero. to bring any return something you must fetch and that has content. sgbd will not bring your query totally empty.

I need the query to return an empty record so Script can read the information of the fields.

I believe that you are reading your text the wrong way if you want an empty record, do an Insert and then display the unfilled data, but in my view this is completely wrong. I suggest you check without have any line if you have filled with what came , if nothing comes, fill in anything.