sequence skips in ADO
I am trying to replace some of our old document# generators that were written back before IDS had sequences.
They just cause unwanted contention.
It seems that ADO is double-buffering and hitting the sequence twice, causing all numbers to be either even or odd.
That may be ok for some things, if you used that on invoices, some accountant would balk. :-{
The function does something like this:
HRESULT hr=0;
CComBSTR bsel="";
CComVariant cvTemp;
bsel = "select seq_something.nextval from systables where tabid = 1";
hr = prs.CreateInstance( __uuidof( Recordset ) );
if (FAILED(hr) || prs==NULL) return E_FAIL;
hr = prs->Open( (CComVariant)bsel, _variant_t((IDispatch *)pConn,true),
adOpenForwardOnly, adLockOptimistic, adCmdUnknown );
if (FAILED(hr)) throw E_FAIL;
if( prs->BOF && prs->adoEOF ) return E_FAIL;
prs->MoveFirst();
cvTemp = prs->Fields->GetItem((CComVariant)0)->GetValue();
if (cvTemp.vt == VT_NULL) return E_FAIL;
if (cvTemp.vt != VT_I4) VariantChangeType(&cvTemp,&cvTemp,0,VT_I4); // it will be VT_I8
return( cvTemp.lVal );
I have a workaround where I create a temp, then select into the temp, them select from the temp, then delete the temp.
That works just fine. I guess that's because it is using pConn->Execute(...) instead of a recordset to fire the generator, so the double-buffering does not enter into the picture.
This is probably the wrong forum to ask this, but I was wondering if anyone here had solved the same problem.
The code will work find in dbaccess, winsql, etc. It is just in MSFT land (ado) that it occurs.
Could be in odbc? Could be a setting someplace?