U++ framework: U++ SQL » MSSQL strange problem and ugly workaround patch...

Home » U++ Library support » U++ SQL » MSSQL strange problem and ugly workaround patch...

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

MSSQL strange problem and ugly workaround patch... [message #19292]

Mon, 24 November 2008 18:47

mirek
Messages: 14291
Registered: November 2005

Ultimate Member

I have found a very strange problem with MSSQL:

When fetching @@IDENTITY column while inside doing inserts inside select/fetch loop, I am getting Nulls until the last portion of data passed from MSSQL and client (in Prefetch routine).

Something like:

Sql sql;
sql * Select(...).From(...);
while(sql.Fetch()) {
    SQL * Insert(...) // table has INDENT columne
    DUMP(SQL % Select("@@IDENTITY"));
}

Really weird. I have not authored OleDB and I am a little bit lost in all those Ole interfaces, but I see nothing particulary wrong there anyway. Other selects work just as expected, just this one if wrong.

I have found a working workaround using IDENT_CURRENT('tablename'). Of course, it requires the name of table to be stored, so we have to look into statement executed if it is insert and what is after 'into'. Anyway, seems to fix the issue....

Mirek

Report message to a moderator

Re: MSSQL strange problem and ugly workaround patch... [message #19294 is a reply to message #19292]

Mon, 24 November 2008 19:08

unodgs
Messages: 1367
Registered: November 2005
Location: Poland

Ultimate Contributor

Or try: select SCOPE_IDENTITY.

Report message to a moderator

Re: MSSQL strange problem and ugly workaround patch... [message #19306 is a reply to message #19294]

Tue, 25 November 2008 11:25

mirek
Messages: 14291
Registered: November 2005

Ultimate Member

unodgs wrote on Mon, 24 November 2008 13:08

Or try: select SCOPE_IDENTITY.

Tried. The same problem as with @@IDENTITY.

BTW, this is really so weird that I would like somebody else to check and try.

AFAIK, the critical moment is when 'outer' select loop has more rows than can be prefetched in single step. In that case, @@IDENTITY (and SCOPE_IDENTITY) does not work until the last prefetch is performed.

That OTOH means that unless you do inserts in large fetch loop, the bug is hidden.

Of course, still possible something is wrong in OleDB.

Mirek

Report message to a moderator

Re: MSSQL strange problem and ugly workaround patch... [message #19310 is a reply to message #19306]

Tue, 25 November 2008 16:04

unodgs
Messages: 1367
Registered: November 2005
Location: Poland

Ultimate Contributor

That's really strange. I think it would be too critical bug to be not discovered by microsoft. Maybe Tom could have a look at OleDb code.

Report message to a moderator

Re: MSSQL strange problem and ugly workaround patch... [message #20220 is a reply to message #19310]

Fri, 27 February 2009 13:26

mirek
Messages: 14291
Registered: November 2005

Ultimate Member

I have interesting followup to this bug - I believe I now know WHY and it is indeed unbelievable:

While working on ODBC, I have noticed strange problem - MSSQL server is in effect able to sustain only single SQL command with result-set (at least in default mode, there is something new called "MARS" to avoid this problem...)

This is debilitating condition make SQL apps development next to impossible (think exactly about the loop here - this is what MSSQL cannot do easily). That is why OLDDB introduces concept of "hidden connections".

To put it straight, when it detects there is some result set pending, it opens another connection to database. That means a new session that knows nothing about previous identity insert.

Now if result set is small, it is (in our testcase) fetched in one go and no more connections need to be created. However, if it is large, OLEDB creates another connection with new session.

What implication this unbelievable thing has to transaction safety or prepared statments I do not dare to predict... Sad

In any case, I am now going to fix my ODBC U++ connector so that it will work with single ODBC statement only and fetch if required. I guess it is the only safe option...

Mirek

Report message to a moderator

Previous Topic:	PostgreSQL string insert fix [with patch]
Next Topic:	SqlArray/SqlCtrl/Select problem

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

] [

]

Current Time: Thu Sep 10 04:38:47 GMT+2 2026

Total time taken to generate the page: 0.00747 seconds