Beginner OLE question
-
I'm currently moving a project over to OLE DB and I've run into a bit of a dilema. I've gotten the basics of OLE down and how to use the accessor to insert into a table. Here's my problem, the table that I'm entering into will be very large once the program starts being used so using the accessor to insert will more than likely kill the computer. The question, is there any way to use the accessor to do the insert without populating it?
-
I'm currently moving a project over to OLE DB and I've run into a bit of a dilema. I've gotten the basics of OLE down and how to use the accessor to insert into a table. Here's my problem, the table that I'm entering into will be very large once the program starts being used so using the accessor to insert will more than likely kill the computer. The question, is there any way to use the accessor to do the insert without populating it?
I take it you're using Visual C++, and native OLE DB with ATL? I've inserted rows into large tables before without any problem. You're using a "real" database, right? (I don't consider Microsoft Access or FoxPro as a real database.) I'm confused by this statement: errenden wrote: The question, is there any way to use the accessor to do the insert without populating it? What is it that you don't want to populate? Are you talking about the "generic" query that the accessor uses by default, where it selects every row in the table? And you're concerned about the program selecting every row first, and then adding your new row to the end? -Thomas
-
I take it you're using Visual C++, and native OLE DB with ATL? I've inserted rows into large tables before without any problem. You're using a "real" database, right? (I don't consider Microsoft Access or FoxPro as a real database.) I'm confused by this statement: errenden wrote: The question, is there any way to use the accessor to do the insert without populating it? What is it that you don't want to populate? Are you talking about the "generic" query that the accessor uses by default, where it selects every row in the table? And you're concerned about the program selecting every row first, and then adding your new row to the end? -Thomas
I'm using Oracle 9i on the backend so no worries there. But yes I'm concerned about the memory usage/performance with the accessor selecting every row just to do an insert. I've thought about just doing performing a specific SELECT statement to pull some set default row in the database but that solution seems clunky at best.
-
I'm using Oracle 9i on the backend so no worries there. But yes I'm concerned about the memory usage/performance with the accessor selecting every row just to do an insert. I've thought about just doing performing a specific SELECT statement to pull some set default row in the database but that solution seems clunky at best.
Beautiful! I use Oracle also. (I also had better luck using Oracle's provider, instead of the Microsoft OLE DB for Oracle provider.) I've actually used that method (pull a default row)- I take the default "take everything" accessor, and change it into a "get me the row with this primary key" parameterized query. If the accessor returns a row, I know that the data already existed in the table. If the rowset is empty, then I set the values up and update the row. Of course, you can handle this by checking out HRESULT and etc to determine if the row already exists... You probably want to create a command object instead of a rowset object. That way you can do direct insert/update/deletes, as well as run stored procedures. In fact, you might want to turn your insert into a stored procedure! Either way, at the top of the accessor, add in all your member variables (TCHARs, DOUBLEs, etc) just as if you were dealing with a rowset accessor. In fact, you might already have this if you created a default rowset accessor! Then, define your parameter map...
BEGIN_PARAM_MAP(CNAMEOFACCESSORAccessor) COLUMN_ENTRY(1, mFIRSTFIELD) COLUMN_ENTRY(2, mSECONDFIELD) COLUMN_ENTRY(ETC...) END_PARAM_MAP()Make sure you defined mFIRSTFIELD and mSECONDFIELD and etc first. Now, change up the default "rowset" command that you were given.DEFINE_COMMAND(CNAMEOFACCESSORAccessor, _T("INSERT INTO MYTABLE VALUES (?, ?)"))Look down at the in the command class that ATL built for the OpenRowset method. Look forreturn CCommand<CAccessor<CNAMEOFACCESSORAccessor>>::Open(mSession);You've gotta change the CCommand::Open parameters. (Look it up in the MSDN, search for "CCommand::Open".) Leave the first parameter as m_session. You can set the second parameter, szCommand, to NULL because you're using the Accessor for the command. The third parameter would be for a property set, if you defined any. (If you're calling an insert, I'd think you would... I used stored procedures so I can't say for sure, but I think you'd need DBPROP_UPDATABILITY.) The fourth parameter's important, that's a pointer (pRowsAffected) to the number of rows that got DML performed. In your case, this should come back as 1 for a successful insert. (Negative 1 means no rows affected, as does a NULL pointer.) Hope this helps; or at least gives you something to search the VC++ help files and the web for! Oh- for the code where I queried first for the data, and if I got no rows bac -
Beautiful! I use Oracle also. (I also had better luck using Oracle's provider, instead of the Microsoft OLE DB for Oracle provider.) I've actually used that method (pull a default row)- I take the default "take everything" accessor, and change it into a "get me the row with this primary key" parameterized query. If the accessor returns a row, I know that the data already existed in the table. If the rowset is empty, then I set the values up and update the row. Of course, you can handle this by checking out HRESULT and etc to determine if the row already exists... You probably want to create a command object instead of a rowset object. That way you can do direct insert/update/deletes, as well as run stored procedures. In fact, you might want to turn your insert into a stored procedure! Either way, at the top of the accessor, add in all your member variables (TCHARs, DOUBLEs, etc) just as if you were dealing with a rowset accessor. In fact, you might already have this if you created a default rowset accessor! Then, define your parameter map...
BEGIN_PARAM_MAP(CNAMEOFACCESSORAccessor) COLUMN_ENTRY(1, mFIRSTFIELD) COLUMN_ENTRY(2, mSECONDFIELD) COLUMN_ENTRY(ETC...) END_PARAM_MAP()Make sure you defined mFIRSTFIELD and mSECONDFIELD and etc first. Now, change up the default "rowset" command that you were given.DEFINE_COMMAND(CNAMEOFACCESSORAccessor, _T("INSERT INTO MYTABLE VALUES (?, ?)"))Look down at the in the command class that ATL built for the OpenRowset method. Look forreturn CCommand<CAccessor<CNAMEOFACCESSORAccessor>>::Open(mSession);You've gotta change the CCommand::Open parameters. (Look it up in the MSDN, search for "CCommand::Open".) Leave the first parameter as m_session. You can set the second parameter, szCommand, to NULL because you're using the Accessor for the command. The third parameter would be for a property set, if you defined any. (If you're calling an insert, I'd think you would... I used stored procedures so I can't say for sure, but I think you'd need DBPROP_UPDATABILITY.) The fourth parameter's important, that's a pointer (pRowsAffected) to the number of rows that got DML performed. In your case, this should come back as 1 for a successful insert. (Negative 1 means no rows affected, as does a NULL pointer.) Hope this helps; or at least gives you something to search the VC++ help files and the web for! Oh- for the code where I queried first for the data, and if I got no rows bacI found some stuff yesterday on binding paramaters in OLE DB and started to tinker with it. Your reply helps parse out some of what I've read, especially with the pRowAffected variable (I had completely missed it the first time around). I'll check out that book later today and see about picking it up. Maybe you could also answer this question since finding info good solid info on OLE DB seems to be very elusive. I've been doing select statements using the Create command and binding two parameters, which I added to accessor .h file. Now I've attempted to expand this out to do a insert which requires four parameters, I added the other two parameters to the accessor file and now the select won't work unless I include the other two parameters I just added in, which defeats the purpose of the select. Thanks for all the help so far, I appreciate it. To answer your last question, the reason we haven't moved over to C# and the whole .NET framework is due to the fact that .NET may not run on all of the client machines (some being very old and some may be running unix) and the fact that some of the current code uses lisp and we don't have the time to make sure C#.NET will run with it. Fun stuff. Once again, thanks for the help.