- edited description
Oracle support fot clob and nclob. Currently FireDAC throws an error when a string is larger than 1000
When a large string is inserted or updated into an oracle clob or nclob field FireDAC will throw an exception if the FDparams data type is not set to ftwidememo.
The FireDAC docs suggest using ftwidememo for clob/nclob param types which works fine in our current implementation. See the Oracle section under Result Set Fields chart on this page Unicode Support Firedac
Currently there is no indicater on the column attribute to differentiate a varchar2 from a clob/nclob type. Also the Spring.Persistence.SQL.Params.TDBParam class has a method to convert the TValue.PTypeInfo to a db field type but does not take into consideration the length of a string and therefore always returns ftWidestring. This is found in the method TypeInfoToFieldType(typeInfo : PTypeInfo): TFieldType;
I have created a test under the TestFireDacAdapterOracle.pas unit Attached, If you run this test against an Oracle database it will fail with the above error.
BTW: Setting TFDParam.Size to a greater value does not work as the size can be only so big, and then another exception occurs. The solution to this that has worked for us is to use ftWideMemo on these large String types.
Comments (18)
-
reporter -
reporter - edited description
-
reporter - edited description
-
reporter - edited description
-
reporter - attached TestFireDACAdapterOracle.pas
To get this to work:
This test should added to the ORMTests project.
A valid Oracle Database must be available
Edit the CONNECTION_STRING const at the top of the file to provide your TNSNames value in Database, also provide your user_name and password values.
-
reporter - edited description
-
repo owner I pushed a proposal to the branch issue/141 - please take a look and let me know if that works.
I also looked into the Hibernate doc and found they have a Lob attribute that you can put on properties that should be handled as blob/clob then. So instead of checking the Size we can also add such attribute and check for its occurence instead of putting yet another parameter on the Column attribute.
-
reporter OK at the risk of being rejected again, Here is another suggested solution. :) BTW I knew you weren't going to like my mmAnywhereIgnoreCase solution. LOL.
Instead of passing in just the pTypeInfo to the TDBParam.TypeInfoToFieldType method the entire TValue is passed.
function TDBParam.TypeInfoToFieldType(Value: TValue): TFieldType; function GetMemberValueDeep(const initialValue: TValue): TValue; <-- NEED This method but since it is no longer exposed I just copied it here for now. I leave that to you to decide. begin Result := initialValue; if IsNullable(Result.TypeInfo) then begin if not initialValue.TryGetNullableValue(Result) then Result := TValue.Empty; end else if IsLazyType(Result.TypeInfo) then if not initialValue.TryGetLazyValue(Result) then Result := TValue.Empty; end; var typeInfo : PTypeInfo; begin typeInfo := Value.TypeInfo; if not Assigned(typeInfo) then Exit(ftUnknown); case typeInfo.Kind of tkInteger, tkSet: Result := ftInteger; tkInt64: Result := ftLargeint; tkChar, tkLString, tkString: Result := ftString; tkWChar, tkWString, tkUString: Result := ftWideString; tkEnumeration: if typeInfo = System.TypeInfo(Boolean) then Result := ftBoolean else Result := ftInteger; tkFloat: if typeInfo = System.TypeInfo(TDateTime) then Result := ftDateTime else if typeInfo = System.TypeInfo(TDate) then Result := ftDate else if typeInfo = System.TypeInfo(TTime) then Result := ftTime else if typeInfo = System.TypeInfo(Currency) then Result := ftCurrency else if typeInfo = System.TypeInfo(Extended) then Result := TFieldType.ftExtended else Result := ftFloat; tkClass, tkArray, tkInterface, tkDynArray: Result := ftBlob; tkVariant: Result := ftVariant; tkRecord: if IsNullable(typeInfo) or IsLazyType(TypeInfo) then Result := TypeInfoToFieldType(GetMemberValueDeep(Value)) else Result := ftBlob; tkClassRef, tkPointer: Result := ftReference; else Result := ftUnknown; end; end;
Now that we have the TValue and can ask for it as a string, the TOracleDBParam.TypeInfoToFieldType method can check the length of the value and return ftWideMemo appropriately
function TOracleDBParam.TypeInfoToFieldType(Value : TValue): TFieldType; begin Result := inherited TypeInfoToFieldType(Value); if assigned(Value.TypeInfo) then case Value.TypeInfo.Kind of tkClass, tkArray, tkDynArray, tkInterface: Result := ftOraBlob; tkLString: begin {Hard Oracle VarChar2 Limit of 4000 Bytes, For Ansi String LString, this corresponds to 4000 characters} if Value.ToString.Length > 4000 then Result := ftWideMemo end; tkWString, tkUString: begin {Hard Oracle VarChar2 Limit of 4000 Bytes, for Wide and Unicode Strings this corresponds to 2000 characters} if Value.ToString.Length > 2000 then Result := ftWideMemo end; end; end;
What do you think (wince)
-
reporter Sorry I did not see your post before I posted my simplistic solution. I will take a look at this. Yes I like the idea of a LOB attribute, just as long as we can make it work for the appropriate database, as Oracle has special ftOraBlob and ftOraClob types in the DB unit.
-
reporter Wow yours is so much simpler than mine. I think your proposal will work. I will try it. Although it should not set the limit to the FireDac limit, but to the oracle limit IMO as I believe that TFDParam.Size can be increased until you get to the oracle limit.
-
reporter ALSO another BTW on this is that the LAZY Record type is not being handled in this method. I added this to my solution above.
-
reporter Sorry, didn't work at first, but I did not totally get what you were doing. I see now that you are depending on the column definition, defined by the developer to determine if the type should be a WideMemo. I also had a suggestion from one of our rocket scientist developers, that we could use WideString as the field definition and then in the OracleDBParam do the following
OracleDBParam.TypeInfoToFieldType(Value : TValue): TFieldType; begin Result := inherited TypeInfoToFieldType(Value); if assigned(Value.TypeInfo) then case Value.TypeInfo.Kind of tkClass, tkArray, tkDynArray, tkInterface: Result := ftOraBlob; tkWString: Result := ftWideMemo
This of course will work for later version of XE when String was maped to ftUString and not ftWString.
-
reporter OH BTW way this proposal works. to set the column.length to a value greater than 2000.
-
repo owner - changed milestone to 1.3
- changed version to 1.2 (develop)
-
repo owner - changed status to open
-
repo owner - changed status to resolved
fixed issue
#141→ <<cset 47f28a3bcb24>>
-
repo owner - changed version to 1.2
-
repo owner - changed milestone to 2.0
- Log in to comment