Temporary Tables not found

Temporary Tables not found

Postby genriquez » Tue 27 Nov 2012 17:36

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.
genriquez
 
Posts: 6
Joined: Tue 27 Nov 2012 17:26

Re: Temporary Tables not found

Postby AlexP » Wed 28 Nov 2012 08:55

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.
AlexP
Devart Team
 
Posts: 5528
Joined: Tue 10 Aug 2010 11:35

Re: Temporary Tables not found

Postby genriquez » Wed 17 Apr 2013 15:42

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.
genriquez
 
Posts: 6
Joined: Tue 27 Nov 2012 17:26

Re: Temporary Tables not found

Postby AlexP » Fri 19 Apr 2013 10:29

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
AlexP
Devart Team
 
Posts: 5528
Joined: Tue 10 Aug 2010 11:35

Re: Temporary Tables not found

Postby genriquez » Tue 30 Apr 2013 22:00

Ok. thanks a lot.
genriquez
 
Posts: 6
Joined: Tue 27 Nov 2012 17:26


Return to dbExpress driver for PostgreSQL