How to return empty record of a Select?

Navigation
1

I'm creating a system in PHP and it has a Script that selects the module data requested by the user. Script creates the form to display and edit the data as per the field information of the tables. The problem I'm facing is that when the request is a record addition, I still need 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 field information.

Select is as follows: 

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

I've tried it in many ways, but when it does not return any, it returns all: 

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 about creating Stored Procedure to empty each field if the query was empty, but it is not possible to maintain the code, since I would have to create a SP for each table and the project idea is to use only Script for each type of request.

What function could I add in Select so that the query shows the fields even though no records return?

    
asked by anonymous 14.06.2017 / 21:07

3 answers

1
  

Edited, Detailing.

  With this 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 a table in SQL, do: 

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

Note:

I used this in FB 2.1, it's been a long time, but it should work on the current versions.     
14.06.2017 / 21:21
0

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

  

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

I think you're reading your text the wrong way if you want an empty record, make an insert and then display the unfilled data, but to my mind this is completely wrong. I suggest you check without having any line if you have fill with what came, if nothing comes, does not fill anything.

    
14.06.2017 / 21:17
0

Attempting here, I remembered the UNION I already used in some cases. I made an adaptation in Select and ended up working, but I still consider @Sidon's answer to be the most appropriate one, I just decided to use it as a matter of adaptation to my code, as it will be less laborious to maintain 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 value 0 passed intentionally to the ID field. The second Select after union all will return an empty record. In this case, the number of columns must be equal in both Selects.

    
15.06.2017 / 04:41
Set up school report How to fade some components in the background to highlight a TPanel on them?