Обсуждение: Output parameters

Hi there,

I've got a situation with the experimental enhanced branch driver
(version 7.03.02.66), PostgreSQL 8.1 backend. I'm executing a query such as:

SELECT myproc(?,?,?,?,?,?,?)

where a number of the parameters are output parameters. I've bound
buffers to the output parameters, but when I read the values, they
contain garbage (uninitialized data, to be precise -- 0xcdcdcdcd). The
first result row then contains the output parameter values. I'd expect
the result row, as this is how PostgreSQL implements output parameters,
but does anybody have any clue why the bound buffers for the output
parameters aren't filled?

Thanks in advance for your help!

Cheers,
Bart

Bart Samwel wrote:
> Hi there,
>
> I've got a situation with the experimental enhanced branch driver
> (version 7.03.02.66), PostgreSQL 8.1 backend. I'm executing a query
> such as:
>
> SELECT myproc(?,?,?,?,?,?,?)
>
> where a number of the parameters are output parameters. I've bound
> buffers to the output parameters, but when I read the values, they
> contain garbage (uninitialized data, to be precise -- 0xcdcdcdcd).

What kind of tool are you using and how do you execute the above example
concretely ?

regards,
Hiroshi Inoue

Bart Samwel wrote:
> Hiroshi Inoue wrote:
>> Bart Samwel wrote:
>>> Hiroshi Inoue wrote:
>>>> Bart Samwel wrote:
>>>>> Hi there,
>>>>>
>>>>> I've got a situation with the experimental enhanced branch driver
>>>>> (version 7.03.02.66), PostgreSQL 8.1 backend. I'm executing a query
>>>>> such as:
>>>>>
>>>>> SELECT myproc(?,?,?,?,?,?,?)
>>>>>
>>>>> where a number of the parameters are output parameters. I've bound
>>>>> buffers to the output parameters, but when I read the values, they
>>>>> contain garbage (uninitialized data, to be precise -- 0xcdcdcdcd).
>>>>
>>>> What kind of tool are you using and how do you execute the above
>>>> example concretely ?
>>>
>>> I'm using our own ODBC front end library, freshly written from spec.
>>> The same code works like a charm on Oracle to retrieve output
>>> parameters. I could figure out the exact sequence of calls if you
>>> want, there's quite a lot of wrapper code around it but It'd probably
>>> be possible.
>>
>> Could you send me the Mylog output ?
>
> Yes, it's attached. Please read on for some extra context information. :-)
>
> Anyway, it turns out that I misinterpreted the way PostgreSQL implements
> output parameters -- apparently, they are nothing but a convenient way
> to make PostgreSQL create a complex return type for a function. In
> PL/pgSQL, however, I can't specify the output parameters in the
> parameter list, while with the ODBC driver I can.

Please use ODBC's standard syntax to execute functions or procedures.

    {[?=]call procedure-name[([?][,[?]]...)]}

For example, please execute {CALL baz(?, ?)} in your case.

regards,
Hiroshi Inoue

Hiroshi Inoue wrote:
> Bart Samwel wrote:
>> Hiroshi Inoue wrote:
>>> Bart Samwel wrote:
>>>> Hiroshi Inoue wrote:
>>>>> Bart Samwel wrote:
>>>>>> Hi there,
>>>>>>
>>>>>> I've got a situation with the experimental enhanced branch driver
>>>>>> (version 7.03.02.66), PostgreSQL 8.1 backend. I'm executing a
>>>>>> query such as:
>>>>>>
>>>>>> SELECT myproc(?,?,?,?,?,?,?)
>>>>>>
>>>>>> where a number of the parameters are output parameters. I've bound
>>>>>> buffers to the output parameters, but when I read the values, they
>>>>>> contain garbage (uninitialized data, to be precise -- 0xcdcdcdcd).
>>>>>
>>>>> What kind of tool are you using and how do you execute the above
>>>>> example concretely ?
>>>>
>>>> I'm using our own ODBC front end library, freshly written from spec.
>>>> The same code works like a charm on Oracle to retrieve output
>>>> parameters. I could figure out the exact sequence of calls if you
>>>> want, there's quite a lot of wrapper code around it but It'd
>>>> probably be possible.
>>>
>>> Could you send me the Mylog output ?
>>
>> Yes, it's attached. Please read on for some extra context information.
>> :-)
>>
>> Anyway, it turns out that I misinterpreted the way PostgreSQL
>> implements output parameters -- apparently, they are nothing but a
>> convenient way to make PostgreSQL create a complex return type for a
>> function. In PL/pgSQL, however, I can't specify the output parameters
>> in the parameter list, while with the ODBC driver I can.
>
> Please use ODBC's standard syntax to execute functions or procedures.
>
>     {[?=]call procedure-name[([?][,[?]]...)]}
>
> For example, please execute {CALL baz(?, ?)} in your case.

Ah, that did the trick! I wasn't aware of this standard syntax, we've
only recently started to use *real* ODBC instead of just using it as an
easy way of getting the same SQL into different databases through one
interface. I guess we'll have to start using more of these ODBC
constructs. Thanks very much for your explanation!

Cheers,
Bart