Temporary Tables not found

[genriquez]

Hi

I Try to open a temporary table with Delphi XE2 and dbexppgsql40.dll driver, but they can´t find the table.

Finally i found with the Driver param FetchAll = True working goood, but FetchAll=False nop.

I Hope be usefull this information.

Bye.

[AlexP]

hello,

We have checked work with temporary tables in our dbExpress Driver For PostgreSQL and have not found your problem. The small console application below demonstrates work with temporary tables. If the application is executed on your PC without errors, please modify it, so that the problem can be reproduced, and send it to us.
Please describe the problems you have encountered while setting the FetchAll property to False.

Code: Select all

program Project15;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils, Data.DB, Data.SqlExpr, DBXDevartPostgreSQL;

var
  SQLConnection: TSQLConnection;
  SQLQuery: TSQLQuery;
begin
  SQLConnection := TSQLConnection.Create(nil);
  try
    SQLConnection.DriverName := 'DevartPostgreSQL';
    SQLConnection.ConnectionName := 'Devart PostgreSQL';
    SQLConnection.GetDriverFunc := 'getSQLDriverPostgreSQL';
    SQLConnection.LibraryName := 'dbexppgsql40.dll';
    SQLConnection.LoginPrompt := False;
    SQLConnection.Params.Values['DataBase'] := 'postgres';
    SQLConnection.Params.Values['HostName'] := 'hostname:5432';
    SQLConnection.Params.Values['User_Name'] := 'postgres';
    SQLConnection.Params.Values['Password'] := 'postgres';
    SQLConnection.Connected := True;
    SQLConnection.ExecuteDirect('CREATE LOCAL TEMPORARY  TABLE temp_table (id numeric)');
    SQLConnection.ExecuteDirect('INSERT INTO temp_table VALUES (1),(2),(3),(4),(5),(6)');
    SQLQuery := TSQLQuery.Create(nil);
    try
      SQLQuery.SQLConnection := SQLConnection;
      SQLQuery.SQL.Text := 'SELECT * FROM temp_table';
      SQLQuery.Open;
      while not SQLQuery.Eof do
      begin
        Writeln(SQLQuery.Fields[0].Value);
        SQLQuery.Next;
      end;
    finally
      SQLQuery.Free;
    end;
  finally
    SQLConnection.Free;
    Readln;
  end;
end.

[genriquez]

Hi again.

I Just add a line

SQLConnection.Params.Values['FetchAll'] := 'False'

and doesen't work.

{$APPTYPE CONSOLE}

{$R *.res}

uses
System.SysUtils, Data.DB, Data.SqlExpr, DBXDevartPostgreSQL;

var
SQLConnection: TSQLConnection;
SQLQuery: TSQLQuery;
begin
SQLConnection := TSQLConnection.Create(nil);
try
SQLConnection.DriverName := 'DevartPostgreSQL';
SQLConnection.ConnectionName := 'Devart PostgreSQL';
SQLConnection.GetDriverFunc := 'getSQLDriverPostgreSQL';
SQLConnection.LibraryName := 'dbexppgsql40.dll';
SQLConnection.LoginPrompt := False;
SQLConnection.Params.Values['DataBase'] := 'postgres';
SQLConnection.Params.Values['HostName'] := 'localhost:5432';
SQLConnection.Params.Values['User_Name'] := 'postgres';
SQLConnection.Params.Values['Password'] := 'postgres';

SQLConnection.Params.Values['FetchAll'] := 'False';
SQLConnection.Connected := True;
SQLConnection.ExecuteDirect('CREATE LOCAL TEMPORARY TABLE temp_table (id numeric)');
SQLConnection.ExecuteDirect('INSERT INTO temp_table VALUES (1),(2),(3),(4),(5),(6)');
SQLQuery := TSQLQuery.Create(nil);
try
SQLQuery.SQLConnection := SQLConnection;
SQLQuery.SQL.Text := 'SELECT * FROM temp_table';
SQLQuery.Open;
while not SQLQuery.Eof do
begin
Writeln(SQLQuery.Fields[0].Value);
SQLQuery.Next;
end;
finally
SQLQuery.Free;
end;
finally
SQLConnection.Free;
Readln;
end;
end.

[AlexP]

Hello,

To make the FetchAll = False mode work with temp tables, you should start a new transaction before creating a temp table, otherwise, when attempting to open the table, a new transaction will be started, in which the created temp table will not be seen

[genriquez]

Ok. thanks a lot.