Re: [sqlite] Bound parameters not working with prepared statement

[D. Richard Hipp]

On Fri, 2005-04-15 at 19:02 -0400, Clay Dowling wrote:
> James Berry wrote:
> 
> > It would be useful to use bound parameters in such cases.
> >
> > Might it make sense to coerce the value at runtime into a string  
> > value in such a case? I believe a similar restriction (ticket #1096:  
> > limit and offset) was recently lifted to allow bound parameters in  
> > those cases, where only an integer is allowed.
> 
> In this case we're dealing with a bit of code that is probably not 
> frequently executed.  snprintf is probably your friend here, at least 
> for the time being.
> 

sqlite3_mprintf() with the %q parameter is perhaps more friendly
still.
-- 
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Bound parameters not working with prepared statement

[Nuno Lucas]

[16-04-2005 1:16, James Berry escreveu]
I'm not the OP, but I'm simply suggesting that it would be, in  general, 
really nice if the architecture could allow bound parameters  for many 
more of these cases. Not to the point of keyword  substitution (that 
would change meaning of a statement) but for any  parameter.

Yes, snprintf and friends can be used, but the ability to use bound  
parameters can really clean up code, and help also to separate SQL  
snippets from logic code.

It's not a must have, but a nice nicety.
I'm playing as the devil's lawyer here (this is right in English?), but
you must remember bindable parameters are for pre-compiled statements so
a lot of restrictions are in order here.
By making the bindable parameter more flexible you will be slowing the
performance of the execution, as it will have to make extra preparation
before binding the values.
(Hope I had it right, as I'm no sqlite expert)
Regards,
~Nuno Lucas

Re: [sqlite] Bound parameters not working with prepared statement

[James Berry]

On Apr 15, 2005, at 4:02 PM, Clay Dowling wrote:
James Berry wrote:
It would be useful to use bound parameters in such cases.
Might it make sense to coerce the value at runtime into a string   
value in such a case? I believe a similar restriction (ticket  
#1096:  limit and offset) was recently lifted to allow bound  
parameters in  those cases, where only an integer is allowed.
In this case we're dealing with a bit of code that is probably not  
frequently executed.  snprintf is probably your friend here, at  
least for the time being.
I'm not the OP, but I'm simply suggesting that it would be, in  
general, really nice if the architecture could allow bound parameters  
for many more of these cases. Not to the point of keyword  
substitution (that would change meaning of a statement) but for any  
parameter.

Yes, snprintf and friends can be used, but the ability to use bound  
parameters can really clean up code, and help also to separate SQL  
snippets from logic code.

It's not a must have, but a nice nicety.
-jdb

Re: [sqlite] Bound parameters not working with prepared statement

[Clay Dowling]

James Berry wrote:
It would be useful to use bound parameters in such cases.
Might it make sense to coerce the value at runtime into a string  
value in such a case? I believe a similar restriction (ticket #1096:  
limit and offset) was recently lifted to allow bound parameters in  
those cases, where only an integer is allowed.
In this case we're dealing with a bit of code that is probably not 
frequently executed.  snprintf is probably your friend here, at least 
for the time being.

Clay

Re: [sqlite] api questions about data lifetimes

[Brian Swetland]

[Austin Gilbert <[EMAIL PROTECTED]>]
> Not to discourage you from rolling your own, but what about CppSQLite??
> www.codeproject.com/database/CppSQLite.asp

Well, it seems a little windows-centric and a little big for my
needs.  I already have code that works and I'm happy with.  Just
trying to be sure I'm doing the right thing as far as data ownership
and lifetime goes.

> On Apr 14, 2005, at 11:21 AM, Brian Swetland wrote:
> 
> >I'm wrapping sqlite3 with a lightweight little C++ API to allow me to
> >use it more easily from C++ code and I have a couple questions about
> >the lifespan of data passed into and received from the sqlite3 API:
> >
> >1. Will the const char* returned by sqlite3_column_name() persist
> >   until the statement is finalized, or are there situations under
> >   which sqlite could free it before then?
> >
> >2. Will SQLITE_STATIC data bound to text or blob parameters (using
> >   the sqlite3_bind_text/blob() functions) ever be touched by sqlite
> >   outside of calls to sqlite3_step()?
> >
> >3. Is my belief that text or blob data returned by
> >   sqlite3_column_text/blob() does not need to be free()'d and is
> >   only valid until the next sqlite3_step/reset/finalize() on that
> >   statement correct?

Re: [sqlite] Bound parameters not working with prepared statement

[Darren Duncan]

I believe that exactly the right circumstances to allow bound 
parameters is all of the same places where literal values are 
allowed, namely strings, numbers, nulls, etc.  It does not make sense 
to have bound parameters in any other situation. -- Darren Duncan

Re: [sqlite] Bound parameters not working with prepared statement

[James Berry]

On Apr 15, 2005, at 4:16 PM, D. Richard Hipp wrote:
On Fri, 2005-04-15 at 15:49 -0700, Cory Nelson wrote:
It seems when a bind a string to "attach ? as dbname", it is never
translated into the final statement.  Is this supposed to happen?
SQLite only allows bound parameters in places where it is legal
to put an expression.  Remember that bound parameters can be
filled in with NULL, integers, floating point numbers, or strings.
And so bound parameters can only be used in places were all those
values are legal options.  You can not use a floating point number,
an integer, or a NULL as the name of a database file in an ATTACH
statement - that would make no sense.  Hence, bound parameters are
not allowed in that context.
It would be useful to use bound parameters in such cases.
Might it make sense to coerce the value at runtime into a string  
value in such a case? I believe a similar restriction (ticket #1096:  
limit and offset) was recently lifted to allow bound parameters in  
those cases, where only an integer is allowed.

-jdb

Re: [sqlite] Bound parameters not working with prepared statement

[D. Richard Hipp]

On Fri, 2005-04-15 at 15:49 -0700, Cory Nelson wrote:
> It seems when a bind a string to "attach ? as dbname", it is never
> translated into the final statement.  Is this supposed to happen?
> 

SQLite only allows bound parameters in places where it is legal
to put an expression.  Remember that bound parameters can be
filled in with NULL, integers, floating point numbers, or strings.
And so bound parameters can only be used in places were all those
values are legal options.  You can not use a floating point number,
an integer, or a NULL as the name of a database file in an ATTACH
statement - that would make no sense.  Hence, bound parameters are
not allowed in that context.
-- 
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Uclibc prob

[D. Richard Hipp]

On Fri, 2005-04-15 at 16:27 +, Rui Silva wrote:
> hi ppl, 
> 
> i'm new to sqlite and i was trying to use sqlite under a uclibc system.
> the version i'm using is 3.2.1-r1 and when i run sqlite3 test.db i get
> a segmentation fault.
> 
> does sqlite works with uclibc??? is it just a a sqlite3 error or the
> main lib prob??
> 

I have never tested sqlite with uclibc.  But sqlite makes very modest
demands on its C library so I cannot imagine that there would be
any serious incompatibilities.

Version 3.2.1 of SQLite does not contain any known segfault errors.
Perhaps there are bugs in your test code that are causing the
segfault?
-- 
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] multi threading

[D. Richard Hipp]

On Fri, 2005-04-15 at 14:44 +0300, Cosmin Vlasiu wrote:
> Hello to everyone,
> 
> I have a question... regarding multi-threading...
> the question is for microsoft windows (a visual c++ application)... I saw 
> the documentation
> and I understood that for microsoft OS, the multi-threading is
> enabled by default.
> So, of course I start two threads, both of them make a loop into a 
> "recordset".
> the first thread it works good, but the second, no way. I made kind of 
> research
> in the documentation and I observed that I have to make new connections for 
> each thread.
> All donne. But when I run the application I got the same problem.
> In the second thread the sqlite3_prepare function always return the 21 value
> that means SQLITE_MISUSE, because sqlite3SafetyOn retun 1.
> 
> Can somebody tells me if there is a solution for that problem?
> 

Two separate threads may not use the same database handle at the
same time.  If they do, the SQLITE_MISUSE value is often returned.
Separate threads should have their own database handles.

I know you said above that you are using separate database handles
in your two threads.  But probably there is a bug in your code that
is preventing this from happening really.

My advice to *all* programmers is to never use more than one thread
in the same address space.  I have never in 20 years worked on a
multiple threaded program that actually got all of the threading
issues right.  There are always subtle bugs that cause error that
are very difficult to reproduce and fix.  Multithreading is the
fastest road to buggy code that I know of.  Avoid it.  If you
absolutely, positively must have multiple threads of control, put
each thread in its own address space (make it a process.)
-- 
D. Richard Hipp <[EMAIL PROTECTED]>

[sqlite] Bound parameters not working with prepared statement

[Cory Nelson]

It seems when a bind a string to "attach ? as dbname", it is never
translated into the final statement.  Is this supposed to happen?

-- 
Cory Nelson
http://www.int64.org

[sqlite] Uclibc prob

[Rui Silva]

hi ppl, 

i'm new to sqlite and i was trying to use sqlite under a uclibc system.
the version i'm using is 3.2.1-r1 and when i run sqlite3 test.db i get
a segmentation fault.

does sqlite works with uclibc??? is it just a a sqlite3 error or the
main lib prob??

thks 




-- 
Rui Silva
Powered by Gentoo Linux under :
CELERON 1000 - Stage1 install with nptl
Pentium M 1800GHz - Stage1 install with NPTL

http://rukinhas.no-ip.org

Re: [sqlite] Copying a table between databases

[Jay Sprenkle]

There's an option on the create table sql to do this.
I believe it's
  create table x as select * from y;

On 4/14/05, Dennis Volodomanov <[EMAIL PROTECTED]> wrote:
> Thank you Derrell and Cory,
> 
> I can create triggers when I create the database file and I'm not using
> indexes in this particular program, so that seems like the way to go.
> Wouldn't a function like sqlite3_copytable(sqlite3 *pDest, sqlite3 *pSrc,
> ...) be a good idea? Or it would make SQLite more complex and thus go
> against the main principle?
>

Re: [sqlite] Multiple Tables on one Flat File

[Uriel_Carrasquilla]

Got it.  Thank you.  Eric did point it out as well.
In summary, do all my creates, then do all my prepares, then do all my
executes with the dummy @bind (? ?) fields.
Finally, I proceed to do my true executes and commits.

Regards,

[EMAIL PROTECTED]
NCCI
Boca Raton, Florida
561.893.2415
greetings / avec mes meilleures salutations / Cordialmente
mit freundlichen Grüßen / Med vänlig hälsning




  John LeSueur  

  <[EMAIL PROTECTED]To:   
sqlite-users@sqlite.org
  om>  cc:  

  Sent by: Subject:  Re: [sqlite] Multiple 
Tables on one Flat File  
  [EMAIL PROTECTED] 

  du





  04/14/2005 01:43  

  PM

  Please respond to 

  sqlite-users  









[EMAIL PROTECTED] wrote:

>
>
>Eric:
>thank you for your reply.
>I ended up creating a %dbh hash with $table as the index along with one
>Operating System file for each table.
>I was hoping there was a way not to create so many Operating System files
>because of the extra Administration they require.
>can you think of any way around this?
>I read all my data from a log and I am creating multiple Tables based on
>the content of the data in the log.
>
>Regards,
>
>[EMAIL PROTECTED]
>NCCI
>Boca Raton, Florida
>561.893.2415
>greetings / avec mes meilleures salutations / Cordialmente
>mit freundlichen Grüßen / Med vänlig hälsning
>
>
If you look at Dr Hipp's mail, the problem is actually the order of your
operations. You need to drop all your prepared statements and reprepare
them after you do any CREATE, ALTER, DROP or VACUUM statements.

John





The information contained in this e-mail message is intended only for 
the personal and confidential use of the recipient(s) named above. This 
message may be an attorney-client communication and/or work product and 
as such is privileged and confidential. If the reader of this message 
is not the intended recipient or an agent responsible for delivering it 
to the intended recipient, you are hereby notified that you have 
received this document in error and that any review, dissemination, 
distribution, or copying of this message is strictly prohibited. If you 
have received this communication in error, please notify us immediately 
by e-mail, and delete the original message.

Re: Re: [sqlite] optimize table

[Jay Sprenkle]

Since you produce the statistics once per month on a schedule
it would be more efficient to not have an index. The index will slow down daily
operations each time a record is inserted. Copy the database file once per
month to a 'snapshot' file. Add the index to the snapshot database and
then produce your statistics. Make it a scheduled task that runs the night
before you need it.

On 4/15/05, msaka msaka <[EMAIL PROTECTED]> wrote:
> have you some feeling on my problem?
> 
> cut or not cut db files?
> 
> >-Pôvodná správa-
> >Od: D. Richard Hipp [mailto:[EMAIL PROTECTED]
> >Odoslané: 15. apríla 2005 11:15
> >Komu: sqlite-users@sqlite.org
> >Predmet: Re: [sqlite] optimize table
> >
> >
> >On Fri, 2005-04-15 at 06:15 -0400, Ken & Deb Allen wrote:
> >> I cannot speak for SQLITE for certain, but I know with a number of
> >> commercial RDBMS packages the index is not used unless it contains a
> >> sufficient degree of distribution.
> >>
> >
> >SQLite does not do this.  It makes no effort to keep track of the
> >"degree of distribution" or other statistics associated with indices.
> >It just uses them if they are available.
> >
> >This can be either a feature or a bug, depending on your point
> >of view.  I have had people tell me that they prefer the SQLite
> >way.  With other databases, they tell me, they are constantly
> >in a struggle trying to outwit and trick the database engine
> >into doing what they want.  SQLite just does what you tell it
> >to do, for better or for worse.
> >--
> >D. Richard Hipp <[EMAIL PROTECTED]>
> 
> 


-- 
---
You a Gamer? If you're near Kansas City:
Conquest 36
https://events.reddawn.net

The Castles of Dereth Calendar: a tour of the art and architecture of
Asheron's Call
http://www.lulu.com/content/77264

Re: [sqlite] How to Embed SQLite in VC++

[Clay Dowling]

Mahendra Batra said:
> Someone, plz tell me how could i embed SQLite. I included sqlite.h but
> getting unresolved errors i.e the definition of functions like
> sqlite3_open(..) and sqlite3_exec(..) can not be found.
> Please favor me as soon as possible.

In general the actual error message thrown by your compiler is the most
helpful piece of information.

On a wild guess you're not linking to the SQLite library.  You'll need to
import the dll to make your link library (not sure of the tool in VC++,
possibly implib). Then link to that library and you should have your
dependencies resolved.

If your problem is more that the program just won't compile, you need to
make usre that the sqlite3.h file is in the include path.

Clay

-- 
Lazarus Notes from Lazarus Internet Development
http://www.lazarusid.com/notes/
Articles, Reviews and Commentary on web development

Re: [sqlite] libreadline.so error undefined reference.

[Mike Shaver]

On 4/15/05, Cem Vedat ISIK <[EMAIL PROTECTED]> wrote:
> ./.libs/libsqlite3.so: undefined reference to `sqlite3KeywordCode'

Can't help you with that, but:

> /opt/eldk/usr/../ppc_8xx/usr/lib/libreadline.so: undefined reference to
> `tgoto'
> /opt/eldk/usr/../ppc_8xx/usr/lib/libreadline.so: undefined reference to
> `tgetflag'

these look like you need to be linking against -lcurses or -lncurses
or whatever your platform has.  Try getting those into the link line
and see if that improves your lot.

Mike

Re: Re: [sqlite] optimize table

[msaka msaka]

have you some feeling on my problem?

cut or not cut db files?


>-PÃvodnà sprÃva-
>Od: D. Richard Hipp [mailto:[EMAIL PROTECTED]
>OdoslanÃ: 15. aprÃla 2005 11:15
>Komu: sqlite-users@sqlite.org
>Predmet: Re: [sqlite] optimize table
>
>
>On Fri, 2005-04-15 at 06:15 -0400, Ken & Deb Allen wrote:
>> I cannot speak for SQLITE for certain, but I know with a number of
>> commercial RDBMS packages the index is not used unless it contains a
>> sufficient degree of distribution.
>>
>
>SQLite does not do this.  It makes no effort to keep track of the
>"degree of distribution" or other statistics associated with indices.
>It just uses them if they are available.
>
>This can be either a feature or a bug, depending on your point
>of view.  I have had people tell me that they prefer the SQLite
>way.  With other databases, they tell me, they are constantly
>in a struggle trying to outwit and trick the database engine
>into doing what they want.  SQLite just does what you tell it
>to do, for better or for worse.
>--
>D. Richard Hipp <[EMAIL PROTECTED]>

[sqlite] multi threading

[Cosmin Vlasiu]

Hello to everyone,
   I have a question... regarding multi-threading...
the question is for microsoft windows (a visual c++ application)... I saw 
the documentation
and I understood that for microsoft OS, the multi-threading is
enabled by default.
   So, of course I start two threads, both of them make a loop into a 
"recordset".
the first thread it works good, but the second, no way. I made kind of 
research
in the documentation and I observed that I have to make new connections for 
each thread.
All donne. But when I run the application I got the same problem.
In the second thread the sqlite3_prepare function always return the 21 value
that means SQLITE_MISUSE, because sqlite3SafetyOn retun 1.

   Can somebody tells me if there is a solution for that problem?
Thanks in advance
Cosmin 

RE: [sqlite] Problem storing integers

[D. Richard Hipp]

On Fri, 2005-04-15 at 10:53 +0100, Richard Boulton wrote:
> > I'm running the latest sqlite 3.2.1 command line tool on Windows
> > XP
> 
> I've just run some older versions of the command line tool and the last time
> the value 281474976710655 was stored correctly was 3.0.8 I was missing a
> couple of releases after 3.0.8 but saw the unexpected behaviour start in
> 3.1.2. Maybe it was introduced in 3.1?
> 

The bug was introduced by check-in [2246] on 2005-Jan-20 just prior
to 3.1.0.  Any integer between 140737488355328 and 281474976710655
is stored incorrectly.  I'll put in a patch sometime today.

-- 
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] optimize table

[D. Richard Hipp]

On Fri, 2005-04-15 at 06:15 -0400, Ken & Deb Allen wrote:
> I cannot speak for SQLITE for certain, but I know with a number of 
> commercial RDBMS packages the index is not used unless it contains a 
> sufficient degree of distribution. 
> 

SQLite does not do this.  It makes no effort to keep track of the
"degree of distribution" or other statistics associated with indices.
It just uses them if they are available.

This can be either a feature or a bug, depending on your point
of view.  I have had people tell me that they prefer the SQLite
way.  With other databases, they tell me, they are constantly
in a struggle trying to outwit and trick the database engine
into doing what they want.  SQLite just does what you tell it
to do, for better or for worse.
-- 
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] vacuum

[D. Richard Hipp]

On Fri, 2005-04-15 at 09:53 +0200, Eggert Henri wrote:
> Hi sqlite-users :)
> 
> Deleting records in a sqlite db leaves unused blocks which are free'd by
> vacuum.
> 
> Whithout doing vacuum , does inserting new records use the wasted space
> , or remains wasted space for ever. 
> 

Unused space is reused as needed for new inserts.  No space is ever
"leaked".
-- 
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] optimize table

[Ken & Deb Allen]

P.S. When experimenting with this, try naming this 'second' table first 
to reduce the work the database has to do in selecting/rejecting 
records. Ypu can experiment with this by creating this new table, 
populating it with a SELECT from the existing table (only the records 
without statistics), and then timing the SELECT/JOIN.

-ken
On 15-Apr-05, at 6:15 AM, Ken & Deb Allen wrote:
I cannot speak for SQLITE for certain, but I know with a number of 
commercial RDBMS packages the index is not used unless it contains a 
sufficient degree of distribution. Microsoft SQL Server, for example, 
is quite explicit in stating that if the distribution of values across 
the range of records in an index falls below a certain level, then the 
index is ignored, which is why you are used to put the most dynamic 
column first in a multi-column index. I do not know if SQLITE performs 
any of this analysis and makes decisions on whether to use the index 
or not, but it is possible, especially if a sort is involved, that the 
index is not helping much if all records have one of two values, and 
as the majority of he records have the same value, you are looking 
only for the minority records. I do not know if using a date/time for 
this files will speed it up or not, since your statistical inclusion 
query will be for records "WHERE statistic IS NULL" or "WHERE 
statistic = 0", and as the database grows in size this will be the 
minority set of records.

One 'trick' I have seen in the past was to create a second table that 
contained an entry for new records, also keyed by the same "id"; as 
each sale is created, an entry is also made in that table, possibly by 
a trigger. You select the records for statistics using a JOIN (inner), 
which will only return records that are in both tables. As the records 
are processed, the record is removed from the second table, and 
therefore the records will not be part of the JOIN in the future. In 
some databases this may be faster than using an index; I have not 
experimented with this in SQLITE, however.

-ken
On 15-Apr-05, at 4:30 AM, msaka msaka wrote:
i dont need working with dates and times..
i only set 1 records on which was done..
which index is better to create for this problem?
must i cut db files? or speed of statistic will be same now and after 
1000 000 records?


-Pôvodná správa-
Od: Gert Rijs [mailto:[EMAIL PROTECTED]
Odoslané: 15. apríla 2005 7:13
Komu: sqlite-users@sqlite.org
Predmet: Re: [sqlite] optimize table
msaka msaka wrote:
CREATE TABLE sale(
   id INTEGER NOT NULL UNIQUE PRIMARY KEY,
   bill_no integer,
   bill_item_no integer,
   item_name varchar(20),
   qty integer,
   amount DOUBLE,
   statistic integer
)

Why don't you replace "statistic" with a timestamp formatted like
mmdd, perhaps add the time as well. If you add an index on it you
can efficiently retrieve the rows for certain date periods without
needing to update the table.
Gert

Re: [sqlite] optimize table

[Ken & Deb Allen]

I cannot speak for SQLITE for certain, but I know with a number of 
commercial RDBMS packages the index is not used unless it contains a 
sufficient degree of distribution. Microsoft SQL Server, for example, 
is quite explicit in stating that if the distribution of values across 
the range of records in an index falls below a certain level, then the 
index is ignored, which is why you are used to put the most dynamic 
column first in a multi-column index. I do not know if SQLITE performs 
any of this analysis and makes decisions on whether to use the index or 
not, but it is possible, especially if a sort is involved, that the 
index is not helping much if all records have one of two values, and as 
the majority of he records have the same value, you are looking only 
for the minority records. I do not know if using a date/time for this 
files will speed it up or not, since your statistical inclusion query 
will be for records "WHERE statistic IS NULL" or "WHERE statistic = 0", 
and as the database grows in size this will be the minority set of 
records.

One 'trick' I have seen in the past was to create a second table that 
contained an entry for new records, also keyed by the same "id"; as 
each sale is created, an entry is also made in that table, possibly by 
a trigger. You select the records for statistics using a JOIN (inner), 
which will only return records that are in both tables. As the records 
are processed, the record is removed from the second table, and 
therefore the records will not be part of the JOIN in the future. In 
some databases this may be faster than using an index; I have not 
experimented with this in SQLITE, however.

-ken
On 15-Apr-05, at 4:30 AM, msaka msaka wrote:
i dont need working with dates and times..
i only set 1 records on which was done..
which index is better to create for this problem?
must i cut db files? or speed of statistic will be same now and after 
1000 000 records?


-Pôvodná správa-
Od: Gert Rijs [mailto:[EMAIL PROTECTED]
Odoslané: 15. apríla 2005 7:13
Komu: sqlite-users@sqlite.org
Predmet: Re: [sqlite] optimize table
msaka msaka wrote:
CREATE TABLE sale(
   id INTEGER NOT NULL UNIQUE PRIMARY KEY,
   bill_no integer,
   bill_item_no integer,
   item_name varchar(20),
   qty integer,
   amount DOUBLE,
   statistic integer
)

Why don't you replace "statistic" with a timestamp formatted like
mmdd, perhaps add the time as well. If you add an index on it you
can efficiently retrieve the rows for certain date periods without
needing to update the table.
Gert

RE: [sqlite] Problem storing integers

[Richard Boulton]

> I'm running the latest sqlite 3.2.1 command line tool on Windows
> XP

I've just run some older versions of the command line tool and the last time
the value 281474976710655 was stored correctly was 3.0.8 I was missing a
couple of releases after 3.0.8 but saw the unexpected behaviour start in
3.1.2. Maybe it was introduced in 3.1?

Rich

Re: Re: [sqlite] optimize table

[msaka msaka]

i dont need working with dates and times..
i only set 1 records on which was done..

which index is better to create for this problem?
must i cut db files? or speed of statistic will be same now and after 1000 000 
records?


>-Pôvodná správa-
>Od: Gert Rijs [mailto:[EMAIL PROTECTED]
>Odoslané: 15. apríla 2005 7:13
>Komu: sqlite-users@sqlite.org
>Predmet: Re: [sqlite] optimize table
>
>
>msaka msaka wrote:
>>
>> CREATE TABLE sale(
>>id INTEGER NOT NULL UNIQUE PRIMARY KEY,
>>bill_no integer,
>>bill_item_no integer,
>>item_name varchar(20),
>>qty integer,
>>amount DOUBLE,
>>statistic integer
>> )
>>
>>
>
>Why don't you replace "statistic" with a timestamp formatted like
>mmdd, perhaps add the time as well. If you add an index on it you
>can efficiently retrieve the rows for certain date periods without
>needing to update the table.
>
>Gert

[sqlite] libreadline.so error undefined reference.

[Cem Vedat ISIK]

Hi everybody,
I'm trying to cross compile sqlite for PowerPC using ppc_8xx-gcc on 
ELDK, I've edited Makefile and libtool by hand, but I'm stuck at this point.

./libtool --mode=link ppc_8xx-gcc -g -O2 -DOS_UNIX=1 -DHAVE_USLEEP=1 -I. 
-I./src -DNDEBUG  -DSQLITE_OMIT_CURSOR -DHAVE_READLINE=1 
-I/usr/include/readline  \
   -o sqlite3 ./src/shell.c libsqlite3.la -lreadline -lreadline
ppc_8xx-gcc -g -O2 -DOS_UNIX=1 -DHAVE_USLEEP=1 -I. -I./src -DNDEBUG 
-DSQLITE_OMIT_CURSOR -DHAVE_READLINE=1 -I/usr/include/readline -o 
.libs/sqlite3 ./src/shell.c  ./.libs/libsqlite3.so -lreadline
/opt/eldk/usr/../ppc_8xx/usr/lib/libreadline.so: undefined reference to 
`tgetnum'
./.libs/libsqlite3.so: undefined reference to `sqlite3KeywordCode'
/opt/eldk/usr/../ppc_8xx/usr/lib/libreadline.so: undefined reference to 
`tgoto'
/opt/eldk/usr/../ppc_8xx/usr/lib/libreadline.so: undefined reference to 
`tgetflag'
/opt/eldk/usr/../ppc_8xx/usr/lib/libreadline.so: undefined reference to `BC'
/opt/eldk/usr/../ppc_8xx/usr/lib/libreadline.so: undefined reference to 
`tputs'
./.libs/libsqlite3.so: undefined reference to `keywordCode'
/opt/eldk/usr/../ppc_8xx/usr/lib/libreadline.so: undefined reference to `PC'
/opt/eldk/usr/../ppc_8xx/usr/lib/libreadline.so: undefined reference to 
`tgetent'
/opt/eldk/usr/../ppc_8xx/usr/lib/libreadline.so: undefined reference to `UP'
/opt/eldk/usr/../ppc_8xx/usr/lib/libreadline.so: undefined reference to 
`tgetstr'
collect2: ld returned 1 exit status
make: *** [sqlite3] Error 1
cem-linux:~/ppc_8xx/mywork/sqlite-3.2.1 #

I'm getting these "undefined reference" ibreadline so errors. I'm 
looking forward to hearing any suggestions from you soon. Thanks for 
your time and considerations.

--
Cem Vedat ISIK
[EMAIL PROTECTED]
R Engineer
Telecommunication Technologies

[sqlite] vacuum

[Eggert Henri]

Hi sqlite-users :)

Deleting records in a sqlite db leaves unused blocks which are free'd by
vacuum.

Whithout doing vacuum , does inserting new records use the wasted space
, or remains wasted space for ever. 


Thanks a lot for any help!

With kind regards

Henri

[sqlite] optimize table

[msaka msaka]

hi

i create cashdesk system with sqlite 3.

my qustions are:

i insert sales into table sale:

CREATE TABLE sale(
   id INTEGER NOT NULL UNIQUE PRIMARY KEY,
   bill_no integer,
   bill_item_no integer,
   item_name varchar(20),
   qty integer,
   amount DOUBLE,
   statistic integer
)


table with recors looks like:

id   | bill no | bill item no|code |name   | qty  | amount 
| statistic was made |
-
1| 1   |1|  825454 |beer 10%   |5 |55.22   
|  1 |
2| 1   |2|  783213 |Butter |6 |12.54   
|  1 |



100  | |1|  521133 |Cd750mb|2 |55  
|  0  |

records in table sale are inserted 1500 lines per day... (when people something 
buy :) )

one times per month i make statistic on table sale (sales per product code)..
i make statistic and update field statistic_was_made to 1 (on this records was 
statistic made i will be not use for next 3 months
 then i make 3monts periodic statcistic)
 
can be this statistics after 1-2 milion records much slowly that now when table 
has 10 000 - 50 000  records?
 
haw can i optimize this table for speed up statistic.. (i wont cutting db files 
after month :()

something as lock lines (on which was made statistic) and by next month 
statistic skipping them :)

thanx

Re: [sqlite] How to Embed SQLite in VC++

[alok]

you forget to include defination of sqlite3_open and sqlite3_exec in your
application

if you like i can send you release version of sqlite.lib which you can
easily include in your project


With Regards
Alok Gupta
Visit me at http://alok.bizhat.com

   "I Believe this will Help"

- Original Message -
From: "Mahendra Batra" <[EMAIL PROTECTED]>
To: 
Sent: Friday, April 15, 2005 10:47 AM
Subject: [sqlite] How to Embed SQLite in VC++


> Someone, plz tell me how could i embed SQLite. I included sqlite.h but
getting unresolved errors i.e the definition of functions like
sqlite3_open(..) and sqlite3_exec(..) can not be found.
> Please favor me as soon as possible.
>
> regards,
> Mahendra Batra
>
>
> -
> Do you Yahoo!?
>  Yahoo! Small Business - Try our new resources site!