How can we bulk update records using CRecordset

Question

I have an mfc application that uses CRecordsets to fetch and update/insert data.

I was able to implement bulk row fetching, but I'm now looking to implement bulk row updating/inserting/deleting using a derived CRecordset.

Has anyone done that? Can you provide code examples?

I need to be able to fetch almost every type : bool byte short long float double binary text guid... — Goldorak84, Apr 01 '13 at 12:22
So text fetching in bulk worked for you? I'm asking because I have [this problem](http://stackoverflow.com/questions/15155374/implementing-bulk-record-fetching). — Anish Ramaswamy, Apr 01 '13 at 12:24

score 2 · Accepted Answer · edited May 23 '17 at 12:29

I stumbled on an old post describing how to implement bulk row updating on a CRecordset. First, you have to implement bulk row fetching on your recordset(You can also see this post for an example)

Once you recordset works for fetching data, it's quite simple.

//Declare your recordset
CMyRecordsetBulk regSetBulk(&myDatabase);

//Open it
regSetBulk.Open(NULL, NULL, CRecordset::useMultiRowFetch);

//Select the row you want to change
regSetBulk.SetRowsetCursorPosition(nRow);

//Update the value(s) you need to change.
regSetBulk.m_pnPrecision = 21;

//Set the length of the data in the field you modified (the "Precision" field is a byte)
regSetBulk.m_plnPrecision = 1;


//Do the same thing for a couple of other rows

regSetBulk.SetRowsetCursorPosition(++nRow);
regSetBulk.m_pnPrecision = 32;
regSetBulk.m_plnPrecision = 1;

regSetBulk.SetRowsetCursorPosition(++nRow);
regSetBulk.m_pnPrecision = 21;
regSetBulk.m_plnPrecision = 1;

regSetBulk.SetRowsetCursorPosition(++nRow);
regSetBulk.m_pnPrecision = 12;
regSetBulk.m_plnPrecision = 1;

//Update the rows and check for errors
int nRetCode;
AFX_ODBC_CALL(::SQLSetPos(regSetBulk.m_hstmt, NULL, SQL_UPDATE, SQL_LOCK_NO_CHANGE)); 
regSetBulk.CheckRowsetError(nRetCode);

score 1 · Answer 2 · answered Feb 05 '12 at 00:30

1

Just use CDatabase::ExecuteSQL. Doing updates by looping through as CRecordset can't be something you really want to do. A CRecordSet is only useful if you work with single entries, but not whole sets of data.

answered Feb 05 '12 at 00:30

dwo

3,576
2
22
39

In fact, using a CRecordset to insert data is generally faster than using CDatabase::ExecuteSQL. I recently experienced a case with msAccess where using CRecordset instead of ExecuteSQL reduced the insert time from 40 to 3 seconds. – Goldorak84 Feb 14 '12 at 17:23
Debug/step into the recordset class and see what it does: creating a sql-statement and executing it! – dwo Feb 15 '12 at 12:26
It's more complex than that. CRecordset Class do variable and parameter binding and other stuff that can speed up the treatment. – Goldorak84 Feb 17 '12 at 14:07
But who says you can execute only one INSERT at a time?! Create a lot of them and execute only once, you'll save a lot of db-roundtrips! – dwo Feb 17 '12 at 15:00
And how would you do that? CDatabase::ExecuteSQL executes only one statement at a time – Goldorak84 May 11 '12 at 17:30
Are you aware of an MFC database object that can execute multiple statements? – Goldorak84 May 11 '12 at 17:31
strSQL = "insert bla bla; insert bla bla;" – dwo May 11 '12 at 20:20
CDatabase::ExecuteSQL doesnt support that. – Goldorak84 Jun 26 '12 at 12:43
Sure it does. I do that all the time. I am using SQL Server, maybe other ODBC clients can't do that. – dwo Jun 26 '12 at 13:24
You're right,before posting the may 11th comment, I tested it with oracle and access and it did not work, but sql server works. My app must support those 3 bd... thks for the help – Goldorak84 Jun 26 '12 at 19:35

How can we bulk update records using CRecordset

2 Answers2