Good Afternoon:
I am relatively new at sqlite and am having a problem with the
sqlite_prepare_v2() statements. My sqlite version is 3.7.8 running under
Windows 7.
After using a sqlite_prepare_v2() statement, if I do a sqlite3_finalize(),
all further sqlite_prepare_v2() calls return a SQLITE_ERROR. If I do not use
sqlite3_finalize(),further sqlite_prepare_v2() statements return SQLITE3_OK.
If I put the sqlite3_finalize() statement just before closing the database,
or do not use the sqlite3_finalize() statement, I can not delete the
database files.
I have investigated all the resources I can find and can not see a solution
to the problem.
Following is my code segment:
void CAppleDevice::GetData5 (CString strTitle, CString strArtist, CString
*strOutFile)
{
CString strLibDB;
CString strLocDB;
sqlite3 *db;
char *zErrMsg = 0;
int rc;
char sCmd [MAX_PATH];
sqlite3_stmt *statement;
unsigned int iRes;
sqlite_uint64 uiVal;
char sLocation[MAX_PATH];
char sBaseLocation[MAX_PATH];
const unsigned char *psBaseLocationID;
const unsigned char *sBaseLocationID;
const unsigned char *psLocation;
const unsigned char *psItemPID;
CString strCmd;
char sFileName [MAX_PATH];
int iRetCode;
strTitle.Replace ("'", "''");
strArtist.Replace ("'", "''");
strLibDB.Format ("%s%s", LOCAL_DB, "MediaLibrary.sqlitedb");
rc = sqlite3_open(strLibDB, &db);
if( rc )
{
MessageBox (NULL, sqlite3_errmsg(db), "Error", MB_OK);
sqlite3_close(db);
}
else
{
sprintf (sCmd, "Select * from item_extra WHERE title = '%s'",
strTitle);
iRes = sqlite3_prepare_v2
(
db, /* Database handle */
sCmd, /* SQL statement, UTF-8 encoded */
strlen (sCmd), /* Maximum length of zSql in
bytes. */
&statement, /* OUT: Statement handle */
NULL /* OUT: Pointer to unused portion of zSql */
);
if (iRes == SQLITE_OK)
{
iRes = sqlite3_step(statement);
if ((iRes == SQLITE_ROW) || (iRes == SQLITE_DONE))
{
psItemPID = sqlite3_column_text (statement, 0);
// column 0 item pid
psLocation = sqlite3_column_text (statement,
7); // location - KYTR.mp3
}
/****** if I use sqlite3_finalize () here, All following sqlite3_prepare_v2
error */
/* If I do not use sqlite3_finalize () here, sqlite3_prepare_v2 does not
error */
// iRes = sqlite3_finalize(statement);
strcpy (sCmd, "Select * from item WHERE item_pid = ");
strcat (sCmd, (char *)psItemPID);
iRes = sqlite3_prepare_v2
(
db, /* Database handle */
sCmd, /* SQL statement, UTF-8 encoded */
strlen (sCmd), /* Maximum length
of zSql in bytes. */
&statement, /* OUT: Statement handle */
NULL /* OUT: Pointer to unused portion of
zSql */
);
if (iRes == SQLITE_OK)
{
iRes = sqlite3_step(statement);
if ((iRes == SQLITE_ROW) || (iRes ==
SQLITE_DONE))
{
psBaseLocationID = sqlite3_column_text
(statement, 27);
}
else
{
strOutFile = NULL;
MessageBox (NULL, "SQL Location Error",
"Error", MB_OK);
}
}
// sqlite3_reset(statement);
strcpy (sCmd, "Select * from base_location WHERE
base_location_id = ");
strcat (sCmd, (char *)psBaseLocationID);
iRes = sqlite3_prepare_v2
(
db, /* Database handle */
sCmd, /* SQL statement, UTF-8 encoded */
strlen (sCmd), /* Maximum length
of zSql in bytes. */
&statement, /* OUT: Statement handle */
NULL /* OUT: Pointer to unused portion of
zSql */
);
if (iRes == SQLITE_OK)
{
iRes = sqlite3_step(statement);
if ((iRes == SQLITE_ROW) || (iRes ==
SQLITE_DONE))
{
sBaseLocationID = sqlite3_column_text
(statement, 1);
strOutFile->Format ("%s\\%s",
sBaseLocationID, psLocation);
strOutFile->Replace ("/", "\\");
}
else
{
strOutFile = NULL;
MessageBox (NULL, "SQL Location Error",
"Error", MB_OK);
}
}
// sqlite3_reset(statement);
}
else
MessageBox (NULL, "Get Data 5 - SQL Library Error",
"Error", MB_OK);
}
/* I wish to remove the databases, but they indicate the file is still in
use if I do not sqlite3_finalize()
after each sqlite3_prepare_v2. */
sscanf (strLibDB,"%s", sFileName);
try
{
CFile::Remove(sFileName);
}
catch (CFileException* pEx)
{
TRACE(_T("File %20s cannot be removed\n"), sFileName);
pEx->Delete();
}
strLibDB.Format ("%s%s", LOCAL_DB, "MediaLibrary.sqlitedb-shm");
sscanf (strLibDB,"%s", sFileName);
try
{
CFile::Remove(sFileName);
}
catch (CFileException* pEx)
{
TRACE(_T("File %20s cannot be removed\n"), sFileName);
pEx->Delete();
}
strLibDB.Format ("%s%s", LOCAL_DB, "MediaLibrary.sqlitedb-wal");
sscanf (strLibDB,"%s", sFileName);
try
{
CFile::Remove(sFileName);
}
catch (CFileException* pEx)
{
TRACE(_T("File %20s cannot be removed\n"), sFileName);
pEx->Delete();
}
}
_____________________________________________________
Thank you in advance for your time.
John Markavitch
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
