[python-sybase] Re: Your test program (sp_test.py) output

[Dave C. <dj...@ob...>]

I keep forgetting to send to the mailing list :-(.

Bradley Feldman wrote:
> Dave:
> 
> If I run your test program (slightly modded for my environment) on
> FreeTDS/MS SQL, here's what I get as output:
> 
> {'@num': 12345, '@result': <DataBufType object at 0x2a95596770>}
> [54321, <DataBufType object at 0x2a95596940>]
> 
> Attached is the debug trace.  Let me know what you discover.

Extremely odd.

The parts of the log to do with calling the stored procedure are:

Cursor.callproc
ct_cmd_alloc(conn0, &cmd) -> CS_SUCCEED, cmd3

Allocated a command structure to send a command to the server.

ct_command(cmd3, CS_RPC_CMD, "sp_test_output", CS_NULLTERM, CS_UNUSED)
-> CS_SUCCEED

Start constructing the command; use an RPC command telling the server to
  execute a stored procedure.

ct_param(cmd3, &databuf0->fmt=[name:"@result" type:CS_INT_TYPE
status:CS_RETURN format:CS_FMT_UNUSED count:1 maxlength:4 scale:0
precision:0], databuf0->buff, 4, 0) -> CS_SUCCEED

Tell the command that we want to bind a buffer to receive a return
parameter (status == CS_RETURN) called @result.

ct_param(cmd3, &databuf1->fmt=[name:"@num" type:CS_INT_TYPE
status:CS_INPUTVALUE format:CS_FMT_UNUSED count:1 maxlength:4 scale:0
precision:0], databuf1->buff, 4, 0) -> CS_SUCCEED

Tell the command that we have an input parameter called '@num'.  The
value is already in the associated buffer.

ct_send(cmd3) -> CS_SUCCEED

Send the command to the server.

ct_results(cmd3, &result) -> CS_SUCCEED, CS_STATUS_RESULT

Server says that there is some status returned.  We fetch this and
discard it.

ct_res_info(cmd3, CS_NUMDATA, &value, CS_UNUSED, NULL) -> CS_SUCCEED, 1
ct_describe(cmd3, 1, &fmt) -> CS_SUCCEED, datafmt0=[name:""
type:CS_INT_TYPE status:CS_FALSE format:CS_FMT_UNUSED count:1
maxlength:4 scale:0 precision:0]
ct_bind(cmd3, 1, &datafmt0->fmt=[name:"" type:CS_INT_TYPE
status:CS_FALSE format:CS_FMT_UNUSED count:1 maxlength:4 scale:0
precision:0], databuf2->buff,
databuf2->copied, databuf2->indicator) -> CS_SUCCEED, databuf2
_fetch_rows
ct_fetch(cmd3, CS_UNUSED, CS_UNUSED, CS_UNUSED, &rows_read) -> CS_SUCCEED, 1
_fetch_rows
ct_fetch(cmd3, CS_UNUSED, CS_UNUSED, CS_UNUSED, &rows_read) ->
CS_END_DATA, 0

Status result has been fetched.  The next logs are where we get the
parameter results.

ct_results(cmd3, &result) -> CS_SUCCEED, CS_PARAM_RESULT

Server tells us to begin fetching the parameter result set.

ct_res_info(cmd3, CS_NUMDATA, &value, CS_UNUSED, NULL) -> CS_SUCCEED, 1

There is one return parameter.

ct_describe(cmd3, 1, &fmt) -> CS_SUCCEED, datafmt1=[name:"@result"
type:CS_INT_TYPE status:CS_FALSE format:CS_FMT_UNUSED count:1
maxlength:4 scale:0 precision:0]

It is called '@result' (which is what we expected).  The *really*
strange thing here is that the status is not CS_RETURN!!!  When I run it
here the status *is* CS_RETURN.  The upshot of this is that the result
is ignored by the code in Sybase.py

I think that a fix for this that should work all of the time is to
change _FetchNowParams._param_result():

     def _param_result(self):
         bufs = _row_bind(self._cmd, 1)
         while 1:
             status, rows_read = self._cmd.ct_fetch()
             if status == CS_SUCCEED:
                 pass
             elif status == CS_END_DATA:
                 break
             elif status in (CS_ROW_FAIL, CS_FAIL, CS_CANCELED):
                 self._raise_error(Error, 'ct_fetch')
             pos = -1
             for buf in bufs:
                 if buf.status & CS_RETURN:
                     if type(self._params) is type({}):
                         self._params[buf.name] = _column_value(buf[0])
                     else:
                         while 1:
                             pos += 1
                             param = self._params[pos]
                             if (type(param) is DataBufType
                                 and param.status & CS_RETURN):
                                 break
                         self._params[pos] = _column_value(buf[0])

If you just remove the test for buf.status & CS_RETURN it should do the
right thing.

     def _param_result(self):
         bufs = _row_bind(self._cmd, 1)
         while 1:
             status, rows_read = self._cmd.ct_fetch()
             if status == CS_SUCCEED:
                 pass
             elif status == CS_END_DATA:
                 break
             elif status in (CS_ROW_FAIL, CS_FAIL, CS_CANCELED):
                 self._raise_error(Error, 'ct_fetch')
             pos = -1
             for buf in bufs:
                 if type(self._params) is type({}):
                     self._params[buf.name] = _column_value(buf[0])
                 else:
                     while 1:
                         pos += 1
                         param = self._params[pos]
                         if (type(param) is DataBufType
                             and param.status & CS_RETURN):
                             break
                     self._params[pos] = _column_value(buf[0])

Let me know how it goes.

- Dave

-- 
http://www.object-craft.com.au