Thread: UseServerSidePrepare and data-at-execution

Hi,

In preparation of changing UseServerSidePrepare=1 as the default, I
tried running the regression tests in UseServerSidePrepare=1 mode, and
got two failures. The first seems to be harmless:

---
/home/heikki/git-sandbox-pgsql/psqlodbc/test/expected/insertreturning.out    2013-06-12
13:58:32.535982856 +0300
+++
/home/heikki/git-sandbox-pgsql/psqlodbc/test/results/insertreturning.out    2013-08-15
16:28:09.892155293 +0300
@@ -1,303 +1,303 @@
  \! ./src/insertreturning-test
  connected
-# of result cols before SQLExecute: 0, after: 1
+# of result cols before SQLExecute: 1, after: 1
  Result set:
  foobar 0
-# of result cols before SQLExecute: 0, after: 1
+# of result cols before SQLExecute: 1, after: 1
  Result set:
  foobar 1

...

That basically means that with UseServerSidePrepare=0, if you call
SQLNumResultCols() on an "INSERT ... RETURNING" statement, after
SQLPrepare but before SQLExecute, it returns 0. With
UseServerSidePrepare=1, it returns 1. That seems logical - the driver
doesn't know how many cols the statement will return before at least
preparing it. In any case, the behavior with UseServerSidePrepare=1 is
better.

The second failure, however, looks like a bug:

***
/home/heikki/git-sandbox-pgsql/psqlodbc/test/expected/dataatexecution.out
  2013-06-12 13:58:32.535982856 +0300
---
/home/heikki/git-sandbox-pgsql/psqlodbc/test/results/dataatexecution.out
    2013-08-15 16:35:04.108136825 +0300
***************
*** 7,12 ****
   Fetching result sets for array bound (2 results expected)
   1: Result set:
   4
- 2: Result set:
- 5
   disconnecting
--- 7,10 ----

The test case executes a SELECT statement twice, using data-at-execution
parameters (ie. SQLParamData and SQLPutData) and array binding together.
In that combination, the server logs confirm that the query is executed
twice with different parameters, but only the first result is returned
to the client.

After some debugging, I came up with the attached patch. It clears the
curr_param_result flag from the statement object, in SQLParamData, after
executing the statement with the current set of parameters. To be
honest, I don't understand how this works; I just mimicked how
SQLExecute clears that flag. So I may have broken something.

Any better insights into this?

- Heikki

Sorry for thr late reply.

(2013/08/15 22:43), Heikki Linnakangas wrote:
> Hi,
>
> In preparation of changing UseServerSidePrepare=1 as the default, I
> tried running the regression tests in UseServerSidePrepare=1 mode, and
> got two failures. The first seems to be harmless:
>
> ---
> /home/heikki/git-sandbox-pgsql/psqlodbc/test/expected/insertreturning.out
> 2013-06-12 13:58:32.535982856 +0300
> +++
> /home/heikki/git-sandbox-pgsql/psqlodbc/test/results/insertreturning.out
> 2013-08-15 16:28:09.892155293 +0300
> @@ -1,303 +1,303 @@
>   \! ./src/insertreturning-test
>   connected
> -# of result cols before SQLExecute: 0, after: 1
> +# of result cols before SQLExecute: 1, after: 1
>   Result set:
>   foobar 0
> -# of result cols before SQLExecute: 0, after: 1
> +# of result cols before SQLExecute: 1, after: 1
>   Result set:
>   foobar 1
>
> ...
>
> That basically means that with UseServerSidePrepare=0, if you call
> SQLNumResultCols() on an "INSERT ... RETURNING" statement, after
> SQLPrepare but before SQLExecute, it returns 0. With
> UseServerSidePrepare=1, it returns 1. That seems logical - the driver
> doesn't know how many cols the statement will return before at least
> preparing it. In any case, the behavior with UseServerSidePrepare=1 is
> better.
>
> The second failure, however, looks like a bug:
>
> ***
> /home/heikki/git-sandbox-pgsql/psqlodbc/test/expected/dataatexecution.out  2013-06-12
> 13:58:32.535982856 +0300
> ---
> /home/heikki/git-sandbox-pgsql/psqlodbc/test/results/dataatexecution.out
>     2013-08-15 16:35:04.108136825 +0300
> ***************
> *** 7,12 ****
>    Fetching result sets for array bound (2 results expected)
>    1: Result set:
>    4
> - 2: Result set:
> - 5
>    disconnecting
> --- 7,10 ----
>
> The test case executes a SELECT statement twice, using data-at-execution
> parameters (ie. SQLParamData and SQLPutData) and array binding together.
> In that combination, the server logs confirm that the query is executed
> twice with different parameters, but only the first result is returned
> to the client.
>
> After some debugging, I came up with the attached patch. It clears the
> curr_param_result flag from the statement object, in SQLParamData, after
> executing the statement with the current set of parameters.

Oops I overlooked this case.
Yes the curr_param_result flag must be cleared before excecuting
the subsequent set of parameters.

regards,
Hirshi Inoue

On 22.08.2013 02:50, Inoue, Hiroshi wrote:
> (2013/08/15 22:43), Heikki Linnakangas wrote:
>> The test case executes a SELECT statement twice, using data-at-execution
>> parameters (ie. SQLParamData and SQLPutData) and array binding together.
>> In that combination, the server logs confirm that the query is executed
>> twice with different parameters, but only the first result is returned
>> to the client.
>>
>> After some debugging, I came up with the attached patch. It clears the
>> curr_param_result flag from the statement object, in SQLParamData, after
>> executing the statement with the current set of parameters.
>
> Oops I overlooked this case.
> Yes the curr_param_result flag must be cleared before excecuting
> the subsequent set of parameters.

Ok then, I committed the patch. Thanks!

- Heikki