SQL Error - Failed to enable constraints. One or more rows contain values violating n

[ralphy]

Very occasionally the following SQL query code throws an Exception.Message "Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints."

Code:

public bool LiveTV(int Channel_Number)
{....
DateTime now = DateTime.Now;
            
_strSQL  = "SELECT programme.oid, channel.name, programme.name, description, start_time, end_time ";
_strSQL += "FROM programme, channel ";
_strSQL += "WHERE channel.oid=programme.channel_oid ";
_strSQL += "AND channel.channel_number= " +Channel_Number;
_strSQL += " AND start_time <='" + now.ToString("yyyy-MM-dd HH:mm") +"'" ;
_strSQL += " AND end_time >='" + now.ToString("yyyy-MM-dd HH:mm") +"' ;";
        
DbCommand command = connection.CreateCommand();
DataTable dt = new DataTable();
                        
command.CommandText=_strSQL;
dt.Load(command.ExecuteReader());
.....

}

It doesn't happen often, but I cannot find out why it occurs when it does.

Does anyone have some ideas on what is going on and how to prevent the errors from occurring?

[Ommina]

Hm - I note that the DataTable.Load function also takes an (optional) error handler delegate, which you can use to determine which row caused the error, and can also be told to continue processing with the next row.

Not ideal, but armed with the row that it causing the conflict, you might be able to track down why the exception is being tossed in the first place.

My guess is that when the DataTable.Load does its thing, it is creating constraints (which it tries to figure out from the DataReader) that shouldn't be there. A null description maybe?

[ralphy]

Thanks for the details on the overload. I've added that to the code and have to wait to see what it pops up.

Unfortunately, the error doesn't happen often, and is hard to reproduce - It seems like you can rerun the query later and it won't come up with the error again:mad:.

[ralphy]

The DataTable overload did not catch the error, but the try/catch did, so I'm no closer in that regard.

However, what I did find was the if I left out the 'description' in the query, that it succeeded. If I look at the details of the description field for failed queries, I note that they are very long strings (over 256 characters). Is this the smoking gun?

Does anyone know of any limits in the length of string returned in this query and how to reset them?

[Ommina]

Hm.

Well (pulling guesses out of the air here). I note that the description field of the programme table is set to 255. What could be happening is that DataTable.Load is using this value as the maximum length of the column, so when it subsequently tries to retrieve a record with a description length greater than 255, it has a fit.

The default maximum length of a sqlite string is 1,000,000,000 bytes, so I think we're OK there.

You might try using the SQLite Command object (and Connection object as well, if you're not) instead of DbCommand. Since they are written with sqlite in mind, they may give you better results.

[ralphy]

Thanks Ommina. Your comment about the column length seems to consistent with what I am observing.

I'm really a complete novice at .net/C# and DataTables. I know SQLite3 works, and thought that a SQLite Command object might be better to use (presumably using System.Data.SQLite.dll?)

I did a very quick google last night after my post but couldn't find any examples quickly. Would you be able to point me somewhere, otherwise I'll keep googling.

Thanks

[ralphy]

I found some clues on the SQLite objects here. As with any search, the key is to choose the right keywords (it must have been getting late when I searched last night)

Anyway, there definitely seems to be a problem with using DBCommand and DataTable trying to retrieve a field with more than 255 characters from a field defined as VARCHAR(255) (as is the Programme.Description field)

For those interested, here's the new code snippet that seems to work.

Code:

using System.Data.SQLite;

private string _strSQL;
private SQLiteConnection connection;
private SQLiteDataReader sqlite_datareader;


private bool ExecuteSQL(string _StrSQL)
{
          SQLiteCommand command = connection.CreateCommand();
          command.CommandText=_strSQL;
          try
        {
            sqlite_datareader=command.ExecuteReader();
            int i=0;
            while (sqlite_datareader.Read()) // Read() returns true if there is still a result line to read
        {
            i++;
            Logger.Verbose("ExternalDisplay Database query " + _strSQL);
            Logger.Verbose("Channel     " + sqlite_datareader["channel"].ToString() );
            Logger.Verbose("Program     " + sqlite_datareader["program"].ToString() );
            Logger.Verbose("SubTitle    " + sqlite_datareader["sub_title"].ToString() );
            Logger.Verbose("Description " + sqlite_datareader["description"].ToString() );
            Logger.Verbose("Start Time  " + sqlite_datareader["start_time"].ToString() );
            Logger.Verbose("End Time    " + sqlite_datareader["end_time"].ToString() );
            
            _channel_name    = sqlite_datareader["channel"].ToString();
            _programme_name    = sqlite_datareader["program"].ToString();
            _sub_title    = sqlite_datareader["sub_title"].ToString();
            _description    = sqlite_datareader["description"].ToString();
            _start_time    = (DateTime) sqlite_datareader["start_time"];
            _end_time    = (DateTime) sqlite_datareader["end_time"];
            _duration        = _end_time - _start_time;
        }
        if (i == 1)
        {
                return true;
            }
            else
            {
                Logger.Warning("ExternalDisplay.ExecuteSQL): " + i + " rows returned");
                Logger.Warning("ExternalDisplay: SQL String " +  _strSQL);
        return false;
            }
            }
            
    catch (Exception e)
     {
                Logger.Error("ExternalDisplay.ExecuteSQL Run SQL Error " + e.Message);
                Logger.Error("ExternalDisplay: SQL String " +  _strSQL);
      return false;
    }
}