Frans,

I think the issue is specific to DateTime. In my code, I create a new variable like this:

DateTime currentDateTime = new DateTime();

Then, I call the

ActionProcedure.GetServerTime(ref currentDateTime, adapter);

When you create a new instance of DateTime, it has a value. Because the SP parameters are set as IN/OUT, it was trying to push the default DATETIME value into the SP which only expected an OUT parameter (not to receive any data). When the parameter is created in the LLBLGen generated code, it is creating it with the value that is passed in by ref in the above call and then when you call ExecuteNonQuery, it was trying to push that value into the stored proc.

I can reproduce the same behavior outside of LLBLGen in ADO.NET code. The error is cryptic, but is being caused by the assumption that the parameters are all IN/OUT and the fact that the parameters are created with a value:

parameters[0] = new SAParameter("@CurrentDateTime", SADbType.TimeStamp, 8, ParameterDirection.InputOutput, true, 0, 0, "",  DataRowVersion.Current, currentDateTime);

I can actually "fix" the code above by specifying "ParameterDirection.Output" and it runs, but for now, I just changed my SP to accept the parameter and ignore it.

Allen

I tested again with the new issue's information. I can reproduce it now. This is what I got:

LLBLGen RTL 2.6.09.0616 (the latest)

Sybase ASA version 11.0.1

iAnywhere Data Provider for .Net 11.0.1.20442

Stored Procedure

ALTER PROCEDURE "DBA"."ShowProductQuantity"(IN product_ID integer, OUT _quantity integer)
BEGIN
  SELECT Quantity
    INTO _quantity
    FROM GROUPO.Products   
    WHERE Products.ID=product_ID
END

Code

[TestMethod]
public void TestRetrievalProcedureWithOUTParam()
{
    // the products to retrieve
    int productId = 300;

    // call the procedure
    int quantityOut = int.MinValue;
    ActionProcedures.ShowProductQuantity(productId, ref quantityOut);

    // test result
    Assert.AreEqual(28, quantityOut);
}

Exception Message

Test method TestProject.SybaseTests.TestRetrievalProcedureWithOUTParam threw exception: iAnywhere.Data.SQLAnywhere.SAException: Communication error.

Exception Stack Trace

iAnywhere.Data.SQLAnywhere.SACommand.ExecuteNonQuery() Demo.DatabaseSpecific.DataAccessAdapter.CallActionStoredProcedure(String storedProcedureToCall, SAParameter[] parameters) in C:\Documents and Settings\David\My Documents\Dev\LLBLGenPro\v2.6\Adapter\Sybase Demo\code\DAL\DatabaseSpecific\DataAccessAdapter.cs: line 171

Demo.DatabaseSpecific.ActionProcedures.ShowProductQuantity(Int32 productId, Int32& quantity, DataAccessAdapter adapter) in C:\Documents and Settings\David\My Documents\Dev\LLBLGenPro\v2.6\Adapter\Sybase Demo\code\DAL\DatabaseSpecific\ActionProcedures.cs: line 63

Demo.DatabaseSpecific.ActionProcedures.ShowProductQuantity(Int32 productId, Int32& quantity) in C:\Documents and Settings\David\My Documents\Dev\LLBLGenPro\v2.6\Adapter\Sybase Demo\code\DAL\DatabaseSpecific\ActionProcedures.cs: line 45 TestProject.SybaseTests.TestRetrievalProcedureWithOUTParam() in C:\Documents and Settings\David\My Documents\Dev\LLBLGenPro\v2.6\Adapter\Sybase Demo\code\TestProject\SybaseTests.cs: line 184

No SQL is traced at this point.

Workaround 1 Changing the SP to this, make the test pass:

ALTER PROCEDURE "DBA"."ShowProductQuantity"(IN product_ID integer, INOUT _quantity integer)
...
END

Workaround 2 Chance the direction directly on the SP method call, make the test pass as well:

public static int ShowProductQuantity(System.Int32 productId, ref System.Int32 quantity, DataAccessAdapter adapter)
{
    ...
    parameters[1] = new SAParameter("_quantity", SADbType.Integer, 4, ParameterDirection.Output, true, 10, 0, "",  DataRowVersion.Current, quantity);
    ...
}

(Edit) Workaround 3 Changing this line on the template seems to correct the problem: (<LLBLGen6InstallationFolder>\Templates\SybaseAsaSpecific\Net2.x\C#\actionProceduresAdapter.template:63)

<[Foreach OutputParameter CrLf]>            parameters[<[ParameterIndex]>] = new SAParameter("<[ActualParameterName]>", SADbType.<[TypeOfActualParameter]>, <[ParameterSize]>, ParameterDirection.<[CurrentParameterDirection]>, true, <[ParameterPrecision]>, <[ParameterScale]>, "",  DataRowVersion.Current, <[CaseCamel CurrentParameterName]>);<[NextForeach]>

All my SP parameter tests (IN, OUT, INOUT) work with this little change.

Please see the attached archive for the fix for this. Re-generate the code with these templates. I'll leave it to you if you want to report this to Sybase or not, as we don't have a support contract with Sybase. These fixes are for iAnywhere 11 only, iAnywhere 10 works fine with the originals.

Sorry for the inconvenience we accidentally caused.

Thanks everyone for all of your help on this.

Frans - I realized that I neglected to answer your question about the return value from the SP and mine seems to be bringing back the return value as expected - good news.

Thanks,

Allen

asowles wrote:

Thanks everyone for all of your help on this.

Frans - I realized that I neglected to answer your question about the return value from the SP and mine seems to be bringing back the return value as expected - good news.

Thanks,

Allen

Ok, then I'll flag that as a problem with v10's provider. I consider this issue fixed now.