[sqlite] How do I output multi-word strings without braces?
Given the following code fragment...
set xname [db eval { select name from elements where e_mtid = $element }]
puts [format "Requested element ==> %s ==> %s" $element $xname]
The "business rules" are such that I know I'll only get one row
returned. I get output like so...
Requested element ==> abcdef ==> {FOO BAR}
What I need is...
Requested element ==> abcdef ==> FOO BAR
What do I need to do to get rid of the braces around the output name?
And no, that's not how the data looked in the tab-delimited file it was
imported from.
--
Walter Dnes
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How do I output multi-word strings without braces?
On Tue, Nov 10, 2009 at 03:06:08AM -0500, Walter Dnes wrote
> Given the following code fragment...
>
> set xname [db eval { select name from elements where e_mtid = $element }]
> puts [format "Requested element ==> %s ==> %s" $element $xname]
Oops, I forgot to mention that this is the TCL interface to SQLite.
This is one of those "grey area" questions that could go to either TCL
or SQLite forums, because it's an interaction between the two of them.
I assume that some people here have dealt with this issue before.
--
Walter Dnes
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Problems passing parameters between SQLite + TCL
There's a lot more to this program, but I've cut it down to the bare
minimum that illustrates my problem. I call a TCL script from the linux
commandline, and get an error message like so...
[waltdnes][~/SQLite] ./fragment 49.25 123 25
can't read "lat1": no such variable
while executing
"expr $lat1 / $radian "
(procedure "sql_distance" line 3)
invoked from within
"sql_distance 49.25 123 48.914 -123.7"
invoked from within
"db eval { select e_stnid, i_stnid, deci_lat, deci_long, elevation,
distance($lat_degrees, $long_degrees, deci_lat, deci_long) as dist
..."
(file "./fragment" line 21)
Here is the cut-down program...
#!/usr/bin/tclsh
set lat_degrees [expr [lindex $argv 0]]
set long_degrees [expr [lindex $argv 1]]
set radius [expr [lindex $argv 2]]
load /usr/lib/sqlite-3.6.17/libtclsqlite3.so
sqlite3 db :memory:
# Note: GIS convention has longitude negative in the western hemisphere.
# But end-users will get annoyed at having to enter the minus sign all the
# time. So the conversion is done internally in the distance() function.
proc sql_distance {lat1, long1, lat2, long2} {
set radian [expr 180 / 3.1415926]
set lat1 [expr $lat1 / $radian ]
set long1 [expr $long1 / $radian * (-1) ]
set lat2 [expr $lat2 / $radian ]
set long2 [expr $long2 / $radian ]
return [expr {
acos(sin($lat1) * sin($lat2) + cos($lat1) * cos($lat2) * cos($long2 -
$long1)) * 6371}]}
db function distance sql_distance
db eval {attach 'climate.sqlite' as cl}
db eval { select e_stnid, i_stnid, deci_lat, deci_long, elevation,
distance($lat_degrees, $long_degrees, deci_lat, deci_long) as dist
from cl.stations
where dist <= $radius} {puts stdout "$e_stnid, $i_stnid, $deci_lat,
$deci_long, $elevation, $dist"}
db close
I'm trying to do a select on all sites within a given radius of a
given point. I'm using the "spherical cosine law" to calculate distance.
The "sql_distance 49.25 123 48.914 -123.7" error message
shows that I successfully passed the first 2 command line parameters and
the last 2 came from an entry in table cl.stations.
Given that all 4 parameters hace been passed tothe distance()
function, why are they undefined in the proc?
--
Walter Dnes
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] any keyword
On Wed, Nov 11, 2009 at 09:37:31AM +0100, Andrea Galeazzi wrote > Probably sqlite doesn't support 'any' keyword as I write it in the > following query: > SELECT G.id,name FROM Genre G > WHERE G.id = ANY (SELECT S.genre_id FROM Song S) > ORDER BY name ASC; Maybe I'm mis-understanding your query. Can you use a subquery... SELECT G.id,name FROM Genre G WHERE G.id IN ( SELECT genre_id FROM Song ) ORDER BY name ASC; -- Walter Dnes ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [SOLVED] Problems passing parameters between SQLite + TCL
On Wed, Nov 11, 2009 at 02:41:50AM -0500, Walter Dnes wrote
> proc sql_distance {lat1, long1, lat2, long2} {
It seems that "the TCL way" to pass multiple parameters is...
proc sql_distance {lat1 long1 lat2 long2} {
i.e. as a list without any commas.
--
Walter Dnes
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Disk activity on Linux
On Thu, Nov 12, 2009 at 11:24:58AM -0600, Bret Patterson wrote > We're seeing a lot more disk activity than expected on Linux when > using sqlite3. We've run this same series of test on windows and > the disk IO is much lower, which is the opposite of what I really > expected. Below is my scenario and perhaps someone can point out > what I can do to fix this problem. Since it seems to be a linux issue, what are the mount settings for the partitions? This may seem obvious, so please don't take this as an insult. Here goes... - "noatime" (for all file systems) will reduce disk I/O and speed things up - if using ReiserFS, use the "notail" option - the default is that all filesystems are mounted async, but check to make sure Then there are the hard drives. Use the hdparm utility to confirm that DMA is turned on. I assume the linux machine is a server, running in text mode, rather than a resource-heavy GUI. -- Walter Dnes ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Problem passing SQLite field values to TCL function
I start off passing a central latitude ($lat_degrees) and longitude
($long_degrees) and a radius ($radius) to a TCL script. Using the
spherical cosine law to calculate distance, I want to select all sites
in a table within that given radius. Here are 2 code fragments from the
script...
===
sqlite3 db :memory:
# Note: GIS convention has longitude negative in the western hemisphere.
# But end-users will get annoyed at having to enter the minus sign all the
# time. So the conversion is done internally in the distance() function.
proc sql_distance { lat1 long1 lat2 long2 } {
set radian [expr 180 / 3.1415926]
set lat1 [expr $lat1 / $radian ]
set long1 [expr $long1 / $radian * (-1) ]
set lat2 [expr $lat2 / $radian ]
set long2 [expr $long2 / $radian ]
return [expr {
acos(sin($lat1) * sin($lat2) + cos($lat1) * cos($lat2) * cos($long2 -
$long1)) * 6371}]}
db function distance sql_distance
...
db eval { create table temp2 as
select e_stnid, i_stnid, deci_lat, deci_long, elevation, stn_name,
distance( $lat_degrees, $long_degrees, deci_lat, deci_long) as dist
from cl.stations
where dist <= $radius}
===
$lat_degrees and $long_degrees are defined
deci_lat and deci_long are valid field names (type real) in table
stations in the attached database (alias "cl"). I get an error message
which tells me that deci_lat and deci_long are not defined. I've run a
separate test to confirm that every row has non-null numbers in deci_lat
and deci_long. So that's not the problem. Here's the error message...
missing operand at _...@_
in expression " _...@_/ 57.295780490442965"
(parsing expression " / 57.295780490442965")
invoked from within
"expr $lat2 / $radian "
(procedure "sql_distance" line 5)
invoked from within
"sql_distance 49.25 123 {} {}"
invoked from within
"db eval { create table temp2 as
select e_stnid, i_stnid, deci_lat, deci_long, elevation,
stn_name,
distance( $lat_degrees, $long..."
invoked from within
"if { $argc < 4 } {
puts "Error: This query requires at least 4 parameters, namely"
puts "Central Latitude, Central Longitude, Radius, and at least..."
(file "./tcltest.004" line 2
--
Walter Dnes
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Suggested user-defined-function example
Whilst trying to get a TCL script to create a function in SQLite I ran
into problems and did a lot of Googling. I got very tired of seeing the
same old same old...
proc sql_sqrt {x} {return [expr {sqrt($x)}]}
db function sqrt sql_sqrt
It didn't help me because it used only one parameter. It didn't say
anything about you
- *MUST NOT* have commas between parameters in the function definition
- *MUST* have commas between parameters when actually calling it
I spent several hours figuring this out. Here's a working example...
package require sqlite3
sqlite3 db :memory:
db eval {create table dual(x varchar(1))}
db eval {insert into dual values(' ')}
proc sql_addnum { a b } { return [expr { $a + $b }] }
db function addnum sql_addnum
db eval {select 'Hello world' as x from dual} {puts stdout "$x"}
db eval {select 999 as y from dual} {puts stdout "$y"}
db eval {select addnum(1, 2) as z from dual} {puts stdout "$z"}
db close
And the output is...
Hello world
999
3
Use this code as an example, and it may save someone else some time
down the road.
--
Walter Dnes
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite won't import 53,066,244 row 11 gigabyte CSV file
I'm running SQLite 3.6.18 on Gentoo linux. The filesystem is Reiserfs, so 11 gigs should not be a problem. Here's a screen scrape... === [d531][waltdnes][~/SQLite] ll dly04.csv -rw-r--r-- 1 waltdnes users 11143911240 Nov 27 15:34 dly04.csv [d531][waltdnes][~/SQLite] sqlite3 dlyxx.sqlite SQLite version 3.6.18 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .mode csv dly04 sqlite> .import dly04.csv dly04 cannot open file: dly04.csv sqlite> === Am I making some glaring noob error here? I've successfully imported, and tested out smaller files using the same method. The table dly04 has already been created, and it's just a matter of importing from the CSV file. Is there a known maximum line-count or file-size? If necessary, I can run "split -l dly04.csv", and import the separate (smaller) pieces. Am I correct in assuming that multiple ".import" statements will append to an existing table, rather than overwriting? (Yes, I am new to SQLite). -- Walter Dnes ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [solved] SQLite won't import 53, 066, 244 row 11 gigabyte CSV file
Thanks for the help. I eventually got it working. And I'm working on the next stage of my project. On Fri, Nov 27, 2009 at 06:31:03PM -0800, Roger Binns wrote > Alternatively use split to make the input a series of files each > less than 4GB in size. Or use a 64 bit host. [...] > If using a file smaller than 4GB fixes the issue for you then please > let me know and I'll add a ticket about large file support too. First, I tried... [d531][waltdnes][~/SQLite] split -l 1800 dly04.csv [d531][waltdnes][~/SQLite] ll xa* -rw-r--r-- 1 waltdnes users 378000 Nov 27 19:49 xaa -rw-r--r-- 1 waltdnes users 378000 Nov 27 19:52 xab -rw-r--r-- 1 waltdnes users 3583911240 Nov 27 19:54 xac ...and import failed with the same error. Then I tried... [d531][waltdnes][~/SQLite] split -l 900 dly04.csv [d531][waltdnes][~/SQLite] ll xa* -rw-r--r-- 1 waltdnes users 189000 Nov 27 20:06 xaa -rw-r--r-- 1 waltdnes users 189000 Nov 27 20:07 xab -rw-r--r-- 1 waltdnes users 189000 Nov 27 20:09 xac -rw-r--r-- 1 waltdnes users 189000 Nov 27 20:10 xad -rw-r--r-- 1 waltdnes users 189000 Nov 27 20:12 xae -rw-r--r-- 1 waltdnes users 1693911240 Nov 27 20:13 xaf ...and import worked. So it appears that the maximum csv file is probably around 2 gigabytes. > You can recompile the shell setting the flags (-D or #define): > _LARGE_FILE, _FILE_OFFSET_BITS=64 and _LARGEFILE_SOURCE I'm not a C programmer. And I'd have to repeat the changes each time there's an update in the Gentoo linux ebuild. Even something minor like 3.6.17 ==> 3.6.18. I've got 2 choices... 1) Pass your comments on to the Gentoo bugzilla, asking the Gentoo SQLite ebuild maintainer to put in a patch with your suggested change. 2) If the patching is going to be done upstream (i.e. by the SQLite people themselves) soon, then don't do anything. The Gentoo version will automatically inherit the large file support with the new version. -- Walter Dnes ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Not matching numbers in where clause
As part of "pandemic planning" at work, I'm trying to duplicate some Oracle functionality at home. I'm running into a problem with the "WHERE" clause that totally baffles me. The problem surfaced in a TCL program, but I can duplicate it from the sqlite3 command prompt, so I'll use that in this email. Here's the situation. I've created a table called dly04. Here are the first few columns from the create statement. Note the 2 columns "national_identifier varchar2(7), local_year integer". sqlite> select * from sqlite_master where name = 'dly04'; table|dly04|dly04|1042384|CREATE TABLE dly04(i_stnid integer, national_identifier varchar2(7), local_year integer, local_month integer, local_day integer, etc, etc I then imported data from work as tab-delimited. I can query with "where" using a character field... sqlite> select local_year, local_month, local_day from dly04 where (national_identifier = '615HMAK'); The last few rows of the query output are... 2009| 9| 21 2009| 9| 22 2009| 9| 23 2009| 9| 24 2009| 9| 25 2009| 9| 26 2009| 9| 27 2009| 9| 28 2009| 9| 29 2009| 9| 30 So I *KNOW* that there are rows with local_year = 2009. But... sqlite> select local_year, local_month, local_day from dly04 where (local_year = 2009); ...doesn't find any rows at all. This should be a no-brainer that works in any SQL-compliant implementation. Neither do I get any rows with alternatives like... = '2009' or == 2009 or == '2009' I'm totally stumped. I wonder if I'm doing something "the Oracle way" that isn't 100% SQL-compliant. One thing I've done is to create an index like so... index|d04_ndx_00|dly04|14555880|CREATE INDEX d04_ndx_00 on dly04 (national_identifier, local_year, local_month) Is that allowed, or would it screw things up? -- Walter Dnes ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How do I properly import numbers from CSV?
On Sun, Dec 06, 2009 at 08:15:46PM -0600, Jay A. Kreibich wrote > On Sun, Dec 06, 2009 at 07:54:45PM -0500, Walter Dnes scratched on the wall: > > > The last few rows of the query output are... > > > > 2009| 9| 21 > > 2009| 9| 22 > > 2009| 9| 23 > > 2009| 9| 24 > > 2009| 9| 25 > > 2009| 9| 26 > > 2009| 9| 27 > > 2009| 9| 28 > > 2009| 9| 29 > > 2009| 9| 30 > > > > So I *KNOW* that there are rows with local_year = 2009. But... > > No, there are rows with a TEXT value of ' 2009'. Note the space. > Your other columns have leading whitespace as well. sqlite> select local_year, local_day, local_month from dly.dly04 where ((national_identifier = '615HMAK') and (local_year = ' 2009') and (local_month = ' 9') and (local_day = ' 30')); ...gives the result... 2009| 30| 9 So you're right. One correction to my previous post; I imported as comma-delimited, i.e CSV. I still don't understand why 2009, *WITHOUT QUOTES* would be forced to text, i.e. ' 2009', when imported into a field that is declared as integer in the create statement. -- Walter Dnes ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [SOLVED] How do I properly import numbers from CSV?
On Sun, Dec 06, 2009 at 11:26:42PM -0500, Walter Dnes wrote > I still don't understand why 2009, *WITHOUT QUOTES* would be forced > to text, i.e. ' 2009', when imported into a field that is declared > as integer in the create statement. There are actually 2 solutions... 1) Use tab-delimited if possible (not really CSV) 2) Get rid of leading/trailing spaces if you're using comma-separated input. E.g. this row results in text fields being imported... 2231,615HMAK, 2005, 3, 28, 8.0, , -1.0, , 3.5 ...while this one results in mostly numeric fields being imported... 2231,615HMAK,2005,3,28,8.0, ,-1.0, ,3.5 As an added bonus, the script that got rid of unnecessary spaces also knocked down the 11,143,911,240 byte CSV file to "only" 5,382,671,854 bytes. This allowed me to import the file in 3 sub-2-gigabyte pieces versus 6 pieces that the original required. -- Walter Dnes ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.21
On Tue, Dec 08, 2009 at 02:41:23AM +0100, Andreas Schwab wrote > It's still crashing due to undefined behaviour. > > $ ./sqlite3 :memory: 'create table test(integer)' > Segmentation fault Possibly a stupid question from a relative newbie; shouldn't that be... sqlite3 :memory: 'create table test(fieldname integer)' -- Walter Dnes ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Import feature requests
The following might be options (compile time, config file, set manually; I don't care), but they should be available... 1) import with strict typing. If I create a table with 3 numeric (real. integer, whatever) fields, then a CSV file containing... 2.345, 42, 27.7 should import as 3 numbers, not as 3 character strings 2) import adjacent commas in a CSV file as NULL, not as a zero-length string. As for the argument that SQLite can't read my mind... a) if it can't read my mind, I need to be able to tell it what I want b) why would I want a "zero-length string" to behave any differently from NULL? c) why on earth would I want a "zero-length string" in an *INTEGER* or *REAL* field??? That is a totally insane default. -- Walter Dnes ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Import feature requests
On Sun, Dec 13, 2009 at 10:31:20PM -0800, Roger Binns wrote > Hint: If you feel the need to get aggressive and abusive when > posting then you probably missed something! If SQLite was useless, > someone else would have noticed by now. I apologize for coming across that way; I din't intend to. Let's just say I'm rather frustrated with some defaults. If I felt SQLite was useless I wouldn't be using it. I read about the "loose typing" during import, in the docs. It was only via "the hard way" that I found out just *HOW* loose. -- Walter Dnes ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
