RE: [sqlite] sqlite 3.x and Win Server 2003 SP1 not working

[Bob Dankert]

I am using a fully patched/updated VS 2003 and have never had an issue
with sqlite on any OS, including Win 2003 (SP1 and pre-SP1).  I am still
using a slightly older 3.x build, however, and have not tested the
newest build.  Also, I have some code modifications in my sqlite dll but
I doubt it would affect the issue you are seeing.

Bob

Envision Information Technologies
Associate
[EMAIL PROTECTED]
v. 608.256.5680
f. 608.256.3780
 

-Original Message-
From: Derek Shaw [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, July 12, 2005 6:10 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] sqlite 3.x and Win Server 2003 SP1 not working

Yeah, :) I just found those. 

Don't quite have it hooked in yet. But we shall see. I spoke with MS,
they are saying that what is happening with the gcc gened dll is that
when the dll loads it has to get relocated, when the dll tables get
fixed up there are a couple of entries that point outside itself. This
seems to point to a gcc bug to me, but who knows. If the vc build still
has the same problem they are going to take a look at it for me :)

I assume you are running under SP1? Everything works fine?

~derek

-Original Message-
From: Bob Dankert [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, July 12, 2005 4:01 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] sqlite 3.x and Win Server 2003 SP1 not working

I followed these instructions for VS 2003

http://www.sqlite.org/cvstrac/wiki?p=HowToCompileWithVsNet

Bob

Envision Information Technologies
Associate
[EMAIL PROTECTED]
v. 608.256.5680
f. 608.256.3780
 
-Original Message-
From: Derek Shaw [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, July 12, 2005 5:22 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] sqlite 3.x and Win Server 2003 SP1 not working

Peerrrfect. I will give that a shot, I was trying to figure out if the
preprocessed files might get in the way of the dll config. But perhaps
not.

I am using VS.NET 2003. 

I'll let you know what happens, thanks.

~derek.

-Original Message-
From: Tim McDaniel [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, July 12, 2005 3:16 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] sqlite 3.x and Win Server 2003 SP1 not working

Which version of VC++ do you have?  I'm using VS.NET 2003.
In any case, it isn't diffcult.  You can download the "pre-processed"
source for Windows from sqlite.org.  Create a dll project, and add the
source files, including sqlite3.def, and excluding shell.c.  I specify
the NO_TCL macro to omit any TCL related stuff, but I don't remember if
that is essential.

> -Original Message-
> From: Derek Shaw [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, July 12, 2005 5:01 PM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] sqlite 3.x and Win Server 2003 SP1 not working
> 
> That would be great for me... How are you building it? Just 
> down load the source and point a project at it? I can imagine 
> its going to be that easy? I could not find any documentation 
> on building with VC++. 
> 
> I am working on upgrading to 3.2.2, I doubt its going to make 
> a difference, but you never know. I just download the dll and 
> run lib against it to get the lib and such. Then soft link to 
> it that way.
> 
> I would not be surprised to find out that it has something to 
> do with the gcc build...
> 
> ~derek.
> 
> -Original Message-
> From: Tim McDaniel [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, July 12, 2005 2:54 PM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] sqlite 3.x and Win Server 2003 SP1 not working
> 
> 
> > -Original Message-
> > From: D. Richard Hipp [mailto:[EMAIL PROTECTED] 
> > Sent: Tuesday, July 12, 2005 1:20 PM
> > To: sqlite-users@sqlite.org
> > Subject: Re: [sqlite] sqlite 3.x and Win Server 2003 SP1 not working
> > 
> > On Tue, 2005-07-12 at 11:01 -0700, Derek Shaw wrote:
> > > SQLite 3 relocates 2 addresses out of its memory bounds 
> > when it loads.
> > 
> > Can you explan in more detail what this means?
> > --
> > D. Richard Hipp <[EMAIL PROTECTED]>
> > 
> 
> If this is really the problem, then it's probably some "problem" with
> GCC compiling for Win32, or maybe some compile switch needs to be
> adjusted.  I don't have any details, I've only compiled sqlite with
> Visual C++.
> 

RE: [sqlite] sqlite 3.x and Win Server 2003 SP1 not working

[Derek Shaw]

Yeah, :) I just found those. 

Don't quite have it hooked in yet. But we shall see. I spoke with MS,
they are saying that what is happening with the gcc gened dll is that
when the dll loads it has to get relocated, when the dll tables get
fixed up there are a couple of entries that point outside itself. This
seems to point to a gcc bug to me, but who knows. If the vc build still
has the same problem they are going to take a look at it for me :)

I assume you are running under SP1? Everything works fine?

~derek

-Original Message-
From: Bob Dankert [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, July 12, 2005 4:01 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] sqlite 3.x and Win Server 2003 SP1 not working

I followed these instructions for VS 2003

http://www.sqlite.org/cvstrac/wiki?p=HowToCompileWithVsNet

Bob

Envision Information Technologies
Associate
[EMAIL PROTECTED]
v. 608.256.5680
f. 608.256.3780
 
-Original Message-
From: Derek Shaw [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, July 12, 2005 5:22 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] sqlite 3.x and Win Server 2003 SP1 not working

Peerrrfect. I will give that a shot, I was trying to figure out if the
preprocessed files might get in the way of the dll config. But perhaps
not.

I am using VS.NET 2003. 

I'll let you know what happens, thanks.

~derek.

-Original Message-
From: Tim McDaniel [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, July 12, 2005 3:16 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] sqlite 3.x and Win Server 2003 SP1 not working

Which version of VC++ do you have?  I'm using VS.NET 2003.
In any case, it isn't diffcult.  You can download the "pre-processed"
source for Windows from sqlite.org.  Create a dll project, and add the
source files, including sqlite3.def, and excluding shell.c.  I specify
the NO_TCL macro to omit any TCL related stuff, but I don't remember if
that is essential.

> -Original Message-
> From: Derek Shaw [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, July 12, 2005 5:01 PM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] sqlite 3.x and Win Server 2003 SP1 not working
> 
> That would be great for me... How are you building it? Just 
> down load the source and point a project at it? I can imagine 
> its going to be that easy? I could not find any documentation 
> on building with VC++. 
> 
> I am working on upgrading to 3.2.2, I doubt its going to make 
> a difference, but you never know. I just download the dll and 
> run lib against it to get the lib and such. Then soft link to 
> it that way.
> 
> I would not be surprised to find out that it has something to 
> do with the gcc build...
> 
> ~derek.
> 
> -Original Message-
> From: Tim McDaniel [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, July 12, 2005 2:54 PM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] sqlite 3.x and Win Server 2003 SP1 not working
> 
> 
> > -Original Message-
> > From: D. Richard Hipp [mailto:[EMAIL PROTECTED] 
> > Sent: Tuesday, July 12, 2005 1:20 PM
> > To: sqlite-users@sqlite.org
> > Subject: Re: [sqlite] sqlite 3.x and Win Server 2003 SP1 not working
> > 
> > On Tue, 2005-07-12 at 11:01 -0700, Derek Shaw wrote:
> > > SQLite 3 relocates 2 addresses out of its memory bounds 
> > when it loads.
> > 
> > Can you explan in more detail what this means?
> > --
> > D. Richard Hipp <[EMAIL PROTECTED]>
> > 
> 
> If this is really the problem, then it's probably some "problem" with
> GCC compiling for Win32, or maybe some compile switch needs to be
> adjusted.  I don't have any details, I've only compiled sqlite with
> Visual C++.
> 

RE: [sqlite] sqlite 3.x and Win Server 2003 SP1 not working

[Bob Dankert]

I followed these instructions for VS 2003

http://www.sqlite.org/cvstrac/wiki?p=HowToCompileWithVsNet

Bob

Envision Information Technologies
Associate
[EMAIL PROTECTED]
v. 608.256.5680
f. 608.256.3780
 
-Original Message-
From: Derek Shaw [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, July 12, 2005 5:22 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] sqlite 3.x and Win Server 2003 SP1 not working

Peerrrfect. I will give that a shot, I was trying to figure out if the
preprocessed files might get in the way of the dll config. But perhaps
not.

I am using VS.NET 2003. 

I'll let you know what happens, thanks.

~derek.

-Original Message-
From: Tim McDaniel [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, July 12, 2005 3:16 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] sqlite 3.x and Win Server 2003 SP1 not working

Which version of VC++ do you have?  I'm using VS.NET 2003.
In any case, it isn't diffcult.  You can download the "pre-processed"
source for Windows from sqlite.org.  Create a dll project, and add the
source files, including sqlite3.def, and excluding shell.c.  I specify
the NO_TCL macro to omit any TCL related stuff, but I don't remember if
that is essential.

> -Original Message-
> From: Derek Shaw [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, July 12, 2005 5:01 PM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] sqlite 3.x and Win Server 2003 SP1 not working
> 
> That would be great for me... How are you building it? Just 
> down load the source and point a project at it? I can imagine 
> its going to be that easy? I could not find any documentation 
> on building with VC++. 
> 
> I am working on upgrading to 3.2.2, I doubt its going to make 
> a difference, but you never know. I just download the dll and 
> run lib against it to get the lib and such. Then soft link to 
> it that way.
> 
> I would not be surprised to find out that it has something to 
> do with the gcc build...
> 
> ~derek.
> 
> -Original Message-
> From: Tim McDaniel [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, July 12, 2005 2:54 PM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] sqlite 3.x and Win Server 2003 SP1 not working
> 
> 
> > -Original Message-
> > From: D. Richard Hipp [mailto:[EMAIL PROTECTED] 
> > Sent: Tuesday, July 12, 2005 1:20 PM
> > To: sqlite-users@sqlite.org
> > Subject: Re: [sqlite] sqlite 3.x and Win Server 2003 SP1 not working
> > 
> > On Tue, 2005-07-12 at 11:01 -0700, Derek Shaw wrote:
> > > SQLite 3 relocates 2 addresses out of its memory bounds 
> > when it loads.
> > 
> > Can you explan in more detail what this means?
> > --
> > D. Richard Hipp <[EMAIL PROTECTED]>
> > 
> 
> If this is really the problem, then it's probably some "problem" with
> GCC compiling for Win32, or maybe some compile switch needs to be
> adjusted.  I don't have any details, I've only compiled sqlite with
> Visual C++.
> 

RE: [sqlite] sqlite 3.x and Win Server 2003 SP1 not working

[Derek Shaw]

Peerrrfect. I will give that a shot, I was trying to figure out if the
preprocessed files might get in the way of the dll config. But perhaps
not.

I am using VS.NET 2003. 

I'll let you know what happens, thanks.

~derek.

-Original Message-
From: Tim McDaniel [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, July 12, 2005 3:16 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] sqlite 3.x and Win Server 2003 SP1 not working

Which version of VC++ do you have?  I'm using VS.NET 2003.
In any case, it isn't diffcult.  You can download the "pre-processed"
source for Windows from sqlite.org.  Create a dll project, and add the
source files, including sqlite3.def, and excluding shell.c.  I specify
the NO_TCL macro to omit any TCL related stuff, but I don't remember if
that is essential.

> -Original Message-
> From: Derek Shaw [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, July 12, 2005 5:01 PM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] sqlite 3.x and Win Server 2003 SP1 not working
> 
> That would be great for me... How are you building it? Just 
> down load the source and point a project at it? I can imagine 
> its going to be that easy? I could not find any documentation 
> on building with VC++. 
> 
> I am working on upgrading to 3.2.2, I doubt its going to make 
> a difference, but you never know. I just download the dll and 
> run lib against it to get the lib and such. Then soft link to 
> it that way.
> 
> I would not be surprised to find out that it has something to 
> do with the gcc build...
> 
> ~derek.
> 
> -Original Message-
> From: Tim McDaniel [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, July 12, 2005 2:54 PM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] sqlite 3.x and Win Server 2003 SP1 not working
> 
> 
> > -Original Message-
> > From: D. Richard Hipp [mailto:[EMAIL PROTECTED] 
> > Sent: Tuesday, July 12, 2005 1:20 PM
> > To: sqlite-users@sqlite.org
> > Subject: Re: [sqlite] sqlite 3.x and Win Server 2003 SP1 not working
> > 
> > On Tue, 2005-07-12 at 11:01 -0700, Derek Shaw wrote:
> > > SQLite 3 relocates 2 addresses out of its memory bounds 
> > when it loads.
> > 
> > Can you explan in more detail what this means?
> > --
> > D. Richard Hipp <[EMAIL PROTECTED]>
> > 
> 
> If this is really the problem, then it's probably some "problem" with
> GCC compiling for Win32, or maybe some compile switch needs to be
> adjusted.  I don't have any details, I've only compiled sqlite with
> Visual C++.
> 

RE: [sqlite] sqlite 3.x and Win Server 2003 SP1 not working

[Tim McDaniel]

Which version of VC++ do you have?  I'm using VS.NET 2003.
In any case, it isn't diffcult.  You can download the "pre-processed"
source for Windows from sqlite.org.  Create a dll project, and add the
source files, including sqlite3.def, and excluding shell.c.  I specify
the NO_TCL macro to omit any TCL related stuff, but I don't remember if
that is essential.

> -Original Message-
> From: Derek Shaw [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, July 12, 2005 5:01 PM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] sqlite 3.x and Win Server 2003 SP1 not working
> 
> That would be great for me... How are you building it? Just 
> down load the source and point a project at it? I can imagine 
> its going to be that easy? I could not find any documentation 
> on building with VC++. 
> 
> I am working on upgrading to 3.2.2, I doubt its going to make 
> a difference, but you never know. I just download the dll and 
> run lib against it to get the lib and such. Then soft link to 
> it that way.
> 
> I would not be surprised to find out that it has something to 
> do with the gcc build...
> 
> ~derek.
> 
> -Original Message-
> From: Tim McDaniel [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, July 12, 2005 2:54 PM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] sqlite 3.x and Win Server 2003 SP1 not working
> 
> 
> > -Original Message-
> > From: D. Richard Hipp [mailto:[EMAIL PROTECTED] 
> > Sent: Tuesday, July 12, 2005 1:20 PM
> > To: sqlite-users@sqlite.org
> > Subject: Re: [sqlite] sqlite 3.x and Win Server 2003 SP1 not working
> > 
> > On Tue, 2005-07-12 at 11:01 -0700, Derek Shaw wrote:
> > > SQLite 3 relocates 2 addresses out of its memory bounds 
> > when it loads.
> > 
> > Can you explan in more detail what this means?
> > --
> > D. Richard Hipp <[EMAIL PROTECTED]>
> > 
> 
> If this is really the problem, then it's probably some "problem" with
> GCC compiling for Win32, or maybe some compile switch needs to be
> adjusted.  I don't have any details, I've only compiled sqlite with
> Visual C++.
> 

RE: [sqlite] sqlite 3.x and Win Server 2003 SP1 not working

[Derek Shaw]

That would be great for me... How are you building it? Just down load
the source and point a project at it? I can imagine its going to be that
easy? I could not find any documentation on building with VC++. 

I am working on upgrading to 3.2.2, I doubt its going to make a
difference, but you never know. I just download the dll and run lib
against it to get the lib and such. Then soft link to it that way.

I would not be surprised to find out that it has something to do with
the gcc build...

~derek.

-Original Message-
From: Tim McDaniel [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, July 12, 2005 2:54 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] sqlite 3.x and Win Server 2003 SP1 not working


> -Original Message-
> From: D. Richard Hipp [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, July 12, 2005 1:20 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] sqlite 3.x and Win Server 2003 SP1 not working
> 
> On Tue, 2005-07-12 at 11:01 -0700, Derek Shaw wrote:
> > SQLite 3 relocates 2 addresses out of its memory bounds 
> when it loads.
> 
> Can you explan in more detail what this means?
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
> 

If this is really the problem, then it's probably some "problem" with
GCC compiling for Win32, or maybe some compile switch needs to be
adjusted.  I don't have any details, I've only compiled sqlite with
Visual C++.

RE: [sqlite] sqlite 3.x and Win Server 2003 SP1 not working

[Tim McDaniel]

> -Original Message-
> From: D. Richard Hipp [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, July 12, 2005 1:20 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] sqlite 3.x and Win Server 2003 SP1 not working
> 
> On Tue, 2005-07-12 at 11:01 -0700, Derek Shaw wrote:
> > SQLite 3 relocates 2 addresses out of its memory bounds 
> when it loads.
> 
> Can you explan in more detail what this means?
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
> 

If this is really the problem, then it's probably some "problem" with
GCC compiling for Win32, or maybe some compile switch needs to be
adjusted.  I don't have any details, I've only compiled sqlite with
Visual C++.

Re: [sqlite] Multi-thread support

[Cory Nelson]

On 7/12/05, Alex Chudnovsky <[EMAIL PROTECTED]> wrote:
> Gé Weijers wrote:
> 
> > No, sqlite3 uses database-level locking, either one process can
> > write, or many processes can read. Good enough for most embedded uses.
> 
> Any plans for internal queueing of queries waiting for their turn to
> write rather than just return database locked?

sqlite 3.x does have the busy timeout, which sounds like a spin lock. 
maybe it does queue things?

> I did implement similar mechanism in C# wrapper on top of ADO.NET
> wrapper, but it seems to me that having this queuing inside sqlite
> itself would be much more efficient and beneficial for all.
> 
> regards
> 
> Alex
> 


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

RE: [sqlite] Ticket 923 status (missing quotes in 2.8.15 .dump...)

[Downey, Shawn]

Does anyone have a work around for ticket 923 (described below)?  Is
this perhaps not considered a bug?  

When dumping an Sqlite 2.8.16 database, the data in the dump file is
enclosed in single quotes except for fields containing only digits
[0-9].  This is the case even if the fields are declared VARCHAR.  I
have many fields which have leading zeros.  When I import these fields
into Sqlite 3.x, the leading zeros are discarded.

The only solution I currently have is to make several hundreds of very
careful edits to the dump file to insert single quotes around the
numbers with leading zeros prior to importing the dump file into Sqlite
3.x.  This method is not fun and is prone to errors.

Dr. Hipp, can you please comment on ticket 923?  Thank you.

Shawn M. Downey
MPR Associates
632 Plank Road, Suite 110
Clifton Park, NY 12065
518-371-3983 x3 (work)
860-508-5015 (cell)


-Original Message-
From: Downey, Shawn 
Sent: Monday, July 11, 2005 4:07 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Ticket 923 status (missing quotes in 2.8.15 .dump...)

Hello,

I recently had some troubles converting a database from v2.8.0 to v3.08
per the following commands (under Windows XP):

sqlite ddg53.db .dump >ddg53.dump
sqlite3 ddg53.db3 ".read ddg53.dump"

The dump of the 2.8.0 database did not enclose varchar fields containing
numbers with leading zeros in single quotes.  As a result of this, the
import into 3.08 dropped the leading zeros on those fields. 

This is exactly the problem identified in ticket number 923.  Does
anyone have a status on this?

Thank you.

 

Shawn M. Downey

MPR Associates

632 Plank Road, Suite 110

Clifton Park, NY 12065

518-371-3983 x3 (work)

860-508-5015 (cell)

 

Re: [sqlite] Multi-thread support

[Alex Chudnovsky]

Gé Weijers wrote:

No, sqlite3 uses database-level locking, either one process can  
write, or many processes can read. Good enough for most embedded uses.


Any plans for internal queueing of queries waiting for their turn to 
write rather than just return database locked?


I did implement similar mechanism in C# wrapper on top of ADO.NET 
wrapper, but it seems to me that having this queuing inside sqlite 
itself would be much more efficient and beneficial for all.


regards

Alex

Re: [sqlite] sqlite 3.x and Win Server 2003 SP1 not working

[D. Richard Hipp]

On Tue, 2005-07-12 at 11:01 -0700, Derek Shaw wrote:
> SQLite 3 relocates 2 addresses out of its memory bounds when it
> loads. 

Can you explan in more detail what this means?
-- 
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Multi-thread support

[Gé Weijers]

Roushan,

No, sqlite3 uses database-level locking, either one process can  
write, or many processes can read. Good enough for most embedded uses.


Gé

On Jul 12, 2005, at 3:12 AM, Roushan Ali wrote:


Hi all,
  Can anybody tell me how to handle concurrent write using   
sqlite3

database ? Does sqlite3 use table level locking ?


Regards,
Roushan






--
Gé Weijers
e-mail: [EMAIL PROTECTED]

[sqlite] sqlite 3.x and Win Server 2003 SP1 not working

[Derek Shaw]

Hi There,

 

After quite a bit of work and the involvement of Microsoft (tunneling
through support) I have been told that the problem with our product is
that SQLite 3 relocates 2 addresses out of its memory bounds when it
loads. Windows 2003 Server SP1 now requires dll's to relocate. The bad
addresses were never ok on previous OS's but were not disallowed. Now
under SP1, this can no longer be done.

 

Has anyone out there tried 3.x on Server 2003 SP1? I am hesitant to
download all the source and such in an attempt to track this and find a
fix. I will if I must, but I don't have any of the correct tools needed
to build. We were just using the pre-built binaries for win32.

 

Thanks,

 

Derek Shaw

 

[EMAIL PROTECTED]

 

 

Re: [sqlite] Saving "Unsigned Long"s in database

[D. Richard Hipp]

On Tue, 2005-07-12 at 18:28 +0200, Gotzon Astondoa wrote:
> This is the number that i don´t understand. Because:
> (28.672  - 12.288)/510 = 31 bytes per row.
> I think it will be:
> 4 bytes for the group_id
> 4 bytes for the domain
> 4 bytes for the idx_domain
> --
>   12 bytes in total
> 
>   Where are the rest of the bytes (23 bytes)?
> 

The program sqlite3_analyzer available on the SQLite download
page will give you a detailed account of out disk space is
used for any database.  Do not be confused by the name - someone
else has written a completely different program by the same name.
You want the "sqlite3_analyzer" that appears on on
http://www.sqlite.org/download.html
-- 
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Saving "Unsigned Long"s in database

[Jay Sprenkle]

> I create the database in this way:
> 
> PRAGMA page_size=4096;
> CREATE TABLE domains (group_id NUMERIC, domain NUMERIC);
> CREATE INDEX idx_domain ON domains (domain ASC);
> 
> I create an index in domain because i want to make sentences where the WHERE
> clause will appear  in this way
> SELECT group_id WHERE domain=IPnumber
> 
> 
> The database empty has 12.288 bytes.
> Then i insert 510 IP values (nothing in group_id for the moment).
> Now the database size is 28.672 bytes.
> This is the number that i don´t understand. Because:
> (28.672  - 12.288)/510 = 31 bytes per row.
> I think it will be:
> 4 bytes for the group_id
> 4 bytes for the domain
> 4 bytes for the idx_domain
> --
>   12 bytes in total
> 
>   Where are the rest of the bytes (23 bytes)?

The btree will take up some space but it shouldn't grow linearly as
you add records.
Why not write a simple program to enter 50 records and see how large it is?

Re: [sqlite] Saving "Unsigned Long"s in database

[Gotzon Astondoa]

OK, OK: i correct  my bad programing  style.
And... forget the use of length. I´m new in this ;-).

But the problem remains. I will explain in in another way.

I create the database in this way:

PRAGMA page_size=4096;
CREATE TABLE domains (group_id NUMERIC, domain NUMERIC);
CREATE INDEX idx_domain ON domains (domain ASC);

I create an index in domain because i want to make sentences where the WHERE
clause will appear  in this way
SELECT group_id WHERE domain=IPnumber


The database empty has 12.288 bytes.
Then i insert 510 IP values (nothing in group_id for the moment).
Now the database size is 28.672 bytes.
This is the number that i don´t understand. Because:
(28.672  - 12.288)/510 = 31 bytes per row.
I think it will be:
4 bytes for the group_id
4 bytes for the domain
4 bytes for the idx_domain
--
  12 bytes in total

  Where are the rest of the bytes (23 bytes)?

I need to resolve this because in the future  i will insert 500.000 IP
values or more and the database size will become enormous...(15 MB is too
big for me, 5 MB is OK).

Re: [sqlite] Saving "Unsigned Long"s in database

[Derrell . Lipman]

Jay Sprenkle <[EMAIL PROTECTED]> writes:

> On 7/12/05, Gotzon Astondoa <[EMAIL PROTECTED]> wrote:
>> ...
>> struct hostent *hep;
>> ...
>> hep=gethostbyname(DomainTxt);
>> ...
>>  strcpy(txtIP, inet_ntoa(*((struct in_addr *)hep->h_addr_list[i])));
>>  ip = inet_addr(txtIP);
>>  ip = ntohl(ip);
>>  strcpy(sql,"INSERT into domains (domain) VALUES ('");
>>  sprintf(nIP,"%u",ip);
>>  strcat(sql,nIP);
>>  strcat(sql, "')");
>
> Try this:
>
> sprintf( sql, "INSERT into domains (domain) VALUES ('%u') ", ip  );

Being pedantic...

  char sql[MAX_QUERY_LEN];
  snprintf(sql, sizeof(sql),
   "INSERT into domains (domain) VALUES ('%lu') ",
   ip);

or

  char * sql;
  if ((sql = sqlite3_mprintf("INSERT into domains (domain) VALUES ('%lu') ",
 ip)) == NULL)
  {
  fatal("out of memory");
  }

  /* use sql pointer (issue query) */

  sqlite3_free(sql);

Note 1: snprintf() instead of sprintf() to ensure that you don't overrun the
sql buffer.  This function takes the size of the buffer as the second
parameter.  Alternatively, use sqlite3_mprintf() to allocate a buffer of an
appropriate length (containing the query), and then free it when done with the
query.

Note 2: Since you stated explicitely that you were treating the IP address as
a "long", then "%lu" instead of "%u" allows for the possibility that "int" and
"long" are different sizes, which of course, depends on the compiler you're
using.  Using "%lu" is more technically correct if "ip" is declared as "long".


The question remains, though as to why you are using LENGTH() on a numeric
field.  LENGTH() is intended for strings, and presumably type-casts the
numeric value to a string when you call it.


Cheers,

Derrell

Re: [sqlite] Saving "Unsigned Long"s in database

[Jay Sprenkle]

On 7/12/05, Gotzon Astondoa <[EMAIL PROTECTED]> wrote:
> Hi all:
> 
>   I need to make an IP database. They will be stored like "unsigned long"
> variables, so, their size will be 4 bytes.
> 
>   The IP will be stored in the "domain" field.
>   The "field type" of domain is NUMERIC.
> 
>   I save the data from a C program.
>   This is part of the code: (Note that DomainTxt is a correct domain like
> "www.google.com" or similar)
> ...
> struct hostent *hep;
> ...
> hep=gethostbyname(DomainTxt);
> ...
>  strcpy(txtIP, inet_ntoa(*((struct in_addr *)hep->h_addr_list[i])));
>  ip = inet_addr(txtIP);
>  ip = ntohl(ip);
>  strcpy(sql,"INSERT into domains (domain) VALUES ('");
>  sprintf(nIP,"%u",ip);
>  strcat(sql,nIP);
>  strcat(sql, "')");

Try this:

sprintf( sql, "INSERT into domains (domain) VALUES ('%u') ", ip  );

instead of this:
>  strcpy(sql,"INSERT into domains (domain) VALUES ('");
>  sprintf(nIP,"%u",ip);
>  strcat(sql,nIP);
>  strcat(sql, "')");





>  rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
> 
>   All goes fine: when i make a select sentence i can see that all the values
> are correctly saved. But if i make this sentence:
> 
>  SELECT avg(length(domain)) from domains;
> 
>The response is 10 and not 4.
> 
>¿Has had someone this problem before? ¿what i´m doing bad?

domain is numeric, why are you getting the length of a numeric field?
length is for strings.

Re: [sqlite] Saving "Unsigned Long"s in database

[Kurt Welgehausen]

>  SELECT avg(length(domain)) from domains;
>
>   The response is 10 and not 4.

>From :

  length(X)  Return the string length of X in characters.
 If SQLite is configured to support UTF-8, then
 the number of UTF-8 characters is returned,
 not the number of bytes.

Re: [sqlite] beginnings with sqlite

[LURKIN Denis]

Ok I found my error, when I launched the application with the
Terminal, the executable and the data base were at the same place
whereas with XCode there were no data base at the place of the
executable. The application thus created a data base with the name
which I passed in parameter but this DB was empty ;-).
Stupid fault which takes time!

Denis Lurkin.

[sqlite] Saving "Unsigned Long"s in database

[Gotzon Astondoa]

Hi all:

  I need to make an IP database. They will be stored like "unsigned long"
variables, so, their size will be 4 bytes.

  The IP will be stored in the "domain" field.
  The "field type" of domain is NUMERIC.

  I save the data from a C program.
  This is part of the code: (Note that DomainTxt is a correct domain like
"www.google.com" or similar)
...
struct hostent *hep;
...
hep=gethostbyname(DomainTxt);
...
 strcpy(txtIP, inet_ntoa(*((struct in_addr *)hep->h_addr_list[i])));
 ip = inet_addr(txtIP);
 ip = ntohl(ip);
 strcpy(sql,"INSERT into domains (domain) VALUES ('");
 sprintf(nIP,"%u",ip);
 strcat(sql,nIP);
 strcat(sql, "')");
 rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);

  All goes fine: when i make a select sentence i can see that all the values
are correctly saved. But if i make this sentence:

 SELECT avg(length(domain)) from domains;

   The response is 10 and not 4.

   ¿Has had someone this problem before? ¿what i´m doing bad?


Thanks to all in advance.

Re: [sqlite] update statements inside select...case inside triggers

[Dan Kennedy]

 
> CREATE TRIGGER trigger_name AFTER UPDATE ON table
> BEGIN
>   SELECT CASE
> WHEN a_condition THEN
>   UPDATE another_table SET a_field=a_value WHERE yet_another_condition
> WHEN another_conditon THEN
>   UPDATE another_table SET a_field=another_value WHERE 
> yet_another_condition
> END; 
> END
> 
> When I try to create the trigger it returns an error before update (or
> similar - sorry do not have access to my PC at the moment to get the
> exact error).
> 
> Can sqlite3 do the above?

I don't think that's regular SQL. You could create two triggers and put
the condition as a WHEN clause on the trigger. i.e.:

CREATE TRIGGER trigger_name AFTER UPDATE ON table
  WHEN (some_condition) BEGIN
UPDATE another_table SET a_field=a_value WHERE yet_another_condition
  END;

And another similar trigger for the other with "WHEN (!some_condition)".






Sell on Yahoo! Auctions – no fees. Bid on great items.  
http://auctions.yahoo.com/

[sqlite] Multi-thread support

[Roushan Ali]

Hi all,
  Can anybody tell me how to handle concurrent write using  sqlite3
database ? Does sqlite3 use table level locking ?


Regards,
Roushan

Re: [sqlite] Does SQLite have a fulltext search like MySQL?

[Lothar Märkle]

On Tue, Jul 12, 2005 at 01:26:19PM +0530, Siddharth Vijayakrishnan wrote:
> On 7/11/05, Henry Miller <[EMAIL PROTECTED]> wrote:
> > For full text search I find the lucene/clucene
> > http://lucene.apache.org/
> > http://sourceforge.net/projects/clucene/
> > 
> > to be good solutions.   The licenses isn't quite as easy as sqlite, but
> > they should be good enough for everyone.
There is also swish-e, http://swish-e.org, which is written in C.
Afaict it is faster in searching and building the fulltext index than mysql, but
has some minor problems with index updates.

lothar

Re: [sqlite] Does SQLite have a fulltext search like MySQL?

[Siddharth Vijayakrishnan]

On 7/11/05, Henry Miller <[EMAIL PROTECTED]> wrote:
> For full text search I find the lucene/clucene
> http://lucene.apache.org/
> http://sourceforge.net/projects/clucene/
> 
> to be good solutions.   The licenses isn't quite as easy as sqlite, but
> they should be good enough for everyone.

Clucene is actually written in C++. There is another project lucene4C
- http://incubator.apache.org/lucene4c/ - but this too has
transitioned from a completely C implementation to a wrapper over a
gcj compiled java version. Using either of these implementations as a
reference, it is possible to use lucene to provide all the services
mentioned in this mail inside the DB itself

http://www.mail-archive.com/sqlite-users@sqlite.org/msg00993.html

Lucene could be used to build the column index and this index can be
queried later using extensions to SQL like CONTAINS.

I would like to contribute / test code for this - but before that I'd
like to know if there is a good case for doing this.

/Siddharth

Re: [sqlite] beginnings with sqlite

[LURKIN Denis]

Hello,

Thanks for replied !

I am sure that XCode loads the good name of the DB.

I tried with another name for my DB and with XCode I have the same 
problem.


With the Terminal, when I try with the name without extention I receive 
the error message but a file with the name of the DB without extention 
is also created.


I use the sources files of SQLITE 3.2.2, then I don't know why it 
doesn't work !


Thank you in advance,

Denis Lurkin.