Re: affected rows after insert

David Nichols <[email protected]>
Newsgroups gmane.comp.db.tds.freetds
Message-ID <[email protected]> 
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


> 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
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.