No device listed -- error saving capture source: SQLite Error 1: 'no such column: −1'

[mvallevand]

No it is not directly related to the first post. NextPVR is sending this valid SQL command

insert into CAPTURE_SOURCE (name, recorder_plugin_class, present, enabled, priority) values (@name, 'NShared.DigitalRecorder', 'Y', 'Y', -1)

but an issue in NextPVR's current netcore implementation for at least the Swedish locale and a few other Nordic countries Norway, Finland, and Faroe islands, (but not Denmark,Iceland or Greenland) is interpreting the negative value incorrectly. Submitting it in sqlite3 works. I tried a couple of other non-English locales and there wasn't the same problem so I don't see a specific pattern.

Using other negative values gives similar misleading error messages. Positive values are fine.

Code:

insert into CAPTURE_SOURCE (name, recorder_plugin_class, present, enabled, priority) values (@name, 'NShared.DigitalRecorder', 'Y', 'Y', -666)

Unhandled exception. Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'no such column: −666'.
   at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteCommand.PrepareAndEnumerateStatements(Stopwatch timer)+MoveNext()
   at Microsoft.Data.Sqlite.SqliteCommand.Prepare()
   at NextTool.Program.Main(String[] args)
   at NextTool.Program.<Main>(String[] args)

I thought at first Sweden used another format, for negation brackets or a space but I couldn't find anything saying that. In the future the change will be passing the -1 in a string NextPVR will use a sqlite parameter.

For now try the workaround. The environment setting only impacts NextPVR.

Martin

[skrottapple]

Thanks, that's interesting. I get the impression that you're saying that the library is performing the integer to string conversion for the '-1'. Then, basically, it looks like the library is performing localized number formatting and manages to insert a certain minus sign that makes SQLite stop interpreting the value as a literal and starts treating it as a column name.

Although the exception you just posted literally says "no such column: −666" which does not exactly convince me that "−666" is the alleged returned status code of the executed query — and even less the return value of "GetOrdinal" (edit: unless the "priority" var is init'd with it at an earlier time than the code snippet that was posted). I think that pretty much looks like the first post:

> it looks like it's trying to access a column with name "-1"

Thanks for the workaround. I'll take a look at it next time I pick up the project.

[mvallevand]

I am saying the opposite, NextPVR sends a (valid) text string as a command that is parsed into the components, by the .NET libraries. I am not sure how globalization is involved in the text parsing but it is failing. NextPVR is not using the most current .NET sqlite libraries which might explain this but the workarounds will handle it for now. In the future when NextPVR is updated and if the problem continues I will report it on the .NET GitHub.

Yes the text message from the sqlite library is saying that the column name is bad but that is bogus since the names are shown in the first part of the command. (name, recorder_plugin_class, present, enabled, priority) and they are not wrong. GetOrdinal() is not even used in the standalone test app that I am using so it is a red herring. -666 is absolutely not a return code just part of the bogus error display returned from the libray. The error code is int32.minvalue and the sqlite error code as shown in 1.

Martin

[mvallevand]

Turns out the problem is here, https://github.com/dotnet/runtime/issues...-727261177 Sweden and some other countries use a different negative sign so sub's priorty.ToString() returns a value that sqlite3 doesn't understand.

If you check your log in a hex editer you can see the E2 88 so the answer was there all along.

Martin

[skrottapple]

> NextPVR sends a (valid) text string as a command that is parsed into the components, by the .NET libraries

Oh, I see. That does sound very difficult to guard against. And thank you for your patience in dealing with someone who hasn't touched SQL/direct db interaction in some time! (not to mention .NET... )

As for the ToString() part, I did look at that github issue, and that's also interesting. I read in the ToString()-docs that "it converts an object to its string representation so that it is suitable for display", so if the .NET libraries are using it under the hood in database routines then, superficially, that does appear irresponsible of them. I.e. "for display" strongly suggests to me that it's not a stable interface, so why on earth would they default to the assumption that your data is meant for "display"... Strange.

> the sqlite library is saying that the column name is bad but that is bogus since the names are shown in the first part of the command

Okay, what seems to be implied here is that "sqlite can't interpret the right hand side of the 'values' keyword as column names", which sounds perfectly reasonable to me. The StackOverflow answer I linked to however did imply that there was no such restiction when using a simple '=', and I just assumed the same behavior was also present for 'values'. I realize now that that must have been the wrong assumption -- I haven't looked up the semantics for that SQL keyword. Still, quite the coincidence (and confusing) that both 'values' and '=' seemed to behave the same in this case.

> -666 is absolutely not a return code

Thanks, just double-checking. I didn't want to dismiss the assertions to the contrary without confirmation.

> If you check your log

Actually it's also in the thread title, so I guess the answer was there before entering!

[mvallevand]

Actually it's also in the thread title, so I guess the answer was there before entering!

Pretty cool I hadn't notice that.

Using name and values isn't the problem, set ... =  ...  would still fail if the code used the ToString() to build the query.  Sub's solution to use the integer will solve this.

Martin

[skrottapple]

I don't doubt it will.

The workaround works.

I wish you good luck in your endeavors.