I have a webcore app with xdata server. The entities are created using Aurelius . I am using MSSQL. Everything seems to be working fine except for one field which is set to nvarchar(1000) . The data is being accessed using TXdataWebDataset using a TDbmemo
On saving when the size of the value is more than 255 I am getting the following error
{
"error": {
"code": "FDException",
"message": "[FireDAC][Phys][ODBC]-345. Data too large for variable [P1]. Max len = [255], actual len = [348] Hint: set the TFDParam.Size to a greater value"
}
}
How is the mapping of such class field/property in entity class?
The entity class is fine.
[Entity]
[Table('Trans_Header')]
[Id('FCustomer_id', TIdGenerator.None)]
[Id('FWeek', TIdGenerator.None)]
TTrans_Header = class
private
[Column('Customer_id', [TColumnProp.Required], 5)]
FCustomer_id: string;
[Column('Week', [TColumnProp.Required], 5)]
FWeek: string;
[Column('SampleDate', [TColumnProp.Required])]
FSampleDate: TDateTime;
[Column('Result_Date', [])]
FResult_Date: Nullable<TDateTime>;
[Column('Recommendations', [], 1000)]
FRecommendations: Nullable<string>;
[Column('Solvent_AWR', [], 10)]
FSolvent_AWR: Nullable<string>;
[Column('Biocide_AWR', [], 10)]
FBiocide_AWR: Nullable<string>;
[Column('Element_AWR', [], 10)]
FElement_AWR: Nullable<string>;
[Column('Basic_AWR', [], 10)]
FBasic_AWR: Nullable<string>;
[Column('Panel_AWR', [], 10)]
FPanel_AWR: Nullable<string>;
[Column('Locked', [], 1)]
FLocked: Nullable<string>;
[Column('CreatedBy', [], 10)]
FCreatedBy: Nullable<string>;
[Column('CreatedDate', [])]
FCreatedDate: Nullable<TDateTime>;
[Column('ModifiedBy', [], 10)]
FModifiedBy: Nullable<string>;
[Column('ModifiedDate', [])]
FModifiedDate: Nullable<TDateTime>;Currently I have created a service call to update the field and it is working fine. But I really do not know why this issue is proping up. The Parameter size is not being set any where. I checked the Aurlieus code also and really could not figure it out. The size is being set only in the case of Oracle database I think
This is hard to tell. Usually the problem is "simple", you are setting a value larger than the field can hold. Are you sure the field data type in the database is ok?
Do you have a sample project reproducing the issue?
I absolutely agree with you that the problem should be simple. The value is not larger as I told you the error pops up after 255 char while the field size is 1000 char.
I tried creating a separate program only with this table and it works fine without any issue. So tried to debugging Aurelius but I was not able to figure out. The project has gone live and so had to do a workaround by creating a function in the interface and calling it with the values which worked.
Actually Aurelius generates a code "Update Recommendations=:p1 where Customer_id=:p2 and week =:p3" and then applies the parameters. I did not see it assigning the size anywhere but still the error indicates the Parameter size is being applied at some point .
It's probably FireDac that sets the parameter size internally based on the length of parameter string value.
I'm getting a similar error, trying to figure out where TFDQuery is used in Aurelius and if I can pass in a bigger value. In my case the field size is 2048 and the value is ~1500, but the error refers to 1000 limit.
I think it's set in function TFireDacRttiStatementAdapter.ExecuteBatch.
Where would it be a good place to change that size?
I can see the size set in TFireDacStatementAdapter.ExecuteBatch, but only for 2 types (mine, varchar2, was excluded anyway), but in TFireDacStatementAdapter.Execute it's not set at all, so it defaults to 0 and then somewhere inside FireDac, it must default to 1000, so even though my field def is 2048, it fails for data > 1000 chars.
I could make it work by setting TFDParam(FADQuery.Params[#]).Size to 2048 in the Debugger in TFireDacStatementAdapter.Execute before it executes the statement.
Can you fix it?
Thing is this looks like a FireDAC issue?
Why is it setting a max value of 1000, it doesn't make sense. Maybe there is some FireDAC configuration that controls this?
Which database are you using?
Maybe this can help?
Otherwise, as I said, we need a small project reproducing the issue.
Oracle. TFDQuery is not exposed through the classes at Aurelius level, so no way to set directly. Only connection, and I can't see any way to set it there. If you set it by field right where I did, it should work.
Ok, I'll try that and get back...
It does not seem to help, see attached.
This particular field is defined in the class as follows:
[Column('N', [], 2048)]
FN: string;
It creates it in the table as:
N VARCHAR2(2048)
So, I guess, it's an ftAnsiString type.
Aurelius adds Params dynamically and it's not setting Size. So while yes, this 1000 default defies logic, maybe it should be set explicitly by Aurelius, once the field sizes are known?
Try to set other types as dtString or dtUnicodeString (I don't know exactly what's available for FireDAC).
In any case, to prevent guessing exercises, please provide a project reproducing the issue.
I've emailed it to you...
Haven't received it. You can send a private message through this Support Center.
Received the project in private. Oracle is tricky with its types, and mixing with FireDAC, gets trickier.
Thing is you are mapping a string Delphi field to a database field with size 2048. It creates it as VARCHAR but FireDAC mixes it up probably because the types in Delphi are Unicode.
If you try to map the strings to NVARCHAR2 (setting TGlobalConfigs.GetInstance.MapStringToNational char to true, as explained here), Oracle doesn't accept such data type (at least the version I'm using).
I'd suggest you simply map such field to NCLOB, this way:
[Column('N', [], 65536)]
FN: string;
Then it should work fine.
Ok, this avoids the error. But it's not very nice in terms of using SQL, as it automatically truncates the output:
select n from testtbl;
N
jybzvndzvqmhsjaatgyoupbwmvfcumqzeolnaivactmcdubanwhgktnqtvzzesikndskrbuzthmncgdu
So I would prefer to use varchar2(2048). It outputs the complete value then. And as I have carriage returns in it, it's output with some formatting as well.
As I mentioned, setting the Param size explicitly in that function in Aurelius that cannot recall right now did prove to be a solution. So, can you either bubble that setting up so it can be set programmatically by the caller per field, or just set it automatically in your code if the field is Oracle/varchar2()?
I'm not sure how this relates to Aurelius? "Who" truncates the output?
This seems to be a tooling issue, not database.
Note that you can build your own drivers with Aurelius. For example, you can simply copy/paste the unit Aurelius.Drivers.FireDAC, and modify it as you wish (explicitly setting size of parameters in some situations). Then just tell Aurelius to use your drivers instead of the original one.