Re: affected rows after insert
David Nichols <[email protected]>
| Newsgroups | gmane.comp.db.tds.freetds |
|---|---|
| Message-ID | <[email protected]> |
On Oct 20, 2015, at 11:17 AM, David Nichols <[email protected]> wrote: > >> >> On Oct 20, 2015, at 9:12 AM, Frediano Ziglio <[email protected]> wrote: >> >> Il 20/Ott/2015 09:09, "David Nichols" <[email protected]> ha scritto: >>> >>> On Oct 19, 2015, at 10:20 PM, Frediano Ziglio <[email protected]> wrote: >>>> >>>> 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 >>> >>> 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 >>>>>>>>> [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 >>> >>> _______________________________________________ >>> 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