Re: prepared statement API proposal

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.

> 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.

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

> 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)".

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.

Kevin