Oracle 10g - wrong Integer field types in v1.20.x and v2

[tobias_cd]

Hello,

I've been going crazy over UniDac mixing up Oracle columns types returned in a datasets FieldDefs!

During debugging I noticed, that the internal TOCICommand "IntegerPrecision" was always reset to "9" (hardcoded somewhere), although Oracle reports 38 for the INTEGER columns.
So OraClassesUni.pas -> GetFieldDesc8 always jumps in the wrong branch and sets the DataType wrongly to ftFloat.
The internal function GetIntegerPrecision has code to use "FConnection.IntegerPrecision", but the documentation does not mention on how to change that setting, if at all possible (TUniConnection does not have any public or protected property for that at least).

Anyways, I had to make some changes to OraClassesUni to make it work (had no problem with MS SQL) and also add "OraClassesUni.IntegerPrecision := 38;" line before creating a TUniConnection instance; in the many attempts I also tried using the SpecificOptions, but no difference.

Is this a known bug investigated currently? This issue is in both v1 and the current v2 of UniDac. What am I missing? This issue took me quite a while to workaround.

One more side-effect: setting TUniQuery's CachedUpdates to True clears all it's "Params" if any are set manually. Not sure if this is supposed to be documented somewhere.

Edit:
Oracle returns Integer fields with precision = 38, scale = 0 and Number fields with precision = 0 and scale = -127.

Regards,
Tobias

[Plash]

You don't need to change UniDAC sources.

Set PrecisionInteger = 38 in the SpecificOptions of TUniConnection.

[tobias_cd]

Plash: thank you for getting back to me.

It's unfortunate, that not all SpecificOptions of e.g. the OracleUniProvider unit are mentioned in the help system, maybe in a next release?

There is also a "PrecisionFloat", but it is unclear to me, what purpose it has or when it is to be used. Maybe you can provide some insight to this, too, please?

The PrecisionInteger option indeed resolves the Integer column type, but now the Number (Oracle) columns are also cast as ftInteger fields, not ftFloat. I tried setting PrecisionFloat, too, but it wasn't used as far as I can tell.

In unit OraClassesUni.pas, line 7778+ the "Prec" and "Scale" variables are eventually set to 38/0, which in combination with PrecisionInteger = 38 leads to ftInteger.
The original Prec/Scale variable values are 0/-127 for "Number" columns, though, and the meaning of that combination being ftFloat gets "lost".

My initial workaround for that is to add the following line at line 7778 (with a new flag):

Code: Select all

IsInteger := (Prec = IntegerPrecision) and (Scale = 0);

and encapsulate the integer handling from then line 7789 to7808 in an IF like this:

Code: Select all

if(IsInteger) then begin
...
end

This also did not break the same SQL tests for MS SQL, so that's the way I have to go for now, unless you can provide me the correct solution to this, please.

Thank you in advance,
Tobias

[tobias_cd]

P.S.:
In addition to Oracle.PrecisionInteger in the SpecificOptions, I still have to add this line in my app before creating a new TUniConnection:

Code: Select all

OraClassesUni.IntegerPrecision := 38;

[Plash]

We have fixed the problem with mapping NUMBER to ftInteger. The fix will be included in the next build of UniDAC.

You can fix the problem if you comment the following lines in OraClassesUni.pas:

if Scale = - 127 then
Scale := 0;

You don't need to make any other changes in the code, and you don't need to assign value to the IntegerPrecision global variable.

Just set PrecisionInteger in SpecificOptions.

The PrecisionFloat option is used when you set Options.EnableFmtBcd to True. In this case fields with precision larger then PrecisionFloat are mapped to ftFmtBcd.

[tobias_cd]

We have fixed the problem with mapping NUMBER to ftInteger. The fix will be included in the next build of UniDAC.

Excellent news, thank you very much! I removed my custom changes and applied the change you indicated and it works as desired.

Great service!

Best regards and happy holidays!
Tobias

[org-team]

We have fixed the problem with mapping NUMBER to ftInteger. The fix will be included in the next build of UniDAC.

Is this Problem fixed in the Version 2.00.0.3?
I´ve just downloaded the File and installed it, but I get the same error like tobias_cd.... In which Version will the Problem be fixed?

[Plash]

Yes, the fix was added to the build 2.00.0.3. Please give a more detailed description of your prroblem. Specify the data type of your field in the database. Which type it is mapped on in UniDAC, and which type do you need ?

[tobias_cd]

Yes, the fix was added to the build 2.00.0.3. Please give a more detailed description of your prroblem. Specify the data type of your field in the database. Which type it is mapped on in UniDAC, and which type do you need ?

Hello Plash,
I've installed the current Pro build and the problem still exists.
Just try a table with both INTEGER and NUMBER fields and you'd see "different data type expected" error appear.
Setting the new variable "NumberAsInteger" does not help because then the error will just "switch" (either integer or number field is then wrong).
The most recent changes in "OraClassesUni.pas -> TOCICommand.GetFieldDesc8" by devart are not correct in my opinion. My code changes from when I started this thread do work for me (in combination with "OraClassesUni.IntegerPrecision := 38;").

[Plash]

The NumberAsInteger variable is added for backward compatibility. If you set it to True, fields are mapped as they was before the fix.

You should check field types that are created. INTEGER data type is mapped on TIntegerField, NUMBER data type is mapped on TFloatField. Maybe you have created fields of other types at design time. In this case you should recreate the fields in Fields Editor.