Re: [sqlite] SQL Lite 2.8.6 Not deleting Enteries
Also I've just noticed the title of this thread says that you use SQLite 2.8.6. Are you sure you are using SQLite that ancient both in your app and in SQLShell? And that's on a super-modern OS Windows 8? On Tue, Jun 19, 2012 at 12:37 PM, Black, Michael (IS) wrote: > Or..the string being displayed in your app isn't being reset to empty when > there are no records. > > Since you didn't mention what happens when you delete just some of the > records instead of all. > > > > Michael D. Black > > Senior Scientist > > Advanced Analytics Directorate > > Advanced GEOINT Solutions Operating Unit > > Northrop Grumman Information Systems > > > From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on > behalf of Pavel Ivanov [paiva...@gmail.com] > Sent: Tuesday, June 19, 2012 11:25 AM > To: chris_how...@mcafee.com > Cc: General Discussion of SQLite Database > Subject: EXT :Re: [sqlite] SQL Lite 2.8.6 Not deleting Enteries > > Then it's definitely different database files or indeed some really > weird network disk caching. > > Pavel > > > On Tue, Jun 19, 2012 at 12:19 PM, wrote: >> Good question. I thought the same, the issue persists across not just an >> app restart but a restart of the entire OS. >> >> Cheers >> Chris >> >> -Original Message- >> From: Pavel Ivanov [mailto:paiva...@gmail.com] >> Sent: Tuesday, June 19, 2012 12:17 PM >> To: Howell, Chris >> Cc: sqlite-users@sqlite.org >> Subject: Re: [sqlite] SQL Lite 2.8.6 Not deleting Enteries >> >> It seems to me you are using SQLite database in WAL-mode and when you delete >> rows from SQLShell your app has some read-only transaction open (or it has >> already started executing its SELECT statement that should return deleted >> rows). Does the issue persist across app restarts? >> >> Pavel >> >> On Tue, Jun 19, 2012 at 11:38 AM, wrote: >>> >>> -----Original Message- >>> From: sqlite-users-boun...@sqlite.org >>> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov >>> Sent: Tuesday, June 19, 2012 11:27 AM >>> To: General Discussion of SQLite Database >>> Subject: Re: [sqlite] SQL Lite 2.8.6 Not deleting Enteries >>> >>>> 4. Try to select against any of these tables and 0 results are >>>> returned yet, the client db hasn't actually changed it's size. >>> >>> By default SQLite doesn't shrink database file size when deleting data, >>> although it reuses this space later when you insert new data. If you want >>> to shrink file size you need to use VACUUM or PRAGMA auto_vacuum. >>> >>> [CH] - Good to know Thanks >>> >>> >>>> 5. When debugging the issue, the app still reports all entries via >>>> SQL query that I, thought were deleted from within SqlShell. >>> >>> Did you issue COMMIT in your SqlShell? If you close database in SqlShell >>> and then reopen again will it still report no rows in the database? >>> >>> [CH] - Yes if I close the SQLShell and re-open it still reports 0 rows. >>> >>>> When I place a debug Message box to display the messages selected from the >>>> client db I see all the messages even though a select statement against >>>> the tables returns 0 results. >>> >>> I wonder what did you mean to say here. If SELECT statement returns 0 rows >>> you can't show any messages unless you get it from somewhere else. >>> >>> [CH] - What I meant to say is. As follows when debugging the application, I >>> place a debug statement to show me the query and the results, when the >>> client app. Executes it's statement there are valid results that are >>> returned. I know it stands to reason that, logically the results must be >>> coming from somewhere else if a select statement in the SQLShell reports 0 >>> results, but the same select stmt exec'd within the context of the app >>> actually returns results. However how do then explain that when copying an >>> empty database in place of the one being currently queried that both the >>> app and the SQLShell report the same thing ? >>> >>>> I know I am accessing the same database, because if I copy a completely >>>> empty database, to where the client db is. Then the app reports 0 records. >>> >>> And after doing that if you insert some rows from SqlShell will the app >>> repo
Re: [sqlite] SQL Lite 2.8.6 Not deleting Enteries
Or..the string being displayed in your app isn't being reset to empty when there are no records. Since you didn't mention what happens when you delete just some of the records instead of all. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Pavel Ivanov [paiva...@gmail.com] Sent: Tuesday, June 19, 2012 11:25 AM To: chris_how...@mcafee.com Cc: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] SQL Lite 2.8.6 Not deleting Enteries Then it's definitely different database files or indeed some really weird network disk caching. Pavel On Tue, Jun 19, 2012 at 12:19 PM, wrote: > Good question. I thought the same, the issue persists across not just an app > restart but a restart of the entire OS. > > Cheers > Chris > > -Original Message- > From: Pavel Ivanov [mailto:paiva...@gmail.com] > Sent: Tuesday, June 19, 2012 12:17 PM > To: Howell, Chris > Cc: sqlite-users@sqlite.org > Subject: Re: [sqlite] SQL Lite 2.8.6 Not deleting Enteries > > It seems to me you are using SQLite database in WAL-mode and when you delete > rows from SQLShell your app has some read-only transaction open (or it has > already started executing its SELECT statement that should return deleted > rows). Does the issue persist across app restarts? > > Pavel > > On Tue, Jun 19, 2012 at 11:38 AM, wrote: >> >> -Original Message- >> From: sqlite-users-boun...@sqlite.org >> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov >> Sent: Tuesday, June 19, 2012 11:27 AM >> To: General Discussion of SQLite Database >> Subject: Re: [sqlite] SQL Lite 2.8.6 Not deleting Enteries >> >>> 4. Try to select against any of these tables and 0 results are >>> returned yet, the client db hasn't actually changed it's size. >> >> By default SQLite doesn't shrink database file size when deleting data, >> although it reuses this space later when you insert new data. If you want to >> shrink file size you need to use VACUUM or PRAGMA auto_vacuum. >> >> [CH] - Good to know Thanks >> >> >>> 5. When debugging the issue, the app still reports all entries via SQL >>> query that I, thought were deleted from within SqlShell. >> >> Did you issue COMMIT in your SqlShell? If you close database in SqlShell and >> then reopen again will it still report no rows in the database? >> >> [CH] - Yes if I close the SQLShell and re-open it still reports 0 rows. >> >>> When I place a debug Message box to display the messages selected from the >>> client db I see all the messages even though a select statement against the >>> tables returns 0 results. >> >> I wonder what did you mean to say here. If SELECT statement returns 0 rows >> you can't show any messages unless you get it from somewhere else. >> >> [CH] - What I meant to say is. As follows when debugging the application, I >> place a debug statement to show me the query and the results, when the >> client app. Executes it's statement there are valid results that are >> returned. I know it stands to reason that, logically the results must be >> coming from somewhere else if a select statement in the SQLShell reports 0 >> results, but the same select stmt exec'd within the context of the app >> actually returns results. However how do then explain that when copying an >> empty database in place of the one being currently queried that both the app >> and the SQLShell report the same thing ? >> >>> I know I am accessing the same database, because if I copy a completely >>> empty database, to where the client db is. Then the app reports 0 records. >> >> And after doing that if you insert some rows from SqlShell will the app >> report any records? If not or if database reopening in SqlShell (which was >> suggested above) still results in different number of records in app and in >> SqlShell then you definitely use different database files. >> >> [CH] - Correct after doing that if I insert records via the SQLShell the app >> will report the records & the shell and app report the equal number of >> records. >> >> >> Chris >> >> >> Pavel >> >> >> On Tue, Jun 19, 2012 at 11:09 AM, wrote: >>> I've got a bit of an interesting issue that I am hoping to get some help >>> on. The issue goes a little bit like t
Re: [sqlite] SQL Lite 2.8.6 Not deleting Enteries
I thinki you need a sanity check. Either your own or somebody looking over your shoulder. If you reboot the OS and it sill has records when you think (and see) 0 records then, in all high-probability likelihood, you are doing something wrong. The idea that you can add records and they show up, but deleting them doesn't make them disappear is completely illogical. You're saying this sequence ocurrs? #1 Start app #2 Get records (N records show up) #3 From shell delete records and commit #4 Refress app window (N records still show up) #5 From shell add new M records and commit #6 Refresh app windows (M records now show up). To test this ensure M < N so you can see if you get some old N records still show up. i.e. 10 N records, 5 M records, 10 M+N records show up. In which case your problem is in your app. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Pavel Ivanov [paiva...@gmail.com] Sent: Tuesday, June 19, 2012 11:25 AM To: chris_how...@mcafee.com Cc: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] SQL Lite 2.8.6 Not deleting Enteries Then it's definitely different database files or indeed some really weird network disk caching. Pavel On Tue, Jun 19, 2012 at 12:19 PM, wrote: > Good question. I thought the same, the issue persists across not just an app > restart but a restart of the entire OS. > > Cheers > Chris > > -Original Message- > From: Pavel Ivanov [mailto:paiva...@gmail.com] > Sent: Tuesday, June 19, 2012 12:17 PM > To: Howell, Chris > Cc: sqlite-users@sqlite.org > Subject: Re: [sqlite] SQL Lite 2.8.6 Not deleting Enteries > > It seems to me you are using SQLite database in WAL-mode and when you delete > rows from SQLShell your app has some read-only transaction open (or it has > already started executing its SELECT statement that should return deleted > rows). Does the issue persist across app restarts? > > Pavel > > On Tue, Jun 19, 2012 at 11:38 AM, wrote: >> >> -Original Message- >> From: sqlite-users-boun...@sqlite.org >> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov >> Sent: Tuesday, June 19, 2012 11:27 AM >> To: General Discussion of SQLite Database >> Subject: Re: [sqlite] SQL Lite 2.8.6 Not deleting Enteries >> >>> 4. Try to select against any of these tables and 0 results are >>> returned yet, the client db hasn't actually changed it's size. >> >> By default SQLite doesn't shrink database file size when deleting data, >> although it reuses this space later when you insert new data. If you want to >> shrink file size you need to use VACUUM or PRAGMA auto_vacuum. >> >> [CH] - Good to know Thanks >> >> >>> 5. When debugging the issue, the app still reports all entries via SQL >>> query that I, thought were deleted from within SqlShell. >> >> Did you issue COMMIT in your SqlShell? If you close database in SqlShell and >> then reopen again will it still report no rows in the database? >> >> [CH] - Yes if I close the SQLShell and re-open it still reports 0 rows. >> >>> When I place a debug Message box to display the messages selected from the >>> client db I see all the messages even though a select statement against the >>> tables returns 0 results. >> >> I wonder what did you mean to say here. If SELECT statement returns 0 rows >> you can't show any messages unless you get it from somewhere else. >> >> [CH] - What I meant to say is. As follows when debugging the application, I >> place a debug statement to show me the query and the results, when the >> client app. Executes it's statement there are valid results that are >> returned. I know it stands to reason that, logically the results must be >> coming from somewhere else if a select statement in the SQLShell reports 0 >> results, but the same select stmt exec'd within the context of the app >> actually returns results. However how do then explain that when copying an >> empty database in place of the one being currently queried that both the app >> and the SQLShell report the same thing ? >> >>> I know I am accessing the same database, because if I copy a completely >>> empty database, to where the client db is. Then the app reports 0 records. >> >> And after doing that if you insert some rows from SqlShell will the app >> report any records? If not or if database reopening in SqlShell (which was >> suggested above) still
Re: [sqlite] SQL Lite 2.8.6 Not deleting Enteries
Then it's definitely different database files or indeed some really weird network disk caching. Pavel On Tue, Jun 19, 2012 at 12:19 PM, wrote: > Good question. I thought the same, the issue persists across not just an app > restart but a restart of the entire OS. > > Cheers > Chris > > -Original Message- > From: Pavel Ivanov [mailto:paiva...@gmail.com] > Sent: Tuesday, June 19, 2012 12:17 PM > To: Howell, Chris > Cc: sqlite-users@sqlite.org > Subject: Re: [sqlite] SQL Lite 2.8.6 Not deleting Enteries > > It seems to me you are using SQLite database in WAL-mode and when you delete > rows from SQLShell your app has some read-only transaction open (or it has > already started executing its SELECT statement that should return deleted > rows). Does the issue persist across app restarts? > > Pavel > > On Tue, Jun 19, 2012 at 11:38 AM, wrote: >> >> -Original Message- >> From: sqlite-users-boun...@sqlite.org >> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov >> Sent: Tuesday, June 19, 2012 11:27 AM >> To: General Discussion of SQLite Database >> Subject: Re: [sqlite] SQL Lite 2.8.6 Not deleting Enteries >> >>> 4. Try to select against any of these tables and 0 results are >>> returned yet, the client db hasn't actually changed it's size. >> >> By default SQLite doesn't shrink database file size when deleting data, >> although it reuses this space later when you insert new data. If you want to >> shrink file size you need to use VACUUM or PRAGMA auto_vacuum. >> >> [CH] - Good to know Thanks >> >> >>> 5. When debugging the issue, the app still reports all entries via SQL >>> query that I, thought were deleted from within SqlShell. >> >> Did you issue COMMIT in your SqlShell? If you close database in SqlShell and >> then reopen again will it still report no rows in the database? >> >> [CH] - Yes if I close the SQLShell and re-open it still reports 0 rows. >> >>> When I place a debug Message box to display the messages selected from the >>> client db I see all the messages even though a select statement against the >>> tables returns 0 results. >> >> I wonder what did you mean to say here. If SELECT statement returns 0 rows >> you can't show any messages unless you get it from somewhere else. >> >> [CH] - What I meant to say is. As follows when debugging the application, I >> place a debug statement to show me the query and the results, when the >> client app. Executes it's statement there are valid results that are >> returned. I know it stands to reason that, logically the results must be >> coming from somewhere else if a select statement in the SQLShell reports 0 >> results, but the same select stmt exec'd within the context of the app >> actually returns results. However how do then explain that when copying an >> empty database in place of the one being currently queried that both the app >> and the SQLShell report the same thing ? >> >>> I know I am accessing the same database, because if I copy a completely >>> empty database, to where the client db is. Then the app reports 0 records. >> >> And after doing that if you insert some rows from SqlShell will the app >> report any records? If not or if database reopening in SqlShell (which was >> suggested above) still results in different number of records in app and in >> SqlShell then you definitely use different database files. >> >> [CH] - Correct after doing that if I insert records via the SQLShell the app >> will report the records & the shell and app report the equal number of >> records. >> >> >> Chris >> >> >> Pavel >> >> >> On Tue, Jun 19, 2012 at 11:09 AM, wrote: >>> I've got a bit of an interesting issue that I am hoping to get some help >>> on. The issue goes a little bit like this. >>> >>> Using Windows 8 64bit, Release Preview 8400. (Classic Mode) >>> >>> 1. Our product is installed and requests data from the server. (Server >>> responds and populates a database on the client machine). >>> 2. Connect to the database on the client machine (Open the database >>> using SQLShell) can see the entries in the database, so I know the database >>> is being populated. >>> 3. Execute delete statements, on tables in SQLLIte Database. >>> 4. Try to select against any of these tables and 0 results are >>> returned yet, the client db hasn&#x
Re: [sqlite] SQL Lite 2.8.6 Not deleting Enteries
It seems to me you are using SQLite database in WAL-mode and when you delete rows from SQLShell your app has some read-only transaction open (or it has already started executing its SELECT statement that should return deleted rows). Does the issue persist across app restarts? Pavel On Tue, Jun 19, 2012 at 11:38 AM, wrote: > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov > Sent: Tuesday, June 19, 2012 11:27 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] SQL Lite 2.8.6 Not deleting Enteries > >> 4. Try to select against any of these tables and 0 results are returned >> yet, the client db hasn't actually changed it's size. > > By default SQLite doesn't shrink database file size when deleting data, > although it reuses this space later when you insert new data. If you want to > shrink file size you need to use VACUUM or PRAGMA auto_vacuum. > > [CH] - Good to know Thanks > > >> 5. When debugging the issue, the app still reports all entries via SQL >> query that I, thought were deleted from within SqlShell. > > Did you issue COMMIT in your SqlShell? If you close database in SqlShell and > then reopen again will it still report no rows in the database? > > [CH] - Yes if I close the SQLShell and re-open it still reports 0 rows. > >> When I place a debug Message box to display the messages selected from the >> client db I see all the messages even though a select statement against the >> tables returns 0 results. > > I wonder what did you mean to say here. If SELECT statement returns 0 rows > you can't show any messages unless you get it from somewhere else. > > [CH] - What I meant to say is. As follows when debugging the application, I > place a debug statement to show me the query and the results, when the client > app. Executes it's statement there are valid results that are returned. I > know it stands to reason that, logically the results must be coming from > somewhere else if a select statement in the SQLShell reports 0 results, but > the same select stmt exec'd within the context of the app actually returns > results. However how do then explain that when copying an empty database in > place of the one being currently queried that both the app and the SQLShell > report the same thing ? > >> I know I am accessing the same database, because if I copy a completely >> empty database, to where the client db is. Then the app reports 0 records. > > And after doing that if you insert some rows from SqlShell will the app > report any records? If not or if database reopening in SqlShell (which was > suggested above) still results in different number of records in app and in > SqlShell then you definitely use different database files. > > [CH] - Correct after doing that if I insert records via the SQLShell the app > will report the records & the shell and app report the equal number of > records. > > > Chris > > > Pavel > > > On Tue, Jun 19, 2012 at 11:09 AM, wrote: >> I've got a bit of an interesting issue that I am hoping to get some help on. >> The issue goes a little bit like this. >> >> Using Windows 8 64bit, Release Preview 8400. (Classic Mode) >> >> 1. Our product is installed and requests data from the server. (Server >> responds and populates a database on the client machine). >> 2. Connect to the database on the client machine (Open the database >> using SQLShell) can see the entries in the database, so I know the database >> is being populated. >> 3. Execute delete statements, on tables in SQLLIte Database. >> 4. Try to select against any of these tables and 0 results are returned >> yet, the client db hasn't actually changed it's size. >> 5. When debugging the issue, the app still reports all entries via SQL >> query that I, thought were deleted from within SqlShell. >> >> Essentially it's as if the messages are being flagged for deletion but not >> actually getting deleted. When I place a debug Message box to display the >> messages selected from the client db I see all the messages even though a >> select statement against the tables returns 0 results. >> >> I know I am accessing the same database, because if I copy a completely >> empty database, to where the client db is. Then the app reports 0 records. >> >> Any light someone could shed on this would be appreciated. >> >> Cheers, >> Chris >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Lite 2.8.6 Not deleting Enteries
Your database isn't on a network share, is it? Sounds like data caching is ocurring. Does your app close and re-open the database? What kind of "app" are you running? Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of chris_how...@mcafee.com [chris_how...@mcafee.com] Sent: Tuesday, June 19, 2012 10:38 AM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] SQL Lite 2.8.6 Not deleting Enteries -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov Sent: Tuesday, June 19, 2012 11:27 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQL Lite 2.8.6 Not deleting Enteries > 4. Try to select against any of these tables and 0 results are returned > yet, the client db hasn't actually changed it's size. By default SQLite doesn't shrink database file size when deleting data, although it reuses this space later when you insert new data. If you want to shrink file size you need to use VACUUM or PRAGMA auto_vacuum. [CH] - Good to know Thanks > 5. When debugging the issue, the app still reports all entries via SQL > query that I, thought were deleted from within SqlShell. Did you issue COMMIT in your SqlShell? If you close database in SqlShell and then reopen again will it still report no rows in the database? [CH] - Yes if I close the SQLShell and re-open it still reports 0 rows. > When I place a debug Message box to display the messages selected from the > client db I see all the messages even though a select statement against the > tables returns 0 results. I wonder what did you mean to say here. If SELECT statement returns 0 rows you can't show any messages unless you get it from somewhere else. [CH] - What I meant to say is. As follows when debugging the application, I place a debug statement to show me the query and the results, when the client app. Executes it's statement there are valid results that are returned. I know it stands to reason that, logically the results must be coming from somewhere else if a select statement in the SQLShell reports 0 results, but the same select stmt exec'd within the context of the app actually returns results. However how do then explain that when copying an empty database in place of the one being currently queried that both the app and the SQLShell report the same thing ? > I know I am accessing the same database, because if I copy a completely empty > database, to where the client db is. Then the app reports 0 records. And after doing that if you insert some rows from SqlShell will the app report any records? If not or if database reopening in SqlShell (which was suggested above) still results in different number of records in app and in SqlShell then you definitely use different database files. [CH] - Correct after doing that if I insert records via the SQLShell the app will report the records & the shell and app report the equal number of records. Chris Pavel On Tue, Jun 19, 2012 at 11:09 AM, wrote: > I've got a bit of an interesting issue that I am hoping to get some help on. > The issue goes a little bit like this. > > Using Windows 8 64bit, Release Preview 8400. (Classic Mode) > > 1. Our product is installed and requests data from the server. (Server > responds and populates a database on the client machine). > 2. Connect to the database on the client machine (Open the database > using SQLShell) can see the entries in the database, so I know the database > is being populated. > 3. Execute delete statements, on tables in SQLLIte Database. > 4. Try to select against any of these tables and 0 results are returned > yet, the client db hasn't actually changed it's size. > 5. When debugging the issue, the app still reports all entries via SQL > query that I, thought were deleted from within SqlShell. > > Essentially it's as if the messages are being flagged for deletion but not > actually getting deleted. When I place a debug Message box to display the > messages selected from the client db I see all the messages even though a > select statement against the tables returns 0 results. > > I know I am accessing the same database, because if I copy a completely empty > database, to where the client db is. Then the app reports 0 records. > > Any light someone could shed on this would be appreciated. > > Cheers, > Chris > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-
Re: [sqlite] SQL Lite 2.8.6 Not deleting Enteries
-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov Sent: Tuesday, June 19, 2012 11:27 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQL Lite 2.8.6 Not deleting Enteries > 4. Try to select against any of these tables and 0 results are returned > yet, the client db hasn't actually changed it's size. By default SQLite doesn't shrink database file size when deleting data, although it reuses this space later when you insert new data. If you want to shrink file size you need to use VACUUM or PRAGMA auto_vacuum. [CH] - Good to know Thanks > 5. When debugging the issue, the app still reports all entries via SQL > query that I, thought were deleted from within SqlShell. Did you issue COMMIT in your SqlShell? If you close database in SqlShell and then reopen again will it still report no rows in the database? [CH] - Yes if I close the SQLShell and re-open it still reports 0 rows. > When I place a debug Message box to display the messages selected from the > client db I see all the messages even though a select statement against the > tables returns 0 results. I wonder what did you mean to say here. If SELECT statement returns 0 rows you can't show any messages unless you get it from somewhere else. [CH] - What I meant to say is. As follows when debugging the application, I place a debug statement to show me the query and the results, when the client app. Executes it's statement there are valid results that are returned. I know it stands to reason that, logically the results must be coming from somewhere else if a select statement in the SQLShell reports 0 results, but the same select stmt exec'd within the context of the app actually returns results. However how do then explain that when copying an empty database in place of the one being currently queried that both the app and the SQLShell report the same thing ? > I know I am accessing the same database, because if I copy a completely empty > database, to where the client db is. Then the app reports 0 records. And after doing that if you insert some rows from SqlShell will the app report any records? If not or if database reopening in SqlShell (which was suggested above) still results in different number of records in app and in SqlShell then you definitely use different database files. [CH] - Correct after doing that if I insert records via the SQLShell the app will report the records & the shell and app report the equal number of records. Chris Pavel On Tue, Jun 19, 2012 at 11:09 AM, wrote: > I've got a bit of an interesting issue that I am hoping to get some help on. > The issue goes a little bit like this. > > Using Windows 8 64bit, Release Preview 8400. (Classic Mode) > > 1. Our product is installed and requests data from the server. (Server > responds and populates a database on the client machine). > 2. Connect to the database on the client machine (Open the database > using SQLShell) can see the entries in the database, so I know the database > is being populated. > 3. Execute delete statements, on tables in SQLLIte Database. > 4. Try to select against any of these tables and 0 results are returned > yet, the client db hasn't actually changed it's size. > 5. When debugging the issue, the app still reports all entries via SQL > query that I, thought were deleted from within SqlShell. > > Essentially it's as if the messages are being flagged for deletion but not > actually getting deleted. When I place a debug Message box to display the > messages selected from the client db I see all the messages even though a > select statement against the tables returns 0 results. > > I know I am accessing the same database, because if I copy a completely empty > database, to where the client db is. Then the app reports 0 records. > > Any light someone could shed on this would be appreciated. > > Cheers, > Chris > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Lite 2.8.6 Not deleting Enteries
> 4. Try to select against any of these tables and 0 results are returned > yet, the client db hasn't actually changed it's size. By default SQLite doesn't shrink database file size when deleting data, although it reuses this space later when you insert new data. If you want to shrink file size you need to use VACUUM or PRAGMA auto_vacuum. > 5. When debugging the issue, the app still reports all entries via SQL > query that I, thought were deleted from within SqlShell. Did you issue COMMIT in your SqlShell? If you close database in SqlShell and then reopen again will it still report no rows in the database? > When I place a debug Message box to display the messages selected from the > client db I see all the messages even though a select statement against the > tables returns 0 results. I wonder what did you mean to say here. If SELECT statement returns 0 rows you can't show any messages unless you get it from somewhere else. > I know I am accessing the same database, because if I copy a completely empty > database, to where the client db is. Then the app reports 0 records. And after doing that if you insert some rows from SqlShell will the app report any records? If not or if database reopening in SqlShell (which was suggested above) still results in different number of records in app and in SqlShell then you definitely use different database files. Pavel On Tue, Jun 19, 2012 at 11:09 AM, wrote: > I've got a bit of an interesting issue that I am hoping to get some help on. > The issue goes a little bit like this. > > Using Windows 8 64bit, Release Preview 8400. (Classic Mode) > > 1. Our product is installed and requests data from the server. (Server > responds and populates a database on the client machine). > 2. Connect to the database on the client machine (Open the database > using SQLShell) can see the entries in the database, so I know the database > is being populated. > 3. Execute delete statements, on tables in SQLLIte Database. > 4. Try to select against any of these tables and 0 results are returned > yet, the client db hasn't actually changed it's size. > 5. When debugging the issue, the app still reports all entries via SQL > query that I, thought were deleted from within SqlShell. > > Essentially it's as if the messages are being flagged for deletion but not > actually getting deleted. When I place a debug Message box to display the > messages selected from the client db I see all the messages even though a > select statement against the tables returns 0 results. > > I know I am accessing the same database, because if I copy a completely empty > database, to where the client db is. Then the app reports 0 records. > > Any light someone could shed on this would be appreciated. > > Cheers, > Chris > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
