headers
Marco Bambini | 9 Feb 23:17
Favicon
Gravatar

Explanation

sqlite 3.7.8 on MacOS X

This query works fine:
SELECT rowid, * FROM 'customer' WHERE rowid < 100;

While this query does not return anything:
SELECT rowid, * FROM 'customer' WHERE 'rowid' < 100;

Do you have an explanation?
--
Marco Bambini
http://www.sqlabs.com
http://twitter.com/sqlabs
Permalink | Reply | 1 comment |
headers
rod | 9 Feb 20:15
Picon
Gravatar

Elegant printout of table (with header) via TCL

My apologies if this question should be directed to some other list.

I'm looking for a better way to printout the:
Column Headers,
followed by the table contents -
in comma separated value (csv) format
from a SELECT statement:

So far I have two solutions, neither seems elegant enough.

First solution I add the rowid tag after the *  then check to see if
rowid==1 in the output and if so print the headers first

<snip solution 1>

# the following code should print the headers and the
# results from the SELECT command in CSV format
sql eval {
SELECT *, rowid \
FROM Fxyz_max_min limit 10} row {
# if at first row print headers first
if $row(rowid)==1 {puts  [join $row(*) "\t"] }
# define an EMPTY list	
set b_list {}
foreach col $row(*) {lappend b_list $row($col)}
set b_list [join $b_list "\t"]
#write list
puts   $b_list
}
(Continue reading)

Permalink | Reply | 0 comments |
headers
Scholz Maik (CM-AI/PJ-CF42 | 9 Feb 18:10

Problem and possible Optimiziation with LOOKASIDE buffer

Hi,
I am working on an embedded application using SQLite.

My lookaside configuration:
      sqlite3_db_config(mydbhandle,
                  SQLITE_DBCONFIG_LOOKASIDE,
                  0,
  64,
                  128);

After doing some profiling, I saw that sqlite3DbMallocRaw does not allocate from the lookaside
buffer pool even when the sizes match.
The reason is, that all available lookaside places are used for prepared statements during application
startup.
For my understanding, this should not!

I added a simply possibility for disabling the allocation from the lookaside pool temporally.
This is controlled by my application during static statement preparation
and enabled afterwards using this added db_config call:
sqlite3_db_config((sqlite3*) db, SQLITE_DBCONFIG_DISABLE_LOOKASIDE, 1);

This small change, keeps the lookaside pool mostly unused, and it is available to serve many small
allocations during normal operation.

My Question:
Is there a way for disabling the use of lookaside buffers for prepared statement handles?

Regards

Maik Scholz
(Continue reading)

Permalink | Reply | 0 comments |
headers
Tim Leland | 9 Feb 17:48
Gravatar

Compiling SQLite3 to run on Windows Mobile 6.5

Does anyone have any tips/suggestions for getting sqlite3 to run on windows
mobile 6.5? 

Thanks

Tim Leland

W. Lee Flowers & Co.

127 E. W Lee Flowers Rd.

Scranton, S.C. 29591

(843)389-2731
Permalink | Reply | 21 comments |
headers
Lavanya Ramanan | 9 Feb 16:40
Picon
Gravatar

Re: Query on Sqlite3 in an Android app

So I can use an excel/csv file as input to the content provider? Where do I
place the file?

And I would also like to know what GUI do people generally use for sqlite
database.

Thanks.
Lavanya

On Fri, Jan 27, 2012 at 1:57 PM, Lavanya Ramanan <lavanyacool27@...>wrote:

>
>
> ---------- Forwarded message ----------
> From: Roger Binns <rogerb@...>
> Date: Thu, Jan 26, 2012 at 12:47 PM
> Subject: Re: [sqlite] Query on Sqlite3 in an Android app
> To: sqlite-users@...
>
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On 26/01/12 09:15, Lavanya Ramanan wrote:
> > I have created my own sqlite file from the command prompt. To access
> > this database in an Android app, I should use content provider - is
> > that what you are saying ?
>
> No.  What I am saying is that if you are providing data to other
> components, code or applications then the natural Android way of doing so
(Continue reading)

Permalink | Reply | 2 comments |
headers
Tim Leland | 9 Feb 15:10
Gravatar

.output Question

Im using sqlite3.exe to convert my database to .csv file. Here are the
commands Im passing to sqlite3.

.separator "," 

.output text.csv 

select * from grocery;

.quit

How can I get .output txt.csv to be %filename%.csv ????

thanks

Tim Leland

W. Lee Flowers & Co.

127 E. W Lee Flowers Rd.

Scranton, S.C. 29591

(843)389-2731
Permalink | Reply | 3 comments |
headers
Rob Richardson | 8 Feb 14:45
Picon
Gravatar

Can I check the auto-vacuum setting on an SQLite database?

My deepest apologies for forgetting to change the subject line in my
last post, and thus accidentally hijacking a thread.  Here is the
message again, this time with the correct subject.

On Wed, Feb 8, 2012 at 8:44 AM, Rob Richardson
<cedriccicada@...> wrote:
> Greetings!
>
> Is there a way to verify the auto-vacuum state of an SQLite database?
> I am hoping that auto-vacuum will replace the need for periodic VACUUM
> calls.  If the files are getting too big, I need to be able to verify
> the auto-vacuum setting before trying to come with some other scheme
> to prevent unbridled file growth.
>
> Thank you very much!
>
> RobR

--

-- 
Please do not copy or forward this message or any attachments without
my permission.  Remember, asking permission is a great way to get me
to visit your site!
Permalink | Reply | 2 comments |
headers
NOCaut2012 | 9 Feb 13:26
Favicon
Gravatar

sqlite3_close() blocked data base file


Hi. maybe it is  bug  sqlite I'm not sure exactly. and  decided to CREATE
POST.

for unknown reasons sqlite3_close() == SQLITE_BUSY, sqlite blocked data base
file. I find on the forum next code:

void CSQLiteDB::Close() 
{ 
    if(m_db) 
    { 
        sqlite3 *db = m_db; 
        m_db = NULL; 
        int rc = sqlite3_close(db); 
        while( rc == SQLITE_BUSY) 
        { 
            // set rc to something that will exit the while loop 
            rc = SQLITE_OK; 
            sqlite3_stmt * stmt = sqlite3_next_stmt(db, NULL); 
            if(stmt != NULL) 
            { 
                rc = sqlite3_finalize(stmt); 
                if(rc == SQLITE_OK) 
                { 
                    rc = sqlite3_close(db); 
                } 
            } 
        } 
    } 
}
(Continue reading)

Permalink | Reply | 0 comments |
headers
Jaco Breitenbach | 9 Feb 11:45
Picon
Gravatar

Page cache block size problem

Dear experts,

In my application performance is of critical importance.  I've chosen to
preallocate a large contiguous block of memory for the page cache.

I make use of the following function to configure the page cache:
sqlite3_config(SQLITE_CONFIG_PAGECACHE, void*, int sz, int N);

The documentation for this function says the following:
*This option specifies a static memory buffer that SQLite can use for the
database page cache with the default page cache implementation. There are
three arguments to this option: A pointer to 8-byte aligned memory, the
size of each page buffer (sz), and the number of pages (N). The sz argument
should be the size of the largest database page (a power of two between 512
and 32768) plus a little extra for each page header. The page header size
is 20 to 40 bytes depending on the host architecture. It is harmless, apart
from the wasted memory, to make sz a little too large.*

Now, first of all, with my default database block size of 1024, the 20 to
40 bytes documented for the cache header size is wrong.  On the Linux and
HP-UX Itanium systems where I've tested this with SQLite 3.7.8, the cache
header size was 248 bytes.  This could be seen in the output from
sqlite3_status(SQLITE_STATUS_PAGECACHE_SIZE) which returned a value of
about 1272 bytes.  (1272 - 1024 = 248).  This means the page cache header
size is already 25% of the size of the actual data block, or put
differently, 20% of the preallocated cache buffer is 'lost' to the header
component.  With a cache buffer of 5 GB, this means a total of 1 GB for the
header meta data only.

SQLite is a little quirky in the way that even though I may allocate a
(Continue reading)

Permalink | Reply | 1 comment |
headers
bhaskarReddy | 9 Feb 11:21
Picon

sqlite3_step getting core dumbed.


HI Friends, 

          I dont know why the sqlite3_step getting core dumped. 

 #include <stdio.h>
 #include <stdlib.h>
 #include "sqlitedb1.h"
 #include <string.h>
 #include <sqlite3.h>
 #include <sys/types.h>
 #include <sys/stat.h>
 #include <fcntl.h>

 int db_retrieve_row(char *db_name, char *table_name, int num_of_keys, char
* col_names[],column_value_t * col_values);

int main(void)
{
      column_type_t enm[2];
   //   int retVal;
      char *col_name[3];
      char *db_name = "LR6002.db";
      char *table_name = "ONTTable";
      column_value_t col_val[3];

      enm[0] = COLUMN_TYPE_INT;   // slotId
      enm[1] = COLUMN_TYPE_STRING;

      col_val[0].number = 1;
(Continue reading)

Permalink | Reply | 7 comments |
headers
Steinar Midtskogen | 8 Feb 23:08
Favicon
Gravatar

Inserts get slower and slower

Hello

I'm having trouble with one table in my database.

When I build my database from scratch using millions of inserts, one
table causes problems.  Inserts get slower and slower.  I have about
830,000 inserts for that table.  It gets to 300,000 pretty fast, but
then it gets slower and slower, and eventually it will only do a few
inserts per second, and I then I have to kill sqlite3 as it will run
for hours if not days.  The -echo option reveals that it gets slower
and slower.  sqlite3 runs at 100% CPU.

I create other similar tables with 830,000 inserts the same way, but
inserts into them don't slow down.

The table in question is:

CREATE TABLE Voksenlia2 (
 temp_in REAL,
 pressure REAL,
 rh_in REAL,
 temp_in_2 REAL,
 temp_in_3 REAL,
 temp_in_4 REAL,
 temp_in_5 REAL,
 temp_ground_0cm REAL,
 temp_ground_10cm REAL,
 temp_ground_20cm REAL,
 temp_ground_50cm REAL,
 radiation INTEGER,
(Continue reading)

Permalink | Reply | 9 comments |

Gmane