[freetds] affected rows after insert

David Nichols david at qore.org
Tue Nov 3 06:56:50 EST 2015


On Oct 20, 2015, at 11:17 AM, David Nichols <david at qore.org> wrote:
> 
>> 
>> On Oct 20, 2015, at 9:12 AM, Frediano Ziglio <freddy77 at gmail.com> wrote:
>> 
>> Il 20/Ott/2015 09:09, "David Nichols" <david at qore.org> ha scritto:
>>> 
>>> 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 at 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 at 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
>>> 
>> 
>> Cs_cmd_succeeded is a valid value and is expected to have rows number
>> attached so upper layer should handle it
> 
> OK, I see what you mean, however please note that with ctlib from Sybase/SAP ASE 16, I get the rows updated correctly with the same driver code as I use with freetds, and now it seems that I’ll need to update the driver to have a separate code path when it’s built with freetds - which seems to defeat the purpose of freetds’s ctlib.  Is it not possible to fix this issue and provide 100% backwards-compatibility with Sybase’s ctlib?
> 

@Frediano: sorry to bother you with this issue, but since it’s important for me, can you please let me know about the above - should I plan on having 2 code paths in my DB driver - one for Sybase and one for FreeTDS, or should there ideally be a way that compatibility between Sybase’s ctlib and FreeTDS’s ctlib can be provided regarding this issue?

thx
David


> thx
> David
> 
>> Frediano
>>> 
>>>> 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
>>> 
>>> _______________________________________________
>>> 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



More information about the FreeTDS mailing list