System.Data.SQLite

1. Create SQLite DB: 

CREATE TABLE [tbcontrol] (
[pID] INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,
[pName] text  NULL,
[pValue] text  NULL,
[pVisible] BOOLEAN
)

2. Add test values to table, Make sure pVisible has both true and false values.

3. Create New .net Project using System.Data.SQLite (from nuget)

4. Add following Logic:

Imports System.Data.SQLite

Public Sub GetControl()
        Dim conControl As New SQLiteConnection("Data Source =" & location & "\control.s3db")
        Dim cmdCont As New SQLiteCommand("", conControl)
        cmdCont.CommandText = "Select * from tbcontrol"
        Dim da As New SQLiteDataAdapter(cmdCont)
        Dim dt As New DataTable
        da.Fill(dt)
End Sub

5. Call getcontrol() and view contents of dt. 

What Happened:
pVisible will always show false regardless of actual value

What was Expected to Happen:
pvisible would show either True or False depending on Actual Value

Additional Info:

Win 7 x64
Visual Studio 2010
nuget System.data.sqlite.dll
Vb.net 4.0

It appears that there is a logic flaw when converting SQLite Bool to .net Bool. also, If I try cast(pvisible as int), the result is always 0.

Workaround:
Instead of "Select * from tbcontrol", use "Select cast(pvisible as nvarchar(5)) from tbcontrol. The resulting string will correctly have True or False.


A test solution and Database can be provided upon request.

What are the actual values in the table for the pVisible field?

Insert into tbcontrol (pName, pValue, pVisible) values ( "Test1", "25", "True")
Insert into tbcontrol (pName, pValue, pVisible) values ( "Test2", "26", "False")


Both of these will show false in .net. Opening up the database in any gui editor shows the expected value.

Have you tried using the latest release (1.0.98.0)?

Translated test case:

    set db [sql open -type SQLite {Data Source=test.db;}]

    sql execute $db {CREATE TABLE [tbcontrol] ([pID] INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL, [pName] text  NULL, [pValue] text  NULL, [pVisible] BOOLEAN);}
    sql execute $db {Insert into tbcontrol (pName, pValue, pVisible) values ( "Test1", "25", "True");}
    sql execute $db {Insert into tbcontrol (pName, pValue, pVisible) values ( "Test2", "26", "False");}

    set connection [getDbConnection]

    set command [$connection -alias CreateCommand]
    $command CommandText {Select * from tbcontrol;}

    set da [object create -alias System.Data.SQLite.SQLiteDataAdapter $command]
    set dt [object create -alias System.Data.DataTable]

    $da Fill $dt
    getRowsFromDataTable $dt

    # freeDbConnection
    # sql close $db

Fixed on trunk via check-in [ed83298257].