Re: [sqlite] MIN() for a timedelta?

2012-07-26 Thread Keith Medcalf 

You could also store the timedelta as a float seconds as well.  Then you 
wouldn't have to worry about inconsistent string formatting ...

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of C M
> Sent: Thursday, 26 July, 2012 15:33
> To: General Discussion of SQLite Database
> Subject: [sqlite] MIN() for a timedelta?
> 
> I have string representations of a Python timedelta stored in an
> SQLite database of the form H:MM:SS:ss (the last is microseconds).
>  Here are a possible examples of such timedeltas:
> 
> '0:00:06.229000'
> '9:00:00.00'
> '10:01:23:041000'
> 
> I want to select the shortest duration (the smallest timedelta) using
> the SQLite MIN(), like so:
> 
> SELECT MIN(duration) FROM Durations
> 
> The problem is, in Python, the string representation of the timedelta
> is not left zero padded, so '9:00:00.00' (nine hours) is selected
> by MIN() as greater than '10:01:23:041000' (ten hours and change).
> This is not right in terms of time, as 9 hours is smaller than 10
> hours.
> 
> I could zero pad these strings myself, so that '9:00:00.00'
> becomes '09:00:00.00', but that would break other uses of these
> values in my code and was wondering if there were a way in SQlite to
> "see" these values as timedeltas.  I tried this:
> 
> SELECT MIN(TIME(duration) FROM Durations
> 
> but that returns nothing.
> 
> Thanks,
> Che
> ___
> 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] MIN() for a timedelta?

2012-07-26 Thread Keith Medcalf 

Select min(substr('0' || duration, -15)) from durations;


---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of C M
> Sent: Thursday, 26 July, 2012 15:33
> To: General Discussion of SQLite Database
> Subject: [sqlite] MIN() for a timedelta?
> 
> I have string representations of a Python timedelta stored in an
> SQLite database of the form H:MM:SS:ss (the last is microseconds).
>  Here are a possible examples of such timedeltas:
> 
> '0:00:06.229000'
> '9:00:00.00'
> '10:01:23:041000'
> 
> I want to select the shortest duration (the smallest timedelta) using
> the SQLite MIN(), like so:
> 
> SELECT MIN(duration) FROM Durations
> 
> The problem is, in Python, the string representation of the timedelta
> is not left zero padded, so '9:00:00.00' (nine hours) is selected
> by MIN() as greater than '10:01:23:041000' (ten hours and change).
> This is not right in terms of time, as 9 hours is smaller than 10
> hours.
> 
> I could zero pad these strings myself, so that '9:00:00.00'
> becomes '09:00:00.00', but that would break other uses of these
> values in my code and was wondering if there were a way in SQlite to
> "see" these values as timedeltas.  I tried this:
> 
> SELECT MIN(TIME(duration) FROM Durations
> 
> but that returns nothing.
> 
> Thanks,
> Che
> ___
> 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] MIN() for a timedelta?

2012-07-26 Thread Igor Tandetnik 

On 7/26/2012 5:32 PM, C M wrote:

I have string representations of a Python timedelta stored in an
SQLite database of the form H:MM:SS:ss (the last is microseconds).
  Here are a possible examples of such timedeltas:

'0:00:06.229000'
'9:00:00.00'
'10:01:23:041000'

I want to select the shortest duration (the smallest timedelta) using
the SQLite MIN(), like so:

SELECT MIN(duration) FROM Durations


Something like this perhaps:

select min(substr('0', 1, 15-length(duration)) || duration) from Durations;

--
Igor Tandetnik

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

Re: [sqlite] MIN() for a timedelta?

2012-07-26 Thread Simon Slavin 

On 27 Jul 2012, at 12:04am, C M  wrote:

> On Thu, Jul 26, 2012 at 6:45 PM, Nico Williams  wrote:
>> 
>> 
>> Just use CASE to add the missing zero as necessary, something like this:
>> 
>> SELECT strftime('%s', (SELECT CASE WHEN '9:12:32' LIKE '0%' THEN
>> '9:12:32' ELSE '0' || '9:12:32' END));
>> 
>> Replace '9:12:32' there with whatever expression, probably a column name.
> 
> Thanks but I can't figure out how to use that to get the MIN()
> timedelta.  For example, if I try this (the table is called
> Durations...the column is duration):
> 
> SELECT  MIN(CASE WHEN duration LIKE '0%' THEN
> duration ELSE '0' || duration END) FROM Durations WHERE duration != ''
> 
> it returns:
> 01:00:00:00
> 
> which is definitely not the min timedelta in the table.

So explore it.  Try variations until you can figure out what's going on.  For 
example, execute

SELECT  MIN(duration) FROM Durations WHERE duration LIKE '0%'

and

SELECT  MIN(duration) FROM Durations WHERE duration NOT LIKE '0%'

and see what you get.

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

Re: [sqlite] MIN() for a timedelta?

2012-07-26 Thread C M 
On Thu, Jul 26, 2012 at 6:45 PM, Nico Williams  wrote:
> On Thu, Jul 26, 2012 at 4:32 PM, C M  wrote:
>> I could zero pad these strings myself, so that '9:00:00.00'
>> becomes '09:00:00.00', but that would break other uses of these
>> values in my code and was wondering if there were a way in SQlite to
>> "see" these values as timedeltas.  I tried this:
>>
>> SELECT MIN(TIME(duration) FROM Durations
>>
>> but that returns nothing.
>
> Just use CASE to add the missing zero as necessary, something like this:
>
> SELECT strftime('%s', (SELECT CASE WHEN '9:12:32' LIKE '0%' THEN
> '9:12:32' ELSE '0' || '9:12:32' END));
>
> Replace '9:12:32' there with whatever expression, probably a column name.

Thanks but I can't figure out how to use that to get the MIN()
timedelta.  For example, if I try this (the table is called
Durations...the column is duration):

SELECT  MIN(CASE WHEN duration LIKE '0%' THEN
duration ELSE '0' || duration END) FROM Durations WHERE duration != ''

it returns:
01:00:00:00

which is definitely not the min timedelta in the table.

If I try:

SELECT  (SELECT strftime('%s', (SELECT CASE WHEN duration LIKE '0%' THEN
duration ELSE '0' || duration END))) FROM Durations

It returns a large number of results, including negative values like -413362495.

I'm really lost on how to combine the above into a sensible query to
get the minimum timedelta.

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

Re: [sqlite] C++ - Evaluating a statement

2012-07-26 Thread Nico Williams 
On Thu, Jul 26, 2012 at 4:32 PM, Arbol One  wrote:
> Reading the documentation, it says that 'sqlite3_step(sqlite3_stmt*)
>
> ' must be called at least once to evaluate the statement. However, what does
> it evaluate?

The prepared statement.

Prepared statements are really a byte-compiled form of your statement.
 sqlite3_step() runs the virtual machine with the given prepared
statement until one row is output or the prepared statement
terminates.  You should call sqlite3_step() in a loop in general, to
ensure that you consume all output rows (if you care about them).
CREATE, ALTER, DROP, INSERT, UPDATE, and DELETE statements run to
completion in one call to sqlite3_step(), and so do many (but not all)
PRAGMAs.

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

Re: [sqlite] MIN() for a timedelta?

2012-07-26 Thread Nico Williams 
On Thu, Jul 26, 2012 at 4:32 PM, C M  wrote:
> I could zero pad these strings myself, so that '9:00:00.00'
> becomes '09:00:00.00', but that would break other uses of these
> values in my code and was wondering if there were a way in SQlite to
> "see" these values as timedeltas.  I tried this:
>
> SELECT MIN(TIME(duration) FROM Durations
>
> but that returns nothing.

Just use CASE to add the missing zero as necessary, something like this:

SELECT strftime('%s', (SELECT CASE WHEN '9:12:32' LIKE '0%' THEN
'9:12:32' ELSE '0' || '9:12:32' END));

Replace '9:12:32' there with whatever expression, probably a column name.

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

[sqlite] MIN() for a timedelta?

2012-07-26 Thread C M 
I have string representations of a Python timedelta stored in an
SQLite database of the form H:MM:SS:ss (the last is microseconds).
 Here are a possible examples of such timedeltas:

'0:00:06.229000'
'9:00:00.00'
'10:01:23:041000'

I want to select the shortest duration (the smallest timedelta) using
the SQLite MIN(), like so:

SELECT MIN(duration) FROM Durations

The problem is, in Python, the string representation of the timedelta
is not left zero padded, so '9:00:00.00' (nine hours) is selected
by MIN() as greater than '10:01:23:041000' (ten hours and change).
This is not right in terms of time, as 9 hours is smaller than 10
hours.

I could zero pad these strings myself, so that '9:00:00.00'
becomes '09:00:00.00', but that would break other uses of these
values in my code and was wondering if there were a way in SQlite to
"see" these values as timedeltas.  I tried this:

SELECT MIN(TIME(duration) FROM Durations

but that returns nothing.

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

[sqlite] C++ - Evaluating a statement

2012-07-26 Thread Arbol One 
Reading the documentation, it says that 'sqlite3_step(sqlite3_stmt*)

' must be called at least once to evaluate the statement. However, what does
it evaluate?

 

 

Freedom of speech does not translate to freedom of insulting

 

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

Re: [sqlite] svn: SQLite compiled for 3.6.20, but running with 3.6.4

2012-07-26 Thread Stephan Beal 
On Thu, Jul 26, 2012 at 6:38 PM, Tilsley, Jerry M.
wrote:

> Anyone know how I can get rid of the 3.6.20 compilation so that the system
> will quit complaining?  This is on a RedHat 6.2 system.
>

You can use ldd to find where the extra lib is, and then simply mv/rename
it or update your LD_LIBRARY_PATH so that the other lib comes first the
search path.

ldd binaryFileName

e.g.:

 [stephan@host:~/cvs/fossil/fossil]$ ldd fossil
linux-vdso.so.1 =>  (0x77dff000)
libz.so.1 => /lib/x86_64-linux-gnu/libz.so.1 (0x7f07ca15e000)
libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x7f07c9da1000)
/lib64/ld-linux-x86-64.so.2 (0x7f07ca396000)

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] svn: SQLite compiled for 3.6.20, but running with 3.6.4

2012-07-26 Thread Tilsley, Jerry M. 
All,

Anyone know how I can get rid of the 3.6.20 compilation so that the system will 
quit complaining?  This is on a RedHat 6.2 system.

Thanks,

Jerry Tilsley
Sr Systems Analyst
St. Claire Regional Medical Center




Disclaimer
This email is confidential and intended solely for the use of the individual to 
whom it is addressed. Any views or opinions presented are solely those of the 
author and do not necessarily represent those of St. Claire Regional Medical 
Center. If you are not the intended recipient, be advised that you have 
received this email in error and that any use, dissemination, forwarding, 
printing or copying of the email is strictly prohibited. If you received this 
email in error please notify the St. Claire Regional Helpdesk by telephone at 
606-783-6565.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Testing the ODBC link

2012-07-26 Thread Maury Markowitz 
Just following up:

The problem getting the "catalog name" was a minor one - the ODBC link for
SQLite only supported early versions of the ODBC call for doing this. When I
reverted to an earlier-model API call, poof, it worked. And it kept working
for all the other DB's I tried too.

So we're uploading shortly.



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Testing-the-ODBC-link-tp62605p63537.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Compiling System.Data.SQLite with latest sqlite3.dll

2012-07-26 Thread Bernhard Mogens Ege 
Ah, so it does! I obviously misread your previous suggestion that was spot
on.

And now it certainly works, memory db with shared cache in
System.Data.SQLite. :)

Thank you for your help. :)

-- 
Bernhard

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joe Mistachkin
Sent: 25. juli 2012 22:04
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Compiling System.Data.SQLite with latest sqlite3.dll


Please use the following and try again:

con.ProviderConnectionString = "FullUri=file::memory:?cache=shared;";

The "FullUri" property supersedes the "Data Source" property.

--
Joe Mistachkin

___
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] Handling concurrent read request to SQLite

2012-07-26 Thread Simon Slavin 

On 26 Jul 2012, at 6:29am, "Navaneeth.K.N"  wrote:

> Recently, I implemented a web version of my program which internally uses
> the shared library. There will be REST URLs exposed for the "learn" API
> call, something like "http://websitename.com/learn; with the word to learn
> in the request parameters.

Make sure you have encoded the word somehow.  There may be problems with 
'words' which include punctuation or accented characters since they can't be 
passed as URLs.

> Since the web-server allows concurrent requests,
> there could be a possibility that two requests for learn getting executed
> in parallel. In this case, SQLite fails with error message "Database is
> locked" as there would be one writer already in progress.

Have you set a timeout ?  If you haven't the SQLite functions never back off 
and retry when they find the database locked, they just immediately return an 
error.



> Currently, I have implemented a queue at the server side which will queue
> all the requests for learn. Another worker process reads this queue and
> call my library routine for each word sequentially. This works well. But I
> am wondering is this the right way to workaround this problem?

That may be a good way to do it, especially since you've already written it.

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