[PD] Question about SQL Placeholder Implementation

[Hans-Christoph Steiner]

On Dec 21, 2007, at 1:36 PM, Mike McGonagle wrote:

>
>
> On 12/21/07, Mathieu Bouchard <matju at artengine.ca> wrote:
> On Tue, 18 Dec 2007, Mike McGonagle wrote:
>
> > [query <srv_id> insert into mytable (id, name, token) values (? 
> f,'?s','?s')]
> > In this example, the first placeholder would expect a Float, with  
> the next
> > two expecting a Symbol (just think of this as a variation on the  
> printf
> > string substitutions).
>
> It looks more like the types of [t] [pack] [unpack] [expr] etc.
>
>
> Yes, they are PD types.
>
>
> You don't really need to type your data. SQL is largely typeless:  
> at least
> at the level of what you can put in one table cell, anything is a  
> string.
> It becomes typed (but auto-cast) when put in the cell, but rebecomes
> untyped when taken out of the cell.
>
>
> Yes, I know that these things can be stored in a typeless manner,  
> but the functions that bind the SQL statement with their  
> placeholders can be of any type, as there are functions for the  
> various datatypes.
>
> http://www.sqlite.org/c3ref/bind_blob.html
>
> This link shows all the different functions used to bind the data  
> to the statement placeholders.
>
> http://dev.mysql.com/doc/refman/5.0/en/c-api-prepared-statement- 
> datatypes.html
>
> And if you look at how MySQL does this, while they only provide a  
> single bind function, it does have the datatypes for the data in a  
> structure that gets passed into the bind function.
>
> Also, from looking at libdbi, it appears that they don't even  
> bother with placeholders at all.

I don't know whether Perl DBI is the same as libdbi, but Perl DBI  
definitely uses placeholders:

http://www.stupidfool.org/perl/docs/perltut/dbi/dbiplace.html

> This is not an issue of how the user sees these things, it is how  
> the C code interfaces with the database.
>
> So, just how do we do this in a consistent manner when all the  
> different interfaces don't support these things, AND if they do,  
> they do them differently.

We just need a clearly defined standard, then each object will handle  
the translation. We can look at code like Perl:DBI for an examples of  
how thing could be done.  That might even be in C, so we could use  
that code.


> > but considering that the databases need to know the specific data  
> types
> > for each placeholder,
>
> heh? do they?
>
>
> SQLite does. MySQL appears to as well. And yes, it is possible to  
> just force everything to be a string/c-string and be done with it.  
> SQLite actually stores everything as a string anyway, despite the  
> defined types in the create statement.

We can get the type info from the Pd type system if we need it.  That  
will give us symbols and floats.

.hc

>
> Mike
>
>
>   _ _ __ ___ _____ ________ _____________ _____________________ ...
> | Mathieu Bouchard - tél:+1.514.383.3801, Montréal QC Canada
>
>
>
> -- 
> Peace may sound simple—one beautiful word— but it requires  
> everything we have, every quality, every strength, every dream,  
> every high ideal.
> —Yehudi Menuhin (1916–1999), musician
> _______________________________________________
> PD-list at iem.at mailing list
> UNSUBSCRIBE and account-management -> http://lists.puredata.info/ 
> listinfo/pd-list



------------------------------------------------------------------------ 
----

Using ReBirth is like trying to play an 808 with a long stick.    - 
David Zicarelli


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.puredata.info/pipermail/pd-list/attachments/20071221/36296d95/attachment.htm>