Concurrency puzzle (ODBC API related)

[Ernesto]

I hope this is not outside the scope of this newsgroup.


have been useing BulkAdd with ms-access with good success.
Until now that I;m switching to MSDE which is on a shared server.

The problem is that MSDE gives an error:
Invalid attribute/option identifier, sql state=HY092
which corresponds to:
SQL_ATTR_CONCUR RENCY statement attribute was set to SQL_CONCUR_READ _ONLY.

but my BulkAdd routine explicitly has a call to:
SQLSetStmtAttr( stHndl, SQL_ATTR_CONCUR RENCY, SQL_CONCUR_LOCK , iRet)

I cannot imagine how the concurrency is readonly.
Could it be that I'm looking at the wrong place or the wrong attribute???

Thanks in adv.

[Erland Sommarskog]

Re: Concurrency puzzle (ODBC API related)

Ernesto (tsh@mathematic uslabs.com) writes:[color=blue]
> have been useing BulkAdd with ms-access with good success.
> Until now that I;m switching to MSDE which is on a shared server.
>
> The problem is that MSDE gives an error:
> Invalid attribute/option identifier, sql state=HY092
> which corresponds to:
> SQL_ATTR_CONCUR RENCY statement attribute was set to SQL_CONCUR_READ _ONLY.
>
> but my BulkAdd routine explicitly has a call to:
> SQLSetStmtAttr( stHndl, SQL_ATTR_CONCUR RENCY, SQL_CONCUR_LOCK , iRet)
>
> I cannot imagine how the concurrency is readonly.
> Could it be that I'm looking at the wrong place or the wrong attribute???[/color]

Without seeing your code, it is not possible to give a definitive answer.
(And it doesn't help that I have little experience of ODBC API programming.)

But checking the docs, my conclusion is that the SELECT statement for your
cursor is such that the cursor is not updatable. This could be due to
several causes: lack of primary key, use of expressions etc. Would need to
see the SQL code together with some information on the table to say more.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Ernesto]

Re: Concurrency puzzle (ODBC API related)

Erland ,
You are good!
I changed the query which created the table to create a primary key and it
SEEMS to solve the problem.
(It's a good thing having a primary key is ok in this case, else I would not
know how to "solve" this.)


I'm not completely out of the woods yet, but I think now there is another
unrelated problem.
Hopefully you'll keep reading if I am fooled and the problem is still there.

Now if you can only tell me how or where in the world can I learn these
things about the odbc api.

Not having found any good resource which explains these things, the way I
operate is by prayer only and geenrous people like yourself.

E

[Erland Sommarskog]

Re: Concurrency puzzle (ODBC API related)

Ernesto (tsh@mathematic uslabs.com) writes:[color=blue]
> Now if you can only tell me how or where in the world can I learn these
> things about the odbc api.[/color]

The place to read about the ODBC API in general is the MDAC Books Online,
which comes with the MDAC SDK. Then there is a section in the SQL Server
Books Online which covers specifics for the SQL Server ODBC Driver.

For this particular issue, though, it's more of a general understanding
of databases in general, and in this particular case about cursors. I
know that Kalen Deleany has a chapter on cursors in her "Inside SQL
Server 2000". I doubt that she says much about ODBC, but the same issues
apply when you use cursors in T-SQL.




--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Ernesto]

Re: Concurrency puzzle (ODBC API related)

Thank you for the sources. I will look them up.
One more dumb question in case you can respond:

How would I do this if I did not want to add a primary key to the table?

[Ross Presser]

Re: Concurrency puzzle (ODBC API related)

On Wed, 7 Jul 2004 14:20:02 -0400, Ernesto wrote:
[color=blue]
> Thank you for the sources. I will look them up.
> One more dumb question in case you can respond:
>
> How would I do this if I did not want to add a primary key to the table?[/color]

You could add it to a temporary table that did have a primary key, then
execute an INSERT INTO command to transfer it to the permanent table.

However, a primary key is almost always a good idea. Some will argue that
without a primary key it isn't a table at all.

[Joe Celko]

Re: Concurrency puzzle (ODBC API related)

>> How would I do this if I did not want to add a primary key to the
table? <<

Without a key, this is not a table; it is a punch card file with a
little class. You would return to what we used to call "EDP"
(Electronic Data Processing) in the late 1950's and throw away all 30
years of progress.

Well, you asked and that is the answer.

--CELKO--
=============== ============
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!