Hello everyone, Sorry about my last email... I clicked Send too quickly.
Jay, the book is great, I have discovered quite a few details I had overlooked (or perhaps missed, since I worked with earlier versions of SQLite and some current features were not available yet). Thank you and all who responded. It's definitely more clear to me how this works... excellent. Best regards, -- Tito 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
