Overview
Examples
Screenshots
Comparisons
Applications
Download
Documentation
Tutorials
Bazaar
Status & Roadmap
FAQ
Authors & License
Forums
Funding Ultimate++
Search on this site
Search in forums












SourceForge.net Logo
Home » U++ Library support » U++ SQL » MSSQL strange problem and ugly workaround patch...
MSSQL strange problem and ugly workaround patch... [message #19292] Mon, 24 November 2008 18:47 Go to next message
mirek is currently offline  mirek
Messages: 13975
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
Re: MSSQL strange problem and ugly workaround patch... [message #19294 is a reply to message #19292] Mon, 24 November 2008 19:08 Go to previous messageGo to next message
unodgs is currently offline  unodgs
Messages: 1366
Registered: November 2005
Location: Poland
Ultimate Contributor

Or try: select SCOPE_IDENTITY.
Re: MSSQL strange problem and ugly workaround patch... [message #19306 is a reply to message #19294] Tue, 25 November 2008 11:25 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13975
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
Re: MSSQL strange problem and ugly workaround patch... [message #19310 is a reply to message #19306] Tue, 25 November 2008 16:04 Go to previous messageGo to next message
unodgs is currently offline  unodgs
Messages: 1366
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.
Re: MSSQL strange problem and ugly workaround patch... [message #20220 is a reply to message #19310] Fri, 27 February 2009 13:26 Go to previous message
mirek is currently offline  mirek
Messages: 13975
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
Previous Topic: PostgreSQL string insert fix [with patch]
Next Topic: SqlArray/SqlCtrl/Select problem
Goto Forum:
  


Current Time: Fri Mar 29 11:21:42 CET 2024

Total time taken to generate the page: 0.02425 seconds