Hi

I am trying to call a stored proc with outputs parameters

It's defined as follows:

CREATE PROCEDURE web_productgetpostage
 @a_sku              VARCHAR(16), 
 @a_postage          NUMERIC(10,2) OUTPUT ,
 @a_no_ph            VARCHAR(10) OUTPUT
...

I can manually run the query using by Db tool as follows and all is good:

DECLARE @l_postage numeric(10,2), @l_no_ph VARCHAR(10)
EXEC web_productgetpostage  '664732000000', @l_postage OUTPUT , @l_no_ph OUTPUT 
select @l_postage, @l_no_ph

Within C# I'm using the following code, where the adapter is an existing DataAccessAdapter already connected to the db:

decimal oPostage = 0;
string oNoPostage = String.Empty;
ActionProcedures.GetPostage(thisSkn.SkuBase, ref oPostage, ref oNoPostage, adapter);

During the ExecuteNonQuery() method I get the message: Precision error during implicit conversion of NUMERIC value '9.90' to a NUMERIC field

I get similar within my db tool if I don't specify the precision i.e. just say "@l_postage numeric" -> "Scale error during implicit conversion of NUMERIC value '9.90' to a NUMERIC field."

Am I missing something obvious?

Thanks, James

Running LLBLGen 2.6 on XP, using Sybase ASE 12.* db.

Which runtime library version (build number) are you using?

Hi Walaa,

Walaa wrote:

Which runtime library version (build number) are you using?

The runtime is 2.6.10.0225 The buildno is 2.6 Final, released October 9, 2009

Is your issue related to this one (please check the entire thread): http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=13429

Hi Walaa

That thread is similar. However I can get it to work by manually coding the parameter precision/scale as per below, so I don't think the driver is the issue. The actionProceduresAdapter.template looked fine to me also. Is it possible to confirm that when the stored procs parameters get analysed by LLBLGen that it includes the scale + precision settings e.g. NUMERIC(10,2) for the relevant Output AseParameters?

using (OrYxConnection dbConnection = OrYxConnection.CreateConnection(_Config.GetDefaultDbConfig())) {
    try {
        dbConnection.Open();
        using (OrYxCommand myCommand = new OrYxCommand("web_productgetpostage", dbConnection, CommandType.StoredProcedure)) {
            myCommand.AddSPParameter("@a_sku", thisSkn.SkuBase);

            IDbDataParameter myParameter = myCommand.CreateParameter();
            myParameter.ParameterName = "@a_postage";
            myParameter.DbType = DbType.Decimal;
            myParameter.Value = 0;
            myParameter.Scale = 10;
            myParameter.Precision = 12;
            myParameter.Direction = ParameterDirection.Output;
            myCommand.Parameters.Add(myParameter);

            myCommand.AddSPOutputParameter("@a_no_ph", DbType.String);

            myCommand.ExecuteNonQuery();
            webProduct.Postage = Convert.ToDecimal(myParameter.Value);
        }

    } catch (Exception ex) {
        ExHandler.Get().HandleException("OOpsy", ex);
    }

    if (dbConnection != null) {
        dbConnection.Close();
    }
}

It's the same issue as the thread linked: in the past the ADO.NET provider produced server errors when precision/scale was set for numeric parameters, so in RetrievalProcedures we added a method to produce parameters similar to how we do it in the runtime lib. As you can see, no precision/scale is set (as that should be retrieved from the value set, which does have a fraction). Anyway, this gives a problem in your query as well.

I'll see what we can do about it, as this same problem should be present when saving an entity with a decimal/numeric field and a scale.

(edit) Decimal and Numeric fields are saved properly with the parameters without precision/scale:

[Test]
public void DecimalFractionTest()
{
    TestTabEntity t = new TestTabEntity();
    t.NumericField = 13.133M;
    t.DecimalField = 13.13M;
    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        Assert.IsTrue(adapter.SaveEntity(t, true));
        Assert.IsTrue(t.Id > 0);
        TestTabEntity x = adapter.FetchNewEntity<TestTabEntity>(new RelationPredicateBucket(TestTabFields.Id == t.Id));
        Assert.AreEqual(EntityState.Fetched, x.Fields.State);
        Assert.AreEqual(13.133M, x.NumericField);
        Assert.AreEqual(13.13M, x.DecimalField);
    }
}

So it's not that parameters are the problem.

It's now a matter of whether ASE wants precision/scale to be set when it's a parameter of a proc. Will now test that too.

(edit) I can't reproduce it. Proc:

CREATE PROCEDURE dbo.pr_GetTestTabRows
    @numFieldValue Numeric(15, 3)
AS
BEGIN
SELECT TestTab.ID, TestTab.Name, TestTab.BlobField, TestTab.FloatRealField, TestTab.FloatDoubleField, TestTab.FloatField, TestTab.RealField, TestTab.MoneyField, TestTab.DateTimeField, TestTab.DecimalField, TestTab.DateField, TestTab.NCharField, TestTab.NVarCharField, TestTab.UniVarChar, TestTab.NumericField FROM TestTab WHERE NumericField = @numFieldValue
END

Test:

[Test]
public void NumericProcParameterCallTest()
{
    DataTable results = RetrievalProcedures.GetTestTabRows(13.133M);
    Assert.IsTrue(results.Rows.Count > 0);
}

runs fine without a problem. However this is on sybase ASE 15. It might be this error is specific for ASE 12.

In that case, please alter the RetrievalProcedures template for ASE and add a couple of lines to the CreateAseParameter method at the bottom to set the precision/scale in the switch/case.