Re: [sqlite] Mystery why SQLite will not work until System.Data.SQLite has been installed

[Doug Currie]

Paul Bainter wrote:

> >
> > Not sure what happened to this post previously, so I guess I'll try it
> > again with some additional information
>

GMail considered these messages spam for some reason. Check your spam
folder.

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

Re: [sqlite] Mystery why SQLite will not work until System.Data.SQLite has been installed

[Joe Mistachkin]

Paul Bainter wrote:
> 
> Not sure what happened to this post previously, so I guess I'll try it
> again with some additional information 
> 

It was posted to the list, see:

http://www.mail-archive.com/sqlite-users%40sqlite.org/msg79938.html

And my original reply, here:

http://www.mail-archive.com/sqlite-users%40sqlite.org/msg79976.html

Your previous message is here:

http://www.mail-archive.com/sqlite-users%40sqlite.org/msg79983.html

With my previous reply here:

http://www.mail-archive.com/sqlite-users%40sqlite.org/msg79985.html

I'm CC'ing you on this reply just in case you are not consistently getting
the list traffic; however, in the future you may wish to check the list
archives to be sure that you did not miss a reply (i.e. because it did not
end up in your inbox, etc).

--
Joe Mistachkin

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

[sqlite] Mystery why SQLite will not work until System.Data.SQLite has been installed

[Paul Bainter]

Not sure what happened to this post previously, so I guess I'll try it again
with some additional information

 

I'm wondering if there is a bug somehow in the "System.Data.SQLite" dll
file.  

 

When deploying my application to a clean Windows 7 x64 virtual machine
(VMWare Workstation 10), I got the message "Failed to find or load the
registered .NET Framework Data Provider" and of course with no database the
app would crash.  I then installed the file:
"sqlite-netFx45-setup-bundle-x86-2012-1.0.88.0.exe" (retrieved from your web
site) to this clean machine. I did NOT install it to the GAC which of course
also eliminated the Visual Studio Design stuff. At that point, I ran my app
again and it worked as expected.  I then went into the control panel and
uninstalled "System.Data.SQLite" and then tried running the application
again and it worked.  Quite a mystery there.  As always, I have this
following section in the App.config file and the App.config file is
appropriately named "appname.exe.config", which is one of the things that
Visual Studio does for me automatically.  I've also checked that particular
file just to make sure.

 





  

  





 

Seems like there is something that the
"sqlite-netFx45-setup-bundle-x86-2012-1.0.88.0.exe" installation file is
installing on the machine that is required for the database to be
recognized.  The application has the correct SQLite dll files which matched
the ones installed by the "System.Data.SQLite" installation file, so it's
not a missing dll, at least it's not missing "System.Data.SQLite.dll" or
"System.Data.SQLite.Linq.dll" as those are both put into the bin\debug
directory from the Visual Studio project. 

 

So in summary, my app does not work before installing System.Data.SQLite and
then works after installing System.Data.SQLite and continues to work after
uninstalling System.Data.SQLite.  I don't see this as a problem with the
App.config file as it has the correct entries per the instructions from the
installation.  Whatever help you can give me on this would be appreciated.

 

Sincerely,

Paul Bainter

 

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

Re: [sqlite] sqlite and Python

[Simon Slavin]

On 23 Oct 2013, at 7:33pm, David Simmons  wrote:

> The paths we follow are sometimes clouded with ego that prevent seeing the 
> obvious.  Making code changes to first find and then include an explicit path 
> to the sqlite database solved the problem.  Thanks for your help I can now 
> move forward.  

I am more than compensated by your Buddhist interpretation of a programming 
problem.

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

[sqlite] Android / Sqlite Scaffolding

[Kenny]

I'm not sure if this is the appropriate place to send this and if not,
please accept my humble apologies!

I slapped something together I think this community *might* be interested
in:

https://code.google.com/p/android-sqlite-scaffolding/

BSD license... (honestly I don't really care, if they had a 'public domain'
option I'd put it there but they didn't).

It's written in Java so as to run under any OS. I coded this in Win7 but if
it looks like crap in another OS, let me know and I'll see what I can do to
remedy that.

Basically this will generate a data access layer in a way similiar to LINQ
or SubSonic.
It's not perfect. My UI skills are pathetic, but it functions. I'm a .NET
guy so please forgive the conventions used. If there are more appropriate
conventions Java tends to have, I'll fix those so as to make things more
readable.

It works for me.. I'm open to opinions and will happily fix
misunderstandings.

Why do this? 2 weeks ago I had open heart surgery. 6 bypasses. I'm 30 years
old.
I'm.Bored.To.Tears.

So I wanted to write a medical app.. then realized that unless I wanted to
the db stuff all by hand I needed something like this... I'm lazy. So here
I am. Distracted by this side project. (hey! look! Squirrel!)

Again, if this is not the place to mention this -- I'm very sorry and the
mistake will not happen again -- I'm merely trying to contribute back to a
community.

Thank you for your time and I apologize if I have wasted it.

-- Kennith (Kenny) Mann
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SqliteDog released

[Sqlite Dog]

Thank you. We'll improve that.


2013/10/23 Donald Griggs 

> Congratulations to the team!
>
> Just a note in the spirit of helpfulness:
>  Note that one of the major headings on the website is misspelled
> "Purhase."There are numerous grammar errors in the website text.  I
> suggest you may want to have a native English speaker proof it.
>
>
> On Wed, Oct 23, 2013 at 12:21 PM, Sqlite Dog  wrote:
>
> > Hi,
> >
> > we are happy to announce a final release of SqliteDog: a modern SQLite
> > database manager.
> >
> > http://sqlitedog.com
> >
> >
> >
> > --
> > Regards,
> > SqliteDog support team
> > ___
> > 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
>



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

Re: [sqlite] Help with a Temp view issue

[Richard Hipp]

On Wed, Oct 23, 2013 at 2:34 PM, Vinay Boocha  wrote:

> Dear Rich,
>
> I try to communicate better this time.
>
> If I go by calls to my VFS then Yes. The subsequent SQL executions (same
> SQL ran multiple times) will not result in page fetches (only fetch is for
> offset=24 length=16).
>
> Here is the pseudo scenario:
>
>
>1. attach 'portfolio.db' as pdb;
>2. create temp view pnlview select pb,security,quantity,descr from
>security s, pdb.portfolio p where s.security=p.security
>3. Execute: select pb,sum(quantity) from pnlview group by pb
>   1. When this executed we get vfs read calls to pages in both main and
>   pdb datbases.
>4. Update pdb.portfolio in a separate component
>5. Repeat steps 3 and 4
>   1. When step 3 SQL is reran we get vfs read calls to pages in main
>   database.
>   2. and one read for portfolio on offset 24 with length 16.
>   3. The output of the SQL does not change even though portfolio is
>   updated in step4
>

The scenario above works correctly when I try it using an unmodified SQLite.

Perhaps your VFS is not returning the correct result for the read from
offset=24, length=16?

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

Re: [sqlite] sqlite and Python

[Stephan Beal]

On Wed, Oct 23, 2013 at 8:33 PM, David Simmons wrote:

> The paths we follow are sometimes clouded with ego that prevent seeing the
> obvious.  Making code changes to first find and then include an explicit
> path to the sqlite database solved the problem.  Thanks for your help I can
> now move forward.
>
> BTW - I could not isolated Error Code 255 to a specific cause - sort of a
> general "Failure Code".
>


i suspect the error code you are referring to is the one coming back from
the shell (via the python interpreter), not from sqlite3. A quick scan of
sqlite3.h doesn't reveal 255 as an error code (though there are some codes
created via bitshifts which i did not bother trying to decode). In that
case, there is no way to track it back except to read the docs for the tool
causing the error (in this case python). In my experience, 255 is often
associated with a segfault, but that's certainly not a law.

The golden rule is: check the result code of all operations where they
happen, not at the end of the program. If any op fails, all other
downstream results are undefined and their error codes will only confuse
the matter.

That said, i have no idea how the python sqlite API reports errors - maybe
it's throwing an exception, which causes python to abort with code 255, but
i'm just speculating.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"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] Help with a Temp view issue

[Vinay Boocha]

Dear Rich,

I try to communicate better this time.

If I go by calls to my VFS then Yes. The subsequent SQL executions (same
SQL ran multiple times) will not result in page fetches (only fetch is for
offset=24 length=16).

Here is the pseudo scenario:


   1. attach 'portfolio.db' as pdb;
   2. create temp view pnlview select pb,security,quantity,descr from
   security s, pdb.portfolio p where s.security=p.security
   3. Execute: select pb,sum(quantity) from pnlview group by pb
  1. When this executed we get vfs read calls to pages in both main and
  pdb datbases.
   4. Update pdb.portfolio in a separate component
   5. Repeat steps 3 and 4
  1. When step 3 SQL is reran we get vfs read calls to pages in main
  database.
  2. and one read for portfolio on offset 24 with length 16.
  3. The output of the SQL does not change even though portfolio is
  updated in step4



On Wed, Oct 23, 2013 at 1:01 PM, Richard Hipp  wrote:

> On Wed, Oct 23, 2013 at 1:50 PM, Vinay Boocha  wrote:
>
> > Dear Rich,
> >
> > Thanks for the response. I guessed it might need some cache but thought
> it
> > will refetch every time to ensure consistency.
> >
> > My main issue is that temp views are not picking up the update after the
> > pages of joined tables got updated (tables are updated by another actor
> in
> > the system).
> >
>
> I don't understand.  Manifested views should only last for a single SQL
> statement then be discarded.  Are you claiming that a manifested view is
> persisting past a single SQL statement?
>
> Maybe you can provide more details about your problem?
>
>
>
> >
> > Any way to notify the temp views to refetch the pages or always refetch
> > pages before executing a query?
> >
> > My VFS is performing a multi-master replication without the knowledge of
> > SQLite layer.
> >
> >
> > On Wed, Oct 23, 2013 at 12:25 PM, Richard Hipp  wrote:
> >
> > > On Wed, Oct 23, 2013 at 1:20 PM, Vinay Boocha 
> wrote:
> > >
> > > > Dear All,
> > > >
> > > > We are observing that temp views are using page cache even after
> > setting
> > > > the cache_size= 0. I am stuck debugging this issue for the last two
> > days.
> > > > Any pointers will be greatly appreciated.
> > > >
> > >
> > > Depending on the query and the schema, the view might need to be
> > evaluated
> > > ("manifested").  Where is that manifested view to be stored, if not in
> > the
> > > page cache?
> > >
> > > Why is SQLite using memory to help it answer a query an "issue" for
> you?
> > >
> > >
> > > >
> > > > When we run a SQL query multiple times on a temp view our custom vfs
> is
> > > > getting read call only for offset=24 and length=16. I believe its
> > > checking
> > > > if the file is updated or not.
> > > >
> > >
> > > Correct.  Just because the database is read-only to you doesn't mean it
> > is
> > > read-only to every other process in the system.
> > >
> > >
> > >
> > > >
> > > > *Details:*
> > > >
> > > >
> > > >1. Temp view is created by joining two tables. One of the table is
> > in
> > > an
> > > >attached database.
> > > >2. pragma cache_size= 0 is set immediately after opening the
> > > connection.
> > > >3. The cache_size zero is confirmed based on vfs calls for the
> table
> > > in
> > > >the main database.
> > > > ___
> > > > 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-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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite and Python

[David Simmons]

The paths we follow are sometimes clouded with ego that prevent seeing the 
obvious.  Making code changes to first find and then include an explicit path 
to the sqlite database solved the problem.  Thanks for your help I can now move 
forward.  

BTW - I could not isolated Error Code 255 to a specific cause - sort of a 
general "Failure Code".

David

On Oct 23, 2013, at 6:54 AM, Simon Slavin  wrote:

> 
> On 22 Oct 2013, at 11:00pm, David Simmons  wrote:
> 
>> I am becoming more and more frustrated when I attempt to create a local Mac 
>> app with py2app when sqlite3 database is being used.  The py2app creates the 
>> application, however; executing it immediately causes the app to exit with 
>> error code 255.  I've spent days searching the internet for a solution on 
>> how to get sqlite to work in a python application.  ANY HELP would be 
>> greatly appreciated.
> 
> 
> 1)
> Just for diagnostics purposes try specifying the full path to a database file 
> instead of just a filename.  Compiled apps may be defaulting to a different 
> path (perhaps one that doesn't exist).
> 
> 2)
> Assuming that you're using this interface:
> 
> 
> 
> Try getting 'sqlite3.sqlite_version' as referred to here:
> 
> 
> 
> before you open the database file.  Do you get a proper string back or an 
> error message ?
> 
> 3)
> What does error code 255 mean ?  It's not a SQLite code, it must be Python.  
> Use all your cleverness in trying to identify which line of code is causing 
> it.
> 
> 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] Help with a Temp view issue

[Richard Hipp]

On Wed, Oct 23, 2013 at 1:50 PM, Vinay Boocha  wrote:

> Dear Rich,
>
> Thanks for the response. I guessed it might need some cache but thought it
> will refetch every time to ensure consistency.
>
> My main issue is that temp views are not picking up the update after the
> pages of joined tables got updated (tables are updated by another actor in
> the system).
>

I don't understand.  Manifested views should only last for a single SQL
statement then be discarded.  Are you claiming that a manifested view is
persisting past a single SQL statement?

Maybe you can provide more details about your problem?



>
> Any way to notify the temp views to refetch the pages or always refetch
> pages before executing a query?
>
> My VFS is performing a multi-master replication without the knowledge of
> SQLite layer.
>
>
> On Wed, Oct 23, 2013 at 12:25 PM, Richard Hipp  wrote:
>
> > On Wed, Oct 23, 2013 at 1:20 PM, Vinay Boocha  wrote:
> >
> > > Dear All,
> > >
> > > We are observing that temp views are using page cache even after
> setting
> > > the cache_size= 0. I am stuck debugging this issue for the last two
> days.
> > > Any pointers will be greatly appreciated.
> > >
> >
> > Depending on the query and the schema, the view might need to be
> evaluated
> > ("manifested").  Where is that manifested view to be stored, if not in
> the
> > page cache?
> >
> > Why is SQLite using memory to help it answer a query an "issue" for you?
> >
> >
> > >
> > > When we run a SQL query multiple times on a temp view our custom vfs is
> > > getting read call only for offset=24 and length=16. I believe its
> > checking
> > > if the file is updated or not.
> > >
> >
> > Correct.  Just because the database is read-only to you doesn't mean it
> is
> > read-only to every other process in the system.
> >
> >
> >
> > >
> > > *Details:*
> > >
> > >
> > >1. Temp view is created by joining two tables. One of the table is
> in
> > an
> > >attached database.
> > >2. pragma cache_size= 0 is set immediately after opening the
> > connection.
> > >3. The cache_size zero is confirmed based on vfs calls for the table
> > in
> > >the main database.
> > > ___
> > > 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-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Help with a Temp view issue

[Vinay Boocha]

Dear Rich,

Thanks for the response. I guessed it might need some cache but thought it
will refetch every time to ensure consistency.

My main issue is that temp views are not picking up the update after the
pages of joined tables got updated (tables are updated by another actor in
the system).

Any way to notify the temp views to refetch the pages or always refetch
pages before executing a query?

My VFS is performing a multi-master replication without the knowledge of
SQLite layer.


On Wed, Oct 23, 2013 at 12:25 PM, Richard Hipp  wrote:

> On Wed, Oct 23, 2013 at 1:20 PM, Vinay Boocha  wrote:
>
> > Dear All,
> >
> > We are observing that temp views are using page cache even after setting
> > the cache_size= 0. I am stuck debugging this issue for the last two days.
> > Any pointers will be greatly appreciated.
> >
>
> Depending on the query and the schema, the view might need to be evaluated
> ("manifested").  Where is that manifested view to be stored, if not in the
> page cache?
>
> Why is SQLite using memory to help it answer a query an "issue" for you?
>
>
> >
> > When we run a SQL query multiple times on a temp view our custom vfs is
> > getting read call only for offset=24 and length=16. I believe its
> checking
> > if the file is updated or not.
> >
>
> Correct.  Just because the database is read-only to you doesn't mean it is
> read-only to every other process in the system.
>
>
>
> >
> > *Details:*
> >
> >
> >1. Temp view is created by joining two tables. One of the table is in
> an
> >attached database.
> >2. pragma cache_size= 0 is set immediately after opening the
> connection.
> >3. The cache_size zero is confirmed based on vfs calls for the table
> in
> >the main database.
> > ___
> > 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with a Temp view issue

[Richard Hipp]

On Wed, Oct 23, 2013 at 1:20 PM, Vinay Boocha  wrote:

> Dear All,
>
> We are observing that temp views are using page cache even after setting
> the cache_size= 0. I am stuck debugging this issue for the last two days.
> Any pointers will be greatly appreciated.
>

Depending on the query and the schema, the view might need to be evaluated
("manifested").  Where is that manifested view to be stored, if not in the
page cache?

Why is SQLite using memory to help it answer a query an "issue" for you?


>
> When we run a SQL query multiple times on a temp view our custom vfs is
> getting read call only for offset=24 and length=16. I believe its checking
> if the file is updated or not.
>

Correct.  Just because the database is read-only to you doesn't mean it is
read-only to every other process in the system.



>
> *Details:*
>
>
>1. Temp view is created by joining two tables. One of the table is in an
>attached database.
>2. pragma cache_size= 0 is set immediately after opening the connection.
>3. The cache_size zero is confirmed based on vfs calls for the table in
>the main database.
> ___
> 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] Help with a Temp view issue

[Vinay Boocha]

Dear All,

We are observing that temp views are using page cache even after setting
the cache_size= 0. I am stuck debugging this issue for the last two days.
Any pointers will be greatly appreciated.

When we run a SQL query multiple times on a temp view our custom vfs is
getting read call only for offset=24 and length=16. I believe its checking
if the file is updated or not.

*Details:*


   1. Temp view is created by joining two tables. One of the table is in an
   attached database.
   2. pragma cache_size= 0 is set immediately after opening the connection.
   3. The cache_size zero is confirmed based on vfs calls for the table in
   the main database.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SqliteDog released

[Donald Griggs]

Congratulations to the team!

Just a note in the spirit of helpfulness:
 Note that one of the major headings on the website is misspelled
"Purhase."There are numerous grammar errors in the website text.  I
suggest you may want to have a native English speaker proof it.


On Wed, Oct 23, 2013 at 12:21 PM, Sqlite Dog  wrote:

> Hi,
>
> we are happy to announce a final release of SqliteDog: a modern SQLite
> database manager.
>
> http://sqlitedog.com
>
>
>
> --
> Regards,
> SqliteDog support team
> ___
> 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] SqliteDog released

[Sqlite Dog]

Hi,

we are happy to announce a final release of SqliteDog: a modern SQLite
database manager.

http://sqlitedog.com



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

Re: [sqlite] Getting the row count when using the sqlite3 library

[Tim Streater]

On 23 Oct 2013 at 15:02, Simon Slavin  wrote: 

> On 23 Oct 2013, at 1:15pm, Tim Streater  wrote:
>
>> HmmmOK. I'm therefore making a second attempt to use the sqlite3 PHP
>> interface in my application instead of PDO. There will be about 35 modules to
>> alter so I hope I can work through any issues. 
>
> Sorry to put you to the work.  Might be worth doing a little test first before
> making all the changes in all the modules ?

I made a small library using PDO which encapsulates error handling, and a while 
ago I did the same for sqlite3 with the same interface (to the extent 
possible). I did some tests then and had some problems, can't remember what 
they were now, but enough to make me abandon the effort at the time. But I 
always wanted to get back to it.

>> One wrinkle is that with the PDO interface, I can fetchAll and get an array
>> of the rows returned from a SELECT, which allows me to count how many there
>> were. This allows me to make an early exit if there are none.
>
> You can, buy all means, write your own fetchAll function which reads all the
> rows and returns them as an array.  I have several apps which have such a
> function.  By the way, even though as far as your code is concerned the
> fetchAll allows you an early exit, it does actually have to fetch all the
> rows, so it's still doing all the work involved in reading all the rows from
> the database.

Yes. In fact I suspect that where I need to, what I'll do is try to fetch the 
first row, and if that fails I know I have no rows. Then I can quit there or 
reset() and proceed normally. I don't *always* need to do that.

About to start testing now, rework over modulo any typos and bugs. I suspect 
I'll have to do more stuff like finalise() and reset() which at present is 
being hidden for me in the PDO.

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

Re: [sqlite] Getting the row count when using the sqlite3 library

[Simon Slavin]

On 23 Oct 2013, at 1:15pm, Tim Streater  wrote:

> HmmmOK. I'm therefore making a second attempt to use the sqlite3 PHP 
> interface in my application instead of PDO. There will be about 35 modules to 
> alter so I hope I can work through any issues. 

Sorry to put you to the work.  Might be worth doing a little test first before 
making all the changes in all the modules ?

> One wrinkle is that with the PDO interface, I can fetchAll and get an array 
> of the rows returned from a SELECT, which allows me to count how many there 
> were. This allows me to make an early exit if there are none.

You can, buy all means, write your own fetchAll function which reads all the 
rows and returns them as an array.  I have several apps which have such a 
function.  By the way, even though as far as your code is concerned the 
fetchAll allows you an early exit, it does actually have to fetch all the rows, 
so it's still doing all the work involved in reading all the rows from the 
database.

> Of course, with PDO, that has internally involved fetching all the rows, 
> which I could do myself but that seems clumsy. If I need columns x, y, z, I 
> had wondered about doing:
> 
>  select count(*),x,y,z from sometable where …;
> 
> or is that a bad idea?

You could have two library routines.  One implements the SELECT you supply 
exactly as you supply it.  The other implements the SELECT but instead of 
fetching the values asked for, it only selects "count(*)".  In many cases this 
second routine should execute significantly faster than the one that reads 
actual values.

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

Re: [sqlite] sqlite and Python

[Simon Slavin]

On 22 Oct 2013, at 11:00pm, David Simmons  wrote:

> I am becoming more and more frustrated when I attempt to create a local Mac 
> app with py2app when sqlite3 database is being used.  The py2app creates the 
> application, however; executing it immediately causes the app to exit with 
> error code 255.  I've spent days searching the internet for a solution on how 
> to get sqlite to work in a python application.  ANY HELP would be greatly 
> appreciated.


1)
Just for diagnostics purposes try specifying the full path to a database file 
instead of just a filename.  Compiled apps may be defaulting to a different 
path (perhaps one that doesn't exist).

2)
Assuming that you're using this interface:



Try getting 'sqlite3.sqlite_version' as referred to here:



before you open the database file.  Do you get a proper string back or an error 
message ?

3)
What does error code 255 mean ?  It's not a SQLite code, it must be Python.  
Use all your cleverness in trying to identify which line of code is causing it.

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

Re: [sqlite] Getting the row count when using the sqlite3 library

[Clemens Ladisch]

Tim Streater wrote:
>   select count(*),x,y,z from sometable where …;
>
> or is that a bad idea?

An aggregate function prevents you from getting the individual records:

sqlite> create table sometable(x,y,z);
sqlite> insert into sometable values (1,2,3), (4,5,6);
sqlite> select count(*),x,y,z from sometable;
2|4|5|6

You could use UNION ALL to put the count into the first record:

sqlite> select count(*),null,null from sometable union all select x,y,z from 
sometable;
2||
1|2|3
4|5|6

... but then you could just as well use a separate query.


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

[sqlite] Getting the row count when using the sqlite3 library

[Tim Streater]

On 22 Oct 2013 at 09:11, Simon Slavin  wrote: 

> If you are writing code specifically intended for use with SQLite (like your
> library was meant to be) then please use PHP's sqlite3 library rather than the
> PDO.  The advantage of using PDO is that if you transfer from one DBMS to
> another, you only need to make minimal changes to your code.  But the
> disadvantage is that it doesn't understand how SQLite works very well, and
> cannot take advantage of the things it does well or return the results it
> produces well.  The PDO library is useful if you are producing one set of code
> that must work with lots of different SQL engines but because it's not native
> to any of them it is its own layer that needs separate understanding and
> debugging.
>
> PHP's sqlite3 library, on the other hand, contains just enough code to let you
> handle sqlite3 databases using conventional PHP calls and objects, rather than
> having to write C code.  It doesn't need much documentation because you just
> look up the equivalent functions in the SQLite3 documentation.  It can do
> everything that SQLite3 does, and provide all the information that SQLite3
> does.

HmmmOK. I'm therefore making a second attempt to use the sqlite3 PHP interface 
in my application instead of PDO. There will be about 35 modules to alter so I 
hope I can work through any issues. One wrinkle is that with the PDO interface, 
I can fetchAll and get an array of the rows returned from a SELECT, which 
allows me to count how many there were. This allows me to make an early exit if 
there are none. Of course, with PDO, that has internally involved fetching all 
the rows, which I could do myself but that seems clumsy. If I need columns x, 
y, z, I had wondered about doing:

  select count(*),x,y,z from sometable where …;

or is that a bad idea?

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

[sqlite] sqlite and Python

[David Simmons]

I am becoming more and more frustrated when I attempt to create a local Mac app 
with py2app when sqlite3 database is being used.  The py2app creates the 
application, however; executing it immediately causes the app to exit with 
error code 255.  I've spent days searching the internet for a solution on how 
to get sqlite to work in a python application.  ANY HELP would be greatly 
appreciated.

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