Re: [sqlite] Looking for SQLite app builder

On Fri, 17 Aug 2018, Bob Sisson wrote: I am looking for a cross platform app builder that will use SQLite to build an inspection checklist that will build a "summary page" that takes selected check boxes, radio buttons and pull downs and looks up their expanded values and fills in a summary page

Re: [sqlite] Are you getting spam when you post to sqlite-users?

On Tue, 17 Apr 2018, Simon Slavin wrote: Okay, that's enough. Thanks for the help, everyone. Simon, FWIW, I haven't seen any spam from this mail list. I do run my own MTA which aggressively rejects known spam. Over 20 years I doubt there have been more than a handfull from all mail lists to

Re: [sqlite] Mailinglist question

On Wed, 9 Aug 2017, Nico Williams wrote: Another thing I've done in the past is: download list archives, run a script to fix the From lines so the archive is then a proper mbox format, then use mutt. :) It helps if the archives keep Message-ID headers. Nico, I've not followed this thread so

Re: [sqlite] Datatype for prices (1,500)

On Wed, 30 Nov 2016, Igor Tandetnik wrote: Store it as an integer, in $.0001 units. So $1.500 would be represented simply as an integer 1500. All you need to store in the database table is the number. How it is formatted for viewing or printing is controlled by the user interface. Rich

Re: [sqlite] Version 3.15.0 released

On Fri, 14 Oct 2016, D. Richard Hipp wrote: SQLite version 3.15.0 is now available on the SQLite website: Thank you, Richard and all other devs, for an excellent product. Your efforts are certainly appreicated even when not publicly expressed. Carpe weekend, Rich __

[sqlite] Mailing List Request

On Fri, 11 Mar 2016, Rousselot, Richard A wrote: > Any way we could have the mailing list strip tabs from the subject lines? > I have been getting very odd subject formatting lately. Richard, That's dependent upon the mail user agent you use, not the mail list. The mail list management softwa

[sqlite] Mozilla wiki 'avoid SQLite'

On Sun, 14 Jun 2015, Richard Hipp wrote: > SQL (and I speak in general terms here, not just of SQLite) provides way > more than syntactic sugar over b-trees. The syntactic sugar over b-trees > part is the *least* of the many advantages of SQL. Other more important > features include: Very well

[sqlite] Sample SQL code thats beyond me :(

On Sat, 23 May 2015, Rob Willett wrote: > Thanks again and as its Saturday have a drink. If any of you are near York > in England I?ll happily buy you a pint. Rob, I'm in the upper left corner of the US so I'll have to pass on your kind offer. Germane to your fundamental concern, over the

[sqlite] Sample SQL code thats beyond me :(

On Sat, 23 May 2015, Rob Willett wrote: > What I want to do is join the table Users and Perimeter Notifications > together but only if the value of Devices.Holiday_Mode is either non > existent or if Devices.Holiday_Mode does exist and its 0. If > Devices.Holiday_Mode is 1 it means the user is on

[sqlite] Appropriate Uses For SQLite

On Wed, 18 Feb 2015, Richard Hipp wrote: > Please be my "focus group", and provide feedback, comments, suggestions, > and/or criticism about the revised document. Send your remarks back to > this mailing list, or directly to me at the email in the signature. Richard, It is clear and well orga

Re: [sqlite] Looking for SQLite schema doc generator (in HTML) for tables, fields, etc

On Tue, 3 Feb 2015, Gerald Bauer wrote: I'm looking for a little tool that reads in an SQLite schema (e.g. beer.db, football.db, etc.) and outputs (generates) documentation for tables, fields etc. as a single HTML page or as HTML pages. Any insight appreciated? Gerald, While not in html f

Re: [sqlite] ORDER BY and LIMIT regression

On Mon, 19 Jan 2015, Richard Hipp wrote: Thank you for reporting the problem. Certainly! We always fix every problem that we are aware of in SQLite. But this problem had not been previously reported to us, and did not occur in any of the 168 million test cases that we ran prior to releasi

Re: [sqlite] PK References One of Two Other Tables

On Sun, 11 Jan 2015, Simon Slavin wrote: You can't do the latter. Foreign keys can reference only one table. You could create yet another table, which just supplies primary keys, but it would seem that this would just duplicate a function of your 'items' table. Simon, That's what I though

[sqlite] PK References One of Two Other Tables

Items to be raffled can be donated or purchased. The Donations and Purchases tables each have the item ID as their PK. The Raffles table should have the PK as either the Donations or the Purchases item ID. How do I write the DDL creating the Raffles table so that the PK is either a donated or

Re: [sqlite] Version Mis-match In /usr/lib/pkgconfig/sqlite3.pc

On Thu, 30 Dec 2010, Eric Smith wrote: > Probably sqlite3.pc was installed by your normal system package manager > (like yum or apt) as part of the sqlite-devel package. What distro are > you in? Eric, Slackware-13.1. The SQLite version that comes with the distribution is well back from the

[sqlite] Version Mis-match In /usr/lib/pkgconfig/sqlite3.pc

What controls the information in /usr/lib/pkgconfig/sqlite3.pc? Here, it contains: # Package Information for pkg-config prefix=/usr exec_prefix=${prefix} libdir=/usr/lib includedir=${prefix}/include Name: SQLite Description: SQL database engine Version: 3.6.23.1 Libs: -L${libdir} -lsqlite

Re: [sqlite] SQLite-3.7.4 Amalgamation?

On Tue, 7 Dec 2010, Richard Hipp wrote: > I changed to a more consistent naming scheme for all of the build products: > > sqlite-PRODUCT-OS-ARCH-VERSION.zip > > with the OS and ARCH being omitted for source-code products. In your > case, you probably are looking for Richard, That's how i

[sqlite] SQLite-3.7.4 Amalgamation?

What happened to the souce tarball of the amalgamation? I'm wondering if the change to the autoconf version will break the Slackbuild script I use. Was there something wrong with the tarballs of previous versions? Rich ___ sqlite-users mailing lis

Re: [sqlite] Multiple databases vs. Multiple tables.

On Tue, 26 Oct 2010, Dariusz Matkowski wrote: > Queries will be done across the servers to aggregate the content. I am > concern about the locking mechanism, if I write to the single database and > I represent the servers as tables I will have no access to read the other > servers/tables, but if I

Re: [sqlite] Multiple databases vs. Multiple tables.

On Tue, 26 Oct 2010, Dariusz Matkowski wrote: > Only one user (the browser). The DB is on a device the same place where > the user (Browser) is. A process will collect the information about the > servers and their contents and story it to the DB at the same time the > user may ask for the contents

Re: [sqlite] Multiple databases vs. Multiple tables.

On Tue, 26 Oct 2010, Dariusz Matkowski wrote: > in your opinion is it better (performance, maintainability etc...) to > have multiple databases or multiple tables. The problem I am facing is as > follows. I have many media servers containing a large amount of images > music and videos, let's assu

Re: [sqlite] Query to compare two sqlite databases

On Fri, 24 Sep 2010, luuk34 wrote: > you mean something like: > select id1, id2 from callprog a where id1 not in (select b.id1 FROM > callprog b where b.id1=a.id1 ); Yeah; much better. > But what is there is more than 1 column? it will grow in complexity when > you have a lot of columns.

Re: [sqlite] Query to compare two sqlite databases

On Fri, 24 Sep 2010, John Reed wrote: > I compare an application every few days which has sqlite as it's client > database. I look at the content and check whether documents have made it > into the application after it has been built. I also check the metadata in > the sqlite client database for c

Re: [sqlite] Import and export databases from/to SQL dump files

On Wed, 22 Sep 2010, Nimish Nayak wrote: > I saw the shell.c file in src and figured out how exactly it exports > databases to SQL dump files and implemented the same in C. But i could not > find anything for importing the databases to SQL dump files. I've not looked at the source code, but th

[sqlite] Syntax for Counting Rows By Multiple Groups

I've a table with 15 columns, including industry number, industry description, and state. I'm trying to formulate the proper SELECT statement to return the count of rows for each industry number/description in each of the 5 states. I've looked at the aggregate function chapter in Rick van der La

Re: [sqlite] WHERE Clause Not Working On Database

On Tue, 14 Sep 2010, Jay A. Kreibich wrote: > If that's a copy-paste from a default sqlite3 session, the single quotes > are part of the value. i.e. you have four-character values, such as > "'ID'". Jay, That's because I used single quotes to delimit strings in the original .csv file. Re

Re: [sqlite] WHERE Clause Not Working On Database

On Tue, 14 Sep 2010, Paul Corke wrote: > What do you get from: > > select distinct state from companies; Paul, sqlite> select distinct state from Companies; 'ID' 'NV' 'OR' 'UT' 'WA' That's why this inability to use the WHERE clause completely escapes my understanding. Rich _

[sqlite] WHERE Clause Not Working On Database

I cannot select rows from a table using the WHERE clause and cannot find my error. Perhaps others will see what I miss. The table, Companies, has 1500+ rows. One column is defined as state CHAR(2), but the select statement seeking all rows where state = 'OR' for example returns nothi

Re: [sqlite] Importing csv to SQLite

On Mon, 13 Sep 2010, Mithun Nair wrote: > When i try importing it into a SQLite table, i get some errors like > "expected 2 columns of data but found 1". Later i found that importing a > comma separated file into SQlite wont work because a comma is considered > as a delimiter by the the SQLite en

Re: [sqlite] Issues .import(ing) a .csv file

On Sun, 12 Sep 2010, Simon Slavin wrote: > That's one danger, depending on how you have set up your delimiters. I > think your earlier post about commas is more likely to solve your problem. Simon, Yup. It did. Thanks, Rich ___ sqlite-users maili

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

On Sun, 12 Sep 2010, Bob Keeland wrote: > Access may not be the best database for my programs, but then again my > needs are not that great. I just need to search a database and then search > the results of the first search, then search the results of the second > search, etc. That sounds simple t

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

On Sat, 11 Sep 2010, Sam Carleton wrote: > If you need to learn the basics of SQL, I would highly recommend the book SQL > For > Smarties. > It covers all the basics

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

On Sat, 11 Sep 2010, Bob Keeland wrote: > While I greatly appreciate the help I've gotten on SQLite in general, I > still wonder about the last part of my questions below. Can anyone > recommend a good book for learning to use SQLite? What about these that I > found on Amazon.com > The Definitive

Re: [sqlite] Issues .import(ing) a .csv file

On Sun, 12 Sep 2010, Simon Slavin wrote: > Any commas in it ? Any single or double quotes ? Simon, Probable. I need to look. There are apostrophe's in a long text column that I thought might be interpreted as single quotation marks. Thanks, Rich ___

Re: [sqlite] Issues .import(ing) a .csv file

On Sat, 11 Sep 2010, Melton Low wrote: > When you saved the spreadsheet as a csv file, you probably took the > default delimiter which happens to be a comma. You should check for a > cell with data that has a comma in it. The embedded comma would be treated > as a column separator which would res

[sqlite] Issues .import(ing) a .csv file

I cannot see what's wrong with a line in a .csv file. SQLite tells me it expected 14 columns of data, but found 15. No matter how many times I count the columns (exported in .csv from an OpenOffice.org Calc spreadsheet with 14 columns), that's all I find. Nothing appears wrong with the following

[sqlite] ADOdb with SQLite-3.7.2

I've not before dealt with ADOdb, but a CMS I'm considering using to change my company web site uses that. My Google search found one reference (from 2009) suggesting that ADOdb does not play well with sqlite3. Is this true (still)? If I can use it, I'll take a deeper look at the CMS. Thank

Re: [sqlite] copy data from one db to another

On Wed, 9 Jun 2010, Vivien Malerba wrote: >> I forgot to mention, the source is a PostgreSQL db, not SQLite, so >> there's no source file to copy.  Though a backup might be >> interesting ... > You can use Libgda's gda-sql tool in which you can: > * open a connection to the PostgreSQL db (for exa

Re: [sqlite] database development - correct way?

On Wed, 9 Jun 2010, Oliver Peters wrote: > So I assume that it is not(!) a mistake not(!) to use a composite PK in my > table "customer" (customernumber,customerorigin) and to refer to it from > the table "order" where I had to use these fields as a composite FK? Oliver, Too many negatives th

Re: [sqlite] database development - correct way?

On Wed, 9 Jun 2010, Tim Romano wrote: > Typically, Orders are divided into OrderHeader and OrderDetail tables: > > OrderHeader > id integer primary key > orderdate > customerid > > OrderDetail > id > orderid references OrderHeader(id) > articleid references article(id) > quantity int > > And you

Re: [sqlite] SQLite template files

On Tue, 6 Apr 2010, BareFeet wrote: > Thanks to those who replied on this topic, but no-one offered any > repository of SQLite template files. Does that mean there aren't any > available? Tom, That's because what you seek does not exist. User interfaces are separate from the rdbms back end. T

Re: [sqlite] SQLite version 3.6.23

On Tue, 9 Mar 2010, D. Richard Hipp wrote: >> The download page no longer mentions any version of SQLite: > Oops. Does now. And I thought it was me! I got lost in the repository, started over, tried a different route, and suddenly the familiar download page appeared. Whew! Rich

Re: [sqlite] Newbie question - SQLite the best choice?

On Sat, 6 Mar 2010, Richard Cooke wrote: > Our application could have up to 10,000 users via a public facing web > site. As a first stab at the schema, I thought I'd have one "Master User" > database which will probably look like this: Richard, If I recall correctly, SQLite does not do well

Re: [sqlite] Aggregate From Two-Table SELECT

On Thu, 18 Feb 2010, P Kishor wrote: > SELECT l.llid, l.name, SUM(s.endKM - s.beginKM) AS distance > FROM lotic AS l JOIN streamlength AS s ON l.llid = s.llid > WHERE l.llid = '1226038453652' > GROUP BY l.llid, l.name Thank you. Now I know. Much appreciated, Rich

[sqlite] Aggregate From Two-Table SELECT

I'd appreciate learning how to correctly write a SELECT statement that reports the SUM of one returned column. I can select all relevant rows, but don't know where to put the SUM(distance) phrase: SELECT l.llid, l.name, s.endKM - s.beginKM AS distance FROM lotic AS l, streamlengt

Re: [sqlite] SQLite Training

On Wed, 27 Jan 2010, Roger Binns wrote: > The main drawback is that the book hasn't been revised since 2006 since > when the virtual machine has changed, foreign key support is present, you > can use threading freely, there is incremental Blob I/O, virtual tables > are available, VFS is available,

Re: [sqlite] SQLite Training

On Thu, 28 Jan 2010, Simon Slavin wrote: > ... but that requires that you understand at least a bit of relational > databases theory. We've recently had questions from people who don't > understand an INDEX, or what you would want one, or how to make one which > is useful for a particular query.

Re: [sqlite] SQLite Training

On Thu, 28 Jan 2010, nyetngoh wong wrote: > I'm currently working on a project that uses SQLite and would like to know > if there is any SQLite certification available in Singapore. Do you > provide any forms of technical training or courses on using SQLite > efficiently ? All you need is to k

Re: [sqlite] SQLite Version 3.6.22

On Tue, 5 Jan 2010, D. Richard Hipp wrote: > It has been our habit for the past several years to do at least one SQLite > release per month. However, we are thinking of backing off from that > schedule and releasing every other month. If we stick to this plan, it > means the next release (3.6.23)

Re: [sqlite] SQL question

On Fri, 11 Dec 2009, Florian Schricker wrote: > Schema of DB (simplified): > - Oper (string) > - Product (string) > - Category (string) > - Name (string) > - CreateTS (Timestamp) > - Value (Double) Florian, The schema refers to the set of tables, and the attributes within each table. Is the a

Re: [sqlite] It's done, except for this nit:

On Sun, 29 Nov 2009, Ted Rolle, Jr. wrote: > I need for computed total price to display with leading and trailing > zeros. > E.g. > 1 -> 1.00 > 2.5 -> 2.50 > .9 -> 0.90 Ted, It is most common to handle display issues in the UI component. _Most_ applications have three components: a database

Re: [sqlite] Output in currency format

On Thu, 12 Nov 2009, Dan Bishop wrote: > Microsoft Excel has a similar problem. I ran into it back when I was > working in a credit union and tried to import a CSV file containing credit > card numbers. Wouldn't have noticed except that credit card numbers are > 16 digits long and double only ha

Re: [sqlite] Converting .dbf to SQLite

On Thu, 12 Nov 2009, Alex Mandel wrote: > Using R might actually be a convenient way to do it all in essentially > one step, and technically batch scriptable. I found a perl script that converts .dbf to .csv. It's then trivial to import the .csv into SQLite. Rich _

Re: [sqlite] ogr2ogr

On Thu, 12 Nov 2009, stormtrooper wrote: > There is a command line tool (ogr2ogr) that runs on linux or windows that > converts dbf to sqlite, csv, xml, etc. It is primarily a converter for > geographic data but supports tabular data as well. OGR is part of fwtools. Keith, Thank you. I'm gett

Re: [sqlite] Converting .dbf to SQLite

On Thu, 12 Nov 2009, Jean-Christophe Deschamps wrote: > I'm pretty sure OpenOffice can do a number of such conversions, free and > portable. About command-line tools for linux, I just don't know. Perhaps. I learned today that only the Winduhs version of OO.o can import .mdb files; the linux v

Re: [sqlite] Converting .dbf to SQLite

On Thu, 12 Nov 2009, dave lilley wrote: > Not trying to be silly here but why not write a wee program that reads in > the dbf file and for each row read in write the data into an sql file? Because I'd have to research the format of the .dbf file and I'd probably be re-inventing the wheel. Ric

[sqlite] Converting .dbf to SQLite

Now that I have a working tool to convert from Access .mdb to sqlitedb files, I need one for dBASE .dbf files. Or, a conversion to .csv will work, too. Needs to run on linux, of course. My Google searches turned up a bunch of tools for the Windows platforms, supposedly free converters that h

Re: [sqlite] Converting .mdb Files

On Wed, 11 Nov 2009, Jan wrote: > I am using this one: > http://code.google.com/p/mdb-sqlite/ > > Have not tried it on linux though. Jan, It's a java app so it should run on anything. What I need to do now is find the java ant tool on my system. Thanks, Rich

[sqlite] Converting .mdb Files

I have a 12.1M .mdb file (soils data) that I want to convert to SQLite. I downloaded, built, and installed mdbtools-0.5 but it segfaults when I try to run mdb-schema and mdb-export on the soils data. The -0.6pre1 won't build because the backend.c file is declared both static and dynamic. I don't

Re: [sqlite] Output in currency format

On Tue, 10 Nov 2009, Peter Haworth wrote: > Is there a way to do this or should I plan on handling it within the > application? Pete, The latter. Display formatting is not part of SQL. You might also consider using integer values for money because the math is more accurate. Rich

Re: [sqlite] Order of operations when using TRIGGERs

On Thu, 22 Oct 2009, Simon Slavin wrote: > I'm using a TRIGGER that is triggered AFTER INSERT. The INSERT commands > do not know or set the value for the ROWID column. Can I reliably fetch > the value for this column from 'NEW.' ? Or can I rely only on values > which are explicitly set in the I

Re: [sqlite] .import on a .csv file

On Thu, 22 Oct 2009, Simon Davies wrote: > One solution is to replace your existing separators (,) with a character > that does not exist in your data, specify that character as the separator > to sqlite, and you should be good to go .import. I found the solution was to change all field separ

Re: [sqlite] Problem about write data into the DB

On Wed, 21 Oct 2009, ?? wrote: > I deployed a django app on my laptop, the whole environment is like this: > the OS is UBUNTU904, the web server is Apache, and the database is > sqlite3. The deployment is success, but when I try to write some data into > the database, I get the HTTP 500 error. An

Re: [sqlite] how to represent a tree in SQL

On Wed, 14 Oct 2009, Michael Chen wrote: > I am developing a numerical application, where a single rooted dynamic > tree is the main data structure. I intended to use SQLite for this purpose > and also for other data as well. However I have no reference how to > represent a tree using tables. I ne

Re: [sqlite] Need Help SQL

On Sun, 11 Oct 2009, Igor Tandetnik wrote: > I simply combine (month, day) pair into a single number - the same way you > combine two digits of a decimal number by multiplying the first by 10 and > adding the second. The multiplier doesn't have to be 100 - any number > greater than 31 will do. Ig

Re: [sqlite] DELETE FROM Not Working -- FIXED

On Thu, 8 Oct 2009, Rich Shepard wrote: > Oh, rats! I messed up the table when converting the dates. Guess I need > to start the process over. Will report results when I'm done. Amazing! When the dates are correct, and not all the same, the delete statement works as intended o

Re: [sqlite] DELETE FROM Not Working

On Thu, 8 Oct 2009, Igor Tandetnik wrote: > Well, do you actually have rows that you believe should satisfy the > condition? Show one of those. Oh, rats! I messed up the table when converting the dates. Guess I need to start the process over. Will report results when I'm done. Rich __

Re: [sqlite] DELETE FROM Not Working

On Thu, 8 Oct 2009, Igor Tandetnik wrote: > Show what the data looks like now, show the statement you are running, and > define "not working". Igor, Here are two records: sqlite> select * from Penalties limit 2; 2009-071|Water Quality, Storm Water|NWR|205 Auto Salvage, Inc.|Portland|Belete|s

Re: [sqlite] DELETE FROM Not Working

On Thu, 8 Oct 2009, Pavel Ivanov wrote: > Your dates are compared as simple strings. Thus with your statement you're > trying to delete all rows where DateIssued is January, 1 of any year > earlier than 2005. Bottom line: change the way you store your dates if you > really want to compare them in

Re: [sqlite] DELETE FROM Not Working

On Thu, 8 Oct 2009, Igor Tandetnik wrote: > Actually, columns with these declared types will have NUMERIC affinity. Thanks, Igor. I missed that. > Realize that -MM-DD format works not because SQLite treats it somehow > specially, but because for strings in this format, alphabetical order

Re: [sqlite] DELETE FROM Not Working

On Thu, 8 Oct 2009, Pavel Ivanov wrote: > And the main problem: SQLite doesn't have such type as date. All types > it supports are listed here: http://www.sqlite.org/datatype3.html. Pavel, We can use DATE, TIME, and DATETIME column types; they all have TEXT storage class. > Bottom line: chan

[sqlite] DELETE FROM Not Working

This must be my error, but I am not seeing it. Your input is requested. I have a table named Penalties with a column named DateIssued and a datatype of DATE. A select operation shows dates such as 4/6/1992 and 12/15/1993. To delete all rows with dates earlier than 1/1/2005 I used the sta

Re: [sqlite] Comparing two tables?

On Wed, 30 Sep 2009, Joe Bennett wrote: > Have two tables that have the same columns, but different number of rows > (rows do not match but columns do. 86 matching columns in each table): That's to be expected; why would the rows match? > Now, I'm looking to do this: > > Find the first row of

Re: [sqlite] TH3 license

On Fri, 25 Sep 2009, Clifford Hung wrote: > How do I obtain a TH3 license? Perhaps go to the local DMV office and wait in line for several hours? I've no idea what a TH3 license is, and Google didn't help any. If you want to use SQLite for some purpose read

Re: [sqlite] Tedious CSV import question

On Thu, 24 Sep 2009, C. Mundi wrote: > I just bit the bullet and did it. Python has an excellent csv module, > capable of handling just about any dialect you're likely to encounter. I > am so grateful I did not have to write a parser for CSV. In just a few > lines I can read the csv right into

Re: [sqlite] Strange File Import Error

On Wed, 23 Sep 2009, Simon Davies wrote: > You seem to have translated one of your data commas into a separator pipe: > "Stormwater; NPDES Construction More Than 1 Acre Disturbed Ground, Issued > By Agent" > -> > Stormwater; NPDES Construction More Than 1 Acre Disturbed Ground|Issued By > Agent

Re: [sqlite] Strange File Import Error

On Tue, 22 Sep 2009, Simon Davies wrote: > 25 separators -> 26 columns? That's what the command line processor is telling me. Makes no sense to me at all. I remove the first line, and get the same error for line 2 which is now line 1. Rich ___ sqlit

Re: [sqlite] Strange File Import Error

On Tue, 22 Sep 2009, Simon Davies wrote: >> 117172|Engineered Structures Inc.|Brockway Center|10875 SW Herman >> Rd|Tualatin|97062-8033|Washington|NWR|45.3834|-122.7882|1542|Nonresidential >> Construct NEC|Gen12c(Agent)|Stormwater; NPDES Construction More Than 1 Acre >> Disturbed Ground|Issued By

Re: [sqlite] Strange File Import Error

On Tue, 22 Sep 2009, Simon Davies wrote: > Remove the trailing pipe character Did that as soon as I learned it made no difference. Each line should be clean and there are 25 columns defined in it. Quite frustrating. Rich ___ sqlite-users mailing lis

Re: [sqlite] Strange File Import Error

On Tue, 22 Sep 2009, Simon Slavin wrote: > Use a text editor on the file and change all occurrences of (including the > quotes) > > "," > > to > > "|" > > then set .separator to the single character | before you import the > file. Simon, The default separator is '|' (as confirmed by the .show

Re: [sqlite] Strange File Import Error

On Tue, 22 Sep 2009, Pavel Ivanov wrote: > AFAIK, you can do only the first - remove all quotes and make sure that no > commas met in field values. Pavel, I've just tried this and the command line processor still finds 26 columns instead of the counted 25. Sigh. Thanks, Rich ___

Re: [sqlite] Strange File Import Error

On Tue, 22 Sep 2009, Pavel Ivanov wrote: > No, the problem is that sqlite3 command line utility doesn't parse quotes > in csv files. It gets line, splits it using comma as delimiter, > disregarding any quotes and then inserts resulting strings into database. > So you'll have to use something else

Re: [sqlite] Strange File Import Error

On Tue, 22 Sep 2009, Pavel Ivanov wrote: > Could you show us this line 1? Pavel, Sure. I think the issue is that the file uses double quotation marks rather than single ones. But, in that case, how do I mark an embedded apostrophe? Line 1: "117172","Engineered Structures, Inc.","Brockway Ce

[sqlite] Strange File Import Error

I've downloaded data in .csv format from an agency's Web site and want to put it in a sqlite database. I created the table as a .sql file and read that into sqlite. It's fine. However, when I try to import the csv file sqlite balks: sqlite> .import all-deq-contacts-fixed.csv Permits all-deq

Re: [sqlite] SQL language question

On Tue, 22 Sep 2009, D. Richard Hipp wrote: > The question is this: Should the no-op UPDATE statement (x=x) cause the > ON UPDATE SET NULL foreign key constraint to set t2.y to NULL or not? > > PostgreSQL says "no" - the t2.y value is not nulled unless the t1.x > value really does change values.

Re: [sqlite] Importing data into SQLite - text files are not really portable

On Tue, 8 Sep 2009, Ribeiro, Glauber wrote: > Unfortunately, the 3 main families of small computer operating systems > have 3 different definitions of what a text file is... > > This causes no end of trouble when moving text files between these kinds > of systems. I've never worked with a Mac

Re: [sqlite] Booleans in SQLite

On Mon, 7 Sep 2009, Jim Showalter wrote: > Oracle doesn't have a native boolean type. You have to use INTEGER and > interpret it. > > MySQL doesn't have a boolean type (it's just a synonym for TINYINT). > > SQL Server doesn't have a boolean type. You have to use BIT and > interpret it. Postgre

Re: [sqlite] Using SQLite for GIS Application

On Sun, 23 Aug 2009, Itzchak Raiskin wrote: > 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 along this line. I

[sqlite] SQLite-3.6.17 for Slackware-12.2

We had a thread not long ago about pre-built packages for SQLite. I just downloaded the 3.6.17 source, built it, and upgraded from 3.6.16. Works fine. So, if anyone wants a package for Slackware-12.2, ask and ye shall receive. Rich -- Richard B. Shepard, Ph.D. | Integrity

Re: [sqlite] A problem with versions of SQLite

On Mon, 10 Aug 2009, Rod Dav4is wrote: > Perhaps the version 3.6 db manager will automatically convert my 2.1 to > the new 3.6 format (or is that too much to hope for?). Knowing nothing about any version of Windows, I suggest that you make a copy of the exiting file and try opening it in the l

Re: [sqlite] sqlite database "signature" ?

On Thu, 6 Aug 2009, luc.moulinier wrote: > I'd like to know what is the best way to know if a file is a sqlite DB or > not (without launching sqlite of course) ? For example, is the first line > of the file unambiguously a signature of sqlite ? If so, what is its > structure ? Many thanks in advan

Re: [sqlite] Data synchronization between two offices

On Tue, 4 Aug 2009, Rstat wrote: > But today we sell more and more books and the addition of a database means > we need to synchronize our data from the warehouse database to the main > office one, mainly to be able to know what kind of stock we have. I think > we will have to go with a batch sync

Re: [sqlite] Date Comparisons SQL

On Mon, 3 Aug 2009, Rick Ratchford wrote: > It was set as String actually. Rick, That's the storage class; well, TEXT is the storage class. > I believe this is a WRAPPER thing though. I'm programming in VB6 and using > Olaf's VB wrapper. Oh. I know nothing about Microsoft languages (or o

Re: [sqlite] Date Comparisons SQL

On Mon, 3 Aug 2009, Rick Ratchford wrote: > The native Date in a table without any additional expressions is > '-mm-dd 00:00:00'. Rick, That's a timestamp format. Did you specify the column as date or timestamp? Rich -- Richard B. Shepard, Ph.D. | IntegrityCr

[sqlite] [OpenSQL Camp] OpenSQL Camp, November 2009 in Portland, OR (fwd)

For those who are interested. Rich -- Richard B. Shepard, Ph.D. | IntegrityCredibility Applied Ecosystem Services, Inc.|Innovation Voice: 503-667-4517 Fax: 503-667-8863 -- Forwarded message -

Re: [sqlite] Subtotal SQL

On Wed, 29 Jul 2009, Adler, Eliedaat wrote: > SQL/sqlite challenge for all: No challenge for anyone who knows SQL. > I need a running sum of size that works regardless of what order the objects > are in. > User Function/Aggregates welcome! _All_ implementations of SQL include a suite of

Re: [sqlite] Insert multiple entries in a single INSERT statement

On Tue, 28 Jul 2009, Shaun Seckman (Firaxis) wrote: > Looking at the SQL syntax chart it doesn't seem like this is possible. > In other SQL servers I'm able to use the statement "insert into > foo('col1', col2') values ('1', '1'), ('2', '2'), ('3', '3');". Is this > possible in SQLite or must

Re: [sqlite] DATETIME and storage type

On Mon, 27 Jul 2009, Rael Bauer wrote: > If I declare a field as DATETIME default "2001-01-01", ( e.g. alter table > "notes" add column "last_modified" DATETIME default "2001-01-01";) will > the declared default value be stored as a string or real value? Rael, What you have above is data type

Re: [sqlite] DATETIME and storage type

On Mon, 27 Jul 2009, Rael Bauer wrote: > If I declare a field as DATETIME default "2001-01-01", ( e.g. alter table > "notes" add column "last_modified" DATETIME default "2001-01-01";) will > the declared default value be stored as a string or real value? Rael, String (the actual data storage

Re: [sqlite] Denormalisation

On Mon, 27 Jul 2009, CityDev wrote: > Over the intervening years I can't ever remember denormalising data (even > when dealing with eg 13 million insurance customers in a table). Is it OK > nowadays to say always aim to be fully normalised - modern RDBMSs are > usually powerful enough to cope with

  1   2   3   >