Re: [sqlite] Insert not working for sqlite3

[Simon Slavin]

On 8 Jul 2011, at 2:50am, James_21th wrote:

> $dbh = new PDO('sqlite:db1.db');

Make sure "extension=php_pdo_sqlite.dll" is enabled in php.ini.  Do a find on 
'sqlite' in it and see if anything looks disabled.

Specify a full path for you database file, e.g.

sqlite:/opt/databases/mydb.sq3

Make sure that your Apache process has permission to create and write in that 
folder.  Possibly easiest to debug this problem by making a folder that 
everything can write to.

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

Re: [sqlite] Insert not working for sqlite3

[James_21th]

Dear all,

Thank you for all your valuable suggestion, it's good idea to put the whole 
script here, also good idea everytime to check the result, so I put the whole 
script here:
The $result always return false, it don't allow me to create a simple table, 
the 
table name surely not exist, I change tbl3, 4, 5, all not able to create. (that 
could be the same reason why insert is not working, if not even able to create 
table, let alone insert.)

query("create table tbl2 (one varchar(10),two varchar(10));");
var_dump($result);
?>



- Original Message 
From: Stephan Beal 
To: General Discussion of SQLite Database 
Sent: Thu, 7 July, 2011 9:18:07 PM
Subject: Re: [sqlite] Insert not working for sqlite3

On Thu, Jul 7, 2011 at 2:17 PM, Simon Slavin  wrote:

> For each operation you should test the $result and check to see it's
> SQLITE_OK.
>

Or enable exceptions and you don't have to check for errors (because they
will cause an exception).

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

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

Re: [sqlite] DROP TABLE yields SQLITE_LOCKED from user-defined function

[Pavel Ivanov]

> I'm assuming this is not a regular pattern that has an idiomatic workaround?

You are correct. With SQLite the only option you have is to call your
user-defined function directly from your host language.


Pavel


On Thu, Jul 7, 2011 at 12:43 PM, Ben Harper  wrote:
> OK.
>
> I'm assuming this is not a regular pattern that has an idiomatic workaround?
>
> Ben
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov
> Sent: 07 July 2011 05:18 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] DROP TABLE yields SQLITE_LOCKED from user-defined 
> function
>
>> Is there something special one needs to do in order to drop a table from 
>> inside a user-defined function?
>>
>> To test, I call it simply like so:
>> SELECT my_function();
>
> It shouldn't be ever possible to change the database from within a
> function called from SELECT statement. SELECT query doesn't ever
> changes any data.
>
>
> Pavel
>
>
> On Thu, Jul 7, 2011 at 10:38 AM, Ben Harper  wrote:
>> I have a user-defined function that I register with create_function.
>>
>> Inside my function I try to drop an r-tree virtual table, but I get the 
>> error SQLITE_LOCKED.
>>
>> Is there something special one needs to do in order to drop a table from 
>> inside a user-defined function?
>>
>> To test, I call it simply like so:
>> SELECT my_function();
>>
>> Thanks,
>> Ben
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Encryption Extension (SEE) and Public Domain Sqlite

[Black, Michael (IS)]

If you hash the username too you don't have that problem.

Passwords should always be a 1-way hash.

It's actually best to collapse username/password into a single one-way hash.

That way it's very difficult to crack it.

You can use a user "account#" value that users would have to track for support.





Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Simon Slavin [slav...@bigfraud.org]
Sent: Thursday, July 07, 2011 2:16 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] SQLite Encryption Extension (SEE) and Public Domain 
Sqlite


On 7 Jul 2011, at 7:41pm, Prakash Reddy Bande wrote:

> I wanted to get an understanding of SQLite Encryption Extension. I am 
> currently using sqlite-3.7.2, and if I want to use SEE, would it be equally 
> compatible. Well, thehttp://www.hwaci.com/sw/sqlite/see.html page does not 
> give much information, i.e. do still build sqlite from public and inject 
> encryption or it is a parallel code base.

SEE is delivered as C source code the same way SQLite is.  If you currently 
compile SQLite into your application you just remove the SQLite .c and .h 
files, put the SEE ones in their place, and recompile.  You can then ignore the 
extra functions entirely (it'll read unencrypted SQLite databases) or use the 
extra functions by C calls or PRAGMAs.

I'm not familiar with the release schedule for SEE.  I doubt it's far behind 
the current release of SQLite.

> Well, all I want to actually do is store passwords in a column and hence it 
> should be encrypted. Of course, alternatively I can do encryption for that 
> value in my application. But I guess it is a nice option to keep the entire 
> db encrypted so that users cannot explore and accidently mess with it using 
> sqlite CLI.

The SEE set will work very well for that.

The problem with encrypting just columns of a database is that it allows people 
to mess with it.  For example, suppose I want to break into someone's account 
and I can see their username and encrypted password in a row.  I copy the 
contents of my own password field into their password field.  Then I can log 
into their account using my password.

Encrypting the entire database prevents this sort of thing.

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 Encryption Extension (SEE) and Public Domain Sqlite

[Simon Slavin]

On 7 Jul 2011, at 7:41pm, Prakash Reddy Bande wrote:

> I wanted to get an understanding of SQLite Encryption Extension. I am 
> currently using sqlite-3.7.2, and if I want to use SEE, would it be equally 
> compatible. Well, thehttp://www.hwaci.com/sw/sqlite/see.html page does not 
> give much information, i.e. do still build sqlite from public and inject 
> encryption or it is a parallel code base.

SEE is delivered as C source code the same way SQLite is.  If you currently 
compile SQLite into your application you just remove the SQLite .c and .h 
files, put the SEE ones in their place, and recompile.  You can then ignore the 
extra functions entirely (it'll read unencrypted SQLite databases) or use the 
extra functions by C calls or PRAGMAs.

I'm not familiar with the release schedule for SEE.  I doubt it's far behind 
the current release of SQLite.

> Well, all I want to actually do is store passwords in a column and hence it 
> should be encrypted. Of course, alternatively I can do encryption for that 
> value in my application. But I guess it is a nice option to keep the entire 
> db encrypted so that users cannot explore and accidently mess with it using 
> sqlite CLI.

The SEE set will work very well for that.

The problem with encrypting just columns of a database is that it allows people 
to mess with it.  For example, suppose I want to break into someone's account 
and I can see their username and encrypted password in a row.  I copy the 
contents of my own password field into their password field.  Then I can log 
into their account using my password.

Encrypting the entire database prevents this sort of thing.

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

[sqlite] SQLite Encryption Extension (SEE) and Public Domain Sqlite

[Prakash Reddy Bande]

Hi,

I wanted to get an understanding of SQLite Encryption Extension. I am currently 
using sqlite-3.7.2, and if I want to use SEE, would it be equally compatible. 
Well, the http://www.hwaci.com/sw/sqlite/see.html page does not give much 
information, i.e. do still build sqlite from public and inject encryption or it 
is a parallel code base.

Well, all I want to actually do is store passwords in a column and hence it 
should be encrypted. Of course, alternatively I can do encryption for that 
value in my application. But I guess it is a nice option to keep the entire db 
encrypted so that users cannot explore and accidently mess with it using sqlite 
CLI.

Regards,

Prakash


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

Re: [sqlite] DROP TABLE yields SQLITE_LOCKED from user-defined function

[Igor Tandetnik]

On 7/7/2011 10:38 AM, Ben Harper wrote:
> I have a user-defined function that I register with create_function.
>
> Inside my function I try to drop an r-tree virtual table, but I get the error 
> SQLITE_LOCKED.
>
> Is there something special one needs to do in order to drop a table from 
> inside a user-defined function?

You can't change the database schema, from a user-defined function or 
otherwise, as long as there's any SELECT, INSERT, UPDATE or DELETE 
statement still in progress (one on which step was called but reset or 
finalize wasn't). Corollary: there's no way to drop a table from a 
user-defined function.
-- 
Igor Tandetnik

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

Re: [sqlite] DROP TABLE yields SQLITE_LOCKED from user-defined function

[Ben Harper]

OK.

I'm assuming this is not a regular pattern that has an idiomatic workaround?

Ben

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Pavel Ivanov
Sent: 07 July 2011 05:18 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] DROP TABLE yields SQLITE_LOCKED from user-defined function

> Is there something special one needs to do in order to drop a table from 
> inside a user-defined function?
>
> To test, I call it simply like so:
> SELECT my_function();

It shouldn't be ever possible to change the database from within a
function called from SELECT statement. SELECT query doesn't ever
changes any data.


Pavel


On Thu, Jul 7, 2011 at 10:38 AM, Ben Harper  wrote:
> I have a user-defined function that I register with create_function.
>
> Inside my function I try to drop an r-tree virtual table, but I get the error 
> SQLITE_LOCKED.
>
> Is there something special one needs to do in order to drop a table from 
> inside a user-defined function?
>
> To test, I call it simply like so:
> SELECT my_function();
>
> Thanks,
> Ben
> ___
> 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] multidimensional representation

[Kit]

2011/7/6 e-mail mgbg25171 :
>                                1990        1991  year   <= dimension
>
> north         sales        10            8
>                cogs         (5)           (4)
> south        sales         6             5
>                cogs         (2)           (1)
>
> region        line item   <==  dimensions

create table stat(region, item, year, value);
insert into stat values("north","sales",1990,10);
insert into stat values("north","sales",1991,8);
:
insert into stat values("south","cogs",1991,-1);

> I just want to be able to specify different dimensional values and get back
> rectangles of data that I can manipulate. I've already done the manipulation
> stuff so it's just how to layout the data.
> Query examples would be...
>
> total = north + south                             returning 9, 8
select region,sum(value) as total from stat group by region;

> total cogs = north.cogs + south.cogs     returning -7, -5
select year,sum(value) as total from stat where item="cogs" group by year;

> 1990                                                   returning 10,-5,6,-2
select region,item,value as total from stat where year=1990;

> north:1991                                           returning 8,-4
select item,value as total from stat where region="north" and year=1991;

> north:1990..1991                                  returning 10,8
select year,sum(value) as total from stat where region="north" and
item="sales" group by year;

> Once you've created region I don't think you can delete it and all it's
> entries cos that would delete everything i.e. I believe you have to leave at
> least one value in the column whether it be null or north.
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] multidimensional representation

[e-mail mgbg25171]

Luuk, Roger
Yes it is like a pivot table.
I was stuck on how you delete dimensions you decide you don't want anymore.
After a day thinking about it I think I've cracked it so...
Thanks very much for your assistance.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DROP TABLE yields SQLITE_LOCKED from user-defined function

[Pavel Ivanov]

> Is there something special one needs to do in order to drop a table from 
> inside a user-defined function?
>
> To test, I call it simply like so:
> SELECT my_function();

It shouldn't be ever possible to change the database from within a
function called from SELECT statement. SELECT query doesn't ever
changes any data.


Pavel


On Thu, Jul 7, 2011 at 10:38 AM, Ben Harper  wrote:
> I have a user-defined function that I register with create_function.
>
> Inside my function I try to drop an r-tree virtual table, but I get the error 
> SQLITE_LOCKED.
>
> Is there something special one needs to do in order to drop a table from 
> inside a user-defined function?
>
> To test, I call it simply like so:
> SELECT my_function();
>
> Thanks,
> Ben
> ___
> 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] DROP TABLE yields SQLITE_LOCKED from user-defined function

[Ben Harper]

I have a user-defined function that I register with create_function.

Inside my function I try to drop an r-tree virtual table, but I get the error 
SQLITE_LOCKED.

Is there something special one needs to do in order to drop a table from inside 
a user-defined function?

To test, I call it simply like so:
SELECT my_function();

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

[sqlite] Suboptimal code in createCollation()

[Dmitry]

Hey.

Currently createCollation() function in main.c is structured like this:

1. functions starts and immediately computes int nName = sqlite3Strlen30(zName);
2. then there's a ton oh checks some of those checks lead to early return
3. then there's if( (pColl->enc & ~SQLITE_UTF16_ALIGNED)==enc2 ) check and 
inside that "if" is the only use of "nName" value.

zName could not have been changed between computation of `nName` and use of 
`nName` so`nName` could just as well be computed right before use, not at the 
beginning of the function and that would save a couple of processor cycles in 
cases where control doesn't pass into that "if" body.

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

Re: [sqlite] Insert not working for sqlite3

[Stephan Beal]

On Thu, Jul 7, 2011 at 2:17 PM, Simon Slavin  wrote:

> For each operation you should test the $result and check to see it's
> SQLITE_OK.
>

Or enable exceptions and you don't have to check for errors (because they
will cause an exception).

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

Re: [sqlite] Insert not working for sqlite3

[Simon Slavin]

On 7 Jul 2011, at 6:52am, James_21th wrote:

> $dbh = new PDO('sqlite:test.db');
> $result=$dbh->query("INSERT INTO tbl1(one,two) VALUES ('new1','new2')");

Make one application which does

DROP table IF EXISTS
CREATE the table
Do the INSERT as above
Do a SELECT for that data

For each operation you should test the $result and check to see it's SQLITE_OK.

You do not need to specify a transaction (BEGIN/COMMIT).  SQLite will do it for 
you.

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

Re: [sqlite] System.Data.SQLite c#/Linq concurrent reads

[Benjamin Savs]

Thanks for the hint. Using a separate data context for the other thread
solves the problem. But it doesn't make the situation much better, because
the data context seem to have separate cashes. So it does not help to cash
stuff in the background, because it won't be ready for the main thread
anyways.
But I think I found an answer for my original question. Thanks!

On Thu, Jul 7, 2011 at 12:12, Joe Mistachkin  wrote:

>
> I do not see anything obviously wrong with using the same connection
> to construct two or more LINQ data contexts; however, my knowledge of
> LINQ and its use of threads is somewhat limited.
>
> --
> 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] Insert not working for sqlite3

[Black, Michael (IS)]

You're going to have to create a complete stand-alone example if you want us to 
debug your code for you.



Make a simple program that only does that one insert and share that with us.



Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of James_21th [james_2...@yahoo.com]
Sent: Thursday, July 07, 2011 3:09 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Insert not working for sqlite3

semicolon added and tried again, still no use.



- Original Message 
From: Michael Stephenson 
To: General Discussion of SQLite Database 
Sent: Thu, 7 July, 2011 3:46:35 PM
Subject: Re: [sqlite] Insert not working for sqlite3

What if you put a semicolon at the end of the query text?

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of James_21th
Sent: Thursday, July 07, 2011 3:32 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Insert not working for sqlite3

Yes, 100% sure, because I only have one copy on my PC, and the script which
can display the db's data also on the same directory.

I also tried below, but no error captured, just nothing happened.

try {
$result=$dbh->exec("INSERT INTO tbl1(one,two) VALUES ('new1','new2')"); }
catch(PDOExecption $e) { print "Error!: " . $e->getMessage() . ""; }




- Original Message 
From: Stephan Beal 
To: General Discussion of SQLite Database 
Sent: Thu, 7 July, 2011 2:27:34 PM
Subject: Re: [sqlite] Insert not working for sqlite3

On Thu, Jul 7, 2011 at 8:24 AM, James_21th  wrote:

> The table already created with some data inside, the existing data can
> be display use "select * from tbl1".


Are you 100% sure that the test.db being opened here is the same test.db
being used in the rest of he code (same directory)?

--
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
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] System.Data.SQLite c#/Linq concurrent reads

[Joe Mistachkin]

I do not see anything obviously wrong with using the same connection
to construct two or more LINQ data contexts; however, my knowledge of
LINQ and its use of threads is somewhat limited.

--
Joe Mistachkin

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

Re: [sqlite] Insert not working for sqlite3

[Jaco Breitenbach]

Are you missing a COMMIT perhaps?

On 7 July 2011 09:11, Stephan Beal  wrote:

> On Thu, Jul 7, 2011 at 9:31 AM, James_21th  wrote:
>
> > try {
> > $result=$dbh->exec("INSERT INTO tbl1(one,two) VALUES ('new1','new2')");
> > } catch(PDOExecption $e) {
> > print "Error!: " . $e->getMessage() . "";
> > }
> >
>
> PDO _only_ throws exceptions if you set the exceptions reporting option.
> Try
> instantiating it like this:
>
>$attr = array( PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION );
>$dbh = new PDO($dsn, "", "", $attr );
>
> --
> - stephan beal
> http://wanderinghorse.net/home/stephan/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] System.Data.SQLite c#/Linq concurrent reads

[Benjamin Savs]

I probably am using a single SQLiteDataReader, although I
never explicitly handle one.

context = new DataContext(_connection);

context.GetTable();

I do all queries on such a table object via Linq. Is there a way to
control the DataReader involved with such a query?


On Thu, Jul 7, 2011 at 11:06, Joe Mistachkin  wrote:

>
> Benjamin Savs wrote:
> 
> If I run Linq-queries from several threads I will get an exception that
> states
> "*DataReader has been closed*". Is this normal or should there be a way to
> issue
> concurrent reads?
> 
>
> Are you trying to share a single SQLiteDataReader object instance between
> threads
> or just the underlying database connection?
>
> The SQLiteDataReader class is not thread-safe and should not be shared
> between
> threads.
>
> --
> 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] System.Data.SQLite c#/Linq concurrent reads

[Joe Mistachkin]

Benjamin Savs wrote:

If I run Linq-queries from several threads I will get an exception that
states
"*DataReader has been closed*". Is this normal or should there be a way to
issue
concurrent reads?


Are you trying to share a single SQLiteDataReader object instance between
threads
or just the underlying database connection?

The SQLiteDataReader class is not thread-safe and should not be shared
between
threads.

--
Joe Mistachkin

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

[sqlite] Get extent of RTree quickly

[Felix Obermaier]

Hello everyone,

is there a faster way to get the spanning bounds of an RTree virtual table than 
querying its min/max values?

thanks

Felix Obermaier

--
Ingenieurgruppe IVV GmbH & Co. KG
Dipl.-Ing. Felix Obermaier
Oppenhoffallee 171
52066 Aachen

Telefon: +49 (241) 94691-39
Telefax: +49 (241) 531622
eMail: o...@ivv-aachen.de
Internet: http://www.ivv-aachen.de

Sitz der Gesellschaft: Aachen
Amtsgericht Aachen HRA 6212
GF: Bauassessor Dr.-Ing. Dieter Hölsken  IVV-Management GmbH  Amtsgericht 
Aachen HRB 12543

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

Re: [sqlite] FTS4 code from the website

[Ryan Henrie]

So, can anyone provide a working example of how this can work? Or, look 
at the code I have to figure out what I have wrong?  I'm still not 
getting anything to work.

To recap:

Overall, I'd like to be able to set up 2 *sqlite3* FTS databases.
One that holds quotes, with ranked search output, as the above
example is trying to do.

The other one is to hold entire books, with some minimal structure,
like 'Book / Volume or Section / Chapter / text' so that I could do
a query to show a list of all the books in the db, then when one is
selected, it would show all chapters in a clickable list on the left
side of a web page, with the text from a selected chapter on the
right.  Or be able to search, and have the results ranked, with the
output like this:

book / Chapter:
 Hit 1's paragraph text
Book / Chapter:
 Hit 2's paragraph text

I want the search results to be limited to either a specific
chapter, a specific book, or over the entire database, via select
boxes in the book/chapter list displayed on the left of a web page. 

Any help with the database side of this would be greatly appreciated.

Thanks,
Ryan

 Original Message 
Subject: Re: [sqlite] FTS4 code from the website
From: Dan Kennedy 
Date: 7/1/2011 7:33 AM
> On 06/30/2011 08:55 PM, Ryan Henrie wrote:
>> Even if I use the stock example from the web page, with only 2 columns,
>> and the exact schema from the example, I get the same result.
>>
>> I'm wondering if it is a bug in the example code on the website (ie the
>> source code has moved on, invalidating an example based on old code), or
>> it's just something in turning their c code example into a full
>> extension that I didn't do right.
>>
>>From the extension's source code:
>>
>>  nCol = aMatchinfo[1];
>>  if( nVal!=(*1+nCol*) ) goto wrong_number_args;
>>
>> So, it should scale with the number of columns.  (I would hope it's not
>> hardcoded to a set number of columns!)
> The page is a bit deceptive. The key phrase relating to the C code
> example is:
>
> "Instead of a single weight, it allows a weight to be externally
>  assigned to each column of each document."
>
> Making the C code function incompatible with the SQL example above
> it. The C code function would work with the example in its header
> comment:
>
>   CREATE VIRTUAL TABLE documents USING fts3(title, content);
>
>   SELECT docid FROM documents
>   WHERE documents MATCH
>   ORDER BY rank(matchinfo(documents), 1.0, 0.5) DESC;
>
> It wouldn't be real hard to adapt the C code so that it accepted
> a single weight argument like the hypothetical function in the
> SQL example above it.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] System.Data.SQLite c#/Linq concurrent reads

[Benjamin Savs]

Hello everybody,

I stumbled upon this mailing list via http://system.data.sqlite.org/ and
hope it is the right place to ask.
I'm doing queries with c# and linq using System.Data.SQLite and noticed that
it doesn't seem to be possible to do concurrent reads on the data. If I run
Linq-queries from several threads I will get an exception that states
"*DataReader
has been closed*". Is this normal or should there be a way to issue
concurrent reads?

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

Re: [sqlite] Insert not working for sqlite3

[Stephan Beal]

On Thu, Jul 7, 2011 at 9:31 AM, James_21th  wrote:

> try {
> $result=$dbh->exec("INSERT INTO tbl1(one,two) VALUES ('new1','new2')");
> } catch(PDOExecption $e) {
> print "Error!: " . $e->getMessage() . "";
> }
>

PDO _only_ throws exceptions if you set the exceptions reporting option. Try
instantiating it like this:

$attr = array( PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION );
$dbh = new PDO($dsn, "", "", $attr );

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

Re: [sqlite] Insert not working for sqlite3

[James_21th]

semicolon added and tried again, still no use.



- Original Message 
From: Michael Stephenson 
To: General Discussion of SQLite Database 
Sent: Thu, 7 July, 2011 3:46:35 PM
Subject: Re: [sqlite] Insert not working for sqlite3

What if you put a semicolon at the end of the query text?

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of James_21th
Sent: Thursday, July 07, 2011 3:32 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Insert not working for sqlite3

Yes, 100% sure, because I only have one copy on my PC, and the script which
can display the db's data also on the same directory. 
 
I also tried below, but no error captured, just nothing happened.
 
try {
$result=$dbh->exec("INSERT INTO tbl1(one,two) VALUES ('new1','new2')"); }
catch(PDOExecption $e) { print "Error!: " . $e->getMessage() . ""; }




- Original Message 
From: Stephan Beal 
To: General Discussion of SQLite Database 
Sent: Thu, 7 July, 2011 2:27:34 PM
Subject: Re: [sqlite] Insert not working for sqlite3

On Thu, Jul 7, 2011 at 8:24 AM, James_21th  wrote:

> The table already created with some data inside, the existing data can 
> be display use "select * from tbl1".


Are you 100% sure that the test.db being opened here is the same test.db
being used in the rest of he code (same directory)?

--
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
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] Insert not working for sqlite3

[Michael Stephenson]

What if you put a semicolon at the end of the query text?

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of James_21th
Sent: Thursday, July 07, 2011 3:32 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Insert not working for sqlite3

Yes, 100% sure, because I only have one copy on my PC, and the script which
can display the db's data also on the same directory. 
 
I also tried below, but no error captured, just nothing happened.
 
try {
$result=$dbh->exec("INSERT INTO tbl1(one,two) VALUES ('new1','new2')"); }
catch(PDOExecption $e) { print "Error!: " . $e->getMessage() . ""; }




- Original Message 
From: Stephan Beal 
To: General Discussion of SQLite Database 
Sent: Thu, 7 July, 2011 2:27:34 PM
Subject: Re: [sqlite] Insert not working for sqlite3

On Thu, Jul 7, 2011 at 8:24 AM, James_21th  wrote:

> The table already created with some data inside, the existing data can 
> be display use "select * from tbl1".


Are you 100% sure that the test.db being opened here is the same test.db
being used in the rest of he code (same directory)?

--
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
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] Insert not working for sqlite3

[James_21th]

Yes, 100% sure, because I only have one copy on my PC, and the script which can 
display the db's data also on the same directory. 
 
I also tried below, but no error captured, just nothing happened.
 
try {
$result=$dbh->exec("INSERT INTO tbl1(one,two) VALUES ('new1','new2')");
} catch(PDOExecption $e) {
print "Error!: " . $e->getMessage() . "";
}




- Original Message 
From: Stephan Beal 
To: General Discussion of SQLite Database 
Sent: Thu, 7 July, 2011 2:27:34 PM
Subject: Re: [sqlite] Insert not working for sqlite3

On Thu, Jul 7, 2011 at 8:24 AM, James_21th  wrote:

> The table already created with some data inside, the existing data can be
> display use "select * from tbl1".


Are you 100% sure that the test.db being opened here is the same test.db
being used in the rest of he code (same directory)?

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

Re: [sqlite] Insert not working for sqlite3

[Stephan Beal]

On Thu, Jul 7, 2011 at 8:24 AM, James_21th  wrote:

> The table already created with some data inside, the existing data can be
> display use "select * from tbl1".


Are you 100% sure that the test.db being opened here is the same test.db
being used in the rest of he code (same directory)?

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

Re: [sqlite] Insert not working for sqlite3

[James_21th]

The table already created with some data inside, the existing data can be 
display use "select * from tbl1".



- Original Message 
From: Michael Stephenson 
To: General Discussion of SQLite Database 
Sent: Thu, 7 July, 2011 1:59:40 PM
Subject: Re: [sqlite] Insert not working for sqlite3

I don't see something like a "create table tbl1(one, two);" statement.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of James_21th
Sent: Thursday, July 07, 2011 1:52 AM
To: General Discussion of SQLite Database
Subject: [sqlite] Insert not working for sqlite3

Dear all,
 
I'm able to connect to sqlite3 DB and display the data inside, but when
insert data, no matter how to try, just don't work, there's no error, but no
data inserted.
 
Below is the simple PDO SQL I'm using, table name is "tbl1", the two fields
name is just "one" & "two":
 
$dbh = new PDO('sqlite:test.db');
$result=$dbh->query("INSERT INTO tbl1(one,two) VALUES ('new1','new2')");
 
 
Any feed back will be greately appreciated!
 
Regards!
James
___
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] Insert not working for sqlite3

[Stephan Beal]

On Thu, Jul 7, 2011 at 7:52 AM, James_21th  wrote:

> Dear all,
>
> I'm able to connect to sqlite3 DB and display the data inside, but when
> insert
> data, no matter how to try, just don't work, there's no error, but no data
> inserted.
> ...
> Any feed back will be greately appreciated!
>

See:

http://www.php.net/manual/en/pdo.error-handling.php

and enable exception throwing. Then any errors will throw an exception and
the problem will be easy to track down.

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