Re: [sqlite] bug "PRAGMA table_info"
Thank you! On 06/19/2012 08:25 PM, Jay A. Kreibich wrote: > On Tue, Jun 19, 2012 at 08:16:37PM +0200, Patrik Nilsson scratched on the > wall: >> Hi All, >> >> I use "PRAGMA table_info" to check my tables at start up and if the >> table columns are the need of updating (adding or removal) I do that. >> >> After a redesign of how the databases are managed this doesn't work >> anymore. Instead of using several database connections, I use one. >> >> The cleaned up the code looks better and is less error prone to select >> the wrong database connection, but it comes with a prize: >> >> "PRAGMA table_info" does not take databases as table names. "test" works >> as an argument, but "main.test" doesn't. > > > No, but following the standard of every other PRAGMA, this does work: > > PRAGMA main.table_info( test ); > > > See the syntax diagrams here: http://sqlite.org/pragma.html > >-j > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] bug "PRAGMA table_info"
On Tue, Jun 19, 2012 at 08:16:37PM +0200, Patrik Nilsson scratched on the wall: > Hi All, > > I use "PRAGMA table_info" to check my tables at start up and if the > table columns are the need of updating (adding or removal) I do that. > > After a redesign of how the databases are managed this doesn't work > anymore. Instead of using several database connections, I use one. > > The cleaned up the code looks better and is less error prone to select > the wrong database connection, but it comes with a prize: > > "PRAGMA table_info" does not take databases as table names. "test" works > as an argument, but "main.test" doesn't. No, but following the standard of every other PRAGMA, this does work: PRAGMA main.table_info( test ); See the syntax diagrams here: http://sqlite.org/pragma.html -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] bug "PRAGMA table_info"
Hi All, I use "PRAGMA table_info" to check my tables at start up and if the table columns are the need of updating (adding or removal) I do that. After a redesign of how the databases are managed this doesn't work anymore. Instead of using several database connections, I use one. The cleaned up the code looks better and is less error prone to select the wrong database connection, but it comes with a prize: "PRAGMA table_info" does not take databases as table names. "test" works as an argument, but "main.test" doesn't. Best Regards, Patrik patrik@debian:~$ ~/Projects/DMemory/trunk/shell/sqlite3 /home/files/test.dmemory "PRAGMA table_info(page)" 0|id|integer|0||1 1|type|integer|0||0 2|sortorder|integer|0||0 3|width|integer|0||0 4|height|integer|0||0 5|sizedata|integer|0||0 6|deleted|integer|1|0|0 7|viewwidth|integer|1|0|0 8|fontname|text|0||0 9|fontsize|integer|1|0|0 10|comment|text|0||0 11|data|blob|0||0 patrik@debian:~$ ~/Projects/DMemory/trunk/shell/sqlite3 /home/files/test.dmemory "PRAGMA table_info(main.page)" Error: near ".": syntax error patrik@debian:~$ ~/Projects/DMemory/trunk/shell/sqlite3 /home/files/test.dmemory "create table testa(a integer)" patrik@debian:~$ ~/Projects/DMemory/trunk/shell/sqlite3 /home/files/test.dmemory "PRAGMA table_info(testa)" 0|a|integer|0||0 patrik@debian:~$ ~/Projects/DMemory/trunk/shell/sqlite3 /home/files/test.dmemory "PRAGMA table_info(main.testa)" Error: near ".": syntax error patrik@debian:~$ ~/Projects/DMemory/trunk/shell/sqlite3 /home/files/test.dmemory "create table main.testb(a integer)" patrik@debian:~$ ~/Projects/DMemory/trunk/shell/sqlite3 /home/files/test.dmemory "PRAGMA table_info(testb)" 0|a|integer|0||0 patrik@debian:~$ ~/Projects/DMemory/trunk/shell/sqlite3 /home/files/test.dmemory "PRAGMA table_info(main.testb)" Error: near ".": syntax error patrik@debian:~$ ___ 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
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 >>> 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 f
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 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
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 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
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'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.
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-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
-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
Re: [sqlite] last_insert_rowid() with conflict
On Tue, Jun 19, 2012 at 11:02 AM, Baruch Burstein wrote: > If I have a column that has unique values, and the ON CONFLICT clause is > IGNORE, is there a way to get the rowid of the last insert (on > success) *or*the last conflict (on conflict). I would like to just > keep adding to the > table without worrying if there is a duplicate, but after each insert I > need to know the row of the inserted item (whether it was inserted now or > previously) No, anything that was inserted previously can be accessed only via SELECT. Pavel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQL Lite 2.8.6 Not deleting Enteries
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] last_insert_rowid() with conflict
If I have a column that has unique values, and the ON CONFLICT clause is IGNORE, is there a way to get the rowid of the last insert (on success) *or*the last conflict (on conflict). I would like to just keep adding to the table without worrying if there is a duplicate, but after each insert I need to know the row of the inserted item (whether it was inserted now or previously) -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug: incorrect forward declaration ofsqlite3_win32_sleep
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 18/06/12 22:55, Joe Mistachkin wrote: > The new function is unsupported; however, it is required from places > in the testing infrastructure where the sqlite3_sleep function cannot > be called for one reason or another (e.g. there is no VFS yet). In that case shouldn't it be inside #ifdef SQLITE_TEST which then means it won't be encountered in regular builds so how it is declared is moot? Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) iEYEARECAAYFAk/gk48ACgkQmOOfHg372QQViwCg5Kbn3a8OEv82E7pOdWu70WPo TPMAoJvTTguZ/JhBzpYw+PGhGohmBdVl =4Rdl -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] escaping GLOB pattern
nobre wrote: > "If the optional ESCAPE clause is present, then the expression following the > ESCAPE keyword must evaluate to a string consisting of a single character. > This character may be used in the LIKE pattern to include literal percent or > underscore characters. The escape character followed by a percent symbol > (%), underscore (_), or a second instance of the escape character itself > matches a literal percent symbol, underscore, or a single escape character, > respectively." > > Choose a escape character and put it before any of the meaningful ones in > your query to escape it. Contrary to syntax diagram in documentation, ESCAPE clause does NOT work with GLOB (or [icu ext] REGEXP) [or, rather, "not implemented" - it would work if 3-arg GLOB function were provided - but it is not]. And it won't solve OP problem anyway. FWIW, I think this should work: SELECT FROM t WHERE foo GLOB replace(replace(replace(?, '[','[[]'),'*','[*]'),'?','[?]')||'*' (but, of course, it looks clumsy and not future-safe). ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] C++ programming - int sqlite3_prepare_v2() question
Arbol One wrote: > Since the purpose of this exercise is to create a simple table, could you > tell me if this is all I have to do? Are there any > other steps? Well, did it work? You can examine the database file using sqlite3 command line shell, or any of the third-party tools listed here: http://www.sqlite.org/cvstrac/wiki?p=ManagementTools It appears that your program lacks cleanup actions. Call sqlite_finalize on every prepared statement once you no longer need it. Then, call sqlite3_close on a database connection once you no longer need it. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Building an fts Query for Double Quote
With the default tokenizer your index would split such an input (quot'ed) into 'quot' and 'ed' , you wouldn't get a hit for a phrase such as "quot''ed". If you use a custom tokenizer , you CAN use " in your terms/queries, however it won't work for phrases, only for single tokens >From the fts3aux.c source: /* See if we are dealing with a quoted phrase. If this is the case, then ** search for the closing quote and pass the whole string to getNextString() ** for processing. This is easy to do, as fts3 has no syntax for escaping ** a quote character embedded in a string. */ -- View this message in context: http://sqlite.1065341.n5.nabble.com/Building-an-fts-Query-for-Double-Quote-tp62541p62545.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] escaping GLOB pattern
"If the optional ESCAPE clause is present, then the expression following the ESCAPE keyword must evaluate to a string consisting of a single character. This character may be used in the LIKE pattern to include literal percent or underscore characters. The escape character followed by a percent symbol (%), underscore (_), or a second instance of the escape character itself matches a literal percent symbol, underscore, or a single escape character, respectively." Choose a escape character and put it before any of the meaningful ones in your query to escape it. -- View this message in context: http://sqlite.1065341.n5.nabble.com/escaping-GLOB-pattern-tp62528p62538.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] C++ programming - int sqlite3_prepare_v2() question
Hi Igor.
Since the purpose of this exercise is to create a simple table, could you tell
me if this is all I have to do? Are there any other steps?
- Original Message -
From: Igor Tandetnik
Sent: 06/18/12 08:36 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] C++ programming - int sqlite3_prepare_v2() question
Arbol One wrote: > Continuing with the questions and
moving to the step three. > int sqlite3_step(sqlite3_stmt*); > --- > rc =
sqlite3_step(stmt); > if(rc != SQLITE_DONE) { > sqlite3_close(db); > std::cout
<< "error sqlite3_step: " << rc << std::endl; > exit(-3); > } > Any
suggestions? None. -- Igor Tandetnik
___ 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] substr bug in 3.7.13?
Oh, sorry.
It was my fault.
It works very well with starting number 1. :)
2012/6/19 Bart Smissaert
> Should that zero not be a 1?
> From the documentation:
> The left-most character of X is number 1
>
> RBS
>
>
> On 6/19/12, Yongil Jang wrote:
> > Dear all,
> >
> > I've found following result when I try to use 'substr' function.
> >
> > sqlite> create table test (data text);
> > sqlite> insert into test values ('010101');
> > sqlite> select substr(data, 0, 2) from test;
> > 0
> > sqlite> select substr(data, 0, 3) from test;
> > 01
> >
> > As you can see, string length should be one plus value to get correct
> > length of string.
> > I'm using sqlite 3.7.13 legacy source code and compiled on my Ubuntu
> server
> > 10.04 64bit.
> >
> > Thank you for read this message.
> > ___
> > 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] substr bug in 3.7.13?
Should that zero not be a 1?
>From the documentation:
The left-most character of X is number 1
RBS
On 6/19/12, Yongil Jang wrote:
> Dear all,
>
> I've found following result when I try to use 'substr' function.
>
> sqlite> create table test (data text);
> sqlite> insert into test values ('010101');
> sqlite> select substr(data, 0, 2) from test;
> 0
> sqlite> select substr(data, 0, 3) from test;
> 01
>
> As you can see, string length should be one plus value to get correct
> length of string.
> I'm using sqlite 3.7.13 legacy source code and compiled on my Ubuntu server
> 10.04 64bit.
>
> Thank you for read this message.
> ___
> 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] substr bug in 3.7.13?
Dear all,
I've found following result when I try to use 'substr' function.
sqlite> create table test (data text);
sqlite> insert into test values ('010101');
sqlite> select substr(data, 0, 2) from test;
0
sqlite> select substr(data, 0, 3) from test;
01
As you can see, string length should be one plus value to get correct
length of string.
I'm using sqlite 3.7.13 legacy source code and compiled on my Ubuntu server
10.04 64bit.
Thank you for read this message.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
