Re: RFC: support for named-placeholders in DBI ?

[Jim Cromie]

Jonathan Leffler wrote:

Jim Cromie wrote:

Jim Cromie wrote:

1.   some DBDs support them as column values only,
2.   most dont allow them as table-names,
3.   most require value to match entire column
      (ie colA like ?   BUT NOT  colA like 'foo?')
4.   do any allow 'where colA in ( ?foolist )   # ie ?foolist is 
(notionally) an array


For DBD::Informix, with the numbers introduced by me:
1. Yes.
2. Agreed (placeholders are not allowed as either table names or 
column names).
3. Yes.
4. No.

Doh, numbers are good..  even if they change.

I'm renumbering, hopefully in some semblance of order from simplest to 
most sophisticated.

1.  ? are emulated - by string interpolation - within the fetch-loop 
(for example)
2.  ? in WHERE clause only - typically yes.
3.   ? as column value only, not column name.
4.  ? in table-list (ex: select * from ?) - typically no.

5.  ? must represent entire column value
      EX:   where colA like 'foo%',    ->     where colA like ?     # 
not ColA like 'foo?'
                 where colA = ?
   answer is typically yes -
        and if otherwize theres a bad ambiguity with colA like '?foo'
         ie, could also be interpreted as    colA like '%foo',

6. quoting around ?
      ie   colA = ?   VS   colA = '?'
      DBI handles quoting typically, tho I think it varies a bit by 
DBD::* in use.
      Id hope that handling named placeholders wouldnt complicate matters,
       but that might be wishfull thinking

7.  DBD supports named-placeholders already
      Oracle does ':p1' etc
8.  when are SQL errors recognized
      CSV - at execute (I think)
      Oracle - at prepare by default - at execute if "ora_check_sql" == 0
9. ? can represent multi-vals - almost always NO
         EX:  where ColA in (1,2,3,?)   # is allowed, iff ? reps 1 
value, not a list.
         VS:  string interpolation, which isnt re-usable. (OK iff only 
1 value for life of $sth)
               $valstr = join ',', qw(a b c);
               prepare("select * from A where ColA in ($valstr) and 
colB = ?")
   BTW - is this what bind_param_array() is for ?

10. for SQLs that support explicit JOINS in the FROM clause
      (Access-SQL, MS-SQL(a sybase derivative ??), SQL-92 ??)
    are placeholders allowed in FROM join clauses
      EX:
            select * FROM TableA JOIN TABLEB on ColA
   ANS:
         I dont see how this would be useful, since ? is precluded for 
column or table names

FWIW,  my goal is not portable SQL per se,  thats a bigger can of worms,
and probably doesnt belong in the response to a prepare() call, for 
efficiency reasons.

Rather the goal is better tie-in to perl-code..
EX: (repeating myself for new readers)
$sth->execute( {%TableA_bindVals}, {%tableB_Bindvals}, {%default_vals});

ANYWAY - repeating the matrix, and sorry for inverting the questions..

module        1    2    3    4    5    6    7    8      9    10

DBD::CSV:     no   yes  yes  no   yes  ?    no   exe    no   no
DBD::Informix no   yes  yes  no   yes  ?    ?    prep?  ?    no
Oracle ??     no   yes  yes  no   yes  ?    :yes both   NA   ??
Different comment syntaxes:

Oracle:
    -- comment to end of line...
  /* c style comment (for PL/SQL only?) */
MySQL:
  # perl/shell style comment
   /* c-style */