[DB-SIG] :1 vs. :arg variables in SQL (oracle interface).

[Greg Stein]

Jim Fulton wrote:
> 
> Anthony Baxter wrote:
> >
> > Strangely, the thought of writing an SQL parser makes me twitch.
> 
> I don't think you actually need to write a full parser.  All you
> have to worry about is not messing with ":1" that might be buried
> in a string literal. Right?  Further, the rules for SQL string literals
> make this pretty easy to avoid.

Agreed.

> Something along the lines of (a better algorithm,
> avoiding regsub should be used):
> 
>   ssql=string.split(sql,"'")
>   for i in range(0,2,len(ssql)):
>     ssql[i]=regsub.gsub(':\([0-9]+\)',':p\\1',ssql[i])
>   sql=string.join(sql,"'")
> 
> should work fine. No?  Can you think of any Oracle SQL that
> would defeat this?

A simple parser to convert :1 syntax to ? syntax for ODBC was written as
part of the Win32 ODBC module. You may be able to adapt this to the
desired behavior. Just call it repeatedly, and it returns characters for
your resulting SQL text (original, or translated).

It also performs a bit of bookkeeping because if :1 appears twice, then
the caller has to bind one of the inputs twice (once for each resulting
'?').

Cheers,
-g


static char doParse(parseContext *ct) 
{
  ct->isParm = 0;
  if (ct->state == *ct->ptr) {
    ct->state = 0;
  }
  else if (ct->state == 0){
    if ((*ct->ptr == '\'') || (*ct->ptr == '"')) {
      ct->state = *ct->ptr;
    }
    else if (*ct->ptr == '?') {
      ct->parmIdx = ct->parmCount;
      ct->parmCount++;
      ct->isParm = 1;
    }
    else if ((*ct->ptr == ':') && !isalnum(ct->prev)) {
      const char *m = ct->ptr + 1;
      int n = 0;
      while (isdigit(*m)) {
 n *= 10;
 n += *m - '0';
 m++;
      }
      if (n) {
 ct->parmIdx = n-1;
 ct->parmCount++;
 ct->ptr = m;
 ct->isParm = 1;
 ct->prev = '0';
 return '?';
      }
    }
  }
  ct->prev = *ct->ptr;
  return *ct->ptr++;
}
--
Greg Stein (gstein@lyra.org)