Re: [sqlite] Updatable views

Stephen Oberholtzer wrote: > I should note that there's a gross inefficiency when using triggers to > handle updates or deletes against views; SQLite does the equivalent of > this: > > For UPDATE ... WHERE , SQLite copies the entire > source view into the temp table. > > SELECT * INTO FROM

Re: [sqlite] Innovative examples / user stories

Lars Aronsson <[EMAIL PROTECTED]> wrote: > Is there any documentation of how people use SQLite in odd ways in > their everyday activities? Did you see http://www.sqlite.org/whentouse.html The document above is not exactly what you are asking for since it does not list real-world examples,

[sqlite] replacing all newlines in a field

I have a db in which the data in some fields in some tables has newlines (line breaks). All is fine until I want to export this into a CSV or tab-delimited format in which case the line breaks mess up the row-by-row records. I am thinking, maybe I should not allow storing line breaks in the

Re: [sqlite] replacing all newlines in a field

Hi Puneet, > I have a db in which the data in some fields in some tables has > newlines (line breaks). All is fine until I want to export this into > a CSV or tab-delimited format in which case the line breaks mess up > the > row-by-row records. How does it mess it up? CSV facilitates

Re: [sqlite] Updatable views

BareFeet wrote: > >> Since you can't use a create table statement in a trigger, > > Yes, I thought of doing that and realized the limitation of triggers > not allowing create temporary table. Is this restriction part of > standard SQL, or just SQLite? Is it likely to change? It seems a >

[sqlite] Quoting identifiers (was: Updatable views)

Hi Dennis, >> I use the square brackets for identifiers because I find that >> using double quotes doesn't catch errors. If I say select "column >> name that does not exist" I get a string back. But if I use square >> brackets SQLite >> gives me an error that the column doesn't exist,

Re: [sqlite] replacing all newlines in a field

Hi Puneet, Question: Is there a way I can search and replace all the line breaks? x'hh' should work, where hh are hex digits. So if your file contained hex 0D type line breaks: UPDATE t SET essay_without_newlines = Replace(essay, x'0D', '~'); This email and any attachments have been

[sqlite] test post via gmane

Hopefully now that sqlite-users is on mailman it will take posts sent via gmane. If this message shows up, then that is the case. - a ___ sqlite-users mailing list sqlite-users@sqlite.org

[sqlite] Innovative examples / user stories

Is there any documentation of how people use SQLite in odd ways in their everyday activities? For example, do you e-mail SQLite DB files between you, as if they were Excel spreadsheets? Or do you distribute SQLite database files via BitTorrent? Even with multi table databases? That would

Re: [sqlite] Join trouble

Johnstone, Simon wrote: > > $query = "SELECT a.company, b.company FROM tblusers a inner join > tblcompany b on (a.company = b.company) "; > > This works but yet again doesn't print any results to the screen. > Simon, Try the following to see if you have any trailing spaces in your table

Re: [sqlite] Updatable views

Hi All (especially Dennis I guess ;-) ) Focusing on the "instead of update" trigger in my example (copied below), you'll notice that the trigger scans through the table [Orders Refunds Amount] three times to find the matching ID, once for each of insert, delete, update, whose where tests

Re: [sqlite] INSERTing records in two tables?

Gilles wrote: > > I'm an SQL newbie, and would like to know how to perform the following > INSERT's: > > BEGIN; > INSERT INTO customers (id,name) VALUES (NULL,'John Doe'); > ;How to get ROW_ID? > INSERT INTO phones (tel,id_customers) VALUES ('1234567',ROW_ID); > COMMIT; > Gilles, In this

Re: [sqlite] Serious problem: lower/upper malfunction

On Sun, Feb 10, 2008 at 10:11:24PM -0500, Shawn Wilsher wrote: > As I understand it, sqlite upper and lower functions only work on > ASCII. There is an icu extension to make it work with UTF-8/UTF-16 as > far as I know. Is it a special extension for SQLite? --

Re: [sqlite] Updatable views

Hi Stephen, > I should note that there's a gross inefficiency when using triggers > to handle updates or deletes against views; SQLite does the > equivalent of this: > > For UPDATE ... WHERE , SQLite copies the entire > source view into the temp table. > > SELECT * INTO FROM > > Then,

Re: [sqlite] Updatable views

BareFeet wrote: > > No, I have no MS legacy or habits. I use the > square brackets for identifiers because I find that using double > quotes doesn't catch errors. If I say select "column name that does > not exist" I get a string back. But if I use square brackets SQLite > gives me an

[sqlite] Selecting all and some columns

Greetings... I know that Puneet will get on my case about the obscurity of the subject (just kidding), but I am trying to find out if I can do this: Imagine this table and data... Class|ProjID|ProjFund|Invoice|Split Finishers|1045|73|| Finishers|1045|75|30| Finishers|1045|75|30|

Re: [sqlite] Updatable views

Hi Dennis, Thanks for your reply. I really appreciate the feedback. > This is a very nice set of triggers to handle the base tables of a > view > I believe this is the best way to handle this. Thanks, it's good to at least know that I'm heading the right way. I believe this is the best way

Re: [sqlite] Selecting all and some columns

Yes, but I want to do them in one shot. Igor's solution worked perfectly. josé - Original Message - From: "P Kishor" <[EMAIL PROTECTED]> To: "General Discussion of SQLite Database" Sent: Monday, February 11, 2008 6:12 PM Subject: Re: [sqlite] Selecting all

[sqlite] Help for sqlite syntax

Hi guys, i am a newbie of sqlite3, we want porting the Freepbx(freepbx.org) to our project www.astfin.org, the freepbx can support sqlite3, but not very well, now i got a problem about sqlite3 - SELECT t.variable, t.value, d.value state FROM `globals` t JOIN (SELECT

Re: [sqlite] Updatable views

Hi Dennis, > I don't think there is any way in SQL to eliminate the redundant > lookups. OK, thanks. I thought I saw syntax of some other SQL engines that permit if/then or case/when type branching within a trigger, but I may be mistaken. >> where new.Amount not null >>

Re: [sqlite] Selecting all and some columns

Hi Jose, > Class|ProjID|ProjFund|Invoice|Split > Finishers|1045|73|| > Finishers|1045|75|30| > Finishers|1045|75|30| > Finishers|1045|75|30| > Finishers|1045|75|| > Finishers|1045|75|75|y > Finishers|1045|75|25| > Finishers|1045|73|| > Finishers|1045|73|| > Finishers|1045|73|| >

Re: [sqlite] Updatable views

On 2/11/08, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > John Stanton <[EMAIL PROTECTED]> wrote: > > That ia a nice idea. To have a pragma which specied the dialect. There > > could be "strict" or "ansi" and "mysql", "oracle", "sqlserver" etc etc. > > It would give tighter control over hard

Re: [sqlite] Innovative examples / user stories

Hi, I used SQLite to move "data-cube" from server to client. The server hosts data in MS SQL databases. There is a server-app that run query based on some input send by client program. The server then run MSSQL query and generate a SQLite db consisting of some tables (fact, dimensions, etc),

Re: [sqlite] View update performance (was: Updatable views)

On Feb 11, 2008 1:53 PM, Dennis Cote <[EMAIL PROTECTED]> wrote: > > Stephen Oberholtzer wrote: > > I should note that there's a gross inefficiency when using triggers to > > handle updates or deletes against views; SQLite does the equivalent of > > this: > > > > For UPDATE ... WHERE , SQLite

Re: [sqlite] Updatable views

[EMAIL PROTECTED] wrote: > John Stanton <[EMAIL PROTECTED]> wrote: >> That ia a nice idea. To have a pragma which specied the dialect. There >> could be "strict" or "ansi" and "mysql", "oracle", "sqlserver" etc etc. >> It would give tighter control over hard to track annoying minor syntax

Re: [sqlite] Updatable views

BareFeet wrote: > create trigger [Insert Orders Refunds Joined] > instead of insert > on [Orders Refunds Joined] > for each row > begin > insert into [Orders Refunds] > ( > ID > , [Order ID] > , Date > , Reason > ) >

Re: [sqlite] Updatable views

That ia a nice idea. To have a pragma which specied the dialect. There could be "strict" or "ansi" and "mysql", "oracle", "sqlserver" etc etc. It would give tighter control over hard to track annoying minor syntax errors. Dennis Cote wrote: > BareFeet wrote: >> No, I have no MS legacy or

Re: [sqlite] Join trouble

Simon, If the query is returning no rows, my guess would be trailing spaces in the data. This has been discussed at length and there is a new collating sequence (http://www.sqlite.org/cvstrac/chngview?cn=4732)to address it. Either use it or try saying: WHERE rtrim(tblusers.company) =

Re: [sqlite] Selecting all and some columns

On 2/11/08, jose isaias cabrera <[EMAIL PROTECTED]> wrote: > > Greetings... > > I know that Puneet will get on my case about the obscurity of the subject > (just kidding), but I am trying to find out if I can do this: Imagine this > table and data... > > Class|ProjID|ProjFund|Invoice|Split >

[sqlite] Virtual table xBestIndex and when to free index resource

SQLite experts: The xBestIndex method of the SQLite virtual table interface implemented by the VT module returns an output to the core by setting idxNum member var of struct sqlite3_index_info to a value that is meaningful to the VT module. Assume that a memory resource was created in

Re: [sqlite] Selecting all and some columns

jose isaias cabrera <[EMAIL PROTECTED]> wrote: > Greetings... > > I know that Puneet will get on my case about the obscurity of the > subject (just kidding), but I am trying to find out if I can do this: > Imagine this table and data... > > Class|ProjID|ProjFund|Invoice|Split > Finishers|1045|73||

Re: [sqlite] replacing all newlines in a field

On 2/11/08, BareFeet <[EMAIL PROTECTED]> wrote: > Hi Puneet, > > > I have a db in which the data in some fields in some tables has > > newlines (line breaks). All is fine until I want to export this into > > a CSV or tab-delimited format in which case the line breaks mess up > > the > > row-by-row

Re: [sqlite] Selecting all and some columns

"Igor Tandetnik" wrote... > jose isaias cabrera wrote: >> Greetings... >> >> I know that Puneet will get on my case about the obscurity of the >> subject (just kidding), but I am trying to find out if I can do this: >> Imagine this table and data... >> >> Class|ProjID|ProjFund|Invoice|Split >>

[sqlite] Igor's Emails come as NewsGroups Posts

Igor, why is it that your sqlite replies come as NewsGroups posts? I can not reply to them because my company does not allow replies to newsgroups. Just wondering, because I wanted to thank you for your previous help, but I could not reply to it. Thanks, josé

Re: [sqlite] Selecting all and some columns

"BareFeet" wrote... > Hi Jose, > >> Class|ProjID|ProjFund|Invoice|Split >> Finishers|1045|73|| >> Finishers|1045|75|30| >> Finishers|1045|75|30| >> Finishers|1045|75|30| >> Finishers|1045|75|| >> Finishers|1045|75|75|y >> Finishers|1045|75|25| >> Finishers|1045|73|| >> Finishers|1045|73|| >>

Re: [sqlite] Help for sqlite syntax

Could you just use ORDER BY 1 ? best regards -- radzi -- - Original Message - From: "li yuqian" <[EMAIL PROTECTED]> To: Cc: "Dimitar Penev" <[EMAIL PROTECTED]>; "Mark" <[EMAIL PROTECTED]> Sent: Tuesday, February 12, 2008 1:56 PM Subject: [sqlite] Help for sqlite

[sqlite] build system type... x86_64-unknown-linux-gnu

I get this when I configure from the source tarball for 3.5.6 Is this importaint? Can I fix it? ./configure checking build system type... x86_64-unknown-linux-gnu checking host system type... x86_64-unknown-linux-gnu checking for gcc... gcc checking for C compiler default output file name...

[sqlite] Blob incremental i/o via Python

The documentation outlines the C interface for incremental blob input/output, and mentions the C typedef for a blob handle. http://www.sqlite.org/c3ref/blob_open.html http://www.sqlite.org/c3ref/blob.html typedef struct sqlite3_blob sqlite3_blob; Can this same interface be accessed in Python?

Re: [sqlite] Quoting identifiers (was: Updatable views)

Puneet said: > Keep SQLite simple, and keep it compliant with whatever the single > ANSI SQL standard may be there out there. Yes, I agree, but only because it seems that SQLite's allowance for double quotes for literals (which I have said introduces ambiguity and prevents proper error

Re: [sqlite] Updatable views

I should note that there's a gross inefficiency when using triggers to handle updates or deletes against views; SQLite does the equivalent of this: For UPDATE ... WHERE , SQLite copies the entire source view into the temp table. SELECT * INTO FROM Then, it iterates over , looking for rows

Re: [sqlite] Join trouble

As a suggestion, try doing this outside of your application first, simply by running sqlite3 from the command line. That way you can see if the problem has is because of something you're doing vs. sqlite itself. The join syntax I suggested (including the rtrim function) works fine here using the

Re: [sqlite] Join trouble

I have tried the following but get the following error Warning: sqlite_query() [function.sqlite-query]: no such function: rtrim in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\swiftdist\website\database\loginsuccess.php on line 22 SQL logic error or missing database SELECT

Re: [sqlite] Updatable views

John Stanton <[EMAIL PROTECTED]> wrote: > That ia a nice idea. To have a pragma which specied the dialect. There > could be "strict" or "ansi" and "mysql", "oracle", "sqlserver" etc etc. > It would give tighter control over hard to track annoying minor syntax > errors. > And, it would

Re: [sqlite] Virtual table xBestIndex and when to free index resource

On Feb 12, 2008, at 7:05 AM, Evans, Mark (Tandem) wrote: > SQLite experts: > > The xBestIndex method of the SQLite virtual table interface > implemented by the VT module returns an output to the core by > setting idxNum member var of struct sqlite3_index_info to a value > that is

Re: [sqlite] Join trouble

Did you try with an inner join with table aliases? so: from tblusers a inner join tblcompany b on (a.company = b.company) RBS > I am having serious trouble creating a join on my database. I have > tested it in other databases and it works so it must be a problem with > sqlite and the join??? I

Re: [sqlite] Updatable views

BareFeet wrote: > Hi All (especially Dennis I guess ;-) ) > > Focusing on the "instead of update" trigger in my example (copied > below), you'll notice that the trigger scans through the table [Orders > Refunds Amount] three times to find the matching ID, once for each of > insert, delete,