DML Array and LargeInt

[LeChuck]

I'm trying to use LargeInt with DML Array parameters (similar to the example in your documentation (https://www.devart.com/odac/docs/#work_dml.htm)).

My code looks something like the following. It works if all item values are set. But I need NULL for some values, so I use ItemClear(x) in these cases.

Code: Select all

OraSQL1.ParamByName('Param1').DataType := ftLargeInt;
OraSQL1.ParamByName('Param2').DataType := ftString;
OraSQL1.ParamByName('Param1').Length := 1000;
OraSQL1.ParamByName('Param2').Length := 1000;
for i := 1 to 1000 do
begin
  if (i == 5) then // stupid random example or course
  begin
    OraSQL1.ParamByName('Param1').ItemClear(i);
  end
  else
  begin
    OraSQL1.ParamByName('Param1').ItemValue[i] := 123;
  end;
  OraSQL1.ParamByName('Param2').ItemValue[i] := 'some text';
end;
OraSQL1.Execute(1000);

As soon as one LargeInt item is NULL I get "OCI-22060: argument [2] is an invalid or uninitialized number".
If all items have actual values it works. If I use ftInteger instead of ftLargeInt is also works. But I need to use Int64 here.
Is there a way to make it work?

Thanks,
Markus

BTW: I'm using ODAC 9.2.7

[AlexP]

Hello,

We cannot reproduce the problem on the latest ODAC version 9.5.15, please try to reproduce the problem on the latest ODAC version and let us know the result.

[LeChuck]

I tested it with 9.3.10 and can confirm that the issue is fixed.
Thanks for your help.

Markus

[AlexP]

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

[LeChuck]

I'm sorry to re-open this thing, but I used the wrong use-case before. In the meantime I updated to ODAC 9.5. and the problem still exists. As long as I use ftInteger for my parameters I have no errors, only with ftLargeInt. But I have to use ftLargeInt as we are in the process of migrating all of our IDs from 32 bit to 64 bit.

To be honest, I'm not sure if DML arrays are supposed to work with Int64 already, as there is an "ItemAsInteger[]" but no "ItemAsLargeInt[]". So I'm setting the DataType explicitly to ftLargeInt and use ItemValue[] to set my Int64 value:

Code: Select all

  Query.ParamByName('myparam').DataType := ftLargeint;
  Query.ParamByName('myparam').Length := 2;
  Query.ParamByName('myparam').ItemValue[1] := 5000000001;
  Query.ParamByName('myparam').ItemValue[2] := 5000000002;

This works, as long as I provide a value for each item. But sometimes I don't have a value, so I want "null" in the database. To my understanding, this is what ItemClear() should do.
So I'm using something like this:

Code: Select all

  Query.ParamByName('myparam').DataType := ftLargeint;
  Query.ParamByName('myparam').Length := 2;
  Query.ParamByName('myparam').ItemValue[1] := 5000000001;
  Query.ParamByName('myparam').ItemClear(2);

But this code leads to an exception: "Project SpeedTest.exe raised exception class EOraError with message 'OCI-22060: argument [2] is an invalid or uninitialized number'."

I get the feeling that ItemClear() does not work properly for LargeInt/Int64.

I'd hope you can give me some advice.

Thanks,
Markus

[AlexP]

Thank you for the information, we have reproduced the problem and will investigate the reasons for this kind of behaviour. We will inform you as soon as we have any results.

[LeChuck]

Any progress yet?

Thanks,
Markus

[MaximG]

The problem is fixed, and the fix will be included in the next build, that will be released next week.
A workaround until the release:

OraSQL.ParamByName('myparam').ParamType := ptInput

[LeChuck]

That's great news, thanks!
Thanks also for the immediate help. I tested the line and it works.

But of course I will update to the new official version as soon as it is available.

Markus

[AlexP]

Our products will be released within 2 days.