In fact, it uses to be N seperate execs and I consolidated it in an effort to fix the problem. It seems like under certain circumstances, my code fails to create a table but does not report it as an error.
// strTmp is filled with other CREATE TABLE commands before this, strTmp.Format(L"CREATE TABLE %s (" L"%s int," // Ticket ID L"%s int," // Group ID L"%s int," // Field ID L"%s text," // Value L"PRIMARY KEY (%s, %s, %s)" L");", TDB_CUSTOMFIELDDATATABLE, TDB_COL_TICKETID, TDB_COL_GROUPID, TDB_COL_FIELDID, TDB_COL_VALUE, TDB_COL_TICKETID, TDB_COL_GROUPID, TDB_COL_FIELDID); strSQL += strTmp; // It is all executed here if (!ExecuteSQLStatements(strSQL.GetBuffer(), &sql, NULL, false)) { CEString strMsg; strMsg.Format(L"Unable to create database! Error: %d, '%s'", sql.GetErrorCode(), sql.GetErrorMsg()); MessageBox(g_hWnd, strMsg.GetBuffer(), L"Error", MB_OK | MB_ICONSTOP); return false; } // This is ExecuteSQLStatements: bool KayakoMobile::ExecuteSQLStatements(const wchar_t *pwszSQL, kce::SQLite *pSQLite, SQLiteRowHandler *pHandler /*= NULL*/, bool bContinueOnFail /*= true*/) { bool r = false; if (pSQLite && pSQLite->IsOpen()) { if (pwszSQL && *pwszSQL) { kce::SQLiteStatement *pStmt = new kce::SQLiteStatement; if (!pStmt) { #ifdef DEBUG HandleAllocFailure(L"ExecuteSQLStatements", sizeof(SQLiteStatement)); #else HandleAllocFailure(sizeof(SQLiteStatement)); #endif return false; } else { int nCode = SQLITE_OK; int nRetry = 0; const wchar_t *pLeftOver = NULL; while (SQLITE_OK == nCode || (SQLITE_SCHEMA == nCode && ((++nRetry) < 2))) { if (!*pwszSQL) { break; } nCode = pSQLite->Prepare(pwszSQL, &pStmt, -1, &pLeftOver); // Check to see if this prepare succeeded. if (SQLITE_OK != nCode) { pStmt->Finalize(); if (SQLITE_ERROR != nCode) { // Could be busy, so we'll retry if (pStmt) { continue; } } else { // There was an error parsing this statement; let's skip to the next one. #ifdef DEBUG_DB kce::odsf(L"ExecuteSQLStatements() : SQLite::Prepare() Failed! Err = {#%d, '%s'} SQL: '%s'\n", nCode, pSQLite->GetErrorMsg(), pwszSQL); #endif // !DEBUG_DB const wchar_t *pwszToken = wcsstr(pwszSQL, L";"); if (pwszToken && *pwszToken + 1) { // We want to print the statement that failed for observation kce::CEString strFailed; size_t nSizeFailed = (pwszToken - pwszSQL); wchar_t *pwszBuf = strFailed.GetMutableBuffer(nSizeFailed + 1); #ifdef DEBUG_DB if (pwszBuf) { wcsncpy(pwszBuf, pwszSQL, nSizeFailed); odsf(L"%s\n", pwszBuf); } #endif if (!bContinueOnFail) { r = false; break; } pwszSQL = pwszToken + 1; nCode = SQLITE_OK; continue; } else { // No more statements; bail. continue; } } } if (!pStmt) { // This happens for a comment or whitespace pwszSQL = pLeftOver; continue; } // Now step to execute each statement bool bFailed = false; int nCode = SQLITE_ERROR; while (pStmt->Step(bFailed, &nCode)) { if (pHandler) { pHandler->OnRowReady(pStmt); } } r = !bFailed; pStmt->Reset(); if (r) { nCode = SQLITE_OK; nRetry = 0; pwszSQL = pLeftOver; while (iswspace(pwszSQL[0])) { pwszSQL++; } } else { pStmt->Finalize(); pStmt->Set(NULL); } #ifdef DEBUG_DB if (bFailed) { kce::odsf(L"ExecuteSQLStatements() : SQLiteStatement::Step() Failed! Err {#%d, '%s'}\n", nCode, pSQLite->GetErrorMsg()); } #endif } delete pStmt; } } } return r; } On 7/6/07, Teg <[EMAIL PROTECTED]> wrote:
Hello Ryan, Why not make N distinct Execs to create the tables and see if it works? Seems like simplification is the way to trouble-shoot this. Friday, July 6, 2007, 7:08:09 PM, you wrote: RML> James, RML> Nope. And actually, it's always a different table that fails to get RML> created. I know that (usually) all the tables get created RML> successfully. It seems plausible that it's a low-memory condition RML> that causes it, seeing as this code is running on PDAs with very very RML> little memory. RML> I will post my code here if necessary. RML> On 7/6/07, James Dennett <[EMAIL PROTECTED]> wrote: >> > -----Original Message----- >> > From: Ryan M. Lederman [mailto:[EMAIL PROTECTED] >> > Sent: Friday, July 06, 2007 3:34 PM >> > To: sqlite-users@sqlite.org >> > Subject: [sqlite] CREATE TABLE >> > >> > I'm using sqlite3, built with Microsoft's CL compiler, 8.0 on a >> > Windows Mobile platform. >> > >> > I have a problem wherein I issue several CREATE TABLE commands >> > concatenated together, semi-colon delimited. It appears, from the >> > feedback from my customers, that one or more table(s) fail to get >> > created, but the database engine reports no failures when executing >> > the CREATE TABLE statement. I know this because later queries will >> > fail with messages such as "no such table: foo" >> > >> > Has anyone ever seen this before? Am I doing something wrong, or is >> > this a known bug? Any help would be greatly appreciated. >> >> Without seeing your code, it's hard to say, but it seems plausible that >> you didn't actually execute all of the statements. Is only the first >> table getting created? >> >> -- James >> >> ----------------------------------------------------------------------------- >> To unsubscribe, send email to [EMAIL PROTECTED] >> ----------------------------------------------------------------------------- >> >> -- Best regards, Teg mailto:[EMAIL PROTECTED] ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
-- Ryan M. Lederman ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------