Re: [sqlite] sqlite3.exe and formatting binary (GUID) data

On 25/05/2009 2:30 PM, Philip Warner wrote: > I use binary GUID values and when I 'select' them in the sqlite3 shell, > they display as *binary* data, often screwing up the terminal. When I > use .dump, or use .mode tcl I get terminal-friendly output. Is there any > simple way (or just a hack)

Re: [sqlite] queries for a fulltext-engine

On 24/05/2009 4:30 AM, Lukas Haase wrote: > Hi, > > Sorry for the subject - I just do not know for what to search of what to > ask - I actually do not know where is exactly my problem :-( > > I have a database containing thousands of HTML pages ("topics"). There > is a fulltext index for these

Re: [sqlite] BLOB Literals and NULL

On 23/05/2009 10:56 AM, Greg and Tracy Hill wrote: > Is it possible to use the BLOB literal sytax X'ABCD' with encoded NULLs? > > For example: > > INSERT INTO mytable VALUES (X'BADF00D0') I see no NULL here, encoded or otherwise. I don't even see an ASCII NUL here; NUL is character terminology,

Re: [sqlite] about insert into select

On 21/05/2009 11:43 AM, Wenton Thomas wrote: > What's the execution sequence about " insert ino A select from B "? > I means,which is correct prescription in the following: > (1) select all rows from B at first, meanwhile this product (frequently chosen for use on devices with minimal

Re: [sqlite] error in documentation of SELECT?

On 19/05/2009 9:57 PM, Igor Tandetnik wrote: > "John Machin" <sjmac...@lexicon.net> wrote > in message news:4a129cb4.2090...@lexicon.net >> It's handy for checking how things work e.g. >> >> sqlite> select (-1) % 7; >> -1 >> sqlite> --

Re: [sqlite] error in documentation of SELECT?

On 19/05/2009 9:37 PM, Nuno Lucas wrote: > On Mon, May 18, 2009 at 5:03 PM, Mitchell L Model wrote: >> I may be misreading the select-core diagram on >> http://www.sqlite.org/lang_select.html but it appears that the down-arrow >> that would allow a query without a FROM clause

Re: [sqlite] column names

On 19/05/2009 6:47 PM, Chanas, Olivier wrote: > Hi all, > > I would like to know what are the allowed characters to build a column name. > Is there some limitation ? OTTOMH (because it's not all in one convienient place in the docs): This applies to names of columns, tables, triggers, indexes,

Re: [sqlite] memory occupied by columns with NULL value

On 19/05/2009 6:25 PM, chandan wrote: > Hi, > I would like to know how much memory is used for a record field > whose value is NULL. http://www.sqlite.org/fileformat.html ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Sqlite as a FIFO buffer?

On 19/05/2009 1:42 AM, Christopher Taylor wrote: > I took a slightly different approach and used a trigger. This is the create > table function from my event log class. The string handler is proprietary > but other than that there should be enough there to give you an idea. > > void

Re: [sqlite] Updating two tables rows in one sql statement

On 17/05/2009 10:34 AM, velkropie wrote: > > > John Machin wrote: >> On 17/05/2009 1:35 AM, velkropie wrote: >>> Hello, >>> >>> I'm trying to update two tables with one statement but, i'm not getting >>> something right. can someone here help

Re: [sqlite] Updating two tables rows in one sql statement

On 17/05/2009 1:35 AM, velkropie wrote: > Hello, > > I'm trying to update two tables with one statement but, i'm not getting > something right. can someone here help me , or guide me to the right place > to get help? > > Thanks > > "UPDATE funrecipes, ingredients SET funrecipes.recipetitle, >

Re: [sqlite] Corrupt Database Problems

On 16/05/2009 3:08 AM, Jim Wilcoxson wrote: > I think you have answered your own question. If you use > synchronous=off, you are saying "I don't care much about this > database." When you "save" documents, you are merely putting them in > a computer's cache (memory) and then confirming to the

Re: [sqlite] How sqlite will store the data?

On 15/05/2009 8:41 PM, Pramoda M. A wrote: > But how to get the rows in the same order of insertion? > Say, I will insert 2 3 4 and it will store in 2 4 3. But I need in the order > 2 3 4. Is it possible? Possibility (1): Unless you use INTEGER PRIMARY KEY and supply your own values for the key

Re: [sqlite] SQLl question

On 15/05/2009 2:37 PM, Dennis Cote wrote: > Evan Burkitt wrote: >> This isn't a Sqlite question per se, but I know there are some SQL gurus >> here who might have some insight into this problem. I apologize for >> being off-topic; I can be shameless when I need help. :)> >> >> I have three

Re: [sqlite] Blob length in characters vs size in bytes

On 14/05/2009 12:23 AM, Salvatore Di Guida wrote: > Hi all! > Since I am a newcomer in SQLite, as a preliminary question, what is the > difference between > sqlite> select length(X'01'); > and > sqlite> select length('01'); > It seems that the former gives the size in bytes, the latter the

Re: [sqlite] problem with sqlite in c#.net

On 12/05/2009 9:30 AM, John Machin wrote: > On 12/05/2009 8:31 AM, manp wrote: >> when i execute a query like this >> UPDATE setting SET value="red" WHERE name="color" >> the row will update temporary while app is running , when i close app noting >>

Re: [sqlite] problem with sqlite in c#.net

On 12/05/2009 8:31 AM, manp wrote: > when i execute a query like this > UPDATE setting SET value="red" WHERE name="color" > the row will update temporary while app is running , when i close app noting > appear in DB > i use ManagedSQLite dll I presume that you refer to this:

Re: [sqlite] Newbie Question about ASCII and UTF-16 strings

On 10/05/2009 3:29 PM, kalyan@aol.in wrote: > Hi, > > I am a newbie to sqlite. > I want to create a sqlite database in Linux OS. > > The data that I wish to put in the table contains both ASCII and UTF-16 > encoded strings. > For eg. File and directory names are UTF-16 where the URL, date

Re: [sqlite] incorrect where clause does not throw error

On 10/05/2009 6:53 AM, Stefan Finzel wrote: > Porting an application to sqlite3.6.13 on Linux i made a mistake > creating a illegal query on a character field: > > select * from Test where Remark = NULL > > select * from Test where Remark <> NULL > > I was confused as there were neither data

Re: [sqlite] Searching for sqlite version 0.1

On 4/05/2009 9:10 PM, lakshmi pathi wrote: > Hi, > Where can i find the sqlite version 0.1 (Assume that's first sqlite > version) source code. I'm an open source programmer,looking to learn > about database. So learn from the LATEST code. It's gone through MAJOR revisions, all for the better.

Re: [sqlite] SQL sintaxe

On 2/05/2009 9:13 PM, Ernany wrote: > Hello John, > > I have 2 tables ("pcprod" and "pcdesc"). I´d like to *add *in my application > "MARCA" and "DESCR". > > This code is VB2005 .NET. How do I write SQL??? You already have columns named "descr" and "marca" in the "pcprod" table. You need to

Re: [sqlite] SQL sintaxe

On 2/05/2009 4:21 PM, Ernany wrote: > Hello, > > CREATE TABLE [pcprod] ( > [codigo] NVARCHAR(20) NOT NULL ON CONFLICT ABORT PRIMARY KEY, > [descr] NVARCHAR(40), > [marca] NVARCHAR(20), > [codigoint] NVARCHAR(20), > [preco] NUMERIC(5, 7), > [preco2] NUMERIC(5, 7)); > > > > CREATE

Re: [sqlite] How do you guys use sqlite from C++?

On 27/04/2009 10:06 PM, Vinnie wrote: >> From: Neville Franks >> Subject: Re: [sqlite] How do you guys use sqlite from C++? >> I use a modified version of the C++ wrapper >> http://www.codeproject.com/KB/database/CppSQLite.aspx > > Apparently I did come up with an original

Re: [sqlite] corrupt database recovery

On 26/04/2009 11:28 AM, Gene wrote: > You are exactly right John...that is indeed what the code looks > like...except we have over 25 columns (it's a flat table). I presume that you are referring to this: """ Write a script that loops around doing 'select * from mytable where rowid = ?' on a

Re: [sqlite] corrupt database recovery

On 26/04/2009 5:47 AM, Gene wrote: > Every now and again, we have a database that gets corrupt in the field (bad > coding on our end, not sqlite). > Hi Gene, The obvious question: why not fix the bad code? What does 'PRAGMA integrity_check;' say about these corrupt databases? > When we get

Re: [sqlite] Validating a file is a SQLite DB

On 24/04/2009 9:20 AM, Tommy Ocel wrote: > Other than using sqlite3_open_V2() with a SQLITE_OPEN_READONLY flag, > which would fully load the database and be time-consuming, > anybody know of a quick way to verify that a file is > actually a SQLite3 database file?

Re: [sqlite] Query takes ages

On 24/04/2009 2:16 AM, Marco Bambini wrote: > I have a query that takes ages ... my app remains in the first > sqlite_step for more than 30 minutes (with 100% CPU time). > Query is: > > SELECT reseaux.name, reseaux.id_reseau, reseaux.insee_id, > panneaux.insee_id, > panneaux > .reseau_id

Re: [sqlite] Find in SQLite database

On 22/04/2009 10:29 PM, anna_shahinyan wrote: > Thanks, > > I have created NSString and added the id value, then for creating > statement I have converted NSString to const char* by UTF8String > but the it seems it does no t work as sqlite3_prepare_v2 does not return > SQLITE_OK. Anna, show us

Re: [sqlite] Multi column ORDER BY across table peformance problem....

On 22/04/2009 10:48 AM, sorka wrote: > Wow. Anybody? I figured this would be a simple question for the gurus on this > board. Seriously, nobody knows a better way to do this? Seriously, consider alternative possibilities > > > sorka wrote: >> This should be simple but apparently it isn't. >>

Re: [sqlite] call PRAGMA page_size twice?

On 21/04/2009 2:07 PM, julian qian wrote: > HI, > call PRAGMA page_size =xxx twice immediately, only first time it has > effect, immediately call it second times, the value can't be changed. > is this predefined? http://www.sqlite.org/pragma.html#pragma_page_size > only say "The page_size pragma

Re: [sqlite] rows limit

On 19/04/2009 4:42 PM, Eugene Wee wrote: > Hi, > > On Sun, Apr 19, 2009 at 12:41 PM, Randomcoder wrote: >> Is there a maximum rows limit imposed on tables in sqlite ? >> I could not find this information anywhere on www.sqlite.org so that's >> why I'm asking here. > >

Re: [sqlite] Update query along with limit option

On 19/04/2009 5:06 PM, thirunavukarasu selvam wrote: > I tried the following query > update table-name SET status='1' where status='0' limit 2 > status - column name. What is "status - column name" meant to do? > I am using sqlite-amalgamation-3.6.13.tar.gz source from sqlite.org. > > I

Re: [sqlite] Slim down join results (all fields returned)

On 18/04/2009 2:33 PM, flakpit wrote: > If anyone else has a moment and a concrete example instead of sending me to > tutorials, feel free to step in here. If you would take a moment to read the fraction of a screen of a tutorial that I pointed you at (and which contains a concrete example),

Re: [sqlite] Slim down join results (all fields returned)

On 18/04/2009 8:24 AM, flakpit wrote: >> SELECT pubs.* from pubs, notes, publishers WHERE ... > > Thanks, but it didn't work the way I expected it to:) > > Now I have my query laid out a lot better and can 'slim' it down from here > > SELECT * FROM pubs > INNER JOIN notes > ON

Re: [sqlite] Limit to database and/or blob size on Mac/Windows?

On 17/04/2009 12:01 AM, Vinnie wrote: > Dear Group: > > I've done some calculations and its a fairly likely scenario that my users > will end up with sqlite databases that are over 1 gigabyte in size, in some > cases 4 gigabytes. An upper limit on the number of rows in a table could be > as

Re: [sqlite] fragmentation, overflow pages

On 16/04/2009 5:47 PM, Martin Pfeifle wrote: > Hi, > we store proprietary organized data in blobs within an sqlite database. > Assume you have a table mydata(id, attr1, attr2, blob). > The page size of the database is 1k. This is the *default* page size. You can change it before you first put

Re: [sqlite] Need help with the SQL statement.

On 16/04/2009 2:17 PM, Igor Tandetnik wrote: > "Joanne Pham" > wrote in message news:872428.4795...@web90308.mail.mud.yahoo.com >> But the first row (20657220 1 2 101 -- this is 2009-04-11 00:00:00) >> may not be there in the dailyDataTable so min(startTime) won't work >>

Re: [sqlite] How to calculate the size of my database for n records ?

On 15/04/2009 11:50 PM, Nicolas Gaiffe wrote: > Thank you Igor. > > Does anyone have a clue about estimating the size of a database ? >> I'm quite new at sqlite and I need to estimate the size the database I am > creating could reach. >> Basically, estimating for any table how many bytes would

Re: [sqlite] Aliased column can't use index outside of VIEWs?

On 15/04/2009 11:40 PM, Kelly Jones wrote: > I have a hideous query that looks like this: > > SELECT anf.name AS child, anf2.name||anf3.name||anf4.name AS parent > [...] > WHERE child='albuquerque' AND parent='newmexico'; > > which takes forever to run. I'm guessing: no column names in your

Re: [sqlite] fail to drop table in transaction

On 14/04/2009 12:21 PM, Wenton Thomas TOP-POSTED: > I have to drop the table,because I will use the same table name with > different table struct. Ever see those signs facing out from the end of a freeway exit: WRONG WAY! GO BACK! ? > > From: Kees Nuyt [snip]

Re: [sqlite] Problem with ordering

On 14/04/2009 6:29 AM, Lukáš Petrovický wrote: > Hello! > > I've been trying probably for ages to make the following work. I have > a table with following schema (most of the columns are irrelevant for > now): > > sqlite> .schema Event > CREATE TABLE Event (id integer, assignedCPUs varchar,

Re: [sqlite] "extension-functions.c" in windows mobile

On 10/04/2009 12:34 AM, Thibaut Gheysen wrote: > Hi, > > I develop a windows mobile application in VB .Net (compact framework 2.0) > with SQLite and the ADO.net provider. In this application, I must calculate > the distance between 2 points for all items of a table. For this I need > "power" and

Re: [sqlite] Datatypes on the command line: can you simply rely on quote()?

On 9/04/2009 8:40 PM, Florian v. Savigny wrote: > > The question was serious, sincere, and earnest! > > > [It] seems [...] that the typeof() column adds no information that > > the quoted value itself does not provide. > > > So, can I simply use quote() [...] to determine the storage >

Re: [sqlite] Is it using an index?

On 7/04/2009 6:43 AM, Scott Baker wrote: [snip] > I must have typod and not noticed. Your hypothesis carries within itself the seed of its own plausibility :-) ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Newbie what does & do.

On 28/03/2009 4:37 AM, Dermot wrote: > Hi, > > I just bough my "Introduction to SQL 4th edition" as per a list > member's advice. > > Before I had a chance to take it home and start going through it I > came across an SQL statement that I could use some help understanding. > > The statement is:

Re: [sqlite] Sqlite3 crashes when using the where command

On 26/03/2009 9:48 AM, JoeT wrote: > I am trying to run sqlite3 on Solaris 8. I have managed to compile it- and > install it. Basic read and write to a database works fine. However when I > use the where command or the delete command it crashes on me and says bus > error. It then does a core

Re: [sqlite] problems with shared cache?

On 25/03/2009 1:16 AM, Griggs, Donald wrote: > > > -Original Message- > > > On Sat, Mar 21, 2009 at 2:27 AM, Griggs, Donald > wrote: >>> However, when I ask the user to send me their deck, I find that: >>> >>> sqlite> pragma integrity_check;

Re: [sqlite] sqlite concat issue

On 19/03/2009 3:09 AM, Patnaik, Anjela wrote: > Hi John, > > Thanks for your response! Hi Anjela, I'm presuming that your off-list reply was accidental. > Now, the second column comes back as {} when I use sqlite TCL API. The TCL > llength is 1, instead of zero, probably due to the curly

Re: [sqlite] Passing FIELD Object To Function

On 18/03/2009 10:53 AM, TW wrote: [snip] > #include "sqlite.h" [snip] > I'm getting a blank form. I could put all of this in one block, but, > when I want to delete a record, > I don't want to write duplicate code, hence, passing the FIELD, FORM, & > WINDOW objects. The address

Re: [sqlite] concat two columns if 2nd non-empty and select first row

On 18/03/2009 8:29 AM, anjela patnaik wrote: > Hello all, > I have a table with multiple columns. I'd like to construct a select > statement that will concat two column values if the second field is non-empty > and also only return one row. > > I noticed the concat operator is not supported. I

Re: [sqlite] SQLITE : Constraint question

On 17/03/2009 9:02 AM, Wolfgang Enzinger wrote: >> Date: Sun, 15 Mar 2009 23:17:04 -0400 >> From: "Griggs, Donald" >> Subject: Re: [sqlite] SQLITE : Constraint question > >>> BTW, is there a document that explains in more detail what operations >>> the CHECK

Re: [sqlite] web page data scraping to sqlite db

On 17/03/2009 1:55 AM, d...@dommel.be wrote: > Hello, > > > I am working on a SQLite db with equity data in it. > On http://finance.yahoo.com/q?s=dow you can find the current P/E and Div & > Yield > fields. So I like to store in my db for name=DOW pe=12.25 and div=7.9 in a > automated way. > >

Re: [sqlite] datetime as integer

On 17/03/2009 1:00 AM, MikeW wrote: > Timothy A. Sawyer writes: > [snip] >> For date calculations, SQLite prefers real values containing >> number of days since noon in Greenwich on November 24, 4714 >> B.C., using the Proleptic Gregorian calendar: > SNIP > > "noon in Greenwich on

Re: [sqlite] starting INTEGER PRIMARY KEY at 0

On 17/03/2009 12:33 AM, P Kishor wrote: > On Mon, Mar 16, 2009 at 8:31 AM, P Kishor wrote: >> is there a way to have a table start the INTEGER PRIMARY KEY sequence >> at 0 (or some other arbitrary number)? >> >> -- >> Puneet Kishor >> > > I should have added.. yes, I can do

Re: [sqlite] Advices to get max performance with SQLITE and BLOBS

On 16/03/2009 5:48 PM, Pierre Chatelier wrote: >> A few hundred blocks of raw data? Blocksize approx 300K bytes? >> Database >> created and dropped by the same process? 500 blocks is approx 150M >> bytes; why not keep it in a hash table in memory? If you keep it in a >> database or the file

Re: [sqlite] designing a db to hold repeating data

On 16/03/2009 11:45 AM, P Kishor wrote: > On Sun, Mar 15, 2009 at 7:29 PM, John Machin <sjmac...@lexicon.net> wrote: >> On 16/03/2009 11:00 AM, P Kishor wrote: >>> I have a grid of 1000 x 1000 cells with their own data as well as 20 >>> years of daily weather dat

Re: [sqlite] IP from number with SQL

On 16/03/2009 8:48 AM, Kees Nuyt wrote: > On Sun, 15 Mar 2009 21:10:02 +0100, "Roger Andersson" > wrote: > >> Hi! >> >> The SQL below might be out there but I didn't find it >> and since there might be other that need to get >> 32-bit integer IP in a sqlite3 database to the >>

Re: [sqlite] Advices to get max performance with SQLITE and BLOBS

On 13/03/2009 11:24 PM, Mike Eggleston wrote: > On Fri, 13 Mar 2009, Pierre Chatelier might have said: > >> Hello, >> >> I am using SQLITE to store and retrieve raw data blocks that are >> basically ~300Ko. Each block has an int identifier, so that insert/ >> select are easy. This is a very

Re: [sqlite] LEFT INNER JOIN a second database

On 14/03/2009 9:45 PM, Derek Developer wrote: > To make it really easy, I have created three .sql files and an application > that is NOT command line akward. There are three .sql files with the > statements needed to create two databases and execute the outer join. > Drag and drop them onto the

Re: [sqlite] datetime as integer

On 12/03/2009 12:21 AM, Nicolás Solá wrote: > Hi I’m using Trac software and it is implemented using SQLITE3. In Trac DB > schema there is a table called “milestone”. It has a field called “due” and > it means due date. The problem is that it uses an integer data type to store > the datum and I

Re: [sqlite] LEFT INNER JOIN a second database

On 12/03/2009 12:12 AM, Derek Developer wrote: > I have read and searched but I am not able to get the following statement to > run: > SELECT MyID, Zip FROM TableOne d LEFT OUTER JOIN DatabseTwo.sdb.TableTwo n ON > n.MyID=d.MyID WHERE d.Zip > 8 ORDER BY d.Zip > > I just get error at "." >

Re: [sqlite] nullable select fields

On 10/03/2009 10:56 PM, Andrea Galeazzi wrote: > Hi All, > I'm developing an application which relies on sqllite as back-end. Now > I face to this problem: I've got a form that allows the user to fill a > lot of fields, obliviously only a little part of them will actually be > filled, the

Re: [sqlite] change the limitation of attached databases through perl

On 8/03/2009 9:02 AM, baxy77bax wrote: > hi, > > i need help with attaching databases. in my last post i recived some > valuable info on limitation of attached databases (Thanx !!). since i'm > using perl (its DBI) all modifications like cache_size, max page number , > journal size ... i've

Re: [sqlite] Set read-only mode

On 8/03/2009 4:27 AM, P Kishor wrote: > On Sat, Mar 7, 2009 at 10:59 AM, Tom Spencer wrote: >> Is there a way to set the current database handle as read-only? I'm >> connecting to an SQLite3 database (actually two including an attached >> database) using Perl with

Re: [sqlite] import a CSV-File

On 7/03/2009 6:16 AM, Rich Shepard wrote: > On Fri, 6 Mar 2009, Hans-Martin wrote: > >> It seems that there is no way to get rid of the embedded CR/LF without parse >> the complete output. > >Use sed. That's what it's for. Has anyone considered that getting rid of the embedded CR/LF is

Re: [sqlite] which func could get the number of rows

On 6/03/2009 9:15 PM, liubin liu wrote: > which func could get the number of rows? select count(*) from your_table_name; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to improve performance for some query statements

On 4/03/2009 2:48 PM, Peng Huang wrote: > On Wed, Mar 4, 2009 at 11:40 AM, John Machin <sjmac...@lexicon.net> wrote: > >> On 4/03/2009 2:12 PM, Peng Huang wrote: >>> Hi Igor Tandetnik, >>> >>> Thanks for your quick reply. >>> >>&g

Re: [sqlite] How to improve performance for some query statements

On 4/03/2009 2:12 PM, Peng Huang wrote: > Hi Igor Tandetnik, > > Thanks for your quick reply. > > Your solution works. But in some cases, each y%d may has two or three > choices. So the SQL will become very complex, we need ( 2 * 2 * 2 * 2) sub > where statements. Does SQLite have some build-in

Re: [sqlite] Slow performance with Sum function

On 4/03/2009 5:52 AM, Trainor, Chris wrote: > I am trying to use the Sum function on a column in a table with ~450K > rows in it. > > Select sum(Col4) from Table1 > > Where Table1 looks like this: > > Create TABLE Table1 ( > Col1 INTEGER NOT NULL, > Col2 INTEGER NOT NULL, >

Re: [sqlite] How do I do this join on multiple columns?

On 3/03/2009 12:48 PM, yaconsult wrote: > SQL newbie here. I have data in a couple of tables that I need to relate. > But I don't know how to relate on more than one column. I'll not go into > detail about why the data is formed the way it is - it comes from other > programs. > > For example,

Re: [sqlite] sqlite3_mprintf - C/C++ formatting 64-bit values?

On 27/02/2009 12:09 PM, His Nerdship wrote: > Does anyone know the sqlite3_mprintf/sqlite3_vmprintf format specifier for > 64-bit integers? > I have tried %Ld and %I64d, but it just guillotines the string at that > point. > I am using Borland C++ Builder v6. > Thanks in advance. Guess: %lld (as

Re: [sqlite] problem reading in SQL file with To_Date from Oracle

On 27/02/2009 8:37 AM, anjela patnaik wrote: > Hello all, > > I'm new to sqlite3. I have data in a Oracle table with one column being a > date type. > Now, I've exported this table into a sql file with insert statements. > > Then, I ran the .read command in sqlite3 to read in the sql

Re: [sqlite] Indexing problem

On 26/02/2009 9:45 PM, John Machin wrote: > On 26/02/2009 8:23 PM, Marian Aldenhoevel wrote: >> Hi, >> >> I am having a strange problem with a sqlite3 database. See the following >> transcript: >> >> > sqlite3 kdb "select * from kfz where kfznr=484

Re: [sqlite] Indexing problem

On 26/02/2009 8:23 PM, Marian Aldenhoevel wrote: > Hi, > > I am having a strange problem with a sqlite3 database. See the following > transcript: > > > sqlite3 kdb "select * from kfz where kfznr=48482364;" > > 48482364|48|0|0C|00|00|0||20|5B93|1746294314|||0|GP-T 1006|0 > > kfznr is the

Re: [sqlite] meta-question: In what version did feature X first appear?

On 26/02/2009 11:55 AM, Roger Binns wrote: Hi Roger, > John Machin wrote: >> In >> that situation, the next question to arise would be "What other >> currently-documented features must I avoid?" > > The usual solution is for documentation for each API t

[sqlite] meta-question: In what version did feature X first appear?

This is becoming a FAQ. E.g. here is a precis of a very recent thread: OP: Error message is : 'SQL error :near "savepoint": syntax error' Dan: Probably you need to upgrade. SAVEPOINT first appeared in 3.6.8. OP: That was the problem; I had 3.6.4 version Yesterday (my time) there was another,

Re: [sqlite] SQL error: no such function: replace

On 25/02/2009 1:13 PM, BenJones12345 wrote: > Hi all > > I'm very much a beginner with sqlite3 and and I'm completely stumped with > using the replace function. > > What I have is a field (TheOldField) with values like: > > HM1 > HP4 > HM3 > HM2 > > and I need to replace all "P" with "+" and

Re: [sqlite] Error on INSERT with SELECT Max(id) FROM ...

On 25/02/2009 11:34 AM, P Kishor wrote: > On Tue, Feb 24, 2009 at 6:12 PM, John Machin <sjmac...@lexicon.net> wrote: >> On 25/02/2009 10:30 AM, P Kishor wrote: >>> On Tue, Feb 24, 2009 at 5:19 PM, Leo Freitag <leofrei...@netcologne.de> >>> wrote: >

Re: [sqlite] Error on INSERT with SELECT Max(id) FROM ...

On 25/02/2009 10:30 AM, P Kishor wrote: > On Tue, Feb 24, 2009 at 5:19 PM, Leo Freitag wrote: >> Hallo, >> >> I'm trying to insert the highest value of tblName into tblZO. >> >> There fore I followed the hints in this article ... >>

Re: [sqlite] Error on INSERT with SELECT Max(id) FROM ...

On 25/02/2009 10:19 AM, Leo Freitag wrote: > Hallo, > > I'm trying to insert the highest value of tblName into tblZO. > > There fore I followed the hints in this article ... >

Re: [sqlite] Using result of subquery both as value and conditional test...

On 25/02/2009 6:15 AM, John Elrick wrote: > I may be overlooking something obvious, however, I cannot discern from > the documentation if this is possible. > > given a simple example: > > create table x (x_id integer); > create table y (y_id integer, y_value varchar); > > insert into x values

Re: [sqlite] Newb-ish performance questions

On 23/02/2009 8:14 PM, Kim Boulton wrote: > Hehe, probably a combination of rubbish grep (i used regex function in a > text editor) and vaccuming a 4GB table at the same time. google("scientific method") :-) > > @echo off > setlocal > set starttime=%time% > egrep --count >

Re: [sqlite] Newb-ish performance questions

On 23/02/2009 5:14 PM, Kim Boulton wrote: > Hello, > > Thanks > > The grep regex on the text file found around 10,000 lines over 5 minutes > (out of a total possible 200,000 rows), at which time I stopped it, > interesting experiment anyway :-) Uh-huh ... so you'd estimate that it would take

Re: [sqlite] Newb-ish performance questions

On 22/02/2009 7:48 PM, Kim Boulton wrote: > Thanks for the hints so far. > > Here is my existing Mysql setup and what I've tried with Sqlite > > *Mysql Table structure:* > CREATE TABLE `mydb`.`mytable` ( > `c1` >

Re: [sqlite] Double entry bookkeeping

On 20/02/2009 12:35 PM, BareFeet wrote: > Hi John (Machin), > > Thanks for the discussion. > >>> I understand that double entry bookkeeping traditionally uses the >>> redundancy as an error check, but that seems more appropriate for >>> manual pa

Re: [sqlite] Double entry bookkeeping

On 20/02/2009 9:23 AM, BareFeet wrote: > Hi John, > > Thanks again for your discussion. > >> "Double Entry" book keeping is actually a misnomer. A transaction >> is very likely to have more than two entries. > > Yes, I realize that, though most Transactions generally have just two. > In

Re: [sqlite] Check for existence of substring in table

On 15/02/2009 9:15 PM, Ulrich Schöbel wrote: > John Machin wrote: >> all I know about Tcl is that I don't want to >> know any more about Tcl :-) > > You should want to ;-) You should want to be using Python instead of Tcl ;-) ___

Re: [sqlite] Check for existence of substring in table

On 15/02/2009 8:17 PM, Ulrich Schöbel wrote: > Hi all, > > I'm stuck with my problem. Hopefully someone here can help. > > I have a very simple table 'friends' with only one column > 'link': > > create table friends (link text); > > Lets assume there are 2 rows, 'abc' and 'def'. > > Then

Re: [sqlite] Querying with dot commands using Perl DBD

On 10/02/2009 8:25 AM, Paulson, Ariel wrote: > Hi all, > > Does anyone know how grab the output of a dot command using DBD::SQLite? Here's a big fat hint: that's *not* what you really want to know; go for the helicopter view ... describe your *real* problem e.g. "I'd like to get the name of

Re: [sqlite] Question on missing Entry Point for Sqlite 3

On 9/02/2009 2:47 PM, W Allan Edwards wrote: > > By preprocessor do you mean #define? I did a search in my sqlite.c file for > > SQLITE_ENABLE_COLUMN_METADATA.. then I #defined above them ALL! The usual way of doing such a thing is somewhat less intrusive and labour-intensive and easier to

Re: [sqlite] Delete, sometimes doesn't seem to work ...

On 8/02/2009 8:33 AM, Simon wrote: > Difficult to say for sure, but it's possible the Indx of 0 were > inserted with another type (ie, the string "0" and of course, 0 != > "0") If the column is declared as integer (as the OP said) you need to try harder than '0' ... not trimmimg leading/trailing

Re: [sqlite] drop table question ?

On 4/02/2009 12:37 AM, Brad Stiles wrote: >>> For my own edification, why the "order by 1" clause? >> To sort them in ascending order of table name, which might make >> old-fashioned capers like visual scrutiny a little easier. > > OK then, why would one not use the column name? Maybe because

Re: [sqlite] drop table question ?

On 3/02/2009 8:07 PM, baxy77bax wrote: > hi > > simple question : How to drop all tables in my database that start , for > example, with X? > > table 1 is X1998 > table 2 is X8676 > table 3 is X2912 > ... > Catch the output of this: sqlite> select 'drop table ' || name || ';' from

Re: [sqlite] playing with sqlite3

On 31/01/2009 8:20 AM, Mike Eggleston wrote: > Wait. I ran the sqlite3 under script during lunch. I have the same > behavior. The script also captured the first line of output from killing > sqlite3. The output shows binary characters in an INSERT statement. The > bad line is (characters

Re: [sqlite] playing with sqlite3

On 30/01/2009 2:27 AM, Mike Eggleston wrote: > On Thu, 29 Jan 2009, Thomas Briggs might have said: > >>When you say the load "stops", what do you mean? Does the sqlite3 >> process end? Does it sit there doing nothing? >> >>The first thing I would do is look at line 55035 of the source

Re: [sqlite] database encrypted

On 29/01/2009 8:07 AM, Joanne Pham wrote: > Hi all, > One of our database had problem to run the "schema". The error message below: > The question is how the database getting to this stats " Error: file is > encrypted or is not a database" > Thanks, > JP > > Below is the error message > >

Re: [sqlite] INNER JOIN, JOIN Confusin

On 27/01/2009 12:00 PM, Cnichols wrote: > I have stumped myself with this sql goal. > > With this statement I am working with 3 tables > Stats - contains question asked history and if it was answered correcty > sessionid > questionid > correct > Questions - contains a list questions and mul

Re: [sqlite] SQLite not performing a certain update

On 26/01/2009 12:37 PM, John Machin wrote: > On 26/01/2009 12:17 PM, Rickard Westerlund wrote: >> In any case, I prepared an isolated test case which can be gotten from >> the link below. It contains source for the program as well as a >> prepared database that is s

Re: [sqlite] SQLite not performing a certain update

On 26/01/2009 12:17 PM, Rickard Westerlund wrote: > On Sun, Jan 25, 2009 at 12:12 AM, John Machin <sjmac...@lexicon.net> wrote: >> So SELECT has the same problem as UPDATE. That would suggest to me that >> the next step would be to try the following: >> >> (1) SE

Re: [sqlite] SQLite not performing a certain update

On 25/01/2009 7:45 AM, Onion Knight wrote: > On Fri, Jan 23, 2009 at 10:41 PM, John Machin <sjmac...@lexicon.net> wrote: >> Two suggestions: >> >> (A) Check to see if the corresponding SELECT works: >> >> "SELECT *, CASE WHEN lft BETWEEN ? AND ?"

<    1   2   3   >