Hello,
2 remarks on this proposal:
First, do not forget stored procedures. We store most of our queries in
stored procedures.
This allows us to use the same (optimized) queries in perl, java and visual
basic. I don't think
that it is hard to do, but you have to keep it in mind.
Secondly, the way we implemented reading multiple results from a query is to
return a tied
handle from which records are read. A close obviously corresponds to a
finsh(). BTW, the
same happens when a stored procedure returns a cursor.
Just my 0.02 EUR
Regards
Paul
----- Original Message -----
From: "Aaron J Mackey" <[EMAIL PROTECTED]>
To: "Tim Bunce" <[EMAIL PROTECTED]>
Cc: "Dean Arnold" <[EMAIL PROTECTED]>; "DBI-users" <[EMAIL PROTECTED]>;
<[EMAIL PROTECTED]>
Sent: Friday, March 01, 2002 14:53
Subject: Re: Fw: Inline::SQL
>
> There has been quite a bit of followup to my first posted ramblings; I've
> recieved lots of ideas, most of them very good. Fundamentally, though,
> there's the idea that this isn't really an "Inline" package, as in
> "(pre)compile this bit of native language in some other language-space and
> then make it available to my perl script". It's more of a "automatically
> build perl subroutines for me that make use of DBI". So maybe it's more
> of a DBIx::Inline::SQL or somesuch.
>
> Ignore the bits about using C-like comments for subroutine definitions;
> that was a crackpot idea. I've had many other suggestions for syntax,
> like:
>
> SCALAR get_name ($id) { # or maybe even $get_name($id);
> SELECT name FROM users WHERE id = $id;
> }
>
> VOID set_name ($id, $name) { # or: set_name($id)
> UPDATE users SET name = $name WHERE id = $id;
> }
>
> LIST get_users () { # or: @get_users()
> SELECT name FROM users ORDER BY id;
> }
>
> etc.
>
> Thanks in advance for any commentary or advice.
>
> -Aaron
>
> On Fri, 1 Mar 2002, Tim Bunce wrote:
>
> > On Thu, Feb 28, 2002 at 10:14:08PM -0800, Dean Arnold wrote:
> > > Just stumbled on this in c.l.p.modules, thought it deserves to be
> > > bounced around here...IMHO, this would be *very* gnarly!
> >
> > Bounced around on dbi-users, that is, not dbi-dev.
> > Also, I've CC'd the author.
> >
> > Tim.
> >
> > > ----- Original Message -----
> > > From: "Aaron J Mackey" <[EMAIL PROTECTED]>
> > > Newsgroups: comp.lang.perl.modules
> > > To: <[EMAIL PROTECTED]>
> > > Sent: Tuesday, February 26, 2002 7:54 AM
> > > Subject: RFC: Inline::SQL
> > >
> > >
> > > >
> > > > No real code yet, just some example usage; I'm "trolling" for
feedback and
> > > > concerns before I really get into it:
> > > >
> > > > #!/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
> > > >
> > > >
> > > >
> > > >
> > >
> >
>
> --
> Aaron J Mackey
> Pearson Laboratory
> University of Virginia
> (434) 924-2821
> [EMAIL PROTECTED]
>
>
>
