Re: [sqlite] Passing Value from one query to another

2010-02-06 Thread Eric Bohlman
BareFeet wrote: > In general, I think it's much better (performance and logic) to do > all you can in SQL, without passing values out of SQL results, into > your non-SQL code, then re-injecting back into another SQL query etc. With SQLite, that's not really going to make a difference. Since it's

Re: [sqlite] Near misses

2009-06-28 Thread Eric Bohlman
Simon Slavin wrote: > On 26 Jun 2009, at 12:25pm, Alberto Simões wrote: > >> one adition, one remotion or one substitution > > I am always amazed at how well people use English. For your word > 'remotion' you probably mean 'removal' or 'omission'. You have joined > the two possibilities

Re: [sqlite] DBD::SQLite reporting corruption sqlite3 CL program does not

2009-06-28 Thread Eric Bohlman
Craig Talbert wrote: >>From Perl, when I attempt to make a database connection using SQLite, > I get the following error: > > [Tue Jun 23 17:10:22 2009] projectory.cgi: > DBI->connect(dbname=projectory.sqlite3) failed: database disk image is > malformed at ./projectory.cgi line 1577 > > At line

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

2009-05-05 Thread Eric Bohlman
Derrell Lipman wrote: > The amalgamation probably installed into some directory not in your path. > You should look at where it installed (re-run ../configure and look at its > output, which should tell you where it will install to. For Ubuntu, you > almost certainly want it to install into

Re: [sqlite] Adding data with periods

2008-12-15 Thread Eric Bohlman
Mohd Radzi Ibrahim wrote: > It seems to works either way. > > I'm just wondering is there any hidden reason that single quote is > preferred? Portability? > Or is double-qoute has some kind of special meaning that we should use it > for that special purpose? If what's enclosed in the double

Re: [sqlite] Unique ids for each record

2007-08-21 Thread Eric Bohlman
Sreedhar.a wrote: Hi Lokesh, Thanks for your suggestions. My proble is assigning a unique record for each record in a table. The database table name MUSIC. I have 3 columns Artist Album and Tracks. Artist1 Album1 Track1 Artist2 Album2 Track2 Artist3 Album2 Track3 I need to assign a

Re: [sqlite] Regexp

2007-05-15 Thread Eric Bohlman
T wrote: I know there are hooks for adding a Regexp function/operator ourselves, but I need to know it's available on other machines with standard install. It's the same reasoning, I guess, as why Trim() was added, but Regexp seems to serve a wider need. I'm pretty sure the problem is that

Re: [sqlite] Pragma table_info(), why no fields like UNIQUE, AUTOINCREMENT

2007-03-14 Thread Eric Bohlman
COS wrote: A small opinion on that matter: what I would really like to see is something like system tables. Today sqlite uses only sqlite_master to keep information about its objects and parsing is required to getter better info of each object (if one needs to). Using other system tables to keep

[sqlite] More problems with fts1

2006-10-14 Thread Eric Bohlman
The following query SELECT title,snippet(stories,"~","~","...") FROM titles JOIN stories ON titles.story_id=stories.rowid WHERE stories.body MATCH ? results in a complaint that MATCH is being called in the wrong context. Prefixing any/all column names with "+" doesn't change this.

Re: [sqlite] converting a sqlite table to dbf

2006-08-31 Thread Eric Bohlman
P Kishor wrote: Thanks, so the answer is there is no straight-forward way of doing this. I will export to CSV, and then rebuild a dbf using Perl. Since you've got Perl, you can skip the CSV step; just make sure you have DBI and DBD::SQLite and DBD::XBase installed and create connections to

Re: [sqlite] Functions embedded in SQL statements

2006-06-21 Thread Eric Bohlman
[EMAIL PROTECTED] wrote: I'm thinking that all documentation is better placed in a wiki. Hmmm. The problem I see is that it makes access to the full documentation contingent on connectivity to a possibility ephemeral external site. Maybe the solution is to incorporate wiki snapshots into

Re: [sqlite] SQLite as R data store

2006-05-27 Thread Eric Bohlman
Andrew Piskorski wrote: As an R user, I'm happy to see the project below, it could become a very handy use of SQLite. I suspect it's well out of scope for that project, but it would be particularly cool to eventually see some of the integration go the other way, and allow use of SQLite's SQL

Re: [sqlite] Extra functions - New Project?

2006-05-26 Thread Eric Bohlman
Mikey C wrote: StdDev Variance When implementing these, make sure *not* to use the "textbook" one-pass formula (mean of the squares - square of the mean); it simply doesn't work properly in floating point (you can find yourself subtracting one large number from another and losing most of

Re: [sqlite] Complile and connecting to SQLite

2006-05-01 Thread Eric Bohlman
Aaron Jones wrote: Windows XP, don't know what compiler to use. MinGW and all the common versions of Visual Studio work. I am creating a GUI to SQLite, so need my interface to connect to SQLite, and wanted to know what it connected to, the source code or the exe file. SQLite is an

Re: [sqlite] LIKE operator with prepared statements

2006-04-07 Thread Eric Bohlman
Dennis Cote wrote: You could also do this: SELECT x from y WHERE y.x LIKE '%' || ? || '%' ; The || operator concatenates the % characters with your string. Now you don't need to massage the string in the calling code. Six of one, half dozen of the other. Note, though, that as currently

Re: [sqlite] Compatability issues with DBD::SQLite and SQLite v3.3.3 and 3.3.4

2006-03-06 Thread Eric Bohlman
Nathan Kurz wrote: On Mon, Mar 06, 2006 at 06:24:13PM -0800, Steve Green wrote: Hi, I'm hoping that someone can shed some light on the following issue that I'm seeing. When I attempt to create a temp table using DBD::SQLite (v1.11) and either SQLite v3.3.3 or v3.3.4, I get the following

Re: [sqlite] aynchronous loading

2006-02-07 Thread Eric Bohlman
chetana bhargav wrote: Does sqlite provides asynchronous loading of data. Basically if I have something around 3000 records and want to do some query, instead of returning the result in one single query is it possible for me to relinquish the control to other apps so that I wont get a time out

Re: [sqlite] Slow query after reboot

2006-01-19 Thread Eric Bohlman
Geoff Simonds wrote: The app is running on Windows XP machines Is it possible that indexing services are enabled and XP is trying to index the database file?

Re: [sqlite] Fw: Slow sqlite_step

2006-01-15 Thread Eric Bohlman
Carl Jacobs wrote: Hello, I'm a new user to this group as I haven't had any issues with sqlite till now. I tried searching to see if this question has been asked before, but couldn't find anything. I'm using Windows. I have a table with ~45 colums and ~17000 records. I do a search for some

Re: [sqlite] Is it possible to determine if data is string or numeric inside a callback in C?

2006-01-15 Thread Eric Bohlman
Downey, Shawn wrote: Does anyone know if it is possible to determine if data is string or numeric inside a callback in C++? I am using sqlite 3.2.7. Unless you have some requirements that your code be compatible with SQLite 2.*, I'd suggest you rewrite it to use the prepare/step interface;

Re: [sqlite] Final Year Project/Dissertation help required!!!!

2005-12-14 Thread Eric Bohlman
m christensen wrote: What you are doing is needs analysis and by definition requires 'help' or input from others. This is not doing YOUR work for you. On the other hand needs analysis is much more complex than just asking users what they want. Most of the time they simply don't know.

Re: [sqlite] how can I import CSV file into SQLite quickly

2005-12-07 Thread Eric Bohlman
Arjen Markus wrote: Hm, there is a CSV reading module in Tcllib, so one could contemplate using Tcl instead of Perl for this. That ought to take care of the quotes and other nastiness... Perl's Text::CSV module available from CPAN also handles these issues.

Re: [sqlite] Improve INSERT INTO tablename performance for bulk loads....

2005-11-25 Thread Eric Bohlman
Michael Scharf wrote: Eric Bohlman wrote: Using prepared statements with placeholders would reduce the amount of parsing even further. Well, I think INSERT INTO tablename VALUES (0,'foo') , (1,'bar') , (2,'baz'); is a kind of prepared statement, or it could be implemented as a prepared

Re: [sqlite] SQL syntax possibilities

2005-11-16 Thread Eric Bohlman
[EMAIL PROTECTED] wrote: uSQLite does not (generally) enter into the details of the query it is passed. There is however an exception for security. uSQLite requires a login and (depending on the user and network) assigns the user a level: 0: No access 1: Select only 2: Update/Insert only 3:

Re: [sqlite] optimizing out function calls

2005-11-13 Thread Eric Bohlman
[EMAIL PROTECTED] wrote: Nathan Kurz <[EMAIL PROTECTED]> wrote: SELECT uid, match("complex", "function", vector) AS match FROM vectors ORDER BY match DESC LIMIT 20; SELECT uid, mx FROM (SELECT uid, match(...) AS mx FROM vectors LIMIT -1) ORDER BY mx DESC LIMIT 20; The LIMIT -1 on

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

2005-11-03 Thread Eric Bohlman
Dennis Cote wrote: I think both of these proposed changes are useful enhancements to SQLite. I also think it would be better to add a new sqlite3_step_v2() API function that does this. This will eliminate the need to change the base version number, since existing code can continue to use the

Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

2005-11-02 Thread Eric Bohlman
Henry Miller wrote: As for 5/2, my grade school teachers taught me that if I round it at all, the answer is 3, NEVER 2. It is only latter in life that I learned about bankers rounding which sometimes is 2, sometimes 3.I have never seen a justification for rounding to 2, except for the

Re: [sqlite] Re: Number of rows in a query result

2005-11-01 Thread Eric Bohlman
Edward Wilson wrote: What I was trying to say was: with other db products the drivers (or something somewhere) calculated the number of rows returned in a query automagicly. I have never had to do anything 'extra' to get the number of rows in a query other than resultset-object.rowcout -

Re: [sqlite] SQLite as a Windows kernel tool

2005-10-29 Thread Eric Bohlman
Ken & Deb Allen wrote: I had a quick look at some of the code, but I am not certain whether all, or even most, of these warnings can be safely ignored or not. I tried modifying the code to add explicit casts to eliminate all of the warnings, which worked, but I do not know whether or not

Re: AW: [sqlite] and , or

2005-10-20 Thread Eric Bohlman
Martin Engelschalk wrote: The problem seems to be that sqlite makes a difference between an empty string and a null value. s/sqlite/SQL/ The SQL standards all say that nulls never compare equal to anything, not even other nulls. SQLite's behavior here is the correct one.

Re: [sqlite] Static library for sqlite for Windows

2005-10-14 Thread Eric Bohlman
Murugan, Muthulakshmi wrote: I have downloaded the sqlite 3.2.7. binary version for Windows. But I found no libsqlite3.lib. Can anyone help in this regard? See This question comes up so often that it should really be included in the FAQ.

Re: [sqlite] Tool to load a database and see all tables and fields.

2005-09-28 Thread Eric Bohlman
spudse bud wrote: I always was a big fan of phpmyadmin because when php/mysql errored I could check table/field names etc to see where my script goes wrong. I am wondering if a easy tool exsists for SQLite, that just lists the tables and fields in a database. I have tried sqlitemanager, but I

Re: [sqlite] SQL Queries

2005-09-26 Thread Eric Bohlman
Chris Gurtler wrote: I'm pretty new to SQLite, and am just looking for a few pointers on SQL queries, this is an example of a query from a crapy MS Access database that I want to convert to SQLite, but it fails. it says c.group_id does not exist. I'm wondering if anyone has got some tips on

Re: [sqlite] group by error in 3.2.6?

2005-09-20 Thread Eric Bohlman
Edzard Pasma wrote: There is a difference in the behaviour of GROUP BY in version 3.2.6. If you group by a column that contains NULL values, each NULL value comes on a seperate output line. Conceptually not so bad, if we read NULL as "unknown" and not as "empty". But I guess it is an error.

Re: [sqlite] Unlucky number for the ROUND function

2005-08-30 Thread Eric Bohlman
Edzard Pasma wrote: I found a number where the ROUND () function goes wrong: SQLite version 3.2.5 Enter ".help" for instructions sqlite> select round (9.95, 1); :.0 I get 9.9 (running on Win98, compiled with MingW).

[sqlite] ALL and ANY

2005-08-29 Thread Eric Bohlman
Are there any (hehe) plans to implement the ALL and ANY keywords for testing against subqueries?

Re: [sqlite] Merging to tables

2005-06-18 Thread Eric Bohlman
Rasmus Christian Kaae wrote: I was wondering if there was some way of merging to tables *fast* for querying? I have the following schemas: CREATE TABLE a (term text, location blob); CREATE TABLE b (term text, location blob); What I would like to do is to iterate through *all* records in a and

Re: [sqlite] request for additions to sqlite 3.xx

2005-05-08 Thread Eric Bohlman
basil thomas wrote: As for storing queries, I'm not sure how useful this feature is given that the database engine itself is part of your program. For simple queries your best bet is a static sqlite3_stmt object, which you prepare once at program initiation and refer back to each time it is

Re: [sqlite] Multiple Tables on one Flat File

2005-04-14 Thread Eric Bohlman
[EMAIL PROTECTED] wrote: I ended up creating a %dbh hash with $table as the index along with one Operating System file for each table. I was hoping there was a way not to create so many Operating System files because of the extra Administration they require. can you think of any way around this? I

Re: [sqlite] Multiple Tables on one Flat File

2005-04-13 Thread Eric Bohlman
D. Richard Hipp wrote: Not only CREATE statements, but also DROP and VACUUM statements will also invalidate all previously prepared statements. Once a prepared statement is invalidated, it must be prepared again. And also ALTER TABLE.

Re: [sqlite] Multiple Tables on one Flat File

2005-04-13 Thread Eric Bohlman
[EMAIL PROTECTED] wrote: I am running into a situation that does not make sense. I have allocated a flat file under the Operating System as follows (notice that autocommit is off): $dbh = DBI->connect('dbi:SQLite:' . $dbms_file , "", "", { RaiseError => 1,AutoCommit =>

Re: [sqlite] beat 120,000 inserts/sec

2005-04-08 Thread Eric Bohlman
Al Danial wrote: A scientific application I work with has clumsy data retrieval options. I dumped the application's output--integer and floating point numbers--into an SQLite database and soon after began to enjoy the power of SQL to pull out interesting results. The main complaint for making the

Re: [sqlite] Quoestion on Order By ... ?

2005-03-31 Thread Eric Bohlman
Shum [Ming Yik] wrote: Actually Sqlite same as other SQL in return order such as sample (1) ( it seems follow the insert order when no specific order by ... ) But Sqlite break the rule (may be it is not a rule), when part of the whole Order by string passing into the select statement ... as

Re: [sqlite] Prepared Statement Interface

2005-03-26 Thread Eric Bohlman
Eugene Wee wrote: As a test, I created a database containing a single table [children]. I filled it with the names of 3 girls and 2 boys, and then tried to write a program that selects the boys and girls separately and printed their names. However, I have difficulty in binding text with

Re: [sqlite] Bug from ticket 1141 breaks existing applications

2005-03-06 Thread Eric Bohlman
George Ionescu wrote: while trying to upgrade to sqlite 3.1.3, I've encountered the bug described in ticket #1141 (sqlite returns the primary key's column name, if any, instead of the ROWID column, in a query like SELECT rowid, * FROM table). Any idea how to fix this (if it's an easy one),

Re: [sqlite] Does SQLite.NET not support AUTOINCREMENT

2005-03-01 Thread Eric Bohlman
[EMAIL PROTECTED] wrote: I used this SQL: CREATE TABLE NewEmployees(EmployeeID INTEGER PRIMARY KEY AUTOINCREMENT, LastName TEXT, FirstName TEXT); and using SQLite3.exe did this SQLite3 employees.db .read create.sql .exit which created my database but no tools nor the SQLite.NET provider can read

Re: [sqlite] Functions

2005-02-25 Thread Eric Bohlman
marco wrote: Hi *, Where I can find the list of the internal functions? for example: datetime() strftime() All but the date/time functions are listed in func.c in a table that's part of sqlite3RegisterBuiltinFunctions(); the date/time functions are listed in a similar table in

Re: [sqlite] strcasecmp build error in VC++

2005-02-20 Thread Eric Bohlman
Kevin Tew wrote: Same here, except I used the sqlite_stricmp function or something like that. Could a maintainer do a quick grep for strcasecmp and fix this. Thanks Kevin Tim Anderson wrote: I got a linker error when building sqlite3.exe with Visual Studio .NET 2003, sqlite version 3.1.2. The

Re: [sqlite] TYPEOF in triggers

2005-02-10 Thread Eric Bohlman
Witold Czarnecki wrote: I just tested it on 3.0.8 and - you are right - it works. Is there any reason to use 2.8 instead of 3.0? I use SQLite via python (pysqlite). 3.x uses a different database file format than 2.8, and the APIs are sufficiently different that they'd need different Python

Re: [sqlite] ? placeholder not allowed in LIMIT or OFFSET clause of SELECT statement?

2005-02-02 Thread Eric Bohlman
Eric Scouten wrote: James, thanks for that update. I've filed a new ticket for this issue, #1096 ( I hope you don't mind that I quoted your response and implementation suggestion in the body of that ticket. There you express some concerns about

Re: [sqlite] String Concatenation

2005-01-05 Thread Eric Bohlman
Keith Herold wrote: OwnerID, AString, Sequence 1, 'concatenate', 0 1, 'some', 1 1, 'strings', 2 1, 'together', 3 What I need to do is create a single string out of the AString, Sequence pairs, for a given owner. Obviously I could do this through some C++ code, but I would prefer to do it within

Re: [sqlite] php4/sqlite - sqlite_escape_string doesn't function

2004-12-25 Thread Eric Bohlman
Peter Jay Salzman wrote: Hi all, This piece of code kept giving error messages that looked like some of my VALUES were getting parsed by php: sqlite_query( $handle, " INSERT INTO course VALUES ( sqlite_escape_string($semester),

Re: [sqlite] Single row insert failure

2004-11-09 Thread Eric Bohlman
Roderick A. Anderson wrote: So here is the issue. Inserting a single row into a SQLite2 database doesn't work using perl on a Windows system. I have a data in a MS SQL Server database I need to process on a Linux box (and getting ODBC to work isn't an option at this time) so my cheat is to

Re: [sqlite] Join function in select statement

2004-11-02 Thread Eric Bohlman
Marcel Strittmatter wrote: Hi all I like to have a join function that I can use in a select statement like this: select join(name, ',') from people; this should produce something like this: scott,martin,jones,adams It is not possible to use perl or another script language. The only language I

Re: [sqlite] Support for ODBC?

2004-10-15 Thread Eric Bohlman
R S wrote: Can an application access data from SQLite via ODBC? Didn't see documentation on the same. If you have an ODBC driver for SQLite, yes. The "SQLite Wrappers" page of the wiki mentions "ODBC driver for SQLite.;. I don't know anything about it,

Re: [sqlite] Single-file databases: how do they do it?

2004-10-07 Thread Eric Bohlman
Cena, Resty wrote: How do single-file databases handle multi-table databases? Do they simply pack separate physical table files into a single file, or have they found a way to store in one table all the definition and data values of a database? Can someone un-curious-ify me? Thanks. To see how

Re: [sqlite] sqlite3ErrStr giving linker error

2004-10-06 Thread Eric Bohlman
Steve Frierdich wrote: The sqlite3ErrStr function is giving me a linker error when compiled. All the other sqlite version 3_0_7 functions used compile correctly. Sqlite is compiled in to a static library, and that library is used in a Win32 application compiled in visual studio. The exact