Thread: Output parameters problem

Hi there,

I've found a problem with psqlodbc and output parameters. The problem is
as follows: if I execute {CALL foobar(?,x,y,z)} where the parameter is
an output parameter, the psqlodbc will execute "SELECT foobar(,x,y,z)",
instead of "SELECT foobar(x,y,z)". Output parameters in the second or
later position are no problem, it's only the first parameter that's
treated incorrectly. This is because even though the output parameter
'?' is ignored, psqlodbc then tries to remove the comma _preceding_ the
parameter from the output. If there is no comma (as is the case with the
first parameter) this doesn't work, of course.

I've fixed it as follows, by making the following changes in convert.c:

1. Pass an optional QueryParse *qp to ResolveOneParam(), i.e.:

static int
ResolveOneParam(QueryBuild *qb, QueryParse *qp);

2. In ResolveOneParam, there is the following segment of code:

if (outputDiscard)
{
   for (npos = qb->npos - 1; npos >= 0 &&
isspace(qb->query_statement[npos]) ; npos--) ;
   if (npos >= 0 && qb->query_statement[npos] == ',')
   {
     qb->npos = npos;
     qb->query_statement[npos] = '\0';
   }
   return SQL_SUCCESS_WITH_INFO;
}


Immediately before the return statement, I added:

   else if (npos >= 0 && qb->query_statement[npos] == '(' && qp)
   {
     for (npos = qp->opos+1; isspace(qp->statement[npos]); npos++) ;
     if (qp->statement[npos] == ',')
     {
       qp->opos = npos;
     }
   }

This made things work for me, but your solution may of course be
different. This mail is just to let you know about the problem and about
a possible solution.

Cheers,
Bart

Bart Samwel wrote:
> Hi there,
>
> I've found a problem with psqlodbc and output parameters. The problem is
> as follows: if I execute {CALL foobar(?,x,y,z)} where the parameter is
> an output parameter, the psqlodbc  will execute

 > "SELECT foobar(,x,y,z)",

Oops my oversight.

> instead of "SELECT foobar(x,y,z)". Output parameters in the second or
> later position are no problem, it's only the first parameter that's
> treated incorrectly. This is because even though the output parameter
> '?' is ignored, psqlodbc then tries to remove the comma _preceding_ the
> parameter from the output. If there is no comma (as is the case with the
> first parameter) this doesn't work, of course.
>
> I've fixed it as follows, by making the following changes in convert.c:
>
> 1. Pass an optional QueryParse *qp to ResolveOneParam(), i.e.:
>
> static int
> ResolveOneParam(QueryBuild *qb, QueryParse *qp);
>
> 2. In ResolveOneParam, there is the following segment of code:
>
> if (outputDiscard)
> {
>   for (npos = qb->npos - 1; npos >= 0 &&
> isspace(qb->query_statement[npos]) ; npos--) ;
>   if (npos >= 0 && qb->query_statement[npos] == ',')
>   {
>     qb->npos = npos;
>     qb->query_statement[npos] = '\0';
>   }
>   return SQL_SUCCESS_WITH_INFO;
> }
>
>
> Immediately before the return statement, I added:
>
>   else if (npos >= 0 && qb->query_statement[npos] == '(' && qp)
>   {
>     for (npos = qp->opos+1; isspace(qp->statement[npos]); npos++) ;
>     if (qp->statement[npos] == ',')
>     {
>       qp->opos = npos;
>     }
>   }

Thanks a lot.
I would take care of it.

regards,
Hiroshi Inoue