From: Aaron J Mackey [mailto:[EMAIL PROTECTED]] > > No real code yet, just some example usage; I'm "trolling" for > feedback and concerns before I really get into it:
Check out DBD::AnyData by Jeff Zucker http://www.vpservices.com/jeff/programs/AnyData/ He has written a partial SQL grammar and an assortment of clever glue which allows SQL-like access to data in many formats. You might consider supporting SQL-99 style user defined functions: __SQL__ CREATE FUNCTION ExtractNamePart(@InName varchar(100), @part tinyint) RETURNS varchar(30) BEGIN DECLARE @offset tinyint SET @offset = charindex('', @InName) IF @part = 1 RETURN substring(@InName, 1, @offset-1) ELSE if @part = 2 RETURN substring(@InName, @offset+1, len(@InName)) RETURN NULL END > #!/usr/bin/perl -Tw > > use Inline; > > my ($host, $user, $pw, $db) = qw(localhost me youwish mydb); > > Inline->bind(SQL => DATA, > DBD => 'mysql', > HOST => $host, > USER => $user, > PW => $pw, > DB => $db); > > my $id = get_user_id('phooey'); > set_user_name($id, 'phoooey'); > > my @users = get_users(); > > __DATA__ > /* get_user_id($name) */ > select id > from users > where name like $name > > /* set_user_name($id, $name) */ > update users > set name = $name > where id = $id > > /* get_users() */ > select id, name > from users > order by name asc > > > To flesh out the ideas, here are some of my own comments: > > 1. We need to specify connection metadata (DBD driver to use, and > connection parameters: we can specify those at import time > (use Inline SQL > => DATA, HOST => 'myhost', etc), or at run time via > Inline->bind(), or in > a metadata section of the __DATA__ stream (not sure I like > that so much). > > 2. Inline::SQL would parse the __DATA__ stream looking for function > prototypes between /* */ C comments (suggestions for other > syntax/delimiters welcome), followed by sql statements that > make use of > the variable names (i.e. none of this ?, ?, ? stuff); I think > this yields > more readable Perl code [rather than $sth->prepare($name, > $id), we have > set_name($id, $name) ]. > > 3. Inline::SQL would use DBI methods to handle the requests (with > prepare_cached and whatnot on transmogrified SQL statements > containing appropriate ? placeholders). > > 4. We'd use wantarray() to figure out whether we need to > return an array > of rows from a select statement, or whether to return a single row. > > Drawbacks of Inline::SQL : > > 1. Don't see a clean way to do the usual prepare(), > while(fetch()) looping > you'd normally do with DBI; Inline::SQL functions will always > return all > of the results (potentially very large sets). > > Others I'm sure; only just beginning to think about it. > > Feedback very welcome, as always. > > -Aaron >
