insert statement kills my opencursorWithID

Hi John,

I have no answer for the discussed problem, but my guess some more information may be required:

• What MicroStation version do you use (build number)?
• Windows version
• Use DB connection (ODBC, OLEDB, BUDBC...)
• Database driver
• Database

... the communication between MicroStation and database is a long chain, so it may be helpful to know exactly what pieces of software are involved.

Just an idea: Is it possible to try the same code with another database or the same database but different driver?

With regards,

  Jan

John,

Please try the steps listed in order from this post: mdlDB_fetchRowByID and let us know if that helps resolve the issue.  If not, here is another search for a couple other threads ( 8 ) related to calling mdlDB_fetchRowByID and needing to enable/disable autocommit accordingly.

HTH,
Bob

Same for me, thank you for posting. This saved me several hours of work.

Thanks Bob, this is the solution!

If you use mdlDB_processSQL() (Update/Insert SQL statements) within mdlDB_openCursorWithID()/mdlDB_fetchRowByID() loop you must turn off auto commit before. Otherwise (auto commit is TRUE by default) you get CURSOR_NOT_OPEN (4075) error code on next (second) mdlDB_fetchRowByID() function because it closes CursorID immediately after mdlDB_processSQL().

Here is an example for Access database.

MS_sqlda    		sqlda;
CursorID			cursorID;
char				sql[1000], sql2[1000];

// lines marked with [*] makes the trick

status = mdlDB_activeAutoCommitMode(FALSE);		// turn off auto commit [*]

if ((status = mdlDB_openCursorWithID(&cursorID, sql)) == SUCCESS)
{
	while ((status = mdlDB_fetchRowByID(&sqlda, cursorID)) == QUERY_NOT_FINISHED)
	{
		// process sqlda.value[0] etc.

        mdlDB_processSQL(sql2); // insert / update SQL statements
	}

	mdlDB_closeCursorByID(cursorID);
	mdlDB_freeSQLDADescriptor(&sqlda);
}

// Below lines are moved to outside if condition according to Jon's comment below.
status = mdlDB_processSQL("COMMIT");		// do a commit [*]
status = mdlDB_activeAutoCommitMode(TRUE);	// turn on auto commit [*]

Sedat Alis said:

you must turn off auto commit before

This thread is several years old, but since you've resurrected it I'll make this comment.

int status = mdlDB_activeAutoCommitMode(false);

You have to remember to restore the commit mode.  However, in procedural code that is often a problem.  In your example, the corresponding restore is inside a scope...

if (condition)
{
   ...
   status = mdlDB_activeAutoCommitMode(true);	// turn on auto commit [*]
}

If that condition fails, then you will have turned off commit mode but never restored it.

C++ Solution

Use a class to wrap the commit/restore function...

• Remove commit in the class constructor
• Restore commit in the class destructor

Here's the class...

struct DbCommitManager
{
  const bool AutoCommitOn {true};
  const bool AutoCommitOff {!AutoCommitOn}
  DbCommitManager ()
  {
    mdlDB_activeAutoCommitMode(AutoCommitOff);
  }
  ~DbCommitManager ()
  {
    mdlDB_activeAutoCommitMode(AutoCommitOn);
  }
};

Use couldn't be simpler...

void SomeSqlMethod ()
{
  // Your method that wants commit off: class constructor performs that task
  DbCommitManager commitMgr;
  if (condition)
  {
    // SQL stuff with commit off
  }
  // DbCommitManager destructor is called automatically on leaving scope.
  // Commit is restored whether or not the condition failed
}

You can use the same idiom for a number of MDL functions that acquire a resource and must subsequently release it.

Jon Summers said:

This thread is several years old, but since you've resurrected it I'll make this comment.

int status = mdlDB_activeAutoCommitMode(false);

You have to remember to restore the commit mode.  However, in procedural code that is often a problem.  In your example, the corresponding restore is inside a scope...

if (condition)
{
   ...
   status = mdlDB_activeAutoCommitMode(true);	// turn on auto commit [*]
}

If that condition fails, then you will have turned off commit mode but never restored it.

You are absolutely right! Turning on auto commit mode within if condition will be a problem. Thank you for the fix. I have fixed the code for anyone copying the code before seeing your comment.