Data Types In SQLite.

[Gary Stout]

The below was taken from http://www.sqlite.org/datatype3.html.
1. Storage Classes
   Each value stored in an SQLite database has one of the following storage classes:
   
      NULL. The value is a NULL value.
      INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
   
      REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.
   
      TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16-LE).
   
      BLOB. The value is a blob of data, stored exactly as it was input.

If I am reading this correct, are these 5 classes the only data types that SQLitening supports?

I am using a program that Joe Byrne mentioned was very handy called "SQLite Expert Personal" and it has many other data classes listed but has the ability to modify the types. If the above 5 are the only choices, I am going to remove all of the extras to reduce any confusion.

Thanks,
Gary

[Fred Meier]

I also use, and like, SQLite Expert (SE).  It supports its own "custom"
data types.  These "custom" types are only useful, as far as I can tell,
within SE and have no affect on the internal five storage classes in
SQLite3 nor any usage within your PB code. 

For example, you can declare a field to be DATE in SE and then set that
field to 'XXX' in you PB program.  Go back to SE and look at the data, you
won't like it. 

Be carful changing data types within SE.  It will change your data.  Also, I
have found no way in SE to change or set a data type to NONE.  In short, I
never use SE to define nor change my data types. 

Look in the SE help file under 'Data Types'.  You will see the five
storage classes that SQLite supports again listed and all the "custom"
data types that SQLite Export supports. 

[Bern Ertl]

I posted this code snippet on the PB forums, but am reposting it here as it was built with this discussion in mind:

Code Select Expand

#COMPILE EXE
#DIM ALL

FUNCTION MyVal( sSource AS STRING) AS LONG

'Assumes sSource contains only characters from "0" to "9" and %MaxLong >= VAL( sSource) >= 0

#REGISTER NONE
LOCAL psSource AS LONG

psSource = STRPTR( sSource)

!mov   esi, psSource
!mov   ecx, [esi-4]      ; get size of string data

!xor   eax, eax          ; clear EAX
!xor   ebx, ebx          ; clear EBX
!mov   edi, 10           ; base 10

!test  ecx, ecx
!jz    Done

NextChar:
!mov   bl, [esi]         ; get a character
!mul   edi               ; eax = eax * 10
!sub   bl, 48            ; convert character to binary digit
!add   eax, ebx          ; add in the digit
!inc   esi
!dec   ecx
!jnz   NextChar

Done:
!mov   FUNCTION, eax

END FUNCTION

%NumTests = 1000000

FUNCTION PBMAIN() AS LONG

LOCAL a$, I AS LONG, J AS LONG
LOCAL qValTix AS QUAD, qMyValTix AS QUAD

RANDOMIZE TIMER

'Does it work?
DO
a$ = FORMAT$( RND( 0, 2147483647&))
I = MyVal(a$)
LOOP UNTIL MSGBOX( "a$ = " + $DQ + a$ + $DQ + $CRLF + $CRLF + "MyVal( a$) = " + FORMAT$( I) + $CRLF + $CRLF + "Test again?", %MB_ICONQUESTION OR %MB_YESNO, "MyVal") = %IDNO

'Benchmarking...
DO
a$ = FORMAT$( RND( 0, 2147483647&))

TIX qValTix
FOR J = 1 TO %NumTests
I = VAL(a$)
I = VAL(a$)
I = VAL(a$)
NEXT
TIX END qValTix

TIX qMyValTix
FOR J = 1 TO %NumTests
I = MyVal(a$)
I = MyVal(a$)
I = MyVal(a$)
NEXT
TIX END qMyValTix

LOOP UNTIL MSGBOX( "VAL() took " + FORMAT$( qValTix, "#,") + " tix." + $CRLF + $CRLF + "MyVal() took " + FORMAT$( qMyValTix, "#,") + " tix." + $CRLF + $CRLF + "Test again?", %MB_ICONQUESTION OR %MB_YESNO, "MyVal") = %IDNO

END FUNCTION

[Jean-Pierre LEROY]

Sorry, I just removed my answer.

[cj]

I am bringing back this very old subject because of problems not defining a column as TEXT.
Be careful not specifying INTEGER or TEXT in CREATE statements.

1. text and binary values are inserted into the column
2. 101 is not "101" so searching and sorting may not return expected value.

If column is UNIQUE:
insert 101 and '101' and two values are insert if no datatype
insert 101 and '101' and second value produces duplicate error if datatype was specified

Bottom line:
Specifying (or not specifing) data type in CREATE statements is very important

CREATE statement without specifying column type and 2 records are inserted
create table t1(key1 primary key) without rowid
insert into t1 values('101') 'insert success
insert into t1 values(101)   'insert sucess

CREATE statement specifying column as TEXT and 1 record is insert and second produces duplicate error
create table t1(key1 TEXT primary key) without rowid"
insert into t1 values('101') 'insert success
insert into t1 values(101)   'duplicate

This example demonstrates inserting 2-values into a unique column
The first loop creates table without a datatype and 2-values are insert
The second loop creates table with a datatype and 1-value is insert

#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG
 slSetProcessMods "E1"
 LOCAL x AS LONG, sArray() AS STRING
 FOR x = 1 TO 2
  slopen "junk.db3","C"
  slexe "drop table if exists t1"

  IF x =1 THEN
   slexe "create table if not exists t1(key1 primary key) without rowid"
  ELSE
   slexe "create table if not exists t1(key1 TEXT primary key) without rowid"
  END IF

  slexe "insert into t1 values('101')"
  slexe "insert into t1 values(101)"
  slselary "select * from t1",sArray()
  ? JOIN$(sArray(),$CR),,"create table if not exists t1(key1 primary key) without rowid"
  slClose
 NEXT
END FUNCTION

[Fredrick Ughimi]

Interesting and right on point.

I usually define all my data types in a table. I use INTEGER, TEXT, BLOB and REAL most of the times and it has served me well.

[cj]

I find the way this is handled in SQLite to be one of the great features.
I normally create columns with no data type, which will result in an
affinity of none, by using the following create syntax:
Code Select
  Create Table T1 (C1, C2, C3)
I have found no advantage in assigning column affinity using the following:
Code Select
  Create Table T1 (C1 Integer, C2 Text, C3 Real)

Hi, Fredrick!

It goes against the above post #1, but had to say something.
Glad we are doing it the same way.
I haven't been assigning affinity or NOT NULL in my demos and will try to remember.