[sqlite] regexp within trim function

[Linuxed]

Is it possible to use regexp within a trim function?  For example, if I wanted 
to remove every alpha-numeric character at the end of a string I could use the 
statement below.  Is there any way to simplify/condense the statement through 
regexp or a wildcard character?  Also, if I want to remove single or double 
quotes in addition to alpha-numeric characters how can I include them in the 
string of characters to trim?  I've tried \' and \" without success.


rtrim('this is an example. this is another example', 
'1234567890qwertyuiopasdfghjklzxcvbnmQWERTYUIOPASDFGHJKLZXCVBNM')
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to store and retrieve list of images from sqlite in android

[Mayuri]

Thanks for your replay.
I wrote the code  in java file for displaying images.But those images are
not displaying in emulator.
If you possible give me a sample application to retrieve list of images from
sqlite.
-- 
View this message in context: 
http://old.nabble.com/How-to-store-and-retrieve-list-of-images-from-sqlite-in-android-tp32469610p32476627.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] Questions about BLOB and page size

[Pavel Ivanov]

> 1. Does BLOB type field cause any performance issues compared to using TEXT> 
> when doing inserts/updates. In other words, if they use the same number of> 
> bytes, would there be any difference in performance?
I believe it's a little weird choice as TEXT fields have some
additional features that you don't want to have with binary data. But
to answer your question I think performance would depend on encoding
in your database (UTF-8 vs UTF-16) and the functions you use to bind
TEXT value.

> 2. When you store multiple tables, does each table get stored on a page by
> itself, so the larger the page size, the more likely you would see
> contiguous items for a table?

Yes, every table has its own set of pages, their data doesn't mix into
same pages.

> 3. How does the sqlite page size correlate with the file system block size?
> In other words, if sqlite uses a page size of 1K and the file system uses a
> block size of 4K, would you still end up writing 4K even if you changed only
> 1K?

Yes, at the OS level (i.e. SQLite doesn't do anything for that to
happen) you'll end up reading 4K (if it's not already in cache) and
writing 4K back with 1K of them changed.


Pavel


On Thu, Sep 15, 2011 at 10:04 PM, Andy Stec  wrote:
> We are trying to determine how BLOB type and page size impact performance.
> I have a few questions related to that:
>
> 1. Does BLOB type field cause any performance issues compared to using TEXT
> when doing inserts/updates. In other words, if they use the same number of
> bytes, would there be any difference in performance?
>
> 2. When you store multiple tables, does each table get stored on a page by
> itself, so the larger the page size, the more likely you would see
> contiguous items for a table?
>
> 3. How does the sqlite page size correlate with the file system block size?
> In other words, if sqlite uses a page size of 1K and the file system uses a
> block size of 4K, would you still end up writing 4K even if you changed only
> 1K?
> ___
> 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] Questions about BLOB and page size

[Andy Stec]

We are trying to determine how BLOB type and page size impact performance.
I have a few questions related to that:

1. Does BLOB type field cause any performance issues compared to using TEXT
when doing inserts/updates. In other words, if they use the same number of
bytes, would there be any difference in performance?

2. When you store multiple tables, does each table get stored on a page by
itself, so the larger the page size, the more likely you would see
contiguous items for a table?

3. How does the sqlite page size correlate with the file system block size?
In other words, if sqlite uses a page size of 1K and the file system uses a
block size of 4K, would you still end up writing 4K even if you changed only
1K?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 python foreign key

[Simon Slavin]

On 15 Sep 2011, at 9:57pm, Fabio Spadaro wrote:

> Sorry but I was wrong from the beginning; the result of "pragma ..." was
> wrong with python version 2.6 because each result were not processed
> in the same
> connection but different connections of database.

No problem.  Good luck with your project.

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

Re: [sqlite] installation problem

[jerome . moliere]

Hi arthur
I am not a windows expert but I guess that installer checks some key in the 
windows registry...May be trying to find the values checked and set them 
manually using regedit ?

Regards
Jerome
--Message d'origine--
De: Artur Ligmann
Expéditeur : sqlite-users-boun...@sqlite.org
À: sqlite-users@sqlite.org
Répondre à: General Discussion of SQLite Database
Objet: [sqlite] installation problem
Envoyé: 15 sept. 2011 22:21

Hi,

i was trying to install system.data.sqlite using the 
sqlite-netFx40-setup-bundle-x86-2010-1.0.74.0.exe installer but i got an 
error message telling me the visual studio 2010 sp1 redist can't be 
installed. Problem is this redist is already installed on my system. The 
installer stops afterwards What can I do ?

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


 Envoyé avec BlackBerry® d'Orange 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 python foreign key

[Fabio Spadaro]

Hi.

2011/9/15 Simon Slavin 

>
> On 15 Sep 2011, at 4:36pm, Fabio Spadaro wrote:
>
> > i just installed python 2.7.2 and the value returned by "PRAGMA
> > foreign_keys" is [(0,)]; Later I will try with the tables.
>
> Can you tell whether your python installation process is installing a new
> version of whatever sqlite API you're using ?  Depending on how it's
> packaged it might be part of it or it might be separate.
>
> Please do this entire sequence:
>
> Make a connection to the database.
> Issue "PRAGMA foreign_keys" and look at the table returned.
> Issue "PRAGMA foreign_keys = OFF".
> Issue "PRAGMA foreign_keys" and look at the table returned.
> Issue "PRAGMA foreign_keys = ON".
> Issue "PRAGMA foreign_keys" and look at the table returned.
>
> We can tell a lot by looking at the results, including whether your version
> was compiled without foreign key support.
>
> Note that foreign key support is always initially off.  Currently you are
> expected to do "PRAGMA foreign_keys = ON" each time you open a database even
> if you have previously used foreign keys with it.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

Sorry but I was wrong from the beginning; the result of "pragma ..." was
wrong with python version 2.6 because each result were not processed
in the same
connection but different connections of database.

-- 
Fabio Spadaro
www.fabiospadaro.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE of field deletes record

[Jean-Christophe Deschamps]

> the test code can be seen at
> http://www.autoitscript.com/trac/autoit/ticket/2012

  Are you sure it is gone?

  I'm not familiar with this API, but you appear to be finalizing the
  query BEFORE you extract rows from it.  In the native API, that's not
  valid, making the code report no rows, when it is really trying to
  report an API error.  Depending on how this API maps to the native
  API, the issue may be the SELECT, not the UPDATE.


Jay, you are of course right about pointing out the actual issue the OP 
has: finalizing a prepared statement before retrieving the 
resultset.  BTW the code in the ticket report has _many_ errors (AutoIt 
specific syntax, SQLite [SQL] syntax, missing bracket[s], a.s.o.).


This has nothing to do with SQLite itself, nor even the AutoIt bug 
report tracker, but only the AutoIt support forum where several 
contributors are more than willing to put posters on the right track.


Note: I'm the unofficial maintainer of the current SQLite support for 
AutoIt.



--
j...@antichoc.net  


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

Re: [sqlite] Named table constraints support in SQLite

[Andrew Sigmund]

On Sep 14, 2011, at 3:14 PM, Simon Slavin wrote:

>> Your exact text may not work to generate exactly the error you expect, but 
>> you should find something similar.
>
> That's the crux of the issue, as the constraint name is lost, even though the 
> constraint itself is implemented. Net loss of information.
> Bummer
> :(
>
> There is a ticket related to this issue, languishing, unloved and unresolved, 
> for many moons:
>
> http://www.sqlite.org/src/tktview?name=23b2128201
> http://www.sqlite.org/cvstrac/tktview?tn=1648

Yes, I too encountered this issue shortly after I started working with SQLite 
last year, and I too was disappointed at the lack of attention the ticket had 
received.  Someone did post code enhancement/fix somewhere in one of those 
tickets, but I've not looked at that code, because we're not quite into 
modifying SQLite's source (yet).

>From a relatively recent e-mail thread on this topic, my understanding is that 
>dredging up the actual constraint name for the message would take a lot of 
>effort (code), and that that is antithetical to the "lite-ness" of SQLite.

Confidentiality Notice.
This message may contain information that is confidential or otherwise 
protected from disclosure. If you are not the intended recipient, you are 
hereby notified that any use, disclosure, dissemination, distribution,  or 
copying  of this message, or any attachments, is strictly prohibited.  If you 
have received this message in error, please advise the sender by reply e-mail, 
and delete the message and any attachments.  Thank you.

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

[sqlite] installation problem

[Artur Ligmann]

Hi,

i was trying to install system.data.sqlite using the 
sqlite-netFx40-setup-bundle-x86-2010-1.0.74.0.exe installer but i got an 
error message telling me the visual studio 2010 sp1 redist can't be 
installed. Problem is this redist is already installed on my system. The 
installer stops afterwards What can I do ?


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

Re: [sqlite] Verified crash with SQLite 3.7.8 and 3.7.7.1

[Richard Hipp]

Ticket for this problem is here:  http://www.sqlite.org/src/info/002caede89

On Thu, Sep 15, 2011 at 10:21 AM, Filip Navara wrote:

> Hi,
>
> there's a bug report filled at
>
> http://system.data.sqlite.org/index.html/info/ce53939214b563cb49ad2375f7c2cb365907898d
> for System.Data.SQLite, but the underlying issue is reproducible with
> the same database on SQLite 3.7.7.1 and my recent builds of SQLite
> 3.7.8. Both crash with access violation. "pragma integrity_check;"
> returns ok for the database.
>
> The database file is linked in the ticket and the query is
>
> SELECT invItemID, storageLocationID, responsiblePersonID, deviceID,
> costCenterID, COALESCE(changeSuggCount, 0) AS changeSuggCount,
> containerInvItemID
> FROM InvItem
> LEFT OUTER JOIN
> (
>  SELECT iicsID, invItemID AS iiID, COUNT(*) AS changeSuggCount FROM
> InvItemChangeSuggestion
>  GROUP BY iiID
> ) AS ChangeSugg ON ChangeSugg.iiID = InvItem.invItemID
> LEFT OUTER JOIN
> (
>  SELECT iicsID AS iicsID2, propID, val FROM
> InvItemPropValueChangeSuggestion
>  WHERE (propID = -4)
>  AND (val = 'AB123987')
>  ORDER BY apprVersion DESC
>  LIMIT 1
> ) AS PropValChangeSugg ON PropValChangeSugg.iicsID2 = ChangeSugg.iicsID
> WHERE
> (rowStatus = 1 AND (val = 'AB123987'))
> OR ((invVersionID = 1)
> AND (rowStatus = 1)
> AND invItemID IN
> (
>  -- ha eredetileg illeszkedik a filter-re
>  SELECT invItemID FROM InvItem
>  WHERE (barCode = 'AB123987')
>  AND (invVersionID = 1)
>  AND (rowStatus = 1)
>
>  UNION
>
>  -- ha a m«¯dos«ìt«âsok ut«ân a(z) barCode illeszkedik a filter-re
>  SELECT invItemID FROM
>  (
>SELECT invItemID, val FROM
>(
>  SELECT invItemID, propID, val
>  FROM InvItemChangeSuggestion, InvItemPropValueChangeSuggestion
>  WHERE InvItemChangeSuggestion.iicsID =
> InvItemPropValueChangeSuggestion.iicsID
>  AND propID = -4
>  AND apprVersion <> 0
>  ORDER BY registerTime ASC
>)
>GROUP BY invitemID, propID
>  )
>  WHERE (val = 'AB123987')
>
>  EXCEPT
>
>  -- a(z) barCode eredetileg illeszkedett, de a m«¯dos«ìt«âsok ut«ân
> m«âr nem illeszkedik a filter-re
>  SELECT invItemID FROM
>  (
>SELECT invItemID, val FROM
>(
>  SELECT invItemID, propID, val
>  FROM InvItemChangeSuggestion, InvItemPropValueChangeSuggestion
>  WHERE InvItemChangeSuggestion.iicsID =
> InvItemPropValueChangeSuggestion.iicsID
>  AND propID = -4
>  AND apprVersion <> 0
>  ORDER BY registerTime ASC
>)
>GROUP BY invitemID, propID
>  )
>  WHERE (val <> 'AB123987')
> ));
>
> Best regards,
> Filip Navara
> ___
> 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] Does coalesce terminate early?

[Jan Hudec]

On Thu, Sep 15, 2011 at 18:58:00 +0100, Tim Streater wrote:
> On 15 Sep 2011 at 18:21, Jay A. Kreibich  wrote: 
> >  Why?  It is a function call.  One would expect all the parameters to
> >  be evaluated, and then the function called.  In almost all languages,
> >  short-circuit evaluation is reserved for operators, not function
> >  parameters.
> 
> Really? I'd like to think that if a parameter is not used due to the
> particular logic of the function, then it's not evaluated but I wouldn't go
> into court on that :-)

No. That only applies to non-strict (lazy) languages. Of which only Haskell
and possibly Miranda and Clean have any non-trivial user base outside
academia. Note, that all those languages are functional. Combining (implicit,
general) lazy evaluation with side effects would lead to unbelievable mess as
you would have hard time telling in which order the side-effect will happen.

Many languages do allow explicit lazy evaluation either by explicitly passing
functions or by having special kind of functions, macros, that take bits of
code instead of values and return bit of code to be evaluated (Lisp, Scheme,
Perl6, D).

That said, pure SQL does not allow side-effects and built-in functions may be
implemented specially just like operators can, so it's hard to tell whether
one should expect SQL to short-circuit in coalesce(). I would definitely
expect it to short-circuit in case/when/then/else/end, but I simply would not
know in function-like-looking coalesce().

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

[sqlite] Doc: wrong xCreate instructions for vtab object

[Martin Gadbois]

Hi,

I think the documentation is wrong in the virtual table document found under
http://www.sqlite.org/vtab.html.
Under xCreate() it says:
"The xCreate method need not initialize the pModule, nRef, and zErrMsg
fields of the sqlite3_vtab  object."

In fact, I had a segfault occurring in vtabBestIndex(), at the line:
  sqlite3_free(pVtab->zErrMsg);

zErrMsg was uninitialized.

Doing a memset(vtab,0,size) in my xCreate() fixed the issue.

Therefore, documentation should say:
"The xCreate method should set to zero the pModule, nRef, and zErrMsg fields
of the sqlite3_vtab  object."

Thank you.

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

Re: [sqlite] sqlite3 python foreign key

[Simon Slavin]

On 15 Sep 2011, at 7:02pm, Kees Nuyt wrote:

>  wrote:
> 
> 
>> i just installed python 2.7.2 and the value returned by "PRAGMA
>> foreign_keys" is [(0,)]; Later I will try with the tables.
> 
> You can get the version of the SQLite library with 
>   SELECT sqlite_version();

Dammit.  I was looking at the PRAGMAs for that and forgot it was a function.

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

Re: [sqlite] sqlite3 python foreign key

[Kees Nuyt]

On Thu, 15 Sep 2011 17:36:52 +0200, Fabio Spadaro
 wrote:


> i just installed python 2.7.2 and the value returned by "PRAGMA
> foreign_keys" is [(0,)]; Later I will try with the tables.

You can get the version of the SQLite library with 
SELECT sqlite_version();

Recent versions also support:
PRAGMA compile_options;
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does coalesce terminate early?

[Tim Streater]

On 15 Sep 2011 at 18:21, Jay A. Kreibich  wrote: 

> On Thu, Sep 15, 2011 at 11:13:57AM -0500, Puneet Kishor scratched on the wall:
>
>> While your suggested documentation won't harm, and will likely help,
>> actually the above does suggest to me a short-circuit-ish kind of
>> logic from the assertion that "The coalesce() function returns a
>> copy of its first non-NULL argument."
>
>  Why?  It is a function call.  One would expect all the parameters to
>  be evaluated, and then the function called.  In almost all languages,
>  short-circuit evaluation is reserved for operators, not function
>  parameters.

Really? I'd like to think that if a parameter is not used due to the particular 
logic of the function, then it's not evaluated but I wouldn't go into court on 
that :-)

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

Re: [sqlite] Does coalesce terminate early?

[Jay A. Kreibich]

On Thu, Sep 15, 2011 at 11:13:57AM -0500, Puneet Kishor scratched on the wall:

> While your suggested documentation won't harm, and will likely help,
> actually the above does suggest to me a short-circuit-ish kind of
> logic from the assertion that "The coalesce() function returns a
> copy of its first non-NULL argument."

  Why?  It is a function call.  One would expect all the parameters to
  be evaluated, and then the function called.  In almost all languages,
  short-circuit evaluation is reserved for operators, not function
  parameters.

   -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] UPDATE of field deletes record

[Jay A. Kreibich]

On Wed, Sep 14, 2011 at 04:28:55PM -0700, Jim Michaels scratched on the wall:
> with an existing record, and a fieldname that is in a column that
> has a UNIQUE INDEX, if I do sqlite_exec("UPDATE database SET
> fieldname="_escape("get calculator batteries")&" WHERE
> fieldname=sqlite_escape("get hp50g calc cells")&";"
> this code actually deletes the record.  it should not.  I don't ever
> remember seeing this happen in any database like pgsql or mysql.  I
> can test again to see for sure.
> 
> 
> the test code can be seen at
> http://www.autoitscript.com/trac/autoit/ticket/2012

  Are you sure it is gone?

  I'm not familiar with this API, but you appear to be finalizing the
  query BEFORE you extract rows from it.  In the native API, that's not
  valid, making the code report no rows, when it is really trying to
  report an API error.  Depending on how this API maps to the native
  API, the issue may be the SELECT, not the UPDATE.

   -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] Does coalesce terminate early?

[Sam Carleton]

On Thu, Sep 15, 2011 at 12:24 PM, Simon Slavin  wrote:
>
> On 15 Sep 2011, at 5:00pm, Sam Carleton wrote:
>
>> I don't mean to be difficult, but I simply don't get any indication of
>> how exactly COALESCE actually functions from this description:
>>
>> "coalesce(X,Y,...)    The coalesce() function returns a copy of its first
>> non-NULL argument, or NULL if all arguments are NULL. Coalesce() must
>> be at least 2 arguments. "
>>
>> I get there has to be at least two arguments, but no where do I see
>> where it states it only executes an argument if all the previous one
>> fail.
>
> Fair point.
>
>> Now it would be crystal clear if it said something like:
>>
>> "coalesce(X,Y,...)    The coalesce() function returns a copy of its first
>> non-NULL argument, or NULL if all arguments are NULL. Coalesce() must
>> be at least 2 arguments. Coalesce() does use short-circuit
>> evaluation."
>
> That's a poor description because it relies on the reader knowing what 
> 'short-circuit' means.  A better one would be something like

As the saying goes, brevity is the soul of wit:)  My theory is that if
someone does not know what short-circuit evaluation means, they can
look it up and will very quickly end up at Wikipedia that has a
complete explaination of it.  They might even learn that they can use
it in other languages in which they use.  It also educates them to a
common term in the programming world which well help them communicate,
in general.

With all that said, I am happy with your more verbose version, too:)
I am simply all for improve the documentation:)

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

Re: [sqlite] Does coalesce terminate early?

[Simon Slavin]

On 15 Sep 2011, at 5:00pm, Sam Carleton wrote:

> I don't mean to be difficult, but I simply don't get any indication of
> how exactly COALESCE actually functions from this description:
> 
> "coalesce(X,Y,...)The coalesce() function returns a copy of its first
> non-NULL argument, or NULL if all arguments are NULL. Coalesce() must
> be at least 2 arguments. "
> 
> I get there has to be at least two arguments, but no where do I see
> where it states it only executes an argument if all the previous one
> fail.

Fair point.

> Now it would be crystal clear if it said something like:
> 
> "coalesce(X,Y,...)The coalesce() function returns a copy of its first
> non-NULL argument, or NULL if all arguments are NULL. Coalesce() must
> be at least 2 arguments. Coalesce() does use short-circuit
> evaluation."

That's a poor description because it relies on the reader knowing what 
'short-circuit' means.  A better one would be something like

The coalesce() function evaluates its first argument.  If the value returns is 
not NULL, that value is returned.  Otherwise it checks its next argument for 
NULL, and so on through the list in sequence.  If it runs out of arguments it 
returns NULL.  Versions before 3.6.21 evaluated all arguments first, then 
inspected the values returned.

However perhaps this suggests that you can't depend on details and should 
depend only on the value returned, not how it was arrived at.

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

Re: [sqlite] Does coalesce terminate early?

[Puneet Kishor]

On Sep 15, 2011, at 11:00 AM, Sam Carleton wrote:

> On Thu, Sep 15, 2011 at 10:05 AM, Simon Slavin  wrote:
>> 
>> Documentation for COALESCE is here:
>> 
>> http://www.sqlite.org/lang_corefunc.html
>> 
>> It does not say whether it does short-circuit evaluation but the description 
>> does imply testing one by one, rather than evaluating all the conditions 
>> first.
> 
> Simon,
> 
> I don't mean to be difficult, but I simply don't get any indication of
> how exactly COALESCE actually functions from this description:
> 
> "coalesce(X,Y,...)The coalesce() function returns a copy of its first
> non-NULL argument, or NULL if all arguments are NULL. Coalesce() must
> be at least 2 arguments. "


While your suggested documentation won't harm, and will likely help, actually 
the above does suggest to me a short-circuit-ish kind of logic from the 
assertion that "The coalesce() function returns a copy of its first non-NULL 
argument."

On the other hand, yeah, I am all for potentially redundant documentation if it 
can help.

> 
> I get there has to be at least two arguments, but no where do I see
> where it states it only executes an argument if all the previous one
> fail.  Now it would be crystal clear if it said something like:
> 
> "coalesce(X,Y,...)The coalesce() function returns a copy of its first
> non-NULL argument, or NULL if all arguments are NULL. Coalesce() must
> be at least 2 arguments. Coalesce() does use short-circuit
> evaluation."
> 
> Sam
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] sqlite3 python foreign key

[Simon Slavin]

On 15 Sep 2011, at 4:36pm, Fabio Spadaro wrote:

> i just installed python 2.7.2 and the value returned by "PRAGMA
> foreign_keys" is [(0,)]; Later I will try with the tables.

Can you tell whether your python installation process is installing a new 
version of whatever sqlite API you're using ?  Depending on how it's packaged 
it might be part of it or it might be separate.

Please do this entire sequence:

Make a connection to the database.
Issue "PRAGMA foreign_keys" and look at the table returned.
Issue "PRAGMA foreign_keys = OFF".
Issue "PRAGMA foreign_keys" and look at the table returned.
Issue "PRAGMA foreign_keys = ON".
Issue "PRAGMA foreign_keys" and look at the table returned.

We can tell a lot by looking at the results, including whether your version was 
compiled without foreign key support.

Note that foreign key support is always initially off.  Currently you are 
expected to do "PRAGMA foreign_keys = ON" each time you open a database even if 
you have previously used foreign keys with it.

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

Re: [sqlite] Does coalesce terminate early?

[Sam Carleton]

On Thu, Sep 15, 2011 at 10:05 AM, Simon Slavin  wrote:
>
> Documentation for COALESCE is here:
>
> http://www.sqlite.org/lang_corefunc.html
>
> It does not say whether it does short-circuit evaluation but the description 
> does imply testing one by one, rather than evaluating all the conditions 
> first.

Simon,

I don't mean to be difficult, but I simply don't get any indication of
how exactly COALESCE actually functions from this description:

"coalesce(X,Y,...)  The coalesce() function returns a copy of its first
non-NULL argument, or NULL if all arguments are NULL. Coalesce() must
be at least 2 arguments. "

I get there has to be at least two arguments, but no where do I see
where it states it only executes an argument if all the previous one
fail.  Now it would be crystal clear if it said something like:

"coalesce(X,Y,...)  The coalesce() function returns a copy of its first
non-NULL argument, or NULL if all arguments are NULL. Coalesce() must
be at least 2 arguments. Coalesce() does use short-circuit
evaluation."

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

Re: [sqlite] sqlite3 python foreign key

[Fabio Spadaro]

Hi

2011/9/15 Simon Slavin 

>
> On 15 Sep 2011, at 9:12am, Fabio Spadaro wrote:
>
> >> So you get no error from the INSERT command, and you can also find the
> new
> > row using > SELECT ?
> > No error for select
>
> If the SELECT command does not show the row then the INSERT did not work.
>
> >> Make a connection to the database.
> >> ssue "PRAGMA foreign_keys" and look at the value returned.
> >> Issue "PRAGMA foreign_keys = OFF".
> >> Issue "PRAGMA foreign_keys" and look at the value returned.
> >> Issue "PRAGMA foreign_keys = ON".
> >> Issue "PRAGMA foreign_keys" and look at the value returned.
> > all [ ]
>
> If you get blank responses from all of those then the version of SQLite
> you're using does not support foreign keys.  But you reported your version
> number in your earlier post and that version of SQLite should support
> foreign keys.  I don't not understand this.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

i just installed python 2.7.2 and the value returned by "PRAGMA
foreign_keys" is [(0,)]; Later I will try with the tables.

-- 
Fabio Spadaro
www.fabiospadaro.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Verified crash with SQLite 3.7.8 and 3.7.7.1

[Richard Hipp]

On Thu, Sep 15, 2011 at 10:21 AM, Filip Navara wrote:

> Hi,
>
> there's a bug report filled at
>
> http://system.data.sqlite.org/index.html/info/ce53939214b563cb49ad2375f7c2cb365907898d
> for System.Data.SQLite, but the underlying issue is reproducible with
> the same database on SQLite 3.7.7.1 and my recent builds of SQLite
> 3.7.8. Both crash with access violation. "pragma integrity_check;"
> returns ok for the database.
>
> The database file is linked in the ticket and the query is
>

I didn't see a reference to the database file in the ticket.  Can you post a
link to the database here?  Or send me the database via private email?

I did see the schema.  When I prepended the schema to the query below, it
worked OK for me.  Maybe it needs data too?


>
> SELECT invItemID, storageLocationID, responsiblePersonID, deviceID,
> costCenterID, COALESCE(changeSuggCount, 0) AS changeSuggCount,
> containerInvItemID
> FROM InvItem
> LEFT OUTER JOIN
> (
>  SELECT iicsID, invItemID AS iiID, COUNT(*) AS changeSuggCount FROM
> InvItemChangeSuggestion
>  GROUP BY iiID
> ) AS ChangeSugg ON ChangeSugg.iiID = InvItem.invItemID
> LEFT OUTER JOIN
> (
>  SELECT iicsID AS iicsID2, propID, val FROM
> InvItemPropValueChangeSuggestion
>  WHERE (propID = -4)
>  AND (val = 'AB123987')
>  ORDER BY apprVersion DESC
>  LIMIT 1
> ) AS PropValChangeSugg ON PropValChangeSugg.iicsID2 = ChangeSugg.iicsID
> WHERE
> (rowStatus = 1 AND (val = 'AB123987'))
> OR ((invVersionID = 1)
> AND (rowStatus = 1)
> AND invItemID IN
> (
>  -- ha eredetileg illeszkedik a filter-re
>  SELECT invItemID FROM InvItem
>  WHERE (barCode = 'AB123987')
>  AND (invVersionID = 1)
>  AND (rowStatus = 1)
>
>  UNION
>
>  -- ha a m«¯dos«ìt«âsok ut«ân a(z) barCode illeszkedik a filter-re
>  SELECT invItemID FROM
>  (
>SELECT invItemID, val FROM
>(
>  SELECT invItemID, propID, val
>  FROM InvItemChangeSuggestion, InvItemPropValueChangeSuggestion
>  WHERE InvItemChangeSuggestion.iicsID =
> InvItemPropValueChangeSuggestion.iicsID
>  AND propID = -4
>  AND apprVersion <> 0
>  ORDER BY registerTime ASC
>)
>GROUP BY invitemID, propID
>  )
>  WHERE (val = 'AB123987')
>
>  EXCEPT
>
>  -- a(z) barCode eredetileg illeszkedett, de a m«¯dos«ìt«âsok ut«ân
> m«âr nem illeszkedik a filter-re
>  SELECT invItemID FROM
>  (
>SELECT invItemID, val FROM
>(
>  SELECT invItemID, propID, val
>  FROM InvItemChangeSuggestion, InvItemPropValueChangeSuggestion
>  WHERE InvItemChangeSuggestion.iicsID =
> InvItemPropValueChangeSuggestion.iicsID
>  AND propID = -4
>  AND apprVersion <> 0
>  ORDER BY registerTime ASC
>)
>GROUP BY invitemID, propID
>  )
>  WHERE (val <> 'AB123987')
> ));
>
> Best regards,
> Filip Navara
> ___
> 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

[sqlite] Verified crash with SQLite 3.7.8 and 3.7.7.1

[Filip Navara]

Hi,

there's a bug report filled at
http://system.data.sqlite.org/index.html/info/ce53939214b563cb49ad2375f7c2cb365907898d
for System.Data.SQLite, but the underlying issue is reproducible with
the same database on SQLite 3.7.7.1 and my recent builds of SQLite
3.7.8. Both crash with access violation. "pragma integrity_check;"
returns ok for the database.

The database file is linked in the ticket and the query is

SELECT invItemID, storageLocationID, responsiblePersonID, deviceID,
costCenterID, COALESCE(changeSuggCount, 0) AS changeSuggCount,
containerInvItemID
FROM InvItem
LEFT OUTER JOIN
(
  SELECT iicsID, invItemID AS iiID, COUNT(*) AS changeSuggCount FROM
InvItemChangeSuggestion
  GROUP BY iiID
) AS ChangeSugg ON ChangeSugg.iiID = InvItem.invItemID
LEFT OUTER JOIN
(
  SELECT iicsID AS iicsID2, propID, val FROM InvItemPropValueChangeSuggestion
  WHERE (propID = -4)
  AND (val = 'AB123987')
  ORDER BY apprVersion DESC
  LIMIT 1
) AS PropValChangeSugg ON PropValChangeSugg.iicsID2 = ChangeSugg.iicsID
WHERE
(rowStatus = 1 AND (val = 'AB123987'))
OR ((invVersionID = 1)
AND (rowStatus = 1)
AND invItemID IN
(
  -- ha eredetileg illeszkedik a filter-re
  SELECT invItemID FROM InvItem
  WHERE (barCode = 'AB123987')
  AND (invVersionID = 1)
  AND (rowStatus = 1)

  UNION

  -- ha a m«¯dos«ìt«âsok ut«ân a(z) barCode illeszkedik a filter-re
  SELECT invItemID FROM
  (
SELECT invItemID, val FROM
(
  SELECT invItemID, propID, val
  FROM InvItemChangeSuggestion, InvItemPropValueChangeSuggestion
  WHERE InvItemChangeSuggestion.iicsID =
InvItemPropValueChangeSuggestion.iicsID
  AND propID = -4
  AND apprVersion <> 0
  ORDER BY registerTime ASC
)
GROUP BY invitemID, propID
  )
  WHERE (val = 'AB123987')

  EXCEPT

  -- a(z) barCode eredetileg illeszkedett, de a m«¯dos«ìt«âsok ut«ân
m«âr nem illeszkedik a filter-re
  SELECT invItemID FROM
  (
SELECT invItemID, val FROM
(
  SELECT invItemID, propID, val
  FROM InvItemChangeSuggestion, InvItemPropValueChangeSuggestion
  WHERE InvItemChangeSuggestion.iicsID =
InvItemPropValueChangeSuggestion.iicsID
  AND propID = -4
  AND apprVersion <> 0
  ORDER BY registerTime ASC
)
GROUP BY invitemID, propID
  )
  WHERE (val <> 'AB123987')
));

Best regards,
Filip Navara
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does coalesce terminate early?

[Simon Slavin]

On 15 Sep 2011, at 2:57pm, Alexey Pechnikov wrote:

> Richard, is the future documented anywhere?

Heh.  I assume you meant to type 'feature'.

Documentation for COALESCE is here:

http://www.sqlite.org/lang_corefunc.html

It does not say whether it does short-circuit evaluation but the description 
does imply testing one by one, rather than evaluating all the conditions first.

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

Re: [sqlite] Does coalesce terminate early?

[Alexey Pechnikov]

Richard, is the future documented anywhere?

2011/9/15 Richard Hipp :
> On Thu, Sep 15, 2011 at 8:08 AM, Igor Tandetnik  wrote:
>
>> Richard Hipp  wrote:
>> > On Wed, Sep 14, 2011 at 9:03 PM, Sam Carleton <
>> scarle...@miltonstreet.com>wrote:
>> >
>> >> Forgive me, fore I have forgotten the term used to describe the behavior
>> if
>> >> a C if statement where it stops executing on the first false statement,
>> >> but...  Does coalesce do that?
>> >>
>> >
>> > "Short-circuit evaluation" is the usual term applied to this kind of
>> thing,
>> > and yes, COALESCE() does short-circuit evaluation.  If you say
>> > "coalesce(A,B)" and A is not NULL than B is never evaluated, which can be
>> a
>> > significant performance win if, for example, B is a complex subquery.
>>
>> When did this start, with what SQLite version?
>
>
> 3.6.21 - December 2009
>
>
>> I must admit I'm somewhat behind (using 3.6.X for some X I don't recall at
>> the moment), but in the version I use, in expression coalesce(someField,
>> customFunction()) I definitely see customFunction() called even when
>> someField is not null.
>> --
>> Igor Tandetnik
>>
>> ___
>> 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
>



-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does coalesce terminate early?

[Sam Carleton]

On Thu, Sep 15, 2011 at 6:38 AM, Richard Hipp  wrote:

> On Wed, Sep 14, 2011 at 9:03 PM, Sam Carleton  >wrote:
>
> > Forgive me, fore I have forgotten the term used to describe the behavior
> if
> > a C if statement where it stops executing on the first false statement,
> > but...  Does coalesce do that?
> >
>
> "Short-circuit evaluation" is the usual term applied to this kind of thing,
> and yes, COALESCE() does short-circuit evaluation.  If you say
> "coalesce(A,B)" and A is not NULL than B is never evaluated, which can be a
> significant performance win if, for example, B is a complex subquery.
>

 Richard,

Thank you!  I did look on the web site to see if it said anything about
whether or not coalesce implemented short-circuit evaluations, but it did
not say one way or another.  How would I go about suggesting that change on
the web site?

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

Re: [sqlite] How to store and retrieve list of images from sqlite in android

[Simon Slavin]

On 15 Sep 2011, at 1:35pm, Mayuri wrote:

> As a BLOB.
> 
> we are able to see these images in SQLite Expert. But those images are not
> visible in emulator.

SQLite Expert is doing its own magic in order to interpret the contents of the 
BLOB field as an image.  This is not something built into the Android platform 
as a whole.  I think you have to write your own code to retrieve the contents 
of the BLOB field and present the data as an image.

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

Re: [sqlite] UPDATE of field deletes record

[Jean-Christophe Deschamps]

with an existing record, and a fieldname that is in a column that has 
a UNIQUE INDEX, if I do sqlite_exec("UPDATE database SET 
fieldname="_escape("get calculator batteries")&" WHERE 
fieldname=sqlite_escape("get hp50g calc cells")&";"
this code actually deletes the record.  it should not.  I don't ever 
remember seeing this happen in any database like pgsql or mysql.  I 
can test again to see for sure.


the test code can be seen at 
http://www.autoitscript.com/trac/autoit/ticket/2012


I used escape on the strings.  existing examples do not put single 
quotes on the strings.  if single quotes are required to mske strings 
work as I think they do, please tell me.  I am not getting errors.


Could you please post your question on the AutoIt help forum?  It's 
there for a reason.

BTW yes you're getting errors but you don't test for them.
More on the AutoIt help forum rather than on this list.

--
j...@antichoc.net  


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

Re: [sqlite] UPDATE of field deletes record

[Black, Michael (IS)]

May I suggest you print out the SQL that you are building in your script for 
every statement?  I assume you can log it or display it to screen somehow? Then 
you'll see what your problem is and can try and execute those statements in the 
shell...plus you can show us.

And your error checking is not working because your statements have errors, as 
Igor pointed out, but you claims you get no errors.  Plus, you don't check the 
$err return from your statements.

Your create statement errors out but apparently your "if $SQLITE_OK <> ..." 
isn't working.  That's not SQlite's problem.

sqlite> CREATE TABLE IF NOT EXISTS todolist (entry_id INTEGER CONSTRAINT 
entry_id_c PRIMARY KEY AUTOINCREMENT, shortdesc TEXT NOT NULL DEFAULT , 
longdesc TEXT N
OT NULL DEFAULT , priority INTEGER(8) NOT NULL DEFAULT '0', state INTEGER(8) 
NOT NULL DEFAULT '0', startdt VARCHAR(25) NOT NULL DEFAULT , duedt VARCHAR(25) 
NOT
NULL DEFAULT , completeddt VARCHAR(25) NOT NULL DEFAULT;
Error: near ",": syntax error


Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Jim Michaels [j...@jimscomputerrepairandwebdesign.com]
Sent: Wednesday, September 14, 2011 6:28 PM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] UPDATE of field deletes record


with an existing record, and a fieldname that is in a column that has a
UNIQUE INDEX, if I do sqlite_exec("UPDATE database SET
fieldname="_escape("get calculator batteries")&" WHERE
fieldname=sqlite_escape("get hp50g calc cells")&";"
this code actually deletes the record.  it should not.  I don't ever
remember seeing this happen in any database like pgsql or mysql.  I can
test again to see for sure.


the test code can be seen at
http://www.autoitscript.com/trac/autoit/ticket/2012

I used escape on the strings.  existing examples do not put single
quotes on the strings.  if single quotes are required to mske strings
work as I think they do, please tell me.  I am not getting errors.

--
Jim Michaels
j...@jimscomputerrepairandwebdesign.com
http://JimsComputerRepairandWebDesign.com
http://JesusnJim.com

___
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] UPDATE of field deletes record

[Igor Tandetnik]

Jim Michaels 
 
wrote:
> the test code can be seen at
> http://www.autoitscript.com/trac/autoit/ticket/2012

Here's a CREATE TABLE statement from this sample:

CREATE TABLE IF NOT EXISTS todolist (entry_id INTEGER CONSTRAINT entry_id_c 
PRIMARY KEY AUTOINCREMENT, shortdesc TEXT NOT NULL DEFAULT , longdesc TEXT NOT 
NULL DEFAULT , priority INTEGER(8) NOT NULL DEFAULT '0', state INTEGER(8) NOT 
NULL DEFAULT '0', startdt VARCHAR(25) NOT NULL DEFAULT , duedt VARCHAR(25) NOT 
NULL DEFAULT , completeddt VARCHAR(25) NOT NULL DEFAULT );

It's invalid - DEFAULT keyword must be followed by an expression. Let's assume 
you meant DEFAULT '' .

Your example creates a table named "todolist", then purports to create a bunch 
of indexes on a non-existent table "events" (with no error checking). All these 
statements fail.

Anyway, here's a transcript of a SQLite session that attempts to reproduce your 
example to the extent possible:

SQLite version 3.7.2
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE IF NOT EXISTS todolist (entry_id INTEGER CONSTRAINT entry_i
d_c PRIMARY KEY AUTOINCREMENT, shortdesc TEXT NOT NULL DEFAULT '', longdesc TEXT
 NOT NULL DEFAULT '', priority INTEGER(8) NOT NULL DEFAULT '0', state INTEGER(8)
 NOT NULL DEFAULT '0', startdt VARCHAR(25) NOT NULL DEFAULT '', duedt VARCHAR(25
) NOT NULL DEFAULT '', completeddt VARCHAR(25) NOT NULL DEFAULT '');
sqlite>
sqlite> INSERT INTO todolist(priority,state,shortdesc,startdt,completeddt,duedt,
longdesc) VALUES('0', '0', 'get hp50g calculator batteries', '', '', '', '');
sqlite>
sqlite> UPDATE todolist SET shortdesc='calc batteries' WHERE shortdesc='get hp50
g calculator batteries';
sqlite>
sqlite> SELECT DISTINCT shortdesc FROM todolist ORDER BY shortdesc ASC;
calc batteries
sqlite>

Looks OK to me.
-- 
Igor Tandetnik

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

Re: [sqlite] How to store and retrieve list of images from sqlite in android

[Mayuri]

As a BLOB.

we are able to see these images in SQLite Expert. But those images are not
visible in emulator.

 
-- 
View this message in context: 
http://old.nabble.com/How-to-store-and-retrieve-list-of-images-from-sqlite-in-android-tp32469610p32471232.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] UPDATE of field deletes record

[Simon Slavin]

On 15 Sep 2011, at 12:28am, Jim Michaels wrote:

> with an existing record, and a fieldname that is in a column that has a 
> UNIQUE INDEX, if I do sqlite_exec("UPDATE database SET 
> fieldname="_escape("get calculator batteries")&" WHERE 
> fieldname=sqlite_escape("get hp50g calc cells")&";"
> this code actually deletes the record.  it should not.

An understatement worthy of an Englishman.

What API are you using ?  The function 'sqlite_escape' is not part of SQLite.

SQLite uses single quotes for quoting strings, but I'm not sure you're actually 
quoting strings in the above.

Can you retype your command again ?  You can't have got it right because the 
brackets don't match.

Also, can you test the command using the sqlite3 shell tool, show us exactly 
what you type and tell us what the result is ?

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

Re: [sqlite] UPDATE of field deletes record

[Stephan Beal]

On Thu, Sep 15, 2011 at 1:28 AM, Jim Michaels <
j...@jimscomputerrepairandwebdesign.com> wrote:

>  sqlite_exec("UPDATE database SET fieldname="_escape("get calculator
> batteries")&" WHERE fieldname=sqlite_escape("get hp50g calc cells")&";"
>

That code can't possibly evaluate to valid SQL, i think: there's a missing
"& pair before the 2nd sqlite_escape() call.

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

Re: [sqlite] About extension-functions.c

[Stephan Beal]

On Thu, Sep 15, 2011 at 3:50 AM, Freddy López wrote:

> extension-functions.c:206:17: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or
> ‘__attribute__’ before ‘xtra_utf8_bytes’
>

When i've seen this in my own code it's normally been due to compiling C99
code in C89 mode.

extension-functions.c: In function ‘sqlite3ReadUtf8’:
> extension-functions.c:279:3: error: ‘xtra_utf8_bytes’ undeclared (first use
> in this function)
> extension-functions.c:279:3: note: each undeclared identifier is reported
> only once for each function it appears in
> extension-functions.c: In function ‘squareFunc’:
> extension-functions.c:477:3: error: ‘i64’ undeclared (first use in this
> function)


It looks to me like some required header is missing - i64 is not a standard
type name extension-functions.c might be expecting it to be pulled from some
#include. Or maybe its relying on an old sqlite3 API (which possibly had an
i64 typedef). But i'm just guessing.

:-?

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

Re: [sqlite] Does coalesce terminate early?

[Richard Hipp]

On Thu, Sep 15, 2011 at 8:08 AM, Igor Tandetnik  wrote:

> Richard Hipp  wrote:
> > On Wed, Sep 14, 2011 at 9:03 PM, Sam Carleton <
> scarle...@miltonstreet.com>wrote:
> >
> >> Forgive me, fore I have forgotten the term used to describe the behavior
> if
> >> a C if statement where it stops executing on the first false statement,
> >> but...  Does coalesce do that?
> >>
> >
> > "Short-circuit evaluation" is the usual term applied to this kind of
> thing,
> > and yes, COALESCE() does short-circuit evaluation.  If you say
> > "coalesce(A,B)" and A is not NULL than B is never evaluated, which can be
> a
> > significant performance win if, for example, B is a complex subquery.
>
> When did this start, with what SQLite version?


3.6.21 - December 2009


> I must admit I'm somewhat behind (using 3.6.X for some X I don't recall at
> the moment), but in the version I use, in expression coalesce(someField,
> customFunction()) I definitely see customFunction() called even when
> someField is not null.
> --
> Igor Tandetnik
>
> ___
> 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

[sqlite] UPDATE of field deletes record

[Jim Michaels]

with an existing record, and a fieldname that is in a column that has a 
UNIQUE INDEX, if I do sqlite_exec("UPDATE database SET 
fieldname="_escape("get calculator batteries")&" WHERE 
fieldname=sqlite_escape("get hp50g calc cells")&";"
this code actually deletes the record.  it should not.  I don't ever 
remember seeing this happen in any database like pgsql or mysql.  I can 
test again to see for sure.



the test code can be seen at 
http://www.autoitscript.com/trac/autoit/ticket/2012


I used escape on the strings.  existing examples do not put single 
quotes on the strings.  if single quotes are required to mske strings 
work as I think they do, please tell me.  I am not getting errors.


--
Jim Michaels
j...@jimscomputerrepairandwebdesign.com
http://JimsComputerRepairandWebDesign.com
http://JesusnJim.com

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

Re: [sqlite] Does coalesce terminate early?

[Igor Tandetnik]

Richard Hipp  wrote:
> On Wed, Sep 14, 2011 at 9:03 PM, Sam Carleton 
> wrote:
> 
>> Forgive me, fore I have forgotten the term used to describe the behavior if
>> a C if statement where it stops executing on the first false statement,
>> but...  Does coalesce do that?
>> 
> 
> "Short-circuit evaluation" is the usual term applied to this kind of thing,
> and yes, COALESCE() does short-circuit evaluation.  If you say
> "coalesce(A,B)" and A is not NULL than B is never evaluated, which can be a
> significant performance win if, for example, B is a complex subquery.

When did this start, with what SQLite version? I must admit I'm somewhat behind 
(using 3.6.X for some X I don't recall at the moment), but in the version I 
use, in expression coalesce(someField, customFunction()) I definitely see 
customFunction() called even when someField is not null.
-- 
Igor Tandetnik

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

Re: [sqlite] sqlite3 python foreign key

[Simon Slavin]

On 15 Sep 2011, at 9:12am, Fabio Spadaro wrote:

>> So you get no error from the INSERT command, and you can also find the new
> row using > SELECT ?
> No error for select

If the SELECT command does not show the row then the INSERT did not work.

>> Make a connection to the database.
>> ssue "PRAGMA foreign_keys" and look at the value returned.
>> Issue "PRAGMA foreign_keys = OFF".
>> Issue "PRAGMA foreign_keys" and look at the value returned.
>> Issue "PRAGMA foreign_keys = ON".
>> Issue "PRAGMA foreign_keys" and look at the value returned.
> all [ ]

If you get blank responses from all of those then the version of SQLite you're 
using does not support foreign keys.  But you reported your version number in 
your earlier post and that version of SQLite should support foreign keys.  I 
don't not understand this.

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

Re: [sqlite] How to store and retrieve list of images from sqlite in android

[Simon Slavin]

On 15 Sep 2011, at 8:38am, Mayuri wrote:

>  I did one application store and retrieve only one image from sqlite,now I
> am trying to store list of images in sqlite it's done but I unable to
> retrieve those images on emulator.

How did you store your image ?  As a BLOB ?  Or as a pointer to a file ?  Or 
some other way ?

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

Re: [sqlite] Does coalesce terminate early?

[Richard Hipp]

On Wed, Sep 14, 2011 at 9:03 PM, Sam Carleton wrote:

> Forgive me, fore I have forgotten the term used to describe the behavior if
> a C if statement where it stops executing on the first false statement,
> but...  Does coalesce do that?
>

"Short-circuit evaluation" is the usual term applied to this kind of thing,
and yes, COALESCE() does short-circuit evaluation.  If you say
"coalesce(A,B)" and A is not NULL than B is never evaluated, which can be a
significant performance win if, for example, B is a complex subquery.


>
> I have to put together a query that has a coalesce such that if the row
> from
> the table is null, it then does a non-trival query to calculate the value
> for the row:  It is for an invoice total and will only be used while the
> user is building the invoice.  Once the invoice is finalized, the values
> will be placed into the invoice row.  The reason I need this logic is
> because the prices of invoice items can change on the fly, each time the
> user displays an invoice that is being built, it needs to show the current
> prices/totals, thus it looks like this:
>
> select
>  coalesce( sub_total, (select round(sum( (select pli.PRICE from
> PRICE_LIST_ITEM pli where pli.EVENT_PRICE_LIST_ID = (select ITEMVALUE from
> DBLOOKUP where CATEGORY = 'SalesCenter' and  ITEMNAME = 'PriceListId') AND
> pli.PRODUCT_ID = PRODUCT_ID) * QTY) from EventDB.INVOICE_ITEM  where
> INVOICE_ID = @invoiceId)) as sub_total,
>  coalesce( tax, (select round(sum( (select pli.PRICE from PRICE_LIST_ITEM
> pli where pli.EVENT_PRICE_LIST_ID = (select ITEMVALUE from DBLOOKUP where
> CATEGORY = 'SalesCenter' and  ITEMNAME = 'PriceListId') AND pli.PRODUCT_ID
> =
> PRODUCT_ID) * QTY) / (1 + (@tax / 100)) - .005, 2) * ( @tax / 100) + .005,
> 2) from EventDB.INVOICE_ITEM  where INVOICE_ID = @invoiceId)) as tax,
>  coalesce( total, (select round(sum( (select pli.PRICE from PRICE_LIST_ITEM
> pli where pli.EVENT_PRICE_LIST_ID = (select ITEMVALUE from DBLOOKUP where
> CATEGORY = 'SalesCenter' and  ITEMNAME = 'PriceListId') AND pli.PRODUCT_ID
> =
> PRODUCT_ID) * QTY) / (1 + (@tax / 100)) - .005, 2)  from
> EventDB.INVOICE_ITEM  where INVOICE_ID = @invoiceId)) as total
> where
>  INVOICE_ID = @invoiceId
>
> I tend to favor creating larger select statements and letting the DB do the
> work rather than adding lots of conditional code in my C code, so is this a
> good time to break it into two statements or should I be fine with the
> above
> code?
>
> Sam
> ___
> 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] sqlite3 python foreign key

[Fabio Spadaro]

Hi,
> So you get no error from the INSERT command, and you can also find the new
row using > SELECT ?
No error for select

> Make a connection to the database.
> ssue "PRAGMA foreign_keys" and look at the value returned.
> Issue "PRAGMA foreign_keys = OFF".
> Issue "PRAGMA foreign_keys" and look at the value returned.
> Issue "PRAGMA foreign_keys = ON".
> Issue "PRAGMA foreign_keys" and look at the value returned.
all [ ]
-- 
Fabio Spadaro
www.fabiospadaro.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to store and retrieve list of images from sqlite in android

[Mayuri]

Hi All,
  I did one application store and retrieve only one image from sqlite,now I
am trying to store list of images in sqlite it's done but I unable to
retrieve those images on emulator.
please can any one help on this.
  
  Thank u in advance.
-- 
View this message in context: 
http://old.nabble.com/How-to-store-and-retrieve-list-of-images-from-sqlite-in-android-tp32469612p32469612.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] How to store and retrieve list of images from sqlite in android

[Mayuri]

Hi All,
  I did one application store and retrieve only one image from sqlite,now I
am trying to store list of images in sqlite it's done but I unable to
retrieve those images on emulator.
please can any one help on this.
  
  Thank u in advance.
-- 
View this message in context: 
http://old.nabble.com/How-to-store-and-retrieve-list-of-images-from-sqlite-in-android-tp32469610p32469610.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] How to store and retrieve list of images from sqlite in android

[Mayuri]

-- 
View this message in context: 
http://old.nabble.com/How-to-store-and-retrieve-list-of-images-from-sqlite-in-android-tp32469558p32469558.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