More ODBC Trouble

[headedtomexico]

Man, I hate having to get you guys to hold my hand through all this, but I've run into another problem. I have my ODBC connecting to a MS access database. This is all coded into a library that gets injected into a video game, and then macro commands call the functions that connect to the database.

After I got it all set up, and wrote a small little test macro which was a loop with a 1 millisecond delay in it, and a function call to the database it totally craps the game out. The ODBC stuff keeps program control while doing the query, and i guess since its injected into the game, it freezes the game for about 1/10th to 1/100th of a second. But this function is to be utilized in a loop... so it just bogs the shit out of the game..... anyone have any ideas on this?

Is there a way to fix that? It would be nice to do the query and have it immediately come back out of the function without waiting for the return... then setup my own loop to wait for it.

I'm not 100% sure my assumption of the problem is correct. Posting the function below with some of the non pertinant stuff left out. For my purposes I only wanted 1 value return so there is no result set loop, and it only links 1 column.

I looped it with a query of "SELECT COUNT(*) WHERE name = 'bob';"

What it looked like in the game it was injected in was as if my framerate dropped to 2 or 3.

bool QueryOneCol(PCHAR query, PCHAR result) {
  char szDSN[256];
  HENV    hEnv;
  HDBC    hDbc;
  RETCODE rc;
  int     iConnStrLength2Ptr;
  char    szConnStrOut[256];
  SQLCHAR         RetCol[128];
  int             ret;
  SQLSMALLINT     fieldCount = 0;
  HSTMT           hStmt;
  
  sprintf(szDSN,"Driver={Microsoft Access Driver (*.mdb)};DSN='';DBQ=%s\\%s;",gszINIPath,dbfilename);

  rc = SQLAllocEnv(&hEnv);
  rc = SQLAllocConnect(hEnv, &hDbc);
  rc = SQLDriverConnect(hDbc, NULL, (unsigned char*)szDSN,SQL_NTS, (unsigned char*)szConnStrOut,255, (SQLSMALLINT*)&iConnStrLength2Ptr, SQL_DRIVER_NOPROMPT);
  if (!SQL_SUCCEEDED(rc)) return false;

  rc = SQLAllocStmt(hDbc,&hStmt);
  rc = SQLPrepare(hStmt, (SQLCHAR*)query, SQL_NTS);
  rc = SQLBindCol(hStmt, 1, SQL_C_CHAR, RetCol, 128,(SQLINTEGER*)&ret);
  rc = SQLExecute(hStmt);
  if (!SQL_SUCCEEDED(rc)) return false;

  SQLNumResultCols(hStmt, &fieldCount);
  if (fieldCount == 0) return false;

  rc = SQLFetch(hStmt);
  strcpy(result,(PCHAR)RetCol);
  rc = SQLFreeStmt(hStmt, SQL_DROP);
  SQLDisconnect(hDbc);
  SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
  SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
  return true;
}

[kvprajapati]

headedtomexico,
Incorrect select query - FROM clause is missing.

SELECT COUNT(*) FROM TableName WHERE name = 'bob'

[headedtomexico]

sorry yeah, was a typo in post, it was correct in the code. I'm successfully getting values back from the database so the query is formatted ok.

here is a cut n paste of the query used for the function call. sprintf(query,"SELECT COUNT(*) FROM creats WHERE name = '%s';",Name);

[Ancient Dragon]

>>so it just bogs the shit out of the game..... anyone have any ideas on this?
Yes I know. Database access is always slow. Maybe you need to shove the database access code in another thread so that it doesn't affect the game. And your problem will only get worse as more people try to play the game at the same time. MSAccess is not a very good database when more than a couple people try to use it at the same time. If you want more people when swap Access out for something like MySQL.

[Laiq Ahmed]

Why don't you keep the connection level stuff at the application/module level rather than within the function, so when the game started the delay is bearable.

follow anciant dragon's advice and try using the concept of database pooling as well, these together will increase your frame-rate back to the one you've configured.

[headedtomexico]

I'll have to check out the multithreading stuff, i've never really messed with it, but that sounds like the answer. The reason for using msaccess is so the users don't have to install any software. The software this is bundled in just comes packaged in a zip file, so I wanted to just bundle the DB as a stand alone file to avoid the users needing to install or understand a db server.

I chopped that routine up, and not on loading the DLL it connects to the DB, and stays connected, then on unload it disconnects. So when a query is done it sped it up enough that the game doesnt get choppy, but I'm worried about the side effects of doing it like this. Such as unexpected termination leaving the connection open....

As far as multiple users they may have multiple instances of the game open and connecting... will that have a negative effect?

[Laiq Ahmed]

basically whenever process is shutdown windows releases all the memory allocated by it, the problem regarding memory leak appears whenever the application kept on running and own the unreferenced memory.

further you can simulate the scenario by creating an independent application and causing a crash, division by zero etc. and check, you can run multiple instances of this application to figure out the behavior.

basically what you are trying to acheive is the static flavour for connection and you know the life time of static variable. Just close the connection and release all the resources if you just wanted to query once or you know the point after which no more DB interaction is required, it all depends on the logic.

If the multiple instances of the same process are open, then each would have the different copy of handles so don't need to worry about them.

[Hope This Helps :)]

[Ancient Dragon]

>>but I'm worried about the side effects of doing it like this. Such as unexpected termination leaving the connection open....

That isn't a problem because the connection will close then the application closes. A much more real problem is what happens if the connection accidentally closes while the program is running.