Re: SQL::Interpolate - request for comments

From: David Manura (dm.list_at_math2.org)
Date: 01/01/04 

Date: Wed, 31 Dec 2003 23:04:36 -0500
To: Terrence Brannon <metaperl@urth.org>

Terrence Brannon wrote:

> isnt that a long word to type every time? I envision subroutines with
> an interpolating property:
>
> sub get_authors : dbi_interp {
>
> my ($author_list) = @_;
>
> qq[SELECT * FROM AUTHORS WHERE author_id IN ], $author_list ;
>
> }
>
> my $sql = get_authors (10 .. 12);
>
> but this is just brainstorming

Yes, I shall shorten the name. I'm not certain if you mean something
similar to what I'm thinking of when using the "get_authors :
dbi_interp" syntax, but I am considering adding support for
Filter::Simple to simplify the syntax even further as such:

use SQL::Interpolate FILTER => 1;
...
my $rows = $dbh->selectall_arrayref(dbi_interp qq[
     SELECT * FROM table
     WHERE color IN {@colors}
           AND y = {$x}
     LIMIT {[1, 10] }
]);

in which case the source filter will internally translate the string
into the parameters list as before rather than letting Perl perform its
standard string interpolation.

>> except that SQL::Interpolate still exposes and utilizes the full
>> native SQL syntax of your database.
>
> this means you must write custom SQL for each database the code is to
> work on... for example LIMIT is handled very differently by Postgres
> and MySQL... it might be nice if LIMIT
> were a subroutine which were translated to the proper syntax... in
> fact I have just started on a module which generates SQL snippets
> based on the DBD of the $dbh.

True, but if SQL::Interpolate were told during module initialialization
(or object construction if made OO) which database it is to generate SQL
for, it would be able to generate the appropriate SQL and translated
binding parameters assuming that the reference [1, 10] is always
interpreted in a database-independent manner.

An alternate approach would be to use something like this:

sql_interp(q[
   SELECT * FROM mytable], &limit($a, $b)
);

in which case the "limit" function (implemented in SQL::Interpolate or
in third-party another module) would return an arrayref as such:

 bless ["LIMIT ", \$b, "OFFSET", \$a], 'SQL::Interpolate::FlattenArray'

dependent on the database type. Before processing, sql_interp will
flatten the contained array elements into its argument list so that the
return value of sql_interp will be

  ("SELECT * FROM mytable LIMIT ? OFFSET ?", $b, $a)

The question then is to what extent SQL::Interpolate should concern
itself with database independence. Simple things like handling how
variables are interpolated between surrounding SQL are the
responsibility of SQL::Interpolate and are easily done. But if an
application must support multiple databases using drastically different
SQL structures (e.g. different built-in functions or choice of joins or
sub-selects), then SQL might not be appropriate, in which case
SQL::Interpolate is not appropriate either. Modules that do aim for
such database independence might internally use SQL::Interpolate for
native database access, however. Admittedly, the code I write tends to
be targeted to a single database or to two databases restricted to
fairly compatible SQL, so database independence is not something I've
looked at much.

> I find it interesting that you require scalars to be passed by
> reference (e.g., \$x) , but this creates consistency.

The interpolation function could have instead understood even-numbered
arguments to be variables to interpolate and odd-numbers arguments as
the SQL:

  sq_interpolate("SELECT * FROM mytable WHERE x=", $x)

but I think its more extensible to have the function unambigously
distinguish arguments by whether each is a reference. This way, the
'limit' function shown above will work as used, and one could even pass
in "manipulators" similar to that of C++'s iostream if the need arrises:

sql_interp(qq[
    SELECT * FROM mytable WHERE height = ], interp_number(), \$y
])

best regards,
-davidm

Relevant Pages

  • Re: Drop all the connections to the DataBase
    ... SQL 7.0 doesn't support the "with rollback ... immediate" syntax. ... the BACKUP DATABASE syntax and not worry about putting it ... >> limitation by killing all the active connections. ...
    (microsoft.public.sqlserver.server)
  • Re: Troubleshooting Append Query
    ... I'm really learning database design from the ground up with this first ... Well, you can edit the SQL in the SQL View pane, then run the query to give ... You never have to deal with the QBE Design Grid unless you want to use your ... Is it just a matter of me manually placing the syntax from the SQL view? ...
    (microsoft.public.access.queries)
  • Re: DataSet transfer data from one database to another
    ... "Incorrect syntax near the keyword "in". ... OleDBConnection1 is a connection to my local SQL Server database. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: create database: programmatically vs app-tools
    ... Once you've typed the syntax once ... ... just modify for the new database, ... > It took a few hours of typing for me to create the database and all its ... > programmatically use SQL? ...
    (microsoft.public.dotnet.languages.csharp)
  • Fixed
    ... I also found this article that gives the proper way to move system dbs in sql 2008: ... The model exists where the master states it exists. ... This is the error log prior to detaching the model database. ...
    (microsoft.public.sqlserver.server)