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: [email protected]
>> > 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]
-----------------------------------------------------------------------------
