Sybase ASE - Data overflow

Posts   
 
    
kerpal
User
Posts: 14
Joined: 16-May-2008
# Posted on: 26-May-2008 08:18:13   

I am getting data overflow exceptions even though the column is more than big enough for the value.

I have the following table created in Sybase ASE Developer Edition:

create table resource ( id numeric(38,0) identity , resource_type varchar(255) not null , version numeric(19,0) not null , amount numeric(38,22) null , section_question_id numeric(19,0) null , resource_profile_id numeric(19,0) null , self_assessment_id numeric(19,0) null , unit_id numeric(19,0) null , unit_name varchar(40) null , energy numeric(38,22) null , indicative_c02 numeric(38,22) null , energy_factor numeric(21,11) null , indicative_c02_factor numeric(21,11) null , other_section_id numeric(19,0) null , other_energy_source varchar(100) null , custom_q_order int null
)

I have an EntityCollection populated, have modified the contents of the "amount" field and am trying to save using this code:

       EntityCollection<ResourceEntity> resources = GetResources();

        try
        {
            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                adapter.SaveEntityCollection(resources, false, false);
            }
        }
        catch (Exception e) 
        {}

I get this exception caught by the above catch block:

SD.LLBLGen.Pro.ORMSupportClasses.ORMException = "An exception was caught during the execution of an action query: Data overflow. Increase specified column size or buffer size. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception."

The following output is generated:

Method Enter: CreateUpdateDQ(4) Method Enter: CreateSingleTargetUpdateDQ(4) Generated Sql query: Query: UPDATE devdb.dbo.resource SET amount=@Amount WHERE ( devdb.dbo.erep_resource.id = @Id1) Parameter: @Amount : Decimal. Length: 38. Precision: 38. Scale: 22. Direction: Input. Value: 1. Parameter: @Id1 : VarNumeric. Length: 38. Precision: 38. Scale: 0. Direction: Input. Value: 1299.

Method Exit: CreateSingleTargetUpdateDQ(4) Method Exit: CreateUpdateDQ(4) Method Enter: DataAccessAdapterBase.ExecuteActionQuery Method Enter: DataAccessAdapterBase.OpenConnection Method Exit: DataAccessAdapterBase.OpenConnection A first chance exception of type 'Sybase.Data.AseClient.AseException' occurred in Sybase.Data.AseClient.dll A first chance exception of type 'SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException' occurred in SD.LLBLGen.Pro.ORMSupportClasses.DLL Method Exit: DataAccessAdapterBase.ExecuteActionQuery A first chance exception of type 'SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException' occurred in SD.LLBLGen.Pro.ORMSupportClasses.DLL Method Enter: DataAccessAdapterBase.Rollback Method Exit: DataAccessAdapterBase.Rollback A first chance exception of type 'SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException' occurred in SD.LLBLGen.Pro.ORMSupportClasses.DLL Method Enter: DataAccessAdapterBase.CloseConnection Method Exit: DataAccessAdapterBase.CloseConnection Method Exit: DataAccessAdapterBase.SaveEntityCollection(3)

Any help greatly appreciated!

Walaa avatar
Walaa
Support Team
Posts: 14643
Joined: 21-Aug-2005
# Posted on: 26-May-2008 08:41:59   

Which LLBLGen Pro runtime library version are you using?

Anyway I suspect this to be a Sybase driver issue. Would you please try to execute the following code using ADO.NET (no LLBLGen)?

kerpal
User
Posts: 14
Joined: 16-May-2008
# Posted on: 26-May-2008 09:15:44   

I am using LLBLGen Pro v2.5 Final (April 23rd, 2008 ).

What code do you want me to execute?

goose avatar
goose
User
Posts: 392
Joined: 06-Aug-2007
# Posted on: 26-May-2008 19:25:57   

Try refreshing your catalog, then regenerating to an empty directory and manually copy-paste the code to your project.

Would you please try to execute the following code using ADO.NET (no LLBLGen)?

Walla means the update using non-lblgen code.

Query: UPDATE devdb.dbo.resource SET amount=@Amount WHERE ( devdb.dbo.erep_resource.id = @Id1)
    Parameter: @Amount : Decimal. Length: 38. Precision: 38. Scale: 22. Direction: Input. Value: 1.
    Parameter: @Id1 : VarNumeric. Length: 38. Precision: 38. Scale: 0. Direction: Input. Value: 1299.
kerpal
User
Posts: 14
Joined: 16-May-2008
# Posted on: 27-May-2008 01:56:38   

The update doesn't work if I manually create the AseDataAdapter, so it looks like yet another example of poor quality Sybase code! rage rage rage rage

Any ideas where I can get an older version of Sybase.Data.AseClient?

kerpal
User
Posts: 14
Joined: 16-May-2008
# Posted on: 27-May-2008 02:34:58   

It seems to be a bug in AseParameter.

If I specify the precision and scale, I get the overflow error.

This code doesn't work:

da.UpdateCommand.Parameters.Add( new Sybase.Data.AseClient.AseParameter("@Param2", Sybase.Data.AseClient.AseDbType.Numeric, 38, false, 38, 6));

However this code does:

da.UpdateCommand.Parameters.Add( new Sybase.Data.AseClient.AseParameter("@Param2", Sybase.Data.AseClient.AseDbType.Numeric, 38 ));

Is it possible to patch LLBL Gen to output the second style of parameter? Where's the best place to change this?

Walaa avatar
Walaa
Support Team
Posts: 14643
Joined: 21-Aug-2005
# Posted on: 27-May-2008 08:39:15   

Is it possible to patch LLBL Gen to output the second style of parameter? Where's the best place to change this?

I think it's possible:

1- Goto the Source code of the SybaseAseDQE. 2- Modify the CreateParamter method in SybaseAseSpecificCreator.cs 3- Build your own version of the DQE, and use it instead of the original one.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 38110
Joined: 17-Aug-2003
# Posted on: 27-May-2008 10:13:23   

The parameter creation statement used in the DQE is:


toReturn = new AseParameter(CreateParameterName(field.Alias), (AseDbType)persistenceInfo.SourceColumnDbType, persistenceInfo.SourceColumnMaxLength,
            direction, persistenceInfo.SourceColumnIsNullable, persistenceInfo.SourceColumnPrecision, persistenceInfo.SourceColumnScale,
            "", DataRowVersion.Current, value);

It's not entirely sure it's Sybase, although their code isn't always the most bugfree-est code on the planet.

If they take into account the maxlength for a numeric field (it should be ignored) even though precision and scale is set, it goes wrong. MaxLength is set with the value from the 'Length' column in the designer.

erl
User
Posts: 38
Joined: 28-Feb-2008
# Posted on: 29-May-2008 20:10:58   

Hi all, Did you plan to fix this issue in the next release?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 38110
Joined: 17-Aug-2003
# Posted on: 29-May-2008 20:40:39   

erl wrote:

Hi all, Did you plan to fix this issue in the next release?

We've to look into IF it's fixable on our part, i.e. what kind of workaround we can implement which always works. The user hasn't reported back yet, so we'll do some tests of our own. However, in the end: it's a bug in sybase's code. So even if we fix it, it has to be reported to sybase, no matter what, as otherwise they'll never fix their code.

kerpal
User
Posts: 14
Joined: 16-May-2008
# Posted on: 30-May-2008 02:21:06   

I am using the following code:

AseParameter parameter = new AseParameter(CreateParameterName(field.Alias), (AseDbType)persistenceInfo.SourceColumnDbType, persistenceInfo.SourceColumnMaxLength); parameter.Direction = direction; parameter.IsNullable = persistenceInfo.SourceColumnIsNullable; parameter.SourceVersion = DataRowVersion.Current; parameter.Value = value;

toReturn = parameter;

Seems like a dirty hack, but at least it means development can continue.

I need to create some additional unit tests to see if there are any adverse side-effects.

erl
User
Posts: 38
Joined: 28-Feb-2008
# Posted on: 30-May-2008 10:32:05   

I think it's possible:

1- Goto the Source code of the SybaseAseDQE. 2- Modify the CreateParamter method in SybaseAseSpecificCreator.cs 3- Build your own version of the DQE, and use it instead of the original one.

I guess I need the SDK to do this? Could you send us this fix please we are facing the same problem here? (We are ordering now the LLBgen the decision has already been taken, Now I'm confused )

Otis avatar
Otis
LLBLGen Pro Team
Posts: 38110
Joined: 17-Aug-2003
# Posted on: 30-May-2008 10:49:40   

erl wrote:

I think it's possible:

1- Goto the Source code of the SybaseAseDQE. 2- Modify the CreateParamter method in SybaseAseSpecificCreator.cs 3- Build your own version of the DQE, and use it instead of the original one.

I guess I need the SDK to do this? Could you send us this fix please we are facing the same problem here? (We are ordering now the LLBgen the decision has already been taken, Now I'm confused )

If there's a workaround possible so we add a fix to our DQE (query engine) for Sybase, we'll do it right away, so all of you will benefit.

We'll do some testing this morning to see what the best workaround is for this issue.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 38110
Joined: 17-Aug-2003
# Posted on: 30-May-2008 11:34:38   

I could reproduce the behavior with:


[Test]
public void UpdateNumericTestForParameterCreation()
{
    EntityCollection<DecTestEntity> decs = new EntityCollection<DecTestEntity>();
    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        adapter.FetchEntityCollection(decs, null);
        Assert.AreEqual(1, decs.Count);

        // set amount in all to be 1 higher
        foreach(DecTestEntity d in decs)
        {
            if(d.Amount == null)
            {
                d.Amount = 0.0M;
            }
            else
            {
                d.Amount += 1.0M;
            }
        }

        int count= adapter.SaveEntityCollection(decs);
        Assert.AreEqual(1, count);
    }
}

DecTest is a table with 1 field, and this layout:

create table DecTest (
    ID                            numeric(6,0)                   not null  ,
    Name                            varchar(50)                   not null  ,
    Amount                        numeric(38,22)                       null   
)

Looking into how to get this fixed. It's an error which is returned from Sybase ASE server btw, not the ADO.NET provider. It can be the ADO.NET provider sends wrong data to the server (likely).

(edit) when I update the DQE to avoid setting the precision/scale/length for decimal/numeric/double/real/ etc. (so the fields with a fraction), and check the query the properties for precision/scale/length aren't set at all in the ADO.NET provider:


Generated Sql query: 
    Query: UPDATE TestDB.dbo.DecTest SET Amount=@Amount WHERE ( TestDB.dbo.DecTest.ID = @Id1)
    Parameter: @Amount : VarNumeric. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 3,0.
    Parameter: @Id1 : VarNumeric. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.

This is a bug in the ADO.NET provider, it has to set these properties through the value set.

Anyway, it seems to fix it. I'll attach a new build of the DQE in a few minutes.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 38110
Joined: 17-Aug-2003
# Posted on: 30-May-2008 11:54:18   

Attached is the updated Sybase DQE which has a workaround for parameter creation for parameters of type: Decimal, Numeric, Double, Real, SmallMoney, Money

Attachments
Filename File size Added on Approval
SD.LLBLGen.Pro.DQE.SybaseAse.NET20.dll 36,864 30-May-2008 11:54.29 Approved
erl
User
Posts: 38
Joined: 28-Feb-2008
# Posted on: 30-May-2008 13:16:27   

thx I give it a try

erl
User
Posts: 38
Joined: 28-Feb-2008
# Posted on: 04-Jun-2008 11:50:37   

Modifying the (action/ retrieval) ProceduresAdapter.template files in order to generate the simplified constructor like Kerpal

AseParameter parameter = new AseParameter(CreateParameterName(field.Alias), (AseDbType)persistenceInfo.SourceColumnDbType, persistenceInfo.SourceColumnMaxLength); parameter.Direction = direction; parameter.IsNullable = persistenceInfo.SourceColumnIsNullable; parameter.SourceVersion = DataRowVersion.Current; parameter.Value = value;

, it works. Without this it doesn’t! I'm looking forward the next official release that integrates all patches you have made the last few weeks (especially for Sybase).

Otis avatar
Otis
LLBLGen Pro Team
Posts: 38110
Joined: 17-Aug-2003
# Posted on: 04-Jun-2008 12:28:41   

I indeed overlooked the templates, my bad! I changed the runtime lib only.

I'll change the templates as well.

(edit) btw, has anyone of you reported this to sybase? It's one thing to write workarounds for their bugs, but they still have to fix it

(edit) I changed the templates and attached the .net 2.0/C# ones. See the updated templates for C#, .NET 2.0 / Sybase ASE. (place in Templates\SybaseAseSpecific\Net2.x\C# )

Attachments
Filename File size Added on Approval
SybaseAseNet20CS_ProcTemplates_06042008.zip 7,334 04-Jun-2008 14:06.44 Approved