Re: prepared statement API proposal

On Dec 6, 2004, at 9:33 PM, Kevin Rosenberg wrote:

> Cyrus Harmon wrote:
>> I'd like to be able to issue queries that return binary results.
>
> I would as well. In fact, I added binary support to the MySQL backend
> recently. I added a function UFFI:CONVERT-FROM-FOREIGN-USB8 to
> handle this.

Right. And this gets used by covert-raw-field, which the stuff I'm 
using eventually calls. Thanks for adding this.

>> Clearly, one way to do this is to return everything as properly 
>> escaped
>> text and convert to binary data and stuff it in a vector. I'd like to
>
> Well, that's not always feasible -- depending upon the escape
> mechanism. New SBCL versions which won't accept arbitrary octets in a
> string, so the escaping would have to conform to a limited subset of
> characters which would have overhead to convert in and out of such a
> string.

Exactly. My point was that postgres, and I assume other dbmses, have a 
text encoding for binary data so that one can do a select and get back 
properly escaped ASCII text that represents the binary data, at the 
cost of increased size, time to decode, etc...

> For the MySQL binary support, I return a vector of (unsigned-byte 8).

ditto.

>> do better than this, however, especially for prepared statements.
>> Postgresql has an option to return the results of prepared queries as
>> binary data and I'd like to be able to use this in clsql. The question
>> is where best to put this? Currently, I switch to binary results if I
>> know I'm getting back a blob. This is nice, but if I want to avoid
>> specifying result types and just have everything be :auto, I won't 
>> know
>> the return types until after I run the query. It would be nice if this
>> all "just worked" and if I didn't specify any time info, I'd get back
>> strings for strings, numbers for numbers, arrays/vectors for blobs,
>> etc... I'd like to add a keyword parameter to run-prepared-sql to
>> retrieve rows as binary and I'd probably also need to add a keyword to
>> database-run-prepared and maybe database-query. Does this sound
>> reasonable?
>
> While inconvenient, I use :result-types with a column of :blob to
> specify returning a vector of octets. The problem with MySQL, is that
> binary data can be stored in a field declared as "CHAR(n) BINARY", but
> I haven't seen a way for the query to know that the field is declared
> to hold binary data. "SHOW COLUMNS FROM THE_TABLE_NAME" just shows the
> column type as "CHAR(n)".

Hmm... Yes, the :result-types thing works for me too, but I'm thinking 
about arbitrary tables where we don't necessarily know the columns. One 
of the odd but interesting things about the result types is that they 
are either a list of types (or :auto) or just :auto. I'm interested in 
supporting the (:auto :auto ...) and :auto cases in addition to 
explicitly saying that a column is of type :blob. I suppose one could 
use some thing like :auto-binary as a keyword for type to try to get 
the args to the right place.

> As for a keyword for run-prepared-sql to return rows as binary, that
> wouldn't work for the majority of my needs. Most of the time I'd make
> such queries, some columns would be binary but most would be strings.

In the postgres case, that's neither here nor there. I guess I'm being 
thinking too closely along the lines of the postgresql API to make 
clear what the problem is. In pgsqlv7.4 when you call the exec prepared 
statement interface, you have a choice of binary or text encoding for 
the results (note that this is for the entire result set, not an a per 
column basis, although this is changing in pgsqlv8). I've got mixed 
results of blobs, strings, ints, etc... The point is that when you call 
the exec prepared call the results come back as binary, which for 
strings and blobs is basically the same. For ints, instead of parsing 
the string, you convert the binary int to a lisp integer. I suppose in 
the pg case, one could just use the binary interface all the time, 
although I haven't tested this on more obscure types like dates and 
what not. So, in this case, even though I'm talking about a 
binary/text, the results should be pretty much the same. Which suggests 
that perhaps I should just use the binary mode and not worry about how 
one can specify which mode to use at call time, which was the whole 
point of this. Hmmm....

Thanks for helping me think this stuff through,

Cyrus