--- [EMAIL PROTECTED] wrote: > Thank you Joe. At least sqlite3_column_type gives me a column type I > can use as > a hint. > > The issue is that I have a well-defined column type naming convention for my > wrapper classes to look for (INT, TEXT, FLOAT, BLOB, etc.). For binary data > fields, its BLOB. For other field types, when encrypted they should be > prefixed with BLOB (eg. BLOB_INT, BLOB_TEXT, BLOB_FLOAT), so that my low-level > execute function can provide hints to the downstream result-set parsing to use > for converting types. > > Wouldn't it be entirely possible to provide the column's type if the function > used only a single column in its parameter list? For example, consider the
You can't infer a function's return type from its arguments. Take the hypothetical function FOO(x). If I pass it a number, it will return the number spelled out as TEXT, but if I pass it a BLOB it will return its length*PI as a FLOAT. As a possible extension one could see sqlite3_create_function taking an optional argument with a hint as to its return type that sqlite may use for sqlite3_column_decltype. But SQLite does not currently return any column types for any ad-hoc expression. So this would not be a trivial change. > UPPER function. In cases where its just used to convert a single column to > upper-case, couldn't Sqlite just use that column's data type? E.g., > > UPPER(FirstName) > > It should be an easy thing to provide this info. Even if there were multiple > fields involved, if they were all the same data type, Sqlite could know this > and provide the common type. E.g., > > UPPER(FirstName + LastName) Even a simple operator like '+' you cannot easily determine the return type of its expression without evaluating it - and even then it may return a FLOAT in one invocation and TEXT in a different invocation. With some static expression analysis you could infer the return type for a number of input combinations, but this would require a fair bit of new code. > If both field types were BLOB_TEXT, then that should be provided. Only > in cases > where there were multiple columns and the data types were different > would a NULL > be returned. > > This would certainly be an improvement for Sqlite, would it not? > > cheers > -brett > > Quoting Joe Wilson <[EMAIL PROTECTED]>: > > > --- [EMAIL PROTECTED] wrote: > >> I have a user-defined function named DECRYPT, which decrypts column data > >> encrypted by my other UDF named ENCRYPT. > >> > >> The UDF callback function (which does the decrypting) calls > >> sqlite3_result_blob > >> after decrypting the data. Sqlite does return the data OK...BUT! > >> It doesn't > >> provide the data type for the column when I call sqlite3_column_decltype > >> for > >> the column. The return value is NULL. I must have the column data > >> type, else > >> my code can't properly interpret the value. This must be a bug, right? > > > > As far as I know, SQLite has to way of knowing what the column type > > is in the case of a function: > > > > "If the Nth column of the result set is not a table column, > > then a NULL pointer is returned." > > > > http://www.sqlite.org/capi3ref.html#sqlite3_column_decltype > > > > You can interrogate the first row's column values with > > sqlite3_column_type as a workaround (assuming the column in subsequent > > rows are of the same type): > > > > http://www.sqlite.org/capi3ref.html#sqlite3_column_type ____________________________________________________________________________________ Boardwalk for $500? In 2007? Ha! Play Monopoly Here and Now (it's updated for today's economy) at Yahoo! Games. http://get.games.yahoo.com/proddesc?gamekey=monopolyherenow ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
