Re: [sqlite] Client/Server Best Practices

On Wednesday, 7 January, 2015 22:57, Kevin Benson said: >-- The OP wrote: >On Thu, Dec 25, 2014 at 7:32 AM, Rick Kelly wrote: >>*SNIP* >> The database file is located in the same folder as the server. The >server >is >> multi-threaded. I'm using

Re: [sqlite] Client/Server Best Practices

On Wed, Jan 7, 2015 at 11:47 PM, Keith Medcalf wrote: > On Wednesday, 7 January, 2015 20:01, James K. Lowden < > jklow...@schemamania.org> said: > >On Fri, 02 Jan 2015 21:41:02 -0700 > >"Keith Medcalf" wrote: > >> On Friday, 2 January, 2015 16:26, James

Re: [sqlite] Client/Server Best Practices

On Wednesday, 7 January, 2015 20:01, James K. Lowden said: >On Fri, 02 Jan 2015 21:41:02 -0700 >"Keith Medcalf" wrote: >> On Friday, 2 January, 2015 16:26, James K. Lowden >> said: >> >> >On Thu, 25 Dec 2014 05:32:45

Re: [sqlite] Time Zone Conversions

The datetime function always returns GMT time unless you request it to do something else (for example, asking for a conversion to 'localtime' using the system c library concept of localtime and timezones -- which will almost always be incorrect on Windows since it has extremely limited

Re: [sqlite] Client/Server Best Practices

On Fri, 02 Jan 2015 21:41:02 -0700 "Keith Medcalf" wrote: > On Friday, 2 January, 2015 16:26, James K. Lowden > said: > > >On Thu, 25 Dec 2014 05:32:45 -0700 (MST) Rick Kelly > > wrote: > > >> All SELECT type requests are wrapped

Re: [sqlite] New column in select will not mask column of the same name in having clause and sqlite won't warn

On Mon, 5 Jan 2015 06:39:42 + Hick Gunter wrote: > This is completely legal and well defined. > > HAVING is applied to the RESULT set of a SELECT. I beg to differ. It's both invalid SQL and (therefore) undefined. Furthermore, it's illogical. Consider: create

Re: [sqlite] Index rownum

Hi Richard, Thank you for your answer, much appreciated. I guessed that insertion and deletion could be an issue. We need to re-number keys (change a lot of Btree nodes) on each operation. Or at least on REINDEX command (I say not strictly, just as idea). What could you advise me? Probably

[sqlite] STOP

Unsubsribe ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite Android Bindings: how difficult to add LOCALIZED back?

I just saw the SQLite Android Bindings page at http://www.sqlite.org/android/doc/trunk/www/index.wiki but was a little disappointed to read in the details that UNICODE and LOCALIZED are not supported. I'd really like the latest SQLite, and LOCALIZED. How difficult would it be to add

Re: [sqlite] using a hex integer as input to DateTime

On 1/7/15, Paul Sanderson wrote: > Evening all > > I dont think this can be done, but would love to be corrected. I have > a column with integer dates stored in hex format so 1234567890 is > stored as 49962d2 > > Obviously > DateTime(1234567890, 'unixepoch') > > work

Re: [sqlite] Time Zone Conversions

On 1/6/15, MikeSnow wrote: > As I look through the posts, i dont see what I am looking for. > > I am trying to create a CASE statement that converts time zones to UTC for > db storage. > For example, I have 3 columns, ReceiveDate, Timezone, UTC Datetime. > 04/11/2014

Re: [sqlite] Index rownum

On 1/7/15, Max Vasilyev wrote: > Hi guys, > Is it possible to get key number from the index? > For example let's consider Figure 4: An Index On The Fruit Column from here > https://www.sqlite.org/queryplanner.html > > index_rownum, fruit, rowid > 1 Apple 2 > 2 Grape 5 > 3

Re: [sqlite] using a hex integer as input to DateTime

On Wed, Jan 7, 2015 at 3:35 PM, Paul Sanderson wrote: > Hmm - why can't I get that to work when the hex value is stored in a > column, i.e. > > select DateTime(base, 'unixepoch') from dates > > where there is a single row and base = 0x49962d2 > Paul > The

Re: [sqlite] Time Zone Conversions

> On 7 Jan 2015, at 2:00pm, MikeSnow wrote: > > I am kind of new at this. so if I get you, I should concat the 3 columns > to get one in the suggested format. But then how do you convert? > > "Column_Time" > 2013-10-07 04:23:19.120-04:00 > >

[sqlite] Index rownum

Hi guys, Is it possible to get key number from the index? For example let's consider Figure 4: An Index On The Fruit Column from here https://www.sqlite.org/queryplanner.html index_rownum, fruit, rowid 1 Apple 2 2 Grape 5 3 Lemon 18 4 Orange 1 5 Orange 23 ... Is it possible to add rownum column

Re: [sqlite] using a hex integer as input to DateTime

> On Jan 7, 2015, at 11:35 PM, Paul Sanderson > wrote: > > Hmm - why can't I get that to work when the hex value is stored in a > column, i.e. Most likely your data is stored as text, e.g.: with DataSet as ( select '0x49962d2' as value union all select

Re: [sqlite] using a hex integer as input to DateTime

Hmm - why can't I get that to work when the hex value is stored in a column, i.e. select DateTime(base, 'unixepoch') from dates where there is a single row and base = 0x49962d2 Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786

Re: [sqlite] using a hex integer as input to DateTime

> On Jan 7, 2015, at 11:08 PM, Paul Sanderson > wrote: > > Is this possible? With a contemporary version of SQLite, yes: sqlite> select DateTime(77161170, 'unixepoch'); 1972-06-12 01:39:30 sqlite> select DateTime(0x49962d2, 'unixepoch'); 1972-06-12 01:39:30

[sqlite] Scrolling Cursor implementation best practices (pagination and arbitrary sorting)

Hi gents, I just want to show DataGrid with my table to a user with support of sorting and filtering by arbitrary column. As my table has ~200k records I'd like to implement data virtualization (paged access). Please, give me recommendations how to do pagination in the best way. The following is

[sqlite] using a hex integer as input to DateTime

Evening all I dont think this can be done, but would love to be corrected. I have a column with integer dates stored in hex format so 1234567890 is stored as 49962d2 Obviously DateTime(1234567890, 'unixepoch') work OK, but what I would like to do is something like DateTime(0x49962d2,

Re: [sqlite] Time Zone Conversions

I am kind of new at this. so if I get you, I should concat the 3 columns to get one in the suggested format. But then how do you convert? "Column_Time" 2013-10-07 04:23:19.120-04:00 datetime("Column_Time", 'utc')? -- View this message in context:

Re: [sqlite] Time Zone Conversions

On 1/6/2015 1:13 PM, MikeSnow wrote: Something like if Update t1. SET "UTC Datetime"=if "TimeZone"='EST', then "ReceiveDate"+5 update t1 SET "UTC Datetime" = datetime(ReceiveDate, (case TimeZone when 'EST' then '+5' when 'PST' then '+8' -- add more clauses to taste else '+0'

Re: [sqlite] Sqlite problem possible bug

On 2015/01/07 12:13, The Responsa Project wrote: To Whom it amy concern I am trying to use SQLITE and the like statement with wildcards and hebrew when I put in an english string it works correctly, such as Select * from dbname where colname like '%123%' I will get all the entries from

Re: [sqlite] Documentation bug: it's ESCAPE not EXCEPT that limit LIKE optimisation

On 1/7/15, Dima Tisnek wrote: > Documentation states: > > The LIKE optimization is not attempted if there is an EXCEPT phrase on > the LIKE operator. > > However you can't really slap EXCEPT (a kind of set difference join) > on top of LIKE. > Thanks for bring that to my

Re: [sqlite] Sqlite problem possible bug

On 1/7/15, The Responsa Project wrote: > To Whom it amy concern > > > I am trying to use SQLITE and the like statement with wildcards and hebrew > > when I put in an english string it works correctly, such as > > Select * from dbname where colname like '%123%' > > I will

Re: [sqlite] Sqlite problem possible bug

How about to use dynamic binding? For example, is your SQL(SELECT * from dbname where colname like '%אב%'), use '?' instead of 'אב'. In my guess, 'אב' can have same ASCII code of wildcard(%). Full SQL can be as like as follows. SELECT * from dbname where colname like '%?%' To do this, you need

Re: [sqlite] Time Zone Conversions

On 6 Jan 2015, at 6:13pm, MikeSnow wrote: > I am trying to create a CASE statement that converts time zones to UTC for > db storage. > For example, I have 3 columns, ReceiveDate, Timezone, UTC Datetime. > 04/11/2014 2:00:00, EST, > > I would like to update UTC

[sqlite] Documentation bug: it's ESCAPE not EXCEPT that limit LIKE optimisation

Documentation states: The LIKE optimization is not attempted if there is an EXCEPT phrase on the LIKE operator. However you can't really slap EXCEPT (a kind of set difference join) on top of LIKE. Instead, what is meant is that ESCAPE modifier disables LIKE optimisation. And that's really the

Re: [sqlite] Howto pivot in SQLite

Follow below steps :Suppose My Table create table data ( ...> id int, ...> countryid int, ...> state varchar(30) ...> );Insert Query sqlite> INSERT INTO data VALUES(56,9,'true');sqlite> INSERT INTO data VALUES(56,54,'true');sqlite> INSERT INTO data

[sqlite] Sqlite problem possible bug

To Whom it amy concern I am trying to use SQLITE and the like statement with wildcards and hebrew when I put in an english string it works correctly, such as Select * from dbname where colname like '%123%' I will get all the entries from that column that contain 123 anywhere in the column.

[sqlite] Time Zone Conversions

As I look through the posts, i dont see what I am looking for. I am trying to create a CASE statement that converts time zones to UTC for db storage. For example, I have 3 columns, ReceiveDate, Timezone, UTC Datetime. 04/11/2014 2:00:00, EST, I would like to update UTC Datetime with logic

Re: [sqlite] sqlite3_exec and returned error

Thank you for the suggestion. But I asked if this by design or it is a bug in sqlite and/or documentation. What do you think? And is it correct mailing list to ask or I should wrote to sqlite-devel? Valery > > From: Roger Binns >To: