How to use Logical field with SQLite

[sudip]

Hello,

Previously I used Integer (0 and 1) for logical field with SQLite. But, is there any better way to do this?

As per SQLite Documentation:

1.1 Boolean Datatype

SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).

This is also helpful http://www.mail-archive.com/sqlite-user ... 30127.html

Rathi, can we change your function as:

Code: Select all

function sql(dbo1,qstr)
local table := {}
local currow := nil
local tablearr := {}
local rowarr := {}
local typesarr := {}
local current := ""
local i := 0
local j := 0
local type1 := ""
if empty(dbo1)
   msgstop("Database Connection Error!")
   return tablearr
endif
table := sqlite3_get_table(dbo1,qstr)
if sqlite3_errcode(dbo1) > 0 // error
   msgstop(sqlite3_errmsg(dbo1)+" Query is : "+qstr)
   return nil
endif
stmt := sqlite3_prepare(dbo1,qstr)
IF ! Empty( stmt )
   for i := 1 to sqlite3_column_count( stmt )
      type1 := upper(alltrim(sqlite3_column_decltype( stmt,i)))
      do case
         case type1 == "INTEGER" .or. type1 == "REAL" .or. type1 == "FLOAT" .or. type1 == "DOUBLE"
            aadd(typesarr,"N")
         case type1 == "DATE" .or. type1 == "DATETIME"
            aadd(typesarr,"D")
         case type1 == "BOOLEAN"            // here is the change
            aadd(typesarr, "L")
         otherwise
            aadd(typesarr,"C")
      endcase
   next i
endif
sqlite3_reset( stmt )
if len(table) > 1
   asize(tablearr,0)
   for i := 2 to len(table)
      rowarr := table[i]
      for j := 1 to len(rowarr)
         do case
            case typesarr[j] == "D"
               cDate := substr(rowarr[j],1,4)+substr(rowarr[j],6,2)+substr(rowarr[j],9,2)
               rowarr[j] := stod(cDate)
            case typesarr[j] == "N"
               rowarr[j] := val(rowarr[j])
            case typesarr[j] == "L"                      // here is the change
               rowarr[j] := if(rowarr[j]==1, .T., .F.)
         endcase
      next j
      aadd(tablearr,aclone(rowarr))
   next i
endif
return tablearr

Your C2SQL function already supports this.

Thanks in advance.

With best regards.

Sudip

[Rathinagiri]

Thanks a lot Sudip.

[sudip]

Hello Rathi,

Your Sql() function is excellent. Previously I didn't used C2sql() function very much for SQLite. I used the typecasting explicitly. Now, I am using C2Sql(). It makes my code more simple with automatic typecasting!!! Thanks a lot

I found, when I used "BOOLEAN" field, it's stored as Character, not Integer

So I changed SQL function:

Code: Select all

function sql(dbo1,qstr)
local table := {}
local currow := nil
local tablearr := {}
local rowarr := {}
local typesarr := {}
local current := ""
local i := 0
local j := 0
local type1 := ""
if empty(dbo1)
   msgstop("Database Connection Error!")
   return tablearr
endif
table := sqlite3_get_table(dbo1,qstr)
if sqlite3_errcode(dbo1) > 0 // error
   msgstop(sqlite3_errmsg(dbo1)+" Query is : "+qstr)
   return nil
endif
stmt := sqlite3_prepare(dbo1,qstr)
IF ! Empty( stmt )
   for i := 1 to sqlite3_column_count( stmt )
      type1 := upper(alltrim(sqlite3_column_decltype( stmt,i)))
      do case
         case type1 == "INTEGER" .or. type1 == "REAL" .or. type1 == "FLOAT" .or. type1 == "DOUBLE"  .or. type1 == "NUMERIC"
            aadd(typesarr,"N")
         case type1 == "DATE" .or. type1 == "DATETIME"
            aadd(typesarr,"D")
         case type1 == "BOOLEAN"
            aadd(typesarr, "L")
         otherwise
            aadd(typesarr,"C")
      endcase
   next i
endif
sqlite3_reset( stmt )
if len(table) > 1
   asize(tablearr,0)
   for i := 2 to len(table)
      rowarr := table[i]
      for j := 1 to len(rowarr)
         do case
            case typesarr[j] == "D"
               cDate := substr(rowarr[j],1,4)+substr(rowarr[j],6,2)+substr(rowarr[j],9,2)
               rowarr[j] := stod(cDate)
            case typesarr[j] == "N"
               rowarr[j] := val(rowarr[j])
            case typesarr[j] == "L"
               if valtype(rowarr[j]) = "C"
                  rowarr[j] := if(rowarr[j]=="1", .T., .F.)
			      elseif valtype(rowarr[j]) = "N"
                  rowarr[j] := if(rowarr[j]==1, .T., .F.)
               endif
         endcase
      next j
      aadd(tablearr,aclone(rowarr))
   next i
endif
return tablearr

Your C2Sql() function doesn't need any change (Thanks a lot SQLite for it's type casting) Because it is converted to character type automatically.

Code: Select all

...
      case Valtype(Value) == "L"
         cValue := AllTrim(Str(iif(Value == .F., 0, 1)))
...

With best regards.

Sudip

[Rathinagiri]

Thanks for the nice explanation Sudip.