NextPVR Forums
  • ______
  • Home
  • New Posts
  • Wiki
  • Members
  • Help
  • Search
  • Register
  • Login
  • Home
  • Wiki
  • Members
  • Help
  • Search
NextPVR Forums Public Developers v
« Previous 1 … 35 36 37 38 39 … 93 Next »
SQL Error - Failed to enable constraints. One or more rows contain values violating n

 
  • 0 Vote(s) - 0 Average
SQL Error - Failed to enable constraints. One or more rows contain values violating n
ralphy
Offline

Senior Member

Posts: 255
Threads: 51
Joined: Nov 2006
#1
2008-01-30, 11:26 AM
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?
[SIZE="1"]Silverstone GD01S-MXR (three dead rows of pixels in the LCD and defective remote control), Power: Zalman ZM460B-APS (blew up - can't remember what's there now); CPU: Pentium D 3.2 GHz with Asus V72 Cooler; MD: Asus P5LD2 Deluxe 2048MB,
WDC WD10EADS 1TB Data, 320GB System, Asus EN9400GT Silent 512MB, Hauppauge HVR 1300,
XP Home SP3, GB-PVR 2.0, ExternalDisplay v0.3[/SIZE]
Ommina
Offline

Senior Member

Posts: 330
Threads: 39
Joined: Feb 2006
#2
2008-02-01, 01:25 AM
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
Offline

Senior Member

Posts: 255
Threads: 51
Joined: Nov 2006
#3
2008-02-02, 09:08 AM
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:.
[SIZE="1"]Silverstone GD01S-MXR (three dead rows of pixels in the LCD and defective remote control), Power: Zalman ZM460B-APS (blew up - can't remember what's there now); CPU: Pentium D 3.2 GHz with Asus V72 Cooler; MD: Asus P5LD2 Deluxe 2048MB,
WDC WD10EADS 1TB Data, 320GB System, Asus EN9400GT Silent 512MB, Hauppauge HVR 1300,
XP Home SP3, GB-PVR 2.0, ExternalDisplay v0.3[/SIZE]
ralphy
Offline

Senior Member

Posts: 255
Threads: 51
Joined: Nov 2006
#4
2008-02-05, 12:19 PM
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?
[SIZE="1"]Silverstone GD01S-MXR (three dead rows of pixels in the LCD and defective remote control), Power: Zalman ZM460B-APS (blew up - can't remember what's there now); CPU: Pentium D 3.2 GHz with Asus V72 Cooler; MD: Asus P5LD2 Deluxe 2048MB,
WDC WD10EADS 1TB Data, 320GB System, Asus EN9400GT Silent 512MB, Hauppauge HVR 1300,
XP Home SP3, GB-PVR 2.0, ExternalDisplay v0.3[/SIZE]
Ommina
Offline

Senior Member

Posts: 330
Threads: 39
Joined: Feb 2006
#5
2008-02-05, 09:24 PM
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
Offline

Senior Member

Posts: 255
Threads: 51
Joined: Nov 2006
#6
2008-02-05, 11:44 PM
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
[SIZE="1"]Silverstone GD01S-MXR (three dead rows of pixels in the LCD and defective remote control), Power: Zalman ZM460B-APS (blew up - can't remember what's there now); CPU: Pentium D 3.2 GHz with Asus V72 Cooler; MD: Asus P5LD2 Deluxe 2048MB,
WDC WD10EADS 1TB Data, 320GB System, Asus EN9400GT Silent 512MB, Hauppauge HVR 1300,
XP Home SP3, GB-PVR 2.0, ExternalDisplay v0.3[/SIZE]
ralphy
Offline

Senior Member

Posts: 255
Threads: 51
Joined: Nov 2006
#7
2008-02-06, 10:03 AM
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;
    }
}
[SIZE="1"]Silverstone GD01S-MXR (three dead rows of pixels in the LCD and defective remote control), Power: Zalman ZM460B-APS (blew up - can't remember what's there now); CPU: Pentium D 3.2 GHz with Asus V72 Cooler; MD: Asus P5LD2 Deluxe 2048MB,
WDC WD10EADS 1TB Data, 320GB System, Asus EN9400GT Silent 512MB, Hauppauge HVR 1300,
XP Home SP3, GB-PVR 2.0, ExternalDisplay v0.3[/SIZE]
« Next Oldest | Next Newest »

Users browsing this thread: 1 Guest(s)



Possibly Related Threads…
Thread Author Replies Views Last Post
  API request returns xml on error scJohn 4 1,896 2020-08-25, 05:41 PM
Last Post: sub
  Status field values in Table SCHEDULED_RECORDING scJohn 2 2,322 2019-07-03, 07:01 PM
Last Post: sub
  custom creation of a recurring error p37307 2 2,251 2017-12-19, 03:45 AM
Last Post: mvallevand
  GetClientIdentifier() return values JavaWiz 3 1,828 2009-03-05, 02:11 PM
Last Post: ACTCMS
  Weather Plugin Error Smoker 26 7,634 2009-02-01, 05:52 AM
Last Post: n00dle
  Validating XML InnerText values ACTCMS 14 4,487 2008-08-27, 03:20 AM
Last Post: whurlston
  Error initializing event plugin: Exception has been thrown by the target of an invoca ralphy 1 1,993 2008-01-21, 11:23 PM
Last Post: ralphy
  Help needed with error in config.exe.log idkpmiller 3 1,798 2007-01-24, 06:08 AM
Last Post: sub
  SkinHelper Constructor error psycik 5 2,184 2006-06-06, 04:20 AM
Last Post: psycik
  Enhanced Web Admin (Time Slice error?) SFX Group 2 1,776 2006-02-04, 08:10 PM
Last Post: normanr

  • View a Printable Version
  • Subscribe to this thread
Forum Jump:

© Designed by D&D, modified by NextPVR - Powered by MyBB

Linear Mode
Threaded Mode