Handing multiple dbcount from freetds?

Question

I have a few queries which should return multiple 'affected rows' (and do so when running via SQL Server Management Studio), but I cannot seem to figure out how to properly call dbcount more than one time.

This is my testing function using the FreeTDS library:

- (BOOL) testCommand: (NSString*) queryToExecute
{
    RETCODE retcode;

    // Set our command
    retcode = dbcmd(dbProc, [queryToExecute UTF8String]);
    if(SUCCEED != retcode)
    {
        return NO;
    } // End of failed to set command

    retcode = dbsqlexec(dbProc);
    if(SUCCEED != retcode)
    {
        NSLog(@"Query failure, retcode was: %d", retcode);

        // Error handling goes here

        return NO;
    } // End of failed to sqlexec

    while(SUCCEED == (retcode = dbresults(dbProc)))
    {
        if(SUCCEED == (retcode = dbrows(dbProc)))
        {
            // Loop though our records
            NSUInteger rowCount = 0;
            while (NO_MORE_ROWS != (retcode = dbnextrow(dbProc)))
            {
                ++rowCount;
            } // End of records loop

            NSLog(@"Command completed successfully. (%ld results).", rowCount);
        } // End dbrows succeeded
        else
        {
            NSNumber * numberOfRowsAffected = [NSNumber numberWithInt: dbcount(dbProc)];
            bool isCountReal = dbiscount(dbProc);
            NSLog(@"Command completed successfully. (%@ rows affected). (%@).",
                  numberOfRowsAffected,
                  isCountReal ? @"YES" : @"NO");
        }
    } // End of dbresults loop

    return YES;
}

If I run queries via this code vs SSMS I get different results:

update actor set [first_name] = 'PENELOPE' where first_name = 'PENELOPE';
SELECT * FROM actor;
update actor set [first_name] = 'NICK' where first_name = 'NICK';
SELECT * FROM actor;

SSMS:

(4 row(s) affected)
(200 row(s) affected)
(3 row(s) affected)
(200 row(s) affected)

FreeTDS:

Command completed successfully. (4 rows affected).
Command completed successfully. (200 results).
Command completed successfully. (200 results).

Query:

SELECT * FROM actor;
update actor set [first_name] = 'PENELOPE' where first_name = 'PENELOPE';

SSMS:

(200 row(s) affected)
(4 row(s) affected)

FreeTDS:

Command completed successfully. (200 results).

Query:

update actor set [first_name] = 'PENELOPE' where first_name = 'PENELOPE';
SELECT * FROM actor;

SSMS:

(4 row(s) affected)
(200 row(s) affected)

FreeTDS:

Command completed successfully. (4 rows affected).
Command completed successfully. (200 results).

I have confirmed via SQL Server Profiler that the commands are executed the same both from SSMS and from my FreeTDS code.

As you can see from the FreeTDS code/output I am never able to get more than one count of the number of affected rows. I am assuming that I've done something wrong somewhere along the lines or that I'm missing something, but so far I have been unable to figure it out. I've gone through the FreeTDS documentation multiple times.

Can anyone point me in the right direction?

Answer 1

Turns it it was an issue in FreeTDS (issue IMO, but for some others it may be the required behaviour).

My fix was to replace the following code in the _dbresults method:

            switch (dbproc->dbresults_state) {

            case _DB_RES_INIT:
            case _DB_RES_NEXT_RESULT:
                dbproc->dbresults_state = _DB_RES_NEXT_RESULT;
                if (done_flags & TDS_DONE_ERROR)
                    return FAIL;
                break;

With:

            switch (dbproc->dbresults_state) {

            case _DB_RES_INIT:
                dbproc->dbresults_state = _DB_RES_NEXT_RESULT;
                if (done_flags & TDS_DONE_ERROR)
                    return FAIL;
                break;

            case _DB_RES_NEXT_RESULT:
                dbproc->dbresults_state = _DB_RES_NEXT_RESULT;
                if (done_flags & TDS_DONE_ERROR)
                    return FAIL;

                return SUCCEED;
                break;

In the previous code, a loop would continue until a result set, or end of result was found. In my case, I wanted to be able to match SSMS output. In my tests so far, this seems to work and if I determine everything is good, I will look into submitting a patch to FreeTDS.

I didn't mess with the _DB_RES_INIT switch as I really didn't want to dig that far into it and that did not seem to be my problem, so I left that case as was.