Re: [sqlite] HELP : how to use datetime('column', 'localtime') as a part of sql string
Here is the code of my TestStatusDate
public static void main(String[] args) throws Exception
{
String jobQuery = "SELECT j.jobId, c.channelName ,
datetime(j.jobCreateDate, 'localtime'), j.jobStatus, j.jobQuality,
j.jobCompleteDate FROM Job j, Channel c where c.channelId =
j.jobChannelId ORDER BY jobId DESC";
long startTime = System.currentTimeMillis();
Statement mStatement;
ResultSet mResults;
CachedRowSetImpl impl=null;
Connection mconn = null;
try
{
Class.forName("org.sqlite.JDBC");
mconn = DriverManager.getConnection("jdbc:sqlite:"+
"database.db");
mStatement = mconn.createStatement();
mResults = mStatement.executeQuery(jobQuery);
impl = new CachedRowSetImpl();
impl.populate(mResults);
mStatement.close();
mResults.close();
}
catch (SQLException e) {
e.printStackTrace();
}
long endTime = System.currentTimeMillis();
Locale l = Locale.getDefault();
System.out.println("Today's Locale is " + l);
System.out.println("timezone ==>" + (TimeZone.getDefault()));
System.out.println("Time taken to execute query ==>" +(endTime
- startTime));
try {
while(impl.next()){
StringBuilder sb = new StringBuilder();
sb.append(impl.getLong(1)+",");
sb.append(impl.getString(2)+",");
sb.append(impl.getString(3)+",");
sb.append(impl.getString(4)+",");
sb.append(impl.getString(5)+",");
sb.append(impl.getString(6)+",");
System.out.println(sb.toString());
}
} catch (SQLException e) {
e.printStackTrace();
}
}
On 02-06-2011 22:02, Pavel Ivanov wrote:
> And your TestStatusDate code is?
>
> I'd bet now that the problem is in the way you get that time from
> jdbc. It can convert the time back to utc for you, although it looks a
> little strange that it converts one date and doesn't convert other
> one...
>
>
> Pavel
>
>
> On Thu, Jun 2, 2011 at 11:52 AM, Sridhar Polavarapu
> wrote:
>> I am on Windows 7 64 bit; I have tested the program as you mentioned, the
>> good point here was the issue still appears please find logs . Let me know
>> if you infer anything from below.
>>
>> C:\development\SignalCoreClient\test\com\Rimage\Starfish\AutomatedTests>java
>> TestStatusDate
>> Today's Locale is en_IN
>> timezone
>> ==>sun.util.calendar.ZoneInfo[id="Asia/Calcutta",offset=1980,dstSavings=0,useDaylight=false,transitions=6,lastRule=null]
>> Time taken to execute query ==>3623
>> 762,automatedTest_13067437059065180049472173,2011-06-02
>> 13:24:53,COMPLETE,md,2011-06-02 13:26:14,
>> 761,automatedTest_13067437059065180049472173,2011-06-02
>> 13:24:52,COMPLETE,md,2011-06-02 13:26:07,
>> 760,automatedTest_13067437059065180049472173,2011-06-02
>> 13:24:52,COMPLETE,md,2011-06-02 13:25:59,
>> 759,automatedTest_13067437059065180049472173,2011-06-02
>> 13:24:51,COMPLETE,md,2011-06-02 13:25:41,
>> 758,automatedTest_13067437059065180049472173,2011-06-02
>> 13:24:50,COMPLETE,md,2011-06-02 13:25:36,
>> 757,automatedTest_13067437059065180049472173,2011-06-02
>> 13:24:50,COMPLETE,md,2011-06-02 13:25:30,
>> 756,automatedTest_13067437059065180049472173,2011-06-02
>> 13:24:49,COMPLETE,md,2011-06-02 13:25:23,
>> 755,automatedTest_130675855965820033590511079,2011-06-02
>> 13:16:09,COMPLETE,md,2011-06-02 13:17:30,
>> 754,automatedTest_130675855965820033590511079,2011-06-02
>> 13:16:08,COMPLETE,md,2011-06-02 13:17:25,
>> 753,automatedTest_130675855965820033590511079,2011-06-02
>> 13:16:07,COMPLETE,md,2011-06-02 13:17:12,
>> 752,automatedTest_130675855965820033590511079,2011-06-02
>> 13:16:07,COMPLETE,md,2011-06-02 13:16:51,
>> 751,automatedTest_130675855965820033590511079,2011-06-02
>> 13:16:06,COMPLETE,md,2011-06-02 13:16:48,
>> 750,automatedTest_130675855965820033590511079,2011-06-02
>> 13:16:05,COMPLETE,md,2011-06-02 13:19:17,
>> 749,automatedTest_1307018964144181250169479584,2011-06-02
>> 12:49:30,COMPLETE,hi,2011-06-02 12:57:58,
>> 748,NaveenTest,2011-06-02 12:49:29,COMPLETE,hi,2011-06-02 12:56:59,
>> 747,automatedTest_1307018192020180478065994419,2011-06-02
>> 12:36:39,COMPLETE,hi,2011-06-02 12:56:30,
>> 746,NaveenTest,2011-06-02 12:36:37,COMPLETE,hi,2011-06-02 12:55:33,
>> 745,automatedTest_130675855965720033590323324,2011-06-02
>> 11:52:21,COMPLETE,md,2011-06-02 11:52:54,
>> 744,automatedTest_1306482594629320685646725968,null,COMPLETE,md,2011-06-02
>> 10:18:47,
>> 743,automatedTest_130675534731016821286104758,2011-06-02
>> 15:15:25,INIT,md,null,
>> 742,automatedTest_130675534731016821286104758,2011-06-02
>> 15:08:15,INIT,md,null,
>> 741,automatedTest_130675534731016821286104758,2011-06-02
>> 09:34:06,COMPLETE,md,2011-06-02 09:34:46,
>> 740,automatedTest_130675855965820033
Re: [sqlite] Making data and tables persistent
Simon Slavin wrote: > On 3 Jun 2011, at 3:50am, Sarkar, Arup wrote: > >> I am using sqlite3 primarily from c++, everything is working fine, except >> when I switch off my computer I loose all data, is there any setting I need >> to do to make the data and table object persistent in the .db file? > > Are you correctly closing your connection to the database before your > application quits ? > > Does a file with the correct name exist on your disk ? Does it have zero > length ? For that matter, maybe hinted from the second point here, are you using a regular file-based database or a MEMORY one? -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Making data and tables persistent
On 3 Jun 2011, at 3:50am, Sarkar, Arup wrote: > I am using sqlite3 primarily from c++, everything is working fine, except > when I switch off my computer I loose all data, is there any setting I need > to do to make the data and table object persistent in the .db file? Are you correctly closing your connection to the database before your application quits ? Does a file with the correct name exist on your disk ? Does it have zero length ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Making data and tables persistent
Hi: I am using sqlite3 primarily from c++, everything is working fine, except when I switch off my computer I loose all data, is there any setting I need to do to make the data and table object persistent in the .db file? Environment: Windows 7 Professional 64 bit. Dev Env: Visual Studio 2010. Regards, Arup ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Big difference in performance between Python and gcc
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 06/02/2011 02:17 PM, Simon Slavin wrote: > Do you understand the strange result the OP reported ? There is no evidence that Python is any way relevant to this issue and the OP appears to have gone silent. I expect the actual cause is how SQLite was compiled. Both Python wrappers include the amalgamation statically within the extension (ie there is no DLL or dynamic linking involved) on Windows. Edzard also showed how much faster my wrapper is that the standard Python one. I aim to please :) The other differences are documented here: http://apidoc.apsw.googlecode.com/hg/pysqlite.html Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) iEYEARECAAYFAk3oSPUACgkQmOOfHg372QRwxgCgjW9Y4X52DSe9XqUWiOfTkqO1 tPsAn3sry8hFbioD6mHOXsCfWfJIo3XM =KIcg -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in sqlite3_bind_parameter_name
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 06/02/2011 07:09 PM, Igor Tandetnik wrote: > For a subselect like the one in question, SQLite picks the value from > the first row - whichever row this happens to be - and ignores the rest. > Providing an explicit LIMIT 1, or for that matter LIMIT 77, doesn't > change anything. In particular, it doesn't make the ordering, and the > choice of which row will be the first, any more deterministic. So you > may just as well drop the LIMIT clause. We are whacking in the weeds here, and actually substantially agree :-) My point was that if a LIMIT of 1 is supplied then it can be reasonably certain that the subquery usage was intentional whereas if a LIMIT of 77 is supplied then it is extremely likely that the subquery is erroneous. SQLite *currently* just takes whatever the first row is (as documented), but could in the future have a "lint" or similar mode where it explicitly rejects or warns about what appear to be erroneous queries. If SQLite ever does that then this query was correct in the sense that there is no reason to reject or warn about it, whereas a LIMIT of 77 would be. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) iEYEARECAAYFAk3oRysACgkQmOOfHg372QTXIACgy28yfrdTgQOrnFQzq7VV1Aoj bu4AoL8z8F0NQuwQe3qPXDhe/6DNgqWb =fdd3 -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in sqlite3_bind_parameter_name
On 6/2/2011 9:57 PM, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 06/02/2011 12:31 PM, Jay A. Kreibich wrote: >>I suppose it would be technically correct to allow a LIMIT to 0, but >>that seems like a highly questionable approach. Any parameter value >>outside of 0 and 1 *is* non-sense and should be dutifully ignored >>and/or corrected-- or the whole statement should be considered >>invalid and an error thrown. >> >>While the phantom parameter issue might be worth addressing, in >>this specific case I think it is fair to call the query incorrect. > > The query used a binding to provide a limit of 1. If the binding is > discarded then SQLite has no idea of what value was going to be provided, > and hence had no idea if it was going to supply say 77 which is definitely > incorrect. For a subselect like the one in question, SQLite picks the value from the first row - whichever row this happens to be - and ignores the rest. Providing an explicit LIMIT 1, or for that matter LIMIT 77, doesn't change anything. In particular, it doesn't make the ordering, and the choice of which row will be the first, any more deterministic. So you may just as well drop the LIMIT clause. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in sqlite3_bind_parameter_name
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 06/02/2011 12:31 PM, Jay A. Kreibich wrote: > I suppose it would be technically correct to allow a LIMIT to 0, but > that seems like a highly questionable approach. Any parameter value > outside of 0 and 1 *is* non-sense and should be dutifully ignored > and/or corrected-- or the whole statement should be considered > invalid and an error thrown. > > While the phantom parameter issue might be worth addressing, in > this specific case I think it is fair to call the query incorrect. The query used a binding to provide a limit of 1. If the binding is discarded then SQLite has no idea of what value was going to be provided, and hence had no idea if it was going to supply say 77 which is definitely incorrect. SQLite already has some functionality to help ensure queries aren't getting lucky. http://www.sqlite.org/pragma.html#pragma_reverse_unordered_selects Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) iEYEARECAAYFAk3oP5cACgkQmOOfHg372QTllQCfat1knfCR5zez1zuAxpplFPYk +qgAn2EHJfwf/RsUCwZwEcVF7cDUID1I =CxFJ -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Create DB file and then Create Table - Table FAILS.
"I can't find any documentation for using that library with C++." Yeah--tell me about it. I've been searching and searching without much luck at all. The only examples I've seen talk about including sqlite.h and then using sqlite functions as opposed to using objects and namespaces. But when I try to run using the example code, I get errors that sqlite.h can't found. The code snippet I previously provided builds and runs without producing any error messages--it creates the DB file but doesn't create the table. But if I try to apply your suggestion, it throws errors that 'System::Data::SQLite::SQLiteCommand::SQLiteCommand' : cannot access private member declared in class 'System::Data::SQLite::SQLiteCommand'. On 6/1/2011 7:16 PM, Simon Slavin wrote: > I can't find any documentation for using that library with C++. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .import error: cannot open large file
Hello, On Fri, Jun 3, 2011 at 00:08, Rense Corten wrote: [...] > So I searched the archives of this list and found two threads on this: > > http://www.mail-archive.com/sqlite-users@sqlite.org/msg51574.html > http://www.mail-archive.com/sqlite-users@sqlite.org/msg48649.html > > The first thread got no answers, but the second suggest to either > split the file or recompile sqlite3 with the option for large file > support . Now I had understood that since version 3.5.9 large file > support is switched on by default so that should not be the problem > (http://www.sqlite.org/changes.html). Splitting the file, however, > seems to solve the problem. I would prefer not to have to split the > file first. > > Any ideas on what causes this problem? It's what the thread says. The SQLite shell on Ubuntu (on 11.04) isn't compiled with large file support on 32-bit systems, so while the SQLite library does work with 64-bit database, the shell doesn't. The easy solution is to either use a 64-bit Ubuntu system or compile the shell yourself with large file support. To compile it, download the sqlite amalgamation files and run: gcc -o sqli -O3 -DNDEBUG=1 -D_FILE_OFFSET_BITS=64 sqlite3.c shell.c -ldl -pthread The resulting binary (sqli) will be compiled with large file support (I verified it was using strace). Regards, ~Nuno Lucas P.S.- While this could be considered an Ubuntu bug, the truth is that the linux shell binary on the sqlite site also isn't compiled with large file support, so I would consider this an SQLite bug. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] .import error: cannot open large file
Hi all, I'm trying to import a large file of about 13GB using SQLite 3.7.6.3 on Ubuntu. I use the precompiled Linux binary. The commands are: = .separator ";" .import largefile.csv mytable = but then I get: "Error: cannot open "largefile.csv" " I can view the file with "head" or "less", so there seems to be no problem with readability or permissions. Moreover, I can (partially) import the same file on a different Ubuntu system using the exact same commands (but run out of storage space before the import completes). So I searched the archives of this list and found two threads on this: http://www.mail-archive.com/sqlite-users@sqlite.org/msg51574.html http://www.mail-archive.com/sqlite-users@sqlite.org/msg48649.html The first thread got no answers, but the second suggest to either split the file or recompile sqlite3 with the option for large file support . Now I had understood that since version 3.5.9 large file support is switched on by default so that should not be the problem (http://www.sqlite.org/changes.html). Splitting the file, however, seems to solve the problem. I would prefer not to have to split the file first. Any ideas on what causes this problem? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Big difference in performance between Python and gcc
Op 2-jun-2011, om 23:17 heeft Simon Slavin het volgende geschreven:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
>
> On 2 Jun 2011, at 7:24pm, Roger Binns wrote:
>
>> (Incidentally I am the author of a "competing" Python SQLite
>> binding and
>> hence know exactly which SQLite API calls result from bits of
>> Python hence
>> being very pedantic about getting these tests the same.)
>
> How does your own Python binding perform ? Is it fast like the
> other Python binding, or slow like the GCC-compiled C code the OP
> reported ? Do you understand the strange result the OP reported ?
>
> Simon.
If I may answer the first question: APSW is even 31.7 % faster than
the default Python wrapper, using the version below. It is however
relevant to drop the table before each new test run. (I changed
'create if not exists' to 'drop if exists' in both tests).
import apsw
import timeit
conn = apsw.Connection('test1.sqlite')
c=conn.cursor()
c.execute('''DROP TABLE IF EXISTS values_log; CREATE TABLE values_log
(acquisition
INTEGER,chunk INTEGER, acq_data BLOB);
CREATE INDEX IF NOT EXISTS values_step ON
values_log(acquisition,chunk);
''' )
def f():
data="01234567"*1024
with conn:
for i in range(0,1):
conn.cursor ().execute("INSERT INTO values_log VALUES (?,?,?)",
(1,i,data))
if not i%1: print i
ret=timeit.timeit(f,'gc.enable()',number=1)
print ret
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Big difference in performance between Python and gcc
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 2 Jun 2011, at 7:24pm, Roger Binns wrote: > (Incidentally I am the author of a "competing" Python SQLite binding and > hence know exactly which SQLite API calls result from bits of Python hence > being very pedantic about getting these tests the same.) How does your own Python binding perform ? Is it fast like the other Python binding, or slow like the GCC-compiled C code the OP reported ? Do you understand the strange result the OP reported ? Simon. -BEGIN PGP SIGNATURE- Version: GnuPG/MacGPG2 v2.0.17 (Darwin) Comment: GPGTools - http://gpgtools.org iQEcBAEBAgAGBQJN5/3qAAoJEIgFUSuEK6DjU8kH/Rp9s0Pa50UZE7o2eTgnkJCA PTMDw0gavFgscxqgnUXdeKWKWuu3Q4iqbt4HFKfNthF3ozbtZjnCzFCWPinTM9KF DgU9oyqfScutWSPdQKQcl/JhkGlvQ3zaB4QUI/PN8/+b+6K8h1uIr8CLCktVh6Kt 1hc3GXSyDJnyRv2pv7yn1t0aDdFvGM1L+mBF+anWQu5LlUZVL6xfD8rtSKNyFQYW lYz1gE+RElGarzpF2UoOp5M4xhomTcfBA0B+6spQ0M68r2eXzkq1baq1gbJKgzeI OkLvpt++HCXrLb4JtKfsS6OH20384RvMFu/yPjAuCUQUr75DAaMFZ7yOCBUOSsM= =RBhc -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in sqlite3_bind_parameter_name
Roger Binns писал(а) в своём письме Fri, 03 Jun 2011 00:30:12 +0600: > SQLite was actually in error IMHO. The subquery is the right hand side of > an equality and SQLite was looking for a single row/value. The query > generation code specifically added a LIMIT to ensure that only one row was > produced. SQLite ignored the LIMIT and hard coded the value one. This > "optimisation" resulted in the binding specifying that limit to be optimized > out causing the whole problem. I don't see much sense in specifying LIMIT value via binding parameter, in the first place. When SQLite prepares your query, it builds a query plan. And the most efficient query plan might depend on the given LIMIT value: a) SELECT ... LIMIT 1000 -> one query plan (full scan of the table data) b) SELECT ... LIMIT 1 -> another query plan (quick lookup in the index) c) SELECT ... LIMIT 0 -> third query plan (query get optimized away) Now how SQLite should build a proper plan for your query if you hide limiting value into bind parameter, so the actual value is unknown on prepare stage? That's why I consider such query "stupid" (okay, maybe it's too rough... let's say "silly" ;)) - you should either specify LIMIT 1 explicitly, or avoid using LIMIT there at all, SQLite will do that for ya. :) Regards, Serge ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in sqlite3_bind_parameter_name
On Thu, Jun 02, 2011 at 11:30:12AM -0700, Roger Binns scratched on the wall: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 06/02/2011 01:18 AM, Dagdamor wrote: > > Patrick Earl ?(?) ? ? ?? Wed, 01 Jun 2011 > > 22:24:10 +0600: > > > >> Is there another work-around? > > > > Yes. Do not feed SQLite with stupid queries. ;) > > The query was actually correct and not stupid in any way. > > If, according to SQLite standards, LIMIT shouldn't be specified in > > that subquery, then you shouldn't use it. > > SQLite was actually in error IMHO. The subquery is the right hand side of > an equality and SQLite was looking for a single row/value. As documented. A sub-query used as an expression generates a single scalar value. As such, it is expected to return only one column, and only the first row is used: http://sqlite.org/lang_expr.html Scalar Subqueries A SELECT statement enclosed in parentheses may appear as a scalar quantity. A SELECT used as a scalar quantity must return a result set with a single column. The result of the expression is the value of the only column in the first row returned by the SELECT statement. If the SELECT yields more than one result row, all rows after the first are ignored. If the SELECT yields no rows, then the value of the expression is NULL. All types of SELECT statement, including aggregate and compound SELECT queries (queries with keywords like UNION or EXCEPT) are allowed as scalar subqueries. If you want a sub-query to act as an IN operator that can deal with more than one value on the right hand side, then you actually need to use the IN operator, not an equality operator. When using IN, the LIMIT becomes meaningful, and I'm sure it won't be optimized out. See "The IN and NOT IN opeators" on the above URL. > SQLite ignored the LIMIT and hard coded the value one. This > "optimisation" resulted in the binding specifying that limit to > be optimized out causing the whole problem. I suppose it would be technically correct to allow a LIMIT to 0, but that seems like a highly questionable approach. Any parameter value outside of 0 and 1 *is* non-sense and should be dutifully ignored and/or corrected-- or the whole statement should be considered invalid and an error thrown. While the phantom parameter issue might be worth addressing, in this specific case I think it is fair to call the query incorrect. -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
Re: [sqlite] Bug in sqlite3_bind_parameter_name
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 06/02/2011 01:18 AM, Dagdamor wrote: > Patrick Earl писал(а) в своём письме Wed, 01 Jun 2011 > 22:24:10 +0600: > >> Is there another work-around? > > Yes. Do not feed SQLite with stupid queries. ;) The query was actually correct and not stupid in any way. > If, according to SQLite standards, LIMIT shouldn't be specified in that > subquery, then you shouldn't use it. SQLite was actually in error IMHO. The subquery is the right hand side of an equality and SQLite was looking for a single row/value. The query generation code specifically added a LIMIT to ensure that only one row was produced. SQLite ignored the LIMIT and hard coded the value one. This "optimisation" resulted in the binding specifying that limit to be optimized out causing the whole problem. > Remember, it's you who should write essential and good queries, not SQLite > should do that for you. Remember, it's you who may not understand the issue in the first place with SQLite arguably doing the wrong thing with good queries :-) Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) iEYEARECAAYFAk3n1rQACgkQmOOfHg372QRTZwCfQQ4KE8mU7OVeM/FL6hWIXwRR BFcAn1Nj03RTF9j8scOa0IAYo49Ehfy8 =/9tF -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Big difference in performance between Python and gcc
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 06/02/2011 04:38 AM, Simon Slavin wrote: > Python is a bytecode language. That isn't relevant in this case. The code that interfaces Python to SQLite is written in C. The amount of Python bytecode involved in this benchmark is irrelevant. On 06/02/2011 12:55 AM, Dominique Pellé wrote: > However, for a benchmark, it's best to avoid things that > can be trivially optimized. I don't know how the python > code translates into SQLite C API. In this particular case the OP is unable to modify the Python SQLite binding and what is trying to be established is why the performance differs, not the optimum sequences of code. That is why it is important to make sure that they are measuring exactly the same thing before investigating the massive difference in run times. (Incidentally I am the author of a "competing" Python SQLite binding and hence know exactly which SQLite API calls result from bits of Python hence being very pedantic about getting these tests the same.) Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) iEYEARECAAYFAk3n1XEACgkQmOOfHg372QTINACgsnRCkY6k6FQJQ0zOjHQUcCxj ryEAnj+2dk8eUYtbImaEfwXAWSQzlyyU =TTPX -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TRANSACTIONs
On 2 Jun 2011, at 6:01pm, Jan Hudec wrote: > - Inside one transaction, running select may or may not see data inserted or > updated in parallel. Sorry to go on about this but you underestimate the problem. Suppose you're part-way through _step()ing through the results of a SELECT and an UPDATE modifies a field in your ORDER BY clause of one row. It could move the row you're currently on, moving it back to the beginning of the sort order. Or it could move it to the end of the sort order. Or it could move a row you haven't got to yet, to a position you've already gone past. When the next _step() moves to the 'next' record it might end up somewhere completely different to where you expected. In all these cases, without knowing undocumented internal details about how that version of SQLite works, you can't predict what will happen. Your SELECT might skip one or more rows: not just a row you modified but other untouched rows. Or it might read some rows twice: not just a row you modified but other untouched rows. Or it might be completely immune to the problem having fetched and stored row indexes somewhere. So don't do that. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error (LOCKFILE_EXCLUSIVE_LOCK) at building sqlite project for pocket pc
On Wed, Jun 01, 2011 at 12:09:35 -0700, hmas wrote: > Actually, I managed to make it work by selecting "Windows Mobile 5.0 Pocket > PC SDK" instead of "Pocket PC 2003" for the SDK in the procedure described > in my first post. Than it might be that WM5.0 already supports that interface while PPC2003 does not. Than it depends whether you can rely on WM5.0, since binary compiled this way won't work on raw WinCE 4.2 (those are custom devices, not phones, so it depends on what you are targeting). -- Jan 'Bulb' Hudec ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TRANSACTIONs
On Wed, Jun 01, 2011 at 19:23:16 +0100, Simon Slavin wrote: > On 1 Jun 2011, at 7:12pm, Jan Hudec wrote: > >> Do not update a table if there is some select statement currently > > > > Actually insert, update and delete are OK. Drop and alter table are > > a problem. > > Pavel is right. He left out some details to make things simple. > > Suppose you do a SELECT ... WHERE ... that returns ten rows. You retrieve > three rows, then make a change that would mean you should have retrieved > eleven rows, not ten. You can't predict what SQLite will do without > knowing the internal workings of SQLite, right ? So don't do that. The > same is true even if the only thing you change is values to be returned. > Does SQLite copy the all values when you execute the SELECT, or row-by-row > as you step through the results ? Again, you don't know unless you know > the internal workings of SQLite. So don't do that. Oh, right. There are actually two distinct problems: - Inside one transaction, running select may or may not see data inserted or updated in parallel. For selects on other handles it's however well defined that they will not read the data and no operations will fail. - Dropping or modifying table will fail (with "table is locked" status) if there are any running statements involving that table. -- Jan 'Bulb' Hudec ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] HELP : how to use datetime('column', 'localtime') as a part of sql string
And your TestStatusDate code is? I'd bet now that the problem is in the way you get that time from jdbc. It can convert the time back to utc for you, although it looks a little strange that it converts one date and doesn't convert other one... Pavel On Thu, Jun 2, 2011 at 11:52 AM, Sridhar Polavarapu wrote: > I am on Windows 7 64 bit; I have tested the program as you mentioned, the > good point here was the issue still appears please find logs . Let me know > if you infer anything from below. > > C:\development\SignalCoreClient\test\com\Rimage\Starfish\AutomatedTests>java > TestStatusDate > Today's Locale is en_IN > timezone > ==>sun.util.calendar.ZoneInfo[id="Asia/Calcutta",offset=1980,dstSavings=0,useDaylight=false,transitions=6,lastRule=null] > Time taken to execute query ==>3623 > 762,automatedTest_13067437059065180049472173,2011-06-02 > 13:24:53,COMPLETE,md,2011-06-02 13:26:14, > 761,automatedTest_13067437059065180049472173,2011-06-02 > 13:24:52,COMPLETE,md,2011-06-02 13:26:07, > 760,automatedTest_13067437059065180049472173,2011-06-02 > 13:24:52,COMPLETE,md,2011-06-02 13:25:59, > 759,automatedTest_13067437059065180049472173,2011-06-02 > 13:24:51,COMPLETE,md,2011-06-02 13:25:41, > 758,automatedTest_13067437059065180049472173,2011-06-02 > 13:24:50,COMPLETE,md,2011-06-02 13:25:36, > 757,automatedTest_13067437059065180049472173,2011-06-02 > 13:24:50,COMPLETE,md,2011-06-02 13:25:30, > 756,automatedTest_13067437059065180049472173,2011-06-02 > 13:24:49,COMPLETE,md,2011-06-02 13:25:23, > 755,automatedTest_130675855965820033590511079,2011-06-02 > 13:16:09,COMPLETE,md,2011-06-02 13:17:30, > 754,automatedTest_130675855965820033590511079,2011-06-02 > 13:16:08,COMPLETE,md,2011-06-02 13:17:25, > 753,automatedTest_130675855965820033590511079,2011-06-02 > 13:16:07,COMPLETE,md,2011-06-02 13:17:12, > 752,automatedTest_130675855965820033590511079,2011-06-02 > 13:16:07,COMPLETE,md,2011-06-02 13:16:51, > 751,automatedTest_130675855965820033590511079,2011-06-02 > 13:16:06,COMPLETE,md,2011-06-02 13:16:48, > 750,automatedTest_130675855965820033590511079,2011-06-02 > 13:16:05,COMPLETE,md,2011-06-02 13:19:17, > 749,automatedTest_1307018964144181250169479584,2011-06-02 > 12:49:30,COMPLETE,hi,2011-06-02 12:57:58, > 748,NaveenTest,2011-06-02 12:49:29,COMPLETE,hi,2011-06-02 12:56:59, > 747,automatedTest_1307018192020180478065994419,2011-06-02 > 12:36:39,COMPLETE,hi,2011-06-02 12:56:30, > 746,NaveenTest,2011-06-02 12:36:37,COMPLETE,hi,2011-06-02 12:55:33, > 745,automatedTest_130675855965720033590323324,2011-06-02 > 11:52:21,COMPLETE,md,2011-06-02 11:52:54, > 744,automatedTest_1306482594629320685646725968,null,COMPLETE,md,2011-06-02 > 10:18:47, > 743,automatedTest_130675534731016821286104758,2011-06-02 > 15:15:25,INIT,md,null, > 742,automatedTest_130675534731016821286104758,2011-06-02 > 15:08:15,INIT,md,null, > 741,automatedTest_130675534731016821286104758,2011-06-02 > 09:34:06,COMPLETE,md,2011-06-02 09:34:46, > 740,automatedTest_130675855965820033590602240,null,COMPLETE,md,2011-06-02 > 09:33:59, > 739,automatedTest_130675855965820033590602240,2011-06-02 > 14:56:11,INIT,md,null, > 738,automatedTest_130675855965820033590602240,2011-06-02 > 09:25:39,INIT,md,null, > 737,NaveenTest,2011-06-02 13:54:34,INIT,md,null, > 736,NaveenTest,2011-06-02 08:24:02,COMPLETE,md,2011-06-02 08:25:51, > 735,SridharTest,2011-06-02 13:23:24,INIT,md,null, > 734,SridharTest,2011-06-02 07:51:44,COMPLETE,md,2011-06-02 07:56:07, > 733,automatedTest_1306481519407319610451279968,2011-06-02 > 13:11:25,INIT,md,null, > 732,automatedTest_1306481519407319610451279968,2011-06-02 > 07:40:45,COMPLETE,md,2011-06-02 07:45:47, > 731,automatedTest_130675534731016821286048311,2011-06-02 > 07:25:25,COMPLETE,md,2011-06-02 07:28:14, > 730,automatedTest_130675534731016821286048311,2011-06-02 > 12:54:59,INIT,md,null, > 729,automatedTest_13067429149993217266307076,2011-06-02 > 07:12:25,COMPLETE,md,2011-06-02 07:16:24, > 728,SridharTest,2011-06-02 07:01:59,COMPLETE,md,2011-06-02 07:02:25, > 727,automatedTest_1306496245310334336328084528,2011-06-02 > 06:54:48,COMPLETE,md,2011-06-02 06:55:29, > 726,automatedTest_130648151940731961056560,2011-06-01 > 07:00:33,COMPLETE,md,2011-06-01 07:01:04, > 725,automatedTest_130651279595727994816196128,2011-05-31 > 13:56:47,COMPLETE,md,2011-05-31 13:57:44, > 724,automatedTest_130651279595727994816196128,2011-05-31 > 13:56:47,COMPLETE,md,2011-05-31 13:57:24, > 723,automatedTest_130651279595727994816196128,2011-05-31 > 13:56:46,COMPLETE,md,2011-05-31 13:57:20, > 722,automatedTest_130651279595727994816196128,2011-06-02 > 12:22:02,COMPLETE,md,2011-05-31 13:57:15, > > C:\development\SignalCoreClient\test\com\Rimage\Starfish\AutomatedTests>cd > \Users\sridhar\Desktop > > C:\Users\sridhar\Desktop>sqlite3.exe C:\Signal\Publisher\publisher.db > SQLite version 3.7.6.3 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> SELECT j.jobId, c.channelName , datetime(j.jobCreateDate, > 'localtime'), j.jobStatus, j.job
Re: [sqlite] dangerous allocation?
On Thu, Jun 2, 2011 at 10:53 AM, Richard Hipp wrote: > http://www.sqlite.org/src/ci/efb20b9da6 > > Note, however, that lemon.c is not a deliverable component of SQLite, but > rather a code generator program that generates some of the C code for > SQLite, and lemon always runs on a workstation, and so it is essentially > impossible for the calloc() to fail since modern workstations do not fail > mallocs. So this is not a real bug. But it is worth patching, all the > same. Thanks. Well, not really. Linux tends to not fail malloc()s ever, instead running an "OOM killer" to kill tasks when the system is out of memory so as to free up memory. Death by OOM killer is a form of malloc() failure :) However, other operating systems don't do this (for example, Solaris doesn't -- malloc() can and does fail on Solaris when the system is out of memory). Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] dangerous allocation?
On Thu, Jun 2, 2011 at 11:38 AM, Ronald Burgman <
r.w.burg...@student.utwente.nl> wrote:
> Hi,
>
> I've found a strange piece of code. The print_stack_union function
> allocates some memory to the types variable (line 3436) and terminates
> with an error code if this allocation fails (line 3450). But types is
> already accessed before this check. So shouldn't the check be earlier
> in the code, or am I missing something?
>
http://www.sqlite.org/src/ci/efb20b9da6
Note, however, that lemon.c is not a deliverable component of SQLite, but
rather a code generator program that generates some of the C code for
SQLite, and lemon always runs on a workstation, and so it is essentially
impossible for the calloc() to fail since modern workstations do not fail
mallocs. So this is not a real bug. But it is worth patching, all the
same. Thanks.
>
> Cheers,
> Ronald
>
> 3436: types = (char**)calloc( arraysize, sizeof(char*) );
>for(i=0; imaxdtlength = 0;
>if( lemp->vartype ){
>maxdtlength = lemonStrlen(lemp->vartype);
>}
>for(i=0; insymbol; i++){
>int len;
>struct symbol *sp = lemp->symbols[i];
>if( sp->datatype==0 ) continue;
>len = lemonStrlen(sp->datatype);
>if( len>maxdtlength ) maxdtlength = len;
>}
>stddt = (char*)malloc( maxdtlength*2 + 1 );
> 3450: if( types==0 || stddt==0 ){
>fprintf(stderr,"Out of memory.\n");
>exit(1);
>}
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] HELP : how to use datetime('column', 'localtime') as a part of sql string
I am on Windows 7 64 bit; I have tested the program as you mentioned, the good point here was the issue still appears please find logs . Let me know if you infer anything from below. C:\development\SignalCoreClient\test\com\Rimage\Starfish\AutomatedTests>java TestStatusDate Today's Locale is en_IN timezone ==>sun.util.calendar.ZoneInfo[id="Asia/Calcutta",offset=1980,dstSavings=0,useDaylight=false,transitions=6,lastRule=null] Time taken to execute query ==>3623 762,automatedTest_13067437059065180049472173,2011-06-02 13:24:53,COMPLETE,md,2011-06-02 13:26:14, 761,automatedTest_13067437059065180049472173,2011-06-02 13:24:52,COMPLETE,md,2011-06-02 13:26:07, 760,automatedTest_13067437059065180049472173,2011-06-02 13:24:52,COMPLETE,md,2011-06-02 13:25:59, 759,automatedTest_13067437059065180049472173,2011-06-02 13:24:51,COMPLETE,md,2011-06-02 13:25:41, 758,automatedTest_13067437059065180049472173,2011-06-02 13:24:50,COMPLETE,md,2011-06-02 13:25:36, 757,automatedTest_13067437059065180049472173,2011-06-02 13:24:50,COMPLETE,md,2011-06-02 13:25:30, 756,automatedTest_13067437059065180049472173,2011-06-02 13:24:49,COMPLETE,md,2011-06-02 13:25:23, 755,automatedTest_130675855965820033590511079,2011-06-02 13:16:09,COMPLETE,md,2011-06-02 13:17:30, 754,automatedTest_130675855965820033590511079,2011-06-02 13:16:08,COMPLETE,md,2011-06-02 13:17:25, 753,automatedTest_130675855965820033590511079,2011-06-02 13:16:07,COMPLETE,md,2011-06-02 13:17:12, 752,automatedTest_130675855965820033590511079,2011-06-02 13:16:07,COMPLETE,md,2011-06-02 13:16:51, 751,automatedTest_130675855965820033590511079,2011-06-02 13:16:06,COMPLETE,md,2011-06-02 13:16:48, 750,automatedTest_130675855965820033590511079,2011-06-02 13:16:05,COMPLETE,md,2011-06-02 13:19:17, 749,automatedTest_1307018964144181250169479584,2011-06-02 12:49:30,COMPLETE,hi,2011-06-02 12:57:58, 748,NaveenTest,2011-06-02 12:49:29,COMPLETE,hi,2011-06-02 12:56:59, 747,automatedTest_1307018192020180478065994419,2011-06-02 12:36:39,COMPLETE,hi,2011-06-02 12:56:30, 746,NaveenTest,2011-06-02 12:36:37,COMPLETE,hi,2011-06-02 12:55:33, 745,automatedTest_130675855965720033590323324,2011-06-02 11:52:21,COMPLETE,md,2011-06-02 11:52:54, 744,automatedTest_1306482594629320685646725968,null,COMPLETE,md,2011-06-02 10:18:47, 743,automatedTest_130675534731016821286104758,2011-06-02 15:15:25,INIT,md,null, 742,automatedTest_130675534731016821286104758,2011-06-02 15:08:15,INIT,md,null, 741,automatedTest_130675534731016821286104758,2011-06-02 09:34:06,COMPLETE,md,2011-06-02 09:34:46, 740,automatedTest_130675855965820033590602240,null,COMPLETE,md,2011-06-02 09:33:59, 739,automatedTest_130675855965820033590602240,2011-06-02 14:56:11,INIT,md,null, 738,automatedTest_130675855965820033590602240,2011-06-02 09:25:39,INIT,md,null, 737,NaveenTest,2011-06-02 13:54:34,INIT,md,null, 736,NaveenTest,2011-06-02 08:24:02,COMPLETE,md,2011-06-02 08:25:51, 735,SridharTest,2011-06-02 13:23:24,INIT,md,null, 734,SridharTest,2011-06-02 07:51:44,COMPLETE,md,2011-06-02 07:56:07, 733,automatedTest_1306481519407319610451279968,2011-06-02 13:11:25,INIT,md,null, 732,automatedTest_1306481519407319610451279968,2011-06-02 07:40:45,COMPLETE,md,2011-06-02 07:45:47, 731,automatedTest_130675534731016821286048311,2011-06-02 07:25:25,COMPLETE,md,2011-06-02 07:28:14, 730,automatedTest_130675534731016821286048311,2011-06-02 12:54:59,INIT,md,null, 729,automatedTest_13067429149993217266307076,2011-06-02 07:12:25,COMPLETE,md,2011-06-02 07:16:24, 728,SridharTest,2011-06-02 07:01:59,COMPLETE,md,2011-06-02 07:02:25, 727,automatedTest_1306496245310334336328084528,2011-06-02 06:54:48,COMPLETE,md,2011-06-02 06:55:29, 726,automatedTest_130648151940731961056560,2011-06-01 07:00:33,COMPLETE,md,2011-06-01 07:01:04, 725,automatedTest_130651279595727994816196128,2011-05-31 13:56:47,COMPLETE,md,2011-05-31 13:57:44, 724,automatedTest_130651279595727994816196128,2011-05-31 13:56:47,COMPLETE,md,2011-05-31 13:57:24, 723,automatedTest_130651279595727994816196128,2011-05-31 13:56:46,COMPLETE,md,2011-05-31 13:57:20, 722,automatedTest_130651279595727994816196128,2011-06-02 12:22:02,COMPLETE,md,2011-05-31 13:57:15, C:\development\SignalCoreClient\test\com\Rimage\Starfish\AutomatedTests>cd \Users\sridhar\Desktop C:\Users\sridhar\Desktop>sqlite3.exe C:\Signal\Publisher\publisher.db SQLite version 3.7.6.3 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> SELECT j.jobId, c.channelName , datetime(j.jobCreateDate, 'localtime'), j.jobStatus, j.jobQuality, j.jobCompleteDate FROM Job j, Channel c where c elId = j.jobChannelId ORDER BY jobId DESC; 762|automatedTest_13067437059065180049472173|2011-06-02 18:54:53|COMPLETE|md|2011-06-02 13:26:14 761|automatedTest_13067437059065180049472173|2011-06-02 18:54:52|COMPLETE|md|2011-06-02 13:26:07 760|automatedTest_13067437059065180049472173|2011-06-02 18:54:52|COMPLETE|md|2011-06-02 13:25:59 759|automatedTest_13067437059065180049472173|2011-06-02 18:54:51|C
[sqlite] dangerous allocation?
Hi,
I've found a strange piece of code. The print_stack_union function
allocates some memory to the types variable (line 3436) and terminates
with an error code if this allocation fails (line 3450). But types is
already accessed before this check. So shouldn't the check be earlier
in the code, or am I missing something?
Cheers,
Ronald
3436: types = (char**)calloc( arraysize, sizeof(char*) );
for(i=0; ivartype ){
maxdtlength = lemonStrlen(lemp->vartype);
}
for(i=0; insymbol; i++){
int len;
struct symbol *sp = lemp->symbols[i];
if( sp->datatype==0 ) continue;
len = lemonStrlen(sp->datatype);
if( len>maxdtlength ) maxdtlength = len;
}
stddt = (char*)malloc( maxdtlength*2 + 1 );
3450: if( types==0 || stddt==0 ){
fprintf(stderr,"Out of memory.\n");
exit(1);
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] HELP : how to use datetime('column', 'localtime') as a part of sql string
> I have verified my locale and default time zone in the same program.The > values are correct and it is not UTC timezone. Any help appreciated. How did you do that? What OS are you running it on? Can you extract the code accessing SQLite database from your program and create a mini-program that does nothing else except executes this query? Run this mini-program, see what it returns. Then from the same shell prompt run sqlite3 command line utility and execute this query there, see if results are different. If they are show us full text of java mini-program, we will try to find the problem. Pavel On Thu, Jun 2, 2011 at 10:57 AM, Sridhar Polavarapu wrote: > I have verified my locale and default time zone in the same program.The > values are correct and it is not UTC timezone. Any help appreciated. > > Sridhar > > On 02-06-2011 19:34, Sridhar Polavarapu wrote: >> >> The returned value is displayed to the user. We are not storing back the >> returned column. >> >> Sridhar >> >> On 02-06-2011 19:28, Pavel Ivanov wrote: but is returning the value stored in the database when executed as a part of a java program. Can anyone help me if I am missing something here ? >>> >>> Looks like your java program runs with a different locale than SQLite >>> Manager. I'm not sure why it thinks that you are in a UTC timezone. >>> Maybe there's something in starting scripts or in initialization code >>> of this program? >>> >>> >>> Pavel >>> >>> >>> On Thu, Jun 2, 2011 at 7:32 AM, Sridhar Polavarapu >>> wrote: Hi I am preparing a string sql query as follows String jobQuery = "SELECT j.jobId, c.channelName , datetime(j.jobCreateDate,'localtime') as jobCreateDate, j.jobStatus, CASE WHEN j.jobQuality='md' THEN 'Medium' WHEN j.jobQuality='hi' THEN 'High' WHEN j.jobQuality='lo' THEN 'Low' ELSE 'Unspecified' END AS jobQuality, j.jobCompleteDate "; jobQuery += "FROM Job j "; jobQuery += "JOIN Channel c ON c.channelId = j.jobChannelId "; this query gives me jobCreateDate in my localtime when run in sqlite manager, but is returning the value stored in the database when executed as a part of a java program. Can anyone help me if I am missing something here ? Sridhar ___ 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] HELP : how to use datetime('column', 'localtime') as a part of sql string
I have verified my locale and default time zone in the same program.The values are correct and it is not UTC timezone. Any help appreciated. Sridhar On 02-06-2011 19:34, Sridhar Polavarapu wrote: > The returned value is displayed to the user. We are not storing back > the returned column. > > Sridhar > > On 02-06-2011 19:28, Pavel Ivanov wrote: >>> but is returning the value stored in the database when executed >>> as a part of a java program. Can anyone help me if I am missing >>> something here ? >> Looks like your java program runs with a different locale than SQLite >> Manager. I'm not sure why it thinks that you are in a UTC timezone. >> Maybe there's something in starting scripts or in initialization code >> of this program? >> >> >> Pavel >> >> >> On Thu, Jun 2, 2011 at 7:32 AM, Sridhar Polavarapu >> wrote: >>> Hi >>> >>> I am preparing a string sql query as follows >>> >>> String jobQuery = "SELECT j.jobId, c.channelName , >>> datetime(j.jobCreateDate,'localtime') as jobCreateDate, j.jobStatus, >>> CASE WHEN j.jobQuality='md' THEN 'Medium' WHEN j.jobQuality='hi' THEN >>> 'High' WHEN j.jobQuality='lo' THEN 'Low' ELSE 'Unspecified' END AS >>>jobQuality, j.jobCompleteDate "; >>> jobQuery += "FROM Job j "; >>> jobQuery += "JOIN Channel c ON c.channelId = j.jobChannelId "; >>> >>> this query gives me jobCreateDate in my localtime when run in sqlite >>> manager, but is returning the value stored in the database when >>> executed >>> as a part of a java program. Can anyone help me if I am missing >>> something here ? >>> >>> Sridhar >>> ___ >>> 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] Create DB file and then Create Table - Table FAILS.
This line seems erroneous: SQLiteCommand sqlCmd(%conn); Isn't the percent the modulus operator? Shouldn't it be: SQLiteCommand sqlCmd(&conn); On 6/1/2011 5:06 PM, Don Ireland wrote: > I'm hoping someone can help me with this. > > Using Visual Studio C++, the following code DOES create the DB file. > But the table doesn't get created and I'm stumped as to why it won't > create the table. > > SQLiteConnection conn; > conn.ConnectionString = "Data > Source=D:\Users\Don\Downloads\CashBoxPrefs.dat"; > conn.Open(); > SQLiteCommand sqlCmd(%conn); > sqlCmd.CommandText = "CREATE TABLE IF NOT EXISTS Prefs > (SyncEnabled bool,SyncWatchPort int,SyncLoginKey TEXT,SyncAccessKey > TEXT, SyncLogFile TEXT, SyncLogClearInterval int,GenLatestBook TEXT, > GenBookMRU_0 TEXT,GenBookMRU_1 TEXT, GenBookMRU_2 TEXT, GenBookMRU_3 > TEXT, GenBookMRU_4 TEXT);"; > > > But if I run following at the SQLite3.exe from the command line, it DOES > create the file and the table. > sqlite3 Cashboxprefs.dat > sqlite> CREATE TABLE IF NOT EXISTS Prefs (SyncEnabled bool,SyncWatchPort > int,SyncLoginKey TEXT,SyncAccessKey TEXT, SyncLogFile TEXT, > SyncLogClearInterval int,GenLatestBook TEXT, GenBookMRU_0 > TEXT,GenBookMRU_1 TEXT, GenBookMRU_2 TEXT, GenBookMRU_3 TEXT, > GenBookMRU_4 TEXT); > ___ > 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] Support for .Net CE 3.5
Thanks for the response, Shane. When would you expect that support to be restored? On Thu, Jun 2, 2011 at 10:11 AM, Shane Harrelson wrote: > You'll have to go back to one of the "legacy versions" from > http://sqlite.phxsoftware.com . > Compact framework support was one of the features that had to be > temporarily > dropped when maintenance was moved to http://www.sqlite.org . > Restoring compact framework is currently one of our top priorities. > > > > On Wed, Jun 1, 2011 at 1:08 PM, Jeff Hoffman >wrote: > > > I am programming for Windows Mobile 6.5.3 and would like to use SQLite. > > > > I see this message on the "Features" page: > > > > Supports the Full and Compact .NET Framework, and native C/C++ > development. > > 100% binary compatible with the original sqlite3.dll. > > Compact framework not currently not included. We hope to have this > feature > > included again soon. > > > > > > > > Can anyone tell me the latest version of System.Data.SQLite that supports > > .Net CE 3.5? Any other advice would be welcome too! > > > > Thanks, > > > > Jeff > > ___ > > 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] Support for .Net CE 3.5
You'll have to go back to one of the "legacy versions" from http://sqlite.phxsoftware.com . Compact framework support was one of the features that had to be temporarily dropped when maintenance was moved to http://www.sqlite.org . Restoring compact framework is currently one of our top priorities. On Wed, Jun 1, 2011 at 1:08 PM, Jeff Hoffman wrote: > I am programming for Windows Mobile 6.5.3 and would like to use SQLite. > > I see this message on the "Features" page: > > Supports the Full and Compact .NET Framework, and native C/C++ development. > 100% binary compatible with the original sqlite3.dll. > Compact framework not currently not included. We hope to have this feature > included again soon. > > > > Can anyone tell me the latest version of System.Data.SQLite that supports > .Net CE 3.5? Any other advice would be welcome too! > > Thanks, > > Jeff > ___ > 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] HELP : how to use datetime('column', 'localtime') as a part of sql string
The returned value is displayed to the user. We are not storing back the returned column. Sridhar On 02-06-2011 19:28, Pavel Ivanov wrote: >> but is returning the value stored in the database when executed >> as a part of a java program. Can anyone help me if I am missing >> something here ? > Looks like your java program runs with a different locale than SQLite > Manager. I'm not sure why it thinks that you are in a UTC timezone. > Maybe there's something in starting scripts or in initialization code > of this program? > > > Pavel > > > On Thu, Jun 2, 2011 at 7:32 AM, Sridhar Polavarapu > wrote: >> Hi >> >> I am preparing a string sql query as follows >> >> String jobQuery = "SELECT j.jobId, c.channelName , >> datetime(j.jobCreateDate,'localtime') as jobCreateDate, j.jobStatus, >> CASE WHEN j.jobQuality='md' THEN 'Medium' WHEN j.jobQuality='hi' THEN >> 'High' WHEN j.jobQuality='lo' THEN 'Low' ELSE 'Unspecified' END AS >>jobQuality, j.jobCompleteDate "; >> jobQuery += "FROM Job j "; >> jobQuery += "JOIN Channel c ON c.channelId = j.jobChannelId "; >> >> this query gives me jobCreateDate in my localtime when run in sqlite >> manager, but is returning the value stored in the database when executed >> as a part of a java program. Can anyone help me if I am missing >> something here ? >> >> Sridhar >> ___ >> 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] Bug reports
On 2 Jun 2011, at 2:40pm, Ronald Burgman wrote: > I'm currently using a tool to find bugs in the SQLite source code. I've > found some interesting things and want to submit some bug reports so the > developers can also have a look at it. Report them here. Although you may be using a tool to /find/ the bugs, the authors will completely mistrust the tool. So you must reproduce the bug yourself: show us some source code, and what SQLite does with it, and what you expected it to do instead. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] HELP : how to use datetime('column', 'localtime') as a part of sql string
> but is returning the value stored in the database when executed > as a part of a java program. Can anyone help me if I am missing > something here ? Looks like your java program runs with a different locale than SQLite Manager. I'm not sure why it thinks that you are in a UTC timezone. Maybe there's something in starting scripts or in initialization code of this program? Pavel On Thu, Jun 2, 2011 at 7:32 AM, Sridhar Polavarapu wrote: > Hi > > I am preparing a string sql query as follows > > String jobQuery = "SELECT j.jobId, c.channelName , > datetime(j.jobCreateDate,'localtime') as jobCreateDate, j.jobStatus, > CASE WHEN j.jobQuality='md' THEN 'Medium' WHEN j.jobQuality='hi' THEN > 'High' WHEN j.jobQuality='lo' THEN 'Low' ELSE 'Unspecified' END AS > jobQuality, j.jobCompleteDate "; > jobQuery += "FROM Job j "; > jobQuery += "JOIN Channel c ON c.channelId = j.jobChannelId "; > > this query gives me jobCreateDate in my localtime when run in sqlite > manager, but is returning the value stored in the database when executed > as a part of a java program. Can anyone help me if I am missing > something here ? > > Sridhar > ___ > 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] Bug reports
> I did try sending a mail over the dev-list, but got no reply. Could > someone tell me how it should be done? You should send it here with the exact steps of reproducing the bug. Hopefully your tool is not code analyzer because "potential bugs" without any real life steps to catch it won't be paid attention to. Pavel On Thu, Jun 2, 2011 at 9:40 AM, Ronald Burgman wrote: > Hi everybody, > > I'm currently using a tool to find bugs in the SQLite source code. I've > found some interesting things and want to submit some bug reports so the > developers can also have a look at it. > > However I'm new to SQLite so I'm not sure where and how to submit these > reports. I did try sending a mail over the dev-list, but got no reply. Could > someone tell me how it should be done? > > Cheers, > Ronald > ___ > 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] Bug reports
Hi everybody, I'm currently using a tool to find bugs in the SQLite source code. I've found some interesting things and want to submit some bug reports so the developers can also have a look at it. However I'm new to SQLite so I'm not sure where and how to submit these reports. I did try sending a mail over the dev-list, but got no reply. Could someone tell me how it should be done? Cheers, Ronald ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Big difference in performance between Python and gcc
On 2 Jun 2011, at 8:55am, Dominique Pellé wrote: > Roger Binns wrote: > >> While those are all valid, they don't address the underlying issue which is >> C code taking five times longer than Python code for the same SQLite >> operations. In addition that same "redundant" code is executed behind the >> scenes in Python so it is fair for these comparisons/benchmark. >> >> Other things having been ruled out, it looks like Jan's suggestion of >> compilation options and code is likely the cause. One does normally assume that C code is going to be pretty efficient. I was surprised at the OP's information. > That's true of course and I should have mentioned it. > However, for a benchmark, it's best to avoid things that > can be trivially optimized. I don't know how the python > code translates into SQLite C API. Python is a bytecode language. Current compilers (the bit that /makes/ the bytecodes, not the bit that reads them) are very good at trimming off code which leads to results which are never used. Which, as commented above, makes it extremely difficult to write benchmark programs in Python. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] HELP : how to use datetime('column', 'localtime') as a part of sql string
Hi I am preparing a string sql query as follows String jobQuery = "SELECT j.jobId, c.channelName , datetime(j.jobCreateDate,'localtime') as jobCreateDate, j.jobStatus, CASE WHEN j.jobQuality='md' THEN 'Medium' WHEN j.jobQuality='hi' THEN 'High' WHEN j.jobQuality='lo' THEN 'Low' ELSE 'Unspecified' END AS jobQuality, j.jobCompleteDate "; jobQuery += "FROM Job j "; jobQuery += "JOIN Channel c ON c.channelId = j.jobChannelId "; this query gives me jobCreateDate in my localtime when run in sqlite manager, but is returning the value stored in the database when executed as a part of a java program. Can anyone help me if I am missing something here ? Sridhar ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in sqlite3_bind_parameter_name
Patrick Earl писал(а) в своём письме Wed, 01 Jun 2011 22:24:10 +0600: > Is there another work-around? Yes. Do not feed SQLite with stupid queries. ;) If, according to SQLite standards, LIMIT shouldn't be specified in that subquery, then you shouldn't use it. Especially with bind parameters. Just optimize your query, make it more sensible - and the problem disappears. No need to change SQLite itself IMO. Remember, it's you who should write essential and good queries, not SQLite should do that for you. Regards, Serge ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Big difference in performance between Python and gcc
Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 06/01/2011 12:25 PM, Dominique Pellé wrote: >> [Various optimisations] > > While those are all valid, they don't address the underlying issue which is > C code taking five times longer than Python code for the same SQLite > operations. In addition that same "redundant" code is executed behind the > scenes in Python so it is fair for these comparisons/benchmark. > > Other things having been ruled out, it looks like Jan's suggestion of > compilation options and code is likely the cause. > > Roger That's true of course and I should have mentioned it. However, for a benchmark, it's best to avoid things that can be trivially optimized. I don't know how the python code translates into SQLite C API. -- Dominique ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
