Re: [sqlite] network access problem

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 05/26/2010 08:45 PM, Art wrote:
> There should be no reason that the openv2 statement with read_only specified 
> should  fail under these circumstances,  

Just because you are opening the database read only does not mean that other
connections in the same or different processes are.  Consequently it is
still necessary to do locking.  Additionally if a journal exists then it may
need to be rolled back which requires writing to the database and deleting
the journal, the latter also requiring write permissions to the directory.

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

iEYEARECAAYFAkv+BncACgkQmOOfHg372QQxOwCdF8V/yoEzqpZ/djmnCwvKKXYy
Z0kAoOVokPwsZ8w41igmMMqRktU6zzep
=nMET
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] network access problem

[Art]

Thanks for the suggestions:


Sqlite Manager also fails.  

This is a permission issue with the sql open api.  This is a "Shared Folder" 
under VirtualBox with "read only permission".  If the folder is tagged as full 
access then sql open works.  

There should be no reason that the openv2 statement with read_only specified 
should  fail under these circumstances,  

If this was a problem with virtualbox then you see their message board light up 
with complaints.  Its not, no other apps have problems accessing files through 
vb shared folders!

Art.



Running in sqlite application in Virtual Box, attempt to open a database with 
sqlite from a shared network folder \\Vboxsvr\testdata  however the open16 and 
openv2 (with read only)  both fail --- rc = 14.  App opens file if moved 
locally to hard drive.

using latest version of sqlite3 3.6.23.1 

this seems to be a bug in the open code of sqlite when accessing a file across 
a network.

Thank you,
Art Zerger
azer...@yahoo.com
===
From: "Griggs, Donald" 
Hi Art,
I just succeeded in opening a db with a UNC path, under Windows XP Pro, using 
the same version (3.6.23.1) with the command-line utility -- but I do NOT use 
virtual box.
Does the command-line utility fail for you under virtual box?  
Might you attempt it without virtualbox?  

Donald
--
From: Kees Nuyt 
A VBox shared folder is not exactly the best example of
"sharing over a network". The purpose of shared folders is
to copy files to and from virtual machines in an easy way.
I think the locking primitives of VBox shared folders are
not 100%.  You could try again with NFSv4.
-- 
  (  Kees Nuyt
  )
c[_]
--
From: Jean-Christophe Deschamps 
I don't believe so: I use several bases which I open with MS short UNC 
syntax 200 times a day without any problem ever (using vanilla 3.6.23.1).

Your issue is elsewhere.  Make sure the account you use has enough 
rights to the destination folder and check you can access it with, for 
instance, a innocent application like a hex editor or a third-party 
SQLite manager.
===


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

Re: [sqlite] FTS1 or FTS2

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 05/26/2010 07:22 PM, Sam Carleton wrote:
> I did this search:
> 
> http://www.google.com/search?q=sqlite+text+search

Thanks.  I have updated the relevant wiki pages to say the information is
old and to point to the current FTS3 implementation.

> maybe things should be configured so that
> CVS isn't crawled by search engines

It isn't CVS but rather cvstrac which is a web interface using CVS as a back
end and providing easy viewing of the source, changes, a wiki, bug tracking etc.

SQLite has since moved to a DVCS named fossil which has that stuff (wiki,
bug tracking, web interface etc) as a native part.  See
http://www.fossil-scm.org/

However the wiki for SQLite remains the old cvstrac based one.  There is
unfortunately a fair amount of cruft accumulated in it as you fell victim
to, especially documentation that is now part of the core SQLite.

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

iEYEARECAAYFAkv95aEACgkQmOOfHg372QSH6gCgh6nQMN1zfArIJ6PP+EyBAHJ0
goEAoNtKK8/IRVH3ENBOdj/RhKKhOcgu
=DXMy
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS1 or FTS2

[P Kishor]

On Wed, May 26, 2010 at 9:22 PM, Sam Carleton
 wrote:
> Roger,
>
> I did this search:
>
> http://www.google.com/search?q=sqlite+text+search
>
> The top link points here:
>
> http://www.sqlite.org/cvstrac/wiki?p=FullTextIndex
>
> Thus I learned of FTS1 and FTS2, maybe things should be configured so that
> CVS isn't crawled by search engines ;)


The latest version is FTS3 (higher numbers are later versions). FTS1
and FTS2 are deprecated. FTS3 source is included in the sqlite source
download. Check the build instructions for sqlite. You can set a
compile time flag and build FTS3 into sqlite3.


>
> Sam
>
> On Wed, May 26, 2010 at 2:47 PM, Roger Binns  wrote:
>
>> -BEGIN PGP SIGNED MESSAGE-
>> Hash: SHA1
>>
>> On 05/26/2010 10:24 AM, Sam Carleton wrote:
>> > If I do opt to use FTS, which one should I be using, FTS1 or FTS2?
>> > According to the web site, it should be FTS1, correct?
>>
>> Just as a matter of interest what part of the web site led you to those
>> conclusions?
>>
>> Roger
>> -BEGIN PGP SIGNATURE-
>> Version: GnuPG v1.4.10 (GNU/Linux)
>> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
>>
>> iEYEARECAAYFAkv9bKcACgkQmOOfHg372QTINwCglr7//BHCgOt3vKApNZ8/EVYI
>> rcIAn1pYT57Rb/7zeyYGuEw+xyYVbxVG
>> =Imqe
>> -END PGP SIGNATURE-
>> ___
>> 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
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS1 or FTS2

[Sam Carleton]

Roger,

I did this search:

http://www.google.com/search?q=sqlite+text+search

The top link points here:

http://www.sqlite.org/cvstrac/wiki?p=FullTextIndex

Thus I learned of FTS1 and FTS2, maybe things should be configured so that
CVS isn't crawled by search engines ;)

Sam

On Wed, May 26, 2010 at 2:47 PM, Roger Binns  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 05/26/2010 10:24 AM, Sam Carleton wrote:
> > If I do opt to use FTS, which one should I be using, FTS1 or FTS2?
> > According to the web site, it should be FTS1, correct?
>
> Just as a matter of interest what part of the web site led you to those
> conclusions?
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.10 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
>
> iEYEARECAAYFAkv9bKcACgkQmOOfHg372QTINwCglr7//BHCgOt3vKApNZ8/EVYI
> rcIAn1pYT57Rb/7zeyYGuEw+xyYVbxVG
> =Imqe
> -END PGP SIGNATURE-
> ___
> 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] Novice SQLite user

[Simon Slavin]

On 27 May 2010, at 2:15am, jdee5 wrote:

> hanks for your reply.  I have read through the link you suggested, very
> helpful...if I may ask another question concerning this.  Say on my
> application I have 2 users reading some of the database contents at the same
> time and they both log something in my application at the same time.

By 'log something' I assume you mean they are making changes to the same 
database they are both reading.

> For
> example say they want to both review different customer accounts and add a
> payment to the different customer accounts.  Would there be a delay with
> both of those when using SQLite, if so would it be significant?  

This is impossible to say because it depends on your network setup and how your 
application works, but purely as a guess, one user would see no delay at all 
and the other would see one of less than half a second.

> Can I use SQLite this way have my application stored on the server and allow
> users on a LAN/peer to peer have the ability to open my app and write to it
> at the same time?

SQLite does not care (or even know) where your application is stored.  It does 
care where the file that holds the database is stored (the one you called in 
your '_open' command) and needs to deal correctly with whatever networking (p2p 
is fine) you are using.  I would guess you are intending to write a Windows 
application so you need to be sure that your Windows network resource supports 
file locking correctly.

> does this type of multi user access often corrupt the
> database?

The page I pointed to has a section called something like 'How to corrupt your 
database'.  Don't do those things.  If you do manage to find a bug that hasn't 
been fixed yet (unlikely but not impossible), please tell us because the clever 
people here will pounce on it and fix it.

> If my database does become corrupt how can I repair it.

One common way is to use the sqlite3 command-line program to dump your entire 
database as one big text file full of SQL commands, then to use those commands 
to create another database.  Sometimes corruption can be fixed just by remaking 
your indexes.  It depends what went wrong.

As a responsible supplier you will, of course, be making sure your customer 
knows to take backups of your database occasionally.

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

Re: [sqlite] Novice SQLite user

[jdee5]

P-

The application I sell is quite small and simple embedded db seems easier to
deploy.  My current install takes just a few minutes and so it doesn't seem
like a good idea to have to have my customer have to configure a
client/server database I need something quick and simple.  I tried out MS
SQL express for myself and had to go in and configure TCP setting and other
settings which took quite a while.  My customers are not tech savvy.  My
application is cheap so using MySQL doesn't make sense (i do not make my app
open source so I have a fee if i use MySQL) I am not familiar with PostGres,
do they offer a version that is free to distribute?  Is their configuration
to the DBMS?

Thanks.



P Kishor-3 wrote:
> 
> On Wed, May 26, 2010 at 8:15 PM, jdee5  wrote:
>>
>>
>>
>> Thanks for your reply.  I have read through the link you suggested, very
>> helpful...if I may ask another question concerning this.  Say on my
>> application I have 2 users reading some of the database contents at the
>> same
>> time and they both log something in my application at the same time.  For
>> example say they want to both review different customer accounts and add
>> a
>> payment to the different customer accounts.  Would there be a delay with
>> both of those when using SQLite, if so would it be significant?
>>
>> Can I use SQLite this way have my application stored on the server and
>> allow
>> users on a LAN/peer to peer have the ability to open my app and write to
>> it
>> at the same time?  does this type of multi user access often corrupt the
>> database?  If my database does become corrupt how can I repair it.
>>
>> Sorry for the redudancy in the questions...like I said am new to SQL
>> (used
>> MS SQL a little) and really new to the idea of using an embedded database
>> engine.
> 
> 
> Why do you want to use an "embedded database engine" and want to have
> multiple concurrent users changing the db over a network? If you want
> to users to be able to change stuff at the same time from different
> locations, use a true client/server db such as Postgres or MySQL.
> 
> If you have only a few users who will be changing the data, you could
> create a SQLite-powered web app, but there could be potential
> instances of lockouts. If you want to put your db on a LAN (shared
> disk), and have remote users change it, there is likely a potential
> for db to be corrupted.
> 
> 
> 
>>
>> Thanks in advance for information
>>
>>
>>
>> Simon Slavin-3 wrote:
>>>
>>>
>>> On 26 May 2010, at 10:04pm, jdee5 wrote:
>>>
 I am looking for an SQL engine that is easy to install and sqlite seems
 like
 the right way to go.  I have an application (currently just for network
 use
 on a LAN) and I want to move to using SQL, my application is fairly
 small
 and simple and I like the idea of embedding SQL and using SQLite so my
 end
 user doesn't notice any difference from my current
 application...however,
 more than 1 person may want to access the db at a time does SQLite
 allow
 for
 this?
>>>
>>> Yes.  SQLite supports locking for multi-user and multi-process access.
>>>  It
>>> is unusual in that it locks the entire database file rather than
>>> individual records, but the SQLite calls you make handle multi-user
>>> access
>>> correctly.  You /will/ have to make your application aware of how to
>>> handle the errors which arise if one user keeps the database locked so
>>> long that the other user can't access it.  I recommend you read this
>>> page
>>> (though you don't have to memorise it all):
>>>
>>> 
>>>
 Can it just sequentially record transactions as they are made even if
 made simultaneously... I have search all over and can't come to a clear
 conclusion, I am also very new to using SQL.
>>>
>>> Under most circumstances, even if two programs are entering transactions
>>> at the same time, SQLite will handle this without the programs having to
>>> be aware of it.  However, make sure you handle the SQLITE_BUSY and
>>> SQLITE_LOCKED errors correctly.  I hope someone can recommend a page
>>> which
>>> breaks down how to do this.
>>>
>>> Simon.
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>>
>>
>> --
>> View this message in context:
>> http://old.nabble.com/Novice-SQLite-user-tp28686380p28688425.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
>>
> 
> 
> 
> -- 
> Puneet Kishor http://www.punkish.org
> Carbon Model http://carbonmodel.org
> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
> Nelson Institute, 

Re: [sqlite] Novice SQLite user

[P Kishor]

On Wed, May 26, 2010 at 8:15 PM, jdee5  wrote:
>
>
>
> Thanks for your reply.  I have read through the link you suggested, very
> helpful...if I may ask another question concerning this.  Say on my
> application I have 2 users reading some of the database contents at the same
> time and they both log something in my application at the same time.  For
> example say they want to both review different customer accounts and add a
> payment to the different customer accounts.  Would there be a delay with
> both of those when using SQLite, if so would it be significant?
>
> Can I use SQLite this way have my application stored on the server and allow
> users on a LAN/peer to peer have the ability to open my app and write to it
> at the same time?  does this type of multi user access often corrupt the
> database?  If my database does become corrupt how can I repair it.
>
> Sorry for the redudancy in the questions...like I said am new to SQL (used
> MS SQL a little) and really new to the idea of using an embedded database
> engine.


Why do you want to use an "embedded database engine" and want to have
multiple concurrent users changing the db over a network? If you want
to users to be able to change stuff at the same time from different
locations, use a true client/server db such as Postgres or MySQL.

If you have only a few users who will be changing the data, you could
create a SQLite-powered web app, but there could be potential
instances of lockouts. If you want to put your db on a LAN (shared
disk), and have remote users change it, there is likely a potential
for db to be corrupted.



>
> Thanks in advance for information
>
>
>
> Simon Slavin-3 wrote:
>>
>>
>> On 26 May 2010, at 10:04pm, jdee5 wrote:
>>
>>> I am looking for an SQL engine that is easy to install and sqlite seems
>>> like
>>> the right way to go.  I have an application (currently just for network
>>> use
>>> on a LAN) and I want to move to using SQL, my application is fairly small
>>> and simple and I like the idea of embedding SQL and using SQLite so my
>>> end
>>> user doesn't notice any difference from my current application...however,
>>> more than 1 person may want to access the db at a time does SQLite allow
>>> for
>>> this?
>>
>> Yes.  SQLite supports locking for multi-user and multi-process access.  It
>> is unusual in that it locks the entire database file rather than
>> individual records, but the SQLite calls you make handle multi-user access
>> correctly.  You /will/ have to make your application aware of how to
>> handle the errors which arise if one user keeps the database locked so
>> long that the other user can't access it.  I recommend you read this page
>> (though you don't have to memorise it all):
>>
>> 
>>
>>> Can it just sequentially record transactions as they are made even if
>>> made simultaneously... I have search all over and can't come to a clear
>>> conclusion, I am also very new to using SQL.
>>
>> Under most circumstances, even if two programs are entering transactions
>> at the same time, SQLite will handle this without the programs having to
>> be aware of it.  However, make sure you handle the SQLITE_BUSY and
>> SQLITE_LOCKED errors correctly.  I hope someone can recommend a page which
>> breaks down how to do this.
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>
> --
> View this message in context: 
> http://old.nabble.com/Novice-SQLite-user-tp28686380p28688425.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
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Novice SQLite user

[jdee5]

Thanks for your reply.  I have read through the link you suggested, very
helpful...if I may ask another question concerning this.  Say on my
application I have 2 users reading some of the database contents at the same
time and they both log something in my application at the same time.  For
example say they want to both review different customer accounts and add a
payment to the different customer accounts.  Would there be a delay with
both of those when using SQLite, if so would it be significant?  

Can I use SQLite this way have my application stored on the server and allow
users on a LAN/peer to peer have the ability to open my app and write to it
at the same time?  does this type of multi user access often corrupt the
database?  If my database does become corrupt how can I repair it.

Sorry for the redudancy in the questions...like I said am new to SQL (used
MS SQL a little) and really new to the idea of using an embedded database
engine.

Thanks in advance for information



Simon Slavin-3 wrote:
> 
> 
> On 26 May 2010, at 10:04pm, jdee5 wrote:
> 
>> I am looking for an SQL engine that is easy to install and sqlite seems
>> like
>> the right way to go.  I have an application (currently just for network
>> use
>> on a LAN) and I want to move to using SQL, my application is fairly small
>> and simple and I like the idea of embedding SQL and using SQLite so my
>> end
>> user doesn't notice any difference from my current application...however,
>> more than 1 person may want to access the db at a time does SQLite allow
>> for
>> this?
> 
> Yes.  SQLite supports locking for multi-user and multi-process access.  It
> is unusual in that it locks the entire database file rather than
> individual records, but the SQLite calls you make handle multi-user access
> correctly.  You /will/ have to make your application aware of how to
> handle the errors which arise if one user keeps the database locked so
> long that the other user can't access it.  I recommend you read this page
> (though you don't have to memorise it all):
> 
> 
> 
>> Can it just sequentially record transactions as they are made even if
>> made simultaneously... I have search all over and can't come to a clear
>> conclusion, I am also very new to using SQL.
> 
> Under most circumstances, even if two programs are entering transactions
> at the same time, SQLite will handle this without the programs having to
> be aware of it.  However, make sure you handle the SQLITE_BUSY and
> SQLITE_LOCKED errors correctly.  I hope someone can recommend a page which
> breaks down how to do this.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Novice-SQLite-user-tp28686380p28688425.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] Novice SQLite user

[Simon Slavin]

On 26 May 2010, at 10:04pm, jdee5 wrote:

> I am looking for an SQL engine that is easy to install and sqlite seems like
> the right way to go.  I have an application (currently just for network use
> on a LAN) and I want to move to using SQL, my application is fairly small
> and simple and I like the idea of embedding SQL and using SQLite so my end
> user doesn't notice any difference from my current application...however,
> more than 1 person may want to access the db at a time does SQLite allow for
> this?

Yes.  SQLite supports locking for multi-user and multi-process access.  It is 
unusual in that it locks the entire database file rather than individual 
records, but the SQLite calls you make handle multi-user access correctly.  You 
/will/ have to make your application aware of how to handle the errors which 
arise if one user keeps the database locked so long that the other user can't 
access it.  I recommend you read this page (though you don't have to memorise 
it all):



> Can it just sequentially record transactions as they are made even if
> made simultaneously... I have search all over and can't come to a clear
> conclusion, I am also very new to using SQL.

Under most circumstances, even if two programs are entering transactions at the 
same time, SQLite will handle this without the programs having to be aware of 
it.  However, make sure you handle the SQLITE_BUSY and SQLITE_LOCKED errors 
correctly.  I hope someone can recommend a page which breaks down how to do 
this.

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

[sqlite] Novice SQLite user

[jdee5]

I am looking for an SQL engine that is easy to install and sqlite seems like
the right way to go.  I have an application (currently just for network use
on a LAN) and I want to move to using SQL, my application is fairly small
and simple and I like the idea of embedding SQL and using SQLite so my end
user doesn't notice any difference from my current application...however,
more than 1 person may want to access the db at a time does SQLite allow for
this?  Can it just sequentially record transactions as they are made even if
made simultaneously... I have search all over and can't come to a clear
conclusion, I am also very new to using SQL.
-- 
View this message in context: 
http://old.nabble.com/Novice-SQLite-user-tp28686380p28686380.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] how to search for asterix character?

[Bart Smissaert]

Glob works fine as well with the [*] and is still case-sensitive and
that is how it is intended.
So this works fine:

select field1 from table1 where field1 glob '*FH*[*]'

RBS


On Wed, May 26, 2010 at 8:22 PM, Olaf Schmidt  wrote:
>
> "Bart Smissaert" 
> schrieb im Newsbeitrag
> news:aanlktikivzcbz81hqs28dtptoy8h6hc6nbukesmth...@mail.gmail.com...
>
>> > "...Where SomeColumnContent Like '%someother[*]part%'
>>
>> Thanks Olaf, that works fine.
>> As my customers won't get this I think I might let my
>> code take care of this.
> Perhaps a good idea. ;-)
>
>> How would it work with glob?
> Umm, not a glob expert  don't use it here (yet).
> my first thought would be, to precede the char in question
> with an escape-char (as the backslash)... testing...
>
> No, it apparently works in the same way as my overridden
> like per:
> "...Where SomeColumnContent glob '*someother[*]part*'
>
> But possibly not case-insensitive (not tested, that) -
> and not "unicode-aware" of course.
>
>
> Olaf
>
>
>
> ___
> 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] how to search for asterix character?

[Olaf Schmidt]

"Bart Smissaert" 
schrieb im Newsbeitrag
news:aanlktikivzcbz81hqs28dtptoy8h6hc6nbukesmth...@mail.gmail.com...

> > "...Where SomeColumnContent Like '%someother[*]part%'
>
> Thanks Olaf, that works fine.
> As my customers won't get this I think I might let my
> code take care of this.
Perhaps a good idea. ;-)

> How would it work with glob?
Umm, not a glob expert  don't use it here (yet).
my first thought would be, to precede the char in question
with an escape-char (as the backslash)... testing...

No, it apparently works in the same way as my overridden
like per:
"...Where SomeColumnContent glob '*someother[*]part*'

But possibly not case-insensitive (not tested, that) -
and not "unicode-aware" of course.


Olaf



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

Re: [sqlite] FTS1 or FTS2

[P Kishor]

On Wed, May 26, 2010 at 1:47 PM, Roger Binns  wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 05/26/2010 10:24 AM, Sam Carleton wrote:
>> If I do opt to use FTS, which one should I be using, FTS1 or FTS2?
>> According to the web site, it should be FTS1, correct?
>
> Just as a matter of interest what part of the web site led you to those
> conclusions?


heh, heh... the waybackmachineforsqlite.org perhaps


>
> Roger




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

Re: [sqlite] FTS1 or FTS2

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 05/26/2010 10:24 AM, Sam Carleton wrote:
> If I do opt to use FTS, which one should I be using, FTS1 or FTS2?
> According to the web site, it should be FTS1, correct?

Just as a matter of interest what part of the web site led you to those
conclusions?

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

iEYEARECAAYFAkv9bKcACgkQmOOfHg372QTINwCglr7//BHCgOt3vKApNZ8/EVYI
rcIAn1pYT57Rb/7zeyYGuEw+xyYVbxVG
=Imqe
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to search for asterix character?

[Jean-Christophe Deschamps]

>I use vb or in SQLlite Expert the * does not seem to work
> >From my testing * doesn't seem to work in the likeif I use '%33' it 
> returns
>everything with test33if I use '33%' it returns everything with 
>33testif I
>use '%33%' it returns everything with 33test, test33 which is the same as
>*

I can't vouch for VB, but SQLite Expert --which I use extensively-- 
definitely handles asterisks '*' in litterals correctly.

What do you mean with "which is the same as *".  Asterisk has no 
special meaning for LIKE.  You must be confusing with GLOB.

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

Re: [sqlite] how to search for asterix character?

[Bart Smissaert]

> "...Where SomeColumnContent Like '%someother[*]part%'

Thanks Olaf, that works fine.
As my customers won't get this I think I might let my code take care of this.

How would it work with glob?

RBS


On Wed, May 26, 2010 at 7:05 PM, Olaf Schmidt  wrote:
>
> "Bart Smissaert" 
> schrieb im Newsbeitrag
> news:aanlktil5lha-3-l6x8umwv8e3pyrda6h0ln3dcoyh...@mail.gmail.com...
>
>> Yes, it must be either my code or the wrapper to blame.
>> Thanks for confirming.
>
> It's a wrapper-"problem", but a perhaps a coding problem
> as well... ;-)
>
> The wrapper overrides sqlites internal Like-Function,
> to achieve (Unicode-)BSTR mapping.
>
> Internally VBs normal Like-Function then does "all the rest".
>
> The wrapper (due to compatibility reasons with regards
> to JET *.mdbs) also understands their older "like-syntax":
> "...Where SomeColumnContent Like '*somepart*'
> (in this regards behaving somewhat similar to SQLites glob,
> which BTW was _not_ overriden)...
>
> But it supports the "default-like-syntax" as well:
> "...Where SomeColumnContent Like '%somepart%'
>
> To specify single chars, which are equal to the "comparison-
> descriptors" you have to "escape them" within brackets:
> "...Where SomeColumnContent Like '%someother[*]part%'
>
> If the above should not work, then this would be considered
> a "real wrapper-bug". ;-)
>
>
> HTH
>
> Olaf Schmidt
>
>
>
> ___
> 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] how to search for asterix character?

[Olaf Schmidt]

"Bart Smissaert" 
schrieb im Newsbeitrag
news:aanlktil5lha-3-l6x8umwv8e3pyrda6h0ln3dcoyh...@mail.gmail.com...

> Yes, it must be either my code or the wrapper to blame.
> Thanks for confirming.

It's a wrapper-"problem", but a perhaps a coding problem
as well... ;-)

The wrapper overrides sqlites internal Like-Function,
to achieve (Unicode-)BSTR mapping.

Internally VBs normal Like-Function then does "all the rest".

The wrapper (due to compatibility reasons with regards
to JET *.mdbs) also understands their older "like-syntax":
"...Where SomeColumnContent Like '*somepart*'
(in this regards behaving somewhat similar to SQLites glob,
which BTW was _not_ overriden)...

But it supports the "default-like-syntax" as well:
"...Where SomeColumnContent Like '%somepart%'

To specify single chars, which are equal to the "comparison-
descriptors" you have to "escape them" within brackets:
"...Where SomeColumnContent Like '%someother[*]part%'

If the above should not work, then this would be considered
a "real wrapper-bug". ;-)


HTH

Olaf Schmidt



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

Re: [sqlite] FTS1 or FTS2

[Black, Michael (IS)]

Or to more directly answer the question...
 
select * from phone where number like '%56%';
 
 
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Simon Slavin
Sent: Wed 5/26/2010 12:25 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] FTS1 or FTS2




On 26 May 2010, at 6:24pm, Sam Carleton wrote:

> 212-456-7890
> 212-789-1234
> 212-564-9875
> 212-357-8452
> 212-285-6100
> 560-851-5522
>
> If searching for a number with '56', I want to get back: 212-4*56*-7890,
> 212-*56*4-9875, and *56*0-851-5522, NOT 212-28*5-6*100.  Is FTS the right
> answer or is there a quicker faster way?

You don't need FTS3.  See LIKE and GLOB in



Simon.
___
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] 64-bit DLL download location

[Carl Buhrke]

Does anyone know where I can download a LIB and DLL pre- built for
Win7-64bit?

I am in the process of converting C/C++ projects from Win XP to Win7-64bit
and don't want to build the DLL myself.

 

Carl Buhrke

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

Re: [sqlite] FTS1 or FTS2

[Simon Slavin]

On 26 May 2010, at 6:24pm, Sam Carleton wrote:

> 212-456-7890
> 212-789-1234
> 212-564-9875
> 212-357-8452
> 212-285-6100
> 560-851-5522
> 
> If searching for a number with '56', I want to get back: 212-4*56*-7890,
> 212-*56*4-9875, and *56*0-851-5522, NOT 212-28*5-6*100.  Is FTS the right
> answer or is there a quicker faster way?

You don't need FTS3.  See LIKE and GLOB in



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

Re: [sqlite] how to search for asterix character?

[Bart Smissaert]

Yes, it must be either my code or the wrapper to blame.
Thanks for confirming.


RBS


On Wed, May 26, 2010 at 6:18 PM, Black, Michael (IS)
 wrote:
> This works for me:
>
> sqlite> create table t(t varchar);
> sqlite> insert into t values('Testing*with asterisk');
> sqlite> insert into t values('Testing without asterisk');
> sqlite> select * from t;
> Testing*with asterisk
> Testing without asterisk
> sqlite> select * from t where t like '%*%';
> Testing*with asterisk
>
> Michael D. Black
> Senior Scientist
> Northrop Grumman Mission Systems
>
>
> 
>
> From: sqlite-users-boun...@sqlite.org on behalf of ro...@zhole.com
> Sent: Wed 5/26/2010 12:14 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] how to search for asterix character?
>
>
>
> I use vb or in SQLlite Expert the * does not seem to work
> From my testing * doesn't seem to work in the likeif I use '%33' it returns
> everything with test33if I use '33%' it returns everything with 33testif I
> use '%33%' it returns everything with 33test, test33 which is the same as
> *
> Hope this helpsRoger
>
> 
> From: "Jean-Christophe Deschamps" 
> Sent: Wednesday, May 26, 2010 12:54 PM
> To: "General Discussion of SQLite Database" 
> Subject: Re: [sqlite] how to search for asterix character?
>
>>How do I search for the asterix character *  ??
>>
>>This doesn't work:
>>select field1 from table1 where field1 like '%FH%*%'
>>as the * character here seems to be ignored.
>>
>>Using the latest version of SQLite.
>
> You're doing it right.  Either you use a wrapper that messes with * in
> litterals or there is actually no matching row.  Try using the
> command-line tool to better diagnose what's going on here.
>
> ___
> 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] how to search for asterix character?

[Bart Smissaert]

> I use vb

What wrapper is that?

RBS


On Wed, May 26, 2010 at 6:14 PM, ro...@zhole.com  wrote:
> I use vb or in SQLlite Expert the * does not seem to work
> From my testing * doesn't seem to work in the likeif I use '%33' it returns
> everything with test33if I use '33%' it returns everything with 33testif I
> use '%33%' it returns everything with 33test, test33 which is the same as
> *
> Hope this helpsRoger
>
> 
> From: "Jean-Christophe Deschamps" 
> Sent: Wednesday, May 26, 2010 12:54 PM
> To: "General Discussion of SQLite Database" 
> Subject: Re: [sqlite] how to search for asterix character?
>
>>How do I search for the asterix character *  ??
>>
>>This doesn't work:
>>select field1 from table1 where field1 like '%FH%*%'
>>as the * character here seems to be ignored.
>>
>>Using the latest version of SQLite.
>
> You're doing it right.  Either you use a wrapper that messes with * in
> litterals or there is actually no matching row.  Try using the
> command-line tool to better diagnose what's going on here.
>
> ___
> 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] how to search for asterix character?

[Black, Michael (IS)]

This works for me:
 
sqlite> create table t(t varchar);
sqlite> insert into t values('Testing*with asterisk');
sqlite> insert into t values('Testing without asterisk');
sqlite> select * from t;
Testing*with asterisk
Testing without asterisk
sqlite> select * from t where t like '%*%';
Testing*with asterisk
 
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of ro...@zhole.com
Sent: Wed 5/26/2010 12:14 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] how to search for asterix character?



I use vb or in SQLlite Expert the * does not seem to work
>From my testing * doesn't seem to work in the likeif I use '%33' it returns
everything with test33if I use '33%' it returns everything with 33testif I
use '%33%' it returns everything with 33test, test33 which is the same as
*
Hope this helpsRoger


From: "Jean-Christophe Deschamps" 
Sent: Wednesday, May 26, 2010 12:54 PM
To: "General Discussion of SQLite Database" 
Subject: Re: [sqlite] how to search for asterix character?

>How do I search for the asterix character *  ??
>
>This doesn't work:
>select field1 from table1 where field1 like '%FH%*%'
>as the * character here seems to be ignored.
>
>Using the latest version of SQLite.

You're doing it right.  Either you use a wrapper that messes with * in
litterals or there is actually no matching row.  Try using the
command-line tool to better diagnose what's going on here.

___
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] how to search for asterix character?

[ro...@zhole.com]

I use vb or in SQLlite Expert the * does not seem to work 
>From my testing * doesn't seem to work in the likeif I use '%33' it returns 
everything with test33if I use '33%' it returns everything with 33testif I 
use '%33%' it returns everything with 33test, test33 which is the same as 
*
Hope this helpsRoger


From: "Jean-Christophe Deschamps" 
Sent: Wednesday, May 26, 2010 12:54 PM
To: "General Discussion of SQLite Database" 
Subject: Re: [sqlite] how to search for asterix character? 

>How do I search for the asterix character *  ??
>
>This doesn't work:
>select field1 from table1 where field1 like '%FH%*%'
>as the * character here seems to be ignored.
>
>Using the latest version of SQLite.

You're doing it right.  Either you use a wrapper that messes with * in 
litterals or there is actually no matching row.  Try using the 
command-line tool to better diagnose what's going on here.

___
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] how to search for asterix character?

[Bart Smissaert]

Yes, thanks, it looks it might indeed be the (VB) wrapper that is to blame here.
Will contact the developer.

RBS


On Wed, May 26, 2010 at 5:53 PM, Jean-Christophe Deschamps
 wrote:
>
>>How do I search for the asterix character *  ??
>>
>>This doesn't work:
>>select field1 from table1 where field1 like '%FH%*%'
>>as the * character here seems to be ignored.
>>
>>Using the latest version of SQLite.
>
> You're doing it right.  Either you use a wrapper that messes with * in
> litterals or there is actually no matching row.  Try using the
> command-line tool to better diagnose what's going on here.
>
> ___
> 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] how to search for asterix character?

[Jean-Christophe Deschamps]

>How do I search for the asterix character *  ??
>
>This doesn't work:
>select field1 from table1 where field1 like '%FH%*%'
>as the * character here seems to be ignored.
>
>Using the latest version of SQLite.

You're doing it right.  Either you use a wrapper that messes with * in 
litterals or there is actually no matching row.  Try using the 
command-line tool to better diagnose what's going on here.

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

[sqlite] how to search for asterix character?

[Bart Smissaert]

How do I search for the asterix character *  ??

This doesn't work:
select field1 from table1 where field1 like '%FH%*%'
as the * character here seems to be ignored.

Using the latest version of SQLite.

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

Re: [sqlite] Does sqlite always allow creating own field named 'rowid'?

[Max Vlasov]

On Wed, May 26, 2010 at 6:19 PM, Pavel Ivanov  wrote:

> > But just
> > wondering, was allowing to create such field intentional? As I suppose
> such
> > field is complete phantom since most sql queries will interpret rowid
> > internally and won't allow access this user field at all.
>
> Situation is completely opposite. When you declare your field named
> "rowid" the real rowid becomes phantom and all queries will select
> your field instead. But real rowid will be still accessible via other
> names like "_rowid_" or "oid" (is there a way to access real rowid
> when all three built-in names are declared by user in the table, I
> wonder?). And this behavior is documented (see
> http://www.sqlite.org/lang_createtable.html) and thus I suppose
> intended and introduced long ago.
>
>
>
Ok, it makes sense. My only complain is that although rowid is a sqlite
specific word, it became more of "reserved" words. One example (maybe too
specific). One works with tables with a some sqlite admin and some imported
tables from other database engine contains its own rowid field. As everyone
knows every sqlite table has id regardless of the developer intention, so I
assume that I can query SELECT rowid ... for almost every existing sqlite
table in the world. But with this hypothetical case the results of such
query even can be puzzling, for example, if the data don't declare
uniqueness limitation on its own rowid field, we could see repeating values
or even non numeric values. I think that using _rowid_ might be additional
guarantee from such problems, but I suppose many developers still prefer
rowid.

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

Re: [sqlite] Does sqlite always allow creating own field named'rowid'?

[Andy Gibbs]

> Of course if you define a column "rowid integer primary key" then the
> declared column and the internal rowid are one and the same...

As an extension of this, if you are in the habit of relying on the internal 
rowid rather than having your own specifically declared "integer primary 
key" *and* use the vacuum command, then adding this particular declaration 
inside your table can be quite useful since the vacuum command *may* alter 
your rowids otherwise...


SQLite version 3.6.23.1
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table t(x);
sqlite> insert into t(rowid,x) values(1,1);
sqlite> insert into t(rowid,x) values(3,3);
sqlite> select rowid,x from t;
1|1
3|3
sqlite> vacuum;
sqlite> select rowid,x from t;
1|1
2|3
sqlite> drop table t;
sqlite> create table t(rowid integer primary key, x);
sqlite> insert into t(rowid,x) values(1,1);
sqlite> insert into t(rowid,x) values(3,3);
sqlite> select rowid,x from t;
1|1
3|3
sqlite> vacuum;
sqlite> select rowid,x from t;
1|1
3|3
sqlite>

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

Re: [sqlite] Does sqlite always allow creating own field named 'rowid'?

[Andy Gibbs]

> While implementing a table that intended for arbitrary table storage, I
> automatically named some field rowid not linking at the moment that it 
> will
> have a name conflict with sqlite internal rowid name. Lately I discovered
> this, and (not a big deal) recreated table with a different name. But just
> wondering, was allowing to create such field intentional? As I suppose 
> such
> field is complete phantom since most sql queries will interpret rowid
> internally and won't allow access this user field at all.

Sqlite works by giving priority to the column names defined in the table. 
So if you have a column named rowid then it will access this rather than the 
internal rowid.  Therefore, there is more than one way of accessing this 
internal rowid: rowid, _rowid_ and oid.

Of course if you define a column "rowid integer primary key" then the 
declared column and the internal rowid are one and the same...

http://www.sqlite.org/lang_createtable.html#rowid

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

[sqlite] Does sqlite always allow creating own field named 'rowid'?

[Max Vlasov]

While implementing a table that intended for arbitrary table storage, I
automatically named some field rowid not linking at the moment that it will
have a name conflict with sqlite internal rowid name. Lately I discovered
this, and (not a big deal) recreated table with a different name. But just
wondering, was allowing to create such field intentional? As I suppose such
field is complete phantom since most sql queries will interpret rowid
internally and won't allow access this user field at all.

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

Re: [sqlite] Update detail record based on master properties

[Max Vlasov]

> where exists (
>  select 1 from master_table where
>master_table.id=detail_table.masterid and
>masterfieldtocheck = okvalue and
>masterfield2tocheck = okvalue2);
>
> -- or
>
> where detail_table.masterid in (
>  select id from master_table where
>masterfieldtocheck = okvalue and
>masterfield2tocheck = okvalue2);
>
> --
> Igor Tandetnik
>
>
Thanks,Igor, it works, I see that sql itself is smart enough )
Both suggested by you are similar in speed, but the latter looks more
self-explanatory for me.

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

Re: [sqlite] Update detail record based on master properties

[Igor Tandetnik]

Max Vlasov wrote:
> We all know UPDATE sometimes is limited so in order to make some complex
> conditional updating we can only rely on the complexity of WHERE clause.
> 
> I would like to update my detail table based on master properties (so to set
> some value only  if the corresponding master record fits some conditions).
> In case of only one property the statement can look like this
> 
> UPDATE detail_table SET somefield=somevalue
>  WHERE
>  (SELECT masterfieldtocheck FROM master_table WHERE master_table.id
> =detail_table.masterid)=okvalue
> 
> Is there a way to alias the master table when I need to check several fields
> of the master table? Or the only choice is to write
> 
>  WHERE
>  ((SELECT masterfieldtocheck FROM master_table WHERE
> master_table.id=detail_table.masterid)=okvalue)
> AND
>  ((SELECT masterfield2tocheck FROM master_table WHERE master_table.id
> =detail_table.masterid)=okvalue2)

where exists (
  select 1 from master_table where 
master_table.id=detail_table.masterid and
masterfieldtocheck = okvalue and
masterfield2tocheck = okvalue2);

-- or

where detail_table.masterid in (
  select id from master_table where
masterfieldtocheck = okvalue and
masterfield2tocheck = okvalue2);

-- 
Igor Tandetnik

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

Re: [sqlite] Update detail record based on master properties

[Simon Slavin]

On 26 May 2010, at 9:21am, Max Vlasov wrote:

> UPDATE detail_table SET somefield=somevalue
>  WHERE
>  (SELECT masterfieldtocheck FROM master_table WHERE master_table.id
> =detail_table.masterid)=okvalue
> 
> Is there a way to alias the master table when I need to check several fields
> of the master table? Or the only choice is to write
> 
>  WHERE
>  ((SELECT masterfieldtocheck FROM master_table WHERE
> master_table.id=detail_table.masterid)=okvalue)
> AND
>  ((SELECT masterfield2tocheck FROM master_table WHERE master_table.id
> =detail_table.masterid)=okvalue2)

This is why you are using a programming language rather than trying to make SQL 
perform contortions.  Your operation has two parts: one part has nothing to do 
with the detail table, one part has nothing to do with the master table.  
Perform a SELECT command first and use it to work out which masterids qualify.  
You can use 'group_concat()' to reduce the results to one string of values.

SELECT group_concat(id) FROM master_table WHERE [list of master_table qualities 
here]

Then do an UPDATE command which uses that string of values:

UPDATE detail_table SET somefield=somevalue
 WHERE
 detail_table.masterid IN listOfApplicableMasters


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

[sqlite] Update detail record based on master properties

[Max Vlasov]

We all know UPDATE sometimes is limited so in order to make some complex
conditional updating we can only rely on the complexity of WHERE clause.

I would like to update my detail table based on master properties (so to set
some value only  if the corresponding master record fits some conditions).
In case of only one property the statement can look like this

UPDATE detail_table SET somefield=somevalue
  WHERE
  (SELECT masterfieldtocheck FROM master_table WHERE master_table.id
=detail_table.masterid)=okvalue

Is there a way to alias the master table when I need to check several fields
of the master table? Or the only choice is to write

  WHERE
  ((SELECT masterfieldtocheck FROM master_table WHERE
master_table.id=detail_table.masterid)=okvalue)
AND
  ((SELECT masterfield2tocheck FROM master_table WHERE master_table.id
=detail_table.masterid)=okvalue2)

If I still need to repeat a similar select statement in the second section,
is sqlite optimizer smart enough to recognize that the both parts queries
the same record and not to perform this operation several times?

Thanks

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

Re: [sqlite] please help beginner on install issue

[zeal]

I have fixed the problem with google's help:
just _sqlite3.so is not installed correctly.
i reinstall python, the problem is gone.
thanks.

--
From: "zeal" 
Sent: Wednesday, May 26, 2010 2:59 PM
To: "General Discussion of SQLite Database" 
Subject: [sqlite] please help beginner on install issue

> Hi,
>
>   beginner question: i installed python, sqlite3 in linux with step:
> 1cd sqlite-3.6.22
>
> 2./configure --prefix=/home/omc/daisy/python/
>
> 3make
>
> 4make install
>
> when i run the python, and want to import sqlite3, error at:
>
> Python 2.5.2 (r252:60911, May 26 2010, 09:38:53)
> [GCC 3.4.6 20060404 (Red Hat 3.4.6-11)] on linux2
> Type "help", "copyright", "credits" or "license" for more information.
 import sqlite3
> Traceback (most recent call last):
>  File "", line 1, in 
>  File "/home/omc/daisy/python/lib/python2.5/sqlite3/__init__.py", line 24, 
> in 
>from dbapi2 import *
>  File "/home/omc/daisy/python/lib/python2.5/sqlite3/dbapi2.py", line 27, 
> in 
>from _sqlite3 import *
> ImportError: No module named _sqlite3
>
> thanks for your help
> daisy.
> ___
> 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] please help beginner on install issue

[zeal]

Hi, 

   beginner question: i installed python, sqlite3 in linux with step:
1cd sqlite-3.6.22

2./configure --prefix=/home/omc/daisy/python/

3make

4make install

when i run the python, and want to import sqlite3, error at:

Python 2.5.2 (r252:60911, May 26 2010, 09:38:53)
[GCC 3.4.6 20060404 (Red Hat 3.4.6-11)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
Traceback (most recent call last):
  File "", line 1, in 
  File "/home/omc/daisy/python/lib/python2.5/sqlite3/__init__.py", line 24, in 

from dbapi2 import *
  File "/home/omc/daisy/python/lib/python2.5/sqlite3/dbapi2.py", line 27, in 

from _sqlite3 import *
ImportError: No module named _sqlite3

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