Page 1 of 1
Direct execution of COPY command
Posted: Mon 05 Feb 2018 09:23
by Tarifer
Hello!
I want execute direct loading of data (not PgLoader) with:
How can I do it?
I have Professional license (no source code).
I use PgLoader now, but it works too slow and use 100% CPU on client. If I can use direct COPY, it should be many faster.
Thanks
Re: Direct execution of COPY command
Posted: Thu 08 Feb 2018 01:59
by nickbarnes
It's complicated, and a bit fragile, but possible. Mine looks something like this:
Code: Select all
uses Classes, Math, PgAccess, PgClasses, PgError, PgSQLNet, PgSQLProtocol, DBAccess, CRTypes;
procedure StreamToTable(Connection: TPgConnection; TableName: String; Stream: TStream);
const
C_BufferSize = 256*1024; // Experimentally determined optimum; yours may be different
C_ClientErrorMessage = 'COPY terminated by client';
var
Net: TPgSQLNet;
Buffer: TBytes;
ByteCount: Integer;
Query: TPgQuery;
Protocol: TPgSQLProtocol;
begin
Connection.Connect; // Make sure IConnection is assigned
Protocol := (TDBAccessUtils.GetIConnection(Connection) as TPgSQLConnection).GetProtocol;
Query := TPgQuery.Create(nil);
try
Query.Connection := Connection;
Query.SQL.Text := 'COPY ' + TableName + ' FROM STDIN';
Query.Prepare; // Necessary to suppress automatic UnPrepare while we're in COPY mode
Query.ExecSQL;
try
Net := nil;
while Stream.Position < Stream.Size do begin
Protocol.BeginCopyDataBlock(Net);
try
ByteCount := Min(Stream.Size - Stream.Position, C_BufferSize);
SetLength(Buffer, ByteCount);
Stream.ReadBuffer(Buffer, ByteCount);
Net.WriteBytes(TValueArr(Buffer), 0, ByteCount);
finally
Protocol.EndCopyDataBlock;
end;
end;
Protocol.PutCopyEnd;
except
on E: Exception do begin
if Protocol.ProtocolState = psCopyIn then begin
// We're still in COPY mode, i.e. error was client-side. Tell the server.
try
Net.WriteAnsiChar(PG_MSG_COPY_FAIL);
Net.WriteInt32(4 + Length(C_ClientErrorMessage) + 1);
Net.WriteAnsiString(C_ClientErrorMessage);
Net.FlushSend;
Net.WriteAnsiChar(PG_COMMAND_SYNC);
Net.WriteInt32(4);
Net.FlushSend;
Protocol.ProcessMessageQueue;
except
on E: EPgError do begin
// query_canceled is the expected response to a CopyFail; swallow it
if E.ErrorCode <> '57014' then begin
raise;
end;
end;
end;
end;
raise;
end;
end;
finally
Query.Free;
end;
end;
It's up to you to ensure that the connection's client_encoding matches the TStream's contents.
If you want the affected record count, I think that requires changes to the source (as does COPY TO STDOUT support).
Re: Direct execution of COPY command
Posted: Fri 09 Feb 2018 14:13
by azyk
You can vote for implementing the COPY FROM/TO functionality at our UserVoice:
https://devart.uservoice.com/forums/104 ... ctionality . If the suggestiong receives a sufficient number of votes, we will consider the possibility of its implementation.
Re: Direct execution of COPY command
Posted: Fri 16 Feb 2018 08:27
by Tarifer
Thanks!
It is very trick code :)