How insert records with DateTime field via DML array and not lose the milliseconds?

[andrefm]

Hi,
I have an insert query which contain two fields (params) of type TOraTimeStamp and it's working fine, but since I'm inserting bulk records, I would like to change to DML. (Will not use loader and lmDirect as I'm connecting via Direct TCP mode).

For the Insert statement I have to use:

Code: Select all

n_StartDateTime : TDateTime
...
Query.ParamByName('START_DATETIME').AsSQLTimeStamp := DateTimeToSQLTimeStamp(n_StartDateTime);

This is the only way I can keep the milliseconds. If I use the line below I will lose the millisecond information, is this a bug?

Code: Select all

Query.ParamByName('START_DATETIME').AsDateTime := n_StartDateTime;

Now I'm changing to DML array to increase the performance. My problem now is that I couldn't find a way or any sample to insert the TimeStamp and keep the milliseconds.

Since the type of Variant and TSQLTimeStamp are not compatible, even knowing that I would have issue with the milliseconds, I tried first using the

Code: Select all

 v_StartDateTime[i]:= n_StartDateTime;

But when trying to pass this array:

Code: Select all

 sqPut.ParamByName('START_DATETIME').Value := v_StartDateTime;

I'm getting the error:

EVariantTypeCastError 'Could not convert variant of type (Array Variant) inyo type (OleStr)'

(Other fields from type string, integer are working fine)


After the initial attempts I used the OraLoader and it worked using the field START_DATETIME as ftTimeStamp and passing the n_StartDateTime to it, but again I'm losing the milliseconds information.
Since I'm using a Direct Connection, I would prefer doing the data loading by hand instead using the OraLoader, but just to inform my problems with the milliseconds and all the methods I tried.

Now I need a solution to insert data to my TimeStamp field without losing the millisecond information.

Thank you

PS: Using ODAC 9.4.14, Delphi XE7 and Oracle 11 R2

[AlexP]

Hello,

You can set parameter values in a loop like the following:

Code: Select all

var
  i: integer;
begin
  OraSQL1.ParamByName('id').DataType := ftInteger;
  OraSQL1.ParamByName('id').Length := 10;

  OraSQL1.ParamByName('timestamp').DataType := ftOraTimeStamp;
  OraSQL1.ParamByName('timestamp').Length := 10;

  for i := 1 to 10 do begin
    dt:= now;
    OraSQL1.ParamByName('id').ItemAsInteger[i] := i;
    OraSQL1.ParamByName('timestamp').ItemAsTimeStamp[i].AsTimeStamp := StrToSqlTimeStamp('04.08.2015 12:12:12:999999');
  end;
  OraSQL1.Execute(10);
end;

[andrefm]

Thx that helped

[AlexP]

Glad to see that the issue was resolved. If you have any further questions, feel free to contact us.