Method Directive with Execute Custom Code to look at SQL View

Hi!

Our team needs to implement a Pre-processing Method Directive on Erp.BO.ReceiptsFromMfg.GetSelectSerialNumbersParams that look at an sql data view from another sql data base to verify information.
We have been able to confirm that when executing the code, it is reaching the view when we hard code the JobNum and SerialNum, but not select the job num serial num we are transacting on.

How to we fix this code, to do that?

callContextBpmData.Checkbox01 = false;
callContextBpmData.ShortChar01 = "SQL found: ";

string server = "string.server.com";
/*string database = "database"; */
string username = "username";
string password = "password";
string timeout = "30";

var JobNum = "NDW141263";
var SerialNum = "P9-22332";

    string select = string.

Format
("select Test_Status from ERPKineticPilot.dbo.vw_NDI_HUGO_ProductionOperations where Work_Order_ID = '{0}' and Serial_Nbr = '{1}' and Test_ID = '0000000' and Test_Status = 1 ;",
JobNum,
SerialNum
);

    System.Data.SqlClient.SqlConnection SQLCon = new System.Data.SqlClient.
SqlConnection
();
    try
    {
      string connectionString = "Persist Security Info=false;Integrated Security=false; " +
                               /* "database=" + database + ";" + */
                                "User ID=" + username + ";" + 
                                "password=" + password + ";" +
                                "server=" + server + ";" + 
                                "Connect Timeout=" + timeout;
      SQLCon.ConnectionString = connectionString;
      SQLCon.
Open
();

      System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.
SqlCommand
(select, SQLCon);  
      command.CommandTimeout = 90;
      System.Data.SqlClient.SqlDataReader reader = command.
ExecuteReader
();

if
( reader.HasRows )
      {
while
(reader.
Read
())
        {
callContextBpmData
.Checkbox01 =
true
;
callContextBpmData
.ShortChar01 += "Tests Available";
        }
      }
else
      {
callContextBpmData
.Checkbox01 =
false
;
callContextBpmData
.ShortChar01 += "No Tests";
      }
    }catch (Exception ex)
    {
callContextBpmData
.Checkbox01 =
false
;
callContextBpmData
.ShortChar01 += "Nothing Returned";
      throw new Ice.
BLException
("Unable to connect to SQL or execute query\tserver: " + server + 
                                      /*" database: " + database +
                                      */ 
                                      " username: " + username + 
                                      "\tQuery: " + select + ex.Message);  
    }
    finally
    {
if
( SQLCon !=
null
)
      {
        SQLCon.
Close
();
        SQLCon =
null
;
      }
    }

Any help is appreciated.

For the SQL query string there:

"select Test_Status from ERPKineticPilot.dbo.vw_NDI_HUGO_ProductionOperations where Work_Order_ID = '" + JobNum + "' and Serial_Nbr = '" + SerialNum + "' and Test_ID = '0000000' and Test_Status = 1"

Out of curiosity - did you try an External BAQ ?

2 Likes

This would be my recommendation. It’s much easier to have the BPM call a function, and create an Epicor function to call an external BAQ. Easier to maintain, and no need to code in long query strings or credentials that might change.

1 Like

What does your BPM look like?