Re: [sqlite] sqlite3_close( ) error

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 03/23/2011 08:06 PM, Zaryab M. Munir wrote:
> I am consistently observing this error in Linux envrionment.  
> Wondering what can be wrong.

By far the best thing to do is use valgrind.  You'll immediately see who
allocates and frees memory and when it used after free.

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

iEYEARECAAYFAk2K2bMACgkQmOOfHg372QTPtACffp7nZHH7jhEm9NZajdFIsC5b
FTwAnA94RsFAVunK8NFY757zDOtqnABv
=B25D
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] detect a flaw

[Shalom Elkin]

Sorry - it doesn't work.
sqlite3_exec with the pragma directive returns no error, but the program
still agrees to insert a record that violates foreign_key constraint.

Here is the tables creation
==
CREATE TABLE people(
id integer,
nm text);
INSERT INTO "people" VALUES(1,'Jack');
INSERT INTO "people" VALUES(2,'Jill');
CREATE TABLE activity(
aid integer,
act  text,
foreign key (aid) references people(id)
);
=
Here's the simple c++ program (it is actually c...)

#include 
#include 
#include 
int main(int argc, char **argv){
sqlite3 *db;
char *zErrMsg = 0,*P0;
const  char *Q0;
const char *Z0= {"insert into activity values (1,\"play\")"};
const char *Z1= {"insert into activity values (3,\"eat\")"};
int rc;
if( argc<2 ){
fprintf(stderr, "Usage: %s DATABASE\n", argv[0]);
return 101;
  }
//open
  rc = sqlite3_open(argv[1], );
  if( rc ){
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
 return 111;
  }
rc = sqlite3_exec(db,"PRAGMA foreign_keys = ON;",NULL,NULL,);
printf("pragma returns  |%s|\n",rc,P0);
//clear
  rc = sqlite3_exec(db,"delete from activity;",NULL,NULL,);
//
  rc = sqlite3_exec(db,"begin transaction;",NULL,NULL,);
  rc = sqlite3_exec(db,Z0,NULL,NULL,);
  Q0 =  sqlite3_errmsg(db);
  printf("|%s| - should succeed : %d,|%s|=>|%s|\n",Z0,rc,P0,Q0);
//
  rc = sqlite3_exec(db,Z1,NULL,NULL,);
  Q0 =  sqlite3_errmsg(db);
  printf("|%s| - should fail : %d,|%s|=>|%s|\n",Z1,rc,P0,Q0);
  rc = sqlite3_exec(db,"commit;",NULL,NULL,);
  sqlite3_close(db);
}
=
and the results:
[shalom@pato sqlite]$ ./porta tik
pragma returns  |(null)|
|insert into activity values (1,"play")| - should succeed : 0,|(null)|=>|not
an error|
|insert into activity values (3,"eat")| - should fail : 0,|(null)|=>|not an
error|
[shalom@pato sqlite]$ sqlite3 tik "select * from activity"
1|play
3|eat
=



Shalom
On Thu, Mar 24, 2011 at 1:48 AM, BareFeetWare wrote:

> On 24/03/2011, at 2:50 AM, Shalom Elkin wrote:
>
> > I appreciate the input. Some of the advice comes obviously from very good
> > and talented people who find a challenge at doing things WITHOUT
> reverting
> > to code writing.
>
> Doing as much (or most often, all) of the logic in SQL (instead of
> application code) removes a level of complexity, but is also generally
> faster and internally consistent.
>
> > I did a small program. Current show -stopper :
> >
> > what is the API equivalent of
> >
> > PRAGMA foreign_keys = ON;
>
> You can just send each of the SQL commands, including the pragma statement,
> in sqlite_exec (or you can get fancy with sqlite_prepare etc where it makes
> sense).
>
> Tom
> BareFeetWare
>
> --
> iPhone/iPad/iPod and Mac software development, specialising in databases
> develo...@barefeetware.com
> --
> Comparison of SQLite GUI tools:
> http://www.barefeetware.com/sqlite/compare/?ml
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Shalom Elkin
+972-544-704994
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] detect a flaw

[Shalom Elkin]

All,

I have done exactly like BareFeetWare suggested. The referential integrity
"feature" is a no brainer for "serious" systems, where you do care about
your data consistency.
SQL doesn't have clauses where you declare where do you
want Erroneous records to go. This is usually mandated to tools like
sqlite3.

Or special purpose C++ hacks.

Thanks, guys.

Shalom

On Thu, Mar 24, 2011 at 1:48 AM, BareFeetWare wrote:

> On 24/03/2011, at 2:50 AM, Shalom Elkin wrote:
>
> > I appreciate the input. Some of the advice comes obviously from very good
> > and talented people who find a challenge at doing things WITHOUT
> reverting
> > to code writing.
>
> Doing as much (or most often, all) of the logic in SQL (instead of
> application code) removes a level of complexity, but is also generally
> faster and internally consistent.
>
> > I did a small program. Current show -stopper :
> >
> > what is the API equivalent of
> >
> > PRAGMA foreign_keys = ON;
>
> You can just send each of the SQL commands, including the pragma statement,
> in sqlite_exec (or you can get fancy with sqlite_prepare etc where it makes
> sense).
>
> Tom
> BareFeetWare
>
> --
> iPhone/iPad/iPod and Mac software development, specialising in databases
> develo...@barefeetware.com
> --
> Comparison of SQLite GUI tools:
> http://www.barefeetware.com/sqlite/compare/?ml
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Shalom Elkin
+972-544-704994
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_close( ) error

[Simon Slavin]

On 24 Mar 2011, at 3:06am, Zaryab M. Munir wrote:

> Also, I must mention that if I call a different callback function which 
> doesn't do many calloc() etc.  I don't see this error.

Very good diagnostic.

Can you compare your code with the C example on this page:



Before proceeding please make sure your own code checks the result codes of 
every SQLite operation you are calling just like the example does.

Then see if you can create the same error in that code, or make the error 
disappear from your own code.  If inserting a few 'calloc()' into the code on 
the web page creates your error, then I suspect some sort of memory-handling 
problem.  Unfortunately I am not a C expert and cannot help but perhaps someone 
else here can.

This assumes that exactly the code on that page doesn't generate the same error 
in your environment !

By the way, to whoever understands the code on the web page.  Why does one 
function simply check the return value as a boolean '( rc )' but the other 
function check by explicitly comparing with SQLITE_OK ?  I have no complaint 
about either, and using both does demonstrate that they are both okay, I just 
wonder if there is something there I am not seeing.

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

[sqlite] sqlite3_close( ) error

[Zaryab M. Munir]

Hi,

I am consistently observing this error in Linux envrionment.  
I have sqlite3 library linked with my DLL. The program links/loads my DLL as 
well as a second DLL which includes the code to open and close db connections. 

Wondering what can be wrong.

Below is description:

sqlite3 *db;
 
1) I open a db connection by sqlite3_open("db-name", );

2) I query a row from database by using sqlite3_exec( ).

3) The row data is processed in the callback function called by sqlite3_exec(). 
 I pass a pointer to the callback function and allocate several chunks of 
memory using calloc.

4) Then I try to close the db connection by calling sqlite3_close().  During 
the close operation I get General Protection Error and the rsp is pointing to 
the address near the db handle.

Below is trace log:   
NOTE:  sqlite3 *db = NULL;

Before calling sqlite3_open():
db = (nil),  = 0x7fffa7e02438

After calling sqlite3_open("db-name", ):
 db = 0x672f10,  = 0x7fffa7e02438

n = sqlite3_exec(db, "select * from table-name;", callback_mem, ptr, _err);
if (n != SQLITE_OK) {  
return ERROR; }

The sqlite3_exec doesn't fail and the callback function "callback_mem()" is 
called with the value of ptr.
As there is ONLY one row, callback_mem( ) is called ONLY once.

In the callback_mem( ) I allocated several chunks of memory in the structure 
pointed to by *ptr.

After completing the sqlite3_exec (), before calling sqlite3_close():
db = 0x672f10,  = 0x7fffa7e02438

sqlite3_close(db);
 general protection rip:2ad90420a7ed rsp:7fffa7e022e0 error:0
[1]+  Segmentation fault  /usr/bin/aim_cp_main.bin
bash-3.00#

NOTE: the value of rsp.

Also, I must mention that if I call a different callback function which doesn't 
do many calloc() etc.  I don't see this error.


Any pointer and/or ideas will be very helpful. 



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

Re: [sqlite] detect a flaw

[BareFeetWare]

On 24/03/2011, at 2:50 AM, Shalom Elkin wrote:

> I appreciate the input. Some of the advice comes obviously from very good
> and talented people who find a challenge at doing things WITHOUT reverting
> to code writing.

Doing as much (or most often, all) of the logic in SQL (instead of application 
code) removes a level of complexity, but is also generally faster and 
internally consistent.

> I did a small program. Current show -stopper :
> 
> what is the API equivalent of
> 
> PRAGMA foreign_keys = ON;

You can just send each of the SQL commands, including the pragma statement, in 
sqlite_exec (or you can get fancy with sqlite_prepare etc where it makes sense).

Tom
BareFeetWare

--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
--
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml

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

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

[Jean-Christophe Deschamps]

At 18:46 23/03/2011, you wrote:

>Current US national debt is 16 digits.

Nothing less?  That's where the bug lies.

OK, OK, I'm out ;-)


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

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

[Igor Tandetnik]

On 3/23/2011 3:52 PM, Black, Michael (IS) wrote:
> Get rid of the pennies and you can get $1.8 quintillion
>
> Is there a use for tracking 1/1000th's of a dollar at these amounts?

That's why I said you can trade range for accuracy, and vice versa.

> Newer gcc's have 128-bit ints for 64-bit platforms (but not 128-bit 
> constants).

...but SQLite can't easily store and manipulate them.
-- 
Igor Tandetnik

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

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

[Black, Michael (IS)]

Hmmm...according to my math...
Max 64-bit unsigned integer is
18446744073709551615

Drop the last digit as it can't hold 0-9
1844674407370955161

Make two decimal positions
18446744073709551.61

Now some commas so we can see better
18,446,744,073,709,551.61
That' $18 quadrillion dollars by my math.
If you want 1/1000's it's $180 trillion

Get rid of the pennies and you can get $1.8 quintillion

Is there a use for tracking 1/1000th's of a dollar at these amounts?

Newer gcc's have 128-bit ints for 64-bit platforms (but not 128-bit constants).


Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Nico Williams [n...@cryptonector.com]
Sent: Wednesday, March 23, 2011 1:44 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

On Wed, Mar 23, 2011 at 1:30 PM, Igor Tandetnik  wrote:
> On 3/23/2011 1:46 PM, TR Shaw wrote:
>> Current US national debt is 16 digits.
>
> A 64-bit unsigned integer can represent about $18 trillion, in
> millionths of a dollar. This should have both range and accuracy to
> spare, for most applications, but you can always trade one for the other
> (e.g. use units of 1/1000 of a dollar instead, for a 100 times greater
> range).

I made the same point, but that doesn't leave much of a comfort zone.
First of all, totaling up all assets and liabilities in the U.S.
economy, including pension funds, Social Security, Medicare, etcetera,
quickly gets you numbers larger than $100 trillion.  Second, a few
more decades like the last few and 2^63 will not suffice to total up
all of those numbers using 1/1,000th of a dollar as the unitm yet
without significant inflation a cent will still be the absolute
smallest unit we'll get by with.  Third, any bout of hyperinflation
(which some worry about) will mean that for a fairly long period of
time people will need a very large range to represent monetary amounts
with 1/1,000th of a dollar as the unit -- even if it doesn't happen
(and let us hope it doesn't), the range is getting uncomfortably
small.

So, yeah, 64-bit integers are a bit too small for representing monetary amounts.

By the time this becomes critical I'm sure SQLite will no longer be
quite so light, and will have grown bignum support :)  But now is a
good time to start asking whether 64-bit integers will do, and for how
long.

Nico
--
___
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] storing big numbers into NUMERIC, DECIMAL columns

[Nico Williams]

On Wed, Mar 23, 2011 at 1:30 PM, Igor Tandetnik  wrote:
> On 3/23/2011 1:46 PM, TR Shaw wrote:
>> Current US national debt is 16 digits.
>
> A 64-bit unsigned integer can represent about $18 trillion, in
> millionths of a dollar. This should have both range and accuracy to
> spare, for most applications, but you can always trade one for the other
> (e.g. use units of 1/1000 of a dollar instead, for a 100 times greater
> range).

I made the same point, but that doesn't leave much of a comfort zone.
First of all, totaling up all assets and liabilities in the U.S.
economy, including pension funds, Social Security, Medicare, etcetera,
quickly gets you numbers larger than $100 trillion.  Second, a few
more decades like the last few and 2^63 will not suffice to total up
all of those numbers using 1/1,000th of a dollar as the unitm yet
without significant inflation a cent will still be the absolute
smallest unit we'll get by with.  Third, any bout of hyperinflation
(which some worry about) will mean that for a fairly long period of
time people will need a very large range to represent monetary amounts
with 1/1,000th of a dollar as the unit -- even if it doesn't happen
(and let us hope it doesn't), the range is getting uncomfortably
small.

So, yeah, 64-bit integers are a bit too small for representing monetary amounts.

By the time this becomes critical I'm sure SQLite will no longer be
quite so light, and will have grown bignum support :)  But now is a
good time to start asking whether 64-bit integers will do, and for how
long.

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

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

[Igor Tandetnik]

On 3/23/2011 1:46 PM, TR Shaw wrote:
> On Mar 23, 2011, at 1:25 PM, Igor Tandetnik wrote:
>> Billing/accounting is best done in integers - say, in millionths of a
>> dollar.
>
> Current US national debt is 16 digits.

A 64-bit unsigned integer can represent about $18 trillion, in 
millionths of a dollar. This should have both range and accuracy to 
spare, for most applications, but you can always trade one for the other 
(e.g. use units of 1/1000 of a dollar instead, for a 100 times greater 
range).
-- 
Igor Tandetnik

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

Re: [sqlite] adding/dropping foreign key to existing table

[Sam Carleton]

On Wed, Mar 23, 2011 at 2:00 PM, Nico Williams wrote:

> Also, just to be clear, making the schema writable and then making any
> updates to sqlite_master is completely unsupported, and should be.
>

This is good, very good, IMHO.  Which is also why I won't do it:) hehehehe

Thank you for insight!

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

Re: [sqlite] adding/dropping foreign key to existing table

[Nico Williams]

Also, just to be clear, making the schema writable and then making any
updates to sqlite_master is completely unsupported, and should be.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

[Jonathan Allin]

FYI a while ago I wrote a Java application using the logistic equation and
big integers (it's still googleable).

 

It didn't take many iterations before differences in the 16th significant
figure showed up.

 

But a bit off-topic ;o)

 

From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
Sent: 23 March 2011 17:25
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

 

On 3/23/2011 12:58 PM, Eugene N wrote:
> I find the discussion very interesting. Apart from billing/accounting
every
> good (scientific) application soones or later is bound to requare
> multiprecision library.

Which scientific instrument can measure which physical quantity with an
accuracy greater than 15 significant digits? By way of example, Planck
constant is measured to about 8 significant digits.

If you are using high precision to work with data measured with low
accuracy, you are just fooling yourself - those least significant digits
you labor so hard to preserve are completely bogus.

Billing/accounting is best done in integers - say, in millionths of a
dollar.
--
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 message.
Checked by AVG - www.avg.com
Version: 10.0.1204 / Virus Database: 1498/3523 - Release Date: 03/22/11

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

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

[TR Shaw]

On Mar 23, 2011, at 1:25 PM, Igor Tandetnik wrote:

> On 3/23/2011 12:58 PM, Eugene N wrote:
>> I find the discussion very interesting. Apart from billing/accounting every
>> good (scientific) application soones or later is bound to requare
>> multiprecision library.
> 
> Which scientific instrument can measure which physical quantity with an 
> accuracy greater than 15 significant digits? By way of example, Planck 
> constant is measured to about 8 significant digits.
> 
> If you are using high precision to work with data measured with low 
> accuracy, you are just fooling yourself - those least significant digits 
> you labor so hard to preserve are completely bogus.
> 
> Billing/accounting is best done in integers - say, in millionths of a 
> dollar.

Years ago it was done with BCD strings on IBM 14xx series.

Current US national debt is 16 digits.

Tom



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

Re: [sqlite] adding/dropping foreign key to existing table

[Sam Carleton]

On Wed, Mar 23, 2011 at 12:20 PM, Nico Williams wrote:

> I do think that SQLite3 will eventually need to grow ALTER support for
> altering constraints.  This whole copy-the-table thing is not really a
> scalable solution.  Without such ALTER functionality users will often
> have to implement all constraints as triggers and/or unique indexes
> instead of using core SQLite3 functionality.
>

I would agree with that, the copy table is a bit time consuming:)


> In the meantime, there is a hack that might work: close all but one
> handle to the DB (quiesce the DB), make the schema writable, update
> the definition in sqlite_master, bump the schema version, close the
> handle you're using, then re-open the DB.  As long as you know what
> you're doing this should work, IIUC.  You must not make changes to the
> schema that are incompatible with what was there before, and in the
> case of foreign keys you might need to manually create any required
> indexes.
>

Thank you, personally I am would prefer the copy table approach, fore I am
not a big fan of low level hacks, they seem to break over time:)

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

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

[Igor Tandetnik]

On 3/23/2011 12:58 PM, Eugene N wrote:
> I find the discussion very interesting. Apart from billing/accounting every
> good (scientific) application soones or later is bound to requare
> multiprecision library.

Which scientific instrument can measure which physical quantity with an 
accuracy greater than 15 significant digits? By way of example, Planck 
constant is measured to about 8 significant digits.

If you are using high precision to work with data measured with low 
accuracy, you are just fooling yourself - those least significant digits 
you labor so hard to preserve are completely bogus.

Billing/accounting is best done in integers - say, in millionths of a 
dollar.
-- 
Igor Tandetnik

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

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

[Eugene N]

Hi

I find the discussion very interesting. Apart from billing/accounting every
good (scientific) application soones or later is bound to requare
multiprecision library.

The best way, as was stated above, would be to use blobs, as it would allow
to dispance with all in/out converting (in case of text). Also, you could
use your own format (apart from IEEE) to store floating point data (or any
other bignum data).

Also, i have noticed that the situation when any oldstyle software platform
meets specific computational needs or formats can be a good test case for
its usefullnes to a particular project.

Sometimes, a non-relational DB might be in order.

Eugene



2011/3/23 Pavel Ivanov 

> > Yes, but I can not affect column type ... FreePascal SQLite3 connector
> must
> > be able to work with any user database.
>
> If your goal is to work with any user database created outside of your
> FreePascal connector then chances are that user will use the same
> database outside of your FreePascal connector too. And if use declared
> column as NUMERIC then he probably will expect numbers to be inserted
> into that column (especially if the data type inside FreePascal is
> also some sort of number). User also will probably execute some
> queries that do some arithmetic operations on values in that column.
> And they will be really surprised to see that not all the data is
> numbers there.
>
> So you better insert everything as numbers, let SQLite transform
> everything to REAL and lose precision. And tell your users that it's
> limitation of SQLite and you cannot do anything with it, only suggest
> to insert big numbers as text.
>
>
> Pavel
>
> On Wed, Mar 23, 2011 at 9:06 AM, LacaK 
> wrote:
> >> And...no conversion is performed if you declare the field as text and
> >> insert as text.
> >
> > Yes, but I can not affect column type ... FreePascal SQLite3 connector
> must
> > be able to work with any user database.
> >
> > And when user defines column like NUMERIC or DECIMAL ... so with NUMERIC
> > column affinity,
> > then it does not help when I write/bind numbers like TEXT
> > (sqlite3_bind_text) '123456789123456789.123456789'
> > because SQLite ALWAYS convert such "text number" to native floating-point
> or
> > integer number, which leads to lost of precision.
> >
> > So probably before bind I will test if number of significant digits > 15
> > then I will use sqlite3_bind_blob else sqlite3_bind_double
> >
> > -Laco.
> >
> >
> > ___
> > 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] adding/dropping foreign key to existing table

[Nico Williams]

I do think that SQLite3 will eventually need to grow ALTER support for
altering constraints.  This whole copy-the-table thing is not really a
scalable solution.  Without such ALTER functionality users will often
have to implement all constraints as triggers and/or unique indexes
instead of using core SQLite3 functionality.

In the meantime, there is a hack that might work: close all but one
handle to the DB (quiesce the DB), make the schema writable, update
the definition in sqlite_master, bump the schema version, close the
handle you're using, then re-open the DB.  As long as you know what
you're doing this should work, IIUC.  You must not make changes to the
schema that are incompatible with what was there before, and in the
case of foreign keys you might need to manually create any required
indexes.

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

Re: [sqlite] detect a flaw

[Igor Tandetnik]

On 3/23/2011 11:50 AM, Shalom Elkin wrote:
> what is the API equivalent of
>
> PRAGMA foreign_keys = ON;

It's a SQL statement like any other. You can run it with sqlite3_exec, 
for example
-- 
Igor Tandetnik

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

Re: [sqlite] detect a flaw

[Shalom Elkin]

All,

I appreciate the input. Some of the advice comes obviously from very good
and talented people who find a challenge at doing things WITHOUT reverting
to code writing.

I did a small program. Current show -stopper :

what is the API equivalent of

PRAGMA foreign_keys = ON;

If I can not turn foreign_key support on, the insert with the erronous
id will just slip in.


Thanks,


Shalom



On Wed, Mar 23, 2011 at 5:24 PM, BareFeetWare wrote:

> On 23/03/2011, at 9:03 PM, Shalom Elkin wrote:
>
> > I am new to SQLITE. Here's the challenge:
> >
> > sqlite> .dump
> > BEGIN TRANSACTION;
> > CREATE TABLE people(
> >id integer,
> >nm text);
> > INSERT INTO "people" VALUES(1,'Jack');
> > INSERT INTO "people" VALUES(2,'Jill');
> > CREATE TABLE activity(
> >aid integer,
> >act  text,
> >foreign key (aid) references people(id)
> > );
> > COMMIT;
> > PRAGMA foreign_keys=On;
> > sqlite> .import ac.in activity
> > Error: foreign key mismatch
> >
> > This is ac.in
> >
> > 1|eat
> > 2|sleep
> > 3|run
> > 1|drink
> > 2|dream
> >
> > id 3 doesn't exist, the insertion fails. Now, this was easy. what if
> > ac.inhad millions of rows? I am looking for some way to get a message
> > like "error
> > in line 3: foreign key mismatch".
> > preferably, the import would go on without actually inserting, but report
> > ALL errors in one pass.
> >
> > Any ideas?
>
> I usually import into a separate table, then use SQL to process the data
> into the final destination. This way, I can use any dumb import tool (such
> as the .import command line tool) and take care of the smarts (including
> constraints, error logging etc) in SQL.
>
> How about this:
>
> pragma foreign_keys = on;
> create temp table "activity import"
> (   aid integer
> ,   act text
> )
> ;
> create table "import error" (aid integer);
>
> .import ac.in "activity import"
>
> begin immediate;
> insert into "import error" select aid from "activity import" where aid not
> in (select id from "people");
> insert into "activity" select * from "activity import" where aid in (select
> id from "people");
> commit;
>
> or, you can add the required people on the fly:
>
> begin immediate;
> insert or ignore into "people" (id) select aid from "activity import" where
> aid not in (select id from "people");
> insert into "activity" select * from "activity import";
> commit;
>
> For this to work, you probably want to define the primary key in people:
>
> CREATE TABLE people
> (   id integer primary key not null
> ,   nm text
> )
> ;
>
> Tom
> BareFeetWare
>
> --
> iPhone/iPad/iPod and Mac software development, specialising in databases
> develo...@barefeetware.com
>  --
> Comparison of SQLite GUI tools:
> http://www.barefeetware.com/sqlite/compare/?ml
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Shalom Elkin
+972-544-704994
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] detect a flaw

[BareFeetWare]

On 23/03/2011, at 9:03 PM, Shalom Elkin wrote:

> I am new to SQLITE. Here's the challenge:
> 
> sqlite> .dump
> BEGIN TRANSACTION;
> CREATE TABLE people(
>id integer,
>nm text);
> INSERT INTO "people" VALUES(1,'Jack');
> INSERT INTO "people" VALUES(2,'Jill');
> CREATE TABLE activity(
>aid integer,
>act  text,
>foreign key (aid) references people(id)
> );
> COMMIT;
> PRAGMA foreign_keys=On;
> sqlite> .import ac.in activity
> Error: foreign key mismatch
> 
> This is ac.in
> 
> 1|eat
> 2|sleep
> 3|run
> 1|drink
> 2|dream
> 
> id 3 doesn't exist, the insertion fails. Now, this was easy. what if
> ac.inhad millions of rows? I am looking for some way to get a message
> like "error
> in line 3: foreign key mismatch".
> preferably, the import would go on without actually inserting, but report
> ALL errors in one pass.
> 
> Any ideas?

I usually import into a separate table, then use SQL to process the data into 
the final destination. This way, I can use any dumb import tool (such as the 
.import command line tool) and take care of the smarts (including constraints, 
error logging etc) in SQL.

How about this:

pragma foreign_keys = on;
create temp table "activity import"
(   aid integer
,   act text
)
;
create table "import error" (aid integer);

.import ac.in "activity import"

begin immediate;
insert into "import error" select aid from "activity import" where aid not in 
(select id from "people");
insert into "activity" select * from "activity import" where aid in (select id 
from "people");
commit;

or, you can add the required people on the fly:

begin immediate;
insert or ignore into "people" (id) select aid from "activity import" where aid 
not in (select id from "people");
insert into "activity" select * from "activity import";
commit;

For this to work, you probably want to define the primary key in people:

CREATE TABLE people
(   id integer primary key not null
,   nm text
)
;

Tom
BareFeetWare

--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml

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

Re: [sqlite] detect a flaw

[Shalom Elkin]

Thanks, guys. i was afraid of the "Do it yourself". Im doing a small
specialized version, to be generalized later ...

Shalom

On Wed, Mar 23, 2011 at 3:41 PM, Igor Tandetnik  wrote:

> On 3/23/2011 8:42 AM, Simon Slavin wrote:
> > On 23 Mar 2011, at 11:27am, Igor Tandetnik wrote:
> >> Shalom Elkin  wrote:
> >>> id 3 doesn't exist, the insertion fails. Now, this was easy. what if
> >>> ac.inhad millions of rows? I am looking for some way to get a message
> >>> like "error
> >>> in line 3: foreign key mismatch".
> >>
> >> Write a program that parses the file, runs INSERT statement on each
> file, and reports any failures. Do it all in a transaction; at the end, roll
> it back.
> >
> > Could Shalom just type BEGIN and COMMIT around the .import command ?
> > Or does the command-line tool execute the .import command internally
> > without reference to the context of individual commands ?
>
> He wants a line-by-line error report. .import won't do that.
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Shalom Elkin
+972-544-704994
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

[Pavel Ivanov]

> Yes, but I can not affect column type ... FreePascal SQLite3 connector must
> be able to work with any user database.

If your goal is to work with any user database created outside of your
FreePascal connector then chances are that user will use the same
database outside of your FreePascal connector too. And if use declared
column as NUMERIC then he probably will expect numbers to be inserted
into that column (especially if the data type inside FreePascal is
also some sort of number). User also will probably execute some
queries that do some arithmetic operations on values in that column.
And they will be really surprised to see that not all the data is
numbers there.

So you better insert everything as numbers, let SQLite transform
everything to REAL and lose precision. And tell your users that it's
limitation of SQLite and you cannot do anything with it, only suggest
to insert big numbers as text.


Pavel

On Wed, Mar 23, 2011 at 9:06 AM, LacaK  wrote:
>> And...no conversion is performed if you declare the field as text and
>> insert as text.
>
> Yes, but I can not affect column type ... FreePascal SQLite3 connector must
> be able to work with any user database.
>
> And when user defines column like NUMERIC or DECIMAL ... so with NUMERIC
> column affinity,
> then it does not help when I write/bind numbers like TEXT
> (sqlite3_bind_text) '123456789123456789.123456789'
> because SQLite ALWAYS convert such "text number" to native floating-point or
> integer number, which leads to lost of precision.
>
> So probably before bind I will test if number of significant digits > 15
> then I will use sqlite3_bind_blob else sqlite3_bind_double
>
> -Laco.
>
>
> ___
> 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] detect a flaw

[Igor Tandetnik]

On 3/23/2011 8:42 AM, Simon Slavin wrote:
> On 23 Mar 2011, at 11:27am, Igor Tandetnik wrote:
>> Shalom Elkin  wrote:
>>> id 3 doesn't exist, the insertion fails. Now, this was easy. what if
>>> ac.inhad millions of rows? I am looking for some way to get a message
>>> like "error
>>> in line 3: foreign key mismatch".
>>
>> Write a program that parses the file, runs INSERT statement on each file, 
>> and reports any failures. Do it all in a transaction; at the end, roll it 
>> back.
>
> Could Shalom just type BEGIN and COMMIT around the .import command ?
> Or does the command-line tool execute the .import command internally
> without reference to the context of individual commands ?

He wants a line-by-line error report. .import won't do that.
-- 
Igor Tandetnik

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

Re: [sqlite] Sorting of Korean Language Characters

[ashish yadav]

Hi Simon ,

Yes , i try .but got mix result  of sorted strings.

I have one sorted stings sequence base on  Hangul Jamo like :
 1. 서산
 2. 서울
 3. 수원
 4. 순천

But this same sequence , i not got  when i use :
SELECT icu_load_collation('ko_KR', 'KOREAN');

So that why i can't say if ICU support sorting base on Hangul Jamo or not
...


Thanks & Regards
 Ashish


On Wed, Mar 23, 2011 at 6:19 PM, Simon Slavin  wrote:

>
> On 23 Mar 2011, at 10:07am, ashish yadav wrote:
>
> > I think , Korean sorting base on Jamo(Hangul Jamo) is not supported by
> ICU.
> > May be i am wrong .
> > I have gone through ICU user guide & its locale but not find any Hangul
> > Jamo support by ICU Collation service.
>
> Let's get this straight.  Have you actually tried the Korean equivalent of
>
> SELECT icu_load_collation('pl_PL', 'POLISH');
>
> to see if it does what you want ?  I would guess that it is
>
> SELECT icu_load_collation('ko_KR', 'KOREAN');
>
> but I am not certain.
>
> 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] storing big numbers into NUMERIC, DECIMAL columns

[LacaK]

And...no conversion is performed if you declare the field as text and insert as 
text.


Yes, but I can not affect column type ... FreePascal SQLite3 connector must be 
able to work with any user database.

And when user defines column like NUMERIC or DECIMAL ... so with NUMERIC column 
affinity,
then it does not help when I write/bind numbers like TEXT (sqlite3_bind_text) 
'123456789123456789.123456789'
because SQLite ALWAYS convert such "text number" to native floating-point or 
integer number, which leads to lost of precision.

So probably before bind I will test if number of significant digits > 15 then I 
will use sqlite3_bind_blob else sqlite3_bind_double

-Laco.

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

Re: [sqlite] Sorting of Korean Language Characters

[Simon Slavin]

On 23 Mar 2011, at 10:07am, ashish yadav wrote:

> I think , Korean sorting base on Jamo(Hangul Jamo) is not supported by ICU.
> May be i am wrong .
> I have gone through ICU user guide & its locale but not find any Hangul
> Jamo support by ICU Collation service.

Let's get this straight.  Have you actually tried the Korean equivalent of

SELECT icu_load_collation('pl_PL', 'POLISH');

to see if it does what you want ?  I would guess that it is

SELECT icu_load_collation('ko_KR', 'KOREAN');

but I am not certain.

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

Re: [sqlite] detect a flaw

[Simon Slavin]

On 23 Mar 2011, at 11:27am, Igor Tandetnik wrote:

> Shalom Elkin  wrote:
>> id 3 doesn't exist, the insertion fails. Now, this was easy. what if
>> ac.inhad millions of rows? I am looking for some way to get a message
>> like "error
>> in line 3: foreign key mismatch".
> 
> Write a program that parses the file, runs INSERT statement on each file, and 
> reports any failures. Do it all in a transaction; at the end, roll it back.

Could Shalom just type BEGIN and COMMIT around the .import command ?  Or does 
the command-line tool execute the .import command internally without reference 
to the context of individual commands ?

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

Re: [sqlite] adding/dropping foreign key to existing table

[Simon Slavin]

On 23 Mar 2011, at 11:39am, Sam Carleton wrote:

> My goal in adding foreign keys (FK) is simple:  Implement referential
> integrity (RI) at the database level, that way I don't shoot myself in the
> foot later (I am the only developer at this time). [snip]

That's fine.

> So what I am wondering is: Does the act of creating FK also result in the
> correct indexes being created to make the RI checks speedy?

No, you have to do it yourself.  See the beginning of section 3 of



for how SQLite will produce an error if an appropriate index cannot be found.

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

Re: [sqlite] Reg: Link error while using sqlite 3

[Black, Michael (IS)]

Unless you're running multiple SQLite apps you don't gain anything by using a 
DLL.  Plus, is your Pocket PC a i386 CPU?

So try downloading the amalgamation and include sqlite3.c and sqlite3.h in your 
project.
http://www.sqlite.org/sqlite-amalgamation-3070500.zip
You'll also find the code will be smaller because the DLL has more options than 
you probably need built into it.

And to add a DLL you need to add the sqlite3.lib file to your project and have 
the DLL in your PATH.

Once you can compile the amalgamation and get things working then you try 
building a DLL yourself if you need to.

Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Ota Durai [durai...@yahoo.com]
Sent: Tuesday, March 22, 2011 12:25 PM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] Reg: Link error while using sqlite 3

Dear Sir/Madam,

I am working on a project in vs2008 with an pocket pc.
I have downloaded the sqlite3.dll and sqlite3.h files from the amalgation & dll
of sqlite site.
i have created the lib file from this def file using the command "lib
/machine:i386 /def:sqlite3.def". and i have linked it to the project.

Please help me to resolve this issue, as i have to finish this project before
this weekend.
And, also i have already used sqlite2 with vs2005 and it worked well. Now when i
try to use sqlite3 with vs2008, it hang up with error.
Can you please suggest me, whether this error would be vs2008 or sqlite3 or any
missing parameters in the setting.

When i try to call sqlite3_open function, i get link error as below
1>-- Build started: Project: DB_NEW, Configuration: Debug Pocket PC 2003
(ARMV4) --
1>Compiling...
1>stdafx.cpp
1>Compiling...
1>DB_NEW.cpp
1>db.cpp
1>Generating Code...
1>Compiling resources...
1>Microsoft (R) Windows (R) Resource Compiler Version 6.0.5724.0
1>Copyright (C) Microsoft Corporation. All rights reserved.
1>Linking...
1>db.obj : error LNK2019: unresolved external symbol sqlite3_open referenced in
function "int __cdecl creteDB(void)" (?creteDB@@YAHXZ)
1>Pocket PC 2003 (ARMV4)\Debug/DB_NEW.exe : fatal error LNK1120: 1 unresolved
externals
1>Build log was saved at "file://e:\demo projects\WINCE
PROJECTS\DB_NEW\DB_NEW\Pocket PC 2003 (ARMV4)\Debug\BuildLog.htm"
1>DB_NEW - 2 error(s), 0 warning(s)
== Build: 0 succeeded, 1 failed, 0 up-to-date, 0 skipped ==

Thanks in advance. Please help me to resolve this.
I am attaching my project with this mail.

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

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

[Black, Michael (IS)]

Blob may be better if you need speed -- then no conversion is necessary inside 
your Pascal code to/from a string.
But if you want to be able to see and understand your database text is better 
(or you have to write a special Pascal program to decode your database to look 
at any problems).

And...no conversion is performed if you declare the field as text and insert as 
text.

sqlite> create table tab1 (a int,c text);
sqlite> insert into tab1 values 
(1,'24395734857634756.92384729847239842398423964294298473927');
sqlite> select * from tab1;
1|24395734857634756.92384729847239842398423964294298473927


Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of LacaK [la...@users.sourceforge.net]
Sent: Wednesday, March 23, 2011 2:20 AM
To: sqlite-users@sqlite.org
Subject: EXT :Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

> Here are two options which will let you get the contents back to the original 
> precision:

> A) Store the values as BLOBs.
> B) Store the value as TEXT, but add a non-digit to the beginning of each 
> number value, for example

> X24395734857634756.92384729847239842398423964294298473927

> Both methods will prevent SQLite from trying to see the value as a number.  
> Oh and since nobody seems to have pointed it out yet, SQLite doesn't have a 
> NUMERIC or a DECIMAL column type.  The types can be found here:

 
Hi all,
thank you all for your answers, advices.

So conclusion is:
A) use sqlite3_bind_blob() then no conversion is performed
B) use sqlite3_bind_text() but with some hack, which "invalidates" numbers

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

Re: [sqlite] Link error while using sqlite 3

[Igor Tandetnik]

Ota Durai  wrote:
> I am working on a project in vs2008 with an pocket pc.
> I have downloaded the sqlite3.dll and sqlite3.h files from the amalgation & 
> dll
> of sqlite site.
> i have created the lib file from this def file using the command "lib
> /machine:i386 /def:sqlite3.def". and i have linked it to the project.

Have you mentioned that .lib file in project settings? Project | Properties | 
Linker | Input | Additional Dependencies.

Also, you seem to be building for ARM processor. The precompiled .DLL file 
provided on sqlite.org is built for regular desktop Windows, running on an x86 
CPU. I don't believe it'll work on a mobile phone. You'll probably have to 
build SQLite from sources, but I must admit I know nothing about mobile 
development.
-- 
Igor Tandetnik

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

Re: [sqlite] adding/dropping foreign key to existing table

[Sam Carleton]

On Tue, Mar 22, 2011 at 10:55 PM, BareFeetWare wrote:

>
> You have to drop the old table and create a new one with the changed
> foreign keys.


This is a bummer.   Is there any desire/plan to add an alter feature, in the
future?


> > Also, from a performance perspective, is there an advantage to using a
> > foreign key in SQLite verses just an index? (aka, is it worth my time to
> add
> > the key to begin with, I understand there are draw backs).
>
> An index and a foreign key serve different purposes. What are you trying to
> achieve. Post the relevant parts of your schema.
>

My goal in adding foreign keys (FK) is simple:  Implement referential
integrity (RI) at the database level, that way I don't shoot myself in the
foot later (I am the only developer at this time).

I am aware that an index is normally used to speed up look ups and FK are to
insure RI, but it is my impression that databases created appropriate
indexes to make the RI checks very fast, alleviating the need to create that
index separately.

So what I am wondering is: Does the act of creating FK also result in the
correct indexes being created to make the RI checks speedy?

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

Re: [sqlite] detect a flaw

[Igor Tandetnik]

Shalom Elkin  wrote:
> id 3 doesn't exist, the insertion fails. Now, this was easy. what if
> ac.inhad millions of rows? I am looking for some way to get a message
> like "error
> in line 3: foreign key mismatch".

Write a program that parses the file, runs INSERT statement on each file, and 
reports any failures. Do it all in a transaction; at the end, roll it back.
-- 
Igor Tandetnik

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

[sqlite] PRAGMA cache_size problem

[Jaco Breitenbach]

Dear all,

I have compiled sqlite-autoconf-3070500 on a RedHat Linux machine.  My
application is written in C, so uses the SQLite C interface.

At startup I connect to an admin datafile, and then attach 20 more datafiles
to the connection.  After creating the initial connection (before attaching
to the other datafiles), I issue "PRAGMA cache_size=1048576".  If I query
the cache size with "PRAGMA cache_size", the correct value is returned.
However, this appears to have no actual effect on the application.  As I
proceed to insert data randomly into the 20 attached datafiles, the
application's memory footprint is capped at 64 MB, even though the combined
size of the datafiles is well over 400 MB.

As a test I recompiled SQLite with -DSQLITE_DEFAULT_CACHE_SIZE=1048576.
Without issuing the PRAGMA, the memory (cache) size now continues to grow to
match the size of the datafiles as expected.

Has any of you encountered this problem before?  Am I doing something wrong,
or could there be a problem with this release of SQLite?

Any ideas or comments will be much appreciated.

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

[sqlite] Reg: Link error while using sqlite 3

[Ota Durai]

--- On Tue, 3/22/11, Ota Durai  wrote:


From: Ota Durai 
Subject: Reg: Link error while using sqlite 3
To: sqlite-users@sqlite.org
Date: Tuesday, March 22, 2011, 10:25 AM






Dear Sir/Madam,
 
I am working on a project in vs2008 with an pocket pc.
I have downloaded the sqlite3.dll and sqlite3.h files from the amalgation & dll 
of sqlite site.
i have created the lib file from this def file using the command "lib 
/machine:i386 /def:sqlite3.def". and i have linked it to the project.
 
Please help me to resolve this issue, as i have to finish this project before 
this weekend.
And, also i have already used sqlite2 with vs2005 and it worked well. Now when 
i try to use sqlite3 with vs2008, it hang up with error.
Can you please suggest me, whether this error would be vs2008 or sqlite3 or any 
missing parameters in the setting.
 
When i try to call sqlite3_open function, i get link error as below

1>-- Build started: Project: DB_NEW, Configuration: Debug Pocket PC 2003 
(ARMV4) --
1>Compiling...
1>stdafx.cpp
1>Compiling...
1>DB_NEW.cpp
1>db.cpp
1>Generating Code...
1>Compiling resources...
1>Microsoft (R) Windows (R) Resource Compiler Version 6.0.5724.0
1>Copyright (C) Microsoft Corporation. All rights reserved.
1>Linking...
1>db.obj : error LNK2019: unresolved external symbol sqlite3_open referenced in 
function "int __cdecl creteDB(void)" (?creteDB@@YAHXZ)
1>Pocket PC 2003 (ARMV4)\Debug/DB_NEW.exe : fatal error LNK1120: 1 unresolved 
externals
1>Build log was saved at "file://e:\demo projects\WINCE 
PROJECTS\DB_NEW\DB_NEW\Pocket PC 2003 (ARMV4)\Debug\BuildLog.htm"
1>DB_NEW - 2 error(s), 0 warning(s)
== Build: 0 succeeded, 1 failed, 0 up-to-date, 0 skipped ==
 
Thanks in advance. Please help me to resolve this.
I am attaching my project with this mail.
 
Regards,
Durai



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

Re: [sqlite] In-memory database with persistent storage

[Amit Chaudhuri]

..representation of a decent sized real world network..

On Tue, Mar 22, 2011 at 10:59 AM, Bart Smissaert
wrote:

> > through a version of dijkstra's routing algorithm
>
> Just out of interest, what data is this working on?
>
> RBS
>
> On Tue, Mar 22, 2011 at 7:25 AM, Amit Chaudhuri
>  wrote:
> > [Not at all expert in sqlite but here's a practical example of speed up
> > using ":memory:" and perhaps a slightly different strategy for getting at
> > the persistent data.]
> >
> > I use sqlite3 with Qt4 / C++ for an application which reads in an
> undirected
> > graph and then chunks through a version of dijkstra's routing algorithm.
>  A
> > colleague runs this on his machine and it takes all night on a large
> network
> > running on a database on disk.  On my own machine which is more powerful
> it
> > probably runs a lot faster but still takes a couple of hours plus.
>  Changing
> > to an in memory database, reading data in and processing in memory brings
> > the run time down to a couple of minutes.  So yes - running in memory can
> be
> > much quicker.  At the end of the run I attach an on disk database and
> copy
> > out the tables I need to save using "create table select" .
> >
> > A
> >
> > On Mon, Mar 21, 2011 at 1:13 PM, Simon Friis  wrote:
> >
> >> I know how to create a database that exists only in memory by using
> >> the :memory: filename. This however, creates a new database every time
> >> and it can not be saved.
> >>
> >> Is is possible to make SQLite load a database file into memory and
> >> then save it back to the file again when the connection to the
> >> database is closed?
> >>
> >> Would it improve speed?
> >>
> >> - paldepind
> >> ___
> >> 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Reg: Link error while using sqlite 3

[Ota Durai]

Dear Sir/Madam,

I am working on a project in vs2008 with an pocket pc.
I have downloaded the sqlite3.dll and sqlite3.h files from the amalgation & dll 
of sqlite site.
i have created the lib file from this def file using the command "lib 
/machine:i386 /def:sqlite3.def". and i have linked it to the project.

Please help me to resolve this issue, as i have to finish this project before 
this weekend.
And, also i have already used sqlite2 with vs2005 and it worked well. Now when 
i 
try to use sqlite3 with vs2008, it hang up with error.
Can you please suggest me, whether this error would be vs2008 or sqlite3 or any 
missing parameters in the setting.

When i try to call sqlite3_open function, i get link error as below
1>-- Build started: Project: DB_NEW, Configuration: Debug Pocket PC 2003 
(ARMV4) --
1>Compiling...
1>stdafx.cpp
1>Compiling...
1>DB_NEW.cpp
1>db.cpp
1>Generating Code...
1>Compiling resources...
1>Microsoft (R) Windows (R) Resource Compiler Version 6.0.5724.0
1>Copyright (C) Microsoft Corporation. All rights reserved.
1>Linking...
1>db.obj : error LNK2019: unresolved external symbol sqlite3_open referenced in 
function "int __cdecl creteDB(void)" (?creteDB@@YAHXZ)
1>Pocket PC 2003 (ARMV4)\Debug/DB_NEW.exe : fatal error LNK1120: 1 unresolved 
externals
1>Build log was saved at "file://e:\demo projects\WINCE 
PROJECTS\DB_NEW\DB_NEW\Pocket PC 2003 (ARMV4)\Debug\BuildLog.htm"
1>DB_NEW - 2 error(s), 0 warning(s)
== Build: 0 succeeded, 1 failed, 0 up-to-date, 0 skipped ==
 
Thanks in advance. Please help me to resolve this.
I am attaching my project with this mail.
 
Regards,
Durai


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

Re: [sqlite] Sorting of Korean Language Characters

[ashish yadav]

Hi Jean ,

I think , Korean sorting base on Jamo(Hangul Jamo) is not supported by ICU.
May be i am wrong .
 I have gone through ICU user guide & its locale but not find any Hangul
Jamo support by ICU Collation service.

 Yes, i got your mail ...  Thanks for info...
 But I am working on Linux platform ,so don't know if your files works or
not for me ?

  I am looking  forward for  your guidance and experience .

  Thanks in advance.

  With  Regards
   Ashish



On Wed, Mar 23, 2011 at 3:08 PM, Jean-Christophe Deschamps  wrote:

> At 09:43 23/03/2011, you wrote:
>
> >I am working on one application which require sorting for Korean Language.
> >
> >The Korean Characters sort by Jamo(Hangul Jamo) ie based on KSX1001
> >character code.
> >
> >Does sqlite3 or any other package support this type of sorting ?
> >If not , then any clue to carry out this type of sorting ?
>
> I would be surprised if ICU wouldn't work for Korean.
>
> Did you get my mail yesterday?  If ICU doesn't fit your needs, I
> believe you can build your Hangul Jamo tries similar to one of the
> tries used in the extension I mailed you and write a very simple nd
> efficient collation based on it.
>
> ___
> 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] detect a flaw

[Shalom Elkin]

All,

I am new to SQLITE. Here's the challenge:

sqlite> .dump
BEGIN TRANSACTION;
CREATE TABLE people(
id integer,
nm text);
INSERT INTO "people" VALUES(1,'Jack');
INSERT INTO "people" VALUES(2,'Jill');
CREATE TABLE activity(
aid integer,
act  text,
foreign key (aid) references people(id)
);
COMMIT;
PRAGMA foreign_keys=On;
sqlite> .import ac.in activity
Error: foreign key mismatch

This is ac.in

1|eat
2|sleep
3|run
1|drink
2|dream

id 3 doesn't exist, the insertion fails. Now, this was easy. what if
ac.inhad millions of rows? I am looking for some way to get a message
like "error
in line 3: foreign key mismatch".
preferably, the import would go on without actually inserting, but report
ALL errors in one pass.

Any ideas?

Thanks


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

Re: [sqlite] PRAGMA cache_size problem

[Jaco Breitenbach]

Hi Dan,

That fixed the problem for me.  Thanks a lot, much appreciated!
Best regards,
Jaco
On 23 March 2011 09:10, Dan Kennedy  wrote:

> On 03/23/2011 03:26 PM, Jaco Breitenbach wrote:
> >   Dear experts,
> >
> > I have compiled sqlite-autoconf-3070500 on a RedHat Linux machine.  My
> > application is written in C, so uses the SQLite C interface.
> >
> > At startup I connect to an admin datafile, and then attach 20 more
> datafiles
> > to the connection.  After creating the initial connection (before
> attaching
> > to the other datafiles), I issue "PRAGMA cache_size=1048576".  If I query
> > the cache size with "PRAGMA cache_size", the correct value is returned.
> > However, this appears to have no actual effect on the application.  As I
> > proceed to insert data randomly into the 20 attached datafiles, the
> > application's memory footprint is capped at 64 MB, even though the
> combined
> > size of the datafiles is well over 400 MB.
> >
> > As a test I recompiled SQLite with -DSQLITE_DEFAULT_CACHE_SIZE=1048576.
> > Without issuing the PRAGMA, the memory (cache) size now continues to grow
> to
> > match the size of the datafiles as expected.
>
> Each attached database has a separate limit. You will need to do:
>
>   ATTACH 'new.db' AS aux;
>   PRAGMA aux.cache_size = 1048576;
>
> or something.
>
> Dan.
> ___
> 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] Sorting of Korean Language Characters

[Jean-Christophe Deschamps]

At 09:43 23/03/2011, you wrote:

>I am working on one application which require sorting for Korean Language.
>
>The Korean Characters sort by Jamo(Hangul Jamo) ie based on KSX1001
>character code.
>
>Does sqlite3 or any other package support this type of sorting ?
>If not , then any clue to carry out this type of sorting ?

I would be surprised if ICU wouldn't work for Korean.

Did you get my mail yesterday?  If ICU doesn't fit your needs, I 
believe you can build your Hangul Jamo tries similar to one of the 
tries used in the extension I mailed you and write a very simple nd 
efficient collation based on it.

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

Re: [sqlite] PRAGMA cache_size problem

[Dan Kennedy]

On 03/23/2011 03:26 PM, Jaco Breitenbach wrote:
>   Dear experts,
>
> I have compiled sqlite-autoconf-3070500 on a RedHat Linux machine.  My
> application is written in C, so uses the SQLite C interface.
>
> At startup I connect to an admin datafile, and then attach 20 more datafiles
> to the connection.  After creating the initial connection (before attaching
> to the other datafiles), I issue "PRAGMA cache_size=1048576".  If I query
> the cache size with "PRAGMA cache_size", the correct value is returned.
> However, this appears to have no actual effect on the application.  As I
> proceed to insert data randomly into the 20 attached datafiles, the
> application's memory footprint is capped at 64 MB, even though the combined
> size of the datafiles is well over 400 MB.
>
> As a test I recompiled SQLite with -DSQLITE_DEFAULT_CACHE_SIZE=1048576.
> Without issuing the PRAGMA, the memory (cache) size now continues to grow to
> match the size of the datafiles as expected.

Each attached database has a separate limit. You will need to do:

   ATTACH 'new.db' AS aux;
   PRAGMA aux.cache_size = 1048576;

or something.

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

[sqlite] Sorting of Korean Language Characters

[ashish yadav]

Hi ,

I am working on one application which require sorting for Korean Language.

The Korean Characters sort by Jamo(Hangul Jamo) ie based on KSX1001
character code.

Does sqlite3 or any other package support this type of sorting ?
If not , then any clue to carry out this type of sorting ?

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

[sqlite] PRAGMA cache_size problem

[Jaco Breitenbach]

 Dear experts,

I have compiled sqlite-autoconf-3070500 on a RedHat Linux machine.  My
application is written in C, so uses the SQLite C interface.

At startup I connect to an admin datafile, and then attach 20 more datafiles
to the connection.  After creating the initial connection (before attaching
to the other datafiles), I issue "PRAGMA cache_size=1048576".  If I query
the cache size with "PRAGMA cache_size", the correct value is returned.
However, this appears to have no actual effect on the application.  As I
proceed to insert data randomly into the 20 attached datafiles, the
application's memory footprint is capped at 64 MB, even though the combined
size of the datafiles is well over 400 MB.

As a test I recompiled SQLite with -DSQLITE_DEFAULT_CACHE_SIZE=1048576.
Without issuing the PRAGMA, the memory (cache) size now continues to grow to
match the size of the datafiles as expected.

Has any of you encountered this problem before?  Am I doing something wrong,
or could there be a problem with this release of SQLite?

Any ideas or comments will be much appreciated.

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

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

[LacaK]

Here are two options which will let you get the contents back to the original 
precision:



A) Store the values as BLOBs.
B) Store the value as TEXT, but add a non-digit to the beginning of each number 
value, for example



X24395734857634756.92384729847239842398423964294298473927



Both methods will prevent SQLite from trying to see the value as a number.  Oh 
and since nobody seems to have pointed it out yet, SQLite doesn't have a 
NUMERIC or a DECIMAL column type.  The types can be found here:



Hi all,
thank you all for your answers, advices.

So conclusion is:
A) use sqlite3_bind_blob() then no conversion is performed
B) use sqlite3_bind_text() but with some hack, which "invalidates" numbers

-Laco.

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