Re: [sqlite] ANN: SQLiteODBC Driver 0.72

[Christian Werner]

Joe Wilson wrote:
> 
> Hi Christian,
> 
> I'm not sure what this patch to SQLite 3.3.12 does.
> Is it fixing a bug or extending SQLite for use in your ODBC driver?
> Should a ticket be created for it in SQLite CVSTrac?

I've already created a low priority ticket for it, see
http://www.sqlite.org/cvstrac/tktview?tn=2206

It's an extension which is useful for the
BLOB to X/Y mapping module also part of the ODBC
driver source code.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] ANN: SQLiteODBC Driver 0.72

[Joe Wilson]

Hi Christian,

I'm not sure what this patch to SQLite 3.3.12 does.
Is it fixing a bug or extending SQLite for use in your ODBC driver?
Should a ticket be created for it in SQLite CVSTrac?

thanks.

# patch: parse foreign key constraints on virtual tables
patch -d sqlite3 -p1 <<'EOD'
diff -ur sqlite3.orig/src/build.c sqlite3/src/build.c
--- sqlite3.orig/src/build.c2007-01-09 14:53:04.0 +0100
+++ sqlite3/src/build.c 2007-01-30 08:14:41.0 +0100
@@ -2063,7 +2063,7 @@
   char *z;

   assert( pTo!=0 );
-  if( p==0 || pParse->nErr || IN_DECLARE_VTAB ) goto fk_end;
+  if( p==0 || pParse->nErr ) goto fk_end;
   if( pFromCol==0 ){
 int iCol = p->nCol-1;
 if( iCol<0 ) goto fk_end;
diff -ur sqlite3.orig/src/pragma.c sqlite3/src/pragma.c
--- sqlite3.orig/src/pragma.c   2007-01-27 03:24:56.0 +0100
+++ sqlite3/src/pragma.c2007-01-30 09:19:30.0 +0100
@@ -589,6 +589,9 @@
 pTab = sqlite3FindTable(db, zRight, zDb);
 if( pTab ){
   v = sqlite3GetVdbe(pParse);
+#ifndef SQLITE_OMIT_VIRTUAL_TABLE
+  if( pTab->isVirtual ) sqlite3ViewGetColumnNames(pParse, pTab);
+#endif
   pFK = pTab->pFKey;
   if( pFK ){
 int i = 0;
diff -ur sqlite3.orig/src/vtab.c sqlite3/src/vtab.c
--- sqlite3.orig/src/vtab.c 2007-01-09 15:01:14.0 +0100
+++ sqlite3/src/vtab.c  2007-01-30 08:23:22.0 +0100
@@ -436,6 +436,9 @@
   int rc = SQLITE_OK;
   Table *pTab = db->pVTab;
   char *zErr = 0;
+#ifndef SQLITE_OMIT_FOREIGN_KEYS
+  FKey *pFKey;
+#endif

   if( !pTab ){
 sqlite3Error(db, SQLITE_MISUSE, 0);
@@ -464,6 +467,15 @@
   }
   sParse.declareVtab = 0;

+#ifndef SQLITE_OMIT_FOREIGN_KEYS
+  assert( pTab->pFKey==0 );
+  pTab->pFKey = sParse.pNewTable->pFKey;
+  sParse.pNewTable->pFKey = 0;
+  for(pFKey=pTab->pFKey; pFKey; pFKey=pFKey->pNextFrom){
+pFKey->pFrom=pTab;
+  }
+#endif
+
   sqlite3_finalize((sqlite3_stmt*)sParse.pVdbe);
   sqlite3DeleteTable(0, sParse.pNewTable);
   sParse.pNewTable = 0;
EOD



 

8:00? 8:25? 8:40? Find a flick in no time 
with the Yahoo! Search movie showtime shortcut.
http://tools.search.yahoo.com/shortcuts/#news

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] ANN: SQLiteODBC Driver 0.72

[Christian Werner]

Joe Wilson wrote:

> Nice job on the new driver.
> 
> The new DSN parameter LoadExt=module1.dll,module2.dll for dynamically
> loadable sqlite extension modules is particularly useful.
> 
> The SQLite+TCC extension is very cool.

Thank you, Joe!

If only I had added that patch to sqlite+tcc.c erlier

---snip---
--- sqlite+tcc.c.old2007/01/28 10:58:04
+++ sqlite+tcc.c2007/02/10 17:07:10
@@ -178,6 +178,10 @@
   unsigned long val;
   void (*xInit)(void *);
   sqlite3 *db = sqlite3_user_data(ctx);
+  if( argc<1 ){
+sqlite3_result_error(ctx, "need at least one argument", -1);
+return;
+  }
 #ifdef _WIN32
   EnterCriticalSection(_mutex);
 #endif
@@ -190,11 +194,25 @@
 return;
   }
   tcc_set_output_type(t, TCC_OUTPUT_MEMORY);
+  for( i=1; i

Re: [sqlite] sqlite3 with cygwin - bash & rxvt-bash interaction

[Joe Wilson]

--- David M X Green <[EMAIL PROTECTED]> wrote:
> I'm trying sqlite3 run on WinXP by console using 
> (1) cygwin bash & 
> (2) bash run on rxvt. 
> I find sqlite3 - interactive  does not work well with .help.
> With just bash only part of the help printed to the screen; though the second 
> attempt seemed to
> provide the complete listing.
> With rxvt-bash all the help text appears but only with some difficulty such 
> as three .help's in
> succession at the command line to get any result at all. Another way of 
> obtaining the .help text
> is to .quit after .help, at which help test appears.
> Other communication from sqlite appears to work, eg .show .schema & select, 
> though I have not
> tested it thoroughly. I have not changed the stdout sqlite3 option, which is 
> set to stdout as
> shown by .show.
> Why is it that .help in particular does not work properly?
> 
> Using the windows command-line console things appear to work fine.
> The rxvt window  is preferable to the Windows command line so it would be 
> useful to use it
> reliably if possible. However I see that rxvt does not work with 
> interactively with a simple
> program compiled from c, which does a lot worse than the sqlite3.exe.
> 
> It seems that rxvt is capable of working with sqlite3.exe but just with some 
> remaining problems.
> 
> Any way of fixing these issues?
> Thank you
> David M X Green

The sqlite3.exe file on sqlite.org was compiled with MinGW (a cross 
MinGW from Linux to be precise) and is not a proper Cygwin executable,
and as result it has no notion of isatty() to tell whether it is run 
from a terminal, and its stdout/stderr buffering is screwed up when 
not run from a Windows console window.

You can run it from rxvt like this with recent versions of sqlite3:

  sqlite3 -interactive foo.db

Or, even better, just build it from scratch so it will work with readline
(up and down arrow keys to get previous commands).

  wget http://sqlite.org/sqlite-3.3.12.tar.gz
  tar xzvf sqlite-3.3.12.tar.gz
  cd sqlite-3.3.12
  ./configure && make sqlite3.exe
  ./sqlite3.exe

If I did not make a typo, it should work.


 

Don't get soaked.  Take a quick peak at the forecast
with the Yahoo! Search weather shortcut.
http://tools.search.yahoo.com/shortcuts/#loc_weather

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Problem with .import

[Rich Shepard]

On Sat, 10 Feb 2007, Kees Nuyt wrote:


This is not a comma delimited values file like .import could
process, but an SQL script. You can execute it like:
sqlite3 databasefilename  .read variable.sql


Kees,

  Thank you. I knew it was somthing simple.

Rich

--
Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.|  Accelerator(TM)
 Voice: 503-667-4517  Fax: 503-667-8863

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Problem with .import

[Kees Nuyt]

On Sat, 10 Feb 2007 12:09:12 -0800 (PST), you wrote:

>On Thu, 28 Sep 2006, Dennis Cote wrote:
>
>> I suspect you may have trailing spaces at the ends of your lines. The
>> .import command isn't very smart about things like that. Your separator is
>> set to one space, not arbitrary whitespace. It there is another separator
>> after the last field it assumes there is another field there (which might
>> be an empty string) as well.
>
>   Well, I'm back with the same problem, but a trailing space at the end of a
>record is not the problem.
>
>   I wrote data from a table using 'insert' mode so I could drop and recreate
>the table without having to manually re-enter the data. That part worked
>just fine: the new table schema matches the number and type of fields in the
>data file.
>
>   However, when I try to import the data into the table I see this error
>message:
>
>sqlite> .import variable.sql variable
>variable.sql line 1: expected 14 columns of data but found 16
>
>   The editor is configured like this:
>
>sqlite> .show
>  echo: off
>   explain: off
>   headers: off
>  mode: insert
>nullvalue: ""
>output: stdout
>separator: ","
> width:
>
>and the first line of data is:
>
>INSERT INTO variable VALUES('Vegetation','Amounts, types, and uses of plant
>cover.','Habitats','','External','x
>100','Centroid',0,100,0.2,'Strong','Fuzzy Space','Minimum','Min-max');
>
>   There are 14 fields and no extra space trailing the final ';'. The schema
>is attached for reference.
>
>   What have I gotten wrong this time, please?

This is not a comma delimited values file like .import could
process, but an SQL script. You can execute it like:
sqlite3 databasefilename  .read variable.sql

>Rich
-- 
  (  Kees Nuyt
  )
c[_]

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] sqlite3 with cygwin - bash & rxvt-bash interaction

[David M X Green]

I'm trying sqlite3 run on WinXP by console using 
(1) cygwin bash & 
(2) bash run on rxvt. 
I find sqlite3 - interactive  does not work well with .help.

With just bash only part of the help printed to the screen; though the second 
attempt seemed to provide the complete listing.
With rxvt-bash all the help text appears but only with some difficulty such as 
three .help's in succession at the command line to get any result at all. 
Another way of obtaining the .help text is to .quit after .help, at which help 
test appears.
Other communication from sqlite appears to work, eg .show .schema & select, 
though I have not tested it thoroughly. I have not changed the stdout sqlite3 
option, which is set to stdout as shown by .show.
Why is it that .help in particular does not work properly?

Using the windows command-line console things appear to work fine.
The rxvt window  is preferable to the Windows command line so it would be 
useful to use it reliably if possible. However I see that rxvt does not work 
with interactively with a simple program compiled from c, which does a lot 
worse than the sqlite3.exe.

It seems that rxvt is capable of working with sqlite3.exe but just with some 
remaining problems.

Any way of fixing these issues?
Thank you
David M X Green














-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Problem with .import

[Rich Shepard]

On Thu, 28 Sep 2006, Dennis Cote wrote:


I suspect you may have trailing spaces at the ends of your lines. The
.import command isn't very smart about things like that. Your separator is
set to one space, not arbitrary whitespace. It there is another separator
after the last field it assumes there is another field there (which might
be an empty string) as well.


  Well, I'm back with the same problem, but a trailing space at the end of a
record is not the problem.

  I wrote data from a table using 'insert' mode so I could drop and recreate
the table without having to manually re-enter the data. That part worked
just fine: the new table schema matches the number and type of fields in the
data file.

  However, when I try to import the data into the table I see this error
message:

sqlite> .import variable.sql variable
variable.sql line 1: expected 14 columns of data but found 16

  The editor is configured like this:

sqlite> .show
 echo: off
  explain: off
  headers: off
 mode: insert
nullvalue: ""
   output: stdout
separator: ","
width:

and the first line of data is:

INSERT INTO variable VALUES('Vegetation','Amounts, types, and uses of plant
cover.','Habitats','','External','x
100','Centroid',0,100,0.2,'Strong','Fuzzy Space','Minimum','Min-max');

  There are 14 fields and no extra space trailing the final ';'. The schema
is attached for reference.

  What have I gotten wrong this time, please?

Rich

--
Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.|  Accelerator(TM)
 Voice: 503-667-4517  Fax: 503-667-8863CREATE TABLE variable (var_name TEXT NOT NULL, var_desc TEXT,
policy_name TEXT NOT NULL, subpolicy_name TEXT,
var_data_src TEXT, var_scale TEXT, var_defuz TEXT,
var_UoD_low INTEGER, var_UoD_high INTEGER, var_alpha_cut REAL,
var_alpha_type TEXT, var_cons_geom TEXT, var_corr_meth TEXT,
var_impl_meth TEXT, PRIMARY KEY (var_name, policy_name));
-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] converting 1,234,567 to a number

[Jay Sprenkle]

On 2/10/07, T <[EMAIL PROTECTED]> wrote:


Hi all,

I have some imported data, where some fields contain numbers with
commas denoting thousands separators. How can I change these to
actual numbers?



If you're using unix or linux there are very nice tools for cleaning up the
data before loading it.
look at 'sed', 'awk', or 'ed'. It would be trivial using sed.

--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

Re: [sqlite] reusing prepared queries or dynamic generation of SQL?

[Jay Sprenkle]

On 2/10/07, Brodie Thiesfield <[EMAIL PROTECTED]> wrote:


Hi,

I'm looking for some SQL advice. I have a query which is used as the
base of a filter. At the moment I am using:

SELECT * FROM table WHERE (1=? OR foo=?);

The actual query is more complex and uses multiple of the constructions
in this WHERE clause. If I wanted to select on the foo column then I
would bind (0, 'desired-foo'). If I didn't then I would bind (1, '').
There is an index on the foo column, but this construction precludes the
use of it even when I have a specific foo that I want. The idea behind
this was to prepare a single select query, and then just reuse it all of
the time.



Why not have multiple statements? The 'or' clause is going to prevent that
one
from using an index effectively. It might be simpler but the performance
won't be good.

--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

[sqlite] reusing prepared queries or dynamic generation of SQL?

[Brodie Thiesfield]

Hi,

I'm looking for some SQL advice. I have a query which is used as the
base of a filter. At the moment I am using:

SELECT * FROM table WHERE (1=? OR foo=?);

The actual query is more complex and uses multiple of the constructions
in this WHERE clause. If I wanted to select on the foo column then I
would bind (0, 'desired-foo'). If I didn't then I would bind (1, '').
There is an index on the foo column, but this construction precludes the
use of it even when I have a specific foo that I want. The idea behind
this was to prepare a single select query, and then just reuse it all of
the time.

I am thinking now that this is false economy and it would be better
(faster) to dynamically assemble the SQL, prepare, step, finalize each
time instead? At the very least it seems like it should enable indexes
to be used where available.

Regards,
Brodie

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] ANN: SQLiteODBC Driver 0.72

[Joe Wilson]

Nice job on the new driver.

The new DSN parameter LoadExt=module1.dll,module2.dll for dynamically 
loadable sqlite extension modules is particularly useful.

The SQLite+TCC extension is very cool.

Thanks.

--- Christian Werner <[EMAIL PROTECTED]> wrote:
> Version 0.72 of the SQLiteODBC Driver is ready for
> download from http://www.ch-werner.de/sqliteodbc
> 
> It now supports loadable extensions as of SQLite >= 3.3.7
> in the form of a DSN option.
> The one-click Win32 installer is based on SQLite 3.3.12
> and contains the FTS1/FTS2 extensions and the experimental
> SQLite+TCC from www.sqlite.org/contrib 
> 
> Enjoy,
> 
> Christian



 

Be a PS3 game guru.
Get your game face on with the latest PS3 news and previews at Yahoo! Games.
http://videogames.yahoo.com/platform?platform=120121

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: converting 1,234,567 to a number

[Igor Tandetnik]

T  wrote:

I have some imported data, where some fields contain numbers with
commas denoting thousands separators. How can I change these to
actual numbers?

I tried using CAST, which only works with later SQLite versions, but
it doesn't seen to know the comma as the thousands marker. For
instance:
sqlite> SELECT CAST('1,234,567' AS REAL);
1.0


Your best bet is probably to clean up the data before passing it on to 
SQLite. Just strip non-digit characters and convert to a number in 
whatever language your application is written. SQL is ill suited for 
string manipulation.


Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] converting 1,234,567 to a number

[John Stanton]

T wrote:

Hi all,

I have some imported data, where some fields contain numbers with  
commas denoting thousands separators. How can I change these to  actual 
numbers?


I tried using CAST, which only works with later SQLite versions, but  it 
doesn't seen to know the comma as the thousands marker. For instance:


sqlite> SELECT CAST('1,234,567' AS REAL);
1.0

As an aside, the imported field data also starts with a dollar sign  in 
most cases. Is this the best way to get rid of it:


CREATE TABLE Sample( Cost );
INSERT INTO Sample VALUES('$1,234,567');
INSERT INTO Sample VALUES('1,000,000');
SELECT CASE WHEN Cost LIKE '$%' THEN substr( Cost, 2, length( Cost )  - 
1 ) ELSE Cost END FROM Sample;


which gives:

1,234,567
1,000,000

Thanks,
Tom


- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 



How about adding a function which parses your string and transforms it 
into the numeric format of your choice.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] converting 1,234,567 to a number

[T]

Hi all,

I have some imported data, where some fields contain numbers with  
commas denoting thousands separators. How can I change these to  
actual numbers?


I tried using CAST, which only works with later SQLite versions, but  
it doesn't seen to know the comma as the thousands marker. For instance:


sqlite> SELECT CAST('1,234,567' AS REAL);
1.0

As an aside, the imported field data also starts with a dollar sign  
in most cases. Is this the best way to get rid of it:


CREATE TABLE Sample( Cost );
INSERT INTO Sample VALUES('$1,234,567');
INSERT INTO Sample VALUES('1,000,000');
SELECT CASE WHEN Cost LIKE '$%' THEN substr( Cost, 2, length( Cost )  
- 1 ) ELSE Cost END FROM Sample;


which gives:

1,234,567
1,000,000

Thanks,
Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] ANN: SQLiteODBC Driver 0.72

[Christian Werner]

Version 0.72 of the SQLiteODBC Driver is ready for
download from http://www.ch-werner.de/sqliteodbc

It now supports loadable extensions as of SQLite >= 3.3.7
in the form of a DSN option.
The one-click Win32 installer is based on SQLite 3.3.12
and contains the FTS1/FTS2 extensions and the experimental
SQLite+TCC from www.sqlite.org/contrib 

Enjoy,

Christian

-
To unsubscribe, send email to [EMAIL PROTECTED]
-