[sqlite] inserting blob data
Hi, I'm using 3.2.2. I have a binary data in a variable which I want to store as a blob in my database table. I have defined a blob field in my table. Since this binary data varies in size every time, hence I want to store it as blob. Please let me know how can I insert a blob data using the function - sqlite_exec_printf(). I also want to later read that data using sqlite_get_table_printf(). Can anybody give an example. Thanks in advance. Regards Mayura ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Insert performance in 3.6.11 vs. 3.5.5
Well, seems that was a false alarm. We were not able to reproduce this on other systems - there the 3.6.11 release even performed slightly better than 3.5.5. Still no idea what caused this, as now even the original system no longer shows this effect, but it's very probably not SQLite. Best regards, Günter -- Günter Obiltschnig Applied Informatics Software Engineering GmbH A-9184 St. Jakob im Rosental | St. Peter 33 | www.appinf.com P: +43 4253 32596 M: +43 676 5166737 F: +43 676 32096 Company Registration: FN 276491 f | Landesgericht Klagenfurt Managing Director: DI Günter Obiltschnig APPLIED INFORMATICS - SMARTER DEVICE NETWORKING On Mar 25, 2009, at 15:24 , Günter Obiltschnig wrote: > Hi there, > > I have just upgraded SQLite in our application from 3.5.5 to 3.6.11 > (we are using the amalgamation), and I have noticed a sharp drop in > insert performance to more than half the speed that we had with 3.5.5. > We are using SQLite in an embedded Linux device, and the database > files are on a CompactFlash device. > > The inserts are being done into an initially empty table with 28 > columns, and all inserts (can be more than 10) are done within one > large transaction, using a prepared insert statement. One additional > unique index on a single column is used on the table as well. > > With 3.5.5, inserting 1000 rows into that table took about 7 seconds, > with 3.6.11 it takes 14-16 seconds. > > We are using PRAGMA synchronous = OFF and a cache size of 6000 pages. > The main reason why we updated was because we experienced memory > issues with 3.5.5. Reducing the cache size (PRAGMA cache_size) would > not release memory. > > Any ideas what causes this? > > Apart from that we are very happy with sqlite. A big thank you to D. > Richard Hipp and everyone who contributed to this great peace of > software. > > Thanks and best regards, > > Günter > > ___ > 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] how to call c/c++ function in trigger
Thank you very much! Igor Tandetnik wrote: > > Simon Chen wrote: >> What I want is: >> >> - whenever I insert/update/delete a table entry, the specified c/c++ >> function is called >> - if the function returns true, the db action can proceed; otherwise, >> the db action should be rolled-back. > > create trigger triggerName before insert on tableName > when not myfunction() > begin > select RAISE(ROLLBACK, 'error message'); > end; > > -- or > > create trigger triggerName before insert on tableName > begin > select RAISE(ROLLBACK, 'error message') > where not myfunction(); > end; > > Similarly for update and delete. > > Igor Tandetnik > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/how-to-call-c-c%2B%2B-function-in-trigger-tp22815938p22818468.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] SQLite version 3.6.12
> The TEA configure script is correct but the sqlite-3.6.12.tar.gz > configure script is messed up. See > http://www.sqlite.org/cvstrac/chngview?cn=6419 > and remember that vapier==Mike Frysinger. I'll try to fix the > tarball soon. I made a try to build TCL-module using the TEA package. There is one doubtful thing: - version 3.6.11 has libtclsqlite3.so of 47486 bytes size, while: - 3.6.12 539796 (!) Is it OK? Of course, the previous version has been built from that now "unsupported" tarball. The size of present 3.6.12 TCL-module is given _after_ "stripping". -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to call c/c++ function in trigger
Simon Chen wrote: > What I want is: > > - whenever I insert/update/delete a table entry, the specified c/c++ > function is called > - if the function returns true, the db action can proceed; otherwise, > the db action should be rolled-back. create trigger triggerName before insert on tableName when not myfunction() begin select RAISE(ROLLBACK, 'error message'); end; -- or create trigger triggerName before insert on tableName begin select RAISE(ROLLBACK, 'error message') where not myfunction(); end; Similarly for update and delete. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.12
On Mar 31, 2009, at 7:42 PM, Zbigniew Baniewski wrote: > On Tue, Mar 31, 2009 at 07:42:49PM -0400, D. Richard Hipp wrote: > >> I just checked the sqlite-3_6_12-tea.tar.gz tarball on the website, >> and it contains no "3.6.11" text. Only "3.6.12". I think you have >> somehow obtained the wrong configure script. > > I meant sqlite-3.6.12.tar.gz package. > > Is it possible to just use the Makefile from sqlite-3_6_12-tea.tar.gz > with sources provided by sqlite-3.6.12.tar.gz ? No, it is not possible to mix configure scripts from one package with sources for another. The TEA configure script is correct but the sqlite-3.6.12.tar.gz configure script is messed up. See http://www.sqlite.org/cvstrac/chngview?cn=6419 and remember that vapier==Mike Frysinger. I'll try to fix the tarball soon. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.12
Sorry, checked it again, and noticed now, that this kind of tarball seems to be kinda abandoned: :( "The Makefile and configure script in this tarball are not supported" -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.12
On Tue, Mar 31, 2009 at 07:42:49PM -0400, D. Richard Hipp wrote: > I just checked the sqlite-3_6_12-tea.tar.gz tarball on the website, > and it contains no "3.6.11" text. Only "3.6.12". I think you have > somehow obtained the wrong configure script. I meant sqlite-3.6.12.tar.gz package. Is it possible to just use the Makefile from sqlite-3_6_12-tea.tar.gz with sources provided by sqlite-3.6.12.tar.gz ? -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.12
I just checked the sqlite-3_6_12-tea.tar.gz tarball on the website, and it contains no "3.6.11" text. Only "3.6.12". I think you have somehow obtained the wrong configure script. On Mar 31, 2009, at 7:34 PM, Zbigniew Baniewski wrote: > The "configure" script included in tarball has entries, like f.e.: > > #! /bin/sh > # Guess values for system-dependent variables and create Makefiles. > # Generated by GNU Autoconf 2.63 for sqlite 3.6.11. > # > [..] > # Identity of this package. > PACKAGE_NAME='sqlite' > PACKAGE_TARNAME='sqlite' > PACKAGE_VERSION='3.6.11' > PACKAGE_STRING='sqlite 3.6.11' > PACKAGE_BUGREPORT='' > [..] > \`configure' configures sqlite 3.6.11 to adapt to many kinds of > systems. > [..] > if test -n "$ac_init_help"; then > case $ac_init_help in > short | recursive ) echo "Configuration of sqlite 3.6.11:";; > esac > cat <<\_ACEOF > [..] > sqlite configure 3.6.11 > generated by GNU Autoconf 2.63 > [..] > > ...and so on... (a few more) > > > Could the above have negative influence? > -- > pozdrawiam / regards > > Zbigniew Baniewski > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.12
The "configure" script included in tarball has entries, like f.e.: #! /bin/sh # Guess values for system-dependent variables and create Makefiles. # Generated by GNU Autoconf 2.63 for sqlite 3.6.11. # [..] # Identity of this package. PACKAGE_NAME='sqlite' PACKAGE_TARNAME='sqlite' PACKAGE_VERSION='3.6.11' PACKAGE_STRING='sqlite 3.6.11' PACKAGE_BUGREPORT='' [..] \`configure' configures sqlite 3.6.11 to adapt to many kinds of systems. [..] if test -n "$ac_init_help"; then case $ac_init_help in short | recursive ) echo "Configuration of sqlite 3.6.11:";; esac cat <<\_ACEOF [..] sqlite configure 3.6.11 generated by GNU Autoconf 2.63 [..] ...and so on... (a few more) Could the above have negative influence? -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.12
On Mar 31, 2009, at 7:27 PM, Mike Frysinger wrote: > On Tuesday 31 March 2009 19:15:57 D. Richard Hipp wrote: >> On Mar 31, 2009, at 7:06 PM, Zbigniew Baniewski wrote: >>> On Tue, Mar 31, 2009 at 09:41:30AM -0400, D. Richard Hipp wrote: SQLite version 3.6.12 is now available on the SQLite website >>> >>> ...and once again TCL module is flawed: >>> >>> #v+ >>> % package require sqlite3 >>> attempt to provide package sqlite3 3.6.12 failed: package sqlite3 >>> 3.6.11 >>> % provided instead >>> #v- >> >> I don't understand that. I can grep in the package for 3.6.11 and I >> get no hits: >> >> grep 3.6.11 * */* >> >> On the other hand, if I grep from 3.6.12, I get lots of hits. So I >> do >> not know where the package gets the idea that it is providing version >> 3.6.11. > > the configure script isnt regenerated before the dist tarball is > made. not > sure how you're making releases, but `autoconf` needs to be part of > the > process ... > -mike I think I did rerun autoconf. Certainly if I "grep 3.6.12 configure" I get multiple hits, but if I run "grep 3.6.11 configure" I get nothing. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to process SQLITE_LOCKED_SHAREDCACHE error?
> SQLITE_LOCKED_SHAREDCACHE is like SQLITE_LOCKED and can be processed > in the same way, if you want. But if you get a > SQLITE_LOCK_SHAREDCACHE you also have the option of calling > sqlite3_unlock_notify(). You should not use sqlite3_unlock_notify() > on an ordinary SQLITE_LOCKED. Additional information at > http://www.sqlite.org/unlock_notify.html Thank you! Dennis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.12
On Tue, Mar 31, 2009 at 07:15:57PM -0400, D. Richard Hipp wrote: > I don't understand that. I can grep in the package for 3.6.11 and I > get no hits: > > grep 3.6.11 * */* > > On the other hand, if I grep from 3.6.12, I get lots of hits. So I do > not know where the package gets the idea that it is providing version > 3.6.11. #v+ # grep -i "3.6.11" tclsqlite.o # Binary file tclsqlite.o matches # grep -i "3.6.12" tclsqlite.o # #v- But OK - I'll try to check it once again, and I'll make another compilation. Perhaps I missed something(?) -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to process SQLITE_LOCKED_SHAREDCACHE error?
On Mar 31, 2009, at 7:14 PM, Dennis Volodomanov wrote: > Hello, > > I'd like to ask how should the SQLITE_LOCKED_SHAREDCACHE be processed? > Is it like a normal SQLITE_BUSY/SQLITE_LOCKED and I should sleep a bit > and try again? Or is this a fatal error and requires me to abort the > transaction? SQLITE_LOCKED_SHAREDCACHE is like SQLITE_LOCKED and can be processed in the same way, if you want. But if you get a SQLITE_LOCK_SHAREDCACHE you also have the option of calling sqlite3_unlock_notify(). You should not use sqlite3_unlock_notify() on an ordinary SQLITE_LOCKED. Additional information at http://www.sqlite.org/unlock_notify.html D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.12
On Mar 31, 2009, at 7:06 PM, Zbigniew Baniewski wrote: > On Tue, Mar 31, 2009 at 09:41:30AM -0400, D. Richard Hipp wrote: > >> SQLite version 3.6.12 is now available on the SQLite website > > ...and once again TCL module is flawed: > > #v+ > % package require sqlite3 > attempt to provide package sqlite3 3.6.12 failed: package sqlite3 > 3.6.11 > % provided instead > #v- I don't understand that. I can grep in the package for 3.6.11 and I get no hits: grep 3.6.11 * */* On the other hand, if I grep from 3.6.12, I get lots of hits. So I do not know where the package gets the idea that it is providing version 3.6.11. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to process SQLITE_LOCKED_SHAREDCACHE error?
Hello, I'd like to ask how should the SQLITE_LOCKED_SHAREDCACHE be processed? Is it like a normal SQLITE_BUSY/SQLITE_LOCKED and I should sleep a bit and try again? Or is this a fatal error and requires me to abort the transaction? Thanks in advance, Dennis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.12
On Tue, Mar 31, 2009 at 09:41:30AM -0400, D. Richard Hipp wrote: > SQLite version 3.6.12 is now available on the SQLite website ...and once again TCL module is flawed: #v+ % package require sqlite3 attempt to provide package sqlite3 3.6.12 failed: package sqlite3 3.6.11 % provided instead #v- -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] how to call c/c++ function in trigger
Hi all, I have a question regarding calling c/c++ functions in sqlite triggers. What I want is: - whenever I insert/update/delete a table entry, the specified c/c++ function is called - if the function returns true, the db action can proceed; otherwise, the db action should be rolled-back. Is there any online instruction about how to do this? Thanks! -Simon -- View this message in context: http://www.nabble.com/how-to-call-c-c%2B%2B-function-in-trigger-tp22815938p22815938.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] export table to csv
You can only use one command in the command-line. Use options for the others, like this: Sqlite3.exe -csv -separator ',' ioimport.db3 "select * from iotemplate;" >thisisit.csv -Original Message- From: Richard Nero [mailto:rich...@rlnero.com] Sent: Monday, March 30, 2009 10:12 AM To: sqlite-users@sqlite.org Subject: [sqlite] export table to csv All, I can successfully import a databese via command line with: sqlite3.exe -separator , ioimport.db3 ".import Temp.csv iofromexcel" Now i am trying to export a table in the db with: sqlite3.exe ioimport.db3 .mode csv .separator , .output thisisit.csv "select * from iotemplate;" .output stdout This does not work and I have tried all solutions possible. Can someone lead me in the right direction on how to do this via command line Thanks in advance! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reading datetime fields in a C++ program
I was able to answer my own question. Because SQLite is typeless, any random collection of bytes can be stored in any field. So, I have no guarantee that the information stored in the value_timestamp field actually represents a double-precision floating-point number. Those fields might actually contain a character string representing the date and time. I deleted all but five records from the trend_data field, vacuumed the database, and then opened the database file in a binary editor. Sure enough, I found the text representation of the datetimes in the file. So, until I can ensure that I control the format of the data being written into the table, I will need to use either julianday() or datetime() to ensure I know the format of the data as I am reading it from the table. Rob Richardson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Reading datetime fields in a C++ program
I am using a program writing in C++ using MS Visual Studio 6 to read a SQLite table. The table's create statement, as reported by SQLiteSpy, is: CREATE TABLE trend_data( tag_key integer, value integer, value_timestamp datetime ); I am trying to retrieve the largest value of the value_timestamp field in this table using this query: select max(value_timestamp) AS latest_time from trend_data I am not sure what the standard method is for reading the value of a datetime field from a C++ program. There is no sqlite3_column_datetime() function. As I understand it, SQLite stores datetime values as Julian times, the number of days since some time before 4700 BC. These are doubles, stored with enough precision to identify microseconds. So, inside my application, I use the sqlite3_column_double() function to retrieve the latest_time field from the above query. The problem I am running into, and have been running into for the two years that I have been working with the ActiveX control that uses this database, is that the formatting of the datetime value inside SQLite seems to be inconsistent. Currently, the value read from the latest_time field is "2009". I finally figured out why. For some reason I don't understand, SQLite is converting the value_timestamp into a string of the form "2009-03-29 12:34:56" before my call to sqlite3_column_double(). Because sqlite3 is typeless, when I ask it for a double, SQLite tries to convert that string to a number, giving me the number that is represented by the first numeric characters in that string. But I swear I have seen this same query return a correct, valid datetime (a double with a value of over 245,000) from this same query. It seems that the only way to ensure that I get a valid datetime is to use the julianday() function in my query: select max(julianday(value_timestamp)) AS latest_time from trend_data. I don't want to have to do that because that would be applying an extra function call to every record in the table, and there could be hundreds of thousands of them. But if I turn it around, as in: select julianday(max(value_timestamp)) AS latest_time from trend_data. so that I'm only applying the julianday() function to one record, I lose the guarantee that I am actually working with datetimes. If SQLite converts the value_timestamps to strings before finding the maximum, max(value_timestamp) will still be 2009. Is there some function I can call from my C++ application to ensure that datetime values are never converted to strings unless I explicitly use the datetime() function? Or is there some SQL statement I can use that will change the database file to do that? Thank you very much. Rob Richardson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.12
On Tue, Mar 31, 2009 at 09:41:30AM -0400, D. Richard Hipp scratched on the wall: > SQLite version 3.6.12 is now available on the SQLite website > New features added to version 3.6.12 include the > sqlite3_unlock_notify() interface and the reverse_unordered_selects > PRAGMA. Additional information about both is available on the website. Might be nice if reverse_unordered_selects had a =2 value that would turn it on or off randomly (or simply alternated) for each select that is issued. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] export table to csv
Hi Richard, The sqlite3 utility allows a *single* dot-command on the command line itself. You can have an unlimited number of commands in a separate text file, though. http://www.sqlite.org/sqlite.html So, using Windows syntax, you can have something like: === Set MyTempFile=%temp%\Mytemp.tmp echo .separator ,>%myTempFile% Echo .output thisisit.csv >>%myTempFile% Echo select * from iotemplate;>>%myTempFile% Echo .output stdout >>%myTempFile% Sqlite3.exe ioimport.db3 ".read %myTempFile%" Del %mytempfile% >nul If you're using linux/unix, you can redirect input to the your shell script and avoid the temporary file. Hope this helps, Donald -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Nero Sent: Monday, March 30, 2009 11:12 AM To: sqlite-users@sqlite.org Subject: [sqlite] export table to csv All, I can successfully import a databese via command line with: sqlite3.exe -separator , ioimport.db3 ".import Temp.csv iofromexcel" Now i am trying to export a table in the db with: sqlite3.exe ioimport.db3 .mode csv .separator , .output thisisit.csv "select * from iotemplate;" .output stdout This does not work and I have tried all solutions possible. Can someone lead me in the right direction on how to do this via command line Thanks in advance! ___ 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] SQLite version 3.6.12
SQLite version 3.6.12 is now available on the SQLite website http://www.sqlite.org/ During release testing for 3.6.12, we stumbled over a critical bug that has existed in SQLite since version 3.4.0. This bug will cause corruption of in-memory databases if an incremental vacuum is rolled back. The corruption is virtually guaranteed if you rollback an incremental vacuum in an in-memory database. Presumably not many people are doing that or we would have seen this sooner. As far as we are aware, incremental vacuum works correctly for on-disk databases. We will continue to study this issue over the next weeks and release additional patches if we find new problems. A manual code review uncovered another bug in the lookaside memory allocator which can result in a segfault when shared cache is enabled. At the very least, users of shared cache should disable lookaside memory. A better solution is to update to version 3.6.12. New features added to version 3.6.12 include the sqlite3_unlock_notify() interface and the reverse_unordered_selects PRAGMA. Additional information about both is available on the website. Because of the incremental vacuum and lookaside memory allocator issues identified above, we recommend that everyone update to SQLite version 3.6.12. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Selecting records by INDEXED key
Hi,
your select statement does not include a "where" or "order by" - clause
for which the index can be used, which is the cause of the error.
If you want to select "the records by their index sequence", you should use
Select * From "APPLE" ORDER BY "MySurname";
The index will then be used automatically.
Also, i see that you enclose column names and table names by double
quotes. This is not necessary.
HTH
Martin
ggcoo...@clearmail.com.au wrote:
> Hi
> I am trying to select (list) records by their index sequence BUT keep
> getting the SQL error.
>
> "SQL error can't use index: Surname"
> . Indices showes the index
>
> ;
> ;Sample INDEX program
> ;
> CREATE TABLE "APPLE" ("MyName", "MySurname", "MyAge", PRIMARY KEY ("MyAge"));
> CREATE INDEX "Surname" on "APPLE" ("MySurname");
>
> INSERT INTO "APPLE" VALUES ('Greg','Hooper', 54);
> INSERT INTO "APPLE" VALUES ('Daniel','Hooper', 30);
> INSERT INTO "APPLE" VALUES ('Nicole','Lvester', 32);
> INSERT INTO "APPLE" VALUES ('Rhonda','Grin', 50);
>
> Select * From "APPLE" INDEX BY "Surname";
>
>
>
> Regards Greg.
>
> ___
> 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] Selecting records by INDEXED key
Hi
I am trying to select (list) records by their index sequence BUT keep
getting the SQL error.
"SQL error can't use index: Surname"
. Indices showes the index
;
;Sample INDEX program
;
CREATE TABLE "APPLE" ("MyName", "MySurname", "MyAge", PRIMARY KEY ("MyAge"));
CREATE INDEX "Surname" on "APPLE" ("MySurname");
INSERT INTO "APPLE" VALUES ('Greg','Hooper', 54);
INSERT INTO "APPLE" VALUES ('Daniel','Hooper', 30);
INSERT INTO "APPLE" VALUES ('Nicole','Lvester', 32);
INSERT INTO "APPLE" VALUES ('Rhonda','Grin', 50);
Select * From "APPLE" INDEX BY "Surname";
Regards Greg.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sqlite .import not ignoring extra fields
With sqlite 2.8, the copy command would ignore extra fields at the end. How can I do this with .import in sqlite 3.11? Many of the files I am importing have trailing field separators. Others may have extra data. The files are too big for me to want a separate pass to trim off the trailing fields. This email, and any documents or data attached hereto, is intended for the addressee(s) only. It may contain confidential and/or privileged information and no rights or obligations have been waived by the sender. Any copying, distribution, taking of action in reliance on, other use of the information contain in this email by persons other than the intended addressee(s) is prohibited. If you have received this email in error, please reply to the sender by email and immediately delete or destroy all copies of this email, along with any attached documents or data. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] export table to csv
All, I can successfully import a databese via command line with: sqlite3.exe -separator , ioimport.db3 ".import Temp.csv iofromexcel" Now i am trying to export a table in the db with: sqlite3.exe ioimport.db3 .mode csv .separator , .output thisisit.csv "select * from iotemplate;" .output stdout This does not work and I have tried all solutions possible. Can someone lead me in the right direction on how to do this via command line Thanks in advance! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unnecessary line breaks in .dump output
On Tue, Mar 31, 2009 at 5:46 AM, Francois Botha wrote: >> >> Perhaps the OP should file a ticket to have this corrected >> in the SQLite command line tool. >> > > What's the OP? Just want to know if I must log the bug or whether you're > referring to somebody else. > > If the OP is somebody else, please log it and let me know what the ticket > number is so that I can follow it. You are the OP, the original poster of the message that started this thread. > > thanks, > Francois ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unnecessary line breaks in .dump output
> > Perhaps the OP should file a ticket to have this corrected > in the SQLite command line tool. > What's the OP? Just want to know if I must log the bug or whether you're referring to somebody else. If the OP is somebody else, please log it and let me know what the ticket number is so that I can follow it. thanks, Francois ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Corruption of incremental_vacuum databases
Hello,
after seeing the recent change #6413 and ticket #3761 I finally
decided to write about a corruption issue we have.
This is the environment of our application:
- We are using SQLite 3.6.3 (with SQLite.NET 1.0.60.0).
- Several database files. Each file is opened in it's own connection
and never shared across them.
- Some of these connections have another database attached to it
(containing mostly BLOB data).
- In all cases the connections are opened on program start and closed
on program shutdown.
- There's a low-priority thread that executes "pragma
incremental_vacuum" when the application is idle and there is enough
free pages. Code of the thread is listed below.
- "journal_mode=persist" is used on all databases in all connections
(to workaround a bug in the journal deletion logic on Windows, search
for "TortoiseSVN" in the mailing list archive for details)
- "synchronous=off" is used on all databases in all connections. This
setting is likely to change in future, but in no case of the
corruption a system crash was involved.
Since we started using the incremental_vacuum mode we were getting
database corruption errors pretty often (sometimes as often as once a
day in 3 people). Most, if not all, of these corruptions happened
following a ROLLBACK (caused by constraint violation). "pragma
integrity_check;" on the already corrupted databases usually reported
few "Page XXX is never used" error.
Unfortunately I don't have any of the corrupted databases at hand and
I have no deterministic way to create them. My question is if these
could be related to the just fixed problem (in ticket 3761) or if it
could be another issue?
Best regards,
Filip Navara
WaitHandle[] handles = new WaitHandle[] { this.stopEvent, this.wakeupEvent };
System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch();
int timeout = -1;
int pagesPerIteration = 32;
// Wait for thread shutdown and wakeup event. The shutdown event
// is used to stop the thread on application exit. The wakeup event is
// fired on startup if there are free pages in the database or if a DELETE
// statement was executed.
while (WaitHandle.WaitAny(handles, timeout, false) != 0)
{
long totalFreePages = 0, freePages;
lock (this.repositories)
{
stopWatch.Reset();
stopWatch.Start();
foreach (IRepositoryBase repository in this.repositories)
{
// wrapper around "pragma freelist_count;"
freePages = repository.GetFreePageCount();
totalFreePages += freePages;
if (freePages > 0)
// wrapper around "pragma incremental_vacuum(x)"
repository.Compact(pagesPerIteration);
}
stopWatch.Stop();
}
// We start by freeing 32 pages per one iteration of the loop for
// each database. After each iteration the number is recalculated
// based on the time spent on the operation and then it's
// truncated to the <24;4096> range.
pagesPerIteration = Math.Min(Math.Max(24, (int)(100.0 *
pagesPerIteration / stopWatch.ElapsedMilliseconds)), 4096);
// If there are still free pages in the databases then schedule the
// thread to wake up in 200ms and continue vacuuming them.
if (totalFreePages > 0)
timeout = 200;
else
timeout = -1;
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
