Handling of sql temporary tables in Delphi

Question

Good afternoon I create in Delphi a Sql temporary table, that is, it does not exist physically. I can even record data to it, but I can’t put the information in a dbgrid:

1) I believe the table so:

qryCIDtemp.SQL.add('Create table ''#tempCiD''(SUS varchar(50), Saude varchar(50), Diferenca varchar(30), dtCompetencia char(6)');  //Cria tabela temporária
qryCIDtemp.Close;
qryCIDtemp.sql.clear;// limpa conteúdo do dataset

qryCIDtemp.sql.Add('Insert into ''#tempcid''(SUS,Sanitas,Diferenca,dtCompetencia)');
qryCIDtemp.sql.Add('values (:SUS,:Saude,:Diferenca,:dtCompetencia)');

2)I recorded inside while not eof..., just a piece of code here for you to understand:

qryCiDtemp.ParamByname('SuS').value    := qryCidProcedimentoBKP.FieldByName('cdcid10').AsString ;
qryCIDtemp.ParamByname('Saude').value:= qryCid.FieldByName('cdcid10').AsString ;
qryCIDtemp.ParamByname('Diferenca').value:= 'CID';

3) I don’t know if it was necessary but I used the following command:

qryCIDtemp.SQL.add('Select * from ''#tempCID''');

4) Only after this I tried to show the output in dbgrid using the code: Obs.: dbgrid is without datasource

with qryCIDtemp do
       begin
            dbgrid2.Columns.Clear;
            dbgrid2.Columns.Add;
            dbgrid2.Columns[0].FieldName :='sus';
            dbgrid2.Columns[0].Title.Caption := 'Informação SUS';
            dbgrid2.Columns[0].Width := 500;
            dbgrid2.Columns.Add;
            dbgrid2.Columns[1].FieldName := 'SAuDE';
            dbgrid2.Columns[1].Title.Caption := 'Informação Saúde';
            dbgrid2.Columns[1].Width := 500;
            dbgrid2.Columns.Add;
            dbgrid2.Columns[2].FieldName := 'Diferença';
            dbgrid2.Columns[2].Title.Caption := 'Diferença';
            dbgrid2.Columns[2].Width := 200;
       end;

dbgrid even shows the captions and the width correctly, but this empty.

in the form I have a query named qryCIDtemp, in the string proppriende there is no SQL sentence. It has a Dasource connected to qryCIDtemp

The Datasource property of qryCIDtemp is empty, I have to put some information here? As it is temporary do not know what to fill.

If anyone can help me, I’d be grateful

Answer 1

Delphi querys only connect directly to the database they do not store temporary data.

To do what you want to use Tclientdataset (Delphi 7) or Tfdmemtable (Delphi XE5+)

In case you will not deal with creates and Inserts, you would +/- so

oMemTable.Append;
oMemTable.FieldByName('Saude').value     := qryCid.FieldByName('cdcid10').AsString ;
oMemTable.FieldByName('Diferenca').value := 'CID';
oMemTable.Post;

Ai yes later when you want to save in the bank, you make a loop in this oMemTable and record each record.

Answer 2

In the Tool Palette’s Data Access palette look for Tclientdataset, it has features to accomplish exactly what you need.. because it can save in memory your temporary table and enables you to get these records right after.

Answer 3

After filling in the parameters, but before consulting the data, you have to call the Execsql method for the system to run the INSERT in the database:

// preenche os parâmetros
qryCiDtemp.ParamByname('SuS').value := qryCidProcedimentoBKP.FieldByName('cdcid10').AsString;
qryCIDtemp.ParamByname('Saude').value:= qryCid.FieldByName('cdcid10').AsString;
qryCIDtemp.ParamByname('Diferenca').value:= 'CID';

// executa o comando contido em qryCiDtemp.SQL no banco de dados
qryCiDtemp.ExecSQL;

// consulta os dados do banco
qryCIDtemp.SQL.add('Select * from ''#tempCID''');