On 06/11/2010, at 21:28, Jay A. Kreibich wrote: > On Sat, Nov 06, 2010 at 08:35:10PM -0300, Tito Ciuro scratched on the wall: >> Hello, >> >> I have a question about manifest typing/data affinity. Assume I have >> created this table: >> >> CREATE TABLE foo (ROWID INTEGER PRIMARY KEY, key TEXT, attr TEXT, value >> NONE); >> >> I was reading the Using SQLite book and came across page #38 (#60 on >> the PDF version) where it states: > > > I hope the book is proving useful. > > >> "None: A column with a none affinity has no preference over storage >> class. Each value is stored as the type provided, with no attempt >> to convert anything." >> >> So it seems that using NONE is a hint that different data types are >> going to be stored in that column, correct? > > I suppose you could look at it that way, but it isn't meant to be > explicit. > > With the exception of an INTEGER PRIMARY KEY, any column can hold > row-values of any type. The affinity will influence stored values > to specific types, but it does not prevent other types from being > stored if the conversion cannot be made. > > The NONE affinity simply indicates to the database that you always > store values in their provided type, and that no conversions should > be attempted, even if a conversion could be done in a loss-less way. > >> My main question has to do with binding values to precompiled statements. >> For the value column, should I: >> >> a) use sqlite3_bind_value()? > > sqlite3_bind_value() is for binding "sqlite3_value" data structures. > If you have one of those (from an sqlite3_column_value(), for > example), and it is in the type representation you want (text, > int, etc), then go ahead and use it. > > If you're binding a more traditional value, such as a string or > integer, you should use one of the sqlite3_bind_text(), > sqlite3_bind_int(), or similar functions. > > The bind function you choose will set the "type" of the value. > If the column has a NONE affinity, no conversion will be attempted > and the value, in the representation you provided, will be stored > directly. > >> b) store it as a string using sqlite3_bind_text()? Will >> sqlite3_bind_text() allow SQLite to choose the proper data affinity >> even though I'm binding it as text? > > An affinity is a property of a table column. The affinity of a > column is defined by the "SQL type" passed to CREATE TABLE. Once the > table has been created, the affinity of each column is set. So the > only time SQLite "chooses" an affinity is when it parses the CREATE > TABLE statement. > > So, for example, if you have an INSERT statement that binds a text > value to a parameter used to set a column with a NONE affinity, the > value in the new row will *always* be stored as a text value, > regardless of the string value. If you were to bind an integer, the > value would always be stored as an integer, and so on. > > This would work differently if the column had a NUMERIC affinity, for > example. In that case, binding the string "abc" would result in > storage of a text value, while binding the string "132" would result > in an integer and "85.3" would result in a floating-point. Binding > 43 (the integer, not a two-character string, using sqlite3_bind_int()) > would result in an integer as well. > > > > As you might know, you can use different sqlite3_column_xxx() functions > to extract values in a specific representation (type), even if that > returned representation does not match the type of the stored value. > For example, if you know you're going to print out a value, you can > use sqlite3_column_text() to get a text representation of the value, > even if that value is stored in the database as an integer. The > conversions used for this are given in table 7.1 of Using SQLite. > > On the input side, you can use different sqlite3_bind_xxx() function > to provide values in a representation (type) that might not match the > type used to actually store the value. The affinity defines your > "preferred" storage type, and is used as a hint to do input > conversions. So if you're taking use input for a number, you can > take the text value passed in by your applications GUI and pass that > directly to sqlite3_bind_text(), even if the string represents a > number, and you want to store it as a number (and have told the > database this by using an INTEGER, REAL, or NUMERIC affinity). > > The NONE affinity is simply a way of saying that you will always > provide values in the representation that should be used to > store them. This means that the specific sqlite3_bind_xxx() function > you choose more or less sets the type of the value. > > -j > > -- > Jay A. Kreibich < J A Y @ K R E I B I.C H > > > "Intelligence is like underwear: it is important that you have it, > but showing it to the wrong people has the tendency to make them > feel uncomfortable." -- Angela Johnson > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
