Re: [sqlite] Why we don't have strpos function?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Jay A. Kreibich wrote:
>   I think you're actual question is if zero-characters are prohibited,
>   and I think the answer is no.

SQLite quite happily works with strings with embedded nulls - it is also
part of my test suite.  SQLite has a count of the number of bytes which
is why there is no problem.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkuhv9IACgkQmOOfHg372QRBbQCfQq6U4VWEbRlNb3jBz8sf5fyT
KDQAoNp3W5w2JA37v6JnI7+gYf2ZIInH
=Q4+l
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Result_* functions

[Dan Kennedy]

On Mar 18, 2010, at 6:40 AM, Jean-Christophe Deschamps wrote:

> Is it allowable/safe to invoke more than once any sqlite3_result_*()
> function?  In other terms, subsequent invokation of any result  
> function
> will it harmlessly override a previous one?

Yes and yes.

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

Re: [sqlite] one column is another table's row num

[Igor Tandetnik]

liubin liu wrote:
> I want to create two tables like:
> create table t1 (id INT PRIMARY KEY, cont TEXT);
> create table t2 ({select count(*) from t1} INT);
> 
> How could I do it?

You create table t2 with plain vanilla integer field, then create insert and 
delete triggers on t1 that update t2.
-- 
Igor Tandetnik

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

[sqlite] one column is another table's row num

[liubin liu]

I want to create two tables like:
create table t1 (id INT PRIMARY KEY, cont TEXT);
create table t2 ({select count(*) from t1} INT);

How could I do it?
-- 
View this message in context: 
http://old.nabble.com/one-column-is-another-table%27s-row-num-tp27940860p27940860.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

[sqlite] Result_* functions

[Jean-Christophe Deschamps]

Is it allowable/safe to invoke more than once any sqlite3_result_*() 
function?  In other terms, subsequent invokation of any result function 
will it harmlessly override a previous one?

As in:

init...
// once for all, post null return in anticipation for the various cases 
where
// parameters or arguments would fail to make sense
sqlite3_result_null();

if (dumb case #1) return;
if (dumb case #2) return;
if (dumb case #3) return;
...
if (dumb case #N) return;

// perform requested action on known-valid input

// override null result by some valid result
sqlite3_result_int(...);




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

Re: [sqlite] database optimization issues.

[Felipe Aramburu]

Thanks I actually resolved this. I wasn't using a UNION operator


On Wed, Mar 17, 2010 at 5:50 PM, Igor Tandetnik  wrote:

> Felipe Aramburu wrote:
> >   stmtGetQuestionsNewSystemType.text = 'SELECT
> DISTINCT
> > Question.id
> > AS id, Question.name AS name, Question.label AS label,
> > Question.datatypeid AS datatypeid, Question.advanced AS advanced,
> > Question.multivalue AS multivalue, Question.measurementtypeid AS
> > measurementtypeid, Question.hotlist AS hotlist FROM main.Question,
> > main.CategoryQuestions, main.ApplicationQuestions,
> > main.TechnologyQuestions WHERE (Question.id =
> > CategoryQuestions.questionid AND CategoryQuestions.categoryid =
> > @categoryid ) OR (Question.id = ApplicationQuestions.questionid AND
> > ApplicationQuestions.applicationid = @applicationid ) OR (Question.id
> > = TechnologyQuestions.questionid AND TechnologyQuestions.technologyid
> > = @technologyid ) ;';
>
> Try this:
>
> select /* what you have now, without DISTINCT */
> from Question where id in (
>select questionid from CategoryQuestions where categoryid = @categoryid
>union all
>select questionid from ApplicationQuestions where applicationid =
> @applicationid
>union all
>select questionid from TechnologyQuestions where technologyid=
> @technologyid
> );
>
> --
> 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] Problem with between clause and dates

[Igor Tandetnik]

Mike Martin wrote:
>>> select tsid from recordings where '2010-03-16 23:05:00' between start and 
>>> end'
> 
> It should return nothing because the time 2010-03-16 23:06:00 is later
> than the end time of 2010-03-16 23:05:00

I don't see 23:06 anywhere in your statement nor in your data.
-- 
Igor Tandetnik

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

[sqlite] Fwd: database optimization issues.

[Felipe Aramburu]

http://www.filesavr.com/dbexample  
Here is an example of some tables which I am having very poor
performance querying. I was wondering if someone could give me some
suggestions as to how I can optimize this.

I am using the adobe Air framework (AS3) to access the database.

I make the connection to the file like this:
   sqlConnection = new SQLConnection();
   sqlConnection.openAsync(dbfile);



In my constructor I setup a parametrized sql statement that I will use
to be running the query:




   stmtGetQuestionsNewSystemType = new SQLStatement();
   stmtGetQuestionsNewSystemType.sqlConnection = sqlConnection;

   stmtGetQuestionsNewSystemType.text = 'SELECT DISTINCT
Question.id
AS id, Question.name AS name, Question.label AS label,
Question.datatypeid AS datatypeid, Question.advanced AS advanced,
Question.multivalue AS multivalue, Question.measurementtypeid AS
measurementtypeid, Question.hotlist AS hotlist FROM main.Question,
main.CategoryQuestions, main.ApplicationQuestions,
main.TechnologyQuestions WHERE (Question.id =
CategoryQuestions.questionid AND CategoryQuestions.categoryid =
@categoryid ) OR (Question.id = ApplicationQuestions.questionid AND
ApplicationQuestions.applicationid = @applicationid ) OR (Question.id
= TechnologyQuestions.questionid AND TechnologyQuestions.technologyid
= @technologyid ) ;';


   stmtGetQuestionsNewSystemType.itemClass = QuestionVO;
   stmtGetQuestionsNewSystemType.addEventListener(
SQLEvent.RESULT,
   function ( event:SQLEvent ):void {
   resultHandlerQuestionsNewSystemType.call(
this, new
ArrayCollection( stmtGetQuestionsNewSystemType.getResult().data ),
_rowItem );
   });


then whenever I run the query i merely do this:

setParameters( stmtGetQuestionsNewSystemType, [ {name:"categoryid",
value:rowItem.systemcategoryid}, {name:"applicationid",
value:rowItem.systemapplicationid}, {name:"technologyid",
value:rowItem.systemtechnologyid} ] );

stmtGetQuestionsNewSystemType.execute();


Things I did to improve efficiency:
reused parametrized statements without modifying text property
declare explicitly that the table is coming from database main
declare are columns I want to retrieve in my select statement


This query is still taking about 40 seconds to execute on a relatively
fast computer. I am almost at my wits end because I can't figure out
how to make this query execute quickly. I know that you have to be
somewhat crafty to be efficient with sqlite so I was hoping someone
could point me in the right direction.

thanks,

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

Re: [sqlite] Why we don't have strpos function?

[Israel Lins Albuquerque]

I fink now is all right: 

I follow your instructions and no more call to strlen are maked. 
And utf8 chars are ignored returning the correct char index when non blob, 
and if in text have any '\0' (only in blob fields i believe), 
this code will work too. 


Porwerfull test cases: 
-- with text 
SELECT DISTINCT 'strpos(' || quote(str) || ', ' 
|| quote(substr) || ', ' 
|| CAST(ti.i * ts.s AS TEXT) || ')' as expr 
, strpos(str, substr, ti.i * ts.s) as result 
FROM (SELECT 'áéíóúáéíóúáéíóúáéíóú' as str, 'áéíóú' as substr) 
JOIN (SELECT 0 as i UNION ALL 
SELECT 1 as i UNION ALL 
SELECT 2 as i UNION ALL 
SELECT 3 as i UNION ALL 
SELECT 4 as i UNION ALL 
SELECT 5 as i) as ti 
JOIN (SELECT 1 as s UNION ALL 
SELECT -1 as s) as ts 
ORDER BY ti.i * ts.s ; 

-- with blob 
SELECT DISTINCT 'strpos(' || quote(str) || ', ' 
|| quote(substr) || ', ' 
|| CAST(ti.i * ts.s AS TEXT) || ')' as expr 
, strpos(str, substr, ti.i * ts.s) as result 
FROM (SELECT X'0001020001020001020001020001' as str, X'000102' as substr) 
JOIN (SELECT 0 as i UNION ALL 
SELECT 1 as i UNION ALL 
SELECT 2 as i UNION ALL 
SELECT 3 as i UNION ALL 
SELECT 4 as i UNION ALL 
SELECT 5 as i) as ti 
JOIN (SELECT 1 as s UNION ALL 
SELECT -1 as s) as ts 
ORDER BY ti.i * ts.s ; 

-- with integer 
SELECT DISTINCT 'strpos(' || quote(str) || ', ' 
|| quote(substr) || ', ' 
|| CAST(ti.i * ts.s AS TEXT) || ')' as expr 
, strpos(str, substr, ti.i * ts.s) as result 
FROM (SELECT 102102102102 as str, 102 as substr) 
JOIN (SELECT 0 as i UNION ALL 
SELECT 1 as i UNION ALL 
SELECT 2 as i UNION ALL 
SELECT 3 as i UNION ALL 
SELECT 4 as i UNION ALL 
SELECT 5 as i) as ti 
JOIN (SELECT 1 as s UNION ALL 
SELECT -1 as s) as ts 
ORDER BY ti.i * ts.s ; 

/**/ 
Index: func.c 
=== 
--- func.c (revision 264) 
+++ func.c (working copy) 
@@ -117,6 +117,77 @@ 
} 

/* 
+** Implementation of the strpos() function 
+*/ 
+static void strposFunc( 
+ sqlite3_context *context, 
+ int argc, 
+ sqlite3_value **argv 
+){ 
+ const char *z; 
+ const char *z1; 
+ const char *z2; 
+ int len; 
+ int len1; 
+ int instnum; 
+ int pass; 
+ 
+ assert( argc==2 || argc==3 ); 
+ if( sqlite3_value_type(argv[1]) == SQLITE_NULL 
+ || (argc==3 && sqlite3_value_type(argv[2]) != SQLITE_INTEGER) 
+ ){ 
+ return; 
+ } 
+ 
+ z = (char*)sqlite3_value_blob(argv[0]); 
+ if (z == NULL) return; 
+ 
+ z1 = (char*)sqlite3_value_blob(argv[1]); 
+ if (z1 == NULL) return; 
+ 
+ if (argc>=3) { 
+ instnum = sqlite3_value_int64(argv[2]); 
+ if (instnum == 0) return; 
+ } else instnum = 1; 
+ 
+ len = sqlite3_value_bytes(argv[0]); 
+ len1 = sqlite3_value_bytes(argv[1]); 
+ if (instnum < 0) { 
+ pass = -1; 
+ z2 = z + len - len1; 
+ } else { 
+ pass = 1; 
+ z2 = z; 
+ } 
+ 
+ while ((z2 >= z) && ((z2 + len1) <= (z + len))) { 
+ if (memcmp(z2, z1, len1) == 0) { 
+ instnum -= pass; 
+ if (instnum == 0) break; 
+ } 
+ z2 += pass; 
+ } 
+ 
+ 
+ if (instnum == 0) { 
+ int pos = 0; 
+ if (sqlite3_value_type(argv[0]) != SQLITE_BLOB) { 
+ // remove the utf-8 chars until here 
+ while (z <= z2) { 
+ SQLITE_SKIP_UTF8((u_char*)z); 
+ ++pos; 
+ } 
+ } else { 
+ pos = (int)(z2 - z) + 1; 
+ } 
+ sqlite3_result_int64(context, pos); 
+ return; 
+ } else { 
+ sqlite3_result_int64(context, 0); 
+ } 
+} 
+ 
+/* 
** Implementation of the abs() function. 
** 
** IMP: R-23979-26855 The abs(X) function returns the absolute value of 
@@ -1527,6 +1598,8 @@ 
AGGREGATE(max, 1, 1, 1, minmaxStep, minMaxFinalize ), 
FUNCTION(typeof, 1, 0, 0, typeofFunc ), 
FUNCTION(length, 1, 0, 0, lengthFunc ), 
+ FUNCTION(strpos, 2, 0, 0, strposFunc ), 
+ FUNCTION(strpos, 3, 0, 0, strposFunc ), 
FUNCTION(substr, 2, 0, 0, substrFunc ), 
FUNCTION(substr, 3, 0, 0, substrFunc ), 
FUNCTION(abs, 1, 0, 0, absFunc ), 

/**/ 

-- 

Atenciosamente, 

Israel Lins Albuquerque 
Desenvolvimento 
Polibrás Brasil Software Ltda. 


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

Re: [sqlite] Why we don't have strpos function?

[Nicolas Williams]

On Wed, Mar 17, 2010 at 03:02:59PM -0500, Jay A. Kreibich wrote:
> On Wed, Mar 17, 2010 at 04:42:59PM -0300, Israel Lins Albuquerque scratched 
> on the wall:
> > are you right the call to strpos("??", "??") are returning 5 and 
> > not 3 
> > 
> > I'm looking for this... 
> 
>   You can't do pointer math with values returned by strlen().

Hmmm.  On Solaris strlen() returns the number of _bytes_, not
characters, in the given string.  On Linux the manpage claims that
strlen() returns the number of characters in the string, but doesn't
define "character", but then, other glibc manpages actually have
examples of using strlen() in pointer arithmetic (see utmpname(3), for
example).  I can't imagine the glibc strlen() counting characters in the
Unicode sense, only in the old C sense (char), but then, I've not looked
at its source code.  In any case, there's a lot of code out there that
uses strlen() for pointer arithmetic.  And if strlen() really did count
characters rather than bytes, that wouldn't cause buffer overruns (since
number of characters in a string is necessarily less than or equal to
the number of bytes) though it would cause other bugs, some potentially
security bugs, such that I believe it quite unsafe for strlen() to do
anything other than count bytes in a string (not including the NUL
terminator).

As to strpos(), one should not naively implement it or anything like
strstr() or strrstr().  The relevant Unicode concepts are: codepoint,
character (composed of codepoints) and glyph (composed of characters and
codepoints).  Even if you support only codepoints you have to be mindful
of multi-byte encodings in UTF-8 and UTF-16.  Multi-character glyphs are
harder to deal with than multi-codepoint characters since you can easily
determine whether a codepoint is a combining codepoint (well, you have
to map the codepoint to various codepoint ranges, so that this is not a
cheap operation).  Normalization also affects strstr()-like functions.

On the plus side you can optimize such functions whenever you see two or
more contiguous US-ASCII codepoints.

Cheers,

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

Re: [sqlite] Why we don't have strpos function?

[Pavel Ivanov]

>  I think you're actual question is if zero-characters are prohibited,
>  and I think the answer is no.

Yes, that was exactly my question.

>  In any event, you can use
>  sqlite3_value_bytes() to find out the length of the text value
>  returned.  That is, however BYTES, not CHARACTERS.

Oh, totally missed sqlite3_value_bytes() function. So then my
suggestion to Israel that I wanted to write in the first place still
stands: to be more generic you should never use strlen() function, use
sqlite3_value_bytes() instead.


Pavel

On Wed, Mar 17, 2010 at 4:49 PM, Jay A. Kreibich  wrote:
> On Wed, Mar 17, 2010 at 04:36:02PM -0400, Pavel Ivanov scratched on the wall:
>> > ?You can't do pointer math with values returned by strlen().
>>
>> BTW, I see that SQLite has sqlite3_value_text() function but has no
>> sqlite3_value_text_len() function. Is it explicitly mentioned
>> somewhere that '\0' bytes in the middle of the text are prohibited? It
>> seems that sqlite3_bind_text() doesn't put that kind of restriction.
>
>  sqlite3_value_text() always returns UTF8.  UTF8 cannot contain a
>  zero-valued byte that is not a zero-valued character.
>
>  I think you're actual question is if zero-characters are prohibited,
>  and I think the answer is no.
>
>  Values returned by _text() are always zero-terminated, but I'm not
>  sure there is anything stopping you from entering a string with a
>  null character in the middle (assuming you pass in an explicit
>  length to sqlite3_bind_text()).  In any event, you can use
>  sqlite3_value_bytes() to find out the length of the text value
>  returned.  That is, however BYTES, not CHARACTERS.
>
>   -j
>
> --
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
>
> "Our opponent is an alien starship packed with atomic bombs.  We have
>  a protractor."   "I'll go home and see if I can scrounge up a ruler
>  and a piece of string."  --from Anathem by Neal Stephenson
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why we don't have strpos function?

[Jay A. Kreibich]

On Wed, Mar 17, 2010 at 04:36:02PM -0400, Pavel Ivanov scratched on the wall:
> > ?You can't do pointer math with values returned by strlen().
> 
> BTW, I see that SQLite has sqlite3_value_text() function but has no
> sqlite3_value_text_len() function. Is it explicitly mentioned
> somewhere that '\0' bytes in the middle of the text are prohibited? It
> seems that sqlite3_bind_text() doesn't put that kind of restriction.

  sqlite3_value_text() always returns UTF8.  UTF8 cannot contain a
  zero-valued byte that is not a zero-valued character.

  I think you're actual question is if zero-characters are prohibited,
  and I think the answer is no.

  Values returned by _text() are always zero-terminated, but I'm not
  sure there is anything stopping you from entering a string with a
  null character in the middle (assuming you pass in an explicit
  length to sqlite3_bind_text()).  In any event, you can use
  sqlite3_value_bytes() to find out the length of the text value
  returned.  That is, however BYTES, not CHARACTERS.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why we don't have strpos function?

[Pavel Ivanov]

>  You can't do pointer math with values returned by strlen().

BTW, I see that SQLite has sqlite3_value_text() function but has no
sqlite3_value_text_len() function. Is it explicitly mentioned
somewhere that '\0' bytes in the middle of the text are prohibited? It
seems that sqlite3_bind_text() doesn't put that kind of restriction.


Pavel

On Wed, Mar 17, 2010 at 4:02 PM, Jay A. Kreibich  wrote:
> On Wed, Mar 17, 2010 at 04:42:59PM -0300, Israel Lins Albuquerque scratched 
> on the wall:
>> are you right the call to strpos("??", "??") are returning 5 and not 
>> 3
>>
>> I'm looking for this...
>
>  You can't do pointer math with values returned by strlen().
>
>   -j
>
>
> --
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
>
> "Our opponent is an alien starship packed with atomic bombs.  We have
>  a protractor."   "I'll go home and see if I can scrounge up a ruler
>  and a piece of string."  --from Anathem by Neal Stephenson
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite error: database is locked

[imin imup]

> Thanks for clarification. I assumed the error message of error code

> > SQLITE_BUSY is something like "databased is busy".
>
> http://www.sqlite.org/c3ref/c_abort.html
> Also in sqlite3.h file - comments on the right are almost exactly
> reflect the corresponding error message.
>

You are totally right.


>
> > Another newbie question. Is it safe for 2 threads to share 1 connection
> when
> > the connection is protected by my own mutex?
>
> Sure, everything protected by mutex can be considered executing in one
> thread and thus is never prone to multi-threading issues.
>
> Thanks for pointing out, very appreciated.
I'll restructure the code.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why we don't have strpos function?

[Jay A. Kreibich]

On Wed, Mar 17, 2010 at 04:42:59PM -0300, Israel Lins Albuquerque scratched on 
the wall:
> are you right the call to strpos("??", "??") are returning 5 and not 
> 3 
> 
> I'm looking for this... 

  You can't do pointer math with values returned by strlen().

   -j


-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why we don't have strpos function?

[Israel Lins Albuquerque]

are you right the call to strpos("áéíóú", "í") are returning 5 and not 3 

I'm looking for this... 

-- 

Atenciosamente, 

Israel Lins Albuquerque 
Desenvolvimento 
Polibrás Brasil Software Ltda. 


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

Re: [sqlite] Why we don't have strpos function?

[Israel Lins Albuquerque]

actually yes, because this is the minimal size of a char in UTF-8, because 
sqlite3_value_text returns the 


- Mensagem original - 
De: "Jay A. Kreibich"  
Para: "General Discussion of SQLite Database"  
Enviadas: Quarta-feira, 17 de Março de 2010 15:19:43 
Assunto: Re: [sqlite] Why we don't have strpos function? 

On Wed, Mar 17, 2010 at 01:15:35PM -0300, Israel Lins Albuquerque scratched on 
the wall: 
> There are the patch: 

Which seems to assume one character equals one byte. 

-j 

-- 
Jay A. Kreibich < J A Y @ K R E I B I.C H > 

"Our opponent is an alien starship packed with atomic bombs. We have 
a protractor." "I'll go home and see if I can scrounge up a ruler 
and a piece of string." --from Anathem by Neal Stephenson 
___ 
sqlite-users mailing list 
sqlite-users@sqlite.org 
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 


-- 

Atenciosamente, 

Israel Lins Albuquerque 
Desenvolvimento 
Polibrás Brasil Software Ltda. 


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

Re: [sqlite] sqlite error: database is locked

[Pavel Ivanov]

> Thanks for clarification. I assumed the error message of error code
> SQLITE_BUSY is something like "databased is busy".

http://www.sqlite.org/c3ref/c_abort.html
Also in sqlite3.h file - comments on the right are almost exactly
reflect the corresponding error message.

> Another newbie question. Is it safe for 2 threads to share 1 connection when
> the connection is protected by my own mutex?

Sure, everything protected by mutex can be considered executing in one
thread and thus is never prone to multi-threading issues.


Pavel

On Wed, Mar 17, 2010 at 3:00 PM, imin imup  wrote:
>> According to documents, sqlite_busy will happen if new reader cannot get
>
>> > shared_lock or new writer cannot get reserved_lock.
>> > I didn't see sqlite_busy error from my application.
>>
>> I didn't understand this. "database is locked" is SQLITE_BUSY.
>>
> Thanks for clarification. I assumed the error message of error code
> SQLITE_BUSY is something like "databased is busy".
>
>>
>> > Does this mean I need to close the current database connection before I
>> can
>> > make the next connection to same database file even through they are not
>> > shared?
>>
>> No, you can have several connections in one thread if you want.
>>
>> > Though it is not shared, it seems there may be cases where more
>> > than 1 database connections are held (in nested function calls). Will
>> this
>> > cause any issue?
>>
>> This can definitely be the issue. Consider this scenario:
>> - open connection 1;
>> - start executing some query on connection 1 - call sqlite3_step() but
>> it didn't return SQLITE_DONE yet;
>> - open connection 2;
>> - execute some update/insert/delete on connection 2
>>
>> In this case you have active statement on connection 1 that holds
>> SHARED lock on the database. And it will prevent any other connections
>> from writing. So connection 2 cannot obtain EXCLUSIVE lock, you get
>> SQLITE_BUSY without any chances to succeed.
>>
>
> Very good point. It seems one connection per function can be error prone.
> One long-lived connection per thread might be better.
>
> Another newbie question. Is it safe for 2 threads to share 1 connection when
> the connection is protected by my own mutex?
>
> Thanks a lot.
>
>
>>
>> Pavel
>>
>> On Wed, Mar 17, 2010 at 1:35 PM, imin imup  wrote:
>> > Thanks for help. As a novel sqlite user, it seems I need more.
>> >
>> > According to documents, sqlite_busy will happen if new reader cannot get
>> > shared_lock or new writer cannot get reserved_lock.
>> > I didn't see sqlite_busy error from my application.
>> >
>> > My usage is that:
>> > single process, multi-thread, multi-database files
>> >
>> > multi-database files
>> > I divided the tables into 2 database files to "increase" concurrency. A
>> > thread may open two database file at same time, but two database never
>> > appear in one sql statement. Is this likely to cause any issue?
>> >
>> > about "one connection per thread"
>> > Does this mean I need to close the current database connection before I
>> can
>> > make the next connection to same database file even through they are not
>> > shared?
>> > I tend to use short-lived database connections locally defined within
>> each
>> > function. Though it is not shared, it seems there may be cases where more
>> > than 1 database connections are held (in nested function calls). Will
>> this
>> > cause any issue?
>> >
>> > On Wed, Mar 17, 2010 at 11:05 AM, Pavel Ivanov 
>> wrote:
>> >
>> >> http://www.sqlite.org/faq.html#q5
>> >> http://www.sqlite.org/lockingv3.html
>> >>
>> >> Pavel
>> >>
>> >> On Wed, Mar 17, 2010 at 11:46 AM, imin imup  wrote:
>> >> > Hello users,
>> >> >
>> >> > I'm using sqlite 3.6.12 in muti-threaded application. I'm getting
>> sqlite
>> >> > errors occasionally.
>> >> > The error message is
>> >> >
>> >> > *sqlite error: database is locked*
>> >> >
>> >> > could someone explain to me what happened and what to be done? or
>> point
>> >> me
>> >> > to a document on how to fix this?
>> >> >
>> >> > Best
>> >> >
>> >> > Imin
>> >> > ___
>> >> > 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
>> >
>> ___
>> 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
> 

Re: [sqlite] sqlite error: database is locked

[imin imup]

> According to documents, sqlite_busy will happen if new reader cannot get

> > shared_lock or new writer cannot get reserved_lock.
> > I didn't see sqlite_busy error from my application.
>
> I didn't understand this. "database is locked" is SQLITE_BUSY.
>
Thanks for clarification. I assumed the error message of error code
SQLITE_BUSY is something like "databased is busy".

>
> > Does this mean I need to close the current database connection before I
> can
> > make the next connection to same database file even through they are not
> > shared?
>
> No, you can have several connections in one thread if you want.
>
> > Though it is not shared, it seems there may be cases where more
> > than 1 database connections are held (in nested function calls). Will
> this
> > cause any issue?
>
> This can definitely be the issue. Consider this scenario:
> - open connection 1;
> - start executing some query on connection 1 - call sqlite3_step() but
> it didn't return SQLITE_DONE yet;
> - open connection 2;
> - execute some update/insert/delete on connection 2
>
> In this case you have active statement on connection 1 that holds
> SHARED lock on the database. And it will prevent any other connections
> from writing. So connection 2 cannot obtain EXCLUSIVE lock, you get
> SQLITE_BUSY without any chances to succeed.
>

Very good point. It seems one connection per function can be error prone.
One long-lived connection per thread might be better.

Another newbie question. Is it safe for 2 threads to share 1 connection when
the connection is protected by my own mutex?

Thanks a lot.


>
> Pavel
>
> On Wed, Mar 17, 2010 at 1:35 PM, imin imup  wrote:
> > Thanks for help. As a novel sqlite user, it seems I need more.
> >
> > According to documents, sqlite_busy will happen if new reader cannot get
> > shared_lock or new writer cannot get reserved_lock.
> > I didn't see sqlite_busy error from my application.
> >
> > My usage is that:
> > single process, multi-thread, multi-database files
> >
> > multi-database files
> > I divided the tables into 2 database files to "increase" concurrency. A
> > thread may open two database file at same time, but two database never
> > appear in one sql statement. Is this likely to cause any issue?
> >
> > about "one connection per thread"
> > Does this mean I need to close the current database connection before I
> can
> > make the next connection to same database file even through they are not
> > shared?
> > I tend to use short-lived database connections locally defined within
> each
> > function. Though it is not shared, it seems there may be cases where more
> > than 1 database connections are held (in nested function calls). Will
> this
> > cause any issue?
> >
> > On Wed, Mar 17, 2010 at 11:05 AM, Pavel Ivanov 
> wrote:
> >
> >> http://www.sqlite.org/faq.html#q5
> >> http://www.sqlite.org/lockingv3.html
> >>
> >> Pavel
> >>
> >> On Wed, Mar 17, 2010 at 11:46 AM, imin imup  wrote:
> >> > Hello users,
> >> >
> >> > I'm using sqlite 3.6.12 in muti-threaded application. I'm getting
> sqlite
> >> > errors occasionally.
> >> > The error message is
> >> >
> >> > *sqlite error: database is locked*
> >> >
> >> > could someone explain to me what happened and what to be done? or
> point
> >> me
> >> > to a document on how to fix this?
> >> >
> >> > Best
> >> >
> >> > Imin
> >> > ___
> >> > 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
> >
> ___
> 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] SQLite ODBC Driver - relative path

[Guy Hachlili]

Hello.

On 17/03/2010 04:16, Shen Nan wrote:
> I used SQLite ODBC Driver to display data in Excel, and everything works
> fine.
> Except in the connection String, I have to enter an absolute path, which is
> very troublesome every time I move the file.
> I wonder is there any way to specify relative path instead in the connection
> String?

You can do two things to (probably) fix this.
1. You can use a symbolic link to the moved database file. Use an 
absolute path to a location that should never change, and in that 
location, put a symbolic link to the database file; to create one, try 
the sysinternal utility here: 
http://technet.microsoft.com/en-us/sysinternals/bb896768.aspx
2. If you move the database file with the Excel file, you can recreate 
the ODBC connection string in VBA every time you open the file by 
parsing the Excel document's path name and replacing the relative part.

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

Re: [sqlite] Why we don't have strpos function?

[Jay A. Kreibich]

On Wed, Mar 17, 2010 at 01:15:35PM -0300, Israel Lins Albuquerque scratched on 
the wall:
> There are the patch: 

  Which seems to assume one character equals one byte.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite error: database is locked

[imin imup]

Thanks for help. As a novel sqlite user, it seems I need more.

According to documents, sqlite_busy will happen if new reader cannot get
shared_lock or new writer cannot get reserved_lock.
I didn't see sqlite_busy error from my application.

My usage is that:
single process, multi-thread, multi-database files

multi-database files
I divided the tables into 2 database files to "increase" concurrency. A
thread may open two database file at same time, but two database never
appear in one sql statement. Is this likely to cause any issue?

about "one connection per thread"
Does this mean I need to close the current database connection before I can
make the next connection to same database file even through they are not
shared?
I tend to use short-lived database connections locally defined within each
function. Though it is not shared, it seems there may be cases where more
than 1 database connections are held (in nested function calls). Will this
cause any issue?

On Wed, Mar 17, 2010 at 11:05 AM, Pavel Ivanov  wrote:

> http://www.sqlite.org/faq.html#q5
> http://www.sqlite.org/lockingv3.html
>
> Pavel
>
> On Wed, Mar 17, 2010 at 11:46 AM, imin imup  wrote:
> > Hello users,
> >
> > I'm using sqlite 3.6.12 in muti-threaded application. I'm getting sqlite
> > errors occasionally.
> > The error message is
> >
> > *sqlite error: database is locked*
> >
> > could someone explain to me what happened and what to be done? or point
> me
> > to a document on how to fix this?
> >
> > Best
> >
> > Imin
> > ___
> > 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] Why we don't have strpos function?

[Israel Lins Albuquerque]

There are the patch: 

/***/ 
Index: func.c 
=== 
--- func.c (revision 264) 
+++ func.c (working copy) 
@@ -117,6 +117,65 @@ 
} 

/* 
+** Implementation of the strpos() function 
+*/ 
+static void strposFunc( 
+ sqlite3_context *context, 
+ int argc, 
+ sqlite3_value **argv 
+){ 
+ const char *z; 
+ const char *z1; 
+ const char *z2; 
+ int len; 
+ int len1; 
+ int pos; 
+ int pass; 
+ 
+ assert( argc==2 || argc==3 ); 
+ if( sqlite3_value_type(argv[1]) == SQLITE_NULL 
+ || (argc==3 && sqlite3_value_type(argv[2]) != SQLITE_INTEGER) 
+ ){ 
+ return; 
+ } 
+ 
+ z = (char*)sqlite3_value_text(argv[0]); 
+ if (z == NULL) return; 
+ 
+ z1 = (char*)sqlite3_value_text(argv[1]); 
+ if (z1 == NULL) return; 
+ 
+ if (argc>=3) { 
+ pos = sqlite3_value_int64(argv[2]); 
+ if (pos == 0) return; 
+ } else pos = 1; 
+ 
+ if (pos < 0) { 
+ pass = -1; 
+ z2 = z + strlen(z) - 1; 
+ } else { 
+ pass = 1; 
+ z2 = z; 
+ } 
+ 
+ len = strlen(z); 
+ len1 = strlen(z1); 
+ do { 
+ if (strncmp(z2, z1, len1) == 0) { 
+ pos -= pass; 
+ if (pos == 0) break; 
+ } 
+ z2 += pass; 
+ } while ((z2 >= z) && (z2 < (z + len))); 
+ 
+ if (pos == 0) { 
+ sqlite3_result_int64(context, (int)(z2 - z) + 1); 
+ } else { 
+ sqlite3_result_int64(context, 0); 
+ } 
+} 
+ 
+/* 
** Implementation of the abs() function. 
** 
** IMP: R-23979-26855 The abs(X) function returns the absolute value of 
@@ -1527,6 +1586,8 @@ 
AGGREGATE(max, 1, 1, 1, minmaxStep, minMaxFinalize ), 
FUNCTION(typeof, 1, 0, 0, typeofFunc ), 
FUNCTION(length, 1, 0, 0, lengthFunc ), 
+ FUNCTION(strpos, 2, 0, 0, strposFunc ), 
+ FUNCTION(strpos, 3, 0, 0, strposFunc ), 
FUNCTION(substr, 2, 0, 0, substrFunc ), 
FUNCTION(substr, 3, 0, 0, substrFunc ), 
FUNCTION(abs, 1, 0, 0, absFunc ), 

/***/ 


- Mensagem original - 
De: "Israel Lins Albuquerque"  
Para: "General Discussion of SQLite Database"  
Enviadas: Quarta-feira, 17 de Março de 2010 12:08:56 
Assunto: Re: [sqlite] Why we don't have strpos function? 

I forgot attach... 

This are based in the current release 3.6.23. 

-- 

Atenciosamente, 

Israel Lins Albuquerque 
Desenvolvimento 
Polibrás Brasil Software Ltda. 



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


-- 

Atenciosamente, 

Israel Lins Albuquerque 
Desenvolvimento 
Polibrás Brasil Software Ltda. 


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

Re: [sqlite] sqlite error: database is locked

[Pavel Ivanov]

http://www.sqlite.org/faq.html#q5
http://www.sqlite.org/lockingv3.html

Pavel

On Wed, Mar 17, 2010 at 11:46 AM, imin imup  wrote:
> Hello users,
>
> I'm using sqlite 3.6.12 in muti-threaded application. I'm getting sqlite
> errors occasionally.
> The error message is
>
> *sqlite error: database is locked*
>
> could someone explain to me what happened and what to be done? or point me
> to a document on how to fix this?
>
> Best
>
> Imin
> ___
> 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 in multi-thread server

[Pavel Ivanov]

No TLS is used in SQLite. So you can open connection in one thread and
use it in another. It's just a general suggestion to have one
connection per thread. Otherwise you'll need to have some
synchronization code or allow SQLite to do full synchronization for
you.

Pavel

On Tue, Mar 16, 2010 at 5:55 PM, HLS  wrote:
> Do you know if TLS is used?  How does it workout splite3_open_v2() per
> thread?  Can the handle be used globally?   In other words, is open
> per thread considered a "different connection?"
>
> On Tue, Mar 16, 2010 at 3:57 PM, Pavel Ivanov  wrote:
>> Please be advised that updating/inserting/deleting from the table
>> while you're executing select on it has undefined behavior in SQLite
>> (if you do that on the same connection) and is considered dangerous.
>> Doing that from different connections is possible only if have shared
>> cache turned on and read_uncommitted set to 1. If you don't use shared
>> cache and turned off locking on VFS level then you can easily get
>> database corruption.
>>
>> Pavel
>>
>> On Tue, Mar 16, 2010 at 2:12 PM, HLS  wrote:
>>> Thanks Simon.  It just seem so simplistic that SQLITE3 does not allow for
>>>
>>> Open Cursor
>>> for each fetch
>>>     Issue Update based on ROWID
>>> endfor
>>> Close Cursor
>>>
>>> The row fetched is already complete, or the rowid in the table is no
>>> longer "sensitive" to anything but a update whether it was opened or
>>> not. The "current" cursor is at the next record ready to be fetched,
>>> not the one that just been read.  So it would seem it would be
>>> possible to implement a wrap a lock on the rowid update.
>>>
>>> I had to see if this is possible with the VFS notes Pavel pointed me
>>> to perhaps, or even been looking at the feasibility of changing code
>>> (which is the last thing I don't want to do.)
>>>
>>> On Tue, Mar 16, 2010 at 1:31 PM, Simon Slavin  wrote:

 On 16 Mar 2010, at 5:17pm, HLS wrote:

> Once approach is to queue any updates/deletes when the database is
> locked with a select request.  So when a fetch ends (like in the
> GetNext function), it will check to see for any pending updates
> and process them.
>
> Does that sound like a viable approach with SQLITE3?
>
> Hm, this would not address possible client code that can break
> from a loop before reaching the end of select query.

 For the approach that involves queueing write commands, you do definitely 
 need to know when a SELECT has finished with the database.  But SQLite 
 depends on that anyway, and there's no reason why it shouldn't depend on 
 correct usage of the API including sqlite3_finalize() .

 I have one installation where it's okay for SELECT commands to return 
 results which are slightly out of date, but new data becomes available at 
 sparse and irregular intervals from many sources.  Consequently, this 
 system delays write commands until there has been no access to the 
 database for one minute (checked by touching a file whenever 
 sqlite3_prepare() is done).  In this particular installation this makes 
 perfect sense, because the patterns of reads and writes is well 
 understood.  However, for some random installation for a random use of 
 SQLite it would be disastrous.  You may be in a situation where you can 
 develop a protocol which fits your particular use of SQL very well even 
 though the solution would be useless for a more general use of SQLite.

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

>>>
>>>
>>>
>>> --
>>> hls
>>> ___
>>> 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
>>
>
>
>
> --
> hls
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite error: database is locked

[imin imup]

Hello users,

I'm using sqlite 3.6.12 in muti-threaded application. I'm getting sqlite
errors occasionally.
The error message is

*sqlite error: database is locked*

could someone explain to me what happened and what to be done? or point me
to a document on how to fix this?

Best

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

Re: [sqlite] Why we don't have strpos function?

[Israel Lins Albuquerque]

I forgot attach... 

This are based in the current release 3.6.23. 

-- 

Atenciosamente, 

Israel Lins Albuquerque 
Desenvolvimento 
Polibrás Brasil Software Ltda. 


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

Re: [sqlite] Why we don't have strpos function?

[Israel Lins Albuquerque]

I've make an own implementation and I want to contribute see the patch 
attached. 

Sintax: 
strpos(string, substring[, pos]); 

string -> the master string, can be any type (INTEGER, REAL, BLOB) 
substring -> to be searched, can be any type (INTEGER, REAL, BLOB) 
pos [optional default = 1] -> is the number of instance of substring in want, 
this value can be negative, negative values 
will be the search in reverse order like (strrchr) 

Return: 
The return is the index (1 based) of the char, or 0 if not found, or null if 
invalid parameters passed 

Exemples: 
dirname = substr(filename, 1, strpos(filename, "/", -1) - 1) 
basename = substr(filename, strpos(filename, "/", -1) + 1) 
strpos(123231, 2) == 2 
strpos(123231, 2, 1) == 2 
strpos(123231, 2, -1) == 4 
strpos(123231, 2, -2) == 2 
strpos(123231, 2, -3) == 0 


retrictions: pos need be different of zero 


- Mensagem original - 
De: "Simon Slavin"  
Para: "General Discussion of SQLite Database"  
Enviadas: Quarta-feira, 17 de Março de 2010 11:05:04 
Assunto: Re: [sqlite] Why we don't have strpos function? 

Strings are 16-bit strings which may or may not be Unicode strings, I think. 
It's hard to work out the position in a string if you don't know what encoding 
you're using. 

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


-- 

Atenciosamente, 

Israel Lins Albuquerque 
Desenvolvimento 
Polibrás Brasil Software Ltda. 


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

Re: [sqlite] Why we don't have strpos function?

[Simon Slavin]

Strings are 16-bit strings which may or may not be Unicode strings, I think.  
It's hard to work out the position in a string if you don't know what encoding 
you're using.

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

[sqlite] Why we don't have strpos function?

[Israel Lins Albuquerque]

... 

-- 

Atenciosamente, 

Israel Lins Albuquerque 
Desenvolvimento 
Polibrás Brasil Software Ltda. 


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

Re: [sqlite] Problem with between clause and dates

[Jay A. Kreibich]

On Wed, Mar 17, 2010 at 12:18:44PM +, Mike Martin scratched on the wall:

> 
> >> select tsid from recordings where '2010-03-16 23:**05**:00' between

> It should return nothing because the time 2010-03-16 23:**06**:00 is later
> than the end time of 2010-03-16 23:**05**:00

  Let me guess... yet another typo.  In your post or in your code?

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite ODBC Driver - relative path

[Shen Nan]

Hi sqlite users,

 

I used SQLite ODBC Driver to display data in Excel, and everything works
fine.

Except in the connection String, I have to enter an absolute path, which is
very troublesome every time I move the file.

I wonder is there any way to specify relative path instead in the connection
String? 

 

Driver={SQLite3 ODBC Driver};Database=C:\TempFolder\test.db;...

 

I use the ODBC driver from http://www.ch-werner.de/sqliteodbc/.

 

Thank you for your help in advance!

 

Best regards,

Nan

 

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

Re: [sqlite] Problem with between clause and dates

[Igor Tandetnik]

Mike Martin wrote:
> I am getting occasional incorrect results using between operator
> 
> example
> 
> table structure
> recordings
> ID
> tsid
> start
> end
> 
> sample time= 2010-03-16 23:06:00
> sample start= 2010-03-16-22:00:00
> sample end= 2010-03-16 23:05:00

Do you actually have a dash between date and time portion in your data (in your 
example, one appears in start but not in time or end)? If you have it 
inconsistently, that would throw off any comparisons.

> select tsid from recordings where '2010-03-16 23:05:00' between start and end'

You have an extra, mismatched single quote at the end.

> This query should return nothing, so can anyone explain why it is
> recording the tsid

Why should this query return nothing? BETWEEN operator is inclusive. "x between 
a and b" is equivalent to "x>=a and x<=b".
-- 
Igor Tandetnik

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

Re: [sqlite] Problem with between clause and dates

[Michal Seliga]

On 17. 3. 2010 11:49, Mike Martin wrote:
> sample time= 2010-03-16 23:06:00
> sample start= 2010-03-16-22:00:00
> sample end= 2010-03-16 23:05:00
> select tsid from recordings where '2010-03-16 23:05:00' between start and end'
>
> This query should return nothing, so can anyone explain why it is
> recording the tsid
>
> thanks
>
>   

as far as i know  XX BETWEEN YY AND ZZ is equal to XX>=YY and XX<=ZZ so
its ok that it returns something. i always use it like that (but on
Sybase database)

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

Re: [sqlite] Problem with between clause and dates

[Simon Slavin]

On 17 Mar 2010, at 10:49am, Mike Martin wrote:

> sample time= 2010-03-16 23:06:00
> sample start= 2010-03-16-22:00:00
> sample end= 2010-03-16 23:05:00
> select tsid from recordings where '2010-03-16 23:05:00' between start and end'
> 
> This query should return nothing, so can anyone explain why it is
> recording the tsid

Your sample start time has a hyphen between date and time.  Is this mistyping 
when you wrote your email or is it in your data file like that ?

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

[sqlite] Problem with between clause and dates

[Mike Martin]

Hi

I am getting occasional incorrect results using between operator

example

table structure
recordings
ID
tsid
start
end

sample time= 2010-03-16 23:06:00
sample start= 2010-03-16-22:00:00
sample end= 2010-03-16 23:05:00
select tsid from recordings where '2010-03-16 23:05:00' between start and end'

This query should return nothing, so can anyone explain why it is
recording the tsid

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

Re: [sqlite] SQLite on 64-bit Windows O/S

[Simon Slavin]

On 17 Mar 2010, at 4:04am, Roger Binns wrote:

> Rashed Iqbal wrote:
>> Has anyone compiled and used SQLite on 64-bit Windows? Are there any
>> code changes that would be needed before compiling? Would the same DB
>> file work on 32-bit and 64-bit Windows platforms?
> 
> The file format is fixed and is independent of the host, 32 or 64 bit,
> endianess etc.
> 
> I don't believe there are any Win64 issues, but I don't have it myself.
> I do run SQLite on 64 bit Linux hosts and have done for years.

I have applications which compile for both 32-bit and 64-bit 
Macintoshes/Operating systems.  I use the same code on both platforms and both 
platforms access the same databases (sometimes at the same time).  I have found 
no problems resulting in the use of the different widths.

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

Re: [sqlite] VACUUM & journal size

[Max Vlasov]

>  First, I'm sure it is nearly impossible to do this as a
>  guaranteed, atomic operation on most OSes and filesystems.  ...
>
>  Second, if this is meant to look like a cleanup operation on the
>  original file, the original file (including any filesystem meta-data)
>  should be kept in-tact.  ...
>
>  Last, file manipulations of this sort aren't supported by the current
>  VFS interface, and (IMHO) with good reason.  ...
> ...
>  However, it might be nice to have a "VACUUM TO " version of the
>  command.
>

Thanks, Jay (and Pavel too), you gave a very detail description of the
situation. Although unlikely your interesting suggestion (VACUUM TO) will be
implemented, I think one always make his own equivalent if it is necessary
(creating a subset of operations from sqlite3RunVacuum)

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