Re: [sqlite] OT!!!!! Understanding the WITH clause

2019-06-19 Thread Sam Carleton
fter such an "interesting" day. Pax vobiscum, Sam Carleton On Tue, Jun 18, 2019 at 2:57 PM James K. Lowden wrote: > On Mon, 17 Jun 2019 20:46:41 -0400 > Sam Carleton wrote: > > > It is my view that peace is not something that can be defined with > > some words

[sqlite] nested set tree: how to change order of one node?

2019-06-18 Thread Sam Carleton
-84a4-56681320fb7d' and ChildOID <> '98f13b01-3936-44b0-84a4-56681320fb7d' order by ChildName Pax vobiscum, Sam Carleton ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/

Re: [sqlite] Understanding the WITH clause

2019-06-17 Thread Sam Carleton
ps me keep myself in check to make sure the context I put in that email is one of peace. Let me tell ya, more than once I have written an email, looked at my salutation and gone back to seek a more peaceful tone. Pax vobiscum, Sam Carleton On Sun, Jun 16, 2019 at 1:33 PM E.Pasma wrote: > > &

[sqlite] Understanding the WITH clause

2019-06-15 Thread Sam Carleton
re b.part = frammis.part ) then ( select coalesce (sum(c.wgt*c.qty), b.wgt) from frammis as c left outer join frammis as b on b.lft = (select max(S.lft) from frammis as s where c.lft > s.lft and c.lft < s.rgt) where b.part = frammis.part ) else frammis.wgt end Pax vobiscu

Re: [sqlite] Understanding the WITH clause

2019-06-15 Thread Sam Carleton
qty, wgt, lft, rgt) VALUES ('k', 5, 3, 22, 23); INSERT INTO frammis (part, qty, wgt, lft, rgt) VALUES ('l', 1, 4, 24, 25); INSERT INTO frammis (part, qty, wgt, lft, rgt) VALUES ('m', 2, 7, 11, 12); INSERT INTO frammis (part, qty, wgt, lft, rgt) VALUES ('n', 3, 2, 13, 14); Pax vobiscum, Sam Carleto

[sqlite] Understanding the WITH clause

2019-06-15 Thread Sam Carleton
re b.part = frammis.part ) then ( select coalesce (sum(c.wgt*c.qty), b.wgt) from frammis as c left outer join frammis as b on b.lft = (select max(S.lft) from frammis as s where c.lft > s.lft and c.lft < s.rgt) where b.part = frammis.part ) else frammis.wgt end Pax vobiscu

[sqlite] Fwd: Best way to set default PRAGMA's in System.Data.SQLite

2019-06-09 Thread Sam Carleton
such as myself won't exist if it wasn't for the folks like you. Thank you, I truly appreciate it! Pax vobiscum, Sam Carleton -- Forwarded message - From: Joe Mistachkin Date: Sat, Jun 8, 2019 at 11:48 PM Subject: Re: [sqlite] Best way to set default PRAGMA's in System.Data.SQLite To: SQLite

Re: [sqlite] Best way to set default PRAGMA's in System.Data.SQLite

2019-06-08 Thread Sam Carleton
On Sat, Jun 1, 2019 at 7:35 PM Joe Mistachkin wrote: > > compile the System.Data.SQLite assembly in such a way that it uses a > "standard" SQLite > DLL, via setting the following MSBuild properties: > > /p:UseSqliteStandard=true /p:UseInteropDll=false > Joe, I am trying out this

[sqlite] compiling System.Data.SQLite and using it across VS versions...

2019-06-08 Thread Sam Carleton
put that into the SQLite.Interop.2015 project, correct? Thanks for your help Pax vobiscum, Sam Carleton ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Best way to set default PRAGMA's in System.Data.SQLite

2019-06-01 Thread Sam Carleton
that came with System.Data.SQLite? Pax vobiscum, Sam Carleton On Sat, Jun 1, 2019 at 1:47 PM Simon Slavin wrote: > On 1 Jun 2019, at 4:34pm, Sam Carleton wrote: > > > What I cannot find is what to set to enable foreign keys and disable > > recursive triggers. > > Only fla

[sqlite] Best way to set default PRAGMA's in System.Data.SQLite

2019-06-01 Thread Sam Carleton
how to separate multiple values, is it a comma, or some other separator? My current guess is: DefaultFlags_SQLiteConnection="recursive_trigger=0,foreign_keys=1" Is there documentation on this somewhere and I am just missing it? Pax vobiscum, Sa

[sqlite] Understanding conditional triggers

2016-03-04 Thread Sam Carleton
> > Does this trigger not compute, is not accepted, or does not work once > installed? What is the problem exactly? > It works fine. Initially I was not able to find any good examples of a conditional trigger but after a lot more searching I was able to find one that validated that it might

[sqlite] Understanding conditional triggers

2016-03-02 Thread Sam Carleton
vobiscum, Sam Carleton

[sqlite] creating trigger to handle multiple types of insert

2014-11-26 Thread Sam Carleton
There are two different types of insert that need to happen into a table, one is where the rowid (EventNodeId) is part of the actual insert, the other it is excluded so that AUTOINCREMENT will fill it in. There is an insert triggers to set the audit fields on the table during the insert. The

Re: [sqlite] Unable to prepare a statement

2014-10-16 Thread Sam Carleton
Yes, that was the case. The app has two databases, a system wide DB and the active data DB. The system points to the active data DB but wasn't configured to point to the active data. Live and learn:) Pax vobiscum, Sam Carleton On Wed, Oct 15, 2014 at 6:01 AM, Dan Kennedy <danie

[sqlite] Unable to prepare a statement

2014-10-14 Thread Sam Carleton
is opening the DB is in a base class which is used other places to access the EventNode table, so I am a bit mystified as to what exactly is going on. Any thoughts? -- Pax vobiscum, Sam Carleton ___ sqlite-users mailing list sqlite-users@sqlite.org http

Re: [sqlite] Inserting from another table... (resolved)

2012-07-06 Thread Sam Carleton
Folks, I would like to thank one and all, I think this was a group effort. I changed the double quotes to single, changed the value into a select and then just ran the select part and found one NULL filed because I had the wrong category. Fixed that and left it as a select and all is well! Thank

Re: [sqlite] Inserting from another table...

2012-07-06 Thread Sam Carleton
> and c.ItemName = "EnableCarts" > ... > and a.? = b.? > and a.? = c.? > ... > ; > > Question marks here is the field which value should identify what row > particular ItemName should go to. > > > Pavel > > > On Thu, Jul 5, 2012 at 11:03 PM, Sam

[sqlite] Inserting from another table...

2012-07-05 Thread Sam Carleton
I am working on converting my system table from one form to another. The old form was one row per value with a category/key/value (DBLookup) , the new form is a separate column for each value (PP_VIEWER_SETTINGS). I am trying to create an insert statement to run when the new table is created,

Re: [sqlite] how to disable a trigger

2011-10-03 Thread Sam Carleton
to the document db, the triggers need to be off. Sam On Mon, Oct 3, 2011 at 4:51 AM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 3 Oct 2011, at 4:12am, Sam Carleton wrote: > > > Ok, how do I list what a trigger is so that I can add it back once I want > to > > "react

Re: [sqlite] how to disable a trigger

2011-10-02 Thread Sam Carleton
Ok, how do I list what a trigger is so that I can add it back once I want to "reactive" it? On Sun, Oct 2, 2011 at 9:07 PM, Igor Tandetnik <itandet...@mvps.org> wrote: > Sam Carleton <scarle...@miltonstreet.com> wrote: > > Is there any way to "disable&qu

[sqlite] how to disable a trigger

2011-10-02 Thread Sam Carleton
Is there any way to "disable" a trigger in sqlite? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does coalesce terminate early?

2011-09-15 Thread Sam Carleton
On Thu, Sep 15, 2011 at 12:24 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 15 Sep 2011, at 5:00pm, Sam Carleton wrote: > >> I don't mean to be difficult, but I simply don't get any indication of >> how exactly COALESCE actually functions from this descr

Re: [sqlite] Does coalesce terminate early?

2011-09-15 Thread Sam Carleton
On Thu, Sep 15, 2011 at 10:05 AM, Simon Slavin wrote: > > Documentation for COALESCE is here: > > http://www.sqlite.org/lang_corefunc.html > > It does not say whether it does short-circuit evaluation but the description > does imply testing one by one, rather than

Re: [sqlite] Does coalesce terminate early?

2011-09-15 Thread Sam Carleton
On Thu, Sep 15, 2011 at 6:38 AM, Richard Hipp <d...@sqlite.org> wrote: > On Wed, Sep 14, 2011 at 9:03 PM, Sam Carleton <scarle...@miltonstreet.com > >wrote: > > > Forgive me, fore I have forgotten the term used to describe the behavior > if > > a C i

[sqlite] Does coalesce terminate early?

2011-09-14 Thread Sam Carleton
Forgive me, fore I have forgotten the term used to describe the behavior if a C if statement where it stops executing on the first false statement, but... Does coalesce do that? I have to put together a query that has a coalesce such that if the row from the table is null, it then does a

[sqlite] Compound update isn't working as I expect

2011-07-03 Thread Sam Carleton
It is very clear to me that my expectations are wrong, please enlighten me... Here is the query: update INVOICE set SUB_TOTAL = (select sum(PRICE * QTY) from INVOICE_ITEM ii where ii.INVOICE_ID = *INVOICE_ID*), TAX = (select round( sum(PRICE * QTY) * ( 8.25 / 100) + .005, 2) from

[sqlite] optimizing an update

2011-07-03 Thread Sam Carleton
I have a select statement that is calculating the subTotal, Tax, and grand total: UPDATE Invoice SET Sub_Total = (select sum(PRICE * QTY) from INVOICE_ITEM where INVOICE_ID = @invoiceId), Tax = (select round( sum(PRICE * QTY) * ( @tax / 100) + .005, 2) from INVOICE_ITEM where INVOICE_ID

Re: [sqlite] Howto pivot in SQLite

2011-06-06 Thread Sam Carleton
release. There is a lot to come, I just need to get this out the door as quickly as possible while providing useful information to my users. Sam On Sun, Jun 5, 2011 at 8:17 PM, BareFeetWare <list@barefeetware.com>wrote: > On 06/06/2011, at 8:30 AM, Sam Carleton <scarle...@milt

Re: [sqlite] Howto pivot in SQLite

2011-06-05 Thread Sam Carleton
On Sun, Jun 5, 2011 at 5:44 PM, Jay A. Kreibich <j...@kreibi.ch> wrote: > On Sun, Jun 05, 2011 at 12:47:47PM -0400, Sam Carleton scratched on the > wall: > > > In one select statement, I want to return a view of all the Invoice_Items > > for a particular Invoice su

Re: [sqlite] Howto pivot in SQLite

2011-06-05 Thread Sam Carleton
On Sun, Jun 5, 2011 at 1:04 PM, Simon Slavin wrote: > > Take a look at the group_concat() function: > > http://www.sqlite.org/lang_aggfunc.html > That is PERFECT, thank you! If the person who thought of this function originally is reading this, thank you!!! What a time

[sqlite] Howto pivot in SQLite

2011-06-05 Thread Sam Carleton
I have a invoice system where one invoice item can have one or more sum items (images). Example is a CD... The invoice item is a CD, there are an infinite numbers of images associated with that CD invoice item. So I have the following: CREATE TABLE Invoice_Item ( Invoice_Item_Id INTEGER

[sqlite] handling SQLITE_LOCKED same as SQLITE_BUSY

2011-05-19 Thread Sam Carleton
I just did a quick load test on my little web app that is using SQLite in C code.  Pretty quick it ran into a SQLITE_LOCKED while a connection/user was logging in to the site.  I have some code that responds to SQLITE_BUSY by sleeping for 50 mills and retrying 4 times: int rc =

Re: [sqlite] Determining how many columns were returned in a query

2011-05-08 Thread Sam Carleton
On Sun, May 8, 2011 at 3:08 PM, Simon Slavin wrote: > > Out of interest, are you trying to analyse the results of a "SELECT *" ? > Because since it's your query in the first place, you should know what > columns you asked for. > Nope, I NEVER do SELECT *, very, very

Re: [sqlite] Determining how many columns were returned in a query

2011-05-08 Thread Sam Carleton
On May 8, 2011, at 11:06 AM, "Jay A. Kreibich" <j...@kreibi.ch> wrote: > On Sun, May 08, 2011 at 11:00:29AM -0400, Sam Carleton scratched on the wall: > >> Is there a way to find out the id of a particular column? > > sqlite3_column_name() I want to go the ot

Re: [sqlite] Determining how many columns were returned in a query

2011-05-08 Thread Sam Carleton
On May 8, 2011, at 12:53 PM, "Igor Tandetnik" <itandet...@mvps.org> wrote: > Sam Carleton <scarle...@gmail.com> wrote: >> I had it wrong in the email body, I meant how many columns are in query? > > sqlite3_column_count. Don't even need to execute the qu

Re: [sqlite] Determining how many columns were returned in a query

2011-05-08 Thread Sam Carleton
On May 8, 2011, at 11:09 AM, Jean-Christophe Deschamps wrote: > >> How does one go about finding out how many rows a query returns? > > This is the number of time sqlite3_step can be called successfully > until it returns SQLITE_DONE. I had it wrong in the email body, I

[sqlite] Determining how many columns were returned in a query

2011-05-08 Thread Sam Carleton
How does one go about finding out how many rows a query returns? Is there a way to find out the id of a particular column? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Understanding a non trivial query plan

2011-04-27 Thread Sam Carleton
I am trying to track down an issue that I MIGHT have with the following query. The explain query is a bit complex and I cannot tell if it is OK or if there are issues. Here it is: sqlite> EXPLAIN QUERY PLAN ...> SELECT DISTINCT f1.FolderId, f1.ImageId, ...>

Re: [sqlite] how to reuse a prepared statement

2011-04-23 Thread Sam Carleton
On Fri, Apr 22, 2011 at 11:44 PM, Drake Wilson wrote: > You probably need to sqlite3_reset the statement after stepping it. Can someone then explain the purpose of sqlite3_clear_bindings()? If I understand things correctly, you call sqlite3_reset() to reuse a prepaired

[sqlite] how to reuse a prepaired statement

2011-04-22 Thread Sam Carleton
I am implementing a dataset update process.  There is a for loop going through the dataset either doing an update on the row or deleting the row.  So there are two statements that are preparied, currently I am using the same basic logic for both. The problem is the second time around I get a

[sqlite] primary key on two columns of an associative table

2011-03-28 Thread Sam Carleton
The system calls for an associative table, a table with two foriegn keys to two other tables, allowing for a many to many relationship. Is there any way to make the primary key be both the columns? CREATE TABLE Invoice_Item_Favorite( Invoice_Item_Id INTEGER, FavoriteId INTEGER,

[sqlite] sqlite3_step behavior on empty set

2011-03-27 Thread Sam Carleton
Is my impression correct that when calling sqlite3_step() on a query that returns no rows, the result will be [SQLITE_DONE]? If that is the case, might that be added to the documentation? ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] adding/dropping foreign key to existing table

2011-03-23 Thread Sam Carleton
On Wed, Mar 23, 2011 at 2:00 PM, Nico Williams wrote: > Also, just to be clear, making the schema writable and then making any > updates to sqlite_master is completely unsupported, and should be. > This is good, very good, IMHO. Which is also why I won't do it:) hehehehe

Re: [sqlite] adding/dropping foreign key to existing table

2011-03-23 Thread Sam Carleton
On Wed, Mar 23, 2011 at 12:20 PM, Nico Williams wrote: > I do think that SQLite3 will eventually need to grow ALTER support for > altering constraints. This whole copy-the-table thing is not really a > scalable solution. Without such ALTER functionality users will often >

Re: [sqlite] adding/dropping foreign key to existing table

2011-03-23 Thread Sam Carleton
On Tue, Mar 22, 2011 at 10:55 PM, BareFeetWare wrote: > > You have to drop the old table and create a new one with the changed > foreign keys. This is a bummer. Is there any desire/plan to add an alter feature, in the future? > > Also, from a performance

[sqlite] adding/dropping foreign key to existing table

2011-03-22 Thread Sam Carleton
I don't see any examples on http://www.sqlite.org/foreignkeys.html how to either add or drop a foreign key to an existing table. What might that syntax look like exactly? Also, from a performance perspective, is there an advantage to using a foreign key in SQLite verses just an index? (aka, is

Re: [sqlite] SQLite IDE's

2011-03-22 Thread Sam Carleton
On Tue, Mar 22, 2011 at 4:02 PM, Ben wrote: > You don't mention which platform you're on, but for OS X there's a good > comparison table of SQLite editors here: > > http://www.barefeetware.com/sqlite/compare/?ml Ben, I have a Mac, but I am currently targetting

Re: [sqlite] SQLite IDE's

2011-03-22 Thread Sam Carleton
On Tue, Mar 22, 2011 at 3:26 PM, Jonathan Allin wrote: > Would the diff have to do more than compare (in some nice graphical way) the > two sqlite_master tables? What I am looking for is this: I have one version of the DB out in the field, I have made changed to it

[sqlite] SQLite IDE's

2011-03-22 Thread Sam Carleton
I am looking for a good SQLite IDE, SQLite Maestro looks like a good candidate with most all the features I need. The price is good, too. The one feature I don't see is a tool that can do a diff on the DDL of two SQLite db's. Does anyone know of any other SQLite IDE's that have that ability?

[sqlite] no such function: sqlite_attach?

2011-03-07 Thread Sam Carleton
Ok, a few weeks ago I posted an issue that I was having with the attach commend because the path has a single quote in it. Someone suggested I use the sqlite_attach() function which allegedly can be parametrized. Well, I finally got around to it this evening but I am missing something. Here is

Re: [sqlite] apostrophes in strings...

2011-02-23 Thread Sam Carleton
; ** > ** SELECT sqlite_attach(x, y, z) > ** > ** If the optional "KEY z" syntax is omitted, an SQL NULL is passed as the > ** third argument. > */ > > -- > -- > -- > --ΞΞ-- > ô¿ô¬ > K e V i N > /¯\

Re: [sqlite] apostrophes in strings...

2011-02-22 Thread Sam Carleton
On Mon, Feb 21, 2011 at 9:42 AM, Sam Carleton <scarle...@miltonstreet.com>wrote: > On Sun, Feb 20, 2011 at 4:11 PM, Scott Hess <sh...@google.com> wrote: > >> You can also convert: >> ATTACH DATABASE x AS y KEY z >> to: >> SELECT sqlite_attach(x, y

[sqlite] apostrophes in strings...

2011-02-20 Thread Sam Carleton
This is a bit crazy and I know the ideal way would be to not allow the apostrophy in the first place but, my focus is easy of use for my customers, as compared to easy for me... I had a customer that saved their SQLite database here: D:/My Events/President's Day/event.sqlite My software uses

[sqlite] wal and shm files

2011-02-11 Thread Sam Carleton
Just wondering, are the wal and shm files suppose to stick around after the process exits? Sam ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] upgrading DB from 3.6.23 to 3.7.5

2011-02-11 Thread Sam Carleton
com> wrote: > On 02/11/2011 08:08 PM, Sam Carleton wrote: > > On Fri, Feb 11, 2011 at 6:54 AM, Philip Graham Willoughby< > > phil.willoug...@strawberrycat.com> wrote: > > > >> Hi Sam, > >> > >> On 11 Feb 2011, at 05:29, Sam Carleto

Re: [sqlite] upgrading DB from 3.6.23 to 3.7.5

2011-02-11 Thread Sam Carleton
On Fri, Feb 11, 2011 at 6:54 AM, Philip Graham Willoughby < phil.willoug...@strawberrycat.com> wrote: > Hi Sam, > > On 11 Feb 2011, at 05:29, Sam Carleton wrote: > > I am sure it is bad form, but attached is one of the 3.6.23 DB, it is > only > > 12K. > &

[sqlite] upgrading DB from 3.6.23 to 3.7.5

2011-02-10 Thread Sam Carleton
I am in the process of upgrading my app from using SQLite.net w/ 3.6.23 to SQLite.net w/ 3.7.5. When the .Net program starts in a fresh install state, aka no system db exists and it builds one up via SQL script all works fine. When it opens an existing 3.6.23 system db, it gets a database lock.

[sqlite] Where to find amalgamation source for the 3.6.23.1

2011-02-09 Thread Sam Carleton
I hacked the 3.6.23.1 amalgamation code a while back. I need to move those hacks to 3.7.500, but I don't seem to have a copy of the 3.6.23.1 amalgamation code. Where can I find a copy? Sam ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Multiple clients accessing one DB

2011-02-09 Thread Sam Carleton
On Wed, Feb 9, 2011 at 1:46 PM, Igor Tandetnik <itandet...@mvps.org> wrote: > > On 2/9/2011 1:42 PM, Sam Carleton wrote: > > > It is my understanding that SQLite is designed to allow multiple clients > > from the same computer to access the DB file at one time. > >

[sqlite] Multiple clients accessing one DB

2011-02-09 Thread Sam Carleton
Currently I have two and sometimes three clients access the SQLite db, all on the same machine. * A C# program that doesn't ever stay connection all that long. * An Apache application that stays connected all the time. * A Qt application that stays connected when it is running. I am getting

[sqlite] Understanding temp tables

2011-02-07 Thread Sam Carleton
I am using SQLite in a Apache module on Windows. On Windows, Apache is a single multi-threaded process. The Apache DBD is used to leverage connection pooling. At one point in the code, the web request gets a connection, creates a temp table, used the temp table, and then deletes the temp table

[sqlite] creating unique indexes, good or bad?

2011-01-17 Thread Sam Carleton
I am adding some indexes to an existing database to improve performance. I am 99.9% sure they are unique, but... it was a while ago that I was in that code. Are there any performance reasons to make them unique or make them not unique? From the stand point of risk, my inclination is to make

[sqlite] Understanding EXPLAIN QUERY

2011-01-16 Thread Sam Carleton
I am trying to optimize a query by using the EXPLAIN QUERY, but the documentation on the web (http://www.sqlite.org/eqp.html) does not match the version of SQLite I am using (v3.6.23.1). The documentation says there are three columns, but I am only seeing two columns. What do the two columns

[sqlite] best Linq to Entity provider for SQLite

2010-09-20 Thread Sam Carleton
What is the best LINQ provider for SQLite? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLITE_ENABLE_ATOMIC_WRITE on windows, good or bad?

2010-09-13 Thread Sam Carleton
When compiling sqlite for Windows desktop OS's (XP, Vista, Win7), should SQLITE_ENABLE_ATOMIC_WRITE be set or not? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New to SQLite and I have a question

2010-09-11 Thread Sam Carleton
using the C interface. Sam Carleton ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Using the sqlite3_unlock_notify() API on Windows

2010-09-11 Thread Sam Carleton
I just read over the "Using the sqlite3_unlock_notify() API" and it looks like exactly what I need. The only catch is that currently I am running everything on Windows. Does anyone have a port of the supporting functions discussed on the page for Windows? Sam

[sqlite] Understanding Shared-Cache Mode

2010-09-10 Thread Sam Carleton
I just read the page on Shared-Cache Mode and it left me with some questions... Q1: Is my understanding correct: Shared-Cache Mode is used within a process to gain table locking, as compared to the normal file locking. How to Enabling Shared-Cache Mode in the following situation: SQLite is

[sqlite] idle state and dead locks

2010-09-09 Thread Sam Carleton
I am having some issues with dead locks. Currently I have two different processes access the sqlite db, one is an Apache module using the dbd framework to manage connections, the other is a .Net program using the standard .Net provider. I wanted to just validate that both the providers can open

Re: [sqlite] PRAGMA database_list: insert into table?

2010-07-24 Thread Sam Carleton
Tim, I, like you, am using SQLite as the DB to a web server. But I have to agree with Roger. I do so from the prospective that web client cannot make native calls to SQLite. Thus the web server is the client. Further more I would say that in all web server based solutions, the web server is

[sqlite] memory only table

2010-07-22 Thread Sam Carleton
I am using SQLite inside of Apache. I am using the Apache connection pool system, so as long as the server is running there is always one connection to the database. I have one very high traffic table with lots of reads and writes, it turns out that this info does *NOT* need to be resident

Re: [sqlite] Cost of PRAGMA database_list

2010-07-21 Thread Sam Carleton
On Wed, Jul 21, 2010 at 11:23 AM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 21 Jul 2010, at 3:53pm, Sam Carleton wrote: > > > In the end, I am looking for the best way to determine if the connection > > contains the correct EventDB, since a connection can li

Re: [sqlite] Cost of PRAGMA database_list

2010-07-21 Thread Sam Carleton
On Wed, Jul 21, 2010 at 9:47 AM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 21 Jul 2010, at 2:30pm, Sam Carleton wrote: > > > My issue with only attaching to the EventDB for only as long as a command > is > > trying to use it is: The EventDB is used a LO

Re: [sqlite] Cost of PRAGMA database_list

2010-07-21 Thread Sam Carleton
On Tue, Jul 20, 2010 at 8:34 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 21 Jul 2010, at 12:42am, Sam Carleton wrote: > > > There are two equally > > important requires, one is to connect to the second "EventDB", the other > is > > that

Re: [sqlite] Cost of PRAGMA database_list

2010-07-20 Thread Sam Carleton
On Mon, Jul 19, 2010 at 9:46 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 20 Jul 2010, at 2:01am, Sam Carleton wrote: > > > On Mon, Jul 19, 2010 at 3:51 PM, Simon Slavin <slav...@bigfraud.org> > wrote: > > > >> You know, I think th

Re: [sqlite] Cost of PRAGMA database_list

2010-07-19 Thread Sam Carleton
On Mon, Jul 19, 2010 at 3:51 PM, Simon Slavin wrote: You know, I think that the most efficient way to do what you want will > probably to always issue the 'ATTACH' command. If EventsDB is already > attached, you should get a specific error code, which you can notice but >

Re: [sqlite] ATTACH DATABASE with connection pooling

2010-07-19 Thread Sam Carleton
On Mon, Jul 19, 2010 at 4:02 PM, Jay A. Kreibich <j...@kreibi.ch> wrote: > On Mon, Jul 19, 2010 at 01:27:52AM -0400, Sam Carleton scratched on the wall: >> Is there any way to use the PRAGMA database_list in a query?  What I would >> like to do is: >> >> SELECT *

Re: [sqlite] Cost of PRAGMA database_list

2010-07-19 Thread Sam Carleton
On Mon, Jul 19, 2010 at 2:58 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 19 Jul 2010, at 3:50pm, Sam Carleton wrote: > >>   - Connection to the second one if it is old (wrong physical file) or not >>   connected. > > (Actually you will get other databas

Re: [sqlite] Cost of PRAGMA database_list

2010-07-19 Thread Sam Carleton
On Mon, Jul 19, 2010 at 2:58 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 19 Jul 2010, at 3:50pm, Sam Carleton wrote: > > > I am using the connection pooling in Apache APR's DBD system.  Currently > > there are multiple places with in one requ

[sqlite] Cost of PRAGMA database_list

2010-07-19 Thread Sam Carleton
How expensive is doing a PRAGMA database_list? I am using the connection pooling in Apache APR's DBD system. Currently there are multiple places with in one request that does the following: - Get a connection to the DB - Call PRAGMA database_list - Iterate through the list looking for

Re: [sqlite] ATTACH DATABASE with connection pooling

2010-07-18 Thread Sam Carleton
Is there any way to use the PRAGMA database_list in a query? What I would like to do is: SELECT * FROM (PRAGMA database_list) WHERE name = 'EventDB'; Sam On Sun, Jul 18, 2010 at 11:56 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 19 Jul 2010, at 4:48am, Sam Ca

[sqlite] ATTACH DATABASE with connection pooling

2010-07-18 Thread Sam Carleton
I am working with an Apache module that is using the Apache DBD connection pool system. The application always need to connect to two DB files, the "System DB" and the "Event DB". The System DB is the default, the "Event DB" is always connected via an ATTACH DATABASE called EventDB. The system

Re: [sqlite] How is the table getting locked and how to unlock it?

2010-07-12 Thread Sam Carleton
On Mon, Jul 12, 2010 at 10:36 AM, Jay A. Kreibich wrote: > > Are you using sqlite3_exec() for all of these? My first guess is that > you're not finalizing the INSERT statement (or allowing it to run to > completion) before trying to drop the table. > I am using

[sqlite] How is the table getting locked and how to unlock it?

2010-07-12 Thread Sam Carleton
I posted this over the weekend, I am assuming it was overlooked because it was, well, the weekend:) Does anyone have any thoughts? -- I am on Window 7, opening an existing database with these flags: SQLITE_OPEN_EXCLUSIVE |

Re: [sqlite] EXTERNAL: binding an IN

2010-07-12 Thread Sam Carleton
)>1) { >printf("SQL too many parens?? - %s\n",sql); >} >puts(sql); > } > > > Michael D. Black > Senior Scientist > Northrop Grumman Mission Systems > > > > > From: sqlite-user

[sqlite] binding an IN

2010-07-11 Thread Sam Carleton
Is there any way to bind to this query? SELECT * FROM table WHERE tableId IN ( ? ); Where ? should be 1,2,3,4 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] update trigger to require input

2010-07-11 Thread Sam Carleton
I have some audit fields, one being updatedby, I would like to create an update trigger that would prevent the row from being updated if this was not set. Can I do that in sqlite? ___ sqlite-users mailing list sqlite-users@sqlite.org

[sqlite] why is this table locked?

2010-07-10 Thread Sam Carleton
I am on Window 7, opening an existing database with these flags: SQLITE_OPEN_EXCLUSIVE | SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE Then I get a value from one table, begin a transaction, create a temp customer table, fill it with the current values from the customer table. Here is that SQL run

Re: [sqlite] concat 2 const chars ?

2010-07-09 Thread Sam Carleton
I really don't mean to be a jerk, but this does seem to be really off topic for this mailing list. Isn't the concatination of two string a general C/C++ question rather then a sqlite question? Don't you think you might be better off asking this question on a C/C++ mailing list or forum, maybe

Re: [sqlite] sqlite query with c++ variable

2010-07-09 Thread Sam Carleton
On Fri, Jul 9, 2010 at 2:48 PM, smengl90 wrote: > > Hi, I want to compose a query that will use a c++ variable. For example I > have: > > int i= 5; > char * query = "SELECT * from userInfo WHERE count<'i'". > > The syntax does not work. How do I do that?

Re: [sqlite] setup sqlite in vc++

2010-07-07 Thread Sam Carleton
On Wed, Jul 7, 2010 at 7:12 PM, Simon Slavin wrote: > > The precompiled binaries are for those who see SQLite as some sort of > external library and don't want to include it in their own application. > It's a useful alternative for those who want to keep their own app as

Re: [sqlite] Books which cover C API

2010-07-07 Thread Sam Carleton
On Wed, Jul 7, 2010 at 2:55 PM, Jay A. Kreibich wrote: > > This one is coming out next month. I like it. > > http://www.amazon.com/Using-SQLite-Jay-Kreibich/dp/0596521189/ > Jay, Without knowing anything more about the book other then the link you provided, the one thing I do

Re: [sqlite] setup sqlite in vc++

2010-07-06 Thread Sam Carleton
On Tue, Jul 6, 2010 at 3:33 PM, smengl90 < fixed-term.seak.meng...@us.bosch.com> wrote: > > Hi guys, > > I am trying to setup sqlite to be used with VC++ 2008. Can someone show me > where I can find instructions on how to set it up? and do I need a c++ > wrapper to code in C++? If yes, can

Re: [sqlite] Use of sqlite3_step()

2010-07-06 Thread Sam Carleton
I use a bit simpler approach, don't know if it is correct or not, but it seems to work: int rc = sqlite3_step(stmt); while(rc == SQLITE_ROW) { /* read the row info */ rc = sqlite3_step(stmt); } if( rc != SQLITE_DONE) { /* handle error */ }

[sqlite] replacing a table

2010-06-27 Thread Sam Carleton
In other databases there have been times when I have played some tricks with the master tables, an example is: Goal: change some fundamental characteristics of 'target_table' which cannot be done by an ALTER 1: Create the new table with a different name: target_table2 with the changes 2: Do an

Re: [sqlite] How to use load_extension()?

2010-06-27 Thread Sam Carleton
Jan, Actually I am already compiling sqlite to my liking and recompiling the Qt sqlite3 driver. The custom sqlite3 has foreign keys turned on by default. When you say I have to build my own driver, do you mean I simply need to make another modification to my sqlite3 or are you saying I need to

[sqlite] dealing with evaluating user-entered SQL...

2010-06-27 Thread Sam Carleton
On Sat, Jun 26, 2010 at 11:22 PM, Igor Tandetnik <itandet...@mvps.org>wrote: > Sam Carleton <scarle...@miltonstreet.com> wrote: > > I have created a little extension function that I would like to load into > my > > Qt program, so I am using the function load_exte

[sqlite] How to use load_extension()?

2010-06-26 Thread Sam Carleton
I have created a little extension function that I would like to load into my Qt program, so I am using the function load_extension, but it always returns false. I am currently hard coding the path: QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE"); db.setDatabaseName(systemDB);

[sqlite] handling sqlite3_close() == SQLITE_BUSY

2010-06-22 Thread Sam Carleton
On Tue, Jun 22, 2010 at 10:13 AM, Pavel Ivanov wrote: > > No, I did not. I am not storing any blobs right now, but... Is the busy > > handler going to kick in? I know the busy handler is not the sole answer > to > > the problem, but it does seem to catch most of my

  1   2   >