How to get the results of a direct SQL call to a stored procedure?

Question

I'm calling a stored procedure via direct SQL from X++, and I can't figure out how to get the integer return value from it. 0 is good, -1 is bad.

// Login to SQL DB
loginProperty = new LoginProperty();
loginProperty.setServer('localhost');
loginProperty.setDatabase('SQL_DB');
odbcConnection = new OdbcConnection(loginProperty);
statement = odbcConnection.createStatement();


/*
@in_customer_id                INT
,@status                        INT
,@dlvmode                        NVARCHAR(25)
,@dlvmodedesc                    NVARCHAR(50)
,@tracking_id                    NVARCHAR(50)
,@note                            NVARCHAR(MAX)
,@modified                        SMALLDATETIME = null
,@override_email                NVARCHAR(200) = null
*/

sqlStatement = strfmt(' EXEC pr_send_status_email ' +
                      ' %1,'        +   // CustomerId
                      ' %2,'        +   // Status
                      ' %3,'        +   // DlvMode
                      ' %4,'        +   // DlvMode description
                      ' %5,'        +   // Tracking #
                      ' %6,'        +   // Note
                      ' %7'             // DateTime
                      , 160308
                      , 2
                      , sqlSystem.sqlLiteral("FD1")
                      , sqlSystem.sqlLiteral("Fed Ex overnight")
                      , sqlSystem.sqlLiteral("1ZABCDEF")
                      , sqlSystem.sqlLiteral("Note Here")
                      , sqlSystem.sqlLiteral(DateTimeUtil::utcNow()));



sqlStatementExecutePermission = new SqlStatementExecutePermission(sqlStatement);
sqlStatementExecutePermission.assert();
//BP deviation documented
resultSet = statement.executeQuery(sqlStatement);
//info(strfmt("%1", statement.executeUpdate(sqlStatement))); // I Tried this too
CodeAccessPermission::revertAssert();

if (resultSet.next()) // Errors here
    info(strfmt("Return: %1", resultSet.getInt(1)));

Hartzel

Are you trying to determine if there are any records? – Chastity

I'm saying if @status NOT IN (3,4,5) return -1 --Quarantine,Received,Invoiced so if a bad status is passed, I can pass error codes back. -2 would be email not found or something. – Hartzel

Answer 1

The executeUpdate returns an updated row count; otherwise 0 for SQL statements that return nothing.

The executeQuery returns an instance of the ResultSet class, but calling a stored procedure is not a select, so you break the contract.

What you are trying to do is not supported.

You may use C# as glue code or use the C# types directly using .NET CLR Interop.

Orelia

Ah. I wonder if I can do a SELECT (EXEC [...]) or some other SQL work around to get the executeQuery to work. – Hartzel