Skip to Content.
Sympa Menu

freetds - Re: [freetds] affected rows after insert

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: David Nichols <david AT qore.org>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] affected rows after insert
  • Date: Mon, 19 Oct 2015 14:35:33 +0200


> On Oct 19, 2015, at 1:53 PM, Frediano Ziglio <freddy77 AT gmail.com> wrote:
>
> 2015-10-19 11:16 GMT+01:00 David Nichols <david AT qore.org>:
>>
>>> On Oct 18, 2015, at 10:06 AM, Frediano Ziglio <freddy77 AT gmail.com> wrote:
>>>
>>> 2015-10-16 12:23 GMT+01:00 David Nichols <david AT qore.org>:
>>>>> 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

Attachment: rows-affected-patch.diff
Description: Binary data

Attachment: simple-rows-affected-test.q
Description: Binary data



> 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
>>>> FreeTDS AT lists.ibiblio.org
>>>> http://lists.ibiblio.org/mailman/listinfo/freetds
>>> _______________________________________________
>>> FreeTDS mailing list
>>> FreeTDS AT lists.ibiblio.org
>>> http://lists.ibiblio.org/mailman/listinfo/freetds
>>
>> _______________________________________________
>> FreeTDS mailing list
>> FreeTDS AT lists.ibiblio.org
>> http://lists.ibiblio.org/mailman/listinfo/freetds
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds




Archive powered by MHonArc 2.6.24.

Top of Page