Skip to Content.
Sympa Menu

freetds - Re: [freetds] affected rows after insert

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: David Nichols <david AT qore.org>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] affected rows after insert
  • Date: Tue, 20 Oct 2015 07:45:20 +0200

On Oct 19, 2015, at 10:20 PM, Frediano Ziglio <freddy77 AT gmail.com> wrote:
>
> 2015-10-19 13:35 GMT+01:00 David Nichols <david AT qore.org>:
>>
>>> On Oct 19, 2015, at 1:53 PM, Frediano Ziglio <freddy77 AT gmail.com> wrote:
>>>
>>> 2015-10-19 11:16 GMT+01:00 David Nichols <david AT qore.org>:
>>>>
>>>>> On Oct 18, 2015, at 10:06 AM, Frediano Ziglio <freddy77 AT gmail.com>
>>>>> wrote:
>>>>>
>>>>> 2015-10-16 12:23 GMT+01:00 David Nichols <david AT qore.org>:
>>>>>>> 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

My test fails with your patch; if I change it back to:
case _CS_RES_INIT: /* command had no result
set */
if (tds->rows_affected !=
TDS_NO_COUNT) {
*result_type = CS_CMD_DONE;
cmd->results_state =
_CS_RES_INIT;
return CS_SUCCEED;
}
break;

then it succeeds - please note that his is a hack from my side, I’m not
saying that the original approach is correct, just that my test fails unless
I set CS_CMD_DONE

thx
David


> 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
>>>>>> FreeTDS AT lists.ibiblio.org
>>>>>> http://lists.ibiblio.org/mailman/listinfo/freetds
>>>>> _______________________________________________
>>>>> FreeTDS mailing list
>>>>> FreeTDS AT lists.ibiblio.org
>>>>> http://lists.ibiblio.org/mailman/listinfo/freetds
>>>>
>>>> _______________________________________________
>>>> FreeTDS mailing list
>>>> FreeTDS AT lists.ibiblio.org
>>>> http://lists.ibiblio.org/mailman/listinfo/freetds
>>> _______________________________________________
>>> FreeTDS mailing list
>>> FreeTDS AT lists.ibiblio.org
>>> http://lists.ibiblio.org/mailman/listinfo/freetds
>>
>>
>> _______________________________________________
>> FreeTDS mailing list
>> FreeTDS AT lists.ibiblio.org
>> http://lists.ibiblio.org/mailman/listinfo/freetds
>>
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds





Archive powered by MHonArc 2.6.24.

Top of Page