Re: affected rows after insert

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

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