Re: [sqlite] How to create a table from a text file via c/c++interface?
It works. I changed " for (int i=0;i<1e5;i++) { rc = sqlite3_exec(db, "insert into wigfile values(1,2000,3000,0.4)", callback, 0, &zErrMsg); }" into "sqlite3_exec(db, "begin", callback, 0, &zErrMsg); for (int i=0;i<1e5;i++){ rc = sqlite3_exec(db, "insert into wigfile values(1,2000,3000,0.4)", callback, 0, &zErrMsg); } sqlite3_exec(db, "commit", callback, 0, &zErrMsg);" the second one run much faster the first one. Thank you very much. Zhixing 2009-05-04 knightfeng 发件人: Griggs, Donald 发送时间: 2009-05-04 11:16:35 收件人: General Discussion of SQLite Database 抄送: 主题: Re: [sqlite] How to create a table from a text file via c/c++interface? Hi, Feng, I don't think that "load data from" is part of the sql standard -- I may be wrong. At any rate, the source code to the command line utility is freely available. When you wrote that "Using ' insert into values' ... is very time comsuming" did you mean time consuming to write the program, or time-consuming to run? If it is taking a very long time to RUN compare to the sqlite3 ".import" command, then be sure you're surrounding the import (or at least every 1000 lines or so) in a TRANSACTION. Expect a large increase in speed. http://www.sqlite.org/lang_transaction.html Perhaps this helps, Donald Griggs -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of knightfeng Sent: Sunday, May 03, 2009 10:41 PM To: sqlite-users Subject: [sqlite] How to create a table from a text file via c/c++ interface? Hi, '.import' can be used to create a table from text file in the command line version , but how can I do that using C/C++ interface? Using ' insert into values' by reading a text file line by line is very time comsuming and it seems that there is no such SQL command "LOAD DATA FROM" supported by MySql in sqlite. Many thanks. Zhixing Feng 2009-05-04 knightfeng ___ 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] some help with datetime
"Gene Allen" wrote in message news:!&!AAAuAKY/0q6/55xppdwmexdclhybajwnk16tjt5kmprugns9ymeaem8aabafb8zhbqucrj+kez0vnifiaqaaa...@bystorm.com > I'm missing something simple since I can't seem to get it to work. > Here is an example of how I'm trying to get the week number: > > date(datetime(eventtime, 'unixepoch', 'localtime'), '%W') > > based on http://www.sqlite.org/lang_datefunc.html Your construct doesn't remotely resemble anything described on that page. You want something like strftime('%W', eventtime, 'unixepoch', 'localtime'); Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "COMMIT"
"Joanne Pham" wrote in message news:760656.34138...@web90308.mail.mud.yahoo.com > I have read the sqlite document and document stated that: > The SQL command "COMMIT" does not actually commit the changes to > disk. It just turns autocommit back on. You omitted the next sentence: "Then, at the conclusion of the command, the regular autocommit logic takes over and causes the actual commit to disk to occur." For all practical purposes, COMMIT immediately writes to disk. > The question is the default of database open connection is > "autocommit" and if my function has : > sqlite3_exec(pDb,"BEGIN;", NULL, 0, &errMsg); > sqlSt = sqlite3_step(pStmt); > .. > sqlSt = sqlite3_exec(pDb,"END;", NULL, 0, &errMsg); > > Then Do I need to sqlSt = sqlite3_exec(pDb,"COMMIT;", NULL, 0, > &errMsg) to turn on the autocommit again. COMMIT and END are synonyms - two names for the same command. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] The details of the behavior of the sqlite3_step() interface
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Joanne Pham wrote: > I read the SQLite document about the sqlite3_step() as below: > The details of the behavior of the sqlite3_step() interface depend on > whether the statement was prepared using the newer "v2" interface > sqlite3_prepare_v2() and sqlite3_prepare16_v2() or the older legacy interface > sqlite3_prepare() and sqlite3_prepare16. > > So what is the detaill behavior of sqlite3_step() interface when: > 1) The statement using sqlite3_prepare() vs > 2) sqlite3_prepare_v2() That information is given in the very next paragraph on the page you quoted as well as a bolded parapgraph at the bottom. http://www.catb.org/~esr/faqs/smart-questions.html Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAkn+bssACgkQmOOfHg372QRsHQCeIB7h2MLl1yk/gBgqBWS6WhTk zzgAoKDwQwOwvd5YFUhYUfZCeXM/GQD6 =Gz7F -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] "COMMIT"
Hi All, I have read the sqlite document and document stated that: The SQL command "COMMIT" does not actually commit the changes to disk. It just turns autocommit back on. The question is the default of database open connection is "autocommit" and if my function has : sqlite3_exec(pDb,"BEGIN;", NULL, 0, &errMsg); sqlSt = sqlite3_step(pStmt); .. sqlSt = sqlite3_exec(pDb,"END;", NULL, 0, &errMsg); Then Do I need to sqlSt = sqlite3_exec(pDb,"COMMIT;", NULL, 0, &errMsg) to turn on the autocommit again. Thanks JP ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to create a table from a text file via c/c++ interface?
Hi, Feng, I don't think that "load data from" is part of the sql standard -- I may be wrong. At any rate, the source code to the command line utility is freely available. When you wrote that "Using ' insert into values' ... is very time comsuming" did you mean time consuming to write the program, or time-consuming to run? If it is taking a very long time to RUN compare to the sqlite3 ".import" command, then be sure you're surrounding the import (or at least every 1000 lines or so) in a TRANSACTION. Expect a large increase in speed. http://www.sqlite.org/lang_transaction.html Perhaps this helps, Donald Griggs -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of knightfeng Sent: Sunday, May 03, 2009 10:41 PM To: sqlite-users Subject: [sqlite] How to create a table from a text file via c/c++ interface? Hi, '.import' can be used to create a table from text file in the command line version , but how can I do that using C/C++ interface? Using ' insert into values' by reading a text file line by line is very time comsuming and it seems that there is no such SQL command "LOAD DATA FROM" supported by MySql in sqlite. Many thanks. Zhixing Feng 2009-05-04 knightfeng ___ 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] some help with datetime
Hello all, I have a table with a unixepoch column in it. I need to do some fancy calculation based on that time, hourly summaries, weekly averages, standard dev, etc.. Right now..I'm only using it for reporting and simple analysis and it's often formatted like this: datetime(eventtime, 'unixepoch', 'localtime') What I need is a count all the records grouped by week or by day or by month so I can do some more advanced processing on it. I'm missing something simple since I can't seem to get it to work. Here is an example of how I'm trying to get the week number: date(datetime(eventtime, 'unixepoch', 'localtime'), '%W') based on http://www.sqlite.org/lang_datefunc.html it seemed to me that the date function needs a string so I passed in a formatted string. No joy. Can someone point out the error in my ways? Thank you, Gene ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to create a table from a text file via c/c++ interface?
Hi, '.import' can be used to create a table from text file in the command line version , but how can I do that using C/C++ interface? Using ' insert into values' by reading a text file line by line is very time comsuming and it seems that there is no such SQL command "LOAD DATA FROM" supported by MySql in sqlite. Many thanks. Zhixing Feng 2009-05-04 knightfeng ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] The details of the behavior of the sqlite3_step() interface
Hi All, I read the SQLite document about the sqlite3_step() as below: The details of the behavior of the sqlite3_step() interface depend on whether the statement was prepared using the newer "v2" interface sqlite3_prepare_v2() and sqlite3_prepare16_v2() or the older legacy interface sqlite3_prepare() and sqlite3_prepare16. So what is the detaill behavior of sqlite3_step() interface when: 1) The statement using sqlite3_prepare() vs 2) sqlite3_prepare_v2() Thanks, JP ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_finalize(sqlite3_stmt) is this call clean up thememory
Thanks a lot Igor for respond my email. JP From: Igor Tandetnik To: sqlite-users@sqlite.org Sent: Sunday, May 3, 2009 7:05:52 PM Subject: Re: [sqlite] sqlite3_finalize(sqlite3_stmt) is this call clean up thememory "Joanne Pham" wrote in message news:111052.72599...@web90308.mail.mud.yahoo.com > Is sqlite3_finalize(sqlite3_stmt) cleaning up the memory which is > allocated by sqlite_prepare()? > I checked the database statement handle before calling > sqlite3_finalize and after calling this sqlite3_finalize the address > is the same. int* p = new int; printf("Before: %p\n", p); delete p; printf("After: %p\n", p); Try this code - you'll see that it prints the same address twice. Does this surprise you? Would you take it as a sign that this code somehow fails to deallocate all the memory it allocates? 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] sqlite3_finalize(sqlite3_stmt) is this call clean up thememory
"Joanne Pham" wrote in message news:111052.72599...@web90308.mail.mud.yahoo.com > Is sqlite3_finalize(sqlite3_stmt) cleaning up the memory which is > allocated by sqlite_prepare()? > I checked the database statement handle before calling > sqlite3_finalize and after calling this sqlite3_finalize the address > is the same. int* p = new int; printf("Before: %p\n", p); delete p; printf("After: %p\n", p); Try this code - you'll see that it prints the same address twice. Does this surprise you? Would you take it as a sign that this code somehow fails to deallocate all the memory it allocates? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3_finalize(sqlite3_stmt) is this call clean up the memory
Hi All, Is sqlite3_finalize(sqlite3_stmt) cleaning up the memory which is allocated by sqlite_prepare()? I checked the database statement handle before calling sqlite3_finalize and after calling this sqlite3_finalize the address is the same. I was wordering if the memory of database statement handle is cleaning up. Thanks, JP ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] OT: how best to convert sqlite3_int64 to and from string in a cross platform fashion?
Sorry, i forgot: my INT64 is the same as sqlite_int64 Martin Engelschalk wrote: > Hi, > > This is what i do, works for me. No OSX hovever, sorry. > > #ifdef _WIN32 > #define INT64 __int64 > #else > #define INT64 long long > #endif > > // From INT64 to String > > INT64 iOther; > char mBuffer[64]; > > #ifdef _WIN32 > sprintf(mBuffer, "%I64d", iOther); > #else > sprintf(mBuffer, "%lld", iOther); > #endif > > // From String to INT64 > > #ifdef _WIN32 > iOther = _atoi64(mBuffer); > #elif defined(AIX5) > iOther = strtoll(mBuffer, 0, 10); > #elif defined(HPUX) > iOther = __strtoll(mBuffer, 0, 10); > #else > iOther = atoll(mBuffer); > #endif > > Martin > > Sam Carleton wrote: > >> I am current developing a system only on Windows, but I do plan to port it >> to OSX someday. I am passing ID's as strings to keep maximum flexibility >> between databases and the existing system. So how do I convert a >> sqlite3_int64 to a string and a string to a sqlite3_int64 in a cross >> platform fashion? >> >> Sam >> ___ >> 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] OT: how best to convert sqlite3_int64 to and from string in a cross platform fashion?
Hi, This is what i do, works for me. No OSX hovever, sorry. #ifdef _WIN32 #define INT64 __int64 #else #define INT64 long long #endif // From INT64 to String INT64 iOther; char mBuffer[64]; #ifdef _WIN32 sprintf(mBuffer, "%I64d", iOther); #else sprintf(mBuffer, "%lld", iOther); #endif // From String to INT64 #ifdef _WIN32 iOther = _atoi64(mBuffer); #elif defined(AIX5) iOther = strtoll(mBuffer, 0, 10); #elif defined(HPUX) iOther = __strtoll(mBuffer, 0, 10); #else iOther = atoll(mBuffer); #endif Martin Sam Carleton wrote: > I am current developing a system only on Windows, but I do plan to port it > to OSX someday. I am passing ID's as strings to keep maximum flexibility > between databases and the existing system. So how do I convert a > sqlite3_int64 to a string and a string to a sqlite3_int64 in a cross > platform fashion? > > Sam > ___ > 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] OT: how best to convert sqlite3_int64 to and from string in a cross platform fashion?
I am current developing a system only on Windows, but I do plan to port it to OSX someday. I am passing ID's as strings to keep maximum flexibility between databases and the existing system. So how do I convert a sqlite3_int64 to a string and a string to a sqlite3_int64 in a cross platform fashion? Sam ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [newbie] How to upgrade sqlite3 in Ubuntu?
On Sun, May 3, 2009 at 12:32 PM, scientist scientist < scientist92...@yahoo.com> wrote: > Thank you for your fast answer, but my problem still exists after I > followed your instructions. > > Firstly I removed the default sqlite3 using sudo apt-get remove sqlite3 > command. > After that I moved to the sqlite-3.6.13 folder which was extracted from > sqlite-amalgamation-3.6.13.tar.gz and executed the 3 commands: > ../configure --prefix=/usr/local > make > sudo make install > I did check the PATH variable and It did contain /usr/local/bin. > However, when I entered > sqlite3 > the output was still 3.4.2 > The weird thing is that, after executing all of these above commands, and > then sudo apt-get remove sqlite3, I got the following message: > Package sqlite3 is not installed, so not removed > I can't understand what's going on. > Did you type "which sqlite3" to figure out where it's find it? Wherever that one is, you need to remove it. Derrell ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [newbie] How to upgrade sqlite3 in Ubuntu?
Thank you for your fast answer, but my problem still exists after I followed your instructions. Firstly I removed the default sqlite3 using sudo apt-get remove sqlite3 command. After that I moved to the sqlite-3.6.13 folder which was extracted from sqlite-amalgamation-3.6.13.tar.gz and executed the 3 commands: ../configure --prefix=/usr/local make sudo make install I did check the PATH variable and It did contain /usr/local/bin. However, when I entered sqlite3 the output was still 3.4.2 The weird thing is that, after executing all of these above commands, and then sudo apt-get remove sqlite3, I got the following message: Package sqlite3 is not installed, so not removed I can't understand what's going on. --- On Sun, 5/3/09, Derrell Lipman wrote: From: Derrell Lipman Subject: Re: [sqlite] [newbie] How to upgrade sqlite3 in Ubuntu? To: "General Discussion of SQLite Database" Date: Sunday, May 3, 2009, 8:56 AM On Sun, May 3, 2009 at 11:37 AM, scientist scientist < scientist92...@yahoo.com> wrote: > Hi all, > My current Ubuntu version is 8.04 and it has sqlite3 3.4.2 by default. Now > I want to upgrade sqlite3 to its newest version. > I've already downloaded sqlite-amalgamation-3.6.13.tar.gz, uncompressed the > package and run the following commands: > ../configure > make > sudo make install > But when I enter > sqlite3 > the output result is still version 3.4.2 > > How can I upgrade sqlite3 to its latest version 3.6.13? > The amalgamation probably installed into some directory not in your path. You should look at where it installed (re-run ../configure and look at its output, which should tell you where it will install to. For Ubuntu, you almost certainly want it to install into /usr/local with the executable going into /usr/local/bin. If it chose some path other than /usr/local, you probably want to remove it from wherever it installed to. Next, remove the Ubuntu-provided version of sqlite3 since you won't need it any longer: sudo apt-get remove sqlite3 Now rerun configure like this, to specify the install prefix, and then rebuild and reinstall: ../configure --prefix=/usr/local make sudo make install If you're running csh type "rehash" to rescan your path. Ensure that /usr/local/bin is in your path: echo $PATH If not, add /usr/local/bin to your path (typically before /usr/bin in the list) in your shell start-up file. You can also type "which sqlite3" to see which version your shell is finding first in the PATH. Cheers, Derrell ___ 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] sqlite3_create_collation_v2 and SQLITE_UTF16_ALIGNED
On May 3, 2009, at 11:15 AM, Florian Weimer wrote: > The documentation suggests that I can pass SQLITE_UTF16_ALIGNED. > However, the logic in main.c:createCollation() assumes that > SQLITE_UTF16_ALIGNED is ORed with another encoding flag value > (presumably SQLITE_UTF16). If I specify SQLITE_UTF16_ALIGNED alone, I > end up with a crash due to a double free bug inside SQLite. > > (This has been observed with SQLite 3.6.13.) This has already been fixed. See http://www.sqlite.org/cvstrac/chngview?cn=6558 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] [newbie] How to upgrade sqlite3 in Ubuntu?
On Sun, May 3, 2009 at 11:37 AM, scientist scientist < scientist92...@yahoo.com> wrote: > Hi all, > My current Ubuntu version is 8.04 and it has sqlite3 3.4.2 by default. Now > I want to upgrade sqlite3 to its newest version. > I've already downloaded sqlite-amalgamation-3.6.13.tar.gz, uncompressed the > package and run the following commands: > ../configure > make > sudo make install > But when I enter > sqlite3 > the output result is still version 3.4.2 > > How can I upgrade sqlite3 to its latest version 3.6.13? > The amalgamation probably installed into some directory not in your path. You should look at where it installed (re-run ../configure and look at its output, which should tell you where it will install to. For Ubuntu, you almost certainly want it to install into /usr/local with the executable going into /usr/local/bin. If it chose some path other than /usr/local, you probably want to remove it from wherever it installed to. Next, remove the Ubuntu-provided version of sqlite3 since you won't need it any longer: sudo apt-get remove sqlite3 Now rerun configure like this, to specify the install prefix, and then rebuild and reinstall: ../configure --prefix=/usr/local make sudo make install If you're running csh type "rehash" to rescan your path. Ensure that /usr/local/bin is in your path: echo $PATH If not, add /usr/local/bin to your path (typically before /usr/bin in the list) in your shell start-up file. You can also type "which sqlite3" to see which version your shell is finding first in the PATH. Cheers, Derrell ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [newbie] How to upgrade sqlite3 in Ubuntu?
Hi all, My current Ubuntu version is 8.04 and it has sqlite3 3.4.2 by default. Now I want to upgrade sqlite3 to its newest version. I've already downloaded sqlite-amalgamation-3.6.13.tar.gz, uncompressed the package and run the following commands: ../configure make sudo make install But when I enter sqlite3 the output result is still version 3.4.2 How can I upgrade sqlite3 to its latest version 3.6.13? Thanks in advanced! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3_create_collation_v2 and SQLITE_UTF16_ALIGNED
The documentation suggests that I can pass SQLITE_UTF16_ALIGNED. However, the logic in main.c:createCollation() assumes that SQLITE_UTF16_ALIGNED is ORed with another encoding flag value (presumably SQLITE_UTF16). If I specify SQLITE_UTF16_ALIGNED alone, I end up with a crash due to a double free bug inside SQLite. (This has been observed with SQLite 3.6.13.) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users