calling SQL sp from Axapta

[gb]

I have a store procedure that returns s number of records from an external database/table based on a parameter.
In Axapta I need to call that sp and get its returned value.

Does anybody have an example?
Thanks.

[sukhanchik]

usually, stored procedure returns no value. But Axapta has a class \System Documentation\Classes\UserConnection, which provides access to execute SQL operators. Also Axapta has a class \System Documentation\Classes\Statement, which executes SQL operators (see help on theese classes). In this class exists methods executeQuery() for executing queries, which returns any rows and method executeUpdate() for executing queries, which returns no rows.
Try use theese methods and class \System Documentation\Classes\ResultSet for getting count of your records

[Ace of Database]

Example:

PHP код:

static void DD_StoredProcReturnRecordsetTest(Args _args)
{
    str     serverName      = "Server";
    str     baseName        = "Database";
    str     userId          = "UserId";
    str     userPassword    = "Password";
    CCADOConnection         cn;
    COM                     comCN;
    COM                     cmd;
    CCADORecordset          rs;
    int                     i;
    ;

    cn = new CCADOConnection();
    cn.open(
        "Provider=SQLOLEDB;"+
        "Data Source="      + serverName    +   ";" +
        "Initial Catalog="  + baseName      +   ";" +
        "uid="              + userId        +   ";" +
        "pwd="              + userPassword);

    comCN = cn.connection();

    cmd     = new COM("ADODB.Command");
    cmd.activeConnection(comCN);
    cmd.commandType(4);  //adCmdStoredProc, see ObjectBrowser in VBA  
    cmd.CommandText("spTestReturnRecordset");
    rs = new CCADORecordset(cmd.Execute());
    while (!rs.eof())
    {
        info(strfmt("%1", rs.fields().itemIdx(0).value()));
        rs.movenext();
    }

    cn.close();
} 


Sooner I was able to pass parameters to stored procedures in Axapta. But now I forget how to do it

[Ace of Database]

Example: how to pass parameter to stored procedure and get a recordset from it:

PHP код:

static void DD_StoredProcReturnRecordsetTest(Args _args)
{
    str     serverName      = "Server";
    str     baseName        = "Database";
    str     userId          = "UserId";
    str     userPassword    = "Password";
    CCADOConnection         cn;
    COM                     comCN;
    COM                     cmd;
    COM                     param, params;
    COMVariant              emptyParam;
    COMVariant              result;
    CCADORecordset          rs;
    str                     parameter;
    ;

    cn = new CCADOConnection();
    cn.open(
        "Provider=SQLOLEDB;"+
        "Data Source="      + serverName    +   ";" +
        "Initial Catalog="  + baseName      +   ";" +
        "uid="              + userId        +   ";" +
        "pwd="              + userPassword);

    comCN = cn.connection();

    cmd     = new COM("ADODB.Command");

    cmd.activeConnection(comCN);
    cmd.commandType(1);  //adCmdText, see ObjectBrowser in VBA
    parameter = "Parameter value";

    cmd.CommandText(strfmt("exec spTestReturnRecordset '%1'", parameter));

    rs = new CCADORecordset(cmd.Execute());
    while (!rs.eof())
    {
        info(strfmt("%1", rs.fields().itemIdx(0).value()));
        rs.movenext();
    }

    cn.close();
} 


[gb]

Well, actually the question was how to catch a returned value from stored procedure. Say, my return procedure returns an integer:
RETURN @return_value

I don't want to loop through the result set, I just want to get that returned value.

The other question, I usually use ODBCConnection, Statement, and ResultSet to accomplish what you showed in your examples.
Is there an advantage to use one approach vs. another?

Thanks for your input.
Good day.

[sukhanchik]

Цитата:

Say, my return procedure returns an integer:

Your procedure cannot return value (Transact-SQL ?).
Function can return value. If you use a function, I recommend to write:

PHP код:

        cmd.commandType(1);  //adCmdText, see ObjectBrowser in VBA
    parameter = "Parameter value";

    cmd.CommandText(strfmt("SELECT spTestReturnRecordset '%1'", parameter)); 


as wrote Ace of Database, but do replace EXEC on SELECT

[gb]

Thank you all.