0

Once in a while I see in the logs, this exception:

12:27:38,504 [8] DEBUG - Inside GetRoles USER1
    12:27:38,537 [8] ERROR - Error in method, "GetRoles" . User account that invoked error-  USER1 
    System.Data.OleDb.OleDbException (0x80004005): SQL0401: Comparison operator = operands not compatible.
    Cause . . . . . :   The operands of comparison operator = are not compatible. -- Numeric operands are compatible with any other numeric operands and with character and graphic operands. -- Character operands are compatible with operands that are character, graphic, date, time, timestamp, or numeric. -- Date, time, and timestamp operands are compatible with character and graphic operands or with another operand of the same type. -- Graphic operands are compatible with graphic, character, date, time, timestamp, or numeric operands. -- Binary operands are compatible only with binary operands. -- Operands that are user-defined types can only be compared to operands that are the same exact type. -- DataLink and XML operands cannot be compared. Recovery  . . . :   Check the data types of all operands to see if the data types are compatible.  If all the operands of the SQL statement are correct and a view is being accessed, then check the data types of all the operands in the view definition. Correct the errors.  Try the request again.
       at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
       at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForMultpleResults(tagDBPARAMS dbParams, Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
       at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
       at Dashboard.DataAccessLayer.DAL.GetRoles() in DAL.cs:line 98

And here is my code:

public List<string> GetRoles()
        {
            List<string> tempList = new List<string>();
            OleDbConnection connection;
            OleDbCommand command;
            string sql = null;

            sql = "SELECT ROLE FROM " + LibraryList.PROJ + ".ROLE where UPPER(USERID) = UPPER(?)";
            using (connection = new OleDbConnection(_connectionString))
            {
                using (command = new OleDbCommand())
                {
                    try
                    {
                        connection.Open();
                        command.Connection = connection;
                        command.Parameters.Add("@userID", OleDbType.Char, 10).Value = CurrentUser.getUserID();
                        log.Debug("Inside GetRoles " + CurrentUser.getUserID()); 
                        command.CommandText = sql;

                        using (OleDbDataReader reader = command.ExecuteReader())
                        {

                            while (reader.Read())
                            {
                                tempList.Add(reader.GetString(0));
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        log.Error("Error in method, \"GetRoles\" . User account that invoked error- " + CurrentUser.getCurrentUserDisplayInfoForLogging(), ex);
                    }
                }
            }
            return tempList;
        }

I print out the value and it is a string, so it's not null or empty or anything. This happens very infrequently so it's hard to pin point what is happening when it seems the value is the same as any other time but sometimes it doesn't like it.

What issue could be causing this?

Amit Joshi
  • 15,448
  • 21
  • 77
  • 141
eaglei22
  • 2,589
  • 1
  • 38
  • 53
  • As you already pointed out (kind of) it is the value. That is the only logical explanation. It is just a mater of finding which record(s) have a bad/unexpected value. Also it might not be a bad idea to do `Value = CurrentUser.getUserID().ToUpper();` instead of doing it inside the query, I am assuming it is a string that is being returned. If its a number then that could cause an issue because you are trying to treat it as a string in your query, in that case make it `Value = CurrentUser.getUserID().ToString().ToUpper();` – Igor Mar 15 '17 at 18:50
  • If you want more help could you post the schema for the table `Roles`. – Igor Mar 15 '17 at 18:53
  • Could it be that in this case the current user is null, because the method CurrentUser.getCurrentUserDisplayInfoForLogging() does not return a value as you can see in the first line of your posted log entry. This would also mean that GetUserId is not working properly. Just an idea... – MUG4N Mar 15 '17 at 18:53
  • @Igor Well what is weird is the same unique value of the same row will get compared plenty of times with no issues to the right hand value which I had determined was fine from the log.debug print. So it's very random. But doing the toUpper() like you say may help. – eaglei22 Mar 15 '17 at 18:54
  • @MUG4N I removed it for security purposes. It does return a value though. Fixed it – eaglei22 Mar 15 '17 at 18:55
  • Could be that it is a DB2 specific conversion problem depending on the value of the UserID, see the following link for further information: https://www-01.ibm.com/support/docview.wss?uid=swg21443029 – MUG4N Mar 15 '17 at 19:11
  • I would agree if it happened to the same user repeatedly. But the same userid will have no problems 99.9% of the times, just will see this error pop up once in a while. I will try the suggestion of removing the UPPER( from the sql line, and using toUpper() on the C# getUserID() call and see if maybe by some luck that will resolve the issue. – eaglei22 Mar 15 '17 at 19:20
  • @eaglei22 - if `USERID` in the schema is a string type (varchar) and there is no case sensitivity enabled in your db2 setup you might be able to get rid of `UPPER` on the left side as well but I am not proficient enough in `db2` to say that for sure. Let us know what happens though either way. – Igor Mar 15 '17 at 19:35
  • It is hard for anyone else to say because we are guessing at what .net type `CurrentUser.getUserID()` actually returns as well as what db type `USERID` is in the `ROLE` table. One last note: you do prefix a schema to `Role`, are there multiple `role` tables and could one of those tables have a different schema or set of values than the other tables? – Igor Mar 15 '17 at 19:40
  • @Igor Thanks, It is case sensitive, so I need to check for that unfortunately.. I will throw some more logging in there and try to print out the actual value going into the parameter and see if it is any different. The getUserID() is just a static call to get the current windows user logged on (as a string), which uses HttpContext. It's all local to the getUserID() so there should not be any problems with multi-threading. USERID type is defined as, "OleDbType.Char" with a maximum of 10 characters. The prefix is just so we can set the environment (prod/test/dev) from a config file. – eaglei22 Mar 15 '17 at 19:48
  • Also for the prefix, there are different libraries used which that prefix represents, that Table is only in one of them. So there isn't any qualifying issues in that regards. I will follow up with any breakthroughs, but since the error is intermittent it will probably take some time to verify any changes made a difference. – eaglei22 Mar 15 '17 at 19:51
  • Looks like your inventing your own security "ROLES" and failing at it. – danny117 Mar 15 '17 at 20:17
  • This has nothing to do with security logic. Nor do you know our business environment. Please stick to answering the problen in question rather than your subjective statements. Thsnk you. – eaglei22 Mar 15 '17 at 20:20
  • @Igor made your suggested change. Checked the logs today, and there was a lot of activity, and no sign of the error. So I think for whatever reason that did it. – eaglei22 Mar 16 '17 at 14:38
  • 1
    @eaglei22 - thanks for reporting back and I am glad the problem appears to be fixed. – Igor Mar 16 '17 at 14:47

0 Answers0