Re: [sqlite] How to specify collating sequences in an expression.

2007-02-01 Thread Derrell . Lipman
[EMAIL PROTECTED] writes: > (4) What syntax do you prefer? This seems the clearest to me, of the given choices: SELECT * FROM t1 WHERE CAST(x AS TEXT COLLATE NOCASE)='HELLO'; Derrell - To unsubscribe, send emai

Re: [sqlite] Backing up a SQlite database

2007-02-09 Thread Derrell . Lipman
Rich Shepard <[EMAIL PROTECTED]> writes: > On Fri, 9 Feb 2007, Mikey C wrote: > >> This might be a dumb question, but is taking a backup of a live database >> simply a matter of copying the file to a backup device/drive? > > Yes. It's a regular file to your OS. As a matter of fact, you can copy

Re: [sqlite] SQLite 2 to SQLite 3 - Varchar sizes

2007-03-09 Thread Derrell . Lipman
"Mitchell Vincent" <[EMAIL PROTECTED]> writes: > I have an old SQLite 2.8 database that shared it's schema with > PostgreSQL. One of the nifty things about PostgreSQL (that admittedly > has me spoiled) is the ability to just say "varchar" without any > length specifier. > > Specifying "varchar" in

Re: [sqlite] Index creation on huge table will never finish.

2007-03-22 Thread Derrell . Lipman
Chris Jones <[EMAIL PROTECTED]> writes: > I don't think that solves my problem. Sure, it guarantees that the IDs are > unique, but not the strings. > > My whole goal is to be able to create a unique identifier for each string, > in such a way that I dont have the same string listed twice, with

Re: [sqlite] Index creation on huge table will never finish.

2007-03-22 Thread Derrell . Lipman
Chris Jones <[EMAIL PROTECTED]> writes: > Derrell.Lipman wrote: >> >> Chris Jones <[EMAIL PROTECTED]> writes: >> >> I don't think that your original solution solves that problem either. You >> first posted this schema: >> >>> My schema looks as follows: >>> >>> CREATE TABLE rawfen ( fen VARCH

Re: [sqlite] Getting a list of items on a column

2007-05-05 Thread Derrell . Lipman
"jose isaias cabrera" <[EMAIL PROTECTED]> writes: > "C.Peachment" wrote, > > >> On Sat, 5 May 2007 19:58:19 -0400, jose isaias cabrera wrote: >> >> >>>Greetings! >> >>>imagine this data content, >> >>>c1, c2, c3 >>>a, 1, 2 >>>b, 3, 4 >>>c, 5, 6 >>>a, 9, 8 >>>a, 1, 9 >>>c, 1, 3 >> >>> I would like

Re: [sqlite] Problem with SQLite Pecl extension for PHP4

2007-05-10 Thread Derrell . Lipman
Brandon Eley <[EMAIL PROTECTED]> writes: > Thank you for this information. How would I access the 2.8.x database from > within PHP? is it even possible? Go to http://www.php.net. Type "sqlite" into the search box at the top. The page it brings you to describes the sqlite 2.8.x interface. The

Re: [sqlite] Problem with SQLite Pecl extension for PHP4

2007-05-10 Thread Derrell . Lipman
Brandon Eley <[EMAIL PROTECTED]> writes: > No, I can't upgrade to PHP5, yet. We're on PHP4. I was accessing sqlite 2.8.x databases with PHP4 a number of years ago. It worked fine. The page I referred you to previously _initially_ discusses the object interface for the sqlite functions. That, A

Re: [sqlite] default values at CREATE TABLE

2007-05-12 Thread Derrell . Lipman
"Frank Pool" <[EMAIL PROTECTED]> writes: > I want to create a table with two colums: > > One ist the primary key (test_num) > and the second column sholud contain the value of the primary key (maybe as > a string) by default. > How can I define this table in sql ? > > CREATE TABLE test_table ("tes

Re: [sqlite] Reading pragma's results

2009-05-25 Thread Derrell Lipman
On Mon, May 25, 2009 at 1:10 PM, João Eiras wrote: > Btw, the same happens with the command line sqlite program > > $ sqlite > SQLite version 2.8.17 These pragmas didn't exist six years ago when 2.8.17 was current. The only pragmas in that version are: default_cache_size cache_size default_synch

Re: [sqlite] Using SQLite for GIS Application

2009-08-23 Thread Derrell Lipman
On Sun, Aug 23, 2009 at 01:08, Itzchak Raiskin wrote: > Hi > I want to use SQLite in a GIS application where I create a database > containing terrain data (coordinates, height). > I would like to query this database with start and end points of a line and > get a vector with all heights point alon

Re: [sqlite] Two commands at once

2008-02-16 Thread Derrell Lipman
On Feb 16, 2008 12:01 PM, Fred J. Stephens <[EMAIL PROTECTED]> wrote: > How can I issue 2 commands to SQLite at the same time in a shell script? > I want to temporarily change the separator for the returned data, > something like: > sqlite3 database.db ".separator " ", SELECT first,last FROM addres

Re: [sqlite] undefined symbol: sqlite3_prepare_v2

2008-02-27 Thread Derrell Lipman
On Wed, Feb 27, 2008 at 12:34 PM, Joanne Pham <[EMAIL PROTECTED]> wrote: > Hi All, > I used the sqlite3_prepare_v2 in my code and some of the servers are ok > but one of my server return an error message that "undefined symbol: > sqlite3_prepare_v2 " when the process is started and access the

Re: [sqlite] undefined symbol: sqlite3_prepare_v2

2008-02-27 Thread Derrell Lipman
On Wed, Feb 27, 2008 at 6:23 PM, Joanne Pham <[EMAIL PROTECTED]> wrote: > Hi all, > Thx for the response! > On different Linux server. So how to find out what is the SQLite version on > the server which has the problem. > How to find out the sqlite version using Linux commnad. Doing it with a

Re: [sqlite] How long time to index this table

2008-02-28 Thread Derrell Lipman
On Thu, Feb 28, 2008 at 11:48 AM, Lars Aronsson <[EMAIL PROTECTED]> wrote: > Gilles Ganault wrote: > > > But then, how many people use SQLite to handle 68 milions rows > > in a table? I've got 60 million in one table, and this is with an sqlite2 database. Works just fine, BTW. Derrell

[sqlite] algorithm for adding columns to a table

2008-03-18 Thread Derrell Lipman
I could use another set of eyes or three to verify that this algorithm makes sense. I have legacy sqlite2 databases for which I need a generic function to add columns to tables. This is the pseudocode for the function. Am I missing anything? In this pseudocode, the parameters are: :tableName

Re: [sqlite] algorithm for adding columns to a table

2008-03-19 Thread Derrell Lipman
ou did. Thanks! Derrell > > > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Derrell Lipman > Sent: terça-feira, 18 de março de 2008 11:26 > To: General Discussion of SQLite Database > Subject: [sqlite] algorithm

Re: [sqlite] SELECTing calls < last two weeks?

2008-03-25 Thread Derrell Lipman
On Tue, Mar 25, 2008 at 11:02 AM, Gilles Ganault <[EMAIL PROTECTED]> wrote: > Hello > > In MySQL, I used the following to only get calls received in the last > two weeks: > > WHERE calls_date > DATE_SUB(CURRENT_DATE, INTERVAL 15 DAY) > > Is there an equivalent in SQLite, or should I perf

Re: [sqlite] Clear screen in command line interface

2008-03-26 Thread Derrell Lipman
On Wed, Mar 26, 2008 at 4:14 PM, Fred J. Stephens <[EMAIL PROTECTED]> wrote: > Mike McGonagle wrote: > > Hum, on the Mac (10.4.9), the terminal window can be cleared from within > the > > terminal program itself. I would imagine that other terminal programs also > > provide this. Does this func

Re: [sqlite] sqlite with emacs (OT)

2008-04-02 Thread Derrell Lipman
On Wed, Apr 2, 2008 at 10:30 AM, Wensui Liu <[EMAIL PROTECTED]> wrote: > good morning, all, > when i tried to use sqlite in emacs with shell mode, it doesn't work. > is there a interface for sqlite in emacs / xemacs? > I use sqlite in emacs shell mode all the time. What problem are you encounter

Re: [sqlite] selecting uncommited rows

2008-04-17 Thread Derrell Lipman
On Thu, Apr 17, 2008 at 3:15 PM, Alex Katebi <[EMAIL PROTECTED]> wrote: > I will give a simple example: > > create table t1(name); > insert into t1('Alex'); > begin; > insert into t1 values ('Dennis'); > select * from t1; > > The above will show two rows. How can I see only the 'Dennis' row in thi

Re: [sqlite] Proposed incompatible changes to the SQLite VFS layer

2008-05-02 Thread Derrell Lipman
On Fri, May 2, 2008 at 11:56 AM, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > However, as we have gained experience with the VFS, we have come to > realize that the original VFS design has some warts. In particular, > not all of the methods in the VFS interface are able to return the > errors tha

Re: [sqlite] What is quicker?

2008-06-04 Thread Derrell Lipman
On Wed, Jun 4, 2008 at 10:01 AM, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > > Let me strongly reiterate that you look into using the new R-Tree > virtual table available for SQLite. R-Trees are specifically designed > to do exactly the kind of query you are asking to do. See > > http://www

Re: [sqlite] segmentation fault error?

2006-04-06 Thread Derrell . Lipman
杰 张 <[EMAIL PROTECTED]> writes: > 1. (*) text/plain > > Hi all, > I just want to get the values of a table.The result implemented is > "Open OK! > segmentation fault ". Why did I got this result ? The following is my code: Just from a quick visual inspection, it appears that y

Re: [sqlite] How can I rename a column without ALTER COLUMN Command

2006-04-07 Thread Derrell . Lipman
Roman <[EMAIL PROTECTED]> writes: > I know that ALTER TABLE -> ALTER COLUMN is not supported by sqlite3. > > I misspelled a column name, and I am curious if there is a command to change > the name from sqlite3 interface. A bit simplistic, but: echo ".dump" | \ sqlite3 database.db | \ sed 's

Re: [sqlite] Selecting a random row from a table

2006-04-16 Thread Derrell . Lipman
[EMAIL PROTECTED] writes: > Thomas Chust <[EMAIL PROTECTED]> wrote: >> Hello, >> >> I have a table of strings and integer primary keys from which I would like >> to retrieve a string at random. The best solution I could think of was to >> first do a >> SELECT count(id) FROM strings; >> and

Re: [sqlite] backwards compatibility

2006-04-16 Thread Derrell . Lipman
"Michael P. Soulier" <[EMAIL PROTECTED]> writes: > Hello, > > I just got a nasty surprise. I installed an sqlite 3.3.5 .dll on windows with > pysqlite2, and I used an application to create a database file. I then scp'd > the file home to my linux box where I have sqlite 3.2.2 installed, and I got

Re: [sqlite] C API Question

2006-05-05 Thread Derrell . Lipman
Anders Persson <[EMAIL PROTECTED]> writes: > I have written a wrapper around >iRes = sqlite3_get_table(db,sql,&resultp,&nrow,&ncolumn,&errmsg); > > But only SELECT works not INSERT or UPDATE > > any ide.. missing somting The database file or the directory in which it is contained is read-only

Re: [sqlite] Proposal: sqlite3_column_rowid

2006-05-16 Thread Derrell . Lipman
Ralf Junker <[EMAIL PROTECTED]> writes: > Drawbacks on sqlite3_column_rowid? > > I can not see any negatives on sqlite3_column_rowid. What does it do with queries that are pulling data from multiple tables? How about computed column data? Consider these queries: SELECT a.c2, b.c3 FROM a, b

Re: [sqlite] Re: Check for duplicate values in database

2006-05-29 Thread Derrell . Lipman
"Anish Enos Mathew" <[EMAIL PROTECTED]> writes: > Igor, I know that it will show error when we try to insert a value which > is already there in the database when that field is a primary key. But > I want to check it out and I don't want that number to be inserted into > the database. I was askin

Re: [sqlite] autonum primary key

2006-06-05 Thread Derrell . Lipman
"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> writes: > I need help in generating a unique integer for the table's primary key. I am > more familiar with MS Access that has a data type called "Autonum" that > generates the integer. Do I need to find the last record to know what the > next number should

Re: [sqlite] Problems with multiple threads?

2006-06-06 Thread Derrell . Lipman
"Jiri Hajek" <[EMAIL PROTECTED]> writes: > 1. Occasionally after running Sqlite3_step() I get SQLITE_CANTOPEN ('Unable > to open the database file') error. I found out that it can be fixed by > running the query again, i.e. again calling Sqlite3_Prepare(). So this isn't > a big issue, but still I

Re: [sqlite] SQLite performance for 10 Million Records

2006-06-19 Thread Derrell . Lipman
Bill King <[EMAIL PROTECTED]> writes: > Manzoor Ilahi Tamimy wrote: > >> We are Using SQLite for one of our project. >> >>The Database Size is more than 500 MB. >>It contain one table and about 10 million Records. > Err, for that size, I'd recommend going something heavier, like > firebird. This

Re: [sqlite] SQLite performance for 10 Million Records

2006-06-20 Thread Derrell . Lipman
"Manzoor Ilahi Tamimy" <[EMAIL PROTECTED]> writes: > Here Is The Schema For these Tables. > > CREATE TABLE HVH ( > Field1 VARCHAR(8),IDC VARCHAR(4), > Field3 VARCHAR(2),Field4 VARCHAR(4), > Field5 VARCHAR(7),Field6 VARCHAR(8), > Field7 VARCHAR(1),Field8 FLOAT); > > CREATE TABL

Re: [sqlite] problem with creating a table

2006-06-25 Thread Derrell . Lipman
Bijan Farhoudi <[EMAIL PROTECTED]> writes: > I am trying to create table and I would like to name one of the columns > order, but pysqlite does not like it. I do not want to have order__ or any > thing like that. for example the following command does not work: > cur.execute('create table foo(i i

Re: [sqlite] problem with creating a table

2006-06-25 Thread Derrell . Lipman
Bijan Farhoudi <[EMAIL PROTECTED]> writes: > A. Pagaltzis wrote: >> * Bijan Farhoudi <[EMAIL PROTECTED]> [2006-06-25 16:35]: >> >>> Thanks for your answer but still I am getting an error message: >>> sqlite> create table foo(i integer, [order] integer); >>> sqlite> .sch >>> CREATE TABLE foo(i inte

Re: [sqlite] Need sql query help

2006-06-25 Thread Derrell . Lipman
onemind <[EMAIL PROTECTED]> writes: > What technology would be best suited for this task? I just assumed that a > databse would be ideal, why do you say sql isn't suited for this and what > is? Take a look at the Controllable Regex Mutilator, CRM114, . It has mecha

Re: [sqlite] BEGIN TRANSACTION appears to not lock database

2006-07-02 Thread Derrell . Lipman
Bud Beacham <[EMAIL PROTECTED]> writes: > In the book, SQLite (version 2.x of SQLite) by Chris Newman, he writes on > page 184 that a way to test a Tcl callback function for a locked database is > to issue a BEGIN TRANSACTION in one session and run the program in another > session. Note the boo

Re: [sqlite] Results even if no rows

2006-07-13 Thread Derrell . Lipman
"andrew gatt" <[EMAIL PROTECTED]> writes: > I'm trying to come up with an SQL statement that works under sqlite that can > return a list of rows depending if a list of ids exist. For individual > checking i'm using: > > select 1 from table where id == 1; > > which returns a 1 if there is a row or

Re: [sqlite] "SQL Error: near 'Table': Syntax error"

2006-07-26 Thread Derrell . Lipman
"John Newby" <[EMAIL PROTECTED]> writes: > Does anyone know any reason why SQLite doesnt like tables called "Table" or > is this a standard SQL thing? It's a reserved word, so if you really, Really, REALLY want to create a table of that name (you're making it confusing to read, so you really shou

Re: [sqlite] From Windows file format to MacOSX (unsuccessfull)

2006-08-04 Thread Derrell . Lipman
Alexander Lamb <[EMAIL PROTECTED]> writes: > 1. (*) text/plain > > Well, I am afraid it didn't work. > > Somehow, the legacy_file_format info is not "sticky". > > I did: > > sqlite3 > > then in command mode: > > PRAGMA legacy_file_format=ON; > > then > > ATTACH "d:\mydb.db" AS mydb; In

Re: [sqlite] Performance Question

2006-08-28 Thread Derrell . Lipman
Kurt Welgehausen <[EMAIL PROTECTED]> writes: > [EMAIL PROTECTED] wrote: > >> Saying NOT NULL on a PRIMARY KEY is redundant, by the way. >> -- >> D. Richard Hipp <[EMAIL PROTECTED]> > > **kaw<~/tdpsa>$ sqlite3 > Loading resources from /home/kaw/.sqliterc > SQLite version 3.3.7 > Enter ".help" for

[sqlite] Potential corruption bug in 2.8.17. Patch attached.

2006-10-22 Thread Derrell . Lipman
This was likely a typo. In its current state, it's accessing uninitialized memory. It looks like it's conceivable that an incorrect nextRowid could be later used if the uninitialized value happens to be a small integer (smaller than pC->nextRowid) and the "valid" flag therefore doesn't get set to

Re: [sqlite] Potential corruption bug in 2.8.17. Patch attached.

2006-10-24 Thread Derrell . Lipman
[EMAIL PROTECTED] writes: > [EMAIL PROTECTED] wrote: >> This was likely a typo. In its current state, it's accessing uninitialized >> memory. It looks like it's conceivable that an incorrect nextRowid could be >> later used if the uninitialized value happens to be a small integer (smaller >> tha

Re: [sqlite] Re: number problem with 3.2.8

2006-10-25 Thread Derrell . Lipman
"Lloyd Thomas" <[EMAIL PROTECTED]> writes: > I did try number literal >10 but mad no difference. I will rebuild the > database row as an integer. As an interim solution (prior to changing your database schema), you should be able to use your existing schema with this query: select ring_time f

Re: [sqlite] Retrieving column data by providing column names

2006-10-26 Thread Derrell . Lipman
"Ever Green" <[EMAIL PROTECTED]> writes: > I am using sqlite3_column_text16 to retrieve data. > > The problem is that sqlite3_column_text16 takes the column number as > argument. This breaks my existing code if I insert new columns in the > beginning of the column list that was specified in the SE

Re: [sqlite] Retrieving column data by providing column names

2006-10-26 Thread Derrell . Lipman
"Ever Green" <[EMAIL PROTECTED]> writes: > I dont think so! > > These functions would help to get the parameter names from a prepared > statement. I want to retrieve data using column headings after a > sqlite3_step. The function I pointed you at takes the parameter name and returns its index. I

Re: [sqlite] command-line shell handling of errors.

2006-10-26 Thread Derrell . Lipman
[EMAIL PROTECTED] writes: > So the question: Who will be adversely effected by the new > error behavior in the sqlite command-line shell? Who is > using the sqlite command-line shell in scripts in such a > way that the script will no longer work with the new > behaviors? Do I need to change the

Re: [sqlite] Q about new SQLite API

2006-11-07 Thread Derrell . Lipman
[EMAIL PROTECTED] writes: > QUESTION 1: sqlite3_prepare_v2 is the merely the working name > for the new function. What should the official name be? > Some possibilities include: > > sqlite3_prepare_ex1 > sqlite3_prepare_ng > sqlite3_new_prepare > sqlite3_compile sqlite3_compile w

Re: [sqlite] Retrieving date

2006-11-07 Thread Derrell . Lipman
Lloyd <[EMAIL PROTECTED]> writes: > Hi, > I stored a unix epoch (32 bit integer) date in the sqlite data base. I > want to retrieve it in the readable date format. For that I use the > following query > > select datetime(sdate,'unixepoch') from mytab; > > It shows a formatted date, but there is

Re: [sqlite] select from commandprompt with output to file

2006-11-16 Thread Derrell . Lipman
"RB Smissaert" <[EMAIL PROTECTED]> writes: > How would I run these 4 commands via a .bat file or via whatever means: > > cd c:\test\ReadCodes > c:\test\Program\sqlite3 c:\test\ReadCodes\ReadCode.db" > .output testfile.txt > select * from readcode where read_code glob 'G2*'; I haven't been trackin

Re: [sqlite] Check for empty table

2006-12-13 Thread Derrell . Lipman
"Scott Hess" <[EMAIL PROTECTED]> writes: > On 12/13/06, RB Smissaert <[EMAIL PROTECTED]> wrote: >> Trying to find the fastest way to determine if a table has no rows. >> >> I think this will do: >> SELECT (SELECT ROWID FROM table limit 1) IS NOT NULL; >> If a table has rows then the result should

Re: [sqlite] Is sqlite the right db for me?

2006-12-17 Thread Derrell . Lipman
"Steve Davis" <[EMAIL PROTECTED]> writes: > It is not a web-based app, rather browser based. Many brigades do not have > net access at their stations, they might only have someone there once a > fortnight, so it isn't viable. Also this app might also be used on a > notebook at a remote location.

Re: [sqlite] What's the collective term for table, view, trigger etc?

2007-01-06 Thread Derrell . Lipman
T&B <[EMAIL PROTECTED]> writes: > Is there a standard (or even non-standard) term that refers to the tables, > triggers, views, indexes (any others?) in a database? The collective term for all of the definitions taken together is the "schema" of the database. Derrell --

Re: [sqlite] attach in transaction

2007-01-08 Thread Derrell . Lipman
[EMAIL PROTECTED] writes: > The prohibition against running ATTACH within transaction > first appeared in version 3.0.1. Presumably in version 3.0.0 > you could ATTACH within a transaction. The prohibition may have appeared in 3.x but attempting an ATTACH within a transaction doesn't work with 2

Re: [sqlite] pragma for headers ( pipe or redirect a series of commands)

2007-01-18 Thread Derrell . Lipman
Bill Hernandez <[EMAIL PROTECTED]> writes: > I tried finding a pragma command for .headers on, but didn't have any luck. > > sqlite> .headers on | .mode column customers | select * from customers ; > I also tried : > > sqlite> select * from customers ; < .headers off > > and that didn't work eith

Re: [sqlite] does select reliably return insert order?

2007-01-19 Thread Derrell . Lipman
"Kevin Dangoor" <[EMAIL PROTECTED]> writes: > I'm using sqlite as a temporary store for flattened report database > (generally just one table). This data is pulled out in chunks using > OFFSET and LIMIT. This particular setup is quite simple... just insert > all of the data, and then pull it out c

Re: [sqlite] Corrupted database repairing

2008-07-25 Thread Derrell Lipman
On Fri, Jul 25, 2008 at 5:23 AM, Alexey Pechnikov <[EMAIL PROTECTED]> wrote: > > > Database servers such as Oracle or PostgreSQL have transactions log and > > > restore log. > > > > Those logs are effectively duplicates of the data or ways of > > reconstructing the data. SQLite has a transaction

Re: [sqlite] Clone SQLite databases

2008-08-05 Thread Derrell Lipman
On Tue, Aug 5, 2008 at 10:31 AM, csabi81 <[EMAIL PROTECTED]> wrote: > > I have made the test with following results: > Useing "SELECT ALL * FROM WHERE... order by id;" on the > original > database and > "SELECT ALL * FROM order by id;" on the cloned database give me > the same results. > outputi

Re: [sqlite] Default Column Value to Local Time

2008-09-01 Thread Derrell Lipman
On Mon, Sep 1, 2008 at 10:16 PM, jonwood <[EMAIL PROTECTED]> wrote: > > Okay, I give up. I've been searching for a while now. I've found a number > of > discussions about how CURRENT_DATE returns the date in UTC, and that it can > be converted to local time. But I've yet to find one article that c

Re: [sqlite] Default Column Value to Local Time

2008-09-01 Thread Derrell Lipman
On Mon, Sep 1, 2008 at 10:29 PM, Derrell Lipman < [EMAIL PROTECTED]> wrote: > > > On Mon, Sep 1, 2008 at 10:16 PM, jonwood <[EMAIL PROTECTED]> wrote: > >> >> Okay, I give up. I've been searching for a while now. I've found a number >> of >&

Re: [sqlite] Help Using RowID

2008-09-06 Thread Derrell Lipman
On Sat, Sep 6, 2008 at 10:44 AM, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > > As a bonus, if the implementation of SQLite changes, SQL itself won't, > > so id will _still_ be a valid integer primary key, even if the > > implementation detail of rowid changes. > > I promise that INTEGER PRIMARY K

Re: [sqlite] About select by "=" condition to a string

2008-10-31 Thread Derrell Lipman
On Fri, Oct 31, 2008 at 9:06 AM, yoky <[EMAIL PROTECTED]> wrote: > Hi all, >I create a table like this "create table tbl1 (ID integer primary > key, name, addr)" , >then insert a record: > "insert into tbl1 values(1, 'aa', 'bb')", > select the record: > "s

Re: [sqlite] Open the database - Creating the empty database

2008-12-15 Thread Derrell Lipman
On Mon, Dec 15, 2008 at 1:43 PM, P Kishor wrote: > On 12/15/08, Joanne Pham wrote: > > Hi All, > > I have this problem about open the database. Here is the detail about > the problem. > > > > Our application have one process to create the database and another > process to open the database and

Re: [sqlite] Transaction?

2008-12-31 Thread Derrell Lipman
On Wed, Dec 31, 2008 at 11:36 AM, John Hawley wrote: > the following block of code executes 2 or 3 orders of magnitude slower > than 1 think it should ( the db was opened in earlier code) > Yup, you're committing each insert to disk (a VERY slow process) rather than waiting until you've inserted

Re: [sqlite] Single quote, newlines and UPDATES

2009-02-11 Thread Derrell Lipman
> > > and I did an SQL command dump of the string that I am passing to the D > SQLite wrapper and it is escaped. I am reclining on the idea that it is > the > D wrapper. I have to go to post this on the D wrapper newsgroup. > My understanding is that you can call C library functions from D. You

Re: [sqlite] Any concept of row number in SQLite?

2009-02-19 Thread Derrell Lipman
On Thu, Feb 19, 2009 at 10:45 PM, His Nerdship wrote: > > Thank you Thomas, that was most helpful. > I have just found the ROWID information > (http://sqlite.org/lang_createtable.html#rowid) - this is what I was > looking > for! You have to know something exists before you can look for it > I

Re: [sqlite] DateTime Objects

2009-02-28 Thread Derrell Lipman
On Sat, Feb 28, 2009 at 1:47 PM, jonwood wrote: > > Greetings, > > I have a SQLite table that contains a DATETIME value. However, the database > does not appear to provide enough control over how that value is formatted. > For example, I'd like a two-digit year, to eliminate leading zeros, and to

Re: [sqlite] sqlite3 datbase disk image malformed

2009-03-06 Thread Derrell Lipman
On Thu, Mar 5, 2009 at 7:39 AM, Peter van Dijk wrote: > On Thu, Mar 05, 2009 at 04:30:50AM -0800, donnied wrote: > > > > rsync was corrupting the database. I'll have to exclude the database > from > > rsync backup. > > I feel a need to point out that it is not, technically, rsync that was > corr

Re: [sqlite] [newbie] How to upgrade sqlite3 in Ubuntu?

2009-05-03 Thread Derrell Lipman
On Sun, May 3, 2009 at 11:37 AM, scientist scientist < scientist92...@yahoo.com> wrote: > Hi all, > My current Ubuntu version is 8.04 and it has sqlite3 3.4.2 by default. Now > I want to upgrade sqlite3 to its newest version. > I've already downloaded sqlite-amalgamation-3.6.13.tar.gz, uncompresse

Re: [sqlite] [newbie] How to upgrade sqlite3 in Ubuntu?

2009-05-03 Thread Derrell Lipman
On Sun, May 3, 2009 at 12:32 PM, scientist scientist < scientist92...@yahoo.com> wrote: > Thank you for your fast answer, but my problem still exists after I > followed your instructions. > > Firstly I removed the default sqlite3 using sudo apt-get remove sqlite3 > command. > After that I moved to

Re: [sqlite] How do I Register on sqlite.org

2005-03-18 Thread Derrell . Lipman
David Wheeler <[EMAIL PROTECTED]> writes: > On Mar 18, 2005, at 1:31 PM, Witold Czarnecki wrote: > >> You don't need to register. Just place the ticket: >> http://www.sqlite.org/cvstrac/tktnew > > I did that before, but then I received no notifications when the ticket was > updated or resolved. I'

Re: [sqlite] synchorineze

2005-03-24 Thread Derrell . Lipman
"illias" <[EMAIL PROTECTED]> writes: > how to sync two tables in sqlite.. > ... > in synchorinze table i import new items which not exist in > production table but also items > which price is changed and alredy exist in production table. > It's unclear whether you want the maximum price from the

Re: [sqlite] Prepared Statement Interface

2005-03-26 Thread Derrell . Lipman
Thomas Fjellstrom <[EMAIL PROTECTED]> writes: >> > Change sizeof to strlen here (and in the corresponding statement below); >> > sqlite3_bind_text wants the length of the string, not the size of the >> > pointer to it. >> >> sizeof(SEX)-1 is fine. > > I hope you mean strlen(SEX)-1 > > sizeof is a

Re: [sqlite] concatenation of row with NULLS suppresses all values

2005-03-28 Thread Derrell . Lipman
Jim Dodgen <[EMAIL PROTECTED]> writes: > drop table foo; > create table foo (x,y,z); > insert into foo values (1, null, null); > insert into foo values (2, null, null); > insert into foo values (3, 33, 333); > select x || "-" || y || "-" || z from foo; > > whe I run the above select I only see t

Re: [sqlite] SELECT (...) GROUP BY not returns!

2005-04-08 Thread Derrell . Lipman
Adam Dziendziel <[EMAIL PROTECTED]> writes: > SELECT COUNT(*) FROM drzewo_towar WHERE lft > 13 AND rgt < 14 GROUP BY towar > ... > the engine doesn't return the value (the above query should return 0). COUNT(*) returns NULL if there are no records selected. Change the query like this to accompli

Re: [sqlite] Copying a table between databases

2005-04-14 Thread Derrell . Lipman
"Dennis Volodomanov" <[EMAIL PROTECTED]> writes: > So, "CREATE TABLE AS" will basically duplicate the table that I have (if > I say for example, "CREATE TABLE AS myNewTable AS SELECT * FROM > myOldTable")? Sounds good if that's true :-) You have one too many "AS" in your example. (No "AS" betwee

Re: [sqlite] bug in ORDER BY ?

2005-04-14 Thread Derrell . Lipman
"Miha Vrhovnik"<[EMAIL PROTECTED]> writes: > SELECT * FROM Mactor WHERE id < 9223372036854775807 ORDER BY id DESC LIMIT 1; > > where 9223372036854775807 is Maximum value of signed Int64. > > P.S. If anybody has better Idea of how to get the last/first row (the one > with highest/lowest ID) then co

Re: [sqlite] bug in ORDER BY ?

2005-04-14 Thread Derrell . Lipman
Thomas Steffen <[EMAIL PROTECTED]> writes: > On 4/14/05, [EMAIL PROTECTED] > <[EMAIL PROTECTED]> wrote: >> How about these: >> >> SELECT * from Mactor WHERE id = (SELECT MAX(id) FROM Mactor); >> SELECT * from Mactor WHERE id = (SELECT MIN(id) FROM Mactor); > > I am working on a similar proble

Re: [sqlite] timestamp how to ?

2005-04-22 Thread Derrell . Lipman
"msaka msaka" <[EMAIL PROTECTED]> writes: > how can i use timestamp value in sqlite http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions

Re: [sqlite] sqlite3_bind_text() and SQLITE_STATIC question

2005-04-27 Thread Derrell . Lipman
"Gerry Blanchette" <[EMAIL PROTECTED]> writes: > Clearly, the C++ compiler is substituting SQLITE_STATIC with ((void(*)(void > *))0), as the warning from the Solaris C++ compiler claims: > > Warning (Anachronism): Formal argument 5 of type extern "C" void(*)(void*) in > call to sqlite3_bind_text

Re: [sqlite] Concatenate NULL strings

2005-04-28 Thread Derrell . Lipman
Paolo Vernazza <[EMAIL PROTECTED]> writes: > Hi, > I've a table with some data... > > CREATE table test (ID INTEGER PRIMARY KEY, a TEXT, b TEXT); > INSERT INTO test (id, a, b) VALUES (1, "0356 bla bla bla", NULL); > > I need to retrieve the string resulting from concatenating the a & b > field...

Re: [sqlite] DEFAULT datetime

2005-05-05 Thread Derrell . Lipman
Gabor Szabo <[EMAIL PROTECTED]> writes: > Is it possible to set datetime('now') as the default value of a field ? > > sqlite-2.8.15.bin, I tried this but it gave me an error message: > > sqlite> create table b (r DATE DEFAULT datetime('now')); > SQL error: near "(": syntax error > > Is there some

Re: [sqlite] Linux Newbie...

2005-05-19 Thread Derrell . Lipman
Claudio Bezerra Leopoldino <[EMAIL PROTECTED]> writes: > May someone tell me a clue to execute the SQLite > utility in linux environment? Or a need to compile the > source code to use the utility? > == > [EMAIL PROTECTED] SQLite]$ ls -l > total 372 > -rwxr-xr-x

Re: [sqlite] Using SQLIte database when working messing w/ chroot

2005-06-01 Thread Derrell . Lipman
Mark Hatle <[EMAIL PROTECTED]> writes: > Does anyone have any suggestions on how to solve this? Inform sqlite of > the root portion of the path? Temporarily disable the journal when the > chroot operation will be performed? Or some other mechanism? Are you able to just close the database fil

Re: [sqlite] Index with two columns

2005-06-03 Thread Derrell . Lipman
Jakub Adamek <[EMAIL PROTECTED]> writes: > Hello, please, is there any way to make SQLite use an index on two > columns when I want to select all rows which have some combination of > the two columns? > > My table is: > CREATE TABLE PointFeature ( > DSetId INTEGER, > SectId INTEGER, > PntItemId

Re: [sqlite] SQL question

2005-06-08 Thread Derrell . Lipman
Martin Engelschalk <[EMAIL PROTECTED]> writes: > Hi Lloyd, > > i am not sure, but i checked the documentation and i don't think it ist > sound. > In your Update, the ROWID semms to refer to ingredients.rowid. However, > you want to set properties.rowid. > Can it be that it works, because Propert

[sqlite] SQLITE_SCHEMA can't happen during transaction, right?

2005-06-11 Thread Derrell . Lipman
Let's say I start a transaction with either BEGIN IMMEDIATE or BEGIN EXCLUSIVE, by successfully going through the sqlite3_prepare(), sqlite3_step(), and sqlite3_finalize() process. 1. I then prepare a SELECT statement with sqlite3_prepare(). Since the BEGIN statement did not give me a SQLITE_SCHE

Re: [sqlite] about text search

2005-06-25 Thread Derrell . Lipman
"Lloyd Dupont" <[EMAIL PROTECTED]> writes: > let's say I have a table > like that > CREATE TABLE Infos > { > id INTEGER, > text TEXT > } > > and I want to search a with the word... 'apple', 'cinamon', 'cake' > I could write > SELECT FROM infos WHERE text LIKE '*apple*' AND text LIKE '*ci

Re: [sqlite] Memory Allocated

2005-07-06 Thread Derrell . Lipman
"D. Richard Hipp" <[EMAIL PROTECTED]> writes: > On Wed, 2005-07-06 at 09:38 -0300, Gerson Luís Fontoura Vaz wrote: >> I'm using SQLite for the first time... >> >> We are using SQLite version 2.8.15. >> > > Why, oh why are you using 2.8.15 when 3.2.2 is known to be > faster, smaller, and to cont

Re: [sqlite] My first post, a few wishes..

2005-07-07 Thread Derrell . Lipman
Edwin Knoppert <[EMAIL PROTECTED]> writes: > Sorry, i seem to have posted to another thread somehow.. > > No you misunderstood me, i'm well able to obtain the fields but it seems not > to have the fieldnames when no rows where inserted. I think what you're looking for is PRAGMA TABLE_INFO(table_

Re: [sqlite] Error 21, "library routine called out of sequence"

2005-07-08 Thread Derrell . Lipman
Ben Clewett <[EMAIL PROTECTED]> writes: > Dear SQLite, > > I am running a sequence of inserts: > > BEGIN > INSERT INTO table ... > INSERT INTO table ... > INSERT INTO table ... > INSERT INTO table ... > INSERT INTO table ... > > I am catching the 'kill -1' signal (aka CTRL-C) and executing a final

Re: [sqlite] file db from memory db

2005-07-10 Thread Derrell . Lipman
Cornel Gazdaru <[EMAIL PROTECTED]> writes: > I try copying a table from a :memory: data base to a file data base, but > could not make it work. > Anybody has any suggestions? > Thanks > > I tried something like: > > open a data base :memory: > create table MYTABLE (...) > ... > > attach 'fil

Re: [sqlite] Possible enhancement to SQL logic

2005-07-11 Thread Derrell . Lipman
Ben Clewett <[EMAIL PROTECTED]> writes: > D. Richard Hipp wrote: >>> MySQL and PostgreSQL will use the indexes here, and therefore return the >>> result considerably faster. >>> >> Really? I would be very interested to know what >> query plan MySQL and PostgreSQL use in this example. > > It looks

Re: [sqlite] Saving "Unsigned Long"s in database

2005-07-12 Thread Derrell . Lipman
Jay Sprenkle <[EMAIL PROTECTED]> writes: > On 7/12/05, Gotzon Astondoa <[EMAIL PROTECTED]> wrote: >> ... >> struct hostent *hep; >> ... >> hep=gethostbyname(DomainTxt); >> ... >> strcpy(txtIP, inet_ntoa(*((struct in_addr *)hep->h_addr_list[i]))); >>

Re: [sqlite] mixing GROUP and non-GROUP columns in a query

2005-08-11 Thread Derrell . Lipman
Will Leshner <[EMAIL PROTECTED]> writes: > Say I have a table defined and populated as follows: > > CREATE TABLE test (a TEXT); > INSERT INTO test (a) VALUES ('hello'); > INSERT INTO test (a) VALUES ('hello'); > INSERT INTO test (a) VALUES ('hello'); > > And I perform the following query: > > SELE

Re: [sqlite] modifying insert/updat data in triggers

2005-08-18 Thread Derrell . Lipman
Mark de Vries <[EMAIL PROTECTED]> writes: > But I still don't know how to do what I want to do. Perhaps I need to > explain with a litle more detail what I mean. Consider the following > table. > > create table foo ( > value TEXT, > date_create TEXT, > date_lch TEXT > ); > > Now, whenever I

Re: [sqlite] SQL assistance with selecting the last n records inserted

2005-09-12 Thread Derrell . Lipman
Jay Sprenkle <[EMAIL PROTECTED]> writes: > If you just want 10 records with the highest modified or created time I > think this will do it: > > select * > from tbl > order by max(created_on,modified_on) desc > limit 10 except that if modified_on is null, you won't get that record. This variati

Re: [sqlite] Request for comment: Proposed SQLite API changes

2005-11-04 Thread Derrell . Lipman
Dennis Cote <[EMAIL PROTECTED]> writes: > Furthermore, I don't believe that most users are using an authorizer anyway > (but I could definitely be wrong about that). In that case all the authorizer > callbacks become no-ops don't they? PHP's use of sqlite uses an authorizer callback in the origin

Re: [sqlite] Re: - [sqlite] can anyone reproduce ticket# 1540 (failure to create a primary key)?

2005-11-28 Thread Derrell . Lipman
"Bogdan Ureche" <[EMAIL PROTECTED]> writes: > I am beginning to believe that maybe I was wrong in my assumption that 'if a > table has an index, that index shows in sqlite_master'. Then my problem is > now to find another way to get index information for a table. Any > suggestions? An index for a

  1   2   3   >