Re: affected rows after insert

2015-10-19 13:35 GMT+01:00 David Nichols <[email protected]>:
>
>> On Oct 19, 2015, at 1:53 PM, Frediano Ziglio <[email protected]> wrote:
>>
>> 2015-10-19 11:16 GMT+01:00 David Nichols <[email protected]>:
>>>
>>>> On Oct 18, 2015, at 10:06 AM, Frediano Ziglio <[email protected]> wrote:
>>>>
>>>> 2015-10-16 12:23 GMT+01:00 David Nichols <[email protected]>:
>>>>>> 2015-06-09 14:48 GMT+01:00 Ondrej Holecek <ondrej.holecek at qoretechnologies.com <http://lists.ibiblio.org/mailman/listinfo/freetds>>:
>>>>>>> Hi,
>>>>>>>
>>>>>>> I made a test with the same code but sybase lib and it works like
>>>>>>> expected. It seems there is a bug in FreeTDS. Could you please check
>>>>>>> my patch?
>>>>>>>
>>>>>>> thx,
>>>>>>>
>>>>>>> Ondrej
>>>>>>>
>>>>>>
>>>>>> Actually works and ctlib tests seems to pass.
>>>>>>
>>>>>> However DONEINPROC is returned in a lot of cases, for instance if
>>>>>> database execute some trigger on the table so your patch can catch
>>>>>> some strange results too.
>>>>>>
>>>>>> Which database version are you using?
>>>>>>
>>>>>> Frediano
>>>>> Hi,
>>>>>
>>>>> Ondra has not been working on this for quite some time, but I’d like to pick up this issue again please.
>>>>>
>>>>> We are using MS SQL Server 2008 - using tds version = 7.2  - with the patch we are able to get the affected rows reliably - without it, we are not.
>>>>>
>>>>> Using the same patched library build with a Sybase (SAP) ASE 16.0 DB, it also gives correct results (with tds version = 5.0)
>>>>>
>>>>> I would be happy to continue helping with this issue if there’s a chance it could be included in freetds (or some other equally effective fix).
>>>>>
>>>>> thanks,
>>>>> David
>>>>>
>>>>
>>>> A fix was written for this case. Do you still have some code that
>>>> fails? Did you update FreeTDS?
>>>>
>>>> Frediano
>>>
>>> yes, I tried it with freetds from freetds-0.95.12 and git master - both failed my tests and with the patch as provded by Ondra the tests then passed.  Sorry I did not mention this in the original email.
>>>
>>
>> Please try a more recent version (like 0.95.21). Also you should sent
>> some extract of your tests. Can you post an updated patch?
>>
> sorry it was a typo on my side - it was 0.95.21 that I tried.  Anyway since it failed in git master then I would assume that would be more relevant than the last stable release.
>
> Attached please find a patch to git master sources that makes my tests work.  Please note that we are using ctlib from freetds to build a DBI driver for the Qore programming language to talk to SQL Server and Sybase DBs; the same source builds with both the sybae libs and ctlib from freetds.  Currently my tests are written in the Qore programming language, so there are quite a few layers between the Qore code that’s executed and freetds’s ctlib.
>
> When this simple test works (ie only with a patch ctlib); the output looks like this:
>
> dnichols@manatee:~/src/qore/git/module-sybase/src$ simple-rows-affected-test.q
> "Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64)
>         Jul  9 2008 14:17:44
>         Copyright (c) 1988-2008 Microsoft Corporation
>         Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)"
> insert: 1
> n delete: 0
> sel: {id: [1]}
> delete: 1
> insert: 1
> delete: 1
>
> When it doesn’t work (ie with an unpatched ctlib), the output looks like this:
> dnichols@manatee:~/src/qore/git/module-sybase/src$ simple-rows-affected-test.q
> "Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64)
>         Jul  9 2008 14:17:44
>         Copyright (c) 1988-2008 Microsoft Corporation
>         Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)"
> insert: 0
> n delete: 0
> sel: {id: [1]}
> delete: 0
> insert: -1
> delete: -1
>
> The Qore source to this simple test is attached for your reference.
>

I applied the patch and improved a test to catch this issue. I changed
the patch slightly to check for TDS_NO_COUNT instead of <0 and
returning CS_CMD_SUCCEEDED as ct_res_info documentation

Frediano

>>> BTW I had to comment out the reference to AM_ICONV in configure.ac to get freetds to build from git master on Linux
>>>
>> Quite odd. Could be you miss some package on your system. Which error
>> did you get? Could be you have an old autoconf package.
>
> I compiled it on fedora 22 with autocont 2.69.  However I just did a search and found that AM_ICONV is installed with the gettext-devel package, which I’ve now installed, and now freetds from git master can be built without any hacks to configure.ac on Fedora.
>
> thx
> David
>
>
>
>
>> Frediano
>>
>>> thx
>>> David
>>>
>>>
>>>>>>> On Tue, Jun 2, 2015 at 10:49 AM, Ondrej Holecek
>>>>>>> <ondrej.holecek at qoretechnologies.com <http://lists.ibiblio.org/mailman/listinfo/freetds>> wrote:
>>>>>>>> Hi,
>>>>>>>>
>>>>>>>> I have a problem obtaining number of affected rows while inserting.
>>>>>>>> The problem occurs when I pass the values by ct_param() call.
>>>>>>>> So, this command order works well:
>>>>>>>>
>>>>>>>> ct_cmd_alloc()
>>>>>>>> ct_command(CS_LANG_CMD, "INSERT INTO test (number)  VALUES(5)")
>>>>>>>> ct_send()
>>>>>>>> ct_result() // the result type is CS_CMD_DONE so I can call...
>>>>>>>> ct_res_info(CS_ROW_COUNT) // as expected 1 row affected
>>>>>>>>
>>>>>>>>
>>>>>>>> but when I call it like this, It skips CS_CMD_DONE, so I can't read
>>>>>>>> the affected rows number.
>>>>>>>>
>>>>>>>> ct_cmd_alloc()
>>>>>>>> ct_command(CS_LANG_CMD, "INSERT INTO test (number)  VALUES(@par1)")
>>>>>>>> ct_param("@par1", 1)
>>>>>>>> ct_send()
>>>>>>>> ct_result() // the result type is CS_END_RESULT
>>>>>>>> // can't do anything here :-(
>>>>>>>>
>>>>>>>>
>>>>>>>> The row is inserted in both cases correctly just in the second case I
>>>>>>>> can't read the number of affected rows.
>>>>>>>>
>>>>>>>> I did some ctlib debugging and I created the patch which would fix
>>>>>>>> this behavior. However, I just changed the code blindly (based on the
>>>>>>>> debugging) not knowing what is the real purpose of the case.
>>>>>>>>
>>>>>>>>
>>>>>>>> in ct_result() function I changed the case _CS_RES_INIT:
>>>>>>>>
>>>>>>>>               case _CS_RES_INIT:   /* commalindlnd had no result set */
>>>>>>>>                   if (tds->rows_affected > 0) {
>>>>>>>>                       *result_type = CS_CMD_DONE;
>>>>>>>>                       cmd->results_state = _CS_RES_INIT;
>>>>>>>>                       return CS_SUCCEED;
>>>>>>>>                   }
>>>>>>>>                   break;
>>>>>>>>
>>>>>>>> now it seems with the patch it works correctly. But I have no idea if
>>>>>>>> it is really the correct way.
>>>>>>>>
>>>>>>>> Any ideas?
>>>>>>>>
>>>>>>>> Thanks,
>>>>>>>>
>>>>>>>> Ondrej
>>>>>
>>>>>
>>>>> _______________________________________________
>>>>> FreeTDS mailing list
>>>>> [email protected]
>>>>> http://lists.ibiblio.org/mailman/listinfo/freetds
>>>> _______________________________________________
>>>> FreeTDS mailing list
>>>> [email protected]
>>>> http://lists.ibiblio.org/mailman/listinfo/freetds
>>>
>>> _______________________________________________
>>> FreeTDS mailing list
>>> [email protected]
>>> http://lists.ibiblio.org/mailman/listinfo/freetds
>> _______________________________________________
>> FreeTDS mailing list
>> [email protected]
>> http://lists.ibiblio.org/mailman/listinfo/freetds
>
>
> _______________________________________________
> FreeTDS mailing list
> [email protected]
> http://lists.ibiblio.org/mailman/listinfo/freetds
>
_______________________________________________
FreeTDS mailing list
[email protected]
http://lists.ibiblio.org/mailman/listinfo/freetds