crbatchmove duplicate key

[lao]

Hi,
i try to use crbatchmove to copy about 1500 rows from table a on the server to table a on localhost.
postgresql database 9.1.
if tables have a primary key with 2 fields( first is timestamp and second varchar) when i use crbatchmove in mode appendupdate and (abortononkeyviol and abortonproblem =true)
i have a key violation( he does not found the key for update and try to insert new record.
if i move less than 1000 record it work.
Regards

[DemetrionQ]

Hello.

    When working with data of TimeStamp fields, the TDateTime type is used, which is the Double type. The problem is in the specificity of work with floating-point types ( http://docwiki.embarcadero.com/RADStudi ... ing_Issues ). Therefore, in some cases, it is impossible to identify a record uniquely, if a TimeStamp field is used as a key field, and a "Key violation" error appear. To solve the problem you can use the following approaches:

 * Use data type mapping and map TimeStamp to String. You can do it with the following code:

Code: Select all

uses PgDataTypeMapUni;
...
UniConnection1.DataTypeMap.AddDBTypeRule(pgTimeStamp,ftString);
UniConnection1.DataTypeMap.AddDBTypeRule(pgTimeStampWithTimeZone,ftString);

 * Use Integer fields for primary key creation.

[lao]

hi DemetrionQ,
thank you for your response.
i try to map fields but does not resolv the problem.
i need to use timestamp as unique index.

any help will be welcome.
thanks

[lao]

Hi,
i resolved the problem by changing this in crbatchmove.pas

function TUniBatchMove.GetKeyValues: Variant;
var
i: integer;
begin
for i := 0 to FDestCountKeys - 1 do
begin
if FSrcKeyFields <> nil then
begin
if FSrcKeyFields.DataType=ftDateTime then
FKeyValues := FSrcKeyFields.AsString
else FKeyValues := FSrcKeyFields.AsVariant;
end
else
FKeyValues := ''
end;

if FDestCountKeys > 1 then
Result := VarArrayOf(FKeyValues)
else
if FDestCountKeys > 0 then
Result := FKeyValues[0]
else
Result := Null;
end;


UniConnection1.DataTypeMap.AddDBTypeRule(pgTimeStamp,ftString);
UniConnection1.DataTypeMap.AddDBTypeRule(pgTimeStampWithTimeZone,ftString);
does not work because the field type is recognized as tDateTime (in database is TimeStamp).
regards,

[DemetrionQ]

Hello.

Data Type Mapping should be used before opening TUniQuery. Please make sure that after opening TUniQuery the time stamp field has the TStringField type. In this case, there is no need for the described modification.

[lao]

Hi,
that's what i do.
on show of my form,
i close uniconnection
and i add:
UniConnection1.DataTypeMap.AddDBTypeRule(pgTimeStamp,ftString);
UniConnection1.DataTypeMap.AddDBTypeRule(pgTimeStampWithTimeZone,ftString);

but in crbatchmove.pas in the function GetKeyValues,
FSrcKeyFields datatype is ftDateTime for my field timestamp.
regards.

[DemetrionQ]

Hello.

I can't reproduce the problem when using data type mapping. Please provide the following information:
- the UniDAC version;
- the table creation script;
- the exact IDE version (for example RAD Studio 2009 Version 12.0.3420.21218).

[lao]

Hi Demetrion,
I am sorry the procedure you described works.
i have 2 uniconnections, 1 connected to the server(source for crbatchmove) and 1 connected to localhost (destination for crbatchmove) so i change the datatypemap for destination connection instead of source connection.
Regards.

[DemetrionQ]

Glad to see that you solved the problem. If any other questions come up, please contact us.

[lao]

Hi,
i have an other question:
i try to batchmove table t1 (appendupdate mode) 5000 rows from server A(interbase) to server B (interbase): the batch take about 29 seconds.

i try with bde, same databases same table and the batch take 5 secondes.

i try to convert ours applications from interbase to postgresql that's why we buy unidac.

for the moment i try to convert application from bde to unidac.

is it normal to multiply by 6 the time?
What can i do to increase performances?
i try with transaction, autoprepare, prepareupdatesql,cachedupdates.
regards,

[AndreyZ]

I cannot reproduce the problem. Please specify the following:
- the script to create your table;
- the exact version of InterBase server and client you are using. You can learn it from the Info sheet of TUniConnection Editor;
- the exact version of UniDAC. You can learn it from the About sheet of TUniConnection Editor;
- the exact version of your IDE.
Also, you can try creating a small sample that demonstrates this problem and send it to andreyz*devart*com . This way, I will be able to help you faster.