Page 1 of 1
Temporary Tables not found
Posted: Tue 27 Nov 2012 17:36
by 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.
Re: Temporary Tables not found
Posted: Wed 28 Nov 2012 08:55
by 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.
Re: Temporary Tables not found
Posted: Wed 17 Apr 2013 15:42
by 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.
Re: Temporary Tables not found
Posted: Fri 19 Apr 2013 10:29
by 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
Re: Temporary Tables not found
Posted: Tue 30 Apr 2013 22:00
by genriquez
Ok. thanks a lot.