date:20091111

Re: [sqlite] Converting .dbf to SQLite

2009-11-11 Thread dave lilley

2009/11/12 Rich Shepard 

> 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.
>

No I mean you use a programming language to read the DBF datafile and write
out to your new database.

And as someone else has suggested you use OOo spreadsheet to connect to the
DBF file and then write it out to a CVS file so you can import into your new
DB.

if your not confidant with programming then i strongly suggest you take this
option as you then only have to import the CVS data into SQLlite.

Rich
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug candidate: virtual tables vs. external db connections

2009-11-11 Thread Dan Kennedy


On Nov 12, 2009, at 11:31 AM, Roger Binns wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Grzegorz Wierzchowski wrote:
>> That was my first suspicion that there is some memmove with cursor  
>> object or so.
>> This would mean that sqlite* or maybe other sensitive pointers can  
>> not be
>> members of cursor object, what is wrong for me.
>
> There is an issue with mutexes and with using freed memory.
>
> My best guess as to what is going on is that you are freeing the db in
> xClose and allocating in xFilter but that they end up mismatched in  
> some
> way.  You can see what has happened using valgrind.
>
> I compiled the testfixture like this:
>
> make -f Makefile.linux-gcc TOP=`pwd` BCC="gcc -g" TCC="gcc -g"
> THREADLIB=-lpthread  READLINE_FLAGS=-DHAVE_READLINE LIBREADLINE=- 
> lreadline
> TCL_FLAGS=-I/usr/include/tcl8.4  LIBTCL=-ltcl testfixture
>
> valgrind then shows in detail what has happened.  If you add
> OPTS=-DSQLITE_DEBUG then you also get all SQLite assertions turned  
> on and a
> mutex isn't held when it should be fires.

I don't think you can use sqlite3_result_value() with a value
that comes from a different database connection. At least not
currently. The xColumn() method of the patched echo-vtab does
that.

Dan.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug candidate: virtual tables vs. external db connections

2009-11-11 Thread Roger Binns

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Grzegorz Wierzchowski wrote:
> That was my first suspicion that there is some memmove with cursor object or 
> so.
> This would mean that sqlite* or maybe other sensitive pointers can not be 
> members of cursor object, what is wrong for me.

There is an issue with mutexes and with using freed memory.

My best guess as to what is going on is that you are freeing the db in
xClose and allocating in xFilter but that they end up mismatched in some
way.  You can see what has happened using valgrind.

I compiled the testfixture like this:

make -f Makefile.linux-gcc TOP=`pwd` BCC="gcc -g" TCC="gcc -g"
THREADLIB=-lpthread  READLINE_FLAGS=-DHAVE_READLINE LIBREADLINE=-lreadline
TCL_FLAGS=-I/usr/include/tcl8.4  LIBTCL=-ltcl testfixture

valgrind then shows in detail what has happened.  If you add
OPTS=-DSQLITE_DEBUG then you also get all SQLite assertions turned on and a
mutex isn't held when it should be fires.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkr7j38ACgkQmOOfHg372QSyGQCeLJs7ZUDu/sbiidgYdOGSqkmY
O9YAoLnuAkLKnixcFJ4ml4gISJWbWcA7
=M9Of
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Converting .dbf to SQLite

2009-11-11 Thread Rich Shepard

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 version cannot.

Rich
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Converting .dbf to SQLite

2009-11-11 Thread Rich Shepard

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.

Rich
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Converting .dbf to SQLite

2009-11-11 Thread Reid Thompson

Reid Thompson wrote:
> Jean-Christophe Deschamps wrote:
>>>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.
> 
> perhaps
> http://developer.berlios.de/projects/dbf/
>
or one of these
http://sourceforge.net/search/?type_of_search=soft=dbf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Converting .dbf to SQLite

2009-11-11 Thread Reid Thompson

Jean-Christophe Deschamps wrote:
> 
>>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.

perhaps
http://developer.berlios.de/projects/dbf/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Converting .dbf to SQLite

2009-11-11 Thread Jean-Christophe Deschamps



>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 had prices on them, but nothing like the
>mdbtools or mdb-sqlite.

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.




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Converting .dbf to SQLite

2009-11-11 Thread dave lilley

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?

note the sql database & tables would be already created.

in this message you don't really give an indication on whether you are able
to do this or not so what i've said maybe of no use to you.

dave.

2009/11/12 Rich Shepard 

>   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 had prices on them, but nothing like the
> mdbtools or mdb-sqlite.
>
> TIA,
>
> Rich
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Converting .dbf to SQLite

2009-11-11 Thread Rich Shepard

   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 had prices on them, but nothing like the
mdbtools or mdb-sqlite.

TIA,

Rich
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reverse Referencing Rows

2009-11-11 Thread Rick Ratchford

This is really not as difficult as you might think.

If the database contains 10 years of price data, where trading only happens
Monday-Friday excluding Holidays, and you were able to extract DATE RANGES
as SETS from this data, it would not be difficult to determine if the SET is
complete or not.

For example, if the DATE RANGE is NOV 05 to FEB 05, you would know you had
the complete set of data if you had data leading UP TO NOV 05 and also data
following FEB 05.

So for example, if the first DATA SET based on the NOV 05 to FEB 05
requirement actually started on NOV 07 because no trading actually occurred
on NOV 05 and 06, you'd know this was the case and not that your data simply
started in the middle of the SET itself if you had data leading up to NOV 07
to begin with, say NOV 02, 03, 04.

And in any case, this issue really is only at the BEGINNING and END of the
data where you may get PARTIAL SETS. If the data started somewhere between
the DATE RANGE, and the data ended also somewhere in the middle, it would
not be difficult to determine this. One other very important fact exists,
and that is that stock data, excluding weekends, never stops trading for 3
straight days. So if you are missing more than say 5 days in front of the
first DATA SET, as that is where the data actually starts, you'd know it was
missing a complete DATA SET for your first SET from this data.

Keep in mind that the whole exercise is to extract ONLY the data that falls
between two dates selected by the user from all the data available. Each SET
(from start date to end date) will eventually be numbered as SET 1, 2, 3,


Then one can compare all odd sets, even sets, all sets, first 5 sets, last
10 sets, etc. Apples to apples. The 'year' isn't the reference, because if
you went from NOV 05 to FEB 05, you'd be going from one year into the next.
The data must be in sets from NOV 05 to FEB 05, not the other way around, if
that is what the user selects.

Hope this makes it clearer.

Cheers!
 
Rick
 

#>-Original Message-
#>From: sqlite-users-boun...@sqlite.org 
#>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
#>Sent: Wednesday, November 11, 2009 4:53 PM
#>To: General Discussion of SQLite Database
#>Subject: Re: [sqlite] Reverse Referencing Rows
#>
#>
#>On 11 Nov 2009, at 7:12pm, Rick Ratchford wrote:
#>
#>> To determine if the set is complete, there would clearly be 
#>data rows 
#>> PRIOR to the start date and data rows that FOLLOW the end 
#>date. This 
#>> is how I'd determine that a set is complete with all available data 
#>> for those 'sample date windows'.
#>
#>This makes no sense to me.  To determine if I have data for 
#>each workday within a period I need a definition of which 
#>days within the period are workdays.  Either a table of all 
#>workdays, or a list of all non-workdays, or some other way of 
#>determination which is in a form SQL can access.  In the 
#>financial systems I used to work with you'd usually find a 
#>TABLE which listed each day and it's workday number.
#>
#>So if the daynumber of today last year was, say, 88,000 the 
#>daynumber of today might be 88,250.  To determine if I had 
#>data for every day in the last year I'd subtract 88,000 from 
#>88,250 and then check to see whether I had data for 250 
#>different days within the period.
#>
#>Simon.
#>___
#>sqlite-users mailing list
#>sqlite-users@sqlite.org
#>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
#>
#>


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reverse Referencing Rows

2009-11-11 Thread Simon Slavin

On 11 Nov 2009, at 7:12pm, Rick Ratchford wrote:

> To determine if the set is complete, there would clearly be data rows PRIOR
> to the start date and data rows that FOLLOW the end date. This is how I'd
> determine that a set is complete with all available data for those 'sample
> date windows'.

This makes no sense to me.  To determine if I have data for each workday within 
a period I need a definition of which days within the period are workdays.  
Either a table of all workdays, or a list of all non-workdays, or some other 
way of determination which is in a form SQL can access.  In the financial 
systems I used to work with you'd usually find a TABLE which listed each day 
and it's workday number.

So if the daynumber of today last year was, say, 88,000 the daynumber of today 
might be 88,250.  To determine if I had data for every day in the last year I'd 
subtract 88,000 from 88,250 and then check to see whether I had data for 250 
different days within the period.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database is locked

2009-11-11 Thread Frank Chang


  Jay Kreibich, Thank for your reply about sqlite3_busy_timeout. We 
found that the sqlite 'database is locked' error message can be fixed by 
updating two different tables in the two SQL Server 2005 client processes. 

 

UPDATE mdMatchUp SET

MatchKey = master.dbo.mdMUIncrementalBuildKeyEx(@Incremental,   Matchcode, 
Zip, Last, First, Address, NULL, NULL, NULL, NULL, NULL, NULL),

@RetVal = master.dbo.mdMUIncrementalAddRecord(@Incremental),

Status = master.dbo.mdMUIncrementalGetStatusCode(@Incremental),

DupeGroup = master.dbo.mdMUIncrementalGetDupeGroup(@Incremental)

 

Perhaps, the problem we were experiencing with the sqlite 'database is locked' 
error message is related to SQL Server 2005 locks.

 The SQL Server 2005 extended stored procedure 
master.dbo.mdMUIncrementalAddRecord(@Incremental) is a wrapper around the C/C++ 
code: 

   



sprintf(Command,"INSERT INTO [Keys] ([Key], [Cluster], "

   "[DupeGroup]) VALUES (\"%s\", \"%*.*s\", %d)",

MCKey,BlockSize,BlockSize,MCKey,DupeGroup);

 



 while (Keys->Execute(Command)==SQLITE_BUSY) {

#if defined(__unix)

   sleep(dRETRYDELAY);

#else

   Sleep(dRETRYDELAY*1000);

 #endif

 }

 

 Thank you.
  
_
Hotmail: Trusted email with powerful SPAM protection.
http://clk.atdmt.com/GBL/go/177141665/direct/01/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug candidate: virtual tables vs. external db connections

2009-11-11 Thread Grzegorz Wierzchowski

Wednesday 11 of November 2009 19:03:09 Roger Binns napisał(a):
> Can you do this a unified diff please?
>
> In any case it looks like you are trying to manipulate the database pointer
> while a query is running.
>
> Roger

That was my first suspicion that there is some memmove with cursor object or so.
This would mean that sqlite* or maybe other sensitive pointers can not be 
members of cursor object, what is wrong for me.
In order to return correct results of query on my virtual table I must collect 
data from external databases, and I know their file names only in xFilter() 
function  (they are derived from query constaraints).
That is why it is covenient and natural to store connection objects inside 
cursor.

Thanks
GW

Here is requested unified diff:

==
--- sqlite-3.6.20-ORIGINAL/src/test8.c 2009-10-27 19:07:06.0 +0100
+++ sqlite-3.6.20-BUG/src/test8.c2009-11-09 19:20:19.0 +0100
@@ -90,6 +90,7 @@
 struct echo_cursor {
   sqlite3_vtab_cursor base;
   sqlite3_stmt *pStmt;
+  sqlite3  *pDb;/* Database connection */
 };

 static int simulateVtabError(echo_vtab *p, const char *zMethod){
@@ -568,6 +569,9 @@
   echo_cursor *pCur = (echo_cursor *)cur;
   sqlite3_stmt *pStmt = pCur->pStmt;
   pCur->pStmt = 0;
+  if (pCur->pDb)
+sqlite3_close(pCur->pDb);
+  pCur->pDb = 0;
   sqlite3_free(pCur);
   rc = sqlite3_finalize(pStmt);
   return rc;
@@ -670,7 +674,10 @@

   echo_cursor *pCur = (echo_cursor *)pVtabCursor;
   echo_vtab *pVtab = (echo_vtab *)pVtabCursor->pVtab;
-  sqlite3 *db = pVtab->db;
+  if (pCur->pDb)
+sqlite3_close(pCur->pDb);
+  sqlite3_open("/tmp/test.db", >pDb);
+  sqlite3 *db = pCur->pDb;

   if( simulateVtabError(pVtab, "xFilter") ){
 return SQLITE_ERROR;

=
--- sqlite-3.6.20--ORIGINAL/test/vtab1.test  2009-10-30 14:34:59.0 +0100
+++  sqlite-3.6.20--BUG/test/vtab1.test  2009-11-03 21:22:16.0 +0100
@@ -330,6 +330,11 @@
 # Test that a SELECT on t1 doesn't crash. No rows are returned
 # because the underlying real table is currently empty.
 #
+file delete -force "/tmp/test.db"
+file delete -force "/tmp/test.db-journal"
+sqlite3 dbTest "/tmp/test.db"
+dbTest eval {CREATE TABLE IF NOT EXISTS treal(a INTEGER, b INTEGER, c)}
+dbTest eval {CREATE INDEX IF NOT EXISTS treal_idx ON treal(b)}
 do_test vtab1-3.2 {
   execsql {
 SELECT a, b, c FROM t1;
@@ -339,6 +344,9 @@
 # Put some data into the table treal. Then try a few simple SELECT
 # statements on t1.
 #
+dbTest eval {INSERT INTO treal VALUES(1, 2, 3)}
+dbTest eval {INSERT INTO treal VALUES(4, 5, 6)}
+dbTest close
 do_test vtab1-3.3 {
   execsql {
 INSERT INTO treal VALUES(1, 2, 3);
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reverse Referencing Rows

2009-11-11 Thread Rick Ratchford

Stock market data.

Stock market only trades on Business days and not weekends and holidays.

The database contains stock data. One row per trading day.

What I want to do is to get all the data from a start date to end date only
into individual sets.

To determine if the set is complete, there would clearly be data rows PRIOR
to the start date and data rows that FOLLOW the end date. This is how I'd
determine that a set is complete with all available data for those 'sample
date windows'.

I want them in SETS for comparison purposes, to later compare one SET to
another without any regard to what year (or dual years) the sets are derived
from.

In my code I intend to number each set from 1 to whatever number of total
sets there are.

That's why I'm trying to finalize the SQL statement to grab the SETS. From
there, I can do the rest with code.

I'm thinking that due to the limitations of SQL that it may be better that I
use what I have in SQL, shown below (with your help) with the addition of
ORDER BY Date..., and then strip off the partial set at either end using
code.

'This SQL statement will pull out the data in SETS. Ex: lngStartMth/Day
to lngEndMth/Day, even cross year divide.
sSQL = "SELECT Date, Year, Month, Day, Open, High, Low, Close FROM [" &
gsTableName & "] " & _
"WHERE ((Month - " & lngStartMth & ")*100 + (Day - " &
lngStartDay & ") + 1300) % 1300 <= ((" & _
lngEndMth - lngStartMth & ") * 100 + (" & lngEndDay -
lngStartDay & ") + 1300) % 1300 " & _
"ORDER BY Date, Year, ((Month - " & lngStartMth & ")*100 + (Day
- " & lngStartDay & ") + 1300) % 1300"

Thanks.

Rick
 
 

#>-Original Message-
#>From: sqlite-users-boun...@sqlite.org 
#>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
#>Sent: Wednesday, November 11, 2009 12:38 PM
#>To: sqlite-users@sqlite.org
#>Subject: Re: [sqlite] Reverse Referencing Rows
#>
#>Rick Ratchford 
#>wrote: 
#>> Only business days are in the data sets. No weekends or Holidays.
#>> 
#>> So I was happy with the other sets because they ARE full sets.
#>> 
#>> A full set is ALL THE BUSINESS DAYS from the Start to End Date.
#>
#>Do you have a table that lists all the business days in a 
#>year, or something? Otherwise, I don't see how you can 
#>formally define the notion of a "complete set". And without 
#>formally defining it, you won't be able to produce a query to 
#>implement it.
#>
#>Igor Tandetnik
#>
#>
#>___
#>sqlite-users mailing list
#>sqlite-users@sqlite.org
#>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
#>
#>


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reverse Referencing Rows

2009-11-11 Thread Igor Tandetnik

Rick Ratchford 
wrote: 
> Only business days are in the data sets. No weekends or Holidays.
> 
> So I was happy with the other sets because they ARE full sets.
> 
> A full set is ALL THE BUSINESS DAYS from the Start to End Date.

Do you have a table that lists all the business days in a year, or something? 
Otherwise, I don't see how you can formally define the notion of a "complete 
set". And without formally defining it, you won't be able to produce a query to 
implement it.

Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reverse Referencing Rows

2009-11-11 Thread Rick Ratchford

Only business days are in the data sets. No weekends or Holidays.

So I was happy with the other sets because they ARE full sets. 

A full set is ALL THE BUSINESS DAYS from the Start to End Date.

Cheers!
 
Rick
 

#>-Original Message-
#>From: sqlite-users-boun...@sqlite.org 
#>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
#>Sent: Wednesday, November 11, 2009 11:44 AM
#>To: sqlite-users@sqlite.org
#>Subject: Re: [sqlite] Reverse Referencing Rows
#>
#>Rick Ratchford 
#>wrote: 
#>> #>>
#>> #>> Date  |  Year  |  Month  |  Day
#>> #>>
#>> #>> 12/28/1988   1988   12   28
#>> #>> 12/29/1988   1988   12   29
#>> #>> 12/30/1988   1988   12   30
#>> #>> 01/04/1988   1988   01   04
#>> #>> 01/05/1988   1988   01   05
#>> #>> 12/28/1989   1989   12   28
#>> #>> 12/29/1989   1989   12   29
#>> #>> 01/03/1989   1989   01   03
#>> #>> 01/04/1989   1989   01   04
#>> #>> 01/05/1989   1989   01   05
#>> #>>
#>> #>> As you can see, the first set has a problem It goes from 
#>> #>December 28, #>> 1988 to January 05, 1988, rather than 
#>January 05, 
#>> 1989 #>like it should #>> for the first SET.
#>> #>
#>> #>Actually, it only seems this way due to the sorting 
#>order. If #>you 
#>> just do "ORDER BY Year, Month, Day" you'll see what's 
#>#>going on. You 
#>> have one set going from 12/28/87 to 01/05/88 #>(which just 
#>happens to 
#>> be incomplete as you have no records #>in 1987), and 
#>another unrelated 
#>> set going from 12/28/88 to #>01/05/89. Your overcomplicated 
#>ORDER BY 
#>> clause causes these #>two sets to interleave.
#>> 
#>> This would then bring up another issue. Only COMPLETE SETS 
#>are needed, 
#>> not partial ones.
#>
#>Define "complete set". You seem to be happy with 1988-1989 
#>one, even though it's missing 3 days out of 9. Sounds pretty 
#>partial to me.
#>
#>> #>> I fugure the way to correct this issue is to make sure 
#>that each 
#>> ROW #>> (record) has a DATE that is greater than the last ROW.
#>> #>
#>> #>So, just say that in ORDER BY.
#>> 
#>> Is that what I did when I added "Date" to my ORDER BY?
#>
#>Which part of "ORDER BY Year, Month, Day" do you find unclear?
#>
#>Igor Tandetnik
#>
#>___
#>sqlite-users mailing list
#>sqlite-users@sqlite.org
#>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
#>
#>


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug candidate: virtual tables vs. external db connections

2009-11-11 Thread Roger Binns

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Grzegorz Wierzchowski wrote:
> $ diff sqlite-3.6.20-BUG/src/test8.c sqlite-3.6.20-ORIGINAL/src/test8.c
> 93d92
> <   sqlite3  *pDb;/* Database connection */
> 572,574d570
> <   if (pCur->pDb)
> < sqlite3_close(pCur->pDb);
> <   pCur->pDb = 0;
> 677,680c673
> <   if (pCur->pDb)
> < sqlite3_close(pCur->pDb);
> <   sqlite3_open("/tmp/test.db", >pDb);
> <   sqlite3 *db = pCur->pDb;
> ---
>>   sqlite3 *db = pVtab->db;

Can you do this a unified diff please?

In any case it looks like you are trying to manipulate the database pointer
while a query is running.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkr6/FgACgkQmOOfHg372QTsdwCgrVdDIB0KiFer1ruIghNJQfQ1
7bMAoN07SuCICFRHZcIMhETY9TlTREyE
=Kf1w
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Converting .mdb Files

2009-11-11 Thread Rich Shepard

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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] optimization question

2009-11-11 Thread Tim Romano

Thank you, Igor. Processing time: 5 seconds.  :-)


Igor Tandetnik wrote:
> Tim Romano  wrote:
>   
>> I've read http://www.sqlite.org/optoverview.html but don't find my
>> answer there.
>>
>> In the following query, WOIDS has 4 million rows and CORNFIX has
>> 25,000 rows.
>>
>> UPDATEWOIDS
>> SET  corn = 1
>> WHERE EXISTS
>> (
>> SELECT *
>>  FROM  CORNFIX
>>  WHERE  (cornfix.col_1 = woids.ttl) AND (cornfix.col_2 =
>> woids.pos) AND (cornfix.col_3 = woids.wrdid)
>> )
>> 
>
> Try this instead:
>
> update WOIDS set corn=1 where rowid in
> (select w2.rowid
>  from cornfix join woids w2 on (
> cornfix.col_1 = w2.ttl AND cornfix.col_2 = w2.pos AND cornfix.col_3 = 
> w2.wrdid)
> );
>
> I'm not sure, but this structure might help SQLite choose cornfix for the 
> outer loop.
>
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com 
> Version: 8.5.425 / Virus Database: 270.14.60/2496 - Release Date: 11/11/09 
> 07:40:00
>
>   

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reverse Referencing Rows

2009-11-11 Thread Igor Tandetnik

Rick Ratchford 
wrote: 
> #>>
> #>> Date  |  Year  |  Month  |  Day
> #>>
> #>> 12/28/1988   1988   12   28
> #>> 12/29/1988   1988   12   29
> #>> 12/30/1988   1988   12   30
> #>> 01/04/1988   1988   01   04
> #>> 01/05/1988   1988   01   05
> #>> 12/28/1989   1989   12   28
> #>> 12/29/1989   1989   12   29
> #>> 01/03/1989   1989   01   03
> #>> 01/04/1989   1989   01   04
> #>> 01/05/1989   1989   01   05
> #>>
> #>> As you can see, the first set has a problem It goes from
> #>December 28,
> #>> 1988 to January 05, 1988, rather than January 05, 1989
> #>like it should
> #>> for the first SET.
> #>
> #>Actually, it only seems this way due to the sorting order. If
> #>you just do "ORDER BY Year, Month, Day" you'll see what's
> #>going on. You have one set going from 12/28/87 to 01/05/88
> #>(which just happens to be incomplete as you have no records
> #>in 1987), and another unrelated set going from 12/28/88 to
> #>01/05/89. Your overcomplicated ORDER BY clause causes these
> #>two sets to interleave.
> 
> This would then bring up another issue. Only COMPLETE SETS are
> needed, not partial ones.

Define "complete set". You seem to be happy with 1988-1989 one, even though 
it's missing 3 days out of 9. Sounds pretty partial to me.

> #>> I fugure the way to correct this issue is to make sure that each
> ROW #>> (record) has a DATE that is greater than the last ROW.
> #>
> #>So, just say that in ORDER BY.
> 
> Is that what I did when I added "Date" to my ORDER BY?

Which part of "ORDER BY Year, Month, Day" do you find unclear?

Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Output in currency format

2009-11-11 Thread Simon Slavin

On 11 Nov 2009, at 4:51pm, Fred Williams wrote:

> The best (safest?) way I have found to handle non scientific math is to 
> work strictly with integers and multiply and divide using ROUND/TRUNC as 
> required to gain the precision required.  This includes way more than 
> SQLite situations as well.

Another aspect of the problem is where to convert from integers to currency: 
inside SQL, in library routines, or in each application.  At the moment my 
preference is that all numbers inside SQL are integers.  No part of the SQL 
system knows anything but integer English currency, integer Euro currency, 
integer US currency, etc..  Conversion between integers and currency format is 
always done in surrounding software.  But there are arguments for doing it in 
other ways.

Simon.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reverse Referencing Rows

2009-11-11 Thread Rick Ratchford


#>> 
#>> Date  |  Year  |  Month  |  Day
#>> 
#>> 12/28/1988   1988   12   28
#>> 12/29/1988   1988   12   29
#>> 12/30/1988   1988   12   30
#>> 01/04/1988   1988   01   04
#>> 01/05/1988   1988   01   05
#>> 12/28/1989   1989   12   28
#>> 12/29/1989   1989   12   29
#>> 01/03/1989   1989   01   03
#>> 01/04/1989   1989   01   04
#>> 01/05/1989   1989   01   05
#>> 
#>> As you can see, the first set has a problem It goes from 
#>December 28, 
#>> 1988 to January 05, 1988, rather than January 05, 1989  
#>like it should 
#>> for the first SET.
#>
#>Actually, it only seems this way due to the sorting order. If 
#>you just do "ORDER BY Year, Month, Day" you'll see what's 
#>going on. You have one set going from 12/28/87 to 01/05/88 
#>(which just happens to be incomplete as you have no records 
#>in 1987), and another unrelated set going from 12/28/88 to 
#>01/05/89. Your overcomplicated ORDER BY clause causes these 
#>two sets to interleave.

This would then bring up another issue. Only COMPLETE SETS are needed, not
partial ones. In another post, I stated adding "Date" to my ORDER BY so that
at least the ORDER would be chronological. However, it still leaves the
issue of the partial unwanted SET.

>
#>> I fugure the way to correct this issue is to make sure that each ROW
#>> (record) has a DATE that is greater than the last ROW.
#>
#>So, just say that in ORDER BY.

Is that what I did when I added "Date" to my ORDER BY? I'm assuming it is.
But correct me if not.

#>
#>> Is it possible to have the SQL statement above do this as well?
#>
#>Yes.

But then, lies the mystery. :-)

Thanks.
Rick


#>
#>Igor Tandetnik
#>
#>
#>___
#>sqlite-users mailing list
#>sqlite-users@sqlite.org
#>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
#>
#>


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Converting .mdb Files

2009-11-11 Thread Jan

Hi,

I am using this one:
http://code.google.com/p/mdb-sqlite/

Have not tried it on linux though.

Jan

Rich Shepard schrieb:
>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 know
> that this tool is maintained any longer.
> 
>Has anyone here needed to convert from M$ Access to SQLite? If so, how
> have you done this on a linux system?
> 
> TIA,
> 
> Rich
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] etiquette question: PNG attachments (28K, 11K) of EXPLAIN results permitted?

2009-11-11 Thread Keith Roberts

On Wed, 11 Nov 2009, Tim Romano wrote:

> To: General Discussion of SQLite Database 
> From: Tim Romano 
> Subject: [sqlite] etiquette question: PNG attachments (28K,
> 11K) of EXPLAIN results permitted?
> 
> My update query has been running for 45 minutes, and I'm not sure how to
> stop it. I think it's selecting a row from a table with 25,000 rows 4
> million times rather than selecting a row from a table with 4 million
> rows 25,000 times.
>
> To prevent this in the future, I need to learn more about how to
> interpret the results of EXPLAIN.  Is it permissible to attach two small
> screen-captures of the EXPLAIN command output? 28K and 11K.  Or are
> attachments forbidden?  I don't know  how to capture the results as
> plain text.
>
> Thanks

I doubt that you are allowed to post attachments here to 
the list.

However you can upload your images here:

===
http://www.freeimagehosting.net/

Filename extensions allowed: gif, jpg, bmp, png
Filesize limit: 3,000KB

Need to upload a file larger than 3MB?
  Use Free File Hosting
===

and then post the URL's to the images here.

Kind Regards,

Keith Roberts

-
Websites:
http://www.php-debuggers.net
http://www.karsites.net
http://www.raised-from-the-dead.org.uk

All email addresses are challenge-response protected with
TMDA [http://tmda.net]
-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] optimization question

2009-11-11 Thread Kristoffer Danielsson


I solved my "inner/outer" problems by compiling Sqlite with 
SQLITE_ENABLE_STAT2=1.

That flag makes it better at choosing the inner table!
 
> To: sqlite-users@sqlite.org
> From: itandet...@mvps.org
> Date: Wed, 11 Nov 2009 12:03:06 -0500
> Subject: Re: [sqlite] optimization question
> 
> Tim Romano  wrote:
> > I've read http://www.sqlite.org/optoverview.html but don't find my
> > answer there.
> > 
> > In the following query, WOIDS has 4 million rows and CORNFIX has
> > 25,000 rows.
> > 
> > UPDATE WOIDS
> > SET corn = 1
> > WHERE EXISTS
> > (
> > SELECT *
> > FROM CORNFIX
> > WHERE (cornfix.col_1 = woids.ttl) AND (cornfix.col_2 =
> > woids.pos) AND (cornfix.col_3 = woids.wrdid)
> > )
> 
> Try this instead:
> 
> update WOIDS set corn=1 where rowid in
> (select w2.rowid
> from cornfix join woids w2 on (
> cornfix.col_1 = w2.ttl AND cornfix.col_2 = w2.pos AND cornfix.col_3 = 
> w2.wrdid)
> );
> 
> I'm not sure, but this structure might help SQLite choose cornfix for the 
> outer loop.
> 
> Igor Tandetnik
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
  
_
Hitta hetaste singlarna på MSN Dejting!
http://dejting.se.msn.com/channel/index.aspx?trackingid=1002952
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need help constructing a query

2009-11-11 Thread Igor Tandetnik

Jörgen Hägglund
 wrote: 
> Hi all!
> I have three tables, one containing IP addresses as integers, lets
> call 
> it 'base'.
> A second table containing IP ranges and the country code the range
> belongs to. Lets call this 'IPtoCC' with the fields IPFrom and IPTo as
> integers and CC as text.
> Then a third, 'Exclude', containing country codes i want to be
> excluded with a single field CC as text.
> What I need is to delete all records in 'base' where base.IP falls
> into 
> a range of IPtoCC.IPFrom to IPtoCC.IPTo and that IPtoCC.CC is in
> Exclude.CC. Is it possible to do this in a single DELETE?

delete from base where exists
(select 1 from IPtoCC join Exclude on (IPtoCC.CC = Exclude.CC)
 where base.IP between IPFrom and IPTo);

Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Output in currency format

2009-11-11 Thread Fred Williams

The best (safest?) way I have found to handle non scientific math is to 
work strictly with integers and multiply and divide using ROUND/TRUNC as 
required to gain the precision required.  This includes way more than 
SQLite situations as well.  Borland (Code Gear) seem to be the only 
developer tools producer to recognize the need for "business" math with 
their native BCD data type.

Fred
> Seems like I should handle the formatting in my application.  Not sure  
> I agree that sqlite is not the place to do output formatting - it  
> provides lots of date and time formatting features so at least in that  
> area, output formatting is available.
>
> Thanks also for the info re accuracy/REAL formatting.  I will change  
> my db design accordingly.
>
> Thanks,
>
> Pete Haworth
>
>
>
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] etiquette question: PNG attachments (28K, 11K) of EXPLAIN results permitted?

2009-11-11 Thread Tim Romano

My update query has been running for 45 minutes, and I'm not sure how to 
stop it. I think it's selecting a row from a table with 25,000 rows 4 
million times rather than selecting a row from a table with 4 million 
rows 25,000 times.

To prevent this in the future, I need to learn more about how to 
interpret the results of EXPLAIN.  Is it permissible to attach two small 
screen-captures of the EXPLAIN command output? 28K and 11K.  Or are 
attachments forbidden?  I don't know  how to capture the results as 
plain text.

Thanks
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 for Mac OSX 10.5

2009-11-11 Thread Jay A. Kreibich

On Wed, Nov 11, 2009 at 08:04:23AM -0800, Peter Haworth scratched on the wall:
> The group_concat function works fine in the Firefox SQLite Manager  
> extension on the same Mac where it fails within sqlite3.  Also, the  
> development software I'm using (Revolution) also rejects the  
> group_concat function.
> 
> I would have thought that the sqlite code would have been in a central  
> library somewhere on my machine for any programs that make calls to  
> it, but based on the above, it seems that each program has it's own  
> private sqlite library of sqlite calls?

  Mac OS X includes a system-wide dynamic (shared) library in /usr/lib.
  That is also used by the CoreData framework and a few other Apple
  tools.  The included /usr/bin/sqlite3 command line tool dynamically
  links against the /usr/lib library as well.

  Most applications (such as Firefox) just compile the SQLite code
  directly into the application.  This is considered the preferred way
  use SQLite in a larger application where the user never directly
  interacts with the database.  Clearly that's not the case when using
  the Firefox SQLite Manager, but that's not exactly the normal way of
  using Firefox.

  Revolution may be using the system library, or it may just have an
  older, internal version.

  You can run the SQL query "SELECT sqlite_version()" to see what code
  a specific environment is using.

  As others have pointed out, it is extremely easy to build a new
  version of the SQLite libs and command line tool if you have the
  XCode tools installed.  The download is a bit big, but they're useful
  to have around, even if you're not writing a lot of code.

  The only gotcha is that you want to be sure to install things into
  /usr/local/bin and /usr/local/lib.  Since the /usr/lib stuff is used
  by some pretty core system OS stuff, I'd leave that alone and just
  install the newer copy elsewhere.  If you use Terminal for a lot of
  command line work, make sure your PATH is setup to use the correct
  binary/library.  No use installing a new one if you keep using the
  old one

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_analyzer with 3.6.19 distribution

2009-11-11 Thread D. Richard Hipp

On Nov 11, 2009, at 11:24 AM, Jens Miltner wrote:
>
> Is sqlite3_analyzer supposed to work in 3.6.19?
>

No.  sqlite3_analyzer has been busted for a long time.  But the 3.6.0  
version of sqlite3_analyzer works just fine, even on databases created  
using 3.6.19.

D. Richard Hipp
d...@hwaci.com

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Converting .mdb Files

2009-11-11 Thread Rich Shepard

   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 know
that this tool is maintained any longer.

   Has anyone here needed to convert from M$ Access to SQLite? If so, how
have you done this on a linux system?

TIA,

Rich
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_analyzer with 3.6.19 distribution

2009-11-11 Thread Jens Miltner

Hi,

I just tried to build the sqlite3_analyzer from the 3.6.19  
distribution on Mac OS X (using 'make sqlite3_analyzer'), but when I  
run the tool, I get the following error:

> Analyzing table agent_registry...
> ERROR: invalid command name "btree_cursor_info"
> invalid command name "btree_cursor_info"
> while executing
> "btree_cursor_info $csr $up"
> (procedure "cursor_info" line 3)
> invoked from within
> "cursor_info ci $csr"
> ("foreach" body line 38)
> invoked from within
> "foreach {name rootpage} [db eval $sql] {
>   puts stderr "Analyzing table $name..."
>
>   # Code below traverses the table being analyzed (table name  
> $name..."

(BTW: when running a debug build, some ALWAYS assertion fires).

Is sqlite3_analyzer supposed to work in 3.6.19?

Thanks,


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Output in currency format

2009-11-11 Thread P Kishor

On Wed, Nov 11, 2009 at 10:07 AM, Peter Haworth  wrote:
> Seems like I should handle the formatting in my application.  Not sure
> I agree that sqlite is not the place to do output formatting - it
> provides lots of date and time formatting features so at least in that
> area, output formatting is available.
>

A wee bit of error in time formatting and display usually will not
amount to a hill of beans, but make an error in the number of pennies
owed to someone and all hell will break loose.



> Thanks also for the info re accuracy/REAL formatting.  I will change
> my db design accordingly.
>
> Thanks,
>
> Pete Haworth
>
>
>
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, Wisconsin, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 for Mac OSX 10.5

2009-11-11 Thread P Kishor

On Wed, Nov 11, 2009 at 10:04 AM, Peter Haworth  wrote:
> Yes, the analyzer is the only precompiled binary I see on the sqlite
> download page
>
> The group_concat function works fine in the Firefox SQLite Manager
> extension on the same Mac where it fails within sqlite3.  Also, the
> development software I'm using (Revolution) also rejects the
> group_concat function.
>
> I would have thought that the sqlite code would have been in a central
> library somewhere on my machine for any programs that make calls to
> it, but based on the above, it seems that each program has it's own
> private sqlite library of sqlite calls?

That is the whole idea behind sqlite... you embed it in your program,
and your program gets its own private db server, client, magic.

Installing sqlite on Mac OS X is extremely easy, but you do have to
have the free Xcode/developer tools installed (on your OS CD, or
freely downloadable from Apple's website).

Download the source, untar/gzip it, then

sqlite-src > ./configure
sqlite-src > make
sqlite-src > sudo make install

that above commands will build sqlite and put it in the "central"
place that you desire, that is, under /usr/local/ and then you can use
them from anywhere. Of course, programs that have their own built in
sqlite will continue to function with those built in versions until
they are individually updated.

>
> Pete Haworth
>
>
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, Wisconsin, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Output in currency format

2009-11-11 Thread Peter Haworth

Seems like I should handle the formatting in my application.  Not sure  
I agree that sqlite is not the place to do output formatting - it  
provides lots of date and time formatting features so at least in that  
area, output formatting is available.

Thanks also for the info re accuracy/REAL formatting.  I will change  
my db design accordingly.

Thanks,

Pete Haworth








___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 for Mac OSX 10.5

2009-11-11 Thread Peter Haworth

Yes, the analyzer is the only precompiled binary I see on the sqlite  
download page

The group_concat function works fine in the Firefox SQLite Manager  
extension on the same Mac where it fails within sqlite3.  Also, the  
development software I'm using (Revolution) also rejects the  
group_concat function.

I would have thought that the sqlite code would have been in a central  
library somewhere on my machine for any programs that make calls to  
it, but based on the above, it seems that each program has it's own  
private sqlite library of sqlite calls?

Pete Haworth







___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] execute or prepare+step+finalize

2009-11-11 Thread Simon Davies

2009/11/11 T :
>
> i`ve done it, and i think i understand it :)
>
> thank you..
>
>
> few more question to be sure that i`m not missing something important..
>
> if i use prepared statements only (dont use exec function in program at all)
> i dont need callback function, do i?

No

>  somehow after i replaced exec (i.e. select_statement function) and i dont
> need select_statement function, neither callback function, evetything seems
> much more simple (which is why i`m bit suspicious if i`ve done everything
> correct)..
>
> are there any cases when it is better to use exec ?


perhaps simpler/quicker to code for sql that returns no data (INSERT etc)


>
>
> does using prepared statement slow down my program since prepare function is
> used every time when i want to execute some command ?

No - sqlite3_exec() is a wrapper around sqlite3_prepare()/sqlite3_step()

> here is how my
> function looks like now:
>
> bool create2 (char * command)
> {
>
> sqlite3_stmt * statement2;
>
> if ( sqlite3_prepare (db, command, -1, , 0) != SQLITE_OK )
>        {
>                int err = sqlite3_prepare (db, command, -1, , 0);

no need to call prepare a second time - assign to err in your if
statement, or use sqlite3_errcode()

>                const char * pErr = sqlite3_errmsg (db);
>                printf ("\nError %d occured! \n %s", err, pErr  );
>                return false;
>        }
>
> int iCol = sqlite3_column_count (statement2);
>
> int smth, i;
> //int rows=1;
> smth=sqlite3_step(statement2);
>
> while (smth == SQLITE_ROW)
> {
>        printf ("\n");
>        //printf ("\n Row %d:\t", rows);   // doesnt give right number of 
> column
> for other select statements except select all
>        for (i=0; i        {
>                const char *txt = (const char*)sqlite3_column_text(statement2, 
> i); // save
> it into dynamical multidimensional array
>                printf (" %s = %s \t", sqlite3_column_name (statement2,i), txt 
> );
>        }
>        printf ("\n");
>        //rows++;
>        smth=sqlite3_step (statement2);
>
> }
>
>        sqlite3_reset (statement2);

Use sqlite3_reset if you are going to bind new values to your prepared
statement and restep. If you are finalizing there is no need to reset.

>        sqlite3_finalize (statement2);
>
>        return true;
> }
>
>
> main:
>
>
> create2 ("CREATE TABLE two (ID INTEGER PRIMARY KEY ASC, a,b,c)");
> create2 ("INSERT INTO two (a,b) VALUES (3, 4)");
> create2 ("INSERT INTO two (a,b,c) VALUES (2, 8, 9)");
> create2 ("INSERT INTO two (a,c) VALUES (4, 1)");
> create2 ("INSERT INTO two (a,b,c) VALUES (1, 4, 9)");
> create2 ("INSERT INTO two (a,b,c) VALUES (1, 2, 8)");
> create2 ("SELECT * FROM two");
> create2 ("SELECT * FROM two WHERE b=4");
>
>
> if you have some advices or suggestions please let me know..
>

Regards,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] optimization question

2009-11-11 Thread Tim Romano

I've read http://www.sqlite.org/optoverview.html but don't find my 
answer there.

In the following query, WOIDS has 4 million rows and CORNFIX has 25,000 
rows.

UPDATEWOIDS
SET  corn = 1
WHERE EXISTS
 (
SELECT *
  FROM  CORNFIX
  WHERE  (cornfix.col_1 = woids.ttl) AND (cornfix.col_2 = woids.pos) 
AND (cornfix.col_3 = woids.wrdid)
)


Each table has a composite unique index:

 unique index WOIDS_IX on ttl, pos, wrdid
 unique index CORNFIX_IX on col_1, col_2, col_3

CORNFIX has only these three columns, no others.

The indexed columns are all INT.

Given these two composite unique indexes on INT columns, will SQLite 
automatically figure out that there's a huge difference in the number of 
rows in the two tables, and do an inner loop on CORNFIX table?   

Thanks
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need help constructing a query

2009-11-11 Thread Jay A. Kreibich

On Wed, Nov 11, 2009 at 09:36:41AM -0600, P Kishor scratched on the wall:
> 2009/11/11 Jörgen Hägglund :
> > Hi all!
> > I have three tables, one containing IP addresses as integers, lets call
> > it 'base'.
> > A second table containing IP ranges and the country code the range
> > belongs to. Lets call this 'IPtoCC' with the fields IPFrom and IPTo as
> > integers and CC as text.
> > Then a third, 'Exclude', containing country codes i want to be excluded
> > with a single field CC as text.
> > What I need is to delete all records in 'base' where base.IP falls into
> > a range of IPtoCC.IPFrom to IPtoCC.IPTo and that IPtoCC.CC is in Exclude.CC.
> > Is it possible to do this in a single DELETE?
> >
> 
> DELETE
> FROM base
> WHERE IP BETWEEN
>   (SELECT IPFrom FROM IPtoCC JOIN Exclude ON IPtoCC.CC = Exclude.CC) AND
>   (SELECT IPTo FROM IPtoCC JOIN Exclude ON IPtoCC.CC = Exclude.CC)

  I think you need to add WHERE clauses to the sub-selects so you pick
  the proper upper and lower bound for that base value.  Something like
  "...WHERE base.ip >= IPtoCC.IPFrom AND base.ip <= IPtoCC.IPTo".
  Otherwise each sub-select may return a whole column of values.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] execute or prepare+step+finalize

2009-11-11 Thread TTTTT


i`ve done it, and i think i understand it :)

thank you..


few more question to be sure that i`m not missing something important..

if i use prepared statements only (dont use exec function in program at all)
i dont need callback function, do i?
 somehow after i replaced exec (i.e. select_statement function) and i dont
need select_statement function, neither callback function, evetything seems
much more simple (which is why i`m bit suspicious if i`ve done everything
correct)..

are there any cases when it is better to use exec ?


does using prepared statement slow down my program since prepare function is
used every time when i want to execute some command ? here is how my
function looks like now:





bool create2 (char * command)
{

sqlite3_stmt * statement2;

if ( sqlite3_prepare (db, command, -1, , 0) != SQLITE_OK )
{
int err = sqlite3_prepare (db, command, -1, , 0);
const char * pErr = sqlite3_errmsg (db);
printf ("\nError %d occured! \n %s", err, pErr  );
return false;
}

int iCol = sqlite3_column_count (statement2);

int smth, i;
//int rows=1;
smth=sqlite3_step(statement2);

while (smth == SQLITE_ROW)
{
printf ("\n");
//printf ("\n Row %d:\t", rows);   // doesnt give right number of column
for other select statements except select all
for (i=0; i

Re: [sqlite] Need help constructing a query

2009-11-11 Thread P Kishor

2009/11/11 Jörgen Hägglund :
> Hi all!
> I have three tables, one containing IP addresses as integers, lets call
> it 'base'.
> A second table containing IP ranges and the country code the range
> belongs to. Lets call this 'IPtoCC' with the fields IPFrom and IPTo as
> integers and CC as text.
> Then a third, 'Exclude', containing country codes i want to be excluded
> with a single field CC as text.
> What I need is to delete all records in 'base' where base.IP falls into
> a range of IPtoCC.IPFrom to IPtoCC.IPTo and that IPtoCC.CC is in Exclude.CC.
> Is it possible to do this in a single DELETE?
>

DELETE
FROM base
WHERE IP BETWEEN
  (SELECT IPFrom FROM IPtoCC JOIN Exclude ON IPtoCC.CC = Exclude.CC) AND
  (SELECT IPTo FROM IPtoCC JOIN Exclude ON IPtoCC.CC = Exclude.CC)


Logically the above should work, but it requires two sub-selects.
Others will likely suggest a better way.


> Regards,
> /Jörgen
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, Wisconsin, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite in C# .NET

2009-11-11 Thread Robert Simpson

This has been asked and answered several times in the forums:

http://sqlite.phxsoftware.com/forums

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Matthew Pulis
Sent: Wednesday, November 11, 2009 6:40 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] SQLite in C# .NET

 Hi guys,

I am trying to do a small GIS for a mobile application. My application is
targeting Windows Mobile 6 Professional Edition. I am using CF 3.5  of .NET.

I have included the .dll from here:
http://freefr.dl.sourceforge.net/project/sqlite-dotnet2/SQLite%20for%20ADO.N
ET%202.0/1.0.65.0/SQLite-1.0.65.0-managedonly-binaries.zip
and
have included the System.Data.SQLite.dll and added the System.Data.SQLite
directive in my source code.

However when executing I am getting: Can't find PInvoke DLL
'SQLite.Interop.065.DLL'.

Any idea how to fix this please? Am I getting the right DLL and class?

Using this:

 using (SQLiteConnection connection = new SQLiteConnection
(connectionString))
{
connection.Open();

using (SQLiteCommand command =
connection.CreateCommand())
{
command.CommandText = @"SELECT load_extension
('libspatialite-1.dll');";
command.ExecuteScalar();

command.CommandText = "select name from
malta_speedcameras where oid=1";
lbCameraPosition.Text = (string)
command.ExecuteScalar();
}
}

Thanks :)

-- 
Matthew Pulis
URL : http://www.matthewpulis.info | http://www.solutions-lab.net
MSN : pulis_matth...@]hotmail.com
ICQ : 145951110
Skype : solutions-lab.net
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Need help constructing a query

2009-11-11 Thread Jörgen Hägglund

Hi all!
I have three tables, one containing IP addresses as integers, lets call 
it 'base'.
A second table containing IP ranges and the country code the range 
belongs to. Lets call this 'IPtoCC' with the fields IPFrom and IPTo as 
integers and CC as text.
Then a third, 'Exclude', containing country codes i want to be excluded 
with a single field CC as text.
What I need is to delete all records in 'base' where base.IP falls into 
a range of IPtoCC.IPFrom to IPtoCC.IPTo and that IPtoCC.CC is in Exclude.CC.
Is it possible to do this in a single DELETE?

Regards,
/Jörgen
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] When should you move to R*Tree?

2009-11-11 Thread Fredrik Karlsson

Dear list,

I am writing small program for large-ish databases involving multiple
(nested) comparisons of time references (REAL) against time intervals
(also REAL) stored as max and min points
(i.e. "is this time point within the intervals I have stored in table X?")

At what point, in terms of database size or otherwise, would it be
sensible (if at all) to move into using the R*Tree module for the time
reference comparisons?

I have tried generating som data to do comparisons, but I get lots of
warning messages about constrain violations, so I am not so sure that
the index is working correctly. Anyway, with 30 000 rows and my
possibly faulty index it seems that merging negates any positive
effects of R*Tree.

This is my setup:

CREATE VIRTUAL TABLE demo_index USING rtree(
   id,  -- Integer primary key
   minX, maxX,);

CREATE TABLE maintab (id INTEGER PRIMARY KEY AUTOINCREMENT, lab TEXT);
CREATE TABLE merged (id INTEGER PRIMARY KEY, lab TEXT, minX REAL, maxX REAL);

-- Data inserted into the database by a bunch of
insert into maintab values ( 1 ,'mmm'); insert into demo_index values
( 1 , (random()+0.0)/(random()/1000),
(random()+0.0)/(random()/1000));
.
(30 000 of them)

Typically
select * from maintab m, demo_index d where d.maxX >= 100.0 and d.minX
<= 100.0 and d.id = m.id;
runns in

> CPU Time: user 0.074044 sys 0.035214

and (where "merged" is a table with everything in it)

select * from merged where maxX >= 100.0 and minX <= 100.0;

in

> CPU Time: user 0.053880 sys 0.031010


Does this seem reasonable? Am I doing something stupid?

/Fredrik

-- 
"Life is like a trumpet - if you don't put anything into it, you don't
get anything out of it."
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 for Mac OSX 10.5

2009-11-11 Thread William Kyngesburye

I have a SQLite3 framework.  Universal binaries for Tiger thru Snow Leopard 
(though I'm not maintaining for Tiger any more).

http://www.kyngchaos.com/software:frameworks

On Nov 10, 2009, at 3:07 PM, Peter Haworth wrote:

> sqlite3 is rejecting a SELECT statement that includes the group_concat  
> function saying it's an unknown function, yet the same SELECT  
> statement works fine in the Firefox SQLite Manager extension.
> 
> The version of sqlite3 on my Mac is 3.4.0 but it looks like the latest  
> version is 3.6.x.  Could that be the cause of the problem and if so,  
> where can I get that version (already compiled) for the Mac?
> 
> Thanks,
> 
> Pete Haworth

-
William Kyngesburye 
http://www.kyngchaos.com/

Theory of the Universe

There is a theory which states that if ever anyone discovers exactly what the 
universe is for and why it is here, it will instantly disappear and be replaced 
by something even more bizarrely inexplicable.  There is another theory which 
states that this has already happened.

-Hitchhiker's Guide to the Galaxy 2nd season intro

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_free()

2009-11-11 Thread Simon Davies

2009/11/11 T :
>
> is this right way how to do it:
>
> sqlite_free (errmsg);
>
> ???
>
>
> or this:
>
>
> sqlite3_free (NULL);

>From http://www.sqlite.org/c3ref/free.html:
"The sqlite3_free() routine is a no-op if is called with a NULL pointer."

>
> or how should i do it?

Rgds,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_free()

2009-11-11 Thread TTTTT




is this right way how to do it:

sqlite_free (errmsg); 

???


or this:


sqlite3_free (NULL);

or how should i do it?
-- 
View this message in context: 
http://old.nabble.com/sqlite3_free%28%29-tp5188068p26302585.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_free()

2009-11-11 Thread TTTTT




jalburger wrote:
> 
> 
> 
> 
> By "the same" I mean the same sequence of bytes.  The error message
> from sqlite3_exec() is always obtained from a malloc-like memory
> allocator and must be freed using sqlite3_free().  The error message
> returned by sqlite3_errmsg() is always a constant, static string.
> The error messages might say the same thing, but they are distinct
> strings.
> --
> D. Richard Hipp   
> 
> 
> 


is this right way how to do it:

sqlite_free (errmsg); 

???

thank you
-- 
View this message in context: 
http://old.nabble.com/sqlite3_free%28%29-tp5188068p26302583.html
Sent from the SQLite mailing list archive at Nabble.com.

___
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

2009-11-11 Thread Walter Dnes

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] Understanding database lock

2009-11-11 Thread P Kishor

There are many problems with your code. See below.

On Wed, Nov 11, 2009 at 12:50 AM, Akash Rao  wrote:
..
> I have a perl script that add numbers 1-1000 into a db.
>
> Here is the code:
> =
> use DBI;
>
> my $number;
>
> for ($number=0;$number <= 1000;$number++)
>  {
> my $dbh = DBI->connect("dbi:SQLite:dbname=/opt/test.db","","",
>                      {RaiseError => 1, AutoCommit => 0});
>
>   $dbh -> do("INSERT INTO test1 VALUES('$number')");
>

You don't want to connect to the database on every iteration of the
loop. Kinda defeats the purpose of a db connection.

>   $dbh -> commit();
>   $dbh->disconnect();
>  };
> 
>
..

Use the following, more perlish, code --


use DBI;

# The db connection has been moved out of the loop, and is now
# created only once and reused.
my $dbh = DBI->connect("dbi:SQLite:dbname=/opt/test.db","","",
 {RaiseError => 1, AutoCommit => 0});

# Prepare a statement with bind vars and reuse it in the loop
my $sth = $dbh->prepare("INSERT INTO test1 VALUES (?)");

for my $number (0 .. 1000) {
 $sth->execute($number);
};

# Commit and disconnect outside the loop
$dbh -> commit();
$dbh->disconnect();


-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] execute or prepare+step+finalize

2009-11-11 Thread Simon Davies

2009/11/11 T :
>
.
.
.
>
>> and finaly i get SQLITE_DONE but it still doesnt show me table i have
>> selected...
>
> See http://www.sqlite.org/c3ref/column_blob.html
>
>
> i dont understand what exactly do you mean..
>
> combination of these:
>
> sqlite3_value *sqlite3_column_value(sqlite3_stmt*, int iCol);
> typedef struct Mem sqlite3_value;
> void sqlite3_result_value(sqlite3_context*, sqlite3_value*);

>From http://www.sqlite.org/c3ref/column_blob.html:
"These routines form the "result set query" interface."

If you want to see the data, you need to get it using the
sqlite3_column_xxx() routines after each call to sqlite3_step(). If
your column contains an integer, use sqlite3_column_int(), for real
values use sqlite3_column_double(), and so on.

>
> maybe?
>
>

Rgds,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite in C# .NET

2009-11-11 Thread Matthew Pulis

 Hi guys,

I am trying to do a small GIS for a mobile application. My application is
targeting Windows Mobile 6 Professional Edition. I am using CF 3.5  of .NET.


I have included the .dll from here:
http://freefr.dl.sourceforge.net/project/sqlite-dotnet2/SQLite%20for%20ADO.NET%202.0/1.0.65.0/SQLite-1.0.65.0-managedonly-binaries.zip
and
have included the System.Data.SQLite.dll and added the System.Data.SQLite
directive in my source code.

However when executing I am getting: Can't find PInvoke DLL
'SQLite.Interop.065.DLL'.


Any idea how to fix this please? Am I getting the right DLL and class?


Using this:

 using (SQLiteConnection connection = new SQLiteConnection
(connectionString))
{
connection.Open();

using (SQLiteCommand command =
connection.CreateCommand())
{
command.CommandText = @"SELECT load_extension
('libspatialite-1.dll');";
command.ExecuteScalar();

command.CommandText = "select name from
malta_speedcameras where oid=1";
lbCameraPosition.Text = (string)
command.ExecuteScalar();
}
}

Thanks :)

-- 
Matthew Pulis
URL : http://www.matthewpulis.info | http://www.solutions-lab.net
MSN : pulis_matth...@]hotmail.com
ICQ : 145951110
Skype : solutions-lab.net
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reverse Referencing Rows

2009-11-11 Thread Olaf Schmidt


"Rick Ratchford" 
schrieb im Newsbeitrag news:dbfb2606d0c1448b930064474496a...@dolphin...
> A while back, Igor gave me some help on pulling out mm/dd ranges (sets)
from
> my table.
>
> This is the code that does that.
>
> sSQL = "SELECT Date, Year, Month, Day, Open, High, Low, Close FROM ["
&
> gsTableName & "] " & _
> "WHERE ((Month - " & lngStartMth & ")*100 + (Day - " &
> lngStartDay & ") + 1300) % 1300 <= ((" & _
> lngEndMth - lngStartMth & ") * 100 + (" & lngEndDay -
> lngStartDay & ") + 1300) % 1300 " & _
> "ORDER BY Year, ((Month - " & lngStartMth & ")*100 + (Day - "
&
> lngStartDay & ") + 1300) % 1300"
>
> The only problem I have is that there are some stray rows that are not
> wanted.

Did you already tried the small function I've posted
into the VB-database-group (which was based on
Igors original suggestion)?

This also shows, how to work with a CommandObject -
(code is included again at the end of this post).

It enhances the *Where* clause (not the Order By),
to get rid of your "stray-rows" in the very first year
of a "year-crossing-set").

Function GetMonthDayRange(TableName As String, _
  ByVal MStart&, ByVal DStart&, _
  ByVal MEnd&, ByVal DEnd&, _
  ByVal InclYearSort As Boolean) As cRecordset
Dim SQL As String

  'a Cmd-SQL does *not* contain any direct "VarConcats", only @Placeholders
  SQL = "SELECT Date,Month,Day,Open,High,Low,Close FROM @TblName" & _
" WHERE (((mon...@mstart)*100 +  (d...@dstart)+ 1300)% 1300" & _
" <=(( @me...@mstart)*100 +(@de...@dstart)+ 1300)% 1300)" & _
" AND (Year > (Select Min(Year) From @TblName) " & _
" OR (Month*100+Day >= @mstart*1...@dstart)) "

  'enhance the above SQL about the appropriate, different Order By Clauses
  If InclYearSort Then
SQL = SQL & "ORDER BY Year, Month, Day"
  Else
SQL = SQL & "ORDER BY ((mon...@mstart)*100+(d...@dstart)+ 1300)% 1300"
  End If


  With Cnn.CreateSelectCommand(SQL) 'create a Select-Command-Object
'now we replace the @PlaceHolders with the current Var-Content,
'which we've got passed in our Function-Parameters
.ReplColumnOrTableName !TblName, TableName
.SetInt32 !MStart, MStart
.SetInt32 !DStart, DStart
.SetInt32 !MEnd, MEnd
.SetInt32 !DEnd, DEnd

Set GetMonthDayRange = .Execute 'the Cmd.Execute returns a Recordset
  End With
End Function

Olaf



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] execute or prepare+step+finalize

2009-11-11 Thread Marcus Grimm

you may also take a look at:

http://www.sqlite.org/cvstrac/wiki?p=SimpleCode

it explains how to use the step mechanism.

hth
Marcus

>
>
> why not:
> int smth = sqlite3_step (statement2);
> while( smth == SQLITE_ROW )
> {
> printf( "\n command= %s result code = %d \n", command, smth );
> smth = sqlite3_step (statement2);
> }
> so that it will work no matter how many rows
>
>
>
> because I dont have much experience :)
> that works great, thank you
>
>
>
>> and finaly i get SQLITE_DONE but it still doesnt show me table i have
>> selected...
>
> See http://www.sqlite.org/c3ref/column_blob.html
>
>
> i dont understand what exactly do you mean..
>
> combination of these:
>
> sqlite3_value *sqlite3_column_value(sqlite3_stmt*, int iCol);
> typedef struct Mem sqlite3_value;
> void sqlite3_result_value(sqlite3_context*, sqlite3_value*);
>
> maybe?
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> --
> View this message in context:
> http://old.nabble.com/execute-or-prepare%2Bstep%2Bfinalize-tp26299247p26300548.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bug candidate: virtual tables vs. external db connections

2009-11-11 Thread Grzegorz Wierzchowski

Sorry for long message, but this is required to explain my point.
Message is intended mainly to SQLite developers according new workflow for 
announcing bug candidates; regular users espacially those not using modules may 
skip it.

It looks like virtual tables' interface do not allow for doing certain things, 
even if they are legal and coded correctly.
E.g. opening other sqlite files within xFilter() or xOpen(), ... functions.
I cut down my case to the bare minimum and produced seg. fault by simple 
modifications in following sqlite files: 
src/test8.c, test/vtab1.test.
Change in test8.c is to make 'echo' module grab data from table in ANOTHER 
sqlite database instead of from THIS database (like in original 
implementation).
Change in vtab1.test is to accomodate test cases to change in test8.c.

$ diff sqlite-3.6.20-BUG/src/test8.c sqlite-3.6.20-ORIGINAL/src/test8.c
93d92
<   sqlite3  *pDb;/* Database connection */
572,574d570
<   if (pCur->pDb)
< sqlite3_close(pCur->pDb);
<   pCur->pDb = 0;
677,680c673
<   if (pCur->pDb)
< sqlite3_close(pCur->pDb);
<   sqlite3_open("/tmp/test.db", >pDb);
<   sqlite3 *db = pCur->pDb;
---
>   sqlite3 *db = pVtab->db;

$ diff sqlite-3.6.20-BUG/test vtab1.test sqlite-3.6.20-ORIGINAL/test/vtab1.test
333,337d332
< file delete -force "/tmp/test.db"
< file delete -force "/tmp/test.db-journal"
< sqlite3 dbTest "/tmp/test.db"
< dbTest eval {CREATE TABLE IF NOT EXISTS treal(a INTEGER, b INTEGER, c)}
< dbTest eval {CREATE INDEX IF NOT EXISTS treal_idx ON treal(b)}
347,349d341
< dbTest eval {INSERT INTO treal VALUES(1, 2, 3)}
< dbTest eval {INSERT INTO treal VALUES(4, 5, 6)}
< dbTest close

After applying those changes and recompiling, make test finish as follows (on 
Debian 5.0.3 / i386):
vtab1-4.3... Ok
vtab1-4.4... Ok
vtab1-4.3...*** glibc detected *** ./testfixture: free(): invalid pointer: 
0x08d7a090 ***
=== Backtrace: =
/lib/i686/cmov/libc.so.6[0x4019a624]
/lib/i686/cmov/libc.so.6(cfree+0x96)[0x4019c826]
./testfixture[0x8068da0]
./testfixture[0x8087fdb]
./testfixture[0x8091f32]
./testfixture[0x80b1591]
./testfixture[0x80b1e88]
./testfixture[0x8063fd0]
./testfixture[0x8066adf]
/usr/lib/libtcl8.4.so.0(TclEvalObjvInternal+0x326)[0x40057926]
/usr/lib/libtcl8.4.so.0(Tcl_EvalEx+0x43b)[0x4005861b]
/usr/lib/libtcl8.4.so.0(Tcl_EvalObjEx+0x78)[0x40058a68]
/usr/lib/libtcl8.4.so.0(Tcl_UplevelObjCmd+0xfe)[0x400b3eee]
/usr/lib/libtcl8.4.so.0(TclEvalObjvInternal+0x326)[0x40057926]
/usr/lib/libtcl8.4.so.0[0x400817cf]
/usr/lib/libtcl8.4.so.0(TclCompEvalObj+0x9f)[0x4007f90f]
/usr/lib/libtcl8.4.so.0(TclObjInterpProc+0x27d)[0x400b440d]
/usr/lib/libtcl8.4.so.0(TclEvalObjvInternal+0x326)[0x40057926]
/usr/lib/libtcl8.4.so.0[0x400817cf]
/usr/lib/libtcl8.4.so.0(TclCompEvalObj+0x9f)[0x4007f90f]
/usr/lib/libtcl8.4.so.0(TclObjInterpProc+0x27d)[0x400b440d]
/usr/lib/libtcl8.4.so.0(TclEvalObjvInternal+0x326)[0x40057926]
/usr/lib/libtcl8.4.so.0(Tcl_EvalEx+0x43b)[0x4005861b]
/usr/lib/libtcl8.4.so.0(Tcl_EvalObjEx+0x78)[0x40058a68]
/usr/lib/libtcl8.4.so.0(Tcl_UplevelObjCmd+0xfe)[0x400b3eee]
/usr/lib/libtcl8.4.so.0(TclEvalObjvInternal+0x326)[0x40057926]
/usr/lib/libtcl8.4.so.0[0x400817cf]
/usr/lib/libtcl8.4.so.0(TclCompEvalObj+0x9f)[0x4007f90f]
/usr/lib/libtcl8.4.so.0(TclObjInterpProc+0x27d)[0x400b440d]
/usr/lib/libtcl8.4.so.0(TclEvalObjvInternal+0x326)[0x40057926]
/usr/lib/libtcl8.4.so.0(Tcl_EvalEx+0x43b)[0x4005861b]
/usr/lib/libtcl8.4.so.0(Tcl_FSEvalFile+0x1e2)[0x4009d5c2]
/usr/lib/libtcl8.4.so.0(Tcl_SourceObjCmd+0x56)[0x40066746]
/usr/lib/libtcl8.4.so.0(TclEvalObjvInternal+0x326)[0x40057926]
/usr/lib/libtcl8.4.so.0[0x400817cf]
/usr/lib/libtcl8.4.so.0(TclCompEvalObj+0x9f)[0x4007f90f]
/usr/lib/libtcl8.4.so.0(Tcl_EvalObjEx+0xc4)[0x40058ab4]
/usr/lib/libtcl8.4.so.0(Tcl_ForeachObjCmd+0x559)[0x4005f9f9]
/usr/lib/libtcl8.4.so.0(TclEvalObjvInternal+0x326)[0x40057926]
/usr/lib/libtcl8.4.so.0(Tcl_EvalEx+0x43b)[0x4005861b]
/usr/lib/libtcl8.4.so.0(Tcl_FSEvalFile+0x1e2)[0x4009d5c2]
/usr/lib/libtcl8.4.so.0(Tcl_SourceObjCmd+0x56)[0x40066746]
/usr/lib/libtcl8.4.so.0(TclEvalObjvInternal+0x326)[0x40057926]
/usr/lib/libtcl8.4.so.0(Tcl_EvalEx+0x43b)[0x4005861b]
/usr/lib/libtcl8.4.so.0(Tcl_FSEvalFile+0x1e2)[0x4009d5c2]
/usr/lib/libtcl8.4.so.0(Tcl_EvalFile+0x41)[0x4009c191]
./testfixture[0x80630db]
/lib/i686/cmov/libc.so.6(__libc_start_main+0xe5)[0x40142455]
./testfixture[0x804a7c1]
=== Memory map: 
08048000-080d8000 r-xp  08:17 
18612471   
/home/grzegorz-deb/Pobrane/Source/Internet/sqlite.org/sqlite-3.6.20/testfixture
080d8000-080da000 rw-p 0008f000 08:17 
18612471   
/home/grzegorz-deb/Pobrane/Source/Internet/sqlite.org/sqlite-3.6.20/testfixture
080da000-080e1000 rw-p 080da000 00:00 0
08cd9000-08d81000 rw-p 08cd9000 00:00 0  [heap]
4000-4001a000 r-xp  08:12 7692962/lib/ld-2.7.so
4001a000-4001c000 rw-p 0001a000 08:12 7692962/lib/ld-2.7.so
4001c000-4001d000 r-xp 4001c000 00:00 0  [vdso]
4001d000-4001f000 rw-p 4001d000 00:00 0

Re: [sqlite] execute or prepare+step+finalize

2009-11-11 Thread TTTTT



why not:
int smth = sqlite3_step (statement2);
while( smth == SQLITE_ROW )
{
printf( "\n command= %s result code = %d \n", command, smth );
smth = sqlite3_step (statement2);
}
so that it will work no matter how many rows



because I dont have much experience :)
that works great, thank you



> and finaly i get SQLITE_DONE but it still doesnt show me table i have
> selected...

See http://www.sqlite.org/c3ref/column_blob.html


i dont understand what exactly do you mean..

combination of these:

sqlite3_value *sqlite3_column_value(sqlite3_stmt*, int iCol);
typedef struct Mem sqlite3_value;
void sqlite3_result_value(sqlite3_context*, sqlite3_value*);

maybe?




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



-- 
View this message in context: 
http://old.nabble.com/execute-or-prepare%2Bstep%2Bfinalize-tp26299247p26300548.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] execute or prepare+step+finalize

2009-11-11 Thread Simon Davies

2009/11/11 T :
>
> i think i understand why it doesnt work for select *..
>
> because sqlite_step executes one row at time.. so after i prepare SELECT * ,
> i need to use sqlite_step as many times as table i`m selecting from has rows
> (in this case 3 times)..
> so i made another function that looks like this:
>
>
>
> bool create2 (char * command)
>
> {
> sqlite3_stmt * statement2;
>
> if ( sqlite3_prepare (db, command, -1, , 0) != SQLITE_OK )
>        {
>                int err = sqlite3_prepare (db, command, -1, , 0);
>                const char * pErr = sqlite3_errmsg (db);
>                printf ("\nError %d occured! \n %s", err, pErr  );
>                return 1;
>        }
> int i;
> for (i=0; i<=3; i++)
> {
>        int smth= sqlite3_step (statement2);
>        printf ("\n command= %s result code = %d \n",command, smth);
> }

why not:
int smth = sqlite3_step (statement2);
while( smth == SQLITE_ROW )
{
printf( "\n command= %s result code = %d \n", command, smth );
smth = sqlite3_step (statement2);
}
so that it will work no matter how many rows

>        sqlite3_reset (statement2);
>        sqlite3_finalize (statement2);
>
>        return 0;
> }
>
>
>
> and finaly i get SQLITE_DONE but it still doesnt show me table i have
> selected...

See http://www.sqlite.org/c3ref/column_blob.html

>
>
> do i need to use prepare function for each command? if so, isnt then
> function select_statement better to use?

You are using prepare for each command...

>
>

Rgds,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] execute or prepare+step+finalize

2009-11-11 Thread TTTTT


i think i understand why it doesnt work for select *..

because sqlite_step executes one row at time.. so after i prepare SELECT * ,
i need to use sqlite_step as many times as table i`m selecting from has rows
(in this case 3 times)..
so i made another function that looks like this:



bool create2 (char * command)

{
sqlite3_stmt * statement2;

if ( sqlite3_prepare (db, command, -1, , 0) != SQLITE_OK )
{
int err = sqlite3_prepare (db, command, -1, , 0);
const char * pErr = sqlite3_errmsg (db);
printf ("\nError %d occured! \n %s", err, pErr  );
return 1;
}
int i;
for (i=0; i<=3; i++)
{
int smth= sqlite3_step (statement2);
printf ("\n command= %s result code = %d \n",command, smth);
}
sqlite3_reset (statement2);
sqlite3_finalize (statement2);

return 0;
}



and finaly i get SQLITE_DONE but it still doesnt show me table i have
selected...


do i need to use prepare function for each command? if so, isnt then
function select_statement better to use? 


-- 
View this message in context: 
http://old.nabble.com/execute-or-prepare%2Bstep%2Bfinalize-tp26299247p26299743.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Feature suggestion - default busy handler

2009-11-11 Thread O'Neill, Owen

Hi Everyone, 

On the basis of the number of times it comes up on the mailing list, 
and the grounds that most 'casual' users will want Sqlite to work as
well as possible 'out the box' -

I'd like to suggest the that the default busy handler is changed from
being none to being 
the 'standard' busy handler installed as a result of calling
sqlite3_busy_timeout 
-   with a timeout of say 30 seconds.

I think this would result in a smoother user experience, as well as a
quieter mailing list !

Thoughts / Disadvantages ???

Many thanks
Owen



 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 for Mac OSX 10.5

2009-11-11 Thread Dan Kennedy


On Nov 11, 2009, at 12:13 PM, Jay A. Kreibich wrote:

> On Wed, Nov 11, 2009 at 11:23:18AM +0700, Dan Kennedy scratched on  
> the wall:
>>
>> On Nov 11, 2009, at 4:07 AM, Peter Haworth wrote:
>>
>>> sqlite3 is rejecting a SELECT statement that includes the  
>>> group_concat
>>> function saying it's an unknown function, yet the same SELECT
>>> statement works fine in the Firefox SQLite Manager extension.
>>>
>>> The version of sqlite3 on my Mac is 3.4.0 but it looks like the  
>>> latest
>>> version is 3.6.x.  Could that be the cause of the problem and if so,
>>> where can I get that version (already compiled) for the Mac?
>>
>> From here:
>>
>>   http://www.sqlite.org/download.html
>
>
>  The only pre-compiled OS X binary up there is the analyzer.

Hmm. True statement.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] execute or prepare+step+finalize

2009-11-11 Thread TTTTT


Hello everyone!

i have been using function select_statement to execute SQL statements as
SELECT, CREATE, DROP, INSERT etc.. 
then i`ve replaced insert by bind function because you ve told me about
various advantages it has over insert.

now i have realized that in official documentation is also said that its
better to use prepared statements instead of execute function..

I wonder does it apply for all cases? 


here is select function that i`ve used:


int select_stmt(const char* stmt) {  
  char *errmsg;
  int   ret;
  int   nrecs = 0;

  first_row = 1;

  ret = sqlite3_exec(db, stmt, select_callback, , );

  if(ret!=SQLITE_OK) {
printf("Error in select statement %s [%s].\n", stmt, errmsg);
getchar ();
  }
  else {
printf("\n   %d records returned.\n", nrecs);
}
return 0;
}



and new function which uses prepared statements:


bool create (char * command)

{
sqlite3_stmt * statement2;


if ( sqlite3_prepare (db, command, -1, , 0) != SQLITE_OK )
{
int err = sqlite3_prepare (db, command, -1, , 0);
const char * pErr = sqlite3_errmsg (db);
printf ("\nError %d occured! \n %s", err, pErr  );
return 1;
}

int smth= sqlite3_step (statement2);
printf ("\n result code = %d \n", smth);


//sqlite3_reset (statement2);
sqlite3_finalize (statement2);

return 0;

}




main :


create ("CREATE TABLE two (a,b,c)");
create ("INSERT INTO two (a,b) VALUES (3, 1)");
create ("INSERT INTO two (a,b,c) VALUES (2, 8, 9)");
create ("INSERT INTO two (a,c) VALUES (4, 1)");
create ("SELECT * FROM two");




The thing is that create function does not execute last instruction:  create
("SELECT * FROM two");
it returns sqlite_row instead of sqlite_done which i would expect.. 

I would appreciate if someone could explain me this..

Thanky in advance..

T
-- 
View this message in context: 
http://old.nabble.com/execute-or-prepare%2Bstep%2Bfinalize-tp26299247p26299247.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Understanding database lock

2009-11-11 Thread Akash Rao

Roger,

Thanks. I have read this a few times. But, i am trying to figure out how the
perl DBI for sqlite works with Sqlite's lock.

Any perl gurus here, please help.

Thanks,
Akash

On Wed, Nov 11, 2009 at 12:42 PM, Roger Binns  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Akash Rao wrote:
> > Wanted to understand the sqlite database lock a little better.
>
> Read this:
>
>  http://www.sqlite.org/lockingv3.html
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.9 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>
> iEYEARECAAYFAkr6Y+oACgkQmOOfHg372QTxtQCbB/loEO/vENj2HHb9HXVN4xol
> 8EsAn2/OsmGiUp4ymdirGG+9ihDsBVVL
> =Je68
> -END PGP SIGNATURE-
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] any keyword

2009-11-11 Thread Walter Dnes

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] any keyword

2009-11-11 Thread Jonas Sandman

Don't you just want to use IN?

SELECT G.id,name FROM Genre G
WHERE G.id IN (SELECT S.genre_id FROM Song S)
ORDER BY name ASC;

/Jonas

On Wed, Nov 11, 2009 at 9:48 AM, Artur Reilin  wrote:
> Does it required the any key? Doesn't it work without it?
>
> greetings
>
> 
>
>> 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;
>>
>> In this case I can write an equivalent query like:
>> select  G.id,name from Genre G
>> WHERE (SELECT COUNT(*) FROM Song S
>> WHERE G.id = S.genre_id) > 0
>> ORDER BY name;
>>
>> Anyway, could I avoid to use count which require a very long time? Does
>> the development
>> team have a plan including the 'any/all' keyword implementation? I think
>> it should be
>> useful for many users.
>> Regards
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>
>
> Artur Reilin
> sqlite.yuedream.de
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] any keyword

2009-11-11 Thread Artur Reilin

Does it required the any key? Doesn't it work without it?

greetings



> 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;
>
> In this case I can write an equivalent query like:
> select  G.id,name from Genre G
> WHERE (SELECT COUNT(*) FROM Song S
> WHERE G.id = S.genre_id) > 0
> ORDER BY name;
>
> Anyway, could I avoid to use count which require a very long time? Does
> the development
> team have a plan including the 'any/all' keyword implementation? I think
> it should be
> useful for many users.
> Regards
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>


Artur Reilin
sqlite.yuedream.de
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] any keyword

2009-11-11 Thread Andrea Galeazzi

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;

In this case I can write an equivalent query like:
select  G.id,name from Genre G
WHERE (SELECT COUNT(*) FROM Song S
WHERE G.id = S.genre_id) > 0
ORDER BY name;

Anyway, could I avoid to use count which require a very long time? Does 
the development
team have a plan including the 'any/all' keyword implementation? I think 
it should be
useful for many users.
Regards
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problems passing parameters between SQLite + TCL

2009-11-11 Thread Brett Schwarz

>
> 
>
>   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]]

Not sure why you are using expr in the above (i.e. you probably don't need 
it)...but 
that's not the problem. Also, if you are using Tcl 8.5, you can use lassign as 
well:

lassign $argv lat_degrees long_degress radius

> 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} {

The variable "lat1" does not exist, but the variable "lat1," does. Tcl 
procedures do not use commas to 
separate args...just spaces...so:

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 ]

Just a tip, most of the time you want to brace expr args to avoid Tcl's double 
substitution. 
No harm, just has a slight performance impact. For example:

set lat1 [expr {$lat1 / $radian}]


HTH,
--brett


  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

68 matches

Mail list logo