C++ Programming
09-25-2010, 11:00 AM
Yes I know it is a Database question but I have not had much luck in that forum so I am looking for a fresh perspective.
I am using accessors to perform DB queries which works fine except when I come to do INSERTs.
This is the SQL in the accessor
DEFINE_COMMAND_EX(CDBEvtRawInsert, L" \
INSERT INTO [RawEvent] ([TimeStamp],[Direction],[Hub],[RawEvent]) \
VALUES (GETDATE(),? ,? , ?);")
which works fine with this snippet...
wcscpy_s(rsEvtRawInsert.f_RawEvent, iSize*2, CA2T(pTmp)); // put it in the accessor
rsEvtRawInsert.f_Direction = 1; // indicate this is received data
rsEvtRawInsert.f_HubID = m_pHubDev->m_iHubID; // and from this hub
hr = rsEvtRawInsert.Open(m_oDB->session); // store it
However in order to retrieve the ID of the record which has just been inserted I need to add SELECT @@IDENTITY AS RecID to the SQL but this makes it a multiple command accessor which is where my problem is.
I have search MSDN and the SDK's but for the life of me I cannot find anything that shows me how to code the accessor properly to deal with the multiple results. I know I can use GetNextResult to move onto the second result set but I cannot get it to work.
It has been suggested using ExecuteScalar would be the solution but as I am going to have multiple threads executing the same code I would prefer to use the @@IDENTITY to avoid any possible collisions between the threads.
The only other solutions I can think of are to either issue a search to find the record I have just created OR maintain a counter which would track the ID field.
Does anyone have any other ideas or point me to a way to get the accessor to process my multiple commands?
Alan
I am using accessors to perform DB queries which works fine except when I come to do INSERTs.
This is the SQL in the accessor
DEFINE_COMMAND_EX(CDBEvtRawInsert, L" \
INSERT INTO [RawEvent] ([TimeStamp],[Direction],[Hub],[RawEvent]) \
VALUES (GETDATE(),? ,? , ?);")
which works fine with this snippet...
wcscpy_s(rsEvtRawInsert.f_RawEvent, iSize*2, CA2T(pTmp)); // put it in the accessor
rsEvtRawInsert.f_Direction = 1; // indicate this is received data
rsEvtRawInsert.f_HubID = m_pHubDev->m_iHubID; // and from this hub
hr = rsEvtRawInsert.Open(m_oDB->session); // store it
However in order to retrieve the ID of the record which has just been inserted I need to add SELECT @@IDENTITY AS RecID to the SQL but this makes it a multiple command accessor which is where my problem is.
I have search MSDN and the SDK's but for the life of me I cannot find anything that shows me how to code the accessor properly to deal with the multiple results. I know I can use GetNextResult to move onto the second result set but I cannot get it to work.
It has been suggested using ExecuteScalar would be the solution but as I am going to have multiple threads executing the same code I would prefer to use the @@IDENTITY to avoid any possible collisions between the threads.
The only other solutions I can think of are to either issue a search to find the record I have just created OR maintain a counter which would track the ID field.
Does anyone have any other ideas or point me to a way to get the accessor to process my multiple commands?
Alan