Re: [sqlite] sqlite3_column_name

[Simon Slavin]

On 13 Nov 2009, at 9:11pm, Kees Nuyt wrote:

> SELECT rowid AS rowid, col1, col2 
>  FROM foo;
> rowid|col1|col2
> 1|1|row1
> 2|2|row2

Heh.  Neat.  Also you can do things like

SELECT col1 AS rowid, col1, col2

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

Re: [sqlite] Disk activity on Linux

[Walter Dnes]

On Thu, Nov 12, 2009 at 11:24:58AM -0600, Bret Patterson wrote
> We're seeing a lot more disk activity than expected on Linux when
> using sqlite3. We've run this same series of test on windows and
> the disk IO is much lower, which is the opposite of what I really
> expected. Below is my scenario and perhaps someone can point out
> what I can do to fix this problem.

  Since it seems to be a linux issue, what are the mount settings for
the partitions?  This may seem obvious, so please don't take this as an
insult.  Here goes...

  - "noatime" (for all file systems) will reduce disk I/O and speed
things up

  - if using ReiserFS, use the "notail" option

  - the default is that all filesystems are mounted async, but check to
make sure

  Then there are the hard drives.  Use the hdparm utility to confirm
that DMA is turned on.  

  I assume the linux machine is a server, running in text mode, rather
than a resource-heavy GUI.

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

Re: [sqlite] sqlite3_column_name

[P Kishor]

On Fri, Nov 13, 2009 at 3:11 PM, Kees Nuyt  wrote:
> On Fri, 13 Nov 2009 15:07:54 +0100, Marco Bambini
>  wrote:
>
>> sqlite 3.6.19
>>
>> CREATE TABLE foo (col1 INTEGER PRIMARY KEY, col2 TEXT);
>>
>> SELECT rowid, col1, col2
>>
>> returns the following column names with sqlite3_column_name:
>> col1, col1, col2
>>
>> Is there a way to force the first column name to
>> be returned as rowid and not as its col1 alias?
>
> CREATE TABLE foo (
>  col1 INTEGER PRIMARY KEY,
>  col2 TEXT
> );
>
> INSERT INTO foo (col1,col2)
>     VALUES (1,'row1');
> INSERT INTO foo (col1,col2)
>     VALUES (2,'row2');
>
> .head on
> .mode list
>
> SELECT rowid, col1, col2
>  FROM foo;
> col1|col1|col2
> 1|1|row1
> 2|2|row2
>
> SELECT rowid AS rowid, col1, col2
>  FROM foo;
> rowid|col1|col2
> 1|1|row1
> 2|2|row2
>

neat!


>>Thanks.
> --
>  (  Kees Nuyt
>  )
> c[_]
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] sqlite3_column_name

[Kees Nuyt]

On Fri, 13 Nov 2009 15:07:54 +0100, Marco Bambini
 wrote:

> sqlite 3.6.19
>
> CREATE TABLE foo (col1 INTEGER PRIMARY KEY, col2 TEXT);
>
> SELECT rowid, col1, col2
>
> returns the following column names with sqlite3_column_name:
> col1, col1, col2
>
> Is there a way to force the first column name to
> be returned as rowid and not as its col1 alias?

CREATE TABLE foo (
  col1 INTEGER PRIMARY KEY, 
  col2 TEXT
);

INSERT INTO foo (col1,col2)
 VALUES (1,'row1');
INSERT INTO foo (col1,col2)
 VALUES (2,'row2');

.head on
.mode list

SELECT rowid, col1, col2 
  FROM foo;
col1|col1|col2
1|1|row1
2|2|row2

SELECT rowid AS rowid, col1, col2 
  FROM foo;
rowid|col1|col2
1|1|row1
2|2|row2

>Thanks.
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Output in currency format

[Simon Slavin]

On 13 Nov 2009, at 4:40pm, Peter Haworth wrote:

> That often give rise to some rounding issues.  I do all the math using  
> however many decimal places are given to me and then round the total  
> to two decimal places, then calculate how much is owed to each band  
> member based on that total.  But whoever writes the checks for some of  
> the above outfits doesn't seem to use the same logic since the checks  
> I get are often different than what I calculate by a few pennies  
> (usually more than the total I calculate).  Not really a big deal but  
> it does require some otherwise-unnecessay accounting entries to deal  
> with the rounding errors.

Whether or not you keep rounding fractions for yourself, be careful that the 
bands have agreed on how you do rounding.  Also, have a tax accountant check 
out that your method of rounding is acceptable by whatever tax authorities you 
deal with.  Oh yeah, and sort out what tax calculations you're going to have to 
do before you write the system.

I once wrote a system that dealt with stock events for many different types of 
stock and it had to use one type of rounding to work out how much money to give 
the stockholder and another type of rounding to work out how much tax to pay.  
My customer was allowed to keep any difference between the two (which was 
always positive).  Horribly complicated system to write but it generated a few 
pence profit every few seconds just by doing what the taxman said.

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

Re: [sqlite] Maintenance Tasks

[Simon Slavin]

On 13 Nov 2009, at 4:03pm, Robert Rawlins - Think Blue wrote:

> What tasks would you recommend running on a SQLite database and on what sort
> of regularity? I know that's a bit of a 'how longs a piece of string' type
> question but I'm just trying to understand if a SQLite database requires the
> same sort of attention as others or whether it's simple and robust nature
> means it can be left alone.

Keep cumulative backups of your database files the same as you'd do for any 
files.  Do a

PRAGMA integrity_check

every few months.  That's it.  For more details see



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

Re: [sqlite] Output in currency format

[Nicolas Williams]

On Fri, Nov 13, 2009 at 03:07:27AM +, Simon Slavin wrote:
> 
> On 13 Nov 2009, at 12:34am, Nicolas Williams wrote:
> 
> > On Thu, Nov 12, 2009 at 11:40:23PM +, Simon Slavin wrote:
> >> There's still some possibility for confusion, however: how many places
> >> of decimals do you use for each currency ?  As far as I know, no
> >> currently traded currency uses more than two digits of precision.
> >   ^^^
> > They use integer math to avoid floating point rounding issues, but
> > logically those integers are still real (or at least rational) numbers,
> > and so we can speak of base, mantissa and exponent.  The precision
> > required is pretty large, much more than two digits.
> 
> Integers in all languages I'm aware of are not stored as
> mantissa/exponent, they're stored as bits with complete precision.

That's why I wrote "logically".  Clearly, very, very clearly, int64_t
is not a float, has no mantissa, no exponent.  But you can use it as
though an int64_t were a real (well, rational) number.

> You can say you need a particular number of bits, but you'll never
> lose the last bit (the 1s) just because your numbers have got too big.
> You'll get an overflow error instead.

Yes.

> By 'two digits of precision' I was referring to cents for US dollars,
> pence for pounds sterling, etc..  Some currencies have no fractional
> part (e.g. Yen).  And I was sure there were ... ah, here we are:

Tenths of pennies are also used though.  See just about any gas station
in the U.S.

Oh, I forgot, with 64-bit _signed_ ints you get one fewer digit of
precision than I wrote earlier.

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

Re: [sqlite] Output in currency format

[Peter Haworth]

Thanks for all the comments on this.  Didn't realise there were so  
many things to worry about when dealing with currency!

The system I'm developing is only dealing with US dollars right now  
but I would hope it might make it's way into other countries at some  
point.  Even with dollars, I will have to deal with more than two  
decimal places though.  The application is aimed at independant music  
bands to manage their businesses and one of the things it does is  
import data from places like iTunes, Rhapsody, etc whose royalty  
payments commonly extend to three decimal places or more.

That often give rise to some rounding issues.  I do all the math using  
however many decimal places are given to me and then round the total  
to two decimal places, then calculate how much is owed to each band  
member based on that total.  But whoever writes the checks for some of  
the above outfits doesn't seem to use the same logic since the checks  
I get are often different than what I calculate by a few pennies  
(usually more than the total I calculate).  Not really a big deal but  
it does require some otherwise-unnecessay accounting entries to deal  
with the rounding errors.


Pete Haworth







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

[sqlite] Maintenance Tasks

[Robert Rawlins - Think Blue]

Hello Guys,

 

I'm a fairly new user to SQLite, I come from using DBMS like SQL Server,
MySQL and Oracle. I've always run scheduled maintenance tasks on these kinds
of databases, such as deleting old data, integrity checks / repairs,
rebuilding indexes, backups and so forth.

 

What tasks would you recommend running on a SQLite database and on what sort
of regularity? I know that's a bit of a 'how longs a piece of string' type
question but I'm just trying to understand if a SQLite database requires the
same sort of attention as others or whether it's simple and robust nature
means it can be left alone.

 

Thanks guys, I appreciate your feedback.

 

Rob

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

Re: [sqlite] sqlite3_column_name

[P Kishor]

On Fri, Nov 13, 2009 at 8:07 AM, Marco Bambini  wrote:
> sqlite 3.6.19
>
> CREATE TABLE foo (col1 INTEGER PRIMARY KEY, col2 TEXT);
> a
> SELECT rowid, col1, col2
>
> returns the following column names with sqlite3_column_name:
> col1, col1, col2
>
> Is there a way to force the first column name to be returned as rowid and not 
> as its col1 alias?
>

As Simon suggested, the only way really is to create your own rowid,
which will then override the automatic alias to the INTEGER PRIMARY
KEY column. Consider

sqlite> CREATE TABLE foo (rowid INTEGER, col1 INTEGER PRIMARY KEY, col2 TEXT);
sqlite> INSERT INTO foo (col2) VALUES ('blah');
sqlite> SELECT rowid, col1, col2 FROM foo;
rowid   col1col2
--  --  --
1   blah
sqlite> SELECT _rowid_, rowid, col1 FROM foo;
col1rowid   col1
--  --  --
1   1
sqlite>


If you are interested in getting a valid result from your rowid, you
could create a TRIGGER that automatically updates your custom rowid to
the value of col1.



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

[Bogdan Ureche]

Unless the user is a paying customer.

On Fri, Nov 13, 2009 at 8:37 AM, Jean-Christophe Deschamps 
wrote:

>
>
> >Unfortunately I cannot modify the query... it is supplied by an user.
>
> Well, what about upgrading the user?
>
> Sorry coul'd resist ... I'm already out!
>
>
>
> ___
> 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] sqlite3_column_name

[Jean-Christophe Deschamps]

>Unfortunately I cannot modify the query... it is supplied by an user.

Well, what about upgrading the user?

Sorry coul'd resist ... I'm already out!



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

Re: [sqlite] sqlite3_column_name

[Simon Slavin]

On 13 Nov 2009, at 2:07pm, Marco Bambini wrote:

> CREATE TABLE foo (col1 INTEGER PRIMARY KEY, col2 TEXT);
> a
> SELECT rowid, col1, col2
> 
> returns the following column names with sqlite3_column_name:
> col1, col1, col2
> 
> Is there a way to force the first column name to be returned as rowid and not 
> as its col1 alias?

Define rowid explicitly as a column of the TABLE.  The documentation explicitly 
states that column names are valid only for those columns returned which are 
copies of columns in a table.  SQLite can return anything it likes as names of 
calculation and derived columns, and what it returns may change between 
different versions of SQLite.

It's better not to rely on 'rowid' in your code unless you have defined it as a 
column yourself.  First because this is purely a SQLite thing and will confuse 
users of other SQLs.  Second because 'rowid' is just one of the many different 
names you can use for that idea.

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

Re: [sqlite] sqlite3_column_name

[Marco Bambini]

Unfortunately I cannot modify the query... it is supplied by an user.

--
Marco Bambini
http://www.sqlabs.com
http://www.creolabs.com/payshield/






On Nov 13, 2009, at 3:10 PM, Virgilio Fornazin wrote:

> SELECT
> field as NAME
> 
> does not work?
> 
> On Fri, Nov 13, 2009 at 12:07, Marco Bambini  wrote:
> 
>> sqlite 3.6.19
>> 
>> CREATE TABLE foo (col1 INTEGER PRIMARY KEY, col2 TEXT);
>> a
>> SELECT rowid, col1, col2
>> 
>> returns the following column names with sqlite3_column_name:
>> col1, col1, col2
>> 
>> Is there a way to force the first column name to be returned as rowid and
>> not as its col1 alias?
>> 
>> Thanks.
>> --
>> Marco Bambini
>> http://www.sqlabs.com
>> http://www.creolabs.com/payshield/
>> 
>> 
>> 
>> 
>> 
>> 
>> ___
>> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_column_name

[Virgilio Fornazin]

SELECT
field as NAME

does not work?

On Fri, Nov 13, 2009 at 12:07, Marco Bambini  wrote:

> sqlite 3.6.19
>
> CREATE TABLE foo (col1 INTEGER PRIMARY KEY, col2 TEXT);
> a
> SELECT rowid, col1, col2
>
> returns the following column names with sqlite3_column_name:
> col1, col1, col2
>
> Is there a way to force the first column name to be returned as rowid and
> not as its col1 alias?
>
> Thanks.
> --
> Marco Bambini
> http://www.sqlabs.com
> http://www.creolabs.com/payshield/
>
>
>
>
>
>
> ___
> 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] sqlite3_column_name

[Marco Bambini]

sqlite 3.6.19

CREATE TABLE foo (col1 INTEGER PRIMARY KEY, col2 TEXT);
a
SELECT rowid, col1, col2

returns the following column names with sqlite3_column_name:
col1, col1, col2

Is there a way to force the first column name to be returned as rowid and not 
as its col1 alias?

Thanks.
--
Marco Bambini
http://www.sqlabs.com
http://www.creolabs.com/payshield/






___
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

[P Kishor]

On Fri, Nov 13, 2009 at 2:17 AM, Jens Miltner  wrote:
>
> Am 12.11.2009 um 20:08 schrieb Peter Haworth:
>
>> Thanks for all the info.  I believe the problem lies within Revolution
>> since I'm pretty sure it includes its own private library of the
>> sqlite code.  I've reported it to them and hopefully they will fix it.
>>
>> I understand the reasons for applications having their own copy of the
>> code like this but think there's equally good reasons why they should
>> use a common library installed on the computer, maybe as an option.
>> Right now, I have to rely on Revolution updating their code to solve
>> my problems whereas with a common library approach, I could download
>> and compile as several people have suggested and not have to rely on
>> Revolution.
>
> I'd say this depends on the type of application: if an application
> uses sqlite for it's internal data management, but none of the SQL
> functionality is exposed to the user, then there are good reasons to
> include a private copy: the app might rely on certain features not
> available in older sqlite distributions which may exist on the
> deployment systems, or it might even have added some extension to
> sqlite which it relies on, etc.
>
> However, for applications that expose the SQL functionality (like php,
> perl modules, Revolution, SQL editors, etc.), it does make sense to
> use a shared library, which allows the user to update the SQLite
> implementation without having to wait for an application update...

The Perl SQlite module, DBD::SQLite comes with its own built-in
sqlite. Initially, I thought that was going to be problematic, but
have come to like it that way. Everything in one package. I screwed up
my shared library once while experimenting with some funky build
options, but my applications dependent on DBD::SQLite kept humming.

>
> As usually: "it depends..." ;-)
>

Exactly.


> my €.02,
> 
>
>
> ___
> 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

[Rich Shepard]

On Thu, 12 Nov 2009, Dan Bishop wrote:

> Microsoft Excel has a similar problem.  I ran into it back when I was
> working in a credit union and tried to import a CSV file containing credit
> card numbers.  Wouldn't have noticed except that credit card numbers are
> 16 digits long and double only has 15 digits of precision.

   Excel also has an incorrect formula for Net Present Value. Lotus 1-2-3 had
an incorrect formula for standard deviation (they used the population
formula rather than the sample formula).

   I don't use any M$ software, but when I need to import large numbers into
a spreadsheet (I use XessSE), it's always as text.

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

[Rich Shepard]

On Thu, 12 Nov 2009, Alex Mandel wrote:

> Using R might actually be a convenient way to do it all in essentially
> one step, and technically batch scriptable.

   I found a perl script that converts .dbf to .csv. It's then trivial to
import the .csv into SQLite.

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

[sqlite] Bugreport: Wrong 'SQL error or missing database' message on SQLite 3.6.18 and later

[Serge Golubenko]

Steps to reproduce.

1. Create two tables in two databases:

DocketLiga.db3

CREATE TABLE [MAIN] (
  [NUM_DEC] integer PRIMARY KEY UNIQUE,
  [DATE_DEC] DATE,
  [ID_FORM_DEC] integer);

DocketTXT.db3

CREATE VIRTUAL TABLE TXT_1 using fts3(UA);

2. Fill tables by some correct values.

3. Open first database DocketLiga.db3

4. Attach second database.
attach 'DocketTXT.db3' as tx

5. Try to execute statement
select m.num_dec from tx.txt_1 t inner join main m on t.docid =
m.num_dec where t.ua match 'word*' and m.num_dec in (1214, 1283,
1451);

On SQLite 3.6.17 all works fine.
On 3.6.18 and later message 'SQL error or missing database' appear

-- 
With best regards, Serge.
___
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

[Artur Reilin]

>
> On Nov 12, 2009, at 4:36 PM, Artur Reilin wrote:
>
>> I also wonder, why the analyzer isn't working. I doesn't work for me.
>
> In what respect is it malfunctioning?
>

I've tried to use it in command line, but it didn't work; starting or put
databases there, also don't work. at lest i didn't really know how this
analyzer should work...

with best wishes

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

Re: [sqlite] image upload to db trouble

[Artur Reilin]

> On Fri, 13 Nov 2009, Artur Reilin wrote:
>
>> To: General Discussion of SQLite Database 
>> From: Artur Reilin 
>> Subject: Re: [sqlite] image upload to db trouble
>>
>>>  //echo $images;
>>>  $db = new PDO('sqlite: database.sqlite3');
>>>
>>>  $con = sqlite_open('sqlite: database.sqlite3');
>>> if (!$con)
>>>   {
>>>   die('Could not connect: ' . mysql_error());
>>>   }
>>
>> why you first make a new db opject and then open an connection to the
>> database? new PDO sqlite already open an connection .. as i am right.
>>
>> and why mysql_error() ?
>>
>>>
>>>   $db->query('sqlite: database.sqlite3', $con);
>>>
>>>   $db->query ("INSERT INTO images (id, images, info, url)
>>>   VALUES ('$i', '$images', '$info', '$url')");
>>>
>>>   $db->query("DELETE FROM images WHERE id='$i'");
>>> }
>>
>> AS i know, if you want to put images in your db you need blob columns
>> and
>> these are supported in sqlite3. or i understand something wrong?
>
> There's no need to save the actual image in the sqlite
> database, as that could slow things like SELECT statements
> down.
>
> Another way is to move the image file to a certain
> directory. Then store only the location of the image file in
> the sqlite DB.
>
> If you move the sqlite DB, then just make sure the directory
> of images also goes with it.
>
> Kind Regards,
>
> Keith Roberts.

I wouldn't put images in the database as well; it would let grow the
database to fast - and also i don't know if the backconversion to show the
images would work right. I use the way to have an own image directory and
have my files in there.

with best wishes

Artur Reilin
---

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

Re: [sqlite] Compile Virtualtext extension

[Gary_Gabriel]

Alexey Pechnikov wrote:
> Original SQLite source tree has "ext " directory for extensions and I did
> place my extensions into this directory
>
> $ ls sqlite3-3.6.20/ext
> async  billing  compress  empty  env  fts1  fts2  fts3  functions  iconv  icu 
>  inet  key  md5  README.txt  rtree  tablefunc  undo  uuid  versioning  
> virtualtext
>
>   
Ok! That's good to know. Now I understand better.

Based on your comments I reversed the changes to determine the essential 
ones. Here's the one that fit's with your corrections and works:
Best. gcc -fPIC -lm -L../iconv/ -c -shared virtualtext.c -o 
libsqlitevirtualtext.so. The  -c command is essential.

Now I want to load the library. Spatialite doesn't cover loading the 
extension because it loads with the database. It starts with creating 
the Virtual Table.

Here is what I tried and the errors:
 SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .load libsqlitevirtualtext.so (also tried 
'libsqlitevirtualtext.so')
Error: %1 ist keine zulässige Win32-Anwendung.
 Query: SELECT load_extension('libsqlitevirtualtext.so'); - executeStep 
failed
Exception Name: NS_ERROR_FAILURE
Exception Message: Component returned failure code: 
0x80004005 (NS_ERROR_FAILURE)
 SQLiteManager: Query: select load_extension('libsqlitevirtualtext.so', 
'auto_load'); - executeStep failed
Exception Name: NS_ERROR_FAILURE
Exception Message: Component returned failure code: 
0x80004005 (NS_ERROR_FAILURE)

Thanks for help to move me along- Gary Gabriel





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

Re: [sqlite] Output in currency format

[Jean-Denis Muys]

On 11/13/09 6:31 , "Dan Bishop"  wrote:

> Microsoft Excel has a similar problem.  I ran into it back when I was
> working in a credit union and tried to import a CSV file containing
> credit card numbers.  Wouldn't have noticed except that credit card
> numbers are 16 digits long and double only has 15 digits of precision.

Well in that case and for once, I must say the problem is not Excel's, it's
yours: credit card "numbers" are not numbers at all, but strings, that just
happen to use digits only. You should have imported them as text, not as
numbers. Excel had no way to guess.

Jean-Denis

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

Re: [sqlite] image upload to db trouble

[Jens Miltner]

Am 13.11.2009 um 09:25 schrieb Keith Roberts:

> On Fri, 13 Nov 2009, Artur Reilin wrote:
>
>> To: General Discussion of SQLite Database 
>> From: Artur Reilin 
>> Subject: Re: [sqlite] image upload to db trouble
>>
>>>//echo $images;
>>>  $db = new PDO('sqlite: database.sqlite3');
>>>
>>>  $con = sqlite_open('sqlite: database.sqlite3');
>>> if (!$con)
>>> {
>>> die('Could not connect: ' . mysql_error());
>>> }
>>
>> why you first make a new db opject and then open an connection to the
>> database? new PDO sqlite already open an connection .. as i am right.
>>
>> and why mysql_error() ?
>>
>>>
>>>   $db->query('sqlite: database.sqlite3', $con);
>>>
>>>   $db->query ("INSERT INTO images (id, images, info, url)
>>>   VALUES ('$i', '$images', '$info', '$url')");
>>>
>>>   $db->query("DELETE FROM images WHERE id='$i'");
>>> }
>>
>> AS i know, if you want to put images in your db you need blob  
>> columns and
>> these are supported in sqlite3. or i understand something wrong?
>
> There's no need to save the actual image in the sqlite
> database, as that could slow things like SELECT statements
> down.
>
> Another way is to move the image file to a certain
> directory. Then store only the location of the image file in
> the sqlite DB.
>
> If you move the sqlite DB, then just make sure the directory
> of images also goes with it.

AFAICS from the original source, this is already what the code is  
supposed to do:

>if (move_uploaded_file($_FILES['userfile']['tmp_name'][$i],  
> $uploadfile))
>{
>   
> $success++;
> $info= $_POST[desc.$i];
> $images=$uploadfile;
> $url=$_POST[textfield.$i];
> ...
> $db->query ("INSERT INTO images (id, images, info, url) VALUES  
> ('$i', '$images', '$info', '$url')");
> ...
>}

Thus, $images is just a string containing the path to the moved upload  
file...

However, there's a couple of statements in the code that I don't know  
what to make of:

(1) as pointed out earlier, the DELETE statement removes the record  
immediately after INSERTing
(2) $db = new PDO('sqlite: database.sqlite3');   -> this will attempt  
to open a connection to the database file "database.sqlite3" in which  
directory? Did you check that this call succeeded??? You may need to  
use an absolute path here...
(3) $con = sqlite_open('sqlite: database.sqlite3');   -> wouldn't this  
open just another sqlite database connection via a different mechanism?
(4) $db->query('sqlite: database.sqlite3', $con);   -> what is this  
supposed to do?

Did you verify that your query calls actually succeeded?
I would
(a) check that $db != null
(b) check that the query calls actually worked  (PDO::query() returns  
a PDOStatement object, or FALSE on failure.)




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

[sqlite] disk image malformed with no system crash

[Claudio Cilloni]

Hi All.

I'm new to Sqlite, and I'm getting some rare and random 'database disk image 
is malformed' error. I have no system crashes, power failures or filesystem 
corruption. My machine is a Linux Debian, ext3 filesystem running on a flash 
disk. I'm using Sqlite 3.6.19 through Python 2.6.2.

I'm trying to get a repeatable case of this problem but, till now, with no 
luck. I even made a stress-test but 1000 times the activity created by my 
application didn't get me any errors.

My Sqlite database is created with auto_vacuum = full (if this was 
important...)

Could you suggest me something to do? The damaged database file could be 
analyzed to get ideas of what is going wrong?

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

Re: [sqlite] image upload to db trouble

[Keith Roberts]

On Fri, 13 Nov 2009, Artur Reilin wrote:

> To: General Discussion of SQLite Database 
> From: Artur Reilin 
> Subject: Re: [sqlite] image upload to db trouble
>
>>  //echo $images;
>>   $db = new PDO('sqlite: database.sqlite3');
>>
>>   $con = sqlite_open('sqlite: database.sqlite3');
>> if (!$con)
>>   {
>>   die('Could not connect: ' . mysql_error());
>>   }
>
> why you first make a new db opject and then open an connection to the
> database? new PDO sqlite already open an connection .. as i am right.
>
> and why mysql_error() ?
>
>>
>>$db->query('sqlite: database.sqlite3', $con);
>>
>>$db->query ("INSERT INTO images (id, images, info, url)
>>VALUES ('$i', '$images', '$info', '$url')");
>>
>>$db->query("DELETE FROM images WHERE id='$i'");
>> }
>
> AS i know, if you want to put images in your db you need blob columns and
> these are supported in sqlite3. or i understand something wrong?

There's no need to save the actual image in the sqlite 
database, as that could slow things like SELECT statements 
down.

Another way is to move the image file to a certain 
directory. Then store only the location of the image file in 
the sqlite DB.

If you move the sqlite DB, then just make sure the directory 
of images also goes with it.

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] sqlite3 for Mac OSX 10.5

[Jens Miltner]

Am 12.11.2009 um 20:08 schrieb Peter Haworth:

> Thanks for all the info.  I believe the problem lies within Revolution
> since I'm pretty sure it includes its own private library of the
> sqlite code.  I've reported it to them and hopefully they will fix it.
>
> I understand the reasons for applications having their own copy of the
> code like this but think there's equally good reasons why they should
> use a common library installed on the computer, maybe as an option.
> Right now, I have to rely on Revolution updating their code to solve
> my problems whereas with a common library approach, I could download
> and compile as several people have suggested and not have to rely on
> Revolution.

I'd say this depends on the type of application: if an application  
uses sqlite for it's internal data management, but none of the SQL  
functionality is exposed to the user, then there are good reasons to  
include a private copy: the app might rely on certain features not  
available in older sqlite distributions which may exist on the  
deployment systems, or it might even have added some extension to  
sqlite which it relies on, etc.

However, for applications that expose the SQL functionality (like php,  
perl modules, Revolution, SQL editors, etc.), it does make sense to  
use a shared library, which allows the user to update the SQLite  
implementation without having to wait for an application update...

As usually: "it depends..." ;-)

my €.02,



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