Advanced Data Server: Problem with an '__error' table in .NET-based stored procedures with parameters

Question

Hey guys!

I created .NET-based AEP for ADS v10 beta. Here is a procedure code. .NET side:

public int TestSP(int connectionId, int connection, ref int numRowsAffected)
{
    AdsConnection cnn;
    lock (_connections)
        cnn = _connections[connectionId];

    using (var cmd = cnn.CreateCommand())
    {
        cmd.CommandText = "INSERT INTO __error (errno, message) VALUES (20683, 'This is a test error message')";
        cmd.ExecuteNonQuery();
    }

    return 0;
}

SQL side:

CREATE PROCEDURE TestSP_AEP(num Integer) 
   FUNCTION "TestSP" IN COMLIBRARY "MyNamespace.MyClass";

and

CREATE PROCEDURE TestSP_AEP_Empty() -- !NOTE! without parameters
   FUNCTION "TestSP" IN COMLIBRARY "MyNamespace.MyClass";

With all this stuff, I'm having the following. The call

EXECUTE PROCEDURE TestSP_AEP_Empty();

produces an error:

poQuery: Error 7200: AQE Error: State = HY000; NativeError = 5154; [iAnywhere Solutions][Advantage SQL][ASA] Error 5154: Execution of the stored procedure failed. ProcError 20683: This is a test error message

And the call

EXECUTE PROCEDURE TestSP_AEP (123);

produces an error:

poQuery: Error 7200: AQE Error: State = HY000; NativeError = 5154; [iAnywhere Solutions][Advantage SQL][ASA] Execution of the stored procedure failed.

I'm stuck with it. Please advice.

Thanks, Ivan Eryshov.

Answer 1

I tried this out and verified that you are correct. It is a new problem in v10. It should be fixed in the first service update. The error details from the __error table are lost if there are input parameters to the AEP. It is not clear that there is an immediate workaround for this.

Depending on the work that the procedure is going to do, an alternative approach might be to use scripted stored procedures. If the script is not interacting directly with the OS (e.g., making system calls) then a script might be a good option. They are sometimes simpler to develop. And the SQL debugger can help with that process too.