drop statement returns SQLITE_ERROR

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

drop statement returns SQLITE_ERROR

Preston Zaugg
I have some updates to the schema of my sqlite database that i am trying to
apply. When i run these statements from the command line util they run fine.
when i run them from the API I get a SQLITE_ERROR on the statement that
drops a table. These statements are selected out of an attached database and
executed against the default database.

The code runs the following statements:
1 drop views (send in all "drop view" statements at once)
2 drop table (statement text: "DROP TABLE Specific_Product;")
3 create new table and insert seed data (this step is repeated for multiple
tables)
4 create new view (repeated for multiple views)

step 2 returns a SQLITE_ERROR, but the other statements that don't rely on
the table being dropped run fine if i ignore the error.


Here is the code i am running:

rc = sqlite3_step(pListStmt);
while( rc == SQLITE_ROW && ok)
{
   sqlite3_prepare(pDb, (const char*)sqlite3_column_text(pListStmt, 0), -1,
&pUpdateStmt, 0);
   rc = sqlite3_step(pUpdateStmt);
   ok = rc == SQLITE_OK || rc == SQLITE_ROW || rc == SQLITE_DONE;
   sqlite3_finalize(pUpdateStmt);

   rc = sqlite3_step(pListStmt);
}


Any help would be greatly appreciated.
--Preston


Reply | Threaded
Open this post in threaded view
|

Re: drop statement returns SQLITE_ERROR

D. Richard Hipp
"Preston Zaugg" <[hidden email]> wrote:

> I have some updates to the schema of my sqlite database that i am trying to
> apply. When i run these statements from the command line util they run fine.
> when i run them from the API I get a SQLITE_ERROR on the statement that
> drops a table. These statements are selected out of an attached database and
> executed against the default database.
>
> The code runs the following statements:
> 1 drop views (send in all "drop view" statements at once)
> 2 drop table (statement text: "DROP TABLE Specific_Product;")
> 3 create new table and insert seed data (this step is repeated for multiple
> tables)
> 4 create new view (repeated for multiple views)
>
> step 2 returns a SQLITE_ERROR, but the other statements that don't rely on
> the table being dropped run fine if i ignore the error.
>

This is probably an SQLITE_SCHEMA error.  Rerun sqlite3_prepare() and
sqlite3_step() and it should work the second time.

This particular SQLITE_SCHEMA error seems unnecessary though.  Looks
like sqlite3_step() could probably do a better job of preventing it.
I'll have a look and see what I can find....
--
D. Richard Hipp <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: drop statement returns SQLITE_ERROR

Preston Zaugg
>"Preston Zaugg" <[hidden email]> wrote:
> > I have some updates to the schema of my sqlite database that i am trying
>to
> > apply. When i run these statements from the command line util they run
>fine.
> > when i run them from the API I get a SQLITE_ERROR on the statement that
> > drops a table. These statements are selected out of an attached database
>and
> > executed against the default database.
> >
> > The code runs the following statements:
> > 1 drop views (send in all "drop view" statements at once)
> > 2 drop table (statement text: "DROP TABLE Specific_Product;")
> > 3 create new table and insert seed data (this step is repeated for
>multiple
> > tables)
> > 4 create new view (repeated for multiple views)
> >
> > step 2 returns a SQLITE_ERROR, but the other statements that don't rely
>on
> > the table being dropped run fine if i ignore the error.
> >
>
>This is probably an SQLITE_SCHEMA error.  Rerun sqlite3_prepare() and
>sqlite3_step() and it should work the second time.
>
>This particular SQLITE_SCHEMA error seems unnecessary though.  Looks
>like sqlite3_step() could probably do a better job of preventing it.
>I'll have a look and see what I can find....
>--
>D. Richard Hipp <[hidden email]>
>

It actually seems to be returning the SQLITE_ERROR code (1) with the message
"SQL logic error or missing database". I re-ordered my statements for a
quick test and had the drop statement run first and i still get the error. I
tried to create a dummy table with just one dummy row to see if it was
something about this table, but that drop statement would run either.

Could it have something to do with the fact that i have a second database
attached?


Reply | Threaded
Open this post in threaded view
|

Re: drop statement returns SQLITE_ERROR

D. Richard Hipp
In reply to this post by Preston Zaugg
"Preston Zaugg" <[hidden email]> wrote:

> >
> >This is probably an SQLITE_SCHEMA error.  Rerun sqlite3_prepare() and
> >sqlite3_step() and it should work the second time.
> >
> >This particular SQLITE_SCHEMA error seems unnecessary though.  Looks
> >like sqlite3_step() could probably do a better job of preventing it.
> >I'll have a look and see what I can find....
> >--
> >D. Richard Hipp <[hidden email]>
> >
>
> It actually seems to be returning the SQLITE_ERROR code (1) with the message
> "SQL logic error or missing database". I re-ordered my statements for a
> quick test and had the drop statement run first and i still get the error. I
> tried to create a dummy table with just one dummy row to see if it was
> something about this table, but that drop statement would run either.
>
> Could it have something to do with the fact that i have a second database
> attached?

Can you provide a specific test case that fails using the
command-line client?
--
D. Richard Hipp <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: drop statement returns SQLITE_ERROR

Preston Zaugg
In reply to this post by D. Richard Hipp
Here is the full source to a stripped down version of the app i am truing to
debug. It exhibits the problem. to recreat this problem simply create a
target.bin and add a table t1, then create a database update.bin with the
table
adhoc_sql(adhocsqlid int, sqltext text) and insert the values (1, 'drop
table t1;')

I'm sure it is something in my code... i'm just not sure what. I really do
appreciate the time that anybody puts into this.. i know it isn't a quick
question.
Thanks again
--Preston

-------------------------
#include "sqlite/sqlite3.h"
#include <stdio.h>
#include <string.h>

bool RunAdHoc(sqlite3** ppDb, char* SqlStatementList)
{
 int rc;
 sqlite3_stmt *pListStmt;
 sqlite3_stmt *pUpdateStmt;
 sqlite3* pDb = *ppDb;
 bool ok = true;

 sqlite3_prepare(pDb, SqlStatementList, -1, &pListStmt, 0);
 rc = sqlite3_step(pListStmt);
 while( rc == SQLITE_ROW && ok)
 {
  sqlite3_prepare(pDb, (const char*)sqlite3_column_text(pListStmt, 0), -1,
&pUpdateStmt, 0);
  rc = sqlite3_step(pUpdateStmt);
  ok = rc == SQLITE_OK || rc == SQLITE_ROW || rc == SQLITE_DONE;
  sqlite3_finalize(pUpdateStmt);

  rc = sqlite3_step(pListStmt);
 }
 sqlite3_finalize(pListStmt);

 return ok;
}

int main(void)
{
 int rc;
 sqlite3 *pDb;
 sqlite3_stmt *pListStmt;
 char tmpbuff[200];

 sqlite3_open( "target.bin", &pDb );

 sprintf(tmpbuff, "attach database 'update.bin' as patch");

 sqlite3_prepare(pDb, tmpbuff, -1, &pListStmt, 0);
 rc = sqlite3_step(pListStmt);
 sqlite3_finalize(pListStmt);

 sqlite3_prepare(pDb, "begin transaction;", -1, &pListStmt, 0);
 rc = sqlite3_step(pListStmt);
 sqlite3_finalize(pListStmt);

 bool adHocOK = RunAdHoc(&pDb, "Select sqltext from patch.adhoc_sql order by
adhocsqlid");
 if(!adHocOK )
 {
  sqlite3_prepare(pDb, "rollback transaction;", -1, &pListStmt, 0);
 }
 else
 {
  sqlite3_prepare(pDb, "commit transaction;", -1, &pListStmt, 0);
 }

 rc = sqlite3_step(pListStmt);
 sqlite3_finalize(pListStmt);
 sqlite3_close(pDb);
}

----- Original Message -----
From: <[hidden email]>
To: <[hidden email]>
Sent: Thursday, October 13, 2005 4:21 PM
Subject: Re: [sqlite] drop statement returns SQLITE_ERROR


"Preston" <[hidden email]> wrote:

> I have some updates to the schema of my sqlite database that i am trying
> to
> apply. When i run these statements from the command line util they run
> fine.
> when i run them from the API I get a SQLITE_ERROR on the statement that
> drops a table. These statements are selected out of an attached database
> and
> executed against the default database.
>
> The code runs the following statements:
> 1 drop views (send in all "drop view" statements at once)
> 2 drop table (statement text: "DROP TABLE Specific_Product;")
> 3 create new table and insert seed data (this step is repeated for
> multiple
> tables)
> 4 create new view (repeated for multiple views)
>
> step 2 returns a SQLITE_ERROR, but the other statements that don't rely on
> the table being dropped run fine if i ignore the error.
>

This is probably an SQLITE_SCHEMA error.  Rerun sqlite3_prepare() and
sqlite3_step() and it should work the second time.

This particular SQLITE_SCHEMA error seems unnecessary though.  Looks
like sqlite3_step() could probably do a better job of preventing it.
I'll have a look and see what I can find....
--
D. Richard Hipp <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: drop statement returns SQLITE_ERROR

Robert Simpson
----- Original Message -----
From: "Preston" <[hidden email]>
To: <[hidden email]>
Sent: Friday, October 14, 2005 1:21 PM
Subject: Re: [sqlite] drop statement returns SQLITE_ERROR


> Here is the full source to a stripped down version of the app i am truing
> to
> debug. It exhibits the problem. to recreat this problem simply create a
> target.bin and add a table t1, then create a database update.bin with the
> table
> adhoc_sql(adhocsqlid int, sqltext text) and insert the values (1, 'drop
> table t1;')
>
> I'm sure it is something in my code... i'm just not sure what. I really do
> appreciate the time that anybody puts into this.. i know it isn't a quick
> question.
> Thanks again
> --Preston
>
> -------------------------
> #include "sqlite/sqlite3.h"
> #include <stdio.h>
> #include <string.h>
>
> bool RunAdHoc(sqlite3** ppDb, char* SqlStatementList)
> {
> int rc;
> sqlite3_stmt *pListStmt;
> sqlite3_stmt *pUpdateStmt;
> sqlite3* pDb = *ppDb;
> bool ok = true;
>
> sqlite3_prepare(pDb, SqlStatementList, -1, &pListStmt, 0);
> rc = sqlite3_step(pListStmt);
> while( rc == SQLITE_ROW && ok)
> {
>  sqlite3_prepare(pDb, (const char*)sqlite3_column_text(pListStmt, 0), -1,
> &pUpdateStmt, 0);
>  rc = sqlite3_step(pUpdateStmt);
>  ok = rc == SQLITE_OK || rc == SQLITE_ROW || rc == SQLITE_DONE;
>  sqlite3_finalize(pUpdateStmt);

You have a prepared statement stepping in a loop (locking the database for
read-only access), and inside that loop you execute a statement that changes
the database schema, thereby invalidating the outer prepared statement.  You
need to read in all the statements first into an array, finalize, then loop
through the array and execute all the statements.  Either that, or you need
to open a new connection to execute the inner statements on.

Robert


Reply | Threaded
Open this post in threaded view
|

Re: drop statement returns SQLITE_ERROR

Preston Zaugg
You right... i had even been following a similar thread and didn't realize
that it was my problem too.. thanks a ton!
--preston

----- Original Message -----
From: "Robert Simpson" <[hidden email]>
To: <[hidden email]>
Sent: Friday, October 14, 2005 2:55 PM
Subject: Re: [sqlite] drop statement returns SQLITE_ERROR


> ----- Original Message -----
> From: "Preston" <[hidden email]>
> To: <[hidden email]>
> Sent: Friday, October 14, 2005 1:21 PM
> Subject: Re: [sqlite] drop statement returns SQLITE_ERROR
>
>
>> Here is the full source to a stripped down version of the app i am truing
>> to
>> debug. It exhibits the problem. to recreat this problem simply create a
>> target.bin and add a table t1, then create a database update.bin with the
>> table
>> adhoc_sql(adhocsqlid int, sqltext text) and insert the values (1, 'drop
>> table t1;')
>>
>> I'm sure it is something in my code... i'm just not sure what. I really
>> do
>> appreciate the time that anybody puts into this.. i know it isn't a quick
>> question.
>> Thanks again
>> --Preston
>>
>> -------------------------
>> #include "sqlite/sqlite3.h"
>> #include <stdio.h>
>> #include <string.h>
>>
>> bool RunAdHoc(sqlite3** ppDb, char* SqlStatementList)
>> {
>> int rc;
>> sqlite3_stmt *pListStmt;
>> sqlite3_stmt *pUpdateStmt;
>> sqlite3* pDb = *ppDb;
>> bool ok = true;
>>
>> sqlite3_prepare(pDb, SqlStatementList, -1, &pListStmt, 0);
>> rc = sqlite3_step(pListStmt);
>> while( rc == SQLITE_ROW && ok)
>> {
>>  sqlite3_prepare(pDb, (const char*)sqlite3_column_text(pListStmt, 0), -1,
>> &pUpdateStmt, 0);
>>  rc = sqlite3_step(pUpdateStmt);
>>  ok = rc == SQLITE_OK || rc == SQLITE_ROW || rc == SQLITE_DONE;
>>  sqlite3_finalize(pUpdateStmt);
>
> You have a prepared statement stepping in a loop (locking the database for
> read-only access), and inside that loop you execute a statement that
> changes the database schema, thereby invalidating the outer prepared
> statement.  You need to read in all the statements first into an array,
> finalize, then loop through the array and execute all the statements.
> Either that, or you need to open a new connection to execute the inner
> statements on.
>
> Robert
>
>
>