Re: [sqlite] Not getting the speed I think is possoble. Basic select statment slow?
Ok I hadn't toyed with those. Just tried it, the speed for queries hasn't increased much, but a little However, I started getting hard drive thrashes for 10+ seconds from time to time. Will investigate the cahce further to see what suits this app > Did you try increasing the page cache size. Your data set is very big. > > pragma page_cache = 2; > > This should at least improve the speed for queries.
Re: [sqlite] Not getting the speed I think is possoble. Basic select statment slow?
Did you try increasing the page cache size. Your data set is very big. pragma page_cache = 2; This should at least improve the speed for queries. Clive "Chris Schirlinger" <[EMAIL PROTECTED]> on 02-02-2005 09:07:49 Please respond to [EMAIL PROTECTED] To: sqlite-users@sqlite.org cc:(bcc: clive/Emultek) Subject: Re: [sqlite] Not getting the speed I think is possoble. Basic select statment slow? > What speed were you expecting? > Are you comparing it to another database? If so what are the results for that > database? Anything better than what we got. The results are the worst we have gotton from any DB or any self rolled data system (Jet is better, het shouldn't be better) After more tests, it is dipping to 10 records per second update time. Based on the speed showen on the web site, I was expecting to at least get 1000 records a second updating and somewhere above 10k when selecting I am assuming we MUST have mucked something up
Re: [sqlite] Not getting the speed I think is possoble. Basic select statment slow?
> What speed were you expecting? > Are you comparing it to another database? If so what are the results for that > database? Anything better than what we got. The results are the worst we have gotton from any DB or any self rolled data system (Jet is better, het shouldn't be better) After more tests, it is dipping to 10 records per second update time. Based on the speed showen on the web site, I was expecting to at least get 1000 records a second updating and somewhere above 10k when selecting I am assuming we MUST have mucked something up
Re: [sqlite] Not getting the speed I think is possoble. Basic select statment slow?
What speed were you expecting? Are you comparing it to another database? If so what are the results for that database? Clive "Chris Schirlinger" <[EMAIL PROTECTED]> on 02-02-2005 06:00:45 Please respond to [EMAIL PROTECTED] To: sqlite-users@sqlite.org cc:(bcc: clive/Emultek) Subject: [sqlite] Not getting the speed I think is possoble. Basic select statment slow? I've got a 6 million row DB in SQLite 3, but getting... odd results which don't match up with the speed tests I've seen The statement: SELECT * FROM myTable WHERE myKey=1000 takes between 1 second to 4 or 5 on spikes. The returned result set is ~2000 records. I havn't seen more than 2000 recs/second usually less Similarly, the query (A basic APPEND or INSERT) INSERT OR REPLACE INTO myTable (myKey, NumOne, NumTwo, NumThree, NumFour, NumFive, NumSix, NumSeven) VALUES (1000, 1, 2, 3, 4, 5, 6, 7) is doing at MOST about 300 records per second and at worst 100 a second. I have about 3000 inserts/updates all wrapped inside a single Transaction unless doing a complete population of the DB in which case it is batched but still all wrapped in transactions The schema is VERY basic: CREATE TABLE [myTable] ( [myKey] [bigint] NULL , [NumOne] [int] NULL , [NumTwo] [real] NULL , [NumThree] [real] NULL , [NumFour] [real] NULL , [NumFive] [real] NULL , [NumSix] [float] NULL , [NumSeven] [float] NULL ); CREATE INDEX myMyIndex ON myTable (myKey); CREATE UNIQUE INDEX [pkPrimary] ON [myTable] ([myKey], [NumOne]); CREATE INDEX myNumOneIndex ON myTable (NumOne); Now initially I didn't have anything except the UNIQUE index, though adding the second two hasn't made any difference once way or the other Personalyl I'd LOVE for that UNIQUE index to be a clustered index (it was elsewhere) but that just doesn't seem to be an option Any ideas where I am going wrong here? Or are these the numbers I am expected to see? (Note: I am using transactions in case I didn't make that clear, I am also doing this in Delphi using the open source Aducom.nl componants, but at the raw end it seems their code is mostly fairly close to the bare bones of the DLL exported functions. Doesn't seem to be an issue there but who knows)
Re: [sqlite] ? placeholder not allowed in LIMIT or OFFSET clause of SELECT statement?
Eric, No, you're not missing anything. I asked this same question about a month ago. Dr. Hipp replied that argument substitution is not allowed in those cases, because it's allowed only where any of the datatypes allowed for substitution would be legal (blob, int, string, null). As limit and offset take only integers, the prepared statement parser wouldn't know ahead of time that the syntax was correct. While I understand that argument, I, too, think this is unfortunate. I wonder if this could be relaxed such the arguments would simply be coerced at execution time into integer, no matter what the actual argument type. -jdb On Feb 1, 2005, at 8:41 PM, Eric Scouten wrote: When I attempt to prepare the following statement using sqlite3_prepare: SELECT id FROM testEntity LIMIT 5 OFFSET ?; I get the following error back from SQLite: near "?": syntax error Is this really not allowed? If so, that seems a bit odd to me. This seems like a classic use-case for prepared statements (re-running the same query with different offsets). Or is there something else I'm missing? -Eric (FWIW, I'm using SQLite 3.0.8 on Mac OS X.)
[sqlite] ? placeholder not allowed in LIMIT or OFFSET clause of SELECT statement?
When I attempt to prepare the following statement using sqlite3_prepare: SELECT id FROM testEntity LIMIT 5 OFFSET ?; I get the following error back from SQLite: near "?": syntax error Is this really not allowed? If so, that seems a bit odd to me. This seems like a classic use-case for prepared statements (re-running the same query with different offsets). Or is there something else I'm missing? -Eric (FWIW, I'm using SQLite 3.0.8 on Mac OS X.)
[sqlite] Not getting the speed I think is possoble. Basic select statment slow?
I've got a 6 million row DB in SQLite 3, but getting... odd results which don't match up with the speed tests I've seen The statement: SELECT * FROM myTable WHERE myKey=1000 takes between 1 second to 4 or 5 on spikes. The returned result set is ~2000 records. I havn't seen more than 2000 recs/second usually less Similarly, the query (A basic APPEND or INSERT) INSERT OR REPLACE INTO myTable (myKey, NumOne, NumTwo, NumThree, NumFour, NumFive, NumSix, NumSeven) VALUES (1000, 1, 2, 3, 4, 5, 6, 7) is doing at MOST about 300 records per second and at worst 100 a second. I have about 3000 inserts/updates all wrapped inside a single Transaction unless doing a complete population of the DB in which case it is batched but still all wrapped in transactions The schema is VERY basic: CREATE TABLE [myTable] ( [myKey] [bigint] NULL , [NumOne] [int] NULL , [NumTwo] [real] NULL , [NumThree] [real] NULL , [NumFour] [real] NULL , [NumFive] [real] NULL , [NumSix] [float] NULL , [NumSeven] [float] NULL ); CREATE INDEX myMyIndex ON myTable (myKey); CREATE UNIQUE INDEX [pkPrimary] ON [myTable] ([myKey], [NumOne]); CREATE INDEX myNumOneIndex ON myTable (NumOne); Now initially I didn't have anything except the UNIQUE index, though adding the second two hasn't made any difference once way or the other Personalyl I'd LOVE for that UNIQUE index to be a clustered index (it was elsewhere) but that just doesn't seem to be an option Any ideas where I am going wrong here? Or are these the numbers I am expected to see? (Note: I am using transactions in case I didn't make that clear, I am also doing this in Delphi using the open source Aducom.nl componants, but at the raw end it seems their code is mostly fairly close to the bare bones of the DLL exported functions. Doesn't seem to be an issue there but who knows)
Re: [sqlite] bogus output for strftime('%s', 'now')
nope. that doesnt work either. i got no problem
outputing other recoard. but got problem with
strftime('%s', 'now')
--- Jeff Thompson <[EMAIL PROTECTED]> wrote:
> On Tue, 1 Feb 2005 06:51:49 -0800 (PST), teoh
> <[EMAIL PROTECTED]> wrote:
> >
> > sqlite3::reader reader=con.executereader("select *
> > from each_transaction;");
> > while(reader.read())
> > {cout << reader.getcolname(0) << ": " <<
> > reader.getstring(0) << endl; }
> >
> > I get output like this:
> >
> > datetime: á " <- invalid
> >
>
> This is a guess since I'm not familiar with
> sql3_plus, but it looks
> like you may be printing the pointer to the
> character string instead
> of the string itself... If reader.getstring(0)
> returns a std::string,
> try the following:
>
> cout << reader.getcolname(0) << ": " <<
> reader.getstring(0).c_str() << endl;
>
> --
> Jeff Thompson
> [EMAIL PROTECTED]
>
__
Do you Yahoo!?
Take Yahoo! Mail with you! Get it on your mobile phone.
http://mobile.yahoo.com/maildemo
[sqlite] Help with SQlite locking in version 3.08
Hi..
We had a question regarding the lock data structures in the file
os_unix.c. The data structure is the lockInfo structure. The fields in
the lock info structure are
struct lockInfo {
struct lockKey key; /* The lookup key */
int cnt; /* Number of SHARED locks held */
int locktype;/* One of SHARED_LOCK, RESERVED_LOCK etc. */
int nRef;/* Number of pointers to this structure */
};
The lockKey structure is defined as follows
struct lockKey {
dev_t dev; /* Device number */
ino_t ino; /* Inode number */
#ifdef SQLITE_UNIX_THREADS
pthread_t tid; /* Thread ID or zero if threads cannot override each other */
#endif
};
The lockKey data structure includes the thread ID. This results in a
per thread lock info structure on Linux . The comment at the head of
the file states that the thread id was included because a close on a
fd in a thread, in Linux, will result in the locks on all the other
threads (of the same process) being released. The openCnt data
structure seems to track the number of opens as well as locks held for
a given file. So we were puzzled with regards to the function of the
tid in the close issue. Can someone please explain how the purpose of
the "tid" field in the lockKey structure and its relation to the close
issue?
Thanks a lot for the assistance,
Regards,
Sho.
Re: [sqlite] foreign keys? (sqlite3)
* Griggs, Donald <[EMAIL PROTECTED]> [0246 18:46]: > > > -Original Message- > > From: Dick Davies [mailto:[EMAIL PROTECTED] > > Sent: Tuesday, February 01, 2005 3:52 PM > > To: SQLite > > Subject: [sqlite] foreign keys? (sqlite3) > > > > > > The docs seem to say sqlite3 does'nt support foreign keys, is > > that correct? > You may find Cody Pisto's utility useful. > http://www.sqlite.org/contrib > It creates sqlite triggers to enforce foreign keys. Ah, that's what I thought - my app can handle the constraints, it's really just the 'on cascade delete' feature I could do with the db handling. I found this earlier: http://www.justatheory.com/computers/databases/sqlite/ which seems to point out the voodoo needed. Thanks for the confirmation(s). -- 'Sweet Zombie Jesus!' -- Prof. Farnsworth Rasputin :: Jack of All Trades - Master of Nuns
[sqlite] network filesystem + multiple readers question
http://www.sqlite.org/faq.html#q7 states, "You should avoid putting SQLite database files on NFS if multiple processes might try to access the file at the same time." Is that recomendation only intended for cases where one of the processes is a "writer"? Should I avoid the case where there are only multiple "reader" processes accessing a .db file from a network filesystem? The "reader" processes only open .db files, exectue SELECT commands, and close .db files. Thank you for your time. Mike - Do you Yahoo!? Yahoo! Search presents - Jib Jab's 'Second Term'
Re: [sqlite] bogus output for strftime('%s', 'now')
> create table each_transaction(datetime int);
> insert into each_transaction values( datetime('%s', 'now'));
Perhaps you should say
insert into each_transaction values( strftime('%s', 'now'));
?
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions
e
> this is how I create table and insert the
> strftime('%s', 'now'). But when I did query with
> sql3_plus.
> sqlite3::reader reader=con.executereader("select *
> from each_transaction;");
> while(reader.read())
> {cout << reader.getcolname(0) << ": " <<
> reader.getstring(0) << endl; }
> I get output like this:
> datetime: á " <- invalid
> Any idea? coz If I change to
> datetime('now','localtime');
> everything is ok. I only cannot use strftime('%s',
> 'now') for "insert" statement.
> I tried out the pre-compiled command-prompt sqlite but
> doesn't faced with this problem. The sql_lite that i'm
> using to do the above test was static lib compiled by
> me using dev-cpp. I even tried out linking directly
> with .dll but the result still the same, unexpected
> output.
> thakn you for reading and clarifying my mistake.
> __
> Do you Yahoo!?
> Yahoo! Mail - Helps protect you from nasty viruses.
> http://promotions.yahoo.com/new_mail
RE: [sqlite] foreign keys? (sqlite3)
> -Original Message- > From: Dick Davies [mailto:[EMAIL PROTECTED] > Sent: Tuesday, February 01, 2005 3:52 PM > To: SQLite > Subject: [sqlite] foreign keys? (sqlite3) > > > The docs seem to say sqlite3 does'nt support foreign keys, is > that correct? > > If so, I don't understand what [it actually does]. > > ---8<- You may find Cody Pisto's utility useful. http://www.sqlite.org/contrib It creates sqlite triggers to enforce foreign keys. Donald Griggs Desk: 803-735-7834 Opinions are not necessarily those of Misys Healthcare Systems nor its board of directors. --
Re: [sqlite] bogus output for strftime('%s', 'now') ermm..
Did you try this?
create table each_transaction(datetime text);
insert into each_transaction(datetime) values(datetime('now'));
D:\temp\convention>sqlite3 test.db
SQLite version 3.0.8
Enter ".help" for instructions
sqlite> create table each_transaction(datetime text);
sqlite> insert into each_transaction(datetime) values(datetime('now'));
sqlite> select * from each_transaction;
2005-02-01 18:30:26
sqlite>
--- teoh <[EMAIL PROTECTED]> wrote:
> nope. reader.getint32(0) will return 0
>
> i tried changed datetime to "text" and did the query
> by getsring(0) and i get same invalid output
>
> output:
> á "
>
> i still cant use strftime('%s', 'now') .
>
>
>
>
>
>
>
> --- [EMAIL PROTECTED] wrote:
>
> > teoh <[EMAIL PROTECTED]> writes:
> >
> > > create table each_transaction(datetime int);
> > >
> > > insert into each_transaction values(
> > datetime('%s',
> > > 'now'));
> > >
> > > this is how I create table and insert the
> > > strftime('%s', 'now'). But when I did query with
> > > sql3_plus.
> > >
> > > sqlite3::reader reader=con.executereader("select *
> > > from each_transaction;");
> > > while(reader.read())
> > > {cout << reader.getcolname(0) << ": " <<
> > > reader.getstring(0) << endl; }
> > >
> > >
> > > I get output like this:
> > >
> > > datetime: á " <- invalid
> > >
> > >
> > > Any idea? coz If I change to
> > > datetime('now','localtime');
> > > everything is ok. I only cannot use strftime('%s',
> > > 'now') for "insert" statement.
> >
> > It appears that you are getting back an integer but
> > not displaying it as an
> > integer. You have declared datetime as an integer
> > in your CREATE TABLE
> > statement. (Maybe you wanted to declare it as
> > TEXT?) I haven't used C++ with
> > sqlite. Can you do reader.getint(0) instead of
> > reader.getstring(0)?
> >
> > Derrell
> >
>
>
>
>
>
> __
> Do you Yahoo!?
> Yahoo! Mail - You care about security. So do we.
> http://promotions.yahoo.com/new_mail
>
=
-
"Lord Tarlington gazed upon the crazed Egyptian hieroglyphics on the walls of
the ancient tomb of the petrified pharaoh, he vowed there would be no curse on
him like on that other Lord, unless you count his marriage to Lady Tarlington
who, when the lost treasure was found, will be dumped faster than that basket
in the bulrushes."
Melissa Rhodes
-
The Castles of Dereth Calendar: a tour of the art and architecture of Asheron's
Call
http://www.lulu.com/content/77264
__
Do you Yahoo!?
Yahoo! Mail - now with 250MB free storage. Learn more.
http://info.mail.yahoo.com/mail_250
RE: [sqlite] foreign keys? (sqlite3)
Sqlite parser DOES parse foreign key constraints. Try this: create table t1( id integer, id1 integer, id2 integer, id3 integer, foreign key (id1,id2) references anotherTable(id,id1), foreign key (id3) references somethingElse(id) ); pragma foreign_key_list(t1); However, this does not mean that it actually DOES anything with this info, it is just there for you to act, not for the engine (yet) > -Original Message- > From: Dick Davies [mailto:[EMAIL PROTECTED] > Sent: Tuesday, February 01, 2005 3:52 PM > To: SQLite > Subject: [sqlite] foreign keys? (sqlite3) > > > The docs seem to say sqlite3 does'nt support foreign keys, is > that correct? > > If so, I don't understand what > > ---8<- > --- > # > PRAGMA foreign_key_list(table-name); > > For each foreign key that references a column in the argument > table, invoke the callback function with information about > that foreign key. > The callback function will be invoked once for each column in > each foreign key. > ---8<- > --- > > actually does? > > -- > 'The pie is ready. You guys like swarms of things, right?' > -- Bender > Rasputin :: Jack of All Trades - Master of Nuns > > >
Re: [sqlite] Cursors
[EMAIL PROTECTED] said: > I have run into problems trying to use SQLite via perl DBA. > According to the SQLite.org web site, DECLARE CURSOR should be supported > (actually, it is not in the list of the only SQL not supported). > I get a error saying "error near DECLARE". I have tried to declare cursor > in many ways but always get the same error. > If "cursro" is not supported, does anybody have a suggestion on how to > achieve the same end? The prepared statement API gives you most of the features of cursors as far as progressing through the database is concerned. Not too swell for supporting writes in the middle of your queries though. Clay -- Lazarus Notes from Lazarus Internet Development http://www.lazarusid.com/notes/ Articles, Reviews and Commentary on web development
Re: RE(1): [sqlite] SQLite Advocacy
>>yep, but again who cares, 99% of the world is on windows<< He does. Since I have no idea what the requirements of this project are, and he does, hopefully the points he wrote are relevant. If one of the requirements for this project are that it run on BeOS on a 400 mhz processor with half a meg of ram, then who cares what the rest of the world does? You're not advocating SQLite in gerneral, you're advocating it for this _specific_ project and what the rest of the world runs(which certainly isn't 99%) is completely irrelevant. Ultimately, since none of us knows what the requirements for the project are, we can only suggest some things that might help advocate for his specific needs. If management has specifically stated that they want something that runs on multiple platforms, then point out that SQL Server won't meet that requirement. If you have no idea if this would be a relevant "feature", then find out because then you're just blindly going around not knowing anything and you're _doomed_, buddy. On Mon, 31 Jan 2005 11:31:06 -0600, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > my comments below. > > > - Original message - > From: "Downey, Shawn" <[EMAIL PROTECTED]> > Date: 1/31/2005 11:11:27 AM > Subject: RE: [sqlite] SQLite Advocacy > > > Thanks everyone for there input. See below for the arguments I've > > compiled so far. Please let me know if I am incorrect on any of these > > items. Do we have any speed comparisons between SQLite and SQL Server > > 7? > > no point, they are both fast and index for queries the same, n time searches. > > > > I do understand we are talking Apples and Oranges here. Never the less, > > I need to do the comparison! If not, I might as well go get an MS > > certification (not that there's anything wrong with that... :-). > > > > SQLite Advocacy > > > > 1.SQLite has "Zero Installation". This means that all that is > > needed to run SQLite is to copy the database on the machine along with > > the program which accesses it. There is no need to adjust the registry > > for SQLite. Installing SQL Server 7 on the other hand is complex and > > sometimes problematic. Even moving SQL Server 7 to a different > > directory on the same machine is difficult. > > SQL server 7 MSDE is also this way. They have 3 versions of SQL Server > > > > 2.An SQLite database is exactly one file. SQL Server 7 has many > > files for each table in the database. > > > > 3.SQLite is much faster than SQL Server 7. > > not on inserts > > > > 4.SQLite is easy to administrate. All that is needed is to copy > > one database file. SQL Server 7 is very difficult for the novice end > > user to backup or to deploy database updates in the field. SQL Server 7 > > is best used for a centralized database (rather than a distributed > > database) since it is so hard to deploy data updates. > > > true > > 5.SQLite has an active community with good people which help each > > other solve rather complex programming problems. > > > YEs, everyone buy myself! : - ) > > 6.SQL Server 7 is obsolete and the database is not upward > > compatible. When Microsoft discontinues support for SQL Server 7, it > > may no longer work on the latest Windows platform. SQL Server can no > > longer be purchased directly from Microsoft. > true, but you can dump the databsae and convert it easily to 2000 or later > Yukon, tools exist for this > > > > 7.SQL server 7 is very expensive. Development tools and > > deployment license costs for SQL Server 7 are very high. SQLite is free > > and can be developed using free compilers or Microsoft compilers as > > desired. > SQL server 7 MSDE is free > > > > 8.SQL Server 7 requires powerful hardware to run. SQLite runs on > > cheaper hardware. > this is true, lots of memory > > > > 9.SQLite has a small memory footprint and SQL Server 7 is a very > > very large memory footprint. > > > big time, Sqlite is a stud here > > 10. SQLite runs on almost ever operating systems. SQL Server runs > > on MS Windows exclusively. > > > yep, but again who cares, 99% of the world is on windows > > 11. By using SQLite you eliminate a possible source of virus > > infection. SQL server has been targeted in at least one major virus > > outbreak. > not always true, microsoft had a bug in their network strnsport distributed > layer "slammer" but they patched it, SQlite does not even have a distributed > network layer, comparison is not valid here. > > > > 12. Changes can be made to the source code for SQLite by the end > > user. In this way, any bugs in SQLite can be fixed quickly. If > > something was wrong with SQL Server 7, Microsoft is unlikely to address > > it as quickly. Microsoft has been known to sit on patches for years > > because it's inconvenient or costly to roll them out. > yeah, but Microsoft also backs their products with millions of testing hours. > I have to admit I used to work in Redmon on VC++. I saw all of it
Re: [sqlite] bogus output for strftime('%s', 'now')
On Tue, 1 Feb 2005 06:51:49 -0800 (PST), teoh <[EMAIL PROTECTED]> wrote:
>
> sqlite3::reader reader=con.executereader("select *
> from each_transaction;");
> while(reader.read())
> {cout << reader.getcolname(0) << ": " <<
> reader.getstring(0) << endl; }
>
> I get output like this:
>
> datetime: á " <- invalid
>
This is a guess since I'm not familiar with sql3_plus, but it looks
like you may be printing the pointer to the character string instead
of the string itself... If reader.getstring(0) returns a std::string,
try the following:
cout << reader.getcolname(0) << ": " << reader.getstring(0).c_str() << endl;
--
Jeff Thompson
[EMAIL PROTECTED]
Re: [sqlite] bogus output for strftime('%s', 'now') ermm..
nope. reader.getint32(0) will return 0
i tried changed datetime to "text" and did the query
by getsring(0) and i get same invalid output
output:
á "
i still cant use strftime('%s', 'now') .
--- [EMAIL PROTECTED] wrote:
> teoh <[EMAIL PROTECTED]> writes:
>
> > create table each_transaction(datetime int);
> >
> > insert into each_transaction values(
> datetime('%s',
> > 'now'));
> >
> > this is how I create table and insert the
> > strftime('%s', 'now'). But when I did query with
> > sql3_plus.
> >
> > sqlite3::reader reader=con.executereader("select *
> > from each_transaction;");
> > while(reader.read())
> > {cout << reader.getcolname(0) << ": " <<
> > reader.getstring(0) << endl; }
> >
> >
> > I get output like this:
> >
> > datetime: á " <- invalid
> >
> >
> > Any idea? coz If I change to
> > datetime('now','localtime');
> > everything is ok. I only cannot use strftime('%s',
> > 'now') for "insert" statement.
>
> It appears that you are getting back an integer but
> not displaying it as an
> integer. You have declared datetime as an integer
> in your CREATE TABLE
> statement. (Maybe you wanted to declare it as
> TEXT?) I haven't used C++ with
> sqlite. Can you do reader.getint(0) instead of
> reader.getstring(0)?
>
> Derrell
>
__
Do you Yahoo!?
Yahoo! Mail - You care about security. So do we.
http://promotions.yahoo.com/new_mail
Re: [sqlite] bogus output for strftime('%s', 'now')
teoh <[EMAIL PROTECTED]> writes:
> create table each_transaction(datetime int);
>
> insert into each_transaction values( datetime('%s',
> 'now'));
>
> this is how I create table and insert the
> strftime('%s', 'now'). But when I did query with
> sql3_plus.
>
> sqlite3::reader reader=con.executereader("select *
> from each_transaction;");
> while(reader.read())
> {cout << reader.getcolname(0) << ": " <<
> reader.getstring(0) << endl; }
>
>
> I get output like this:
>
> datetime: á " <- invalid
>
>
> Any idea? coz If I change to
> datetime('now','localtime');
> everything is ok. I only cannot use strftime('%s',
> 'now') for "insert" statement.
It appears that you are getting back an integer but not displaying it as an
integer. You have declared datetime as an integer in your CREATE TABLE
statement. (Maybe you wanted to declare it as TEXT?) I haven't used C++ with
sqlite. Can you do reader.getint(0) instead of reader.getstring(0)?
Derrell
Re: [sqlite] bogus output for strftime('%s', 'now') .errm..
what i trying to say is, i cant use strftime('%s',
'now') but also to use other function like
datetime('now') . This is because the output of select
will be invalid. if i use datetime('now') . the output
is 2005-02-01 15:44:23 . How does the "select"
statement should look like if i want it to show only
records before 2005-01-01 0:0:00 ? i believe that is
impossible if I unable to use strftime('%s', 'now') .
please comment. thank you.
--- Jay <[EMAIL PROTECTED]> wrote:
>
> Does this fix it?
> insert into each_transaction(datetime)
> values(datetime('now'));
>
>
> --- teoh <[EMAIL PROTECTED]> wrote:
>
> >
> > create table each_transaction(datetime int);
> >
> > insert into each_transaction values(
> datetime('%s',
> > 'now'));
> >
> > this is how I create table and insert the
> > strftime('%s', 'now'). But when I did query with
> > sql3_plus.
> >
> > sqlite3::reader reader=con.executereader("select *
> > from each_transaction;");
> > while(reader.read())
> > {cout << reader.getcolname(0) << ": " <<
> > reader.getstring(0) << endl; }
> >
> >
> > I get output like this:
> >
> > datetime: á " <- invalid
> >
> >
> > Any idea? coz If I change to
> > datetime('now','localtime');
> > everything is ok. I only cannot use strftime('%s',
> > 'now') for "insert" statement.
> >
> > I tried out the pre-compiled command-prompt sqlite
> but
> > doesn't faced with this problem. The sql_lite that
> i'm
> > using to do the above test was static lib compiled
> by
> > me using dev-cpp. I even tried out linking
> directly
> > with .dll but the result still the same,
> unexpected
> > output.
> >
> > thakn you for reading and clarifying my mistake.
> >
> >
> >
> >
> >
> >
> > __
> > Do you Yahoo!?
> > Yahoo! Mail - Helps protect you from nasty
> viruses.
> > http://promotions.yahoo.com/new_mail
> >
>
>
> =
>
> -
>
> "Lord Tarlington gazed upon the crazed Egyptian
> hieroglyphics on the walls of the ancient tomb of
> the petrified pharaoh, he vowed there would be no
> curse on him like on that other Lord, unless you
> count his marriage to Lady Tarlington who, when the
> lost treasure was found, will be dumped faster than
> that basket in the bulrushes."
> Melissa Rhodes
> -
>
> The Castles of Dereth Calendar: a tour of the art
> and architecture of Asheron's Call
> http://www.lulu.com/content/77264
>
>
>
> __
> Do you Yahoo!?
> Meet the all-new My Yahoo! - Try it today!
> http://my.yahoo.com
>
>
>
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
Re: [sqlite] Compacting a database
Hi ! An interesting enchantment may be (if anyone development that) an "VACUUM INFILE" method. That is (when many pages are unuseable (free) in db) a "pragma signed" transaction. If flag is -1, the db engine is use the last, and nearly to last pages. When flag is 1, the engine is use first, and nearly to first pages. When many pages are deleted, we are use -1, and relocate datas. When it is finished, we are use 1, and relocate page nearly to file begin position. If we have enough pages, the file size is not changed until this progress. Then the empty pages are in the end of file, we can truncate that. See that: xxx xxx x x x xxx x | ->x | x<- | truncate x| This is like an unfragmenting method in windows file systems... :-) But that is db engine dependent, so it is my idea now... :-) Sorry for disturbing: ft Dan Kennedy wrote: Version 3.1 supports "pragma auto_vacuum" as an alternative. 3.1 is currently still in alpha stage though. http://www.sqlite.org/pragma.html#modify
Re: [sqlite] bogus output for strftime('%s', 'now')
Does this fix it?
insert into each_transaction(datetime) values(datetime('now'));
--- teoh <[EMAIL PROTECTED]> wrote:
>
> create table each_transaction(datetime int);
>
> insert into each_transaction values( datetime('%s',
> 'now'));
>
> this is how I create table and insert the
> strftime('%s', 'now'). But when I did query with
> sql3_plus.
>
> sqlite3::reader reader=con.executereader("select *
> from each_transaction;");
> while(reader.read())
> {cout << reader.getcolname(0) << ": " <<
> reader.getstring(0) << endl; }
>
>
> I get output like this:
>
> datetime: á " <- invalid
>
>
> Any idea? coz If I change to
> datetime('now','localtime');
> everything is ok. I only cannot use strftime('%s',
> 'now') for "insert" statement.
>
> I tried out the pre-compiled command-prompt sqlite but
> doesn't faced with this problem. The sql_lite that i'm
> using to do the above test was static lib compiled by
> me using dev-cpp. I even tried out linking directly
> with .dll but the result still the same, unexpected
> output.
>
> thakn you for reading and clarifying my mistake.
>
>
>
>
>
>
> __
> Do you Yahoo!?
> Yahoo! Mail - Helps protect you from nasty viruses.
> http://promotions.yahoo.com/new_mail
>
=
-
"Lord Tarlington gazed upon the crazed Egyptian hieroglyphics on the walls of
the ancient tomb of the petrified pharaoh, he vowed there would be no curse on
him like on that other Lord, unless you count his marriage to Lady Tarlington
who, when the lost treasure was found, will be dumped faster than that basket
in the bulrushes."
Melissa Rhodes
-
The Castles of Dereth Calendar: a tour of the art and architecture of Asheron's
Call
http://www.lulu.com/content/77264
__
Do you Yahoo!?
Meet the all-new My Yahoo! - Try it today!
http://my.yahoo.com
[sqlite] bogus output for strftime('%s', 'now')
create table each_transaction(datetime int);
insert into each_transaction values( datetime('%s',
'now'));
this is how I create table and insert the
strftime('%s', 'now'). But when I did query with
sql3_plus.
sqlite3::reader reader=con.executereader("select *
from each_transaction;");
while(reader.read())
{cout << reader.getcolname(0) << ": " <<
reader.getstring(0) << endl; }
I get output like this:
datetime: á " <- invalid
Any idea? coz If I change to
datetime('now','localtime');
everything is ok. I only cannot use strftime('%s',
'now') for "insert" statement.
I tried out the pre-compiled command-prompt sqlite but
doesn't faced with this problem. The sql_lite that i'm
using to do the above test was static lib compiled by
me using dev-cpp. I even tried out linking directly
with .dll but the result still the same, unexpected
output.
thakn you for reading and clarifying my mistake.
__
Do you Yahoo!?
Yahoo! Mail - Helps protect you from nasty viruses.
http://promotions.yahoo.com/new_mail
Re: [sqlite] Compacting a database
Version 3.1 supports "pragma auto_vacuum" as an alternative. 3.1 is currently still in alpha stage though. http://www.sqlite.org/pragma.html#modify --- [EMAIL PROTECTED] wrote: > > > > Thanks. > I am not sure this is very useful, because it copies the database, which > requires that there be enough file space for a copy. > It would be nice if there was something that worked by truncating the file. > > Clive > > > > > > Dick Davies <[EMAIL PROTECTED]> on 01-02-2005 13:40:13 > > Please respond to Dick Davies <[EMAIL PROTECTED]> > > To: sqlite-users@sqlite.org > cc:(bcc: clive/Emultek) > > Subject: Re: [sqlite] Compacting a database > > > > * [EMAIL PROTECTED] <[EMAIL PROTECTED]> [0212 11:12]: > > > > > > > > Is there a way to compact a database after removing data? > > vacuum ? > > -- > 'One cannot make an omelette without breaking eggs -- > but it is amazing > how many eggs one can break without making a decent omelette.' > -- Charles P. Issawi > Rasputin :: Jack of All Trades - Master of Nuns > > > > > > > __ Do you Yahoo!? Yahoo! Mail - You care about security. So do we. http://promotions.yahoo.com/new_mail
[sqlite] Cursors
Hello list!
I have run into problems trying to use SQLite via perl DBA.
According to the SQLite.org web site, DECLARE CURSOR should be supported
(actually, it is not in the list of the only SQL not supported).
I get a error saying "error near DECLARE". I have tried to declare cursor
in many ways but always get the same error.
If "cursro" is not supported, does anybody have a suggestion on how to
achieve the same end?
# PERL
##
# DECLARE CURSOR sar_cursor
# AS SELECT * FROM cleanenv ORDER BY server
# FOR UPDATE
$dbh_s->do
(q{
DECLARE CURSOR sar_cursor
FOR SELECT * FROM cleanenv ORDER BY server
});
$dbh_s->do
(q{
OPEN sar_cursor
});
$fetch = $dbh_s->prepare(q{FETCH NEXT FROM sar_cursor});
##
### Fetch rows
##
while ($fetch->execute())
{
Data::Dumper::Dumper($fetch->fetchall_arrayref());
$row = $fetch->fetchrow_arrayref;
### $dbh_s -> do("UPDATE cleanenv SET xxx='???' WHERE CURRENT OF
$fetch->{CursorName}");
last if $fetch->rows() < 10;
}
$dbh_s->do(q{CLOSE sar_cursor });
$dbh_s->do(q{DEALLOCATE sar_cursor });
Regards,
Uriel Carrasquilla
[sqlite] foreign keys? (sqlite3)
The docs seem to say sqlite3 does'nt support foreign keys, is that correct? If so, I don't understand what ---8< # PRAGMA foreign_key_list(table-name); For each foreign key that references a column in the argument table, invoke the callback function with information about that foreign key. The callback function will be invoked once for each column in each foreign key. ---8< actually does? -- 'The pie is ready. You guys like swarms of things, right?' -- Bender Rasputin :: Jack of All Trades - Master of Nuns
Re: [sqlite] Compacting a database
Thanks. I am not sure this is very useful, because it copies the database, which requires that there be enough file space for a copy. It would be nice if there was something that worked by truncating the file. Clive Dick Davies <[EMAIL PROTECTED]> on 01-02-2005 13:40:13 Please respond to Dick Davies <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org cc:(bcc: clive/Emultek) Subject: Re: [sqlite] Compacting a database * [EMAIL PROTECTED] <[EMAIL PROTECTED]> [0212 11:12]: > > > > Is there a way to compact a database after removing data? vacuum ? -- 'One cannot make an omelette without breaking eggs -- but it is amazing how many eggs one can break without making a decent omelette.' -- Charles P. Issawi Rasputin :: Jack of All Trades - Master of Nuns
Re: [sqlite] Compacting a database
* [EMAIL PROTECTED] <[EMAIL PROTECTED]> [0212 11:12]: > > > > Is there a way to compact a database after removing data? vacuum ? -- 'One cannot make an omelette without breaking eggs -- but it is amazing how many eggs one can break without making a decent omelette.' -- Charles P. Issawi Rasputin :: Jack of All Trades - Master of Nuns
[sqlite] Compacting a database
Is there a way to compact a database after removing data? Clive
Re: [sqlite] Delphi wrapper + ISO-8859-2 ordering
--- "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: > Hi ! > > My question is that: I use hungarian (win1250, iso-8859-2) character > set, what containing special accented characters: > a' - á > e' - é > > and etc. > > I want to sort my sql by this collation order. > But I cannot do that, only with outer programme (I use Delphi wrapper > library). > > 1. Can I do that with SQLite ? > 2. Have the SQLite an possibility to a user write an order library > extension ? SQLite supports this. I don't know if the Delphi wrapper does though. See: http://www.sqlite.org/capi3ref.html#sqlite3_create_collation And this: http://www.sqlite.org/datatype3.html In particular section 7. __ Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail
[sqlite] Delphi wrapper + ISO-8859-2 ordering
Hi ! My question is that: I use hungarian (win1250, iso-8859-2) character set, what containing special accented characters: a' - á e' - é and etc. I want to sort my sql by this collation order. But I cannot do that, only with outer programme (I use Delphi wrapper library). 1. Can I do that with SQLite ? 2. Have the SQLite an possibility to a user write an order library extension ? 3. If not, I think, that is a possible enchantment "area". I think, that library (dll or so) is need to contain only two function: function GetOrderName(var Name: array[0..10] of char):boolean;stdcall; begin Name.='ANSI HUN'; Result:=True; end; function CompareAnsiStrings(s1:PChar;s2:PChar):integer;stdcall; begin // end; where result is: - -1 when s1 is bigger - +1 when s2 is bigger - 0 when s1 and s2 are equals This library called by SQL parser (to get name of coll. sequence), and SQL executer (to get order of strings). And the SQL is extended by one "command": select * from anytable order by name order with "ANSI HUN" And the engine is get back the result in the needed order. Now I need to select all, and I need to fetch all item, and I need order them, and I need to store all items index in an array. And I need to get back by array index... Very complex... Uff: ft
Re: [sqlite] SQLite Advocacy
>Not true at all. In fact, from experience, the Linux OS is >>much more full of holes than Windows. It appears most hate >> >> Linux OS is the kernel, the rest is third party. The kernel 2.6.10 is now the latest, that means there were 10 upgrades last three (four ?) years in the serie 2.6.x. And not all of them were security issues. Windows OS, is a huge complex of binaries and libraries, even the Internet Explorer is a part of the OS, as Microsoft stated many times before court. (what does that tell us about the OS-design, if a webbrowser is an unremovable and essential part of the OS) There were many. many security upgrades, even after they promised they were going secure from now on in 2002, they had many new holes. It makes no sense counting security holes in third party software, because that is not the responsibility of the OS-vendor. Normally don't do Microsoft bashing, but I could not resist this time. Bert Verhees
ODP: RE(1): [sqlite] SQLite Advocacy
Hi, > > 10. SQLite runs on almost ever operating systems. SQL Server runs > > on MS Windows exclusively. > > > yep, but again who cares, 99% of the world is on windows This is probably true for desktops, but not for servers. And a lot of people cares. > > 13. Open source and Free Software such as SQLite has proven to be > > more robust and more secure than proprietary software due to open, > > intense, and ongoing peer reviews by the user community. "Security > > through Obscurity" is regarded a fallacy in the cryptographic > > community at large. > > > Not true at all. In fact, from experience, the Linux OS is > much more full of holes than Windows. It appears most hate > Microsoft so thier OS gets the most virus and hackers. All I > can say is we independently did a test with Linux and Windows > we isntalled a default OS and put it on the net without a > firewall. Windows was never hacked, but Linux was hacked in > a day and they took root access to the point where we could > not get back in. > > While I fully agree that "open source always guarantees better security" is a dangerous myth, your statement seems to be really brave. In fact, my experience is definitely the opposite. I've seen people unable to just download SP2 (or other patches) after installing Windows beacuse their box was owned irrecoverably after 15 minutes (no joke) after connecting to the world, many times. But I agree that comparing SQLite and SQL Server is unusual. Regards, Jarek
