1

I have a database built with this statement:

CREATE TABLE starsystems
(
   name TEXT NOT NULL COLLATE NOCASE,
   systemaddress INT UNIQUE,
   CONSTRAINT combined_uniques UNIQUE (name, systemaddress)
)

(and i can't change how the DB and the table is made)

One of the fields contains these data:

Name: "61 Cygni", systemaddress: 5856288576210

I'm using powershell to execute this query to fill a dataset:

$oSQLiteDBCommand.Commandtext='SELECT name, systemaddress FROM starsystems WHERE name IS "61 Cygni" '

But my result is:

61 Cygni, -2046815534

So, for some reason, the systemaddress field i get is like

(systemaddress & 0x00000000FFFFFFFF) | 0xFFFFFFFF00000000

(sorry for the horrible explanation, i lack the right english words, please pardon me).

What am i doing wrong and what should i do instead? And, since i need to change the systemaddress value, what should i do it to avoid this kind of unwanted conversion?

mklement0
  • 382,024
  • 64
  • 607
  • 775
Parduz
  • 662
  • 5
  • 22
  • 1
    The implication is that `INT` is a 64-bit integer in SQLite, but it gets truncated to a 32-bit integer in the query result on the PowerShell side. – mklement0 Jun 17 '23 at 01:11

1 Answers1

1

The solution i've found is to cast the INT field to text:

SELECT name, cast(systemaddress as text) as sysadd FROM starsystems WHERE name IS "61 Cygni"

The alias sysadd is handy 'cause it gives an easier column name than cast(systemaddress as text).

Parduz
  • 662
  • 5
  • 22
  • Good to know a workaround, but it would be good to know if there's a proper solution. What are you using on the PowerShell side to communicate with SQLite? – mklement0 Jun 17 '23 at 14:53
  • 1
    @mklement0: this is the code i use to query the DB: https://pastebin.com/851vQnsq – Parduz Jun 19 '23 at 11:24
  • 1
    Thanks; I see that you're using the [`System.Data.SQLite`](https://www.nuget.org/packages/System.Data.SQLite) NuGet package. A homegrown alternative that maps `INTEGER` SQLite values to `System.Int64` .NET values can be found in [this answer](https://stackoverflow.com/a/76515318/45375). – mklement0 Jun 20 '23 at 13:44
  • Thanks to you. I've "just" copy-pasted code from tutorials and from answers here and adjusted it until it worked, so i don't really understand what a NuGet package is... but if the answer you linked resolves it better, i'll try that and see if i can get it to works: i should have already learned something in the process :D – Parduz Jun 20 '23 at 14:16
  • 1
    A NuGet packages are a pre-built .NET libraries (assemblies), typically downloaded from https://www.nuget.org/ While they're well supported in .NET SDK projects, their use from PowerShell is difficult - see [this answer](https://stackoverflow.com/a/75537273/45375). The previously linked answer is dependency-free; it uses `Add-Type` to compile C# code on demand that directly interacts with the native SQLite library. – mklement0 Jun 20 '23 at 14:30