Re: [sqlite] SQLite: Database or disk full

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 21/11/11 23:02, gavyas wrote:
> I dont understand why I am getting this error.

My best guess would be that a fork is happening after the database has
been opened inserted by the compiler (but not gcc) in order to make things
parallel.  If you do a fork after a database has been opened then you are
very likely to get this kind of error in addition to database corruption.
 Ensure all open calls happen after forks.

It is possible to put checking into SQLite to detect when the API has been
used across a fork.  I have it as part of the APSW project where it keeps
track of which pid the internal mutexes are called.  It incurs about 1%
overhead in a program only doing database calls.  Code example is here:

  http://code.google.com/p/apsw/source/browse/src/apsw.c#860

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk7LTdQACgkQmOOfHg372QTyZwCfbMHOcLjp9ctFMDvZtMJXu5oC
yW0AoJRRaJtNHB1xQviY00khV79aVIWV
=W5E1
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Problems with SQLite when used with MPI

[Gaurav Vyas]

Here is the situation. I am using a cluster on which you can use MPI to
split the processing time on to different processors. I was using
PostgreSQL but since the data was on different server than the one I am
running the code, it was slowing down the simulation due to connection
overhead on PostgreSQL server. Then I switched to on-disk management
software and so I chose SQLite. I first ran it without MPI on server,  and
it ran (compile g++). Then I switched to MPI (mpicc compiler), it is not
even running for one node (on which I am using one thread). It gives error
"Database or Disk is full". Further debugging revlealed that sqlite3_step()
was returning 1  and that means "SQL error or Database missing". It was
able to get the number of columns with the same database and SQL querry. I
dont understand why I am getting this error.

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

Re: [sqlite] SQLite: Database or disk full

[gavyas]

Here is the situation. I am using a cluster on which you can use MPI to split
the processing time on to different processors. I was using PostgreSQL but
since the data was on different server than the one I am running the code,
it was slowing down the simulation due to connection overhead on PostgreSQL
server. Then I switched to on-disk management software and so I chose
SQLite. I first ran it without MPI on server,  and it ran (compile g++).
Then I switched to MPI (mpicc compiler), it is not even running for one node
(on which I am using one thread). It gives error "Database or Disk is full".
Further debugging revlealed that sqlite3_step() was returning 1  and that
means "SQL error or Database missing". It was able to get the number of
columns with the same database and SQL querry. I dont understand why I am
getting this error.

Gaurav

gavyas wrote:
> 
> Its working fine with g++ compiler but not mpicc!!
> 
> Dan Kennedy-4 wrote:
>> 
>> On 11/22/2011 09:48 AM, gavyas wrote:
>>>
>>> I checked the code again and debugged it. The  code gave error when I am
>>> passing the querry "SELECT * FROM households ORDER BY zone_id LIMIT
>>> 1
>>> OFFSET 0" but it ran successfully for LIMIT 5000. I dont understand if
>>> there
>>> is an upper limit on LIMIT or what but the table "households" has 1
>>> data
>>> points. The code didnt run for LIMIT  either
>> 
>> This is a unix system, correct?
>> 
>> It's worth trying 3.7.9 if you are using something older than
>> that. There has been a fix or two regarding interrupts during
>> write() system calls over the last few months.
>> 
>> If you're already on 3.7.9, try adding some debugging code
>> to SQLite to print out errno and call perror() right before
>> the "return SQLITE_FULL;" line in function unixWrite().
>> Line 27722 of the 3.7.9 amalgamation on the website. Maybe
>> there is some other error code we need to retry writes following.
>> 
>> Dan.
>> 
>> 
>> 
>>>
>>> Simon Slavin-3 wrote:


 On 21 Nov 2011, at 11:09pm, gavyas wrote:

> I am able to run the code successfully when I dont use parallel runs.
> It
> gives error when I am running the code parallely.

 Ahha.  That's a more useful diagnostic.  If you haven't already, read
 these:

 

 

 but I can't comment on multi-threading from my own use.  I hope someone
 else can.

 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
>> 
>> 
> 
> 

-- 
View this message in context: 
http://old.nabble.com/SQLite%3A-Database-or-disk-full-tp32871505p32872083.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] SQLite: Database or disk full

[gavyas]

Its working fine with g++ compiler but not mpicc!!

Dan Kennedy-4 wrote:
> 
> On 11/22/2011 09:48 AM, gavyas wrote:
>>
>> I checked the code again and debugged it. The  code gave error when I am
>> passing the querry "SELECT * FROM households ORDER BY zone_id LIMIT 1
>> OFFSET 0" but it ran successfully for LIMIT 5000. I dont understand if
>> there
>> is an upper limit on LIMIT or what but the table "households" has 1
>> data
>> points. The code didnt run for LIMIT  either
> 
> This is a unix system, correct?
> 
> It's worth trying 3.7.9 if you are using something older than
> that. There has been a fix or two regarding interrupts during
> write() system calls over the last few months.
> 
> If you're already on 3.7.9, try adding some debugging code
> to SQLite to print out errno and call perror() right before
> the "return SQLITE_FULL;" line in function unixWrite().
> Line 27722 of the 3.7.9 amalgamation on the website. Maybe
> there is some other error code we need to retry writes following.
> 
> Dan.
> 
> 
> 
>>
>> Simon Slavin-3 wrote:
>>>
>>>
>>> On 21 Nov 2011, at 11:09pm, gavyas wrote:
>>>
 I am able to run the code successfully when I dont use parallel runs.
 It
 gives error when I am running the code parallely.
>>>
>>> Ahha.  That's a more useful diagnostic.  If you haven't already, read
>>> these:
>>>
>>> 
>>>
>>> 
>>>
>>> but I can't comment on multi-threading from my own use.  I hope someone
>>> else can.
>>>
>>> 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
> 
> 

-- 
View this message in context: 
http://old.nabble.com/SQLite%3A-Database-or-disk-full-tp32871505p32872048.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] Followup: Added Missing error message to past post.

[Taleeb Anwar]

Download the x64 mixed-mode assembly compiled statically against the .NET
Framework 3.5 (as you are using VS2010 on a 64 bit computer). Regarding
targetting x86 users you can control this by using the property of the
project...

Thanks & Regards
Taleeb Anwar

*Hum Mashriq Ke "Miskeenon" Ka Dil Maghrib men Ja Atka Hai!!*




On Mon, Nov 21, 2011 at 7:17 PM, Dave  wrote:

>
>
> On 11/21/2011 3:53 PM, Joe Mistachkin wrote:
>
>> I am running my Visual Studio on Win 7 64 Bit Pro but my database needs
>>> are simple for now so I am not sure which download I need or which
>>> framework to target. I want XP users to be able to use my software so I
>>> am guessing I should go with the 3.5 framework. And I also am guessing
>>> 32 Bit would be the best choice as it will work on both 32 and 64 bit
>>> platforms. I tried to run a few of the installers and kept getting the
>>> following error message:
>>>
>>> Failed to Install Microsoft Visual C++ Runtime
>>> vcredist_x64_2010_SP1.exe, Asia
>>>
>>> Which download would be the correct one for me?
>>>
>>> The installers on the web site are primarily intended for users that do
>> not already have the necessary Microsoft Visual C++ runtime installed.
>>
>> For development machines, it would be far better to simply download the
>> ZIP file(s) containing the pre-compiled binaries you need.
>>
>> In your case, it sounds like you will want to download the x86 mixed-mode
>> assembly compiled statically against the .NET Framework 3.5, available
>> here:
>>
>> https://system.data.sqlite.**org/sqlite-netFx35-static-**
>> binary-bundle-Win32-200
>> 8-1.0.76.0.zip
>>
>> --
>> Joe Mistachkin
>>
>> Thanks Joe,
>  I am coming from VB6 Classic and finally making the move to .NET as I
> didn't like the looks of it when it came out and I actually bought it. It
> should have settled down by now.
> Dave
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite: Database or disk full

[Dan Kennedy]

On 11/22/2011 09:48 AM, gavyas wrote:


I checked the code again and debugged it. The  code gave error when I am
passing the querry "SELECT * FROM households ORDER BY zone_id LIMIT 1
OFFSET 0" but it ran successfully for LIMIT 5000. I dont understand if there
is an upper limit on LIMIT or what but the table "households" has 1 data
points. The code didnt run for LIMIT  either


This is a unix system, correct?

It's worth trying 3.7.9 if you are using something older than
that. There has been a fix or two regarding interrupts during
write() system calls over the last few months.

If you're already on 3.7.9, try adding some debugging code
to SQLite to print out errno and call perror() right before
the "return SQLITE_FULL;" line in function unixWrite().
Line 27722 of the 3.7.9 amalgamation on the website. Maybe
there is some other error code we need to retry writes following.

Dan.





Simon Slavin-3 wrote:



On 21 Nov 2011, at 11:09pm, gavyas wrote:


I am able to run the code successfully when I dont use parallel runs. It
gives error when I am running the code parallely.


Ahha.  That's a more useful diagnostic.  If you haven't already, read
these:





but I can't comment on multi-threading from my own use.  I hope someone
else can.

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

Re: [sqlite] SQLite: Database or disk full

[gavyas]

I checked the code again and debugged it. The  code gave error when I am
passing the querry "SELECT * FROM households ORDER BY zone_id LIMIT 1
OFFSET 0" but it ran successfully for LIMIT 5000. I dont understand if there
is an upper limit on LIMIT or what but the table "households" has 1 data
points. The code didnt run for LIMIT  either

Simon Slavin-3 wrote:
> 
> 
> On 21 Nov 2011, at 11:09pm, gavyas wrote:
> 
>> I am able to run the code successfully when I dont use parallel runs. It
>> gives error when I am running the code parallely. 
> 
> Ahha.  That's a more useful diagnostic.  If you haven't already, read
> these:
> 
> 
> 
> 
> 
> but I can't comment on multi-threading from my own use.  I hope someone
> else can.
> 
> 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/SQLite%3A-Database-or-disk-full-tp32871505p32871964.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] Followup: Added Missing error message to past post.

[Dave]

On 11/21/2011 3:53 PM, Joe Mistachkin wrote:

I am running my Visual Studio on Win 7 64 Bit Pro but my database needs
are simple for now so I am not sure which download I need or which
framework to target. I want XP users to be able to use my software so I
am guessing I should go with the 3.5 framework. And I also am guessing
32 Bit would be the best choice as it will work on both 32 and 64 bit
platforms. I tried to run a few of the installers and kept getting the
following error message:

Failed to Install Microsoft Visual C++ Runtime
vcredist_x64_2010_SP1.exe, Asia

Which download would be the correct one for me?


The installers on the web site are primarily intended for users that do
not already have the necessary Microsoft Visual C++ runtime installed.

For development machines, it would be far better to simply download the
ZIP file(s) containing the pre-compiled binaries you need.

In your case, it sounds like you will want to download the x86 mixed-mode
assembly compiled statically against the .NET Framework 3.5, available
here:

https://system.data.sqlite.org/sqlite-netFx35-static-binary-bundle-Win32-200
8-1.0.76.0.zip

--
Joe Mistachkin


Thanks Joe,
  I am coming from VB6 Classic and finally making the move to .NET as I 
didn't like the looks of it when it came out and I actually bought it. 
It should have settled down by now.

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

Re: [sqlite] SQLite: Database or disk full

[Simon Slavin]

On 21 Nov 2011, at 11:09pm, gavyas wrote:

> I am able to run the code successfully when I dont use parallel runs. It
> gives error when I am running the code parallely. 

Ahha.  That's a more useful diagnostic.  If you haven't already, read these:





but I can't comment on multi-threading from my own use.  I hope someone else 
can.

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

Re: [sqlite] SQLite: Database or disk full

[gavyas]

I am able to run the code successfully when I dont use parallel runs. It
gives error when I am running the code parallely. 
On 21 Nov 2011, at 9:44pm, gavyas wrote:

> I am using SQLite to handle database while running a simulation on
> supercomputers here. I am using MPI process to do multiple threading. I am
> keep getting error that says "database or disk is full" while passing any
> querry to SQLite. I have checked the space quota and I have so much of the
> space available. I dont know why I am getting this error, so can you tell
> me
> what is causing this problem?

Are you able to process any SQL statement at all, or do you get this error
every time ?

If you access the database file using the sqlite3 command-line tool, and try
to execute the same SQL commands, do you get the same error ?

SQLite makes a temporary 'journal' file while it's working.  I think that,
on your platform, by default it will be in the same directory as the
database file.  Does your application have enough privilages to create new
files in that directory ?

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/SQLite%3A-Database-or-disk-full-tp32871505p32871706.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] SQLite: Database or disk full

[Simon Slavin]

On 21 Nov 2011, at 9:44pm, gavyas wrote:

> I am using SQLite to handle database while running a simulation on
> supercomputers here. I am using MPI process to do multiple threading. I am
> keep getting error that says "database or disk is full" while passing any
> querry to SQLite. I have checked the space quota and I have so much of the
> space available. I dont know why I am getting this error, so can you tell me
> what is causing this problem?

Are you able to process any SQL statement at all, or do you get this error 
every time ?

If you access the database file using the sqlite3 command-line tool, and try to 
execute the same SQL commands, do you get the same error ?

SQLite makes a temporary 'journal' file while it's working.  I think that, on 
your platform, by default it will be in the same directory as the database 
file.  Does your application have enough privilages to create new files in that 
directory ?

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

Re: [sqlite] Followup: Added Missing error message to past post.

[Joe Mistachkin]

> 
> I am running my Visual Studio on Win 7 64 Bit Pro but my database needs 
> are simple for now so I am not sure which download I need or which 
> framework to target. I want XP users to be able to use my software so I 
> am guessing I should go with the 3.5 framework. And I also am guessing 
> 32 Bit would be the best choice as it will work on both 32 and 64 bit 
> platforms. I tried to run a few of the installers and kept getting the 
> following error message:
> 
> Failed to Install Microsoft Visual C++ Runtime 
> vcredist_x64_2010_SP1.exe, Asia
> 
> Which download would be the correct one for me?
> 

The installers on the web site are primarily intended for users that do
not already have the necessary Microsoft Visual C++ runtime installed.

For development machines, it would be far better to simply download the
ZIP file(s) containing the pre-compiled binaries you need.

In your case, it sounds like you will want to download the x86 mixed-mode
assembly compiled statically against the .NET Framework 3.5, available
here:

https://system.data.sqlite.org/sqlite-netFx35-static-binary-bundle-Win32-200
8-1.0.76.0.zip

--
Joe Mistachkin

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

[sqlite] SQLite: Database or disk full

[gavyas]

Hi all,

I am using SQLite to handle database while running a simulation on
supercomputers here. I am using MPI process to do multiple threading. I am
keep getting error that says "database or disk is full" while passing any
querry to SQLite. I have checked the space quota and I have so much of the
space available. I dont know why I am getting this error, so can you tell me
what is causing this problem?
-- 
View this message in context: 
http://old.nabble.com/SQLite%3A-Database-or-disk-full-tp32871505p32871505.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] SQLite: Database or disk full

[gavyas]

Hi all,

I am using SQLite to handle database while running a simulation on
supercomputers here. I am using MPI process to do multiple threading. I am
keep getting error that says "database or disk is full" while passing any
querry to SQLite. I have checked the space quota and I have so much of the
space available. I dont know why I am getting this error, so can you tell me
what is causing this problem?
-- 
View this message in context: 
http://old.nabble.com/SQLite%3A-Database-or-disk-full-tp32871501p32871501.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] Followup: Added Missing error message to past post.

[Dave]

Hi,
  I want to use SQLite with VB.Net. I have Visual Studio 2010 Pro. I 
have downloaded and installed the SQLite-1.0.66.0 setup from:


http://sourceforge.net/projects/sqlite-dotnet2/files/SQLite%20for%20ADO.NET%202.0/1.0.66.0/ 



This installed fine. Then I went to the SQLite page here to get the 
latest version:


http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

and now I am a bit confused on what I need to download and install as it 
seemed so simple from the first place.


I am running my Visual Studio on Win 7 64 Bit Pro but my database needs 
are simple for now so I am not sure which download I need or which 
framework to target. I want XP users to be able to use my software so I 
am guessing I should go with the 3.5 framework. And I also am guessing 
32 Bit would be the best choice as it will work on both 32 and 64 bit 
platforms. I tried to run a few of the installers and kept getting the 
following error message:


Failed to Install Microsoft Visual C++ Runtime 
vcredist_x64_2010_SP1.exe, Asia


Which download would be the correct one for me?

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

[sqlite] SQLite and VB.Net Questions

[Dave]

Hi,
  I want to use SQLite with VB.Net. I have Visual Studio 2010 Pro. I 
have downloaded and installed the SQLite-1.0.66.0 setup from:


http://sourceforge.net/projects/sqlite-dotnet2/files/SQLite%20for%20ADO.NET%202.0/1.0.66.0/

This installed fine. Then I went to the SQLite page here to get the 
latest version:


http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

and now I am a bit confused on what I need to download and install as it 
seemed so simple from the first place.


I am running my Visual Studio on Win 7 64 Bit Pro but my database needs 
are simple for now so I am not sure which download I need or which 
framework to target. I want XP users to be able to use my software so I 
am guessing I should go with the 3.5 framework. And I also am guessing 
32 Bit would be the best choice as it will work on both 32 and 64 bit 
platforms. I tried to run a few of the installers and kept getting the 
following error message:


Which download would be the correct one for me?

Thanks,
Dave


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

Re: [sqlite] Confused by DELETE statement error

[Pavel Ivanov]

DELETE statement doesn't support ORDER BY clause and by default
doesn't support LIMIT clause. So you should do the select, find the
value of prty for the second row and then issue a delete adding to its
WHERE clause "and prty < ?" where you will bind that value of prty you
found.

Pavel


On Mon, Nov 21, 2011 at 3:32 PM, Don V Nielsen  wrote:
> I'm confused by the error message, "near 'order' : syntax error", with the
> following statement.  The same statement, beginning with "select *" in
> place of "delete", will function fine.
>
> delete from seg_ny_adds
> where needid = 90
> order by prty desc
> limit (select count() from seg_ny_adds where needid = 90) - 2
>
> What I am trying to accomplish is to remove from each group (needid
> identifies the group) all but the top 2 rows.
> The plan is to loop through the table, identify each group that has more
> than two rows, order them by priority descending,
> and then delete the top records leaving the bottom two.  The bottom two
> will be the highest priority rows.
>
> Thanks for time and consideration,
> dvn
> ___
> 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] Confused by DELETE statement error

[Igor Tandetnik]

On 11/21/2011 3:32 PM, Don V Nielsen wrote:

I'm confused by the error message, "near 'order' : syntax error", with the
following statement.  The same statement, beginning with "select *" in
place of "delete", will function fine.

delete from seg_ny_adds
where needid = 90
order by prty desc
limit (select count() from seg_ny_adds where needid = 90) - 2


You need to build SQLite with a particular option to enable LIMIT and 
ORDER BY clauses in DELETE statement:


http://sqlite.org/compile.html#enable_update_delete_limit

Alternatively, change your statement to

delete from seg_ny_adds where rowid in (
  select rowid from seg_ny_adds
  where needid = 90
  order by prty desc
  limit (select count() from seg_ny_adds where needid = 90) - 2
);

or

delete from seg_ny_adds
where needid = 90 and rowid not in (
  select rowid from seg_ny_adds
  where needid = 90
  order by prty limit 2
);

Replace "rowid" with the name of INTEGER PRIMARY KEY column, if you have 
one.



What I am trying to accomplish is to remove from each group (needid
identifies the group) all but the top 2 rows.
The plan is to loop through the table, identify each group that has more
than two rows, order them by priority descending,
and then delete the top records leaving the bottom two


You should be able to do the whole thing in a single statement, without 
any loop:


delete from seg_ny_adds where rowid not in (
  select rowid from seg_ny_adds s
  where s.needid = seg_ny_adds.needid
  order by prty limit 2
);

--
Igor Tandetnik

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

[sqlite] Confused by DELETE statement error

[Don V Nielsen]

I'm confused by the error message, "near 'order' : syntax error", with the
following statement.  The same statement, beginning with "select *" in
place of "delete", will function fine.

delete from seg_ny_adds
where needid = 90
order by prty desc
limit (select count() from seg_ny_adds where needid = 90) - 2

What I am trying to accomplish is to remove from each group (needid
identifies the group) all but the top 2 rows.
The plan is to loop through the table, identify each group that has more
than two rows, order them by priority descending,
and then delete the top records leaving the bottom two.  The bottom two
will be the highest priority rows.

Thanks for time and consideration,
dvn
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proper way to change temp directory

[Don V Nielsen]

In Ruby (just for giggles), its

Dir.chdir('dir text') {|dir|
  # do your sort stuff here
}  # previous directory restored when logic block is completed




On Sun, Nov 20, 2011 at 3:50 AM, Yang Zhang  wrote:

> Cool beans, perhaps this should be added to the docs!
>
> On Sun, Nov 20, 2011 at 1:36 AM, Dan Kennedy 
> wrote:
> > On 11/20/2011 04:00 PM, Yang Zhang wrote:
> >>
> >> Out of curiosity, what's the proper way to change the temp directory
> >> (say, to avoid "Error: database or disk full" errors on vacuum, which
> >> I ran into)?  temp_store_directory has been working for me but it's
> >> deprecated and may be elided from builds.  Is the only option to
> >> recompile sqlite?  Thanks.
> >
> > On unix setting the TMPDIR environment variable works.
> >
> > On windows SQLite uses GetTempPath(). So perhaps setting
> > TMP or TEMP works there.
> >
> >
> >
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> Yang Zhang
> http://yz.mit.edu/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite command shell - read/process file then exit?

[Simon Davies]

On 21 November 2011 13:41, SupportLists  wrote:
> Hi,
>
> I am trying to use sqlite3 command shell with -init filename.
>
> The file has:
>
> CREATE TABLE griddata (id_suit VARCHAR(14) PRIMARY KEY,xpos DECIMAL(7),ypos
> DECIMAL(7),cellvalue DECIMAL(14,5) DEFAULT -);
> .separator ","
> .import /home/cquiros/temp/layers/layers/New/lgp001.csv griddata
> ALTER TABLE griddata ADD COLUMN colour char(9);
> ALTER TABLE griddata ADD COLUMN classCode integer;
> .quit
>
> All the commands works except .quit  . How can I execute the shell with a
> -init and then exit when it finished processing the file?

sqlite3 -init filename tst.db ".quit"

or

sqlite3 tst.db ".read filename"

>
> Many thanks,
> Carlos.

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

[sqlite] sqlite command shell - read/process file then exit?

[SupportLists]

Hi,

I am trying to use sqlite3 command shell with -init filename.

The file has:

CREATE TABLE griddata (id_suit VARCHAR(14) PRIMARY KEY,xpos 
DECIMAL(7),ypos DECIMAL(7),cellvalue DECIMAL(14,5) DEFAULT -);

.separator ","
.import /home/cquiros/temp/layers/layers/New/lgp001.csv griddata
ALTER TABLE griddata ADD COLUMN colour char(9);
ALTER TABLE griddata ADD COLUMN classCode integer;
.quit

All the commands works except .quit  . How can I execute the shell with 
a -init and then exit when it finished processing the file?


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

Re: [sqlite] cyrillic search

[Simon Slavin]

On 21 Nov 2011, at 12:44am, coax wrote:

> I'm trying to do a simple search like this:
> 
> select * from foo where bar like "%тпример%";
> 
> but sqlite isn't allowing me to enter the cyrillic text for some
> reason. It either does nothing or acts like I hit the up arrow for a
> few of the letters. Any ideas on how to get this working?

'sqlite' is an API library for programmers.  It has nothing to do with the 
keyboard.

Are you using a program ?  Perhaps one called 'sqlite3.exe' ?  If so, this 
program needs to be compiled with a version of readline() which understands 
non-Roman characters.  It's probably that the pre-compiled version you can get 
from the sqlite.org site handles only Roman characters.

Perhaps a Russian Windows user is reading and can advise you.

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

[sqlite] cyrillic search

[coax]

I'm trying to do a simple search like this:

select * from foo where bar like "%тпример%";

but sqlite isn't allowing me to enter the cyrillic text for some
reason. It either does nothing or acts like I hit the up arrow for a
few of the letters. Any ideas on how to get this working?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS3/FTS4 - Finding the term(s) that completes the input

[Mohit Sindhwani]

Hi Filip,


On 21/11/2011 4:33 PM, Filip Navara wrote:

On Mon, Nov 21, 2011 at 4:13 AM, Mohit Sindhwani  wrote:


I think my examples muddied the waters.  I have looked at Section 3 of the
FTS documents and that lets me bring back the "full result" that matches -
so, if I search for 'ling j', it can tell me that the result that matches
is 'james ling, alibaba'.  What it does not let me do is figure out that
the partly entered term "j" completes to the word 'james' - that is the
part I'm trying to figure out.

Best Regards,
Mohit.


http://sqlite.org/fts3.html#section_4_1



I am so sure that I read this part so many times... and I always missed 
it!  This will obviously help a lot!


Best Regards,
Mohit.
21/11/2011 | 8:36 PM.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] disk I/O error

[Richard Hipp]

On unix, you should usually only get SQLITE_IOERR_WRITE if write() returns
-1 for some reason other than ENOSPC - in other words, if the write failed
for some reason other than the disk being full.

On Mon, Nov 21, 2011 at 4:05 AM, Tal Tabakman wrote:

> As a continuation to the bellow:
> I am using linux RH5
> "uname -a" on my machine gives:
> Linux vl-talt 2.6.18-194.el5 #1 SMP Tue Mar 16 21:52:39 EDT 2010 x86_64
> x86_64 x86_64 GNU/Linux
>
>
> On Sun, Nov 20, 2011 at 6:53 AM, Tal Tabakman  gmail.com  >>wrote:
>
> >* in a continuation to the below, it seems I get the following extended
> err*>* code*>* SQLITE_IOERR_WRITE*>* what are the reasons for that one ? is
> it a symptom of mem blow-up ?*>* cheers*>* Tal*>**>**What operating system
> did you say you were using?
>
>
> >* >* Hi Guys,*>* in which cases can I get sqlite3 error "disk I/O error",
> is*>* it only when*>* disk is full ?*>* any other reasons for getting this
> one*>* ?*>***>* Lots of reasons.  Use sqlite3_extended_errcode() to get a
> more detailed*>* error code that can help isolate the problem.  And/or
> use*>* sqlite3_config(SQLITE_CONFIG_LOG,...) to register a callback
> function that*>* will give more detailed error messages.*>*
> ___*
> ___
> 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] disk I/O error

[Tal Tabakman]

As a continuation to the bellow:
I am using linux RH5
"uname -a" on my machine gives:
Linux vl-talt 2.6.18-194.el5 #1 SMP Tue Mar 16 21:52:39 EDT 2010 x86_64
x86_64 x86_64 GNU/Linux


On Sun, Nov 20, 2011 at 6:53 AM, Tal Tabakman http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users>>wrote:

>* in a continuation to the below, it seems I get the following extended err*>* 
>code*>* SQLITE_IOERR_WRITE*>* what are the reasons for that one ? is it a 
>symptom of mem blow-up ?*>* cheers*>* Tal*>**>**What operating system did you 
>say you were using?


>* >* Hi Guys,*>* in which cases can I get sqlite3 error "disk I/O error", 
>is*>* it only when*>* disk is full ?*>* any other reasons for getting this 
>one*>* ?*>***>* Lots of reasons.  Use sqlite3_extended_errcode() to get a more 
>detailed*>* error code that can help isolate the problem.  And/or use*>* 
>sqlite3_config(SQLITE_CONFIG_LOG,...) to register a callback function that*>* 
>will give more detailed error messages.*>* 
>___*
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS3/FTS4 - Finding the term(s) that completes the input

[Filip Navara]

On Mon, Nov 21, 2011 at 4:13 AM, Mohit Sindhwani  wrote:

> Hi Abhinav,
>
>
> On 21/11/2011 2:52 AM, Abhinav Upadhyay wrote:
>
>> On Mon, Nov 21, 2011 at 12:17 AM, Mohit Sindhwani  wrote:
>>
>>> What I'd like to be able to do is something like this:
>>>
>>> - let's say that the FTS4 table has values such as:
>>> * mohit sindhwani, onghu
>>> * john doe, gmail
>>> * james ling, alibaba
>>> * john barn, yahoo
>>> ...and so on
>>>
>>> If the user enters "j", I'd like to suggest that this would complete to
>>> the
>>> words:
>>> john and james in the current set
>>>
>>> If the user enters 'ling j', I'd like to restrict it and say:
>>>
 james is the only word that can be matched now
 james ling, alibaba is the result

>>>
>>> I think you might want to look at Token Prefix queries:
>>> http://sqlite.org/fts3.html#**section_3
>>>
>>
> I think my examples muddied the waters.  I have looked at Section 3 of the
> FTS documents and that lets me bring back the "full result" that matches -
> so, if I search for 'ling j', it can tell me that the result that matches
> is 'james ling, alibaba'.  What it does not let me do is figure out that
> the partly entered term "j" completes to the word 'james' - that is the
> part I'm trying to figure out.
>
> Best Regards,
> Mohit.


http://sqlite.org/fts3.html#section_4_1

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