problem with DBLib access to SQL Server 2005

microsoft.public.sqlserver.programming

11 Feb 2006 1:21 AM

Balt

Hi all,

I'm having a (probably) fundamental problem executing an insert statement on
SQL Server 2005 from an app written in plain C, using the DBLib API. Please
DO NOT comment on the fact the DBlib is obsolete, I know it is. It's just the
fastest way with the least overhead, and as such my method of choice and
currently still available with SQL Server 2005.

All appears to go well for the init, login and begin transaction calls. The
first and any subsequent inserts however cause dbresults to return 2, which,
depending on where and what in the docs you want to believe, means
NO_MORE_RESULTS. In the context of an insert, that doesn't make much sense.
In any case, the data did not get inserted.

When I keep SQL Monitor open during those calls, I see the calls are being
made, the insert statements transferred, and when I copy/paste them into the
SQL Manager window and execute them, they work just fine, no syntax problems
there.

Here are a few code snippets, maybe somebody can see where I'm messing up.

the database init:

    // Initialize DB-Library.
    dbinit();

    // Get a LOGINREC.
    login = dblogin ();
    DBSETLSECURE (login);
    DBSETLAPP(login, "mydb");
    DBSETLUSER(login, "TITAN\\user");

    // Get a DBPROCESS structure for communication with SQL Server.
    dbproc = dbopen(login, "Titan");

    //switch the default database to the mydb DB:
    dbcmd (dbproc, "EXEC sp_defaultdb 'sa', 'mydb'");
    dbsqlexec (dbproc);

This part does not produce any error messages. I'm logging into sql server
as the logged in user, which should be the sa in my default environment.
Then, I initiate a transaction. Each transaction will consist of about
20'000 inserts:

        strcpy_s(cBuf, sizeof(cBuf), "BEGIN TRANSACTION");

        // Send the command to SQL Server and start execution.
        dbcmd (dbproc, cBuf);
        dbsqlexec (dbproc);

        // Process the results.
        rc = dbresults (dbproc);
        if (rc != SUCCEED && rc != NO_MORE_RESULTS) {
            printf("Command failed: %s", cBuf);
            dbexit();
            exit(1);
        }

This returns rc = 1 (i.e. SUCCEED).
Then I begin with the insert statement:

            dbcmd (dbproc, "INSERT INTO data (\"Field1\") VALUES (-12);");
            rc = dbsqlexec (dbproc);

            // Process the results.
            rc = dbresults(dbproc);
            if (rc != SUCCEED && rc != NO_MORE_RESULTS) {
                fprintf(stderr, "dbresults returned: %d\n", rc);
                dbexit();
                exit(1);
            }

Now, rc = 2. Why? The same statement executed in the SQL Server Manager
window works just fine, the same statement in SQLite worked just fine as well
(actually, the " were ' and had to be changed to " because SQL Server
complained about syntax).

This happens regardless of whether I start with a "BEGIN TRANSACTION" call
or not.

Cheers

- Balt

11 Feb 2006 2:36 PM

Andrew J. Kelly

Balt,

>>> It's just the

fastest way with the least overhead, and as such my method of choice and
currently still available with SQL Server 2005. <<<

Actually it is not still available with 2005 you have to get it from a
previous version to even use it. It is obsolete for a reason and your
excuse for using it simply will not cut it. Even if you get thru this
hurdle you will ultimately hit another. If you are trying to insert 20K rows
as fast as possible I suggest you use one of the newer bulk insert api's
instead. I know this is not what you wanted to hear but this is a loosing
proposition in the long run.

--
Andrew J. Kelly SQL MVP

Show quote

"Balt" <B***@discussions.microsoft.com> wrote in message
news:CBB6F9F3-711F-4837-B831-C62DEA5F411A@microsoft.com...
> Hi all,
>
> I'm having a (probably) fundamental problem executing an insert statement
> on
> SQL Server 2005 from an app written in plain C, using the DBLib API.
> Please
> DO NOT comment on the fact the DBlib is obsolete, I know it is. It's just
> the
> fastest way with the least overhead, and as such my method of choice and
> currently still available with SQL Server 2005.
>
> All appears to go well for the init, login and begin transaction calls.
> The
> first and any subsequent inserts however cause dbresults to return 2,
> which,
> depending on where and what in the docs you want to believe, means
> NO_MORE_RESULTS. In the context of an insert, that doesn't make much
> sense.
> In any case, the data did not get inserted.
>
> When I keep SQL Monitor open during those calls, I see the calls are being
> made, the insert statements transferred, and when I copy/paste them into
> the
> SQL Manager window and execute them, they work just fine, no syntax
> problems
> there.
>
> Here are a few code snippets, maybe somebody can see where I'm messing up.
>
> the database init:
>
>    // Initialize DB-Library.
>    dbinit();
>
>    // Get a LOGINREC.
>    login = dblogin ();
>    DBSETLSECURE (login);
>    DBSETLAPP(login, "mydb");
>    DBSETLUSER(login, "TITAN\\user");
>
>
>    // Get a DBPROCESS structure for communication with SQL Server.
>    dbproc = dbopen(login, "Titan");
>
>    //switch the default database to the mydb DB:
>    dbcmd (dbproc, "EXEC sp_defaultdb 'sa', 'mydb'");
>    dbsqlexec (dbproc);
>
> This part does not produce any error messages. I'm logging into sql server
> as the logged in user, which should be the sa in my default environment.
> Then, I initiate a transaction. Each transaction will consist of about
> 20'000 inserts:
>
> strcpy_s(cBuf, sizeof(cBuf), "BEGIN TRANSACTION");
>
> // Send the command to SQL Server and start execution.
> dbcmd (dbproc, cBuf);
> dbsqlexec (dbproc);
>
> // Process the results.
> rc = dbresults (dbproc);
> if (rc != SUCCEED && rc != NO_MORE_RESULTS) {
> printf("Command failed: %s", cBuf);
> dbexit();
> exit(1);
> }
>
> This returns rc = 1 (i.e. SUCCEED).
> Then I begin with the insert statement:
>
> dbcmd (dbproc, "INSERT INTO data (\"Field1\") VALUES (-12);");
> rc = dbsqlexec (dbproc);
>
> // Process the results.
> rc = dbresults(dbproc);
> if (rc != SUCCEED && rc != NO_MORE_RESULTS) {
> fprintf(stderr, "dbresults returned: %d\n", rc);
> dbexit();
> exit(1);
> }
>
> Now, rc = 2. Why? The same statement executed in the SQL Server Manager
> window works just fine, the same statement in SQLite worked just fine as
> well
> (actually, the " were ' and had to be changed to " because SQL Server
> complained about syntax).
>
> This happens regardless of whether I start with a "BEGIN TRANSACTION" call
> or not.
>
> Cheers
>
> - Balt
>

11 Feb 2006 7:50 PM

Erland Sommarskog

Balt (B***@discussions.microsoft.com) writes:

> All appears to go well for the init, login and begin transaction calls.
> The first and any subsequent inserts however cause dbresults to return
> 2, which, depending on where and what in the docs you want to believe,
> means NO_MORE_RESULTS. In the context of an insert, that doesn't make
> much sense.

Actually, it does. An INSERT statement will return a rowcount (the
thing you see as a ("1 row affected") in Query Analyzer or Mgmt Studio,
unless SET NOCOUNT ON.

>     //switch the default database to the mydb DB:
>     dbcmd (dbproc, "EXEC sp_defaultdb 'sa', 'mydb'");
>     dbsqlexec (dbproc);

This seems like a very funny thing to do. And dangerous. Changing the
default database for sa is something I never had the guts to try.
And why would you change the default database for sa, when you logged
in with integrated security?

> This part does not produce any error messages. I'm logging into sql server
> as the logged in user, which should be the sa in my default environment.

No! "sa" is an SQL login, and you login with Integrated Security. If you
have admin rights in Windows, you will have sysadmin rights in SQL Server,
but you will not be sa.

> Then, I initiate a transaction. Each transaction will consist of about
> 20'000 inserts:

As Andrew said, consider using bulk operations instead.

Another alternative is to build an XML document of all the 20000
rows, send that to SQL Server and then shred it into the target table(s).

If you really want to send row-by-row, use a stored procedure that you
call through RPC instead. This is faster, as you get a cached query plan,
and there is no need for parsing the query each time.

And if you don't want to use stored procedures, at least call
sp_executesql through RPC instead. Hm, wait, sp_executesql insists
on parameters on nvarchar that DB-Library cannot handle. So that won't
fly.

Anyway, whatever, don't send 20000 INSERT statements over the wire. It's
simple ineffecient.

> dbcmd (dbproc, "INSERT INTO data (\"Field1\") VALUES (-12);");

For this to work, the setting QUOTED_IDENTIFIER must be ON. Which it is
by default with ODBC, OLE DB and SqlClient. But not with DB-Library. So
you would need to issue SET QUOTED_IDENTIFIER ON from your program. Or
use [] to quote your identifiers instead.

Then again, I told you not to send INSERT statements.

>                rc = dbsqlexec (dbproc);
>
>                // Process the results.
>                rc = dbresults(dbproc);
>                if (rc != SUCCEED && rc != NO_MORE_RESULTS) {
>                     fprintf(stderr, "dbresults returned: %d\n", rc);
>                     dbexit();
>                     exit(1);
>                }
>
> Now, rc = 2. Why?

Because there are no more results. But not from the INSERT statment.
You failed to check the return code from dbsqlexec, but I bet it says
FAIL. Because you previous call to dbresults return SUCCEED, and this
means that you have not cleared out the previous command, so you
get an error when trying to start a new one. You should always loop
over dbresults until it returns FAIL or NO_MORE_RESULTS.

Finally, there are tons of limitations you run into when you use
DB-Library, so it is very difficult to recommend it. It is a very nice
client API once you have learnt to master it. Particularly, it does not
perform tricks behind your back. However, it certainly has a taste
of black-and-white television these days.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx