Re: [sqlite] cannot set connection while a datareader is active

[rava]

Ops... i'm not able to do this, never used first and don't know c# so well to 
understand the project. Can i try somes others way from my project??
Have you idea about why with the same code the sqlite provider is the only wich 
give me this error? Could depend from a query ? But i don't obtain visible 
error passing query from a gui database external software, just tested ... 

Joe Mistachkin  ha scritto:

>
>Stefano Ravagni wrote:
>> 
>> Joe i cannot find the way for follow the command object properties you 
>> saycould you tell me what and how to observe exactly please ? 
>> 
>
>You might need to add the System.Data.SQLite project(s) to the solution in
>order
>to be able to see full debugging information for them.
>
>I recommend adding the "System.Data.SQLite.20XX.csproj" and
>"SQLite.Interop.20XX.vc[x]proj" projects that correspond to the version of
>Visual
>Studio you are using.
>
>Then, you'll want to reset the reference to point to the project instead of
>the
>built assembly.
>
>--
>Joe Mistachkin
>
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] deleting dupicate rows

[Simon Slavin]

On 17 Apr 2014, at 1:21am, James K. Lowden  wrote:

> Simon Slavin  wrote:
> 
>> If you really want to do it in the TABLE definition, use the SQLite
>> shell tool to '.dump' the table as a set of SQL commands, edit the
>> dump file to add that constraint, then use the SQLite tool to '.read'
>> the SQL command file.
> 
> Why not just create the new table, then
> 
>   insert into S select * from R;
>   drop table R;
>   alter table S rename to R;
> 
> or similar?

You're right.  I can only come up with two reasons:

(A) The one that made me make the mistake: I'm not yet used to the INSERT ... 
SELECT facility SQLite provides.
(B) The other one: FOREIGN KEYS can prevent you from deleting the first TABLE.

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

Re: [sqlite] WITHOUT ROWID problem and large number of row updating

[James K. Lowden]

On Wed, 16 Apr 2014 10:24:51 -0700
Joseph Yesselman  wrote:

> sql = "CREATE TABLE two_way("   \
>   "id VARCHAR(50) NOT NULL,"  \
>   "names VARCHAR(2000),"  \
>   "rs VARCHAR(3000),"  \
>   "ds VARCHAR(2000)," \
>   "sugs VARCHAR(2000), " \
>   "PRIMARY KEY (id));";
...
> if names is currently "motif_1,motif_2;motif_5,motif6"
> and the new data I have is "motif_10,motif_11", after the update i
> need names to be:
> "motif_1,motif_2;motif_5,motif6;motif_10,motif_11"
...
> Is there a way to do this for a large number of rows in a single
> transaction, since I am currently doing it for one row per
> transaction and its very very slow.

Three words: first normal form.  

I guess, from your descripion of the data, that you've packed 4 or 5
tables into one.  To nomalize it, you would made a bunch of tables in
the form

create table two_way_names 
( id VARCHAR(50) NOT NULL -- (integer would be better ...)
, name VARCHAR(12)
, primary key (id, name)
);

where name is a single name instead of a list.  Then, the updates would
be very fast, and you'd need one update per table instead of per row.  

Contrary to a popular misconception, normal forms were invented -- or
perhaps discovered -- in part *for* efficiency.  By minimizing
redunancy, you minimize what needs to be searched and updated.  

if you take my advice, you'll also confront another question, namely,
what is the meaning, if any, implicit in the order of the existing
column two_way.names?  Does 'motif_11' "align" to anything in the
other columns, or is it just another name belonging to id?  

If the order has implicit meaning -- if 'motif_11' relates to some
specific position in the list in another column -- then that column
becomes part of the new table, but holding only a single value per
row.  If the order is meaningless, your existing table can be
reconstituted using string concatenation and JOIN.  

HTH.  

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

Re: [sqlite] deleting dupicate rows

[James K. Lowden]

On Wed, 16 Apr 2014 16:27:01 +0100
Simon Slavin  wrote:

> If you really want to do it in the TABLE definition, use the SQLite
> shell tool to '.dump' the table as a set of SQL commands, edit the
> dump file to add that constraint, then use the SQLite tool to '.read'
> the SQL command file.

Why not just create the new table, then

insert into S select * from R;
drop table R;
alter table S rename to R;

or similar?  

If you're going to write out the data to a text file, wouldn't it be
faster to export the data to a delimited file and reload them
with .import? Why wrap them up in SQL?  

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

Re: [sqlite] cannot set connection while a datareader is active

[Joe Mistachkin]

Stefano Ravagni wrote:
> 
> Joe i cannot find the way for follow the command object properties you 
> saycould you tell me what and how to observe exactly please ? 
> 

You might need to add the System.Data.SQLite project(s) to the solution in
order
to be able to see full debugging information for them.

I recommend adding the "System.Data.SQLite.20XX.csproj" and
"SQLite.Interop.20XX.vc[x]proj" projects that correspond to the version of
Visual
Studio you are using.

Then, you'll want to reset the reference to point to the project instead of
the
built assembly.

--
Joe Mistachkin

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

Re: [sqlite] SQLite for single user data manipulation

[danap]

> Hello 'Liters!
>
> I'd like to move to SQLite from Postgres, but have two quick questions.
>
> I'm a social scientist looking to manipulate a large dataset (5 billion
> transactions, 700gb). I do not need multiple connections, and will only
> ever run one query at a time.
>
> I started in Postgres, but discovered that in Windows one cannot increase
> some of the per-query RAM memory caps above 2gb (I know -- I would love to
> switch operating systems, but it's beyond my control).  So I'm thinking of
> moving to SQLite.

So may I understand more fully.

1. If you perform a SELECT * FROM XXX_5B_ROW_TABLE
   you are unable to retrieve that result set becasue if exceeds per-query
RAM
   memory cap 2GB on Windows?

2. Or in trying to stuff that result set into a data structure you exceed the
   RAM allocation cap?

>
> Before I make the move, I was hoping you kind people could answer two quick
> questions for me:
>   -- am I going to have problems using all 16gb of ram on my Windows 8
> machine for data manipulations if I switch to SQLite? Or will SQLite set me
> free?

If 2. above I do not see how moving to SQLite is going to help you. As
indicated
below no tool is going to allow the manipulation of large datasets like
that in
memory. I have found in processing data that most software makes this
mistake.

The proper approach is to process that data in chunks for your visualation or
algorithmn.

>   -- Is there any reason I should NOT use SQLite for manipulation of large
> datasets like this (for example, pulling out unique pairs of transaction
> participants, averages across users, etc.)? All the literature I can find
> talks about SQL database choices for people setting up databases that will
> be queried by lots of people, and I just can't find any input for people
> like me who just want a data manipulation tool for data that's too big to
> read into RAM and manipulate with the usual suspects (R, Stata, Matlab,
> etc.).
>
> Thanks all!
> Nick

I have been working on a routine for the MyJSQLView project that could pull
a query from PostgreSQL and create a local database either in memory or file,
say SQLite, so that processing could take place for analysis.

The main reason this came about is because in processing large datasets from
a networked RDBS it became apparent that to speed up analysis it may be
easier
to have the query result stored locally in a memory or file database.

Dana M. Proctor
MyJSQLView Project Manager

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

[sqlite] WITHOUT ROWID problem and large number of row updating

[Joseph Yesselman]

Hi All,

I have two problems, the first one is probably very simple but I could not
find anything online telling me how to fix it. I would like to use the
WITHOUT ROWID statement as my primary key is a string in the format of 6
coordinates.

example:
"0.0 1.0 3.0 1.6 2.4 0.0"

I am using c++, but not using a wrapper even if I try the example given on
the website

char * sql = "CREATE TABLE IF NOT EXISTS wordcount(word TEXT PRIMARY KEY,
cnt INTEGER) WITHOUT ROWID;";

rc = sqlite3_exec(db, sql, callback, 0, );
if( rc != SQLITE_OK ){
  fprintf(stderr, "SQL error: %s\n", zErrMsg);
  sqlite3_free(zErrMsg);
}else{
  fprintf(stdout, "Table created successfully\n");
}

I get a SQL error near WITHOUT. I have tried going into sqlite3 and
creating the same table there without c++ and it works fine and I have
downloaded the most recent version of the c++ files from the website so I
really have no idea. I have created other tables just fine using the same
method.

My other problem is maybe more a of a methodological problem but I will
throw it out there. I am building a database of approximately 50-100gb of
data per c++ program run, after each round (~500 rounds) in my program I
need to go back and update my sqlite database, most of the data will be new
rows but still many previous rows will need to be updated. I am not sure
how efficiently update a large number of rows at once.

Here is my table definition:

sql = "CREATE TABLE two_way("   \
  "id VARCHAR(50) NOT NULL,"  \
  "names VARCHAR(2000),"  \
  "rs VARCHAR(3000),"  \
  "ds VARCHAR(2000)," \
  "sugs VARCHAR(2000), " \
  "PRIMARY KEY (id));";

When I need to update a row I need to take the previous values from
names,rs,ds and sugs and add the new values I have to the end of the
strings seperated by a ;.

example:
if names is currently "motif_1,motif_2;motif_5,motif6"
and the new data I have is "motif_10,motif_11", after the update i need
names to be:
"motif_1,motif_2;motif_5,motif6;motif_10,motif_11"

This same thing needs to be done for names,rs,ds and sugs.
*So sorry my questions is:*
Is there a way to do this for a large number of rows in a single
transaction, since I am currently doing it for one row per transaction and
its very very slow.

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

Re: [sqlite] cannot set connection while a datareader is active

[Stefano Ravagni]

Il 14/04/2014 21.12, Joe Mistachkin ha scritto:

Stefano Ravagni wrote:

Hello Joe, tryed to change the code in the IF expression but nothing
change
I would ask if you seen the screenshot i attached somes days ago because
it show what happen

  From debugger Datareader result CLOSED and HasRows properties result NO
ROWS, but i obtain the error wich say datareader is open !

The error happen at line were i set command connection properties as in
the code above...

   Try
  Call CloseDati() ' which call Dati.Close()

  objCmd.Connection = Connetti *** HERE HAPPEN THE ERROR

***

  objCmd.CommandText = strSQLvar
  Dati = objCmd.ExecuteReader()
  ...

  
The only thing that I can think of is that somehow the data reader being

closed
is not the same one associated with the command you are attempting to set
the
connection of.

Are you 100% sure that the "Dati" data reader is associated with the
"objCmd"
command?

--
Joe Mistachkin

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

I am thinking about one thing System.Data.Sqlite.Dll have abilities 
to return Multiple result sets or not ? could be this the motivation 
because SQlite is the only database who get me this error ? ... or this 
abilities is already supported ?

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

Re: [sqlite] SQLite for single user data manipulation

[Stephan Beal]

On Wed, Apr 16, 2014 at 6:13 PM, RSmith  wrote:

>
> On 2014/04/16 03:39, Nick Eubank wrote:
>
>> I started in Postgres, but discovered that in Windows one cannot increase
>> some of the per-query RAM memory caps above 2gb (I know -- I would love to
>> switch operating systems, but it's beyond my control).  So I'm thinking of
>> moving to SQLite.
>>
>
> Firstly, Windows isn't limiting the memory you can use, you can use all
> 16gb installed memory, and in fact much more if needed (which will just
> cause a lot of drive-swapping and slowness, but no less...), the 2gb limit
> is in fact a 32bit limit and is self imposed by any 32 bit application


See also: https://sqlite.org/limits.html


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite for single user data manipulation

[RSmith]

On 2014/04/16 03:39, Nick Eubank wrote:

I started in Postgres, but discovered that in Windows one cannot increase
some of the per-query RAM memory caps above 2gb (I know -- I would love to
switch operating systems, but it's beyond my control).  So I'm thinking of
moving to SQLite.


Firstly, Windows isn't limiting the memory you can use, you can use all 16gb installed memory, and in fact much more if needed 
(which will just cause a lot of drive-swapping and slowness, but no less...), the 2gb limit is in fact a 32bit limit and is self 
imposed by any 32 bit application (which I'm guessing postgress on your system is) or API or device context, etc, and even if it is 
compiled in 64bit guise it can still be self limiting by using 32bit UInts or whatever for the mapping. Also, the 32bit limit is in 
fact near 4gb, but the data transfer typically happen in unicode strings which in most cases are mapped to a 2-bytes-per-character 
memory which then translates to ~2gb.  Another way some systems limit to 2gb is because they use Integer mappers (as opposed to UInt 
mappers) which has a ~2gb upper limit.


Anyway, none of that matters, it is only information - what does matter is that sqlite is not bogged down by this in 64bit guise and 
you can even compile it into your own program (just to be sure), or use it as stand-alone or one of the pre-compiled binaries.


That said, it is hard to imagine the actual query using >2gb memory to simply execute, but I guess it is possible. One can usually 
rethink queries that are too big or too slow with some other coded algorithms or options, which makes sqlite quite handy as it is 
real quick at supplying a query line by line using the API.


Also there is a lot of tweaking that can be done with page-sizes and the like to enable storage and querying of insanely large DBs 
(of which your's certainly qualify!) - Documentation abound re this on the SQLite site.



   -- Is there any reason I should NOT use SQLite for manipulation of large
datasets like this (for example, pulling out unique pairs of transaction
participants, averages across users, etc.)?


I don't think SQLite supports quite the range of functions you may find in, say, PostGreSQL or MSSQL etc, but a lot of add-ons exist 
that can enable, for instance, Math functions, Statistical functions and RTree Relational structures etc. etc. - Might be worth 
seeing if the sort of thing you are exactly after is available (feel free to ask here).


The great thing with SQLite is that you can test all this in every detail free of charge and as much as you like to ensure you are 
on the right track, and you can ask here if anything is unclear.


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

Re: [sqlite] SQLite for single user data manipulation

[Simon Slavin]

On 16 Apr 2014, at 4:42pm, Dominique Devienne  wrote:

> On Wed, Apr 16, 2014 at 3:41 PM, Simon Slavin  wrote:
>> I would recommend that you download the SQLite shell tool and get to know it 
>> a little. Not only is it useful in creating a SQLite database from an SQL 
>> command dump or CSV files, but it allows you to test whether SQLite 
>> understands your SQL commands or not (you might be using a trick that works 
>> only in Postgres).  If something works in the shell tool but not in your own 
>> code, you know it's because of a bug in your own code.
> 
> I also recommend the shell tool.
> 
> But note that there are tons of GUI tools for SQLite as well, although
> I suspect many (all?) may not like 5B rows ;)

Indeed.  And GUI tools are frequently easier to use than command-line tools.  
But my reason for mentioning the Shell Tool specifically is more for debugging 
your code than for the things it does.  It is written and maintained by the 
same team who writes and maintains SQLite itself so it is a way to tell if 
something should or shouldn't work in SQLite.  If a SQL command works in the 
shell tool it is definitely legal and you should definitely get the same 
results from the same command in your code.  It can save hours of trial and 
error trying to figure out whether the problem is a misunderstanding of how 
SQLite works.

This is not true of other tools available for working with SQLite.  I have seen 
countless examples of things various tools can't do, or of incorrect results 
(including incorrect error messages) they can return.  Most of this isn't the 
result of bad programming, it's a natural consequence of trying to fit SQL to a 
GUI: the two don't really match up well, and the programmer naturally has to 
make simplifications and compromises.  Other problems arise because the 
programmer isn't working directly with the SQLite API but instead with a 
data-manipulation framework designed for their OS, and the framework does not 
implement SQLite well.

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

Re: [sqlite] SQLite for single user data manipulation

[Dominique Devienne]

On Wed, Apr 16, 2014 at 3:41 PM, Simon Slavin  wrote:
> I would recommend that you download the SQLite shell tool and get to know it 
> a little.  Not only is it useful in creating a SQLite database from an SQL 
> command dump or CSV files, but it allows you to test whether SQLite 
> understands your SQL commands or not (you might be using a trick that works 
> only in Postgres).  If something works in the shell tool but not in your own 
> code, you know it's because of a bug in your own code.

I also recommend the shell tool.

But note that there are tons of GUI tools for SQLite as well, although
I suspect many (all?) may not like 5B rows ;)

On Windows, I like SQLiteSpy, because it's simple and fast (Delphi
based I believe). It didn't have a problem with DBs in the millions of
rows (3-5M tops - a few 100s of MB) in my experience, but could be
that 5B is too much to handle.

In any case, if you do try SQLite GUI tools, and find one that works
with such large DBs as you work with, I'd be very interested if you
would share you experiences and share with all of us what works (or
not!) on this list.

TIA, --DD

PS: Also look at https://sqlite.org/limits.html and
http://www.sqlite.org/pragma.html. You can tune the page size of your
DB (from 512B, 1KB to 64KB), and the cache size SQLite uses to avoid
always reading stuff from disk. The default of 2,000 pages might be
too low for your DB sizes and 16GB of RAM. An fast disk (SSD) would
help too of course.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] deleting dupicate rows

[Simon Slavin]

On 16 Apr 2014, at 4:02pm, Christoph P.U. Kukulies  wrote:

> Am 16.04.2014 15:57, schrieb Richard Hipp:
>> 
>> CREATE UNIQUE INDEX version_idx1 ON version(major,minor,date);
> 
> Though this seems to work, could I achieve this also by a table constraint, 
> like UNIQUE(major,minor,date) ?

Yes.  In the CREATE TABLE command, after your list of columns but inside the 
same set of brackets, add

, CONSTRAINT con_1 UNIQUE (major,minor,date) ON CONFLICT IGNORE

> Can I apply that a posteriori to the table? I tried SQLite database browser 
> 2.0.b1 but can't figure out how.

No.  There's no way to add it once you have defined the table.  Which is 
probably why Dr Hipp suggested doing it as an INDEX.

If you really want to do it in the TABLE definition, use the SQLite shell tool 
to '.dump' the table as a set of SQL commands, edit the dump file to add that 
constraint, then use the SQLite tool to '.read' the SQL command file.

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

Re: [sqlite] deleting dupicate rows

[Christoph P.U. Kukulies]

Am 16.04.2014 15:57, schrieb Richard Hipp:

On Wed, Apr 16, 2014 at 9:46 AM, Christoph P.U. Kukulies
wrote:


Maybe been asked a hundred times but Im seeking for an elegant way to get
rid of duplicate rows which had been entered during development.
I have a database "versionen.sq3" having a table created by

CREATE TABLE version (major TEXT, minor TEST, date DATE)

Due to running across an iPython notebook several times it happened that I
now have every row occuring as duplicate around five times or so.

Two questions:

1.  would like to delete all duplicate rows by some SQL statement


DELETE FROM version WHERE rowid NOT IN
   (SELECT min(rowid) FROM version GROUP BY major, minor, date);



OK, fine. Worked perfectly.


2. defend myself against this happening again, that is, major,minor and
date may only occur one time in ther respective combination.


CREATE UNIQUE INDEX version_idx1 ON version(major,minor,date);


Though this seems to work, could I achieve this also by a table 
constraint, like UNIQUE(major,minor,date) ?
Can I apply that a posteriori to the table? I tried SQLite database 
browser 2.0.b1 but can't figure out how.



I think I'll have to create some UNIQUE Key. Also, when INSERTing I would
like to avoid error messages being thrown in case of a duplicate row coming
along.



--
Christoph

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

Re: [sqlite] deleting dupicate rows

[Richard Hipp]

On Wed, Apr 16, 2014 at 9:46 AM, Christoph P.U. Kukulies
wrote:

> Maybe been asked a hundred times but Im seeking for an elegant way to get
> rid of duplicate rows which had been entered during development.
> I have a database "versionen.sq3" having a table created by
>
> CREATE TABLE version (major TEXT, minor TEST, date DATE)
>
> Due to running across an iPython notebook several times it happened that I
> now have every row occuring as duplicate around five times or so.
>
> Two questions:
>
>1.  would like to delete all duplicate rows by some SQL statement
>

DELETE FROM version WHERE rowid NOT IN
  (SELECT min(rowid) FROM version GROUP BY major, minor, date);



>
>2. defend myself against this happening again, that is, major,minor and
> date may only occur one time in ther respective combination.
>

CREATE UNIQUE INDEX version_idx1 ON version(major,minor,date);



>
> I think I'll have to create some UNIQUE Key. Also, when INSERTing I would
> like to avoid error messages being thrown in case of a duplicate row coming
> along.
>
> Thanks.
>
> --
> Christoph
>
>
>
> ___
> 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] deleting dupicate rows

[Christoph P.U. Kukulies]

Maybe been asked a hundred times but Im seeking for an elegant way to 
get rid of duplicate rows which had been entered during development.

I have a database "versionen.sq3" having a table created by

CREATE TABLE version (major TEXT, minor TEST, date DATE)

Due to running across an iPython notebook several times it happened that 
I now have every row occuring as duplicate around five times or so.


Two questions:

   1.  would like to delete all duplicate rows by some SQL statement

   2. defend myself against this happening again, that is, major,minor 
and date may only occur one time in ther respective combination.


I think I'll have to create some UNIQUE Key. Also, when INSERTing I 
would like to avoid error messages being thrown in case of a duplicate 
row coming along.


Thanks.

--
Christoph



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

Re: [sqlite] Lock before beginning SELECT statement

[Simon Slavin]

On 15 Apr 2014, at 1:47pm, MikeD  wrote:

> If another thread deletes/updates or inserts while a SELECT statement is
> processing the results could
> be unpredictable?

Only if your timing is unpredictable.  If you know which command is issued 
first, you know what the results will be.

> Would issuing a BEGIN IMMEDIATE before a SELECT statement
> solve this?

Not necessary.  Even though a SELECT command only reads and database and 
doesn't change it, SQLite still wraps it in a transaction (BEGIN ... END) for 
you.  Another process trying to modify the database is made to wait until the 
SELECT is finished.

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

Re: [sqlite] SQLite for single user data manipulation

[Simon Slavin]

On 16 Apr 2014, at 2:39am, Nick Eubank  wrote:

>  -- am I going to have problems using all 16gb of ram on my Windows 8
> machine for data manipulations if I switch to SQLite? Or will SQLite set me
> free?

If you're talking about per-query memory cap, then that won't figure into 
anything SQLite does.  SQLite never needs to fit an entire index or result-set 
into memory at one time.  Only a single row or a chunk of rows is held in 
memory at one time.  Worth remembering that SQLite is used mostly in small 
devices with extremely limited memory (smartphones, embedded devices), so it is 
designed not to be a memory-hog.

The exception to the above is when you specifically declare an entire database 
or a table to be in memory rather than in a persistent storage device.  Which 
is sometimes done for temporary tables or where something needs to be done 
extremely quickly.

>  -- Is there any reason I should NOT use SQLite for manipulation of large
> datasets like this (for example, pulling out unique pairs of transaction
> participants, averages across users, etc.)? All the literature I can find
> talks about SQL database choices for people setting up databases that will
> be queried by lots of people, and I just can't find any input for people
> like me who just want a data manipulation tool for data that's too big to
> read into RAM and manipulate with the usual suspects (R, Stata, Matlab,
> etc.).

If you're doing most of your work in R or Matlab (I've never used Stata) then 
you'll probably be using just a thin SQLite 'shim' that gets your data into and 
out of variables.  It won't be a problem.

>From your description it's worth doing the experiment to see whether it's good 
>for you.  One of the reasons /not/ to use SQLite is that the user really needs 
>concurrent multi-user access which supports high-resolution locking, and you 
>don't have that problem.  Others can be found in the second half of this 
>document:



I would recommend that you download the SQLite shell tool and get to know it a 
little.  Not only is it useful in creating a SQLite database from an SQL 
command dump or CSV files, but it allows you to test whether SQLite understands 
your SQL commands or not (you might be using a trick that works only in 
Postgres).  If something works in the shell tool but not in your own code, you 
know it's because of a bug in your own code.



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

Re: [sqlite] cannot set connection while a datareader is active

[Stefano Ravagni]

Il 14/04/2014 21.12, Joe Mistachkin ha scritto:

Stefano Ravagni wrote:

Hello Joe, tryed to change the code in the IF expression but nothing
change
I would ask if you seen the screenshot i attached somes days ago because
it show what happen

  From debugger Datareader result CLOSED and HasRows properties result NO
ROWS, but i obtain the error wich say datareader is open !

The error happen at line were i set command connection properties as in
the code above...

   Try
  Call CloseDati() ' which call Dati.Close()

  objCmd.Connection = Connetti *** HERE HAPPEN THE ERROR

***

  objCmd.CommandText = strSQLvar
  Dati = objCmd.ExecuteReader()
  ...

  
The only thing that I can think of is that somehow the data reader being

closed
is not the same one associated with the command you are attempting to set
the
connection of.

Are you 100% sure that the "Dati" data reader is associated with the
"objCmd"
command?

--
Joe Mistachkin

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

Joe i cannot find the way for follow the command object properties you 
saycould you tell me what and how to observe exactly please ?

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

Re: [sqlite] Lock before beginning SELECT statement

[Igor Tandetnik]

On 4/15/2014 8:47 AM, MikeD wrote:

If another thread deletes/updates or inserts while a SELECT statement is
processing the results could
be unpredictable?


Is that other thread using the same connection as the SELECT, or a 
different connection? If the latter (two separate connections), than the 
problem fails to arise: the other thread will be blocked / get an error 
(in rollback journal mode), or will safely write to the journal while 
the SELECT reads original data from the main database (in WAL mode).


If the two threads use the same connection, then yes, the results of 
SELECT are unpredictable if another thread (or the same thread, in 
between sqlite3_step calls) modifies the data being iterated over.



Would issuing a BEGIN IMMEDIATE before a SELECT statement solve this?


No it will not. Each statement initiates an implicit transaction anyway, 
if one was not created explicitly.

--
Igor Tandetnik

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

[sqlite] SQLite for single user data manipulation

[Nick Eubank]

Hello 'Liters!

I'd like to move to SQLite from Postgres, but have two quick questions.

I'm a social scientist looking to manipulate a large dataset (5 billion
transactions, 700gb). I do not need multiple connections, and will only
ever run one query at a time.

I started in Postgres, but discovered that in Windows one cannot increase
some of the per-query RAM memory caps above 2gb (I know -- I would love to
switch operating systems, but it's beyond my control).  So I'm thinking of
moving to SQLite.

Before I make the move, I was hoping you kind people could answer two quick
questions for me:
  -- am I going to have problems using all 16gb of ram on my Windows 8
machine for data manipulations if I switch to SQLite? Or will SQLite set me
free?
  -- Is there any reason I should NOT use SQLite for manipulation of large
datasets like this (for example, pulling out unique pairs of transaction
participants, averages across users, etc.)? All the literature I can find
talks about SQL database choices for people setting up databases that will
be queried by lots of people, and I just can't find any input for people
like me who just want a data manipulation tool for data that's too big to
read into RAM and manipulate with the usual suspects (R, Stata, Matlab,
etc.).

Thanks all!

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

Re: [sqlite] Lock before beginning SELECT statement

[MikeD]

Is this true?
A SELECT statement acquires a SHARED lock which block writers anyway. 

Source:
http://sqlite.1065341.n5.nabble.com/Multiple-SELECTs-and-single-SELECT-and-TRANSACTION-td12752.html



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/TRANSACTIONs-tp23854p75091.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] Lock before beginning SELECT statement

[MikeD]

If another thread deletes/updates or inserts while a SELECT statement is
processing the results could
be unpredictable?  Would issuing a BEGIN IMMEDIATE before a SELECT statement
solve this?







--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/TRANSACTIONs-tp23854p75089.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] Lock before beginning SELECT statement

[MikeD]

SELECT automatically handles locking?


https://www.sqlite.org/lockingv3.html

If multiple commands are being executed against the same SQLite database
connection at the same time, the autocommit is deferred until the very last
command completes. For example, if a SELECT statement is being executed, the
execution of the command will pause as each row of the result is returned.
During this pause other INSERT, UPDATE, or DELETE commands can be executed
against other tables in the database. But none of these changes will commit
until the original SELECT statement finishes. 



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/TRANSACTIONs-tp23854p75092.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