System.Data.SQLite
View Ticket
Not logged in
Ticket UUID: 7714b60d61e4c15506edd239b1f76c0af1698c23
Title: Boolean Always shows as false
Status: Closed Type: Code_Defect
Severity: Important Priority: Medium
Subsystem: Convert Resolution: Fixed
Last Modified: 2015-09-25 21:17:25
Version Found In: 1.0.97.0
User Comments:
anonymous added on 2015-09-24 19:47:09: (text/x-fossil-plain)
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.

mistachkin added on 2015-09-24 20:09:55: (text/x-fossil-plain)
What are the actual values in the table for the pVisible field?

anonymous added on 2015-09-25 14:00:05: (text/x-fossil-plain)
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.

mistachkin added on 2015-09-25 17:16:43: (text/x-fossil-plain)
Have you tried using the latest release (1.0.98.0)?

mistachkin added on 2015-09-25 17:32:18: (text/x-fossil-plain)
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

mistachkin added on 2015-09-25 21:17:25: (text/x-fossil-plain)
Fixed on trunk via check-in [ed83298257].