0

I'm doing some maintenance on a legacy app uses OracleConnection and OracleCommand to manage our data. I'm Having an issue where a specific update isn't working when I use parameters, but if I convert the same statement to an interpolated string, it's working fine. I'm not getting any exceptions, the update just doesn't happen, and returns a 0 for rows updated. I'm doing other updates with parameters, so I'm curious if anyone sees anything that I might have missed with this one. I've tried with/without the transaction as well as explicitly creating OracleParameter objects to no effect.

The method is below. I've left the parameterized version and the parameter setting commented out for reference.

    public int UpdateBusinessEntitlement(int appId, int businessId, int entitlementTypeId, string sso)
    {

        // Non-Working Parameterized Version
        //var sql = "UPDATE APD.APD_BUS_TO_APP_MAP " +
        //                   "SET ENTITLEMENT_TYPE_SEQ_ID = :entitlementTypeId, " +
        //                   "LAST_UPDATE_DATE = SYSDATE, " +
        //                   "LAST_UPDATED_BY = :lastUpdatedBy " +
        //                   "WHERE APP_SEQ_ID = :appId AND BUSINESS_SEQ_ID = :businessId";

        var sql = "UPDATE APD.APD_BUS_TO_APP_MAP " +
               $"SET ENTITLEMENT_TYPE_SEQ_ID = {entitlementTypeId}, " +
               "LAST_UPDATE_DATE = SYSDATE, " +
               $"LAST_UPDATED_BY = {sso} " +
               $"WHERE APP_SEQ_ID = {appId} AND BUSINESS_SEQ_ID = {businessId}";


        using (var cn = _connectionBuilder.GetUpdaterConnection())
        {
            using (var cmd = _connectionBuilder.GetCommand(sql, cn))
            {
                cn.Open();
                var transaction = cn.BeginTransaction(IsolationLevel.ReadCommitted);
                cmd.Transaction = transaction;

                //cmd.Parameters.Add("appId", appId);
                //cmd.Parameters.Add("businessId", businessId);
                //cmd.Parameters.Add("entitlementTypeId", entitlementTypeId);
                //cmd.Parameters.Add("lastUpdatedBy", sso);


                var rows = cmd.ExecuteNonQuery();
                transaction.Commit();

                return rows;
            }
        }
    }
Mike Whitis
  • 126
  • 2
  • 8

2 Answers2

2

I suspect you are binding parameters by position rather than name.

By position, you'd be putting appId first into entitlement_type_seq_id. Then BusinessId into last_Updated_By, entitlementTypeId into app_seq_id and lastUpdatedBy into business_seq_id.

https://docs.oracle.com/cd/B19306_01/win.102/b14307/OracleCommandClass.htm#i997666

Gary Myers
  • 34,963
  • 3
  • 49
  • 74
  • Thank you! This fixed my problem. I didn't realize that it was binding by position by default. I'll appreciate the link to the docs. – Mike Whitis Apr 15 '16 at 12:21
1

Either you have to set

cmd.BindByName = true;

because default value for BindByName property is false, which means the parameters are bound by position.

Or you have to use the same order of parameters as they appear in your statement, i.e.

cmd.Parameters.Add("entitlementTypeId", entitlementTypeId);
cmd.Parameters.Add("lastUpdatedBy", sso);
cmd.Parameters.Add("appId", appId);
cmd.Parameters.Add("businessId", businessId);

btw, usually OracleParameter are added like this:

cmd.Parameters.Add("entitlementTypeId", OracleDbType.Int32, ParameterDirection.Input).Value = entitlementTypeId;
cmd.Parameters.Add("lastUpdatedBy", OracleDbType.Varchar2, ParameterDirection.Input).Value = entitlementTypeId;
cmd.Parameters.Add("appId", OracleDbType.Int32, ParameterDirection.Input).Value = appId;
cmd.Parameters.Add("businessId", OracleDbType.Int32, ParameterDirection.Input).Value = businessId;

I assume for simple data types like numbers of string the syntax does not matter, however other data type (e.g. Date) may fail if you simply use cmd.Parameters.Add(string name, object val).

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • Thank you! This fixed my problem. I didn't realize that it was binding by position by default. I'll read through the docs that Gary posted, and change my parameters to the new format. – Mike Whitis Apr 15 '16 at 12:20