--- John Stanton <[EMAIL PROTECTED]> wrote:
> Sqlite lets you put in anything as the declared type. "DEAD PARROT",
> "MONGOOSE", "GODZILLA" or "DECIMAL(6,1)" are all acceptable declared
> types. Sqlite makes the underlying type TEXT if it is not obviously
> numeric.
The default affinity type is SQLITE_AFF_NUMERIC if SQLite cannot determine
the type:
SQLite version 3.3.17
Enter ".help" for instructions
sqlite> create table t1(a GODZILLA);
sqlite> insert into t1 values(3);
sqlite> insert into t1 values('duck');
sqlite> insert into t1 values('007');
sqlite> insert into t1 values('000000000004.56');
sqlite> select a, typeof(a) from t1;
3|integer
duck|text
7|integer
4.56|real
Note, if a column has no type specified, then its affinity is none:
SQLite version 3.3.17
Enter ".help" for instructions
sqlite> create table n1(a);
sqlite> insert into n1 values('009');
sqlite> select a, typeof(a) from n1;
009|text
But it's up to your program or sqlite wrapper to decide how to read each
column with the appropriate sqlite3_column_* function.
/*
** Scan the column type name zType (length nType) and return the
** associated affinity type.
**
** This routine does a case-independent search of zType for the
** substrings in the following table. If one of the substrings is
** found, the corresponding affinity is returned. If zType contains
** more than one of the substrings, entries toward the top of
** the table take priority. For example, if zType is 'BLOBINT',
** SQLITE_AFF_INTEGER is returned.
**
** Substring | Affinity
** --------------------------------
** 'INT' | SQLITE_AFF_INTEGER
** 'CHAR' | SQLITE_AFF_TEXT
** 'CLOB' | SQLITE_AFF_TEXT
** 'TEXT' | SQLITE_AFF_TEXT
** 'BLOB' | SQLITE_AFF_NONE
** 'REAL' | SQLITE_AFF_REAL
** 'FLOA' | SQLITE_AFF_REAL
** 'DOUB' | SQLITE_AFF_REAL
**
** If none of the substrings in the above table are found,
** SQLITE_AFF_NUMERIC is returned.
*/
char sqlite3AffinityType(const Token *pType){
u32 h = 0;
char aff = SQLITE_AFF_NUMERIC;
const unsigned char *zIn = pType->z;
const unsigned char *zEnd = &pType->z[pType->n];
while( zIn!=zEnd ){
h = (h<<8) + sqlite3UpperToLower[*zIn];
zIn++;
if( h==(('c'<<24)+('h'<<16)+('a'<<8)+'r') ){ /* CHAR */
aff = SQLITE_AFF_TEXT;
}else if( h==(('c'<<24)+('l'<<16)+('o'<<8)+'b') ){ /* CLOB */
aff = SQLITE_AFF_TEXT;
}else if( h==(('t'<<24)+('e'<<16)+('x'<<8)+'t') ){ /* TEXT */
aff = SQLITE_AFF_TEXT;
}else if( h==(('b'<<24)+('l'<<16)+('o'<<8)+'b') /* BLOB */
&& (aff==SQLITE_AFF_NUMERIC || aff==SQLITE_AFF_REAL) ){
aff = SQLITE_AFF_NONE;
#ifndef SQLITE_OMIT_FLOATING_POINT
}else if( h==(('r'<<24)+('e'<<16)+('a'<<8)+'l') /* REAL */
&& aff==SQLITE_AFF_NUMERIC ){
aff = SQLITE_AFF_REAL;
}else if( h==(('f'<<24)+('l'<<16)+('o'<<8)+'a') /* FLOA */
&& aff==SQLITE_AFF_NUMERIC ){
aff = SQLITE_AFF_REAL;
}else if( h==(('d'<<24)+('o'<<16)+('u'<<8)+'b') /* DOUB */
&& aff==SQLITE_AFF_NUMERIC ){
aff = SQLITE_AFF_REAL;
#endif
}else if( (h&0x00FFFFFF)==(('i'<<16)+('n'<<8)+'t') ){ /* INT */
aff = SQLITE_AFF_INTEGER;
break;
}
}
return aff;
}
____________________________________________________________________________________
Take the Internet to Go: Yahoo!Go puts the Internet in your pocket: mail, news,
photos & more.
http://mobile.yahoo.com/go?refer=1GNXIC
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
