Re: affected rows after insert
David Nichols <[email protected]>
| Newsgroups | gmane.comp.db.tds.freetds |
|---|---|
| Message-ID | <[email protected]> |
> 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? 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