Re: [sqlite] concurrent write

[Teg]

Hello Richard,

I set the busy timeout to zero and manage the errors in my code with
delays and retries. What I found is I couldn't pick a busy timeout
value that was always correct.


Friday, November 28, 2008, 8:12:06 PM, you wrote:

RK> Hi Alexey,

RK> Thanks for your link, but it is not really click me too well
RK> I should set this busy_timeout to longer ms than current ?


RK> Richard K 

RK> -Original Message-
RK> From: [EMAIL PROTECTED]
RK> [mailto:[EMAIL PROTECTED] On Behalf Of Alexey Pechnikov
RK> Sent: Friday, November 28, 2008 3:00 PM
RK> To: General Discussion of SQLite Database
RK> Subject: Re: [sqlite] concurrent write

RK> Hello!

RK> В сообщении от Saturday 29 November 2008 01:16:17 Richard Kim написал(а):
>> We have database table, and it is being written, sometimes 30 times a 
>> second.
>> Some of the data succeeded, but some aren't.
>>  
>> How should we handle this sort of lock issue  ?

RK> http://www.sqlite.org/c3ref/busy_timeout.html

RK> Best regards, Alexey.
RK> ___
RK> sqlite-users mailing list
RK> sqlite-users@sqlite.org
RK> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
RK> ___
RK> sqlite-users mailing list
RK> sqlite-users@sqlite.org
RK> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


-- 
Best regards,
 Tegmailto:[EMAIL PROTECTED]

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

Re: [sqlite] concurrent write

[Richard Kim]

Hi Alexey,

Thanks for your link, but it is not really click me too well
I should set this busy_timeout to longer ms than current ?


Richard K 

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Alexey Pechnikov
Sent: Friday, November 28, 2008 3:00 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] concurrent write

Hello!

В сообщении от Saturday 29 November 2008 01:16:17 Richard Kim написал(а):
> We have database table, and it is being written, sometimes 30 times a 
> second.
> Some of the data succeeded, but some aren't.
>  
> How should we handle this sort of lock issue  ?

http://www.sqlite.org/c3ref/busy_timeout.html

Best regards, Alexey.
___
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] concurrent write

[Alexey Pechnikov]

Hello!

В сообщении от Saturday 29 November 2008 01:16:17 Richard Kim написал(а):
> We have database table, and it is being written, sometimes 30 times a
> second.  
> Some of the data succeeded, but some aren't.
>  
> How should we handle this sort of lock issue  ?

http://www.sqlite.org/c3ref/busy_timeout.html

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

[sqlite] concurrent write

[Richard Kim]

Hi,
 
We have database table, and it is being written, sometimes 30 times a
second.  
Some of the data succeeded, but some aren't.
 
How should we handle this sort of lock issue  ?

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

Re: [sqlite] Elementary arithmetic question

[P Kishor]

On 11/28/08, tom_slee <[EMAIL PROTECTED]> wrote:
>
>  I used the Numerical python package and its types don't always behave well
>  with other python variables - I had to insert into sqlite by piecing
>  togethera literal SQL statement rather than binding the variables. So no
>  doubt you are right and the problem is related to the number of digits or
>  the type of the python variable I was using.  I'll try to repeat using more
>  orthodox python types.
>
>  Many thanks,
>
>  Tom
>
>
>  Griggs, Donald-3 wrote:
>  >
>  >
>  > Hi, Tom,
>  >
>  >
>  > Regarding:  "Adding values to att0  (one of the numeric columns) gives
>  > this odd result:"
>  >
>  > I tried this on win xp 32 using the sqlite3.exe command-line interface
>  > and for me it *does* behave as expected.
>  >
>  > I tried it under sqlite 3.5.5 and under 3.6.6.2.
>  >
>  > I wonder if PHP (or something) is having a problem with the larger
>  > number of digits in the result when rowid=2?
>  >
>  > sqlite> select itemid, att0, (att0 + 0.95) dx from map;
>  > 0|0.0290534291416|0.9790534291416
>  > 1|-0.0737001597881|0.8762998402119
>  > 2|-0.957751333714|-0.0077513337145
>  > 3|-0.229464128613|0.720535871387
>  > ___
>  > sqlite-users mailing list
>  > sqlite-users@sqlite.org
>  > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>  >
>  >


There has to be something wrong with either your code or with the
Python interface you are using. It works fine for both via the sqlite3
shell (I am using SQLite v. 3.5.9 on Mac OS X) or via Perl DBI (which
uses sqlite 3.6.1). I get

0|0.0290534291416|0.9790534291416
1|-0.0737001597881|0.8762998402119
2|-0.957751333714|-0.0077513337145
3|-0.229464128613|0.720535871387



-- 
Puneet Kishor http://www.punkish.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Elementary arithmetic question

[tom_slee]

I used the Numerical python package and its types don't always behave well
with other python variables - I had to insert into sqlite by piecing
togethera literal SQL statement rather than binding the variables. So no
doubt you are right and the problem is related to the number of digits or
the type of the python variable I was using.  I'll try to repeat using more
orthodox python types.

Many thanks,

Tom

Griggs, Donald-3 wrote:
> 
> 
> Hi, Tom,
> 
> 
> Regarding:  "Adding values to att0  (one of the numeric columns) gives
> this odd result:"
> 
> I tried this on win xp 32 using the sqlite3.exe command-line interface
> and for me it *does* behave as expected.
> 
> I tried it under sqlite 3.5.5 and under 3.6.6.2.
> 
> I wonder if PHP (or something) is having a problem with the larger
> number of digits in the result when rowid=2?
> 
> sqlite> select itemid, att0, (att0 + 0.95) dx from map;
> 0|0.0290534291416|0.9790534291416
> 1|-0.0737001597881|0.8762998402119
> 2|-0.957751333714|-0.0077513337145
> 3|-0.229464128613|0.720535871387 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Elementary-arithmetic-question-tp20737193p20737894.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Elementary arithmetic question

[Griggs, Donald]

Hi, Tom,


Regarding:  "Adding values to att0  (one of the numeric columns) gives
this odd result:"

I tried this on win xp 32 using the sqlite3.exe command-line interface
and for me it *does* behave as expected.

I tried it under sqlite 3.5.5 and under 3.6.6.2.

I wonder if PHP (or something) is having a problem with the larger
number of digits in the result when rowid=2?

sqlite> select itemid, att0, (att0 + 0.95) dx from map;
0|0.0290534291416|0.9790534291416
1|-0.0737001597881|0.8762998402119
2|-0.957751333714|-0.0077513337145
3|-0.229464128613|0.720535871387 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Elementary arithmetic question

[tom_slee]

This seems basic, so please excuse me if it is a common question.

I have a table defined like this:

CREATE TABLE map ( 
runID INT, type TEXT, itemID int, 
att0 NUMERIC, att1 NUMERIC, PRIMARY KEY ( runID, type, itemID )
);

Here are the first four rows:

1|c|0|0.0290534291416|-0.580828785896
1|c|1|-0.0737001597881|1.31409645081
1|c|2|-0.957751333714|0.65147652
1|c|3|-0.229464128613|0.659206032753

Adding values to att0  (one of the numeric columns) gives this odd result:

sqlite> select itemid, att0, (att0 + 0.95) dx from map;
0|0.0290534291416|
1|-0.0737001597881|
2|-0.957751333714|-0.0077513337145
3|-0.229464128613|
4|-1.05415046215|-0.10415046215
...
7|1.09137058258|2.04137058258

I don't understand why some values of att0 cannot be added to 0.95 and don't
see a pattern (which is why I included row 7. Could someone explain please?

I am using sqlite3 3.5.9 on ubuntu to query the database. I am using python
2.5 to create and enter the numbers.


Tom




-- 
View this message in context: 
http://www.nabble.com/Elementary-arithmetic-question-tp20737193p20737193.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Left Join

[Tommy Anderson]

It's working, Thank you!


Igor Tandetnik wrote:
> 
> "Tommy Anderson" <[EMAIL PROTECTED]>
> wrote in message news:[EMAIL PROTECTED]
>> Select * From ExcelMapValue
>>
>> OUTER JOIN   (SELECT ProjectId, InputId, DataValue, Formula
>> FROM ProjectData
>> WHERE  (ProjectId = @Id))  derivedtbl_1
>>
>> On ExcelMapValue.InputId =  derivedtbl_1.InputId
> 
> Try this:
> 
> Select * From ExcelMapValue e left join ProjectData p
> on (e.InputId = p.InputId and p.ProjectId = @Id);
> 
> Igor Tandetnik 
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Left-Join-tp20725943p20735463.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Database file size

[D. Richard Hipp]

On Nov 28, 2008, at 8:13 AM, Simon Bulman wrote:

> Ok, I am having trouble counting this morning.
>
> No of rows in Table 1: 1165
> No of rows in Table 2: 376295
>
> Analysis is attached.
>
> Sorry for the continued confusion.

The SUMMARY_VECTORS table consumes 99.55% of the file.  I assume that  
this is "Table 2".  We can ignore the rest.

I see that the index on SUMMARY_VECTORS is as big as the table  
itself.  This is surprising.  What does your schema look like?  You  
must be indexing every column of the table.  Is that really  
necessary?  You might save some space by only
indexing the columns you really need to index.

The storage efficiency for entries in SUMMARY_VECTORS is 62.2%. 
This is lower than normal.  SQLite usually gives a storage efficiency  
in the 80-90% range.  The reason it is lower here is that each row of  
your table is small - only 10.51 bytes on average.  Thus the few bytes  
of fixed overhead at the beginning of each row amount to a larger  
fraction of the total.  Even so, it is not a bad ratio.

Of the 10.51 bytes per row, 4 bytes is probably devoted to storing  
type and string-length information.  You likely omit the type  
information in your proprietary format, but I suppose you still need  
to store the string information.  So you could be using as little as  
8.51 bytes per row.   That still gives 3.2MB - not 1.7MB as you  
claim.  So you must also be doing some kind of compression in your  
proprietary format.

Bottom line:  The SQLite database file is densely packed.  I think you  
will be hard pressed to find another general-purpose database engine  
that will store your data more compactly.  If you want something to  
store data more compactly than what SQLite is currently doing, I  
suspect you will need to develop a proprietary one-off solution, as  
you have done.


>
>
> Cheers,
> S.
>
> -Original Message-
> From: Jens Miltner [mailto:[EMAIL PROTECTED]
> Sent: 28 November 2008 12:58
> To: [EMAIL PROTECTED]
> Cc: 'General Discussion of SQLite Database'
> Subject: Re: [sqlite] Database file size
>
>
> Am 28.11.2008 um 13:37 schrieb Simon Bulman:
>
>> Ahhh, sorry, I wrongly calculated the number of rows in table 2. It
>> actually
>> has 29581 rows. Still surprised at the 7x size increase but perhaps
>> you are
>> not based on the overheads?
>
> I still can't reproduce your database sizes - creating 3000 rows in
> table 1 and 3 in table 2 I end up with 745kB database file size.
> Even with 3 rows in table 1 I end up with only 2.9MB file size...
>
> And the overhead I was talking about is overhead that sqlite maintains
> for each row in every database, so that should be the same on your end
> and my end.
>
> Unless your row counts are much, much higher, I suspect that there's
> either
> (1) other tables that contribute the major part to the database file
> size
> (2) your database does have lots of free space and needs to be  
> vacuumed
>
> You could try to run sqlite3_analyzer on your database to see the
> memory usage of each table, free space, etc.
> Are you using a stock sqlite3 installation or did you modify/customize
> it through build settings?
>
> 
>
>
>>
>> -Original Message-
>> From: Jens Miltner [mailto:[EMAIL PROTECTED]
>> Sent: 28 November 2008 08:38
>> To: [EMAIL PROTECTED]
>> Cc: General Discussion of SQLite Database
>> Subject: Re: [sqlite] Database file size
>>
>>
>> Am 28.11.2008 um 09:20 schrieb Simon Bulman:
>>
>>> Hi Jens,
>>>
>>> Thanks for your input. UTF-8 did not make a difference. I expected
>>> that
>>> SQLite file would be larger on disk than our proprietary format
>>> because of
>>> the overheads that you mention - I am surprised however it at least
>>> 7x
>>> larger.
>>
>> To be honest - given your table definitions below, I'm surprised the
>> database is _that_ large, too:
>>
>> Table 1 - according to your definition - should contain at most about
>> 50 bytes of pure data per row (plus the overhead needed by SQLite).
>> Table 2 would only contain ~ 16 bytes of data per row.
>>
>> Dividing the database disk size by the total number of rows you
>> mentioned, would indicate a whopping 8k per row.
>>
>> I did a quick test and created a schema similar to what you outlined
>> and filled it with data (the same number of rows you mentioned and 28
>> and ~20 characters per row for the two varchar columns) and my
>> database ended up being 71kB in size instead of the 11.8 MB you  
>> saw...
>>
>> Are there any other tables that contain non-negligible amounts of
>> data?
>> Are the data sizes indeed what's indicated in the schema (since  
>> SQLite
>> doesn't really care about the varchar size constraints you can
>> actually put any amount of data into a varchar(30) column) ?
>>
>> 
>>
>>
>>>
>>> I am actually recreating the whole database (delete file and
>>> recreate)
>>> programmatically so vacuuming has not effect.
>>>
>>> Cheers,
>>> S.
>>>
>>> -Original Message-
>>> From: Jens Miltner [mailto:[EMAIL 

Re: [sqlite] Database file size

[Simon Bulman]

Ok, I am having trouble counting this morning.

No of rows in Table 1: 1165
No of rows in Table 2: 376295

Analysis is attached.

Sorry for the continued confusion.

Cheers,
S.

-Original Message-
From: Jens Miltner [mailto:[EMAIL PROTECTED] 
Sent: 28 November 2008 12:58
To: [EMAIL PROTECTED]
Cc: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Database file size


Am 28.11.2008 um 13:37 schrieb Simon Bulman:

> Ahhh, sorry, I wrongly calculated the number of rows in table 2. It  
> actually
> has 29581 rows. Still surprised at the 7x size increase but perhaps  
> you are
> not based on the overheads?

I still can't reproduce your database sizes - creating 3000 rows in  
table 1 and 3 in table 2 I end up with 745kB database file size.
Even with 3 rows in table 1 I end up with only 2.9MB file size...

And the overhead I was talking about is overhead that sqlite maintains  
for each row in every database, so that should be the same on your end  
and my end.

Unless your row counts are much, much higher, I suspect that there's  
either
(1) other tables that contribute the major part to the database file  
size
(2) your database does have lots of free space and needs to be vacuumed

You could try to run sqlite3_analyzer on your database to see the  
memory usage of each table, free space, etc.
Are you using a stock sqlite3 installation or did you modify/customize  
it through build settings?




>
> -Original Message-
> From: Jens Miltner [mailto:[EMAIL PROTECTED]
> Sent: 28 November 2008 08:38
> To: [EMAIL PROTECTED]
> Cc: General Discussion of SQLite Database
> Subject: Re: [sqlite] Database file size
>
>
> Am 28.11.2008 um 09:20 schrieb Simon Bulman:
>
>> Hi Jens,
>>
>> Thanks for your input. UTF-8 did not make a difference. I expected
>> that
>> SQLite file would be larger on disk than our proprietary format
>> because of
>> the overheads that you mention - I am surprised however it at least  
>> 7x
>> larger.
>
> To be honest - given your table definitions below, I'm surprised the
> database is _that_ large, too:
>
> Table 1 - according to your definition - should contain at most about
> 50 bytes of pure data per row (plus the overhead needed by SQLite).
> Table 2 would only contain ~ 16 bytes of data per row.
>
> Dividing the database disk size by the total number of rows you
> mentioned, would indicate a whopping 8k per row.
>
> I did a quick test and created a schema similar to what you outlined
> and filled it with data (the same number of rows you mentioned and 28
> and ~20 characters per row for the two varchar columns) and my
> database ended up being 71kB in size instead of the 11.8 MB you saw...
>
> Are there any other tables that contain non-negligible amounts of  
> data?
> Are the data sizes indeed what's indicated in the schema (since SQLite
> doesn't really care about the varchar size constraints you can
> actually put any amount of data into a varchar(30) column) ?
>
> 
>
>
>>
>> I am actually recreating the whole database (delete file and  
>> recreate)
>> programmatically so vacuuming has not effect.
>>
>> Cheers,
>> S.
>>
>> -Original Message-
>> From: Jens Miltner [mailto:[EMAIL PROTECTED]
>> Sent: 27 November 2008 13:48
>> To: General Discussion of SQLite Database
>> Cc: [EMAIL PROTECTED]
>> Subject: Re: [sqlite] Database file size
>>
>>
>> Am 27.11.2008 um 09:12 schrieb Simon Bulman:
>>
>>> I have been playing around with SQLite to use as an alternative to
>>> one of
>>> our proprietary file formats used to read large amounts of data. Our
>>> proprietary format performs very badly i.e. takes a long time to
>>> load some
>>> data; as expected SQLite is lighting quick in comparison - great!
>>>
>>> One considerable stumbling block is the footprint (size) of the
>>> database
>>> file on disk. It turns out that SQLite is roughly 7x larger than our
>>> proprietary format - this is prohibitive. The data is pretty simple
>>> really,
>>> 2 tables
>>>
>>> Table 1
>>>
>>> BIGINT (index),  VARCHAR(30), VARCHAR(10)
>>>
>>>
>>> Table 2
>>>
>>> BIGINT (index), FLOAT
>>>
>>>
>>> For a particular data set Table1 has 1165 rows and Table 2 has 323
>>> rows,
>>> however typically Table 2 becomes bigger for larger models. The size
>>> on disk
>>> of this file is 11.8 Mb (compared to 1.7 Mb for our proprietary
>>> format). I
>>> have noticed that if I drop the indexes the size drops  
>>> dramatically -
>>> however the query performance suffers to an unacceptable level.
>>>
>>> For a larger model the DB footprint is 2.2 Gb compared to 267 Mb for
>>> the
>>> proprietary format.
>>>
>>> Does anybody have any comments on this? Are there any configuration
>>> options
>>> or ideas I could use to reduce the footprint of the db file?
>>
>>
>> I don't think you'll be able to make SQLite as efficient (regarding
>> storage size) as a custom file format, because it has to have some
>> overhead for indexes, etc.
>>
>> However, one thing that comes to mind is the way 

Re: [sqlite] Database file size

[Jens Miltner]

Am 28.11.2008 um 13:37 schrieb Simon Bulman:

> Ahhh, sorry, I wrongly calculated the number of rows in table 2. It  
> actually
> has 29581 rows. Still surprised at the 7x size increase but perhaps  
> you are
> not based on the overheads?

I still can't reproduce your database sizes - creating 3000 rows in  
table 1 and 3 in table 2 I end up with 745kB database file size.
Even with 3 rows in table 1 I end up with only 2.9MB file size...

And the overhead I was talking about is overhead that sqlite maintains  
for each row in every database, so that should be the same on your end  
and my end.

Unless your row counts are much, much higher, I suspect that there's  
either
(1) other tables that contribute the major part to the database file  
size
(2) your database does have lots of free space and needs to be vacuumed

You could try to run sqlite3_analyzer on your database to see the  
memory usage of each table, free space, etc.
Are you using a stock sqlite3 installation or did you modify/customize  
it through build settings?




>
> -Original Message-
> From: Jens Miltner [mailto:[EMAIL PROTECTED]
> Sent: 28 November 2008 08:38
> To: [EMAIL PROTECTED]
> Cc: General Discussion of SQLite Database
> Subject: Re: [sqlite] Database file size
>
>
> Am 28.11.2008 um 09:20 schrieb Simon Bulman:
>
>> Hi Jens,
>>
>> Thanks for your input. UTF-8 did not make a difference. I expected
>> that
>> SQLite file would be larger on disk than our proprietary format
>> because of
>> the overheads that you mention - I am surprised however it at least  
>> 7x
>> larger.
>
> To be honest - given your table definitions below, I'm surprised the
> database is _that_ large, too:
>
> Table 1 - according to your definition - should contain at most about
> 50 bytes of pure data per row (plus the overhead needed by SQLite).
> Table 2 would only contain ~ 16 bytes of data per row.
>
> Dividing the database disk size by the total number of rows you
> mentioned, would indicate a whopping 8k per row.
>
> I did a quick test and created a schema similar to what you outlined
> and filled it with data (the same number of rows you mentioned and 28
> and ~20 characters per row for the two varchar columns) and my
> database ended up being 71kB in size instead of the 11.8 MB you saw...
>
> Are there any other tables that contain non-negligible amounts of  
> data?
> Are the data sizes indeed what's indicated in the schema (since SQLite
> doesn't really care about the varchar size constraints you can
> actually put any amount of data into a varchar(30) column) ?
>
> 
>
>
>>
>> I am actually recreating the whole database (delete file and  
>> recreate)
>> programmatically so vacuuming has not effect.
>>
>> Cheers,
>> S.
>>
>> -Original Message-
>> From: Jens Miltner [mailto:[EMAIL PROTECTED]
>> Sent: 27 November 2008 13:48
>> To: General Discussion of SQLite Database
>> Cc: [EMAIL PROTECTED]
>> Subject: Re: [sqlite] Database file size
>>
>>
>> Am 27.11.2008 um 09:12 schrieb Simon Bulman:
>>
>>> I have been playing around with SQLite to use as an alternative to
>>> one of
>>> our proprietary file formats used to read large amounts of data. Our
>>> proprietary format performs very badly i.e. takes a long time to
>>> load some
>>> data; as expected SQLite is lighting quick in comparison - great!
>>>
>>> One considerable stumbling block is the footprint (size) of the
>>> database
>>> file on disk. It turns out that SQLite is roughly 7x larger than our
>>> proprietary format - this is prohibitive. The data is pretty simple
>>> really,
>>> 2 tables
>>>
>>> Table 1
>>>
>>> BIGINT (index),  VARCHAR(30), VARCHAR(10)
>>>
>>>
>>> Table 2
>>>
>>> BIGINT (index), FLOAT
>>>
>>>
>>> For a particular data set Table1 has 1165 rows and Table 2 has 323
>>> rows,
>>> however typically Table 2 becomes bigger for larger models. The size
>>> on disk
>>> of this file is 11.8 Mb (compared to 1.7 Mb for our proprietary
>>> format). I
>>> have noticed that if I drop the indexes the size drops  
>>> dramatically -
>>> however the query performance suffers to an unacceptable level.
>>>
>>> For a larger model the DB footprint is 2.2 Gb compared to 267 Mb for
>>> the
>>> proprietary format.
>>>
>>> Does anybody have any comments on this? Are there any configuration
>>> options
>>> or ideas I could use to reduce the footprint of the db file?
>>
>>
>> I don't think you'll be able to make SQLite as efficient (regarding
>> storage size) as a custom file format, because it has to have some
>> overhead for indexes, etc.
>>
>> However, one thing that comes to mind is the way string data is
>> stored:
>> If you're concerned about disk space an your string data is mostly
>> ASCII, make sure your strings are stored as UTF-8 - for ASCII string
>> data, this will save you one byte per character in the string data
>> storage.
>> To enforce UTF-8 string storage, execute "PRAGMA encoding='UTF-8'" as
>> the first command when creating the database (before you create and

Re: [sqlite] Database file size

[Simon Bulman]

Ahhh, sorry, I wrongly calculated the number of rows in table 2. It actually
has 29581 rows. Still surprised at the 7x size increase but perhaps you are
not based on the overheads?

Sorry for the misleading info,

Cheers,
S.

-Original Message-
From: Jens Miltner [mailto:[EMAIL PROTECTED] 
Sent: 28 November 2008 08:38
To: [EMAIL PROTECTED]
Cc: General Discussion of SQLite Database
Subject: Re: [sqlite] Database file size


Am 28.11.2008 um 09:20 schrieb Simon Bulman:

> Hi Jens,
>
> Thanks for your input. UTF-8 did not make a difference. I expected  
> that
> SQLite file would be larger on disk than our proprietary format  
> because of
> the overheads that you mention - I am surprised however it at least 7x
> larger.

To be honest - given your table definitions below, I'm surprised the  
database is _that_ large, too:

Table 1 - according to your definition - should contain at most about  
50 bytes of pure data per row (plus the overhead needed by SQLite).
Table 2 would only contain ~ 16 bytes of data per row.

Dividing the database disk size by the total number of rows you  
mentioned, would indicate a whopping 8k per row.

I did a quick test and created a schema similar to what you outlined  
and filled it with data (the same number of rows you mentioned and 28  
and ~20 characters per row for the two varchar columns) and my  
database ended up being 71kB in size instead of the 11.8 MB you saw...

Are there any other tables that contain non-negligible amounts of data?
Are the data sizes indeed what's indicated in the schema (since SQLite  
doesn't really care about the varchar size constraints you can  
actually put any amount of data into a varchar(30) column) ?




>
> I am actually recreating the whole database (delete file and recreate)
> programmatically so vacuuming has not effect.
>
> Cheers,
> S.
>
> -Original Message-
> From: Jens Miltner [mailto:[EMAIL PROTECTED]
> Sent: 27 November 2008 13:48
> To: General Discussion of SQLite Database
> Cc: [EMAIL PROTECTED]
> Subject: Re: [sqlite] Database file size
>
>
> Am 27.11.2008 um 09:12 schrieb Simon Bulman:
>
>> I have been playing around with SQLite to use as an alternative to
>> one of
>> our proprietary file formats used to read large amounts of data. Our
>> proprietary format performs very badly i.e. takes a long time to
>> load some
>> data; as expected SQLite is lighting quick in comparison - great!
>>
>> One considerable stumbling block is the footprint (size) of the
>> database
>> file on disk. It turns out that SQLite is roughly 7x larger than our
>> proprietary format - this is prohibitive. The data is pretty simple
>> really,
>> 2 tables
>>
>> Table 1
>>
>> BIGINT (index),  VARCHAR(30), VARCHAR(10)
>>
>>
>> Table 2
>>
>> BIGINT (index), FLOAT
>>
>>
>> For a particular data set Table1 has 1165 rows and Table 2 has 323
>> rows,
>> however typically Table 2 becomes bigger for larger models. The size
>> on disk
>> of this file is 11.8 Mb (compared to 1.7 Mb for our proprietary
>> format). I
>> have noticed that if I drop the indexes the size drops dramatically -
>> however the query performance suffers to an unacceptable level.
>>
>> For a larger model the DB footprint is 2.2 Gb compared to 267 Mb for
>> the
>> proprietary format.
>>
>> Does anybody have any comments on this? Are there any configuration
>> options
>> or ideas I could use to reduce the footprint of the db file?
>
>
> I don't think you'll be able to make SQLite as efficient (regarding
> storage size) as a custom file format, because it has to have some
> overhead for indexes, etc.
>
> However, one thing that comes to mind is the way string data is  
> stored:
> If you're concerned about disk space an your string data is mostly
> ASCII, make sure your strings are stored as UTF-8 - for ASCII string
> data, this will save you one byte per character in the string data
> storage.
> To enforce UTF-8 string storage, execute "PRAGMA encoding='UTF-8'" as
> the first command when creating the database (before you create and
> tables).
> You can query the format using "PRAGMA encoding" - UTF-16 encodings
> will store two bytes / character, regardless of the actual  
> characters...
>
> Note that this doesn't mean your database size will shrink to half the
> size - it merely means you'll be able to fit more rows onto a single
> page, thus eventually you should see a decrease in file size when
> comparing UTF-16 vs. UTF-8 databases.
>
> BTW: are you aware that SQLite database won't shrink by themselves?
> You'll have to vacuum them to reclaim unused space (see
> > )
>
> HTH,
> 
>

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

Re: [sqlite] Database file size

[D. Richard Hipp]

Download the sqlite3_analyzer.exe utility from the SQLite website 
(http://www.sqlite.org/download.html 
) and run it against your database file.  The output will tell you  
where the disk space is being used.  You might want to post the output  
to this list.

D. Richard Hipp
[EMAIL PROTECTED]



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

Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b

[Ti Ny]

Done, it works :-)

DEF file was the one that had been missing for a long time :(

Thank you Mike.

Ti Ny

> From: [EMAIL PROTECTED]
> To: sqlite-users@sqlite.org
> Date: Fri, 28 Nov 2008 11:28:34 +0100
> Subject: Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b
> 
> Use the one included in this archive:
> http://www.sqlite.org/sqlitedll-3_6_6_2.zip
> 
> Mike
> 
> > -Ursprüngliche Nachricht-
> > Von: [EMAIL PROTECTED] 
> > [mailto:[EMAIL PROTECTED] Im Auftrag von Ti Ny
> > Gesendet: Freitag, 28. November 2008 11:26
> > An: sqlite-users@sqlite.org
> > Betreff: Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b
> > 
> > 
> > I don't have DEF file :(
> > 
> > Ti Ny
> > 
> > > From: [EMAIL PROTECTED]
> > > To: sqlite-users@sqlite.org
> > > Date: Fri, 28 Nov 2008 11:17:54 +0100
> > > Subject: Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b
> > > 
> > > Have you given the DEF file to the linker? Are the exports 
> > visible in 
> > > dependency walker? Is your DLL and the executable process type 
> > > compatible (e.g. both 32 or 64-bits?)
> > > 
> > > Mike
> > > 
> > > > -Ursprüngliche Nachricht-
> > > > Von: [EMAIL PROTECTED] 
> > > > [mailto:[EMAIL PROTECTED] Im Auftrag von Ti Ny
> > > > Gesendet: Freitag, 28. November 2008 10:57
> > > > An: sqlite-users@sqlite.org
> > > > Betreff: Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b
> > > > 
> > > > 
> > > > >Are you sure that your sqlite.dll exports its API?
> > > > I downloaded sources available on the sqlite.org, is 
> > there anything 
> > > > special that is required to do to export API?
> > > > 
> > > > > Like I said in my previous mail, I'd suggest using one of
> > > > the already
> > > > > available and well-tested .NET wrappers.
> > > > Unfortunately that's not possible for objective reasons.
> > > > 
> > > > Ti Ny
> > > > 
> > > > > From: [EMAIL PROTECTED]
> > > > > To: sqlite-users@sqlite.org
> > > > > Date: Fri, 28 Nov 2008 10:54:10 +0100
> > > > > Subject: Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b
> > > > > 
> > > > > Have you set ExactSpelling? EntryPointNotFound doesn't have
> > > > to do with
> > > > > CallingConvention. Are you sure that your sqlite.dll
> > > > exports its API?
> > > > > 
> > > > > Like I said in my previous mail, I'd suggest using one of
> > > > the already
> > > > > available and well-tested .NET wrappers.
> > > > > 
> > > > > Mike
> > > > > 
> > > > > > -Ursprüngliche Nachricht-
> > > > > > Von: [EMAIL PROTECTED] 
> > > > > > [mailto:[EMAIL PROTECTED] Im Auftrag von Ti Ny
> > > > > > Gesendet: Freitag, 28. November 2008 09:54
> > > > > > An: sqlite-users@sqlite.org
> > > > > > Betreff: Re: [sqlite] sqlite3_open16 fails on Windows 
> > Vista 64b
> > > > > > 
> > > > > > 
> > > > > > It doesn't work even if I set CallingConvention. It fails on 
> > > > > > EntryPointNotFound exception.
> > > > > > 
> > > > > > Ti Ny
> > > > > > 
> > > > > > > From: [EMAIL PROTECTED]
> > > > > > > To: sqlite-users@sqlite.org
> > > > > > > Date: Thu, 27 Nov 2008 19:57:30 +0100
> > > > > > > Subject: Re: [sqlite] sqlite3_open16 fails on Windows Vista 
> > > > > > > 64b
> > > > > > > 
> > > > > > > The given code is correct. The lower-case string is a C#
> > > > > > alias for the
> > > > > > > System.String class. System.Int64 is an opaque 64-bit
> > > > pointer value.
> > > > > > > 
> > > > > > > The code so far is correct. What is missing though is
> > > > the calling
> > > > > > > convention, which by default is cdecl, but .NET doesn't use
> > > > > > that one
> > > > > > > by default for P/Invoke. It uses stdcall/winapi as the
> > > > > > default calling
> > > > > > > convention. Change the CallingConvention in the 
> > DllImport line.
> > > > > > > 
> > > > > > > Additionally using CharSet=CharSet.Unicode adds an
> > > > implicit W as
> > > > > > > per
> > > > > > > Win32 calling conventions to the function name. Since the
> > > > > > > sqlite3_open16 doesn't have that you need to use
> > > > ExactSpelling to
> > > > > > > prevent it from adding that W.
> > > > > > > 
> > > > > > > Instead of rolling your own P/Invoke wrapper I'd suggest
> > > > > > using one of
> > > > > > > the available .NET wrappers for SQLite.
> > > > > > > 
> > > > > > > Mike
> > > > > > > 
> > > > > > > > -Ursprüngliche Nachricht-
> > > > > > > > Von: [EMAIL PROTECTED] 
> > > > > > > > [mailto:[EMAIL PROTECTED] Im Auftrag von
> > > > > > Sherief N. 
> > > > > > > > Farouk
> > > > > > > > Gesendet: Donnerstag, 27. November 2008 18:26
> > > > > > > > An: 'General Discussion of SQLite Database'
> > > > > > > > Betreff: Re: [sqlite] sqlite3_open16 fails on Windows
> > > > Vista 64b
> > > > > > > > 
> > > > > > > > > I am calling now this:
> > > > > > > > > 
> > > > > > > > > [DllImport("sqlite3.dll", CharSet =
> > > > CharSet.Unicode)]
> > > > > > > > > internal static extern System.Int64
> > > > > > sqlite3_open16(string
> > > > > > > > > filename, out IntPtr handle);
> > > > > > > > > 
> 

Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b

[Michael Ruck]

Use the one included in this archive:
http://www.sqlite.org/sqlitedll-3_6_6_2.zip

Mike

> -Ursprüngliche Nachricht-
> Von: [EMAIL PROTECTED] 
> [mailto:[EMAIL PROTECTED] Im Auftrag von Ti Ny
> Gesendet: Freitag, 28. November 2008 11:26
> An: sqlite-users@sqlite.org
> Betreff: Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b
> 
> 
> I don't have DEF file :(
> 
> Ti Ny
> 
> > From: [EMAIL PROTECTED]
> > To: sqlite-users@sqlite.org
> > Date: Fri, 28 Nov 2008 11:17:54 +0100
> > Subject: Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b
> > 
> > Have you given the DEF file to the linker? Are the exports 
> visible in 
> > dependency walker? Is your DLL and the executable process type 
> > compatible (e.g. both 32 or 64-bits?)
> > 
> > Mike
> > 
> > > -Ursprüngliche Nachricht-
> > > Von: [EMAIL PROTECTED] 
> > > [mailto:[EMAIL PROTECTED] Im Auftrag von Ti Ny
> > > Gesendet: Freitag, 28. November 2008 10:57
> > > An: sqlite-users@sqlite.org
> > > Betreff: Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b
> > > 
> > > 
> > > >Are you sure that your sqlite.dll exports its API?
> > > I downloaded sources available on the sqlite.org, is 
> there anything 
> > > special that is required to do to export API?
> > > 
> > > > Like I said in my previous mail, I'd suggest using one of
> > > the already
> > > > available and well-tested .NET wrappers.
> > > Unfortunately that's not possible for objective reasons.
> > > 
> > > Ti Ny
> > > 
> > > > From: [EMAIL PROTECTED]
> > > > To: sqlite-users@sqlite.org
> > > > Date: Fri, 28 Nov 2008 10:54:10 +0100
> > > > Subject: Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b
> > > > 
> > > > Have you set ExactSpelling? EntryPointNotFound doesn't have
> > > to do with
> > > > CallingConvention. Are you sure that your sqlite.dll
> > > exports its API?
> > > > 
> > > > Like I said in my previous mail, I'd suggest using one of
> > > the already
> > > > available and well-tested .NET wrappers.
> > > > 
> > > > Mike
> > > > 
> > > > > -Ursprüngliche Nachricht-
> > > > > Von: [EMAIL PROTECTED] 
> > > > > [mailto:[EMAIL PROTECTED] Im Auftrag von Ti Ny
> > > > > Gesendet: Freitag, 28. November 2008 09:54
> > > > > An: sqlite-users@sqlite.org
> > > > > Betreff: Re: [sqlite] sqlite3_open16 fails on Windows 
> Vista 64b
> > > > > 
> > > > > 
> > > > > It doesn't work even if I set CallingConvention. It fails on 
> > > > > EntryPointNotFound exception.
> > > > > 
> > > > > Ti Ny
> > > > > 
> > > > > > From: [EMAIL PROTECTED]
> > > > > > To: sqlite-users@sqlite.org
> > > > > > Date: Thu, 27 Nov 2008 19:57:30 +0100
> > > > > > Subject: Re: [sqlite] sqlite3_open16 fails on Windows Vista 
> > > > > > 64b
> > > > > > 
> > > > > > The given code is correct. The lower-case string is a C#
> > > > > alias for the
> > > > > > System.String class. System.Int64 is an opaque 64-bit
> > > pointer value.
> > > > > > 
> > > > > > The code so far is correct. What is missing though is
> > > the calling
> > > > > > convention, which by default is cdecl, but .NET doesn't use
> > > > > that one
> > > > > > by default for P/Invoke. It uses stdcall/winapi as the
> > > > > default calling
> > > > > > convention. Change the CallingConvention in the 
> DllImport line.
> > > > > > 
> > > > > > Additionally using CharSet=CharSet.Unicode adds an
> > > implicit W as
> > > > > > per
> > > > > > Win32 calling conventions to the function name. Since the
> > > > > > sqlite3_open16 doesn't have that you need to use
> > > ExactSpelling to
> > > > > > prevent it from adding that W.
> > > > > > 
> > > > > > Instead of rolling your own P/Invoke wrapper I'd suggest
> > > > > using one of
> > > > > > the available .NET wrappers for SQLite.
> > > > > > 
> > > > > > Mike
> > > > > > 
> > > > > > > -Ursprüngliche Nachricht-
> > > > > > > Von: [EMAIL PROTECTED] 
> > > > > > > [mailto:[EMAIL PROTECTED] Im Auftrag von
> > > > > Sherief N. 
> > > > > > > Farouk
> > > > > > > Gesendet: Donnerstag, 27. November 2008 18:26
> > > > > > > An: 'General Discussion of SQLite Database'
> > > > > > > Betreff: Re: [sqlite] sqlite3_open16 fails on Windows
> > > Vista 64b
> > > > > > > 
> > > > > > > > I am calling now this:
> > > > > > > > 
> > > > > > > > [DllImport("sqlite3.dll", CharSet =
> > > CharSet.Unicode)]
> > > > > > > > internal static extern System.Int64
> > > > > sqlite3_open16(string
> > > > > > > > filename, out IntPtr handle);
> > > > > > > > 
> > > > > > > > I now finds an entry point, but i returned to the
> > > > > previous error:
> > > > > > > > An attempt was made to read program in invalid format.
> > > > > > > > 
> > > > > > > > But now everything is 64bit.
> > > > > > > > 
> > > > > > > > Ti Ny
> > > > > > > > 
> > > > > > > 
> > > > > > > No offense, but I'm beginning to believe you don't fully
> > > > > grasp what
> > > > > > > you're trying to do. First of all, if DW can't find the
> > > > > entry point
> > > > > > > then it's not there. Second, I don't think 

Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b

[Ti Ny]

I don't have DEF file :(

Ti Ny

> From: [EMAIL PROTECTED]
> To: sqlite-users@sqlite.org
> Date: Fri, 28 Nov 2008 11:17:54 +0100
> Subject: Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b
> 
> Have you given the DEF file to the linker? Are the exports visible in
> dependency walker? Is your DLL and the executable process type compatible
> (e.g. both 32 or 64-bits?)
> 
> Mike
> 
> > -Ursprüngliche Nachricht-
> > Von: [EMAIL PROTECTED] 
> > [mailto:[EMAIL PROTECTED] Im Auftrag von Ti Ny
> > Gesendet: Freitag, 28. November 2008 10:57
> > An: sqlite-users@sqlite.org
> > Betreff: Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b
> > 
> > 
> > >Are you sure that your sqlite.dll exports its API?
> > I downloaded sources available on the sqlite.org, is there 
> > anything special that is required to do to export API?
> > 
> > > Like I said in my previous mail, I'd suggest using one of 
> > the already 
> > > available and well-tested .NET wrappers.
> > Unfortunately that's not possible for objective reasons.
> > 
> > Ti Ny
> > 
> > > From: [EMAIL PROTECTED]
> > > To: sqlite-users@sqlite.org
> > > Date: Fri, 28 Nov 2008 10:54:10 +0100
> > > Subject: Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b
> > > 
> > > Have you set ExactSpelling? EntryPointNotFound doesn't have 
> > to do with 
> > > CallingConvention. Are you sure that your sqlite.dll 
> > exports its API?
> > > 
> > > Like I said in my previous mail, I'd suggest using one of 
> > the already 
> > > available and well-tested .NET wrappers.
> > > 
> > > Mike
> > > 
> > > > -Ursprüngliche Nachricht-
> > > > Von: [EMAIL PROTECTED] 
> > > > [mailto:[EMAIL PROTECTED] Im Auftrag von Ti Ny
> > > > Gesendet: Freitag, 28. November 2008 09:54
> > > > An: sqlite-users@sqlite.org
> > > > Betreff: Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b
> > > > 
> > > > 
> > > > It doesn't work even if I set CallingConvention. It fails on 
> > > > EntryPointNotFound exception.
> > > > 
> > > > Ti Ny
> > > > 
> > > > > From: [EMAIL PROTECTED]
> > > > > To: sqlite-users@sqlite.org
> > > > > Date: Thu, 27 Nov 2008 19:57:30 +0100
> > > > > Subject: Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b
> > > > > 
> > > > > The given code is correct. The lower-case string is a C#
> > > > alias for the
> > > > > System.String class. System.Int64 is an opaque 64-bit 
> > pointer value.
> > > > > 
> > > > > The code so far is correct. What is missing though is 
> > the calling 
> > > > > convention, which by default is cdecl, but .NET doesn't use
> > > > that one
> > > > > by default for P/Invoke. It uses stdcall/winapi as the
> > > > default calling
> > > > > convention. Change the CallingConvention in the DllImport line.
> > > > > 
> > > > > Additionally using CharSet=CharSet.Unicode adds an 
> > implicit W as 
> > > > > per
> > > > > Win32 calling conventions to the function name. Since the
> > > > > sqlite3_open16 doesn't have that you need to use 
> > ExactSpelling to 
> > > > > prevent it from adding that W.
> > > > > 
> > > > > Instead of rolling your own P/Invoke wrapper I'd suggest
> > > > using one of
> > > > > the available .NET wrappers for SQLite.
> > > > > 
> > > > > Mike
> > > > > 
> > > > > > -Ursprüngliche Nachricht-
> > > > > > Von: [EMAIL PROTECTED] 
> > > > > > [mailto:[EMAIL PROTECTED] Im Auftrag von
> > > > Sherief N. 
> > > > > > Farouk
> > > > > > Gesendet: Donnerstag, 27. November 2008 18:26
> > > > > > An: 'General Discussion of SQLite Database'
> > > > > > Betreff: Re: [sqlite] sqlite3_open16 fails on Windows 
> > Vista 64b
> > > > > > 
> > > > > > > I am calling now this:
> > > > > > > 
> > > > > > > [DllImport("sqlite3.dll", CharSet = 
> > CharSet.Unicode)]
> > > > > > > internal static extern System.Int64
> > > > sqlite3_open16(string
> > > > > > > filename, out IntPtr handle);
> > > > > > > 
> > > > > > > I now finds an entry point, but i returned to the
> > > > previous error:
> > > > > > > An attempt was made to read program in invalid format.
> > > > > > > 
> > > > > > > But now everything is 64bit.
> > > > > > > 
> > > > > > > Ti Ny
> > > > > > > 
> > > > > > 
> > > > > > No offense, but I'm beginning to believe you don't fully
> > > > grasp what
> > > > > > you're trying to do. First of all, if DW can't find the
> > > > entry point
> > > > > > then it's not there. Second, I don't think the CLR type 
> > > > > > corresponding to the return value of
> > > > > > sqlite3_open16 is System.Int64, and I'm not sure what 
> > string is 
> > > > > > (CLR's string type is String, capital S). How about you
> > > > upload that
> > > > > > DLL of your somewhere, send a link and I wouldn't mind
> > > > checking it
> > > > > > for you.
> > > > > > 
> > > > > > - Sherief
> > > > > > 
> > > > > > ___
> > > > > > sqlite-users mailing list
> > > > > > sqlite-users@sqlite.org
> > > > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > > > > > 
> 

Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b

[Michael Ruck]

If you haven't specified the DEF file during linking, no symbol is exported.
There's no way for the .NET runtime to find the entry point without an
export table.

Mike 

> -Ursprüngliche Nachricht-
> Von: [EMAIL PROTECTED] 
> [mailto:[EMAIL PROTECTED] Im Auftrag von Ti Ny
> Gesendet: Freitag, 28. November 2008 11:23
> An: sqlite-users@sqlite.org
> Betreff: Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b
> 
> 
> >Have you given the DEF file to the linker? Are the exports 
> visible in 
> >dependency walker? Is your DLL and the executable process type 
> >compatible (e.g. both 32 or 64-bits?)No I did not give DEF to linker 
> >(will try)
> No, they are not
> Executable and DLL are both 64b
> 
> Ti Ny
> 
> > From: [EMAIL PROTECTED]
> > To: sqlite-users@sqlite.org
> > Date: Fri, 28 Nov 2008 11:17:54 +0100
> > Subject: Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b
> > 
> > Have you given the DEF file to the linker? Are the exports 
> visible in 
> > dependency walker? Is your DLL and the executable process type 
> > compatible (e.g. both 32 or 64-bits?)
> > 
> > Mike
> > 
> > > -Ursprüngliche Nachricht-
> > > Von: [EMAIL PROTECTED] 
> > > [mailto:[EMAIL PROTECTED] Im Auftrag von Ti Ny
> > > Gesendet: Freitag, 28. November 2008 10:57
> > > An: sqlite-users@sqlite.org
> > > Betreff: Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b
> > > 
> > > 
> > > >Are you sure that your sqlite.dll exports its API?
> > > I downloaded sources available on the sqlite.org, is 
> there anything 
> > > special that is required to do to export API?
> > > 
> > > > Like I said in my previous mail, I'd suggest using one of
> > > the already
> > > > available and well-tested .NET wrappers.
> > > Unfortunately that's not possible for objective reasons.
> > > 
> > > Ti Ny
> > > 
> > > > From: [EMAIL PROTECTED]
> > > > To: sqlite-users@sqlite.org
> > > > Date: Fri, 28 Nov 2008 10:54:10 +0100
> > > > Subject: Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b
> > > > 
> > > > Have you set ExactSpelling? EntryPointNotFound doesn't have
> > > to do with
> > > > CallingConvention. Are you sure that your sqlite.dll
> > > exports its API?
> > > > 
> > > > Like I said in my previous mail, I'd suggest using one of
> > > the already
> > > > available and well-tested .NET wrappers.
> > > > 
> > > > Mike
> > > > 
> > > > > -Ursprüngliche Nachricht-
> > > > > Von: [EMAIL PROTECTED] 
> > > > > [mailto:[EMAIL PROTECTED] Im Auftrag von Ti Ny
> > > > > Gesendet: Freitag, 28. November 2008 09:54
> > > > > An: sqlite-users@sqlite.org
> > > > > Betreff: Re: [sqlite] sqlite3_open16 fails on Windows 
> Vista 64b
> > > > > 
> > > > > 
> > > > > It doesn't work even if I set CallingConvention. It fails on 
> > > > > EntryPointNotFound exception.
> > > > > 
> > > > > Ti Ny
> > > > > 
> > > > > > From: [EMAIL PROTECTED]
> > > > > > To: sqlite-users@sqlite.org
> > > > > > Date: Thu, 27 Nov 2008 19:57:30 +0100
> > > > > > Subject: Re: [sqlite] sqlite3_open16 fails on Windows Vista 
> > > > > > 64b
> > > > > > 
> > > > > > The given code is correct. The lower-case string is a C#
> > > > > alias for the
> > > > > > System.String class. System.Int64 is an opaque 64-bit
> > > pointer value.
> > > > > > 
> > > > > > The code so far is correct. What is missing though is
> > > the calling
> > > > > > convention, which by default is cdecl, but .NET doesn't use
> > > > > that one
> > > > > > by default for P/Invoke. It uses stdcall/winapi as the
> > > > > default calling
> > > > > > convention. Change the CallingConvention in the 
> DllImport line.
> > > > > > 
> > > > > > Additionally using CharSet=CharSet.Unicode adds an
> > > implicit W as
> > > > > > per
> > > > > > Win32 calling conventions to the function name. Since the
> > > > > > sqlite3_open16 doesn't have that you need to use
> > > ExactSpelling to
> > > > > > prevent it from adding that W.
> > > > > > 
> > > > > > Instead of rolling your own P/Invoke wrapper I'd suggest
> > > > > using one of
> > > > > > the available .NET wrappers for SQLite.
> > > > > > 
> > > > > > Mike
> > > > > > 
> > > > > > > -Ursprüngliche Nachricht-
> > > > > > > Von: [EMAIL PROTECTED] 
> > > > > > > [mailto:[EMAIL PROTECTED] Im Auftrag von
> > > > > Sherief N. 
> > > > > > > Farouk
> > > > > > > Gesendet: Donnerstag, 27. November 2008 18:26
> > > > > > > An: 'General Discussion of SQLite Database'
> > > > > > > Betreff: Re: [sqlite] sqlite3_open16 fails on Windows
> > > Vista 64b
> > > > > > > 
> > > > > > > > I am calling now this:
> > > > > > > > 
> > > > > > > > [DllImport("sqlite3.dll", CharSet =
> > > CharSet.Unicode)]
> > > > > > > > internal static extern System.Int64
> > > > > sqlite3_open16(string
> > > > > > > > filename, out IntPtr handle);
> > > > > > > > 
> > > > > > > > I now finds an entry point, but i returned to the
> > > > > previous error:
> > > > > > > > An attempt was made to read program in invalid format.
> > > > > > > > 
> > > > > > > > 

Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b

[Ti Ny]

>Have you given the DEF file to the linker? Are the exports visible in
>dependency walker? Is your DLL and the executable process type compatible
>(e.g. both 32 or 64-bits?)No I did not give DEF to linker (will try)
No, they are not
Executable and DLL are both 64b

Ti Ny

> From: [EMAIL PROTECTED]
> To: sqlite-users@sqlite.org
> Date: Fri, 28 Nov 2008 11:17:54 +0100
> Subject: Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b
> 
> Have you given the DEF file to the linker? Are the exports visible in
> dependency walker? Is your DLL and the executable process type compatible
> (e.g. both 32 or 64-bits?)
> 
> Mike
> 
> > -Ursprüngliche Nachricht-
> > Von: [EMAIL PROTECTED] 
> > [mailto:[EMAIL PROTECTED] Im Auftrag von Ti Ny
> > Gesendet: Freitag, 28. November 2008 10:57
> > An: sqlite-users@sqlite.org
> > Betreff: Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b
> > 
> > 
> > >Are you sure that your sqlite.dll exports its API?
> > I downloaded sources available on the sqlite.org, is there 
> > anything special that is required to do to export API?
> > 
> > > Like I said in my previous mail, I'd suggest using one of 
> > the already 
> > > available and well-tested .NET wrappers.
> > Unfortunately that's not possible for objective reasons.
> > 
> > Ti Ny
> > 
> > > From: [EMAIL PROTECTED]
> > > To: sqlite-users@sqlite.org
> > > Date: Fri, 28 Nov 2008 10:54:10 +0100
> > > Subject: Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b
> > > 
> > > Have you set ExactSpelling? EntryPointNotFound doesn't have 
> > to do with 
> > > CallingConvention. Are you sure that your sqlite.dll 
> > exports its API?
> > > 
> > > Like I said in my previous mail, I'd suggest using one of 
> > the already 
> > > available and well-tested .NET wrappers.
> > > 
> > > Mike
> > > 
> > > > -Ursprüngliche Nachricht-
> > > > Von: [EMAIL PROTECTED] 
> > > > [mailto:[EMAIL PROTECTED] Im Auftrag von Ti Ny
> > > > Gesendet: Freitag, 28. November 2008 09:54
> > > > An: sqlite-users@sqlite.org
> > > > Betreff: Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b
> > > > 
> > > > 
> > > > It doesn't work even if I set CallingConvention. It fails on 
> > > > EntryPointNotFound exception.
> > > > 
> > > > Ti Ny
> > > > 
> > > > > From: [EMAIL PROTECTED]
> > > > > To: sqlite-users@sqlite.org
> > > > > Date: Thu, 27 Nov 2008 19:57:30 +0100
> > > > > Subject: Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b
> > > > > 
> > > > > The given code is correct. The lower-case string is a C#
> > > > alias for the
> > > > > System.String class. System.Int64 is an opaque 64-bit 
> > pointer value.
> > > > > 
> > > > > The code so far is correct. What is missing though is 
> > the calling 
> > > > > convention, which by default is cdecl, but .NET doesn't use
> > > > that one
> > > > > by default for P/Invoke. It uses stdcall/winapi as the
> > > > default calling
> > > > > convention. Change the CallingConvention in the DllImport line.
> > > > > 
> > > > > Additionally using CharSet=CharSet.Unicode adds an 
> > implicit W as 
> > > > > per
> > > > > Win32 calling conventions to the function name. Since the
> > > > > sqlite3_open16 doesn't have that you need to use 
> > ExactSpelling to 
> > > > > prevent it from adding that W.
> > > > > 
> > > > > Instead of rolling your own P/Invoke wrapper I'd suggest
> > > > using one of
> > > > > the available .NET wrappers for SQLite.
> > > > > 
> > > > > Mike
> > > > > 
> > > > > > -Ursprüngliche Nachricht-
> > > > > > Von: [EMAIL PROTECTED] 
> > > > > > [mailto:[EMAIL PROTECTED] Im Auftrag von
> > > > Sherief N. 
> > > > > > Farouk
> > > > > > Gesendet: Donnerstag, 27. November 2008 18:26
> > > > > > An: 'General Discussion of SQLite Database'
> > > > > > Betreff: Re: [sqlite] sqlite3_open16 fails on Windows 
> > Vista 64b
> > > > > > 
> > > > > > > I am calling now this:
> > > > > > > 
> > > > > > > [DllImport("sqlite3.dll", CharSet = 
> > CharSet.Unicode)]
> > > > > > > internal static extern System.Int64
> > > > sqlite3_open16(string
> > > > > > > filename, out IntPtr handle);
> > > > > > > 
> > > > > > > I now finds an entry point, but i returned to the
> > > > previous error:
> > > > > > > An attempt was made to read program in invalid format.
> > > > > > > 
> > > > > > > But now everything is 64bit.
> > > > > > > 
> > > > > > > Ti Ny
> > > > > > > 
> > > > > > 
> > > > > > No offense, but I'm beginning to believe you don't fully
> > > > grasp what
> > > > > > you're trying to do. First of all, if DW can't find the
> > > > entry point
> > > > > > then it's not there. Second, I don't think the CLR type 
> > > > > > corresponding to the return value of
> > > > > > sqlite3_open16 is System.Int64, and I'm not sure what 
> > string is 
> > > > > > (CLR's string type is String, capital S). How about you
> > > > upload that
> > > > > > DLL of your somewhere, send a link and I wouldn't mind
> > > > checking it
> > > > > > for you.
> > > > > > 
> > > > > > - 

Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b

[Michael Ruck]

Have you given the DEF file to the linker? Are the exports visible in
dependency walker? Is your DLL and the executable process type compatible
(e.g. both 32 or 64-bits?)

Mike

> -Ursprüngliche Nachricht-
> Von: [EMAIL PROTECTED] 
> [mailto:[EMAIL PROTECTED] Im Auftrag von Ti Ny
> Gesendet: Freitag, 28. November 2008 10:57
> An: sqlite-users@sqlite.org
> Betreff: Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b
> 
> 
> >Are you sure that your sqlite.dll exports its API?
> I downloaded sources available on the sqlite.org, is there 
> anything special that is required to do to export API?
> 
> > Like I said in my previous mail, I'd suggest using one of 
> the already 
> > available and well-tested .NET wrappers.
> Unfortunately that's not possible for objective reasons.
> 
> Ti Ny
> 
> > From: [EMAIL PROTECTED]
> > To: sqlite-users@sqlite.org
> > Date: Fri, 28 Nov 2008 10:54:10 +0100
> > Subject: Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b
> > 
> > Have you set ExactSpelling? EntryPointNotFound doesn't have 
> to do with 
> > CallingConvention. Are you sure that your sqlite.dll 
> exports its API?
> > 
> > Like I said in my previous mail, I'd suggest using one of 
> the already 
> > available and well-tested .NET wrappers.
> > 
> > Mike
> > 
> > > -Ursprüngliche Nachricht-
> > > Von: [EMAIL PROTECTED] 
> > > [mailto:[EMAIL PROTECTED] Im Auftrag von Ti Ny
> > > Gesendet: Freitag, 28. November 2008 09:54
> > > An: sqlite-users@sqlite.org
> > > Betreff: Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b
> > > 
> > > 
> > > It doesn't work even if I set CallingConvention. It fails on 
> > > EntryPointNotFound exception.
> > > 
> > > Ti Ny
> > > 
> > > > From: [EMAIL PROTECTED]
> > > > To: sqlite-users@sqlite.org
> > > > Date: Thu, 27 Nov 2008 19:57:30 +0100
> > > > Subject: Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b
> > > > 
> > > > The given code is correct. The lower-case string is a C#
> > > alias for the
> > > > System.String class. System.Int64 is an opaque 64-bit 
> pointer value.
> > > > 
> > > > The code so far is correct. What is missing though is 
> the calling 
> > > > convention, which by default is cdecl, but .NET doesn't use
> > > that one
> > > > by default for P/Invoke. It uses stdcall/winapi as the
> > > default calling
> > > > convention. Change the CallingConvention in the DllImport line.
> > > > 
> > > > Additionally using CharSet=CharSet.Unicode adds an 
> implicit W as 
> > > > per
> > > > Win32 calling conventions to the function name. Since the
> > > > sqlite3_open16 doesn't have that you need to use 
> ExactSpelling to 
> > > > prevent it from adding that W.
> > > > 
> > > > Instead of rolling your own P/Invoke wrapper I'd suggest
> > > using one of
> > > > the available .NET wrappers for SQLite.
> > > > 
> > > > Mike
> > > > 
> > > > > -Ursprüngliche Nachricht-
> > > > > Von: [EMAIL PROTECTED] 
> > > > > [mailto:[EMAIL PROTECTED] Im Auftrag von
> > > Sherief N. 
> > > > > Farouk
> > > > > Gesendet: Donnerstag, 27. November 2008 18:26
> > > > > An: 'General Discussion of SQLite Database'
> > > > > Betreff: Re: [sqlite] sqlite3_open16 fails on Windows 
> Vista 64b
> > > > > 
> > > > > > I am calling now this:
> > > > > > 
> > > > > > [DllImport("sqlite3.dll", CharSet = 
> CharSet.Unicode)]
> > > > > > internal static extern System.Int64
> > > sqlite3_open16(string
> > > > > > filename, out IntPtr handle);
> > > > > > 
> > > > > > I now finds an entry point, but i returned to the
> > > previous error:
> > > > > > An attempt was made to read program in invalid format.
> > > > > > 
> > > > > > But now everything is 64bit.
> > > > > > 
> > > > > > Ti Ny
> > > > > > 
> > > > > 
> > > > > No offense, but I'm beginning to believe you don't fully
> > > grasp what
> > > > > you're trying to do. First of all, if DW can't find the
> > > entry point
> > > > > then it's not there. Second, I don't think the CLR type 
> > > > > corresponding to the return value of
> > > > > sqlite3_open16 is System.Int64, and I'm not sure what 
> string is 
> > > > > (CLR's string type is String, capital S). How about you
> > > upload that
> > > > > DLL of your somewhere, send a link and I wouldn't mind
> > > checking it
> > > > > for you.
> > > > > 
> > > > > - Sherief
> > > > > 
> > > > > ___
> > > > > 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
> > > 
> > > _
> > > Explore the seven wonders of the world 
> > > http://search.msn.com/results.aspx?q=7+wonders+world=en-US
> > > =QBRE
> > > ___
> > > 

Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b

[Ti Ny]

>Are you sure that your sqlite.dll exports its API?
I downloaded sources available on the sqlite.org, is there anything special 
that is required to do to export API?

> Like I said in my previous mail, I'd suggest using one of the already
> available and well-tested .NET wrappers.
Unfortunately that's not possible for objective reasons.

Ti Ny

> From: [EMAIL PROTECTED]
> To: sqlite-users@sqlite.org
> Date: Fri, 28 Nov 2008 10:54:10 +0100
> Subject: Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b
> 
> Have you set ExactSpelling? EntryPointNotFound doesn't have to do with
> CallingConvention. Are you sure that your sqlite.dll exports its API?
> 
> Like I said in my previous mail, I'd suggest using one of the already
> available and well-tested .NET wrappers.
> 
> Mike 
> 
> > -Ursprüngliche Nachricht-
> > Von: [EMAIL PROTECTED] 
> > [mailto:[EMAIL PROTECTED] Im Auftrag von Ti Ny
> > Gesendet: Freitag, 28. November 2008 09:54
> > An: sqlite-users@sqlite.org
> > Betreff: Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b
> > 
> > 
> > It doesn't work even if I set CallingConvention. It fails on 
> > EntryPointNotFound exception.
> > 
> > Ti Ny
> > 
> > > From: [EMAIL PROTECTED]
> > > To: sqlite-users@sqlite.org
> > > Date: Thu, 27 Nov 2008 19:57:30 +0100
> > > Subject: Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b
> > > 
> > > The given code is correct. The lower-case string is a C# 
> > alias for the 
> > > System.String class. System.Int64 is an opaque 64-bit pointer value.
> > > 
> > > The code so far is correct. What is missing though is the calling 
> > > convention, which by default is cdecl, but .NET doesn't use 
> > that one 
> > > by default for P/Invoke. It uses stdcall/winapi as the 
> > default calling 
> > > convention. Change the CallingConvention in the DllImport line.
> > > 
> > > Additionally using CharSet=CharSet.Unicode adds an implicit W as per
> > > Win32 calling conventions to the function name. Since the 
> > > sqlite3_open16 doesn't have that you need to use ExactSpelling to 
> > > prevent it from adding that W.
> > > 
> > > Instead of rolling your own P/Invoke wrapper I'd suggest 
> > using one of 
> > > the available .NET wrappers for SQLite.
> > > 
> > > Mike
> > > 
> > > > -Ursprüngliche Nachricht-
> > > > Von: [EMAIL PROTECTED] 
> > > > [mailto:[EMAIL PROTECTED] Im Auftrag von 
> > Sherief N. 
> > > > Farouk
> > > > Gesendet: Donnerstag, 27. November 2008 18:26
> > > > An: 'General Discussion of SQLite Database'
> > > > Betreff: Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b
> > > > 
> > > > > I am calling now this:
> > > > > 
> > > > > [DllImport("sqlite3.dll", CharSet = CharSet.Unicode)]
> > > > > internal static extern System.Int64 
> > sqlite3_open16(string 
> > > > > filename, out IntPtr handle);
> > > > > 
> > > > > I now finds an entry point, but i returned to the 
> > previous error:
> > > > > An attempt was made to read program in invalid format.
> > > > > 
> > > > > But now everything is 64bit.
> > > > > 
> > > > > Ti Ny
> > > > > 
> > > > 
> > > > No offense, but I'm beginning to believe you don't fully 
> > grasp what 
> > > > you're trying to do. First of all, if DW can't find the 
> > entry point 
> > > > then it's not there. Second, I don't think the CLR type 
> > > > corresponding to the return value of
> > > > sqlite3_open16 is System.Int64, and I'm not sure what string is 
> > > > (CLR's string type is String, capital S). How about you 
> > upload that 
> > > > DLL of your somewhere, send a link and I wouldn't mind 
> > checking it 
> > > > for you.
> > > > 
> > > > - Sherief
> > > > 
> > > > ___
> > > > 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
> > 
> > _
> > Explore the seven wonders of the world
> > http://search.msn.com/results.aspx?q=7+wonders+world=en-US
> > =QBRE
> > ___
> > 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

_
News, entertainment and everything you care about at Live.com. Get it now!
http://www.live.com/getstarted.aspx
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b

[Michael Ruck]

Have you set ExactSpelling? EntryPointNotFound doesn't have to do with
CallingConvention. Are you sure that your sqlite.dll exports its API?

Like I said in my previous mail, I'd suggest using one of the already
available and well-tested .NET wrappers.

Mike 

> -Ursprüngliche Nachricht-
> Von: [EMAIL PROTECTED] 
> [mailto:[EMAIL PROTECTED] Im Auftrag von Ti Ny
> Gesendet: Freitag, 28. November 2008 09:54
> An: sqlite-users@sqlite.org
> Betreff: Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b
> 
> 
> It doesn't work even if I set CallingConvention. It fails on 
> EntryPointNotFound exception.
> 
> Ti Ny
> 
> > From: [EMAIL PROTECTED]
> > To: sqlite-users@sqlite.org
> > Date: Thu, 27 Nov 2008 19:57:30 +0100
> > Subject: Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b
> > 
> > The given code is correct. The lower-case string is a C# 
> alias for the 
> > System.String class. System.Int64 is an opaque 64-bit pointer value.
> > 
> > The code so far is correct. What is missing though is the calling 
> > convention, which by default is cdecl, but .NET doesn't use 
> that one 
> > by default for P/Invoke. It uses stdcall/winapi as the 
> default calling 
> > convention. Change the CallingConvention in the DllImport line.
> > 
> > Additionally using CharSet=CharSet.Unicode adds an implicit W as per
> > Win32 calling conventions to the function name. Since the 
> > sqlite3_open16 doesn't have that you need to use ExactSpelling to 
> > prevent it from adding that W.
> > 
> > Instead of rolling your own P/Invoke wrapper I'd suggest 
> using one of 
> > the available .NET wrappers for SQLite.
> > 
> > Mike
> > 
> > > -Ursprüngliche Nachricht-
> > > Von: [EMAIL PROTECTED] 
> > > [mailto:[EMAIL PROTECTED] Im Auftrag von 
> Sherief N. 
> > > Farouk
> > > Gesendet: Donnerstag, 27. November 2008 18:26
> > > An: 'General Discussion of SQLite Database'
> > > Betreff: Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b
> > > 
> > > > I am calling now this:
> > > > 
> > > > [DllImport("sqlite3.dll", CharSet = CharSet.Unicode)]
> > > > internal static extern System.Int64 
> sqlite3_open16(string 
> > > > filename, out IntPtr handle);
> > > > 
> > > > I now finds an entry point, but i returned to the 
> previous error:
> > > > An attempt was made to read program in invalid format.
> > > > 
> > > > But now everything is 64bit.
> > > > 
> > > > Ti Ny
> > > > 
> > > 
> > > No offense, but I'm beginning to believe you don't fully 
> grasp what 
> > > you're trying to do. First of all, if DW can't find the 
> entry point 
> > > then it's not there. Second, I don't think the CLR type 
> > > corresponding to the return value of
> > > sqlite3_open16 is System.Int64, and I'm not sure what string is 
> > > (CLR's string type is String, capital S). How about you 
> upload that 
> > > DLL of your somewhere, send a link and I wouldn't mind 
> checking it 
> > > for you.
> > > 
> > > - Sherief
> > > 
> > > ___
> > > 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
> 
> _
> Explore the seven wonders of the world
> http://search.msn.com/results.aspx?q=7+wonders+world=en-US
> =QBRE
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 

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

Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b

[Ti Ny]

It doesn't work even if I set CallingConvention. It fails on EntryPointNotFound 
exception.

Ti Ny

> From: [EMAIL PROTECTED]
> To: sqlite-users@sqlite.org
> Date: Thu, 27 Nov 2008 19:57:30 +0100
> Subject: Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b
> 
> The given code is correct. The lower-case string is a C# alias for the 
> System.String class. System.Int64 is an opaque 64-bit pointer value.
> 
> The code so far is correct. What is missing though is the calling 
> convention, which by default is cdecl, but .NET doesn't use that one 
> by default for P/Invoke. It uses stdcall/winapi as the default calling
> convention. Change the CallingConvention in the DllImport line.
> 
> Additionally using CharSet=CharSet.Unicode adds an implicit W as per
> Win32 calling conventions to the function name. Since the sqlite3_open16
> doesn't have that you need to use ExactSpelling to prevent it from adding
> that W.
> 
> Instead of rolling your own P/Invoke wrapper I'd suggest using one of 
> the available .NET wrappers for SQLite.
> 
> Mike
> 
> > -Ursprüngliche Nachricht-
> > Von: [EMAIL PROTECTED] 
> > [mailto:[EMAIL PROTECTED] Im Auftrag von 
> > Sherief N. Farouk
> > Gesendet: Donnerstag, 27. November 2008 18:26
> > An: 'General Discussion of SQLite Database'
> > Betreff: Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b
> > 
> > > I am calling now this:
> > > 
> > > [DllImport("sqlite3.dll", CharSet = CharSet.Unicode)]
> > > internal static extern System.Int64 sqlite3_open16(string 
> > > filename, out IntPtr handle);
> > > 
> > > I now finds an entry point, but i returned to the previous error:
> > > An attempt was made to read program in invalid format.
> > > 
> > > But now everything is 64bit.
> > > 
> > > Ti Ny
> > > 
> > 
> > No offense, but I'm beginning to believe you don't fully 
> > grasp what you're trying to do. First of all, if DW can't 
> > find the entry point then it's not there. Second, I don't 
> > think the CLR type corresponding to the return value of 
> > sqlite3_open16 is System.Int64, and I'm not sure what string 
> > is (CLR's string type is String, capital S). How about you 
> > upload that DLL of your somewhere, send a link and I wouldn't 
> > mind checking it for you.
> > 
> > - Sherief
> > 
> > ___
> > 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

_
Explore the seven wonders of the world
http://search.msn.com/results.aspx?q=7+wonders+world=en-US=QBRE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database file size

[Jens Miltner]

Am 28.11.2008 um 09:20 schrieb Simon Bulman:

> Hi Jens,
>
> Thanks for your input. UTF-8 did not make a difference. I expected  
> that
> SQLite file would be larger on disk than our proprietary format  
> because of
> the overheads that you mention - I am surprised however it at least 7x
> larger.

To be honest - given your table definitions below, I'm surprised the  
database is _that_ large, too:

Table 1 - according to your definition - should contain at most about  
50 bytes of pure data per row (plus the overhead needed by SQLite).
Table 2 would only contain ~ 16 bytes of data per row.

Dividing the database disk size by the total number of rows you  
mentioned, would indicate a whopping 8k per row.

I did a quick test and created a schema similar to what you outlined  
and filled it with data (the same number of rows you mentioned and 28  
and ~20 characters per row for the two varchar columns) and my  
database ended up being 71kB in size instead of the 11.8 MB you saw...

Are there any other tables that contain non-negligible amounts of data?
Are the data sizes indeed what's indicated in the schema (since SQLite  
doesn't really care about the varchar size constraints you can  
actually put any amount of data into a varchar(30) column) ?




>
> I am actually recreating the whole database (delete file and recreate)
> programmatically so vacuuming has not effect.
>
> Cheers,
> S.
>
> -Original Message-
> From: Jens Miltner [mailto:[EMAIL PROTECTED]
> Sent: 27 November 2008 13:48
> To: General Discussion of SQLite Database
> Cc: [EMAIL PROTECTED]
> Subject: Re: [sqlite] Database file size
>
>
> Am 27.11.2008 um 09:12 schrieb Simon Bulman:
>
>> I have been playing around with SQLite to use as an alternative to
>> one of
>> our proprietary file formats used to read large amounts of data. Our
>> proprietary format performs very badly i.e. takes a long time to
>> load some
>> data; as expected SQLite is lighting quick in comparison - great!
>>
>> One considerable stumbling block is the footprint (size) of the
>> database
>> file on disk. It turns out that SQLite is roughly 7x larger than our
>> proprietary format - this is prohibitive. The data is pretty simple
>> really,
>> 2 tables
>>
>> Table 1
>>
>> BIGINT (index),  VARCHAR(30), VARCHAR(10)
>>
>>
>> Table 2
>>
>> BIGINT (index), FLOAT
>>
>>
>> For a particular data set Table1 has 1165 rows and Table 2 has 323
>> rows,
>> however typically Table 2 becomes bigger for larger models. The size
>> on disk
>> of this file is 11.8 Mb (compared to 1.7 Mb for our proprietary
>> format). I
>> have noticed that if I drop the indexes the size drops dramatically -
>> however the query performance suffers to an unacceptable level.
>>
>> For a larger model the DB footprint is 2.2 Gb compared to 267 Mb for
>> the
>> proprietary format.
>>
>> Does anybody have any comments on this? Are there any configuration
>> options
>> or ideas I could use to reduce the footprint of the db file?
>
>
> I don't think you'll be able to make SQLite as efficient (regarding
> storage size) as a custom file format, because it has to have some
> overhead for indexes, etc.
>
> However, one thing that comes to mind is the way string data is  
> stored:
> If you're concerned about disk space an your string data is mostly
> ASCII, make sure your strings are stored as UTF-8 - for ASCII string
> data, this will save you one byte per character in the string data
> storage.
> To enforce UTF-8 string storage, execute "PRAGMA encoding='UTF-8'" as
> the first command when creating the database (before you create and
> tables).
> You can query the format using "PRAGMA encoding" - UTF-16 encodings
> will store two bytes / character, regardless of the actual  
> characters...
>
> Note that this doesn't mean your database size will shrink to half the
> size - it merely means you'll be able to fit more rows onto a single
> page, thus eventually you should see a decrease in file size when
> comparing UTF-16 vs. UTF-8 databases.
>
> BTW: are you aware that SQLite database won't shrink by themselves?
> You'll have to vacuum them to reclaim unused space (see
> > )
>
> HTH,
> 
>

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

Re: [sqlite] Database file size

[Jens Miltner]

Am 28.11.2008 um 09:20 schrieb Simon Bulman:

> Hi Jens,
>
> Thanks for your input. UTF-8 did not make a difference. I expected  
> that
> SQLite file would be larger on disk than our proprietary format  
> because of
> the overheads that you mention - I am surprised however it at least 7x
> larger.

To be honest - given your table definitions below, I'm surprised the  
database is _that_ large, too:

Table 1 - according to your definition - should contain at most about  
50 bytes of pure data per row (plus the overhead needed by SQLite).
Table 2 would only contain ~ 16 bytes of data per row.

Dividing the database disk size by the total number of rows you  
mentioned, would indicate a whopping 8k per row.

I did a quick test and created a schema similar to what you outlined  
and filled it with data (the same number of rows you mentioned and 28  
and ~20 characters per row for the two varchar columns) and my  
database ended up being 71kB in size instead of the 11.8 MB you saw...

Are there any other tables that contain non-negligible amounts of data?
Are the data sizes indeed what's indicated in the schema (since SQLite  
doesn't really care about the varchar size constraints you can  
actually put any amount of data into a varchar(30) column) ?




>
> I am actually recreating the whole database (delete file and recreate)
> programmatically so vacuuming has not effect.
>
> Cheers,
> S.
>
> -Original Message-
> From: Jens Miltner [mailto:[EMAIL PROTECTED]
> Sent: 27 November 2008 13:48
> To: General Discussion of SQLite Database
> Cc: [EMAIL PROTECTED]
> Subject: Re: [sqlite] Database file size
>
>
> Am 27.11.2008 um 09:12 schrieb Simon Bulman:
>
>> I have been playing around with SQLite to use as an alternative to
>> one of
>> our proprietary file formats used to read large amounts of data. Our
>> proprietary format performs very badly i.e. takes a long time to
>> load some
>> data; as expected SQLite is lighting quick in comparison - great!
>>
>> One considerable stumbling block is the footprint (size) of the
>> database
>> file on disk. It turns out that SQLite is roughly 7x larger than our
>> proprietary format - this is prohibitive. The data is pretty simple
>> really,
>> 2 tables
>>
>> Table 1
>>
>> BIGINT (index),  VARCHAR(30), VARCHAR(10)
>>
>>
>> Table 2
>>
>> BIGINT (index), FLOAT
>>
>>
>> For a particular data set Table1 has 1165 rows and Table 2 has 323
>> rows,
>> however typically Table 2 becomes bigger for larger models. The size
>> on disk
>> of this file is 11.8 Mb (compared to 1.7 Mb for our proprietary
>> format). I
>> have noticed that if I drop the indexes the size drops dramatically -
>> however the query performance suffers to an unacceptable level.
>>
>> For a larger model the DB footprint is 2.2 Gb compared to 267 Mb for
>> the
>> proprietary format.
>>
>> Does anybody have any comments on this? Are there any configuration
>> options
>> or ideas I could use to reduce the footprint of the db file?
>
>
> I don't think you'll be able to make SQLite as efficient (regarding
> storage size) as a custom file format, because it has to have some
> overhead for indexes, etc.
>
> However, one thing that comes to mind is the way string data is  
> stored:
> If you're concerned about disk space an your string data is mostly
> ASCII, make sure your strings are stored as UTF-8 - for ASCII string
> data, this will save you one byte per character in the string data
> storage.
> To enforce UTF-8 string storage, execute "PRAGMA encoding='UTF-8'" as
> the first command when creating the database (before you create and
> tables).
> You can query the format using "PRAGMA encoding" - UTF-16 encodings
> will store two bytes / character, regardless of the actual  
> characters...
>
> Note that this doesn't mean your database size will shrink to half the
> size - it merely means you'll be able to fit more rows onto a single
> page, thus eventually you should see a decrease in file size when
> comparing UTF-16 vs. UTF-8 databases.
>
> BTW: are you aware that SQLite database won't shrink by themselves?
> You'll have to vacuum them to reclaim unused space (see
> > )
>
> HTH,
> 
>

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

Re: [sqlite] Database file size

[Simon Bulman]

Hi Jens,

Thanks for your input. UTF-8 did not make a difference. I expected that
SQLite file would be larger on disk than our proprietary format because of
the overheads that you mention - I am surprised however it at least 7x
larger.

I am actually recreating the whole database (delete file and recreate)
programmatically so vacuuming has not effect.

Cheers,
S.

-Original Message-
From: Jens Miltner [mailto:[EMAIL PROTECTED] 
Sent: 27 November 2008 13:48
To: General Discussion of SQLite Database
Cc: [EMAIL PROTECTED]
Subject: Re: [sqlite] Database file size


Am 27.11.2008 um 09:12 schrieb Simon Bulman:

> I have been playing around with SQLite to use as an alternative to  
> one of
> our proprietary file formats used to read large amounts of data. Our
> proprietary format performs very badly i.e. takes a long time to  
> load some
> data; as expected SQLite is lighting quick in comparison - great!
>
> One considerable stumbling block is the footprint (size) of the  
> database
> file on disk. It turns out that SQLite is roughly 7x larger than our
> proprietary format - this is prohibitive. The data is pretty simple  
> really,
> 2 tables
>
> Table 1
>
> BIGINT (index),  VARCHAR(30), VARCHAR(10)
>
>
> Table 2
>
> BIGINT (index), FLOAT
>
>
> For a particular data set Table1 has 1165 rows and Table 2 has 323  
> rows,
> however typically Table 2 becomes bigger for larger models. The size  
> on disk
> of this file is 11.8 Mb (compared to 1.7 Mb for our proprietary  
> format). I
> have noticed that if I drop the indexes the size drops dramatically -
> however the query performance suffers to an unacceptable level.
>
> For a larger model the DB footprint is 2.2 Gb compared to 267 Mb for  
> the
> proprietary format.
>
> Does anybody have any comments on this? Are there any configuration  
> options
> or ideas I could use to reduce the footprint of the db file?


I don't think you'll be able to make SQLite as efficient (regarding  
storage size) as a custom file format, because it has to have some  
overhead for indexes, etc.

However, one thing that comes to mind is the way string data is stored:
If you're concerned about disk space an your string data is mostly  
ASCII, make sure your strings are stored as UTF-8 - for ASCII string  
data, this will save you one byte per character in the string data  
storage.
To enforce UTF-8 string storage, execute "PRAGMA encoding='UTF-8'" as  
the first command when creating the database (before you create and  
tables).
You can query the format using "PRAGMA encoding" - UTF-16 encodings  
will store two bytes / character, regardless of the actual characters...

Note that this doesn't mean your database size will shrink to half the  
size - it merely means you'll be able to fit more rows onto a single  
page, thus eventually you should see a decrease in file size when  
comparing UTF-16 vs. UTF-8 databases.

BTW: are you aware that SQLite database won't shrink by themselves?  
You'll have to vacuum them to reclaim unused space (see
)

HTH,


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