Re: affected rows after insert
David Nichols <[email protected]>
| Newsgroups | gmane.comp.db.tds.freetds |
|---|---|
| Message-ID | <[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. >> 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
rows-affected-patch.diff
(application/octet-stream, 526 B)
diff --git a/src/ctlib/ct.c b/src/ctlib/ct.c
index 2f57ad1..54ab20d 100644
--- a/src/ctlib/ct.c
+++ b/src/ctlib/ct.c
@@ -1345,6 +1345,11 @@ ct_results(CS_COMMAND * cmd, CS_INT * result_type)
switch (cmd->results_state) {
case _CS_RES_INIT: /* command had no result set */
+ if (tds->rows_affected > 0) {
+ *result_type = CS_CMD_DONE;
+ cmd->results_state = _CS_RES_INIT;
+ return CS_SUCCEED;
+ }
break;
case _CS_RES_RESULTSET_EMPTY:
if (cmd->command_type == CS_CUR_CMD) {
simple-rows-affected-test.q
(application/octet-stream, 737 B) - not displayed