Andy Ross <[EMAIL PROTECTED]> wrote:
>
> > SQLite takes the approach of always making every BLOB larger than
> > every TEXT string. That is simple and unambiguous.
>
> ....but it's a disaster for people like me trying to actually take
> advantage of the manifest typing feature. I have an (IMHO really
> pleasing) API that looks like this:
>
> sqlite.exec(db, "select * from X where Y = ?", ...bind params...)
>
> This obviously presumes that types are convertable at runtime, which
> in SQLite they *mostly* are; I can pass in the string "12" and get a
> valid comparison to an integer or real value, etc... But I *can't*
> know when binding the parameter whether the context in which it will
> be used is a blob or a string. So what I do is use bind_blob()
> universally for all string objects.
>
Have you looked at the TCL bindings? The above would be
db eval {select * from X where Y=$parameter} {... code here ...}
Notice that the variable to be bound is specify by name directly
in the SQL. So if you are binding multiple parameters (as I
often do) there is no danger of miscounting the number of "?"
and getting the bind parameters wrong on the end. And when
reading, you do not have to move your eye out to the "bind parameters"
section to figure out what the "?" means.
At runtime, SQLite determines the internal data representation
of the $parameter variable, then uses bind_text, bind_blob,
bind_int, or bind_whatever as appropriate. And this works very,
very well in practice.
> I was led to believe by your
> documentation on manifest typing and by analogy to your automatic
> numeric conversions that this sort of conversion was legal.
I am sorry that the documentation misled you. I'll work on
improving it.
>
> Seriously: what's wrong with just (1) converting a string to a blob by
> exposing the literal byte in whatever encoding it was stored in, and
> (2) converting a blob to a string by interpreting the bytes literally
> in the current "pragma encoding" environment? Sure, the user can
> shoot herself in the foot with that, but it works unambiguously in the
> only sane case: where the user-side string environment and the
> database schema are written to use the same encoding.
>
Well, I suppose we might have made that choice when we were
defining the interface for SQLite version 3 - if you had
brought it up then. But we did not. And the interface is
now frozen is not going to change regardless of whether or
not your system is better than the one that is implemented.
SQLite takes backwards compatibility very seriously, and
so we are not going to make a change of this magnitude
without a very good reason.
You might not think SQLite's backwards compatibility
pledge is a good thing now, but if you continue using
SQLite then someday you will likely thank me.
> Do you have any other suggestions, or is the clean/simple API choice
> above just not something you want to suppot with SQLite? It seems
> like many other language bindings are going to have the same issue...
I would rather support the thousands of applications that
I know are already using SQLite successfully than break
all those other application in order to support a single
language that I have never heard of before. You are the
first person to complain about this in 2.5 years since
the beginning of version 3 and I am aware of at least 2
dozen other language bindings that already exist and are
fully functional, so I really do not think it will become
a widespread problem.
> I think what I'll have to do in the interrim is default to bind_text()
> instead of bind_blob(). Does that work if the data has embedded nuls?
I believe you can bind_text with embedded '\000' characters,
as long as you explicitly specify the length of the text, of
course. This is not something that is covered in the regression
tests, that I recall, so you might run into problems. But if
you do, I would consider them bugs and will fix them.
--
D. Richard Hipp <[EMAIL PROTECTED]>
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
