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

[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

[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

[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

[Michael Ruck]

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

Re: [sqlite] rtree cast warnings on 64bit OS - strange parameter use

[Michael Ruck]

I just wanted to explain the reason of the warning. There's valid  
reason to emit these warnings as the compiler can not infer the usage  
from static type analysis. I did not specifically call this use wrong.

Mike

Am 25.10.2008 um 19:44 schrieb D. Richard Hipp:

>
> On Oct 25, 2008, at 1:28 PM, Michael Ruck wrote:
>
>> The problem with these types of C tricks is that they only work
>> right if the
>> platform they're used on has the property of sizeof(void*) ==
>> sizeof(int).
>> Unfortunately this is not always the case and not mandated by the C
>> standard. That's the reason for these warnings. Its also a reason
>> not to
>> turn off the warnings.
>>
>
> Dan is using the void* to hold a 0 or a 1.  So his code will work
> correctly as long as sizeof(void*)>=1.  It would be a strange machine
> indeed that failed to meet that requirement.
>
> D. Richard Hipp
> [EMAIL PROTECTED]
>
>
>
> ___
> 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] rtree cast warnings on 64bit OS - strange parameter use

[Michael Ruck]

The problem with these types of C tricks is that they only work right if the
platform they're used on has the property of sizeof(void*) == sizeof(int).
Unfortunately this is not always the case and not mandated by the C
standard. That's the reason for these warnings. Its also a reason not to
turn off the warnings.

Mike

> -Ursprüngliche Nachricht-
> Von: [EMAIL PROTECTED] 
> [mailto:[EMAIL PROTECTED] Im Auftrag von Dan
> Gesendet: Samstag, 25. Oktober 2008 19:22
> An: General Discussion of SQLite Database
> Betreff: Re: [sqlite] rtree cast warnings on 64bit OS - 
> strange parameter use
> 
> 
> On Oct 25, 2008, at 11:40 PM, William Kyngesburye wrote:
> 
> > I added rtree to my sqlite compilation for the first time and got 
> > these warnings for OSX 64bit:
> >
> > /Users/Shared/src/sqlite/sqlite-3.6.4/sqlite3.c: In function
> > ‘rtreeCreate’:
> > /Users/Shared/src/sqlite/sqlite-3.6.4/sqlite3.c:94784: 
> warning: cast 
> > from pointer to integer of different size
> > /Users/Shared/src/sqlite/sqlite-3.6.4/sqlite3.c: In function
> > ‘rtreeConnect’:
> > /Users/Shared/src/sqlite/sqlite-3.6.4/sqlite3.c:94797: 
> warning: cast 
> > from pointer to integer of different size
> >
> > These are in the rtreeCreate() and rtreeConnect() functions, calling
> > rtreeInit():
> >
> > return rtreeInit(db, pAux, argc, argv, ppVtab, pzErr, 1, (int)pAux);
> >
> > I thought the two pAux parameters were odd - one bare and 
> one cast to 
> > (int), so I looked up rtreeInit().
> 
> Good point. I removed the first of the two "pAux" parameters 
> from rtreeInit(). It was not being used.
> 
>http://www.sqlite.org/cvstrac/chngview?cn=5842
> 
> > static int rtreeInit(sqlite3 *db, void *pAux, int argc, const char 
> > *const*argv, sqlite3_vtab **ppVtab, char **pzErr, int isCreate, int
> > eCoordType)
> >
> > The first pAux is a pointer, so this one looks correct.  But the 
> > second is an int (eCoordType), and the only two values I 
> found defined 
> > are 0 & 1:
> >
> > #define RTREE_COORD_REAL32 0
> > #define RTREE_COORD_INT32  1
> >
> > Forget the cast warnings now - why is pAux used to set the 
> eCoordType?  
> > My C skills are pretty basic, so maybe there is some pointer/cast 
> > magic happening?  Or maybe it's simply screwed up?
> 
> The two functions that call rtreeInit() are registered as 
> callbacks with SQLite. When you register the callback 
> function you also specify a void* pointer that is passed to 
> the callback whenever it is invoked.
> This is not an uncommon pattern in C code.
> 
> So, since the interface allows us to pass a void* as context 
> to the callback function, but in this instance we really just 
> want an integer, the value has to be cast to a void* when the 
> callback function is registered, and back to an integer when 
> the callback is invoked.
> 
> A lot of compilers throw a warning when they encounter this. 
> In my opinion (having never had anything to do with compiler design or
> implementation) they shouldn't.
> 
> Dan.
> 
> ___
> 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] SQLite vs. quad-cores

[Michael Ruck]

I would suggest you force your software to use only one processor on both
dual- and quad-core machines and check the timings again. If the timings are
in the range of expectancy (due to hardware speed/processor frequency) then
your software has a bug related to NUMA. The non-uniform memory architecture
used by AMD since the Athlon64/Opteron line penalizes non-local memory
access - especially in an SMP scenario.

Mike 

> -Ursprüngliche Nachricht-
> Von: [EMAIL PROTECTED] 
> [mailto:[EMAIL PROTECTED] Im Auftrag von 
> Benilton Carvalho
> Gesendet: Montag, 8. September 2008 21:20
> An: sqlite-users@sqlite.org
> Betreff: [sqlite] SQLite vs. quad-cores
> 
> Hi everyone,
> 
> I'm a BioConductor developer and we use SQLite (via RSQLite 
> package) to handle annotation of high-density oligonucleotide 
> microarrays.
> 
> Our solution, implemented in the pdInfoBuilder package, 
> worked great until we got machines with quad-core processors. 
> One particular setup is a machine with 2 Quad-Core AMD 
> Opteron Processor 2356. The processes that usually took
> 1-2 hours on older machines (dual-cores AMD), now take 6-8 
> hours on the new computers.
> 
> We thought there would be something wrong with our switch 
> (given the particular network scenario we have here), and 
> changing it didn't solve the problem. Later, we decided to 
> run everything on local storage (instead of network), and the 
> same behavior was observed again.
> 
> Is there anything else that you guys could recommend?
> 
> thanks a bunch,
> 
> benilton
> ___
> 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] GCC -ffast-math safe version of sqlite3IsNaN

[Michael Ruck]

Have you tried to compile the util.c/amalgamation file without -ffast-math
and
use it with your other sources (compiled with -ffast-math)?

Mike

> -Ursprüngliche Nachricht-
> Von: [EMAIL PROTECTED] 
> [mailto:[EMAIL PROTECTED] Im Auftrag von Brown, Daniel
> Gesendet: Mittwoch, 27. August 2008 00:38
> An: General Discussion of SQLite Database
> Betreff: [sqlite] GCC -ffast-math safe version of sqlite3IsNaN
> 
> Good afternoon list,
> 
> In the process of upgrading to 3.6.1 I've run into the error 
> on line 46 of util.c about int sqlite3IsNaN(double x) not 
> behaving consistently with the GCC  -ffast-math compiler 
> option (which we have enabled), is there any alternative 
> function I could use that would be compatible with GCC 
> -ffast-math?  Removing the -ffast-math option from our 
> projects would be highly undesirable for us, as performance 
> is paramount.
> 
> Cheers,
> 
> Daniel Brown
> "The best laid schemes o' mice an' men, gang aft agley"
> 
> 
> ___
> 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] Proposed removal of (mis-)feature

[Michael Ruck]

Remove it. Better now than never.

Mike 

> -Ursprüngliche Nachricht-
> Von: [EMAIL PROTECTED] 
> [mailto:[EMAIL PROTECTED] Im Auftrag von D. 
> Richard Hipp
> Gesendet: Donnerstag, 7. August 2008 19:26
> An: General Discussion of SQLite Database
> Betreff: [sqlite] Proposed removal of (mis-)feature
> 
> String literals in SQL are suppose to be enclosed in 
> single-quotes - Pascal-style.  Double-quotes are used around 
> table and/or column names in cases where the name would 
> otherwise be a keyword or when the name contains non-standard 
> characters.
> 
> But SQLite tries to be flexible and accommodating.  To this 
> end, it accepts some non-standard quoting mechanisms:
> 
> 1.  Names can be enclosed in [...] for compatibility with 
> Access and SQLServer.
> 2.  Names can be enclosed in grave accents for 
> compatibility with MySQL.
> 3.  Double-quoted names fall back to being string 
> literals if there is no matching table or column name.
> 
> In retrospect, (3) seems to be a bad idea.  It is 
> accident-prone and leads to all kinds of confusion.  For 
> example, if double-quotes are being used correctly (which is 
> to say to quote table or column names) but a misspelling 
> occurs in the name, the token reverts to being a string 
> literal rather than throwing an error.  Or if a double-quoted 
> string really is being used as a string literal, but later a 
> new column is added to a table that has the same name as the 
> string text, the string literal will suddenly take on the 
> value of the column.  It seems like we have one or two 
> problem reports per month on this mailing list that involve 
> people using double-quoted names where they should be using 
> single-quoted string literals instead.
> 
> So I'm giving some thought to removing feature (3) above and 
> disallowing double-quoted string literals.  My concern is, 
> though, that this might break many existing applications.
> 
> What opinion do you, gentle users, have of this matter?
> 
> D. Richard Hipp
> [EMAIL PROTECTED]
> 
> 
> 
> ___
> 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] SQLite in DotNet environment and virtual tables

[Michael Ruck]

Hi Sylko,

sqlite3_load_extension is unfortunately not directly usable from .NET. No
.NET language (except MC++) exposes the ability
to export functions from a DLL. In order to use extensions you need to
create at least a small helper DLL, which can then
be used as an adapter or proxy to an extension written in any .NET language.
As this can not be done generically without
a lot of logic it has not been done for any .NET wrapper AFAIK.

Mike

> -Ursprüngliche Nachricht-
> Von: [EMAIL PROTECTED] 
> [mailto:[EMAIL PROTECTED] Im Auftrag von Sylko 
> Zschiedrich
> Gesendet: Mittwoch, 16. April 2008 10:53
> An: sqlite-users@sqlite.org
> Betreff: [sqlite] SQLite in DotNet environment and virtual tables
> 
> Hi all,
> 
> we are using SQLite in a DotNet environment and want to 
> support virtual tables via sqlite3_load_extension.
> 
> Did anybody know a wrapper for SQLite or sample code for 
> implementing virtual tables in DetNet. (C#)?
> 
> I know there are many DotNet wrappers for SQLite but noone 
> supports virtual tables. :-( 
> 
> 
> Thanks
> Sylko
> ___
> 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] SQLite>=3.5.6 does not open database file

[Michael Ruck]

Is there a previous version, which is able to open this database file? Which
version of SQLite was the database file created with? 

-Ursprüngliche Nachricht-
Von: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Im Auftrag von Steve Topov
Gesendet: Montag, 14. April 2008 22:31
An: sqlite-users@sqlite.org
Betreff: [sqlite] SQLite>=3.5.6 does not open database file

I already reported this bug but did not receive any responds so I am trying
one more time.
The problem:
SQLite version 3.5.6 and up returns an error when opening some database
files. The error is “SQL logic error or missing database”.
Version prior to version 3.5.6 works fine with these database files.
 
I do not know the version of SQLite the database file in question was
created with. And I am talking about SQLite 3 database file – not SQLite 2,
not Microsoft Access, not any other file format.
 
To make story short: SQLite 3.5.6 can not open SQLite 3 database file.
 
Thanks for your time and possible help.
___
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] reading MS Access 97 files

[Michael Ruck]

Even though I haven't done anything with this stuff for almost 6 years, I
haven't forgotten this stuff. But I intentionally left out a couple of short
lived technologies... :(

Mike 

-Ursprüngliche Nachricht-
Von: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Im Auftrag von Fred J. Stephens
Gesendet: Freitag, 11. April 2008 04:36
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] reading MS Access 97 files

Rich Shepard wrote:
> On Thu, 10 Apr 2008, Fred J. Stephens wrote:
> 
>> But to us former Access users, "database" is quick shorthand.
> 
> Fred,
> 
>Access is a joke. At least, that's what my colleagues who must work 
> in the Microsoft world tell me.
I agree. Thats why I said "former" user. Anyway, I am rusty in Access,
haven't used it since switching to Linux 4-5 years ago. I had forgotten all
about the DAO, ADO stuff that Michael mentioned.

>> And I did say later in the post that sqlite is a database engine.
> 
>That's ambiguous.
If it's good enough for DRH to refer to it thus on his website, it's good
enough for me! :-) I understand the distinction, but I was just trying to
help John get a quick overview of the situation until more knowledgeable
users chimed in.
Now they have, so I will bow out.
Fred Stephens
___
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] reading MS Access 97 files

[Michael Ruck]

John,

If you want to go the ODBC route, then that's the way to go. The Access ODBC
Driver docs can be found at
http://msdn2.microsoft.com/en-us/library/ms710302(VS.85).aspx

Mike

-Ursprüngliche Nachricht-
Von: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Im Auftrag von John Grant
Gesendet: Freitag, 11. April 2008 03:19
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] reading MS Access 97 files

Mike,
Thanks for the advice - that's the info I've been trying to find.

When you say 'ODBC API'- is this what you are thinking?
http://msdn2.microsoft.com/en-us/library/ms714562(VS.85).aspx

Thanks,
-John


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Michael Ruck
Sent: Thursday, April 10, 2008 5:58 PM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] reading MS Access 97 files

John,

Access comes with an ODBC driver itself. An alternative would be to access
the MS-Jet-Engine directly. (Jet is the database engine used by Microsoft
Access.)

IMHO you have three/four data access APIs possibilities:

- ODBC
- Jet Engine
- DAO
- ADO (not ADO.NET)

Choose one depending on your circumstances. All of these APIs can natively
access Access databases on Windows if the Microsoft Data Access Components
are installed (those include the Jet-Engine driver.)

Mike

-Ursprüngliche Nachricht-
Von: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Im Auftrag von John Grant
Gesendet: Freitag, 11. April 2008 02:54
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] reading MS Access 97 files

@Fred,
Thanks for the overview!

I'll start looking for an ODBC driver that I can use from C++ (not Managed
C++).  I guess I need a driver that works with Access 97 files.
The code I am porting to C++ makes a "connection" with this string:
Provider=Microsoft.JET.OLEDB.4.0

I hope to find a class library for C++ (so far I've only seen MC++
classes) that allows me to use an interface which is not directly tied to
the driver DLL.  If multiple drivers will do the job, I want my code to be
able to use the "best/default".

-John



-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Fred J. Stephens
Sent: Thursday, April 10, 2008 4:13 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] reading MS Access 97 files

John Grant wrote:
> @All,
> 
> Thanks for the replies.  I would like to say that I am new to this,
and
> I've spent many hours trying to find documentation so that I don't
look
> like an idiot on any forums.  :)  With that said, I'm very confused.
No problem, I am probably going to make one out of myself, but I will try to
help until more competent advice comes along.

> I guess I was under the impression that SQLite was a library for
reading
> databases of various formats. 
No, SQLite IS a database. It can be used to replace others like Access,
MySQL etc. However, (and I went through this confusion when moving to Linux
and away from Access) Access is more than a database engine, it also a
report generator, query creator, form designer, etc.
SQLite is just the database engine (or library or command line tool).
You provide your own code to create the tables, insert data, and work with
it.

  I have a file that can be viewed with MS
> Access.  I have tested this myself.  Open Office immediately closed 
> (probably crashed) when I tried to open the file with its 'Base'
> program.  I need a library that can help me load that same data into
my
> application.  It does not help me to view the data in MS Access or
other
> applications.
What language is your application written in? You may be able to use the
ODBC driver from within that language.

> What do you mean when you say I need a driver?  Are you telling me
that
> SQLite can read the file or not?  If not, do you know of a library
that
> will help me with my task?
An ODBC (Open Database Connectivity) driver lets you read data out of a
database, without having it's native application. I think that is roughly
correct. So, no, SQLite won't open the file and won't help you until you get
the data out of Access format. The only way I know offhand

to do that without exporting it in csv (comma separated values) or
something, is an ODBC driver.
Hope this helps.
Fred Stephens
___
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

___
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

Re: [sqlite] Inserting data containing apostrope

[Michael Ruck]

I don't know Liberty Basic, but the sqlite3 library allows you to prepare
statements and bind variables to them before executing them. This will solve
your problem. The APIs in question are sqlite3_prepare(_v2), sqlite3_bind_*,
sqlite3_step, sqlite3_reset and sqlite3_finalize. Use these functions (in a
loop) to execute your insert(s) statement(s). The other alternative is to
use sqlite3_mprintf to build a proper sql string. Though I wouldn't
recommend doing it.

Mike 

-Ursprüngliche Nachricht-
Von: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Im Auftrag von metro
Gesendet: Freitag, 11. April 2008 03:16
An: sqlite-users@sqlite.org
Betreff: [sqlite] Inserting data containing apostrope

Hi all
very new to this game, I am creating a reverse search phone book for my POS
program I think the problem has something to do with using an ESCAPE clause
but at this stage it's beyond my comprehension I am using Liberty Basic (new
to that too) + dll how do I insert the following

INSERT into phone (INITIALS,NAME,PHONE_NO,SUBURB) Values ('AC',' 
O'Hara','0413000153','Seafort ')

thanks in advance

Laurie





___
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] reading MS Access 97 files

[Michael Ruck]

John,

Access comes with an ODBC driver itself. An alternative would be to access
the MS-Jet-Engine directly. (Jet is the database engine used by Microsoft
Access.)

IMHO you have three/four data access APIs possibilities:

- ODBC
- Jet Engine
- DAO
- ADO (not ADO.NET)

Choose one depending on your circumstances. All of these APIs can natively
access Access databases on Windows if the Microsoft Data Access Components
are
installed (those include the Jet-Engine driver.)

Mike

-Ursprüngliche Nachricht-
Von: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Im Auftrag von John Grant
Gesendet: Freitag, 11. April 2008 02:54
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] reading MS Access 97 files

@Fred,
Thanks for the overview!

I'll start looking for an ODBC driver that I can use from C++ (not Managed
C++).  I guess I need a driver that works with Access 97 files.
The code I am porting to C++ makes a "connection" with this string:
Provider=Microsoft.JET.OLEDB.4.0

I hope to find a class library for C++ (so far I've only seen MC++
classes) that allows me to use an interface which is not directly tied to
the driver DLL.  If multiple drivers will do the job, I want my code to be
able to use the "best/default".

-John



-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Fred J. Stephens
Sent: Thursday, April 10, 2008 4:13 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] reading MS Access 97 files

John Grant wrote:
> @All,
> 
> Thanks for the replies.  I would like to say that I am new to this,
and
> I've spent many hours trying to find documentation so that I don't
look
> like an idiot on any forums.  :)  With that said, I'm very confused.
No problem, I am probably going to make one out of myself, but I will try to
help until more competent advice comes along.

> I guess I was under the impression that SQLite was a library for
reading
> databases of various formats. 
No, SQLite IS a database. It can be used to replace others like Access,
MySQL etc. However, (and I went through this confusion when moving to Linux
and away from Access) Access is more than a database engine, it also a
report generator, query creator, form designer, etc.
SQLite is just the database engine (or library or command line tool).
You provide your own code to create the tables, insert data, and work with
it.

  I have a file that can be viewed with MS
> Access.  I have tested this myself.  Open Office immediately closed 
> (probably crashed) when I tried to open the file with its 'Base'
> program.  I need a library that can help me load that same data into
my
> application.  It does not help me to view the data in MS Access or
other
> applications.
What language is your application written in? You may be able to use the
ODBC driver from within that language.

> What do you mean when you say I need a driver?  Are you telling me
that
> SQLite can read the file or not?  If not, do you know of a library
that
> will help me with my task?
An ODBC (Open Database Connectivity) driver lets you read data out of a
database, without having it's native application. I think that is roughly
correct. So, no, SQLite won't open the file and won't help you until you get
the data out of Access format. The only way I know offhand

to do that without exporting it in csv (comma separated values) or
something, is an ODBC driver.
Hope this helps.
Fred Stephens
___
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

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

Re: [sqlite] WinCE Memory Problem

[Michael Ruck]

You may want to read the docs for the following functions:

- sqlite3_release_memory: http://www.sqlite.org/c3ref/release_memory.html
- sqlite3_soft_heap_limit: http://www.sqlite.org/c3ref/soft_heap_limit.html

Additionally some pragmas may help, if properly used:

http://www.sqlite.org/pragma.html

- pragma cache_size=number-of-pages;
- pragma default-cache-size=number-of-pages;

Additionally you should wrap the insert into a transaction to improve
performance.

Mike

-Ursprüngliche Nachricht-
Von: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Im Auftrag von Yang WenYuan
Gesendet: Mittwoch, 9. April 2008 10:23
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] WinCE Memory Problem

Now, I change my code as follows, but, the problem is still there. The PDA
memory usage become larger and larger. Did I miss something? Anything wrong
with my code?  Thanks.



sqlcmd = sqlite3_mprintf( "INSERT INTO TEST( BARCODE, TAGTYPE,
PRINTDATA ) VALUES( ?, ?, ? );" );

rc = sqlite3_prepare( db, sqlcmd, -1, , 0 );
if( rc != SQLITE_OK )
{
sqlite3_free( sqlcmd );
sqlite3_close(db);
return -1;
}

for( i = 0 ; i < 20; i ++ )
{
// ...
// chrBarCode, tagtype and chrPrintData is changed for every
loop
// ...

rc = sqlite3_bind_text( stat, 1, chrBarCode, strlen(
chrBarCode ), NULL ); 
if( rc != SQLITE_OK )
{
sqlite3_free( sqlcmd );
sqlite3_close(db);
return -1;
}

rc = sqlite3_bind_int( stat, 2, tagtype ); 
if( rc != SQLITE_OK )
{
sqlite3_free( sqlcmd );
sqlite3_close(db);
return -1;
}

rc = sqlite3_bind_blob( stat, 3, chrPrintData, length, NULL
); 
if( rc != SQLITE_OK )
{
sqlite3_free( sqlcmd );
sqlite3_close(db);
return -1;
}

rc = sqlite3_step( stat );

if( rc != SQLITE_DONE )
{
sqlite3_free( sqlcmd );
sqlite3_close(db);
return -1;
}

rc = sqlite3_reset( stat );
if( rc != SQLITE_OK )
{
sqlite3_free( sqlcmd );
sqlite3_close(db);
return -1;
}

}

rc = sqlite3_finalize( stat );
if( rc != SQLITE_OK )
{
sqlite3_free( sqlcmd );
sqlite3_close(db);
return -1;
}



Best Regards,
WenYuan




--- Michael Ruck <[EMAIL PROTECTED]> wrote:

> No, you only need to create the statement once. And bind all variables 
> inside the loop. This at least saves computation time. Additionally 
> you should use sqlite3_mprintf instead of sprintf to protect against 
> sql injection (if that is an issue for you.)
> 
> You're already doing it with the blob, why not with the other fields?
> 
> Mike
> 
> 
> -Ursprüngliche Nachricht-
> Von: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] Im Auftrag von Yang WenYuan
> Gesendet: Mittwoch, 9. April 2008 08:11
> An: General Discussion of SQLite Database
> Betreff: Re: [sqlite] WinCE Memory Problem
> 
> Thanks, Mike. 
> 
> Because, each record has different barcode, tagtype, and printdata. 
> That means, I need to call:
> 
> sprintf( sqlcmd, "INSERT INTO TEST( BARCODE, TAGTYPE, PRINTDATA ) 
> VALUES( %s, %d, ? );", chrBarCode, tagtype ); rc = sqlite3_prepare( 
> db, sqlcmd, -1, , 0 );
> 
> to update the contents of the each records inside the loop. Am I 
> right?
> 
> Any suggestion? Thanks.
> 
> WenYuan
> 
> 
> 
> 
> 
> --- Michael Ruck <[EMAIL PROTECTED]>
> wrote:
> 
> > You should only prepare the statement once before
> the loop. The only
> > thing you should do in the loop itself is bind
> varying data and call
> > sqlite_step.
> > There's no need to call prepare, reset, finalize
> inside the loop. If
> > chrPrintData doesn't change you can also move
> bind_blob in front of
> > the loop.
> > 
> > Mike
> > 
> > -Ursprüngliche Nachricht-
> > Von: [EMAIL PROTECTED] 
> > [mailto:[EMAIL PROTECTED] Im
> Auftrag von Yang WenYuan
> > Gesendet: Mittwoch, 9. April 2008 06:08
> > An: sqlite-users@sqlite.org
> > Betreff: [sqlite] WinCE Memory Problem
> > 
> >

Re: [sqlite] WinCE Memory Problem

[Michael Ruck]

No, you only need to create the statement once. And bind all variables
inside the loop. This at least saves computation time. Additionally you
should use sqlite3_mprintf instead
of sprintf to protect against sql injection (if that is an issue for you.)

You're already doing it with the blob, why not with the other fields?

Mike


-Ursprüngliche Nachricht-
Von: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Im Auftrag von Yang WenYuan
Gesendet: Mittwoch, 9. April 2008 08:11
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] WinCE Memory Problem

Thanks, Mike. 

Because, each record has different barcode, tagtype, and printdata. That
means, I need to call:

sprintf( sqlcmd, "INSERT INTO TEST( BARCODE, TAGTYPE, PRINTDATA ) VALUES(
%s, %d, ? );", chrBarCode, tagtype ); rc = sqlite3_prepare( db, sqlcmd, -1,
, 0 );

to update the contents of the each records inside the loop. Am I right? 

Any suggestion? Thanks.

WenYuan





--- Michael Ruck <[EMAIL PROTECTED]> wrote:

> You should only prepare the statement once before the loop. The only 
> thing you should do in the loop itself is bind varying data and call 
> sqlite_step.
> There's no need to call prepare, reset, finalize inside the loop. If 
> chrPrintData doesn't change you can also move bind_blob in front of 
> the loop.
> 
> Mike
> 
> -Ursprüngliche Nachricht-
> Von: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] Im Auftrag von Yang WenYuan
> Gesendet: Mittwoch, 9. April 2008 06:08
> An: sqlite-users@sqlite.org
> Betreff: [sqlite] WinCE Memory Problem
> 
> I use the Sqlite in the PDA which is WinCE OS. I need to add more than 
> 200,000 records. I used sqlite3_prepare-> 
> sqlite3_bind_blob->sqlite3_step->sqlite3_finalize to write each record 
> in to Database. However, I found that after each record is inserted, 
> the PDA memory became larger and larger. In the end, the whole PDA 
> memory is occupied by this application and the system halt. Is there 
> any thing I miss to release the memory?
> Following is my code:
> 
> sqlite3 *db;
> sqlite3_stmt * stat;
> char *zErrMsg = 0;
> char sqlcmd[ 512 ];
> int rc;
> char chrBarCode[ 16 ], chrPrintData[ 512 ]; int tagtype;
> 
> 
> 
> for( i = 0; i < 200; i ++ )
> { 
>   sprintf( sqlcmd, "INSERT INTO TEST( BARCODE, TAGTYPE, PRINTDATA ) 
> VALUES( %s, %d, ? );", chrBarCode, tagtype );
>   
>   rc = sqlite3_prepare( db, sqlcmd, -1, , 0 );
>   if( rc != SQLITE_OK )
>   {
>   sqlite3_close(db);
>   return -1;
>   }
>   
>   rc = sqlite3_bind_blob( stat, 1, chrPrintData, length, NULL );
>   if( rc != SQLITE_OK )
>   {
>   sqlite3_close(db);
>   return -1;
>   }
>   
>   rc = sqlite3_step( stat );  
>   if( rc != SQLITE_DONE )
>   {
>   sqlite3_close(db);
>   return -1;
>   }
>   
>   rc = sqlite3_reset( stat );
>   if( rc != SQLITE_OK )
>   {
>   sqlite3_close(db);
>   return -1;
>   }
>   
>   rc = sqlite3_finalize( stat );
>   if( rc != SQLITE_OK )
>   {
>   sqlite3_close(db);
>   return -1;
>   }
>   
> }
> 
> 
> WenYuan
> 
> 
> 
>  
>
__
> Search, browse and book your hotels and flights through Yahoo! Travel.
> http://sg.travel.yahoo.com
> ___
> 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
> 



  __
Search, browse and book your hotels and flights through Yahoo! Travel.
http://sg.travel.yahoo.com
___
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] WinCE Memory Problem

[Michael Ruck]

You should only prepare the statement once before the loop. The only thing
you should do in the loop itself is bind varying data and call sqlite_step.
There's no need to call prepare, reset, finalize inside the loop. If
chrPrintData doesn't change you can also move bind_blob in front of the
loop.

Mike

-Ursprüngliche Nachricht-
Von: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Im Auftrag von Yang WenYuan
Gesendet: Mittwoch, 9. April 2008 06:08
An: sqlite-users@sqlite.org
Betreff: [sqlite] WinCE Memory Problem

I use the Sqlite in the PDA which is WinCE OS. I need to add more than
200,000 records. I used sqlite3_prepare->
sqlite3_bind_blob->sqlite3_step->sqlite3_finalize to write each record in to
Database. However, I found that after each record is inserted, the PDA
memory became larger and larger. In the end, the whole PDA memory is
occupied by this application and the system halt. Is there any thing I miss
to release the memory?
Following is my code:

sqlite3 *db;
sqlite3_stmt * stat;
char *zErrMsg = 0;
char sqlcmd[ 512 ];
int rc;
char chrBarCode[ 16 ], chrPrintData[ 512 ]; int tagtype;



for( i = 0; i < 200; i ++ )
{   
sprintf( sqlcmd, "INSERT INTO TEST( BARCODE, TAGTYPE, PRINTDATA )
VALUES( %s, %d, ? );", chrBarCode, tagtype );

rc = sqlite3_prepare( db, sqlcmd, -1, , 0 );
if( rc != SQLITE_OK )
{
sqlite3_close(db);
return -1;
}

rc = sqlite3_bind_blob( stat, 1, chrPrintData, length, NULL ); 
if( rc != SQLITE_OK )
{
sqlite3_close(db);
return -1;
}

rc = sqlite3_step( stat );  
if( rc != SQLITE_DONE )
{
sqlite3_close(db);
return -1;
}

rc = sqlite3_reset( stat );
if( rc != SQLITE_OK )
{
sqlite3_close(db);
return -1;
}

rc = sqlite3_finalize( stat );
if( rc != SQLITE_OK )
{
sqlite3_close(db);
return -1;
}

}


WenYuan



  __
Search, browse and book your hotels and flights through Yahoo! Travel.
http://sg.travel.yahoo.com
___
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] Clear screen in command line interface

[Michael Ruck]

Imho the shell should rather implement the ! command to ask the bash (or
other shell) to execute a command in its preferred format. This 
way any executable could be launched from sqlites command prompt. A clear
screen would be available as !clear or !cls.

Michael

-Ursprüngliche Nachricht-
Von: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Im Auftrag von Fred J. Stephens
Gesendet: Mittwoch, 26. März 2008 21:15
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Clear screen in command line interface

Mike McGonagle wrote:
> Hum, on the Mac (10.4.9), the terminal window can be cleared from 
> within the terminal program itself. I would imagine that other 
> terminal programs also provide this. Does this functionality really need
to be in SQLite?
> Mike
You're right I guess. In Konsole under KDE at least, there is a "Clear
Terminal" command.
With SQLite though it leaves you with a blank screen and no prompt. 
Hitting enter brings back the prompt, but at the BOTTOM of the screen.
Better than nothing I guess. I just thought it would probably be trivial to
add this to SQLite's command line program.
Fred

> On Wed, Mar 26, 2008 at 1:59 PM, Fred J. Stephens <[EMAIL PROTECTED]>
wrote:
> 
>> Would it be possible to incorporate a clear screen command, like "clear"
>> in BASH? This would be handy to keep the view in the command line 
>> interface uncluttered.
>> Maybe ".clear"?
>> ___
>> 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

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

Re: [sqlite] insert on windows server 2003 very slow

[Michael Ruck]

Hi Angela,

for every iteration of your while loop a new transaction is opened and
commited. You need to wrap the loop in manual transactions using BEGIN and
COMMIT statements. Creating transaction logs is an expensive operation, when
compared to the insert itself.

Regards,
Michael

-Ursprüngliche Nachricht-
Von: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Im Auftrag von Angela Kramer
Gesendet: Dienstag, 26. Februar 2008 10:29
An: Eugene Wee
Cc: General Discussion of SQLite Database
Betreff: Re: [sqlite] insert on windows server 2003 very slow

Hi Eugene,

thank you for your reply.

Eugene Wee wrote:
> Hi Angela,
> 
> Did you wrap the inserts in a transaction?

I do not start a transaction manually. However a transaction is probably
started automatically. But: "Automatically started transactions are
committed at the conclusion of the command." 
(http://www.sqlite.org/lang_transaction.html)

My program executes only three queries:
1. create table contacts ...
2. "insert into contacts values (?, ?, ?, ?, ?, ?, ?, ?, ?);"
3. "create index idx_last_name on contacts (lastName ASC)"

The second query is run in a while loop (about 200.000 times).

> Regards,
> Eugene Wee
> 
> Angela Kramer wrote:
>> Hi,
>>
>> I've written a java programm which reads data from a file and inserts 
>> them into a sqlite database. In order to speed up this process I use 
>> PreparedStatement.
>>
>> On a computer running Windows XP inserting one line into the database 
>> takes about 16 milliseconds. On a machine with Windows Server 2003 
>> inserting the same line takes 150 to 200 milliseconds.
I execute the same jar-file and use exactly the same data on both machines.
>>
>> Where might this big difference come from?
>>
>> Thank you for your answers in advance!
>> Best regards

___
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] Given sqlite3_enable_load_extension(), why omit loadable extensions?

[Michael Ruck]

I believe the configure script is not maintained. It probably needs more
patches than this.

Mike 

-Ursprüngliche Nachricht-
Von: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Im Auftrag von Nicolas Williams
Gesendet: Mittwoch, 20. Februar 2008 00:19
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Given sqlite3_enable_load_extension(),why omit
loadable extensions?

On Wed, Feb 20, 2008 at 12:09:16AM +0100, Michael Ruck wrote:
> This is most likely done to reduce compiled code size. For a lot of 
> systems this code probably is wasted space, as the feature is not used
everywhere.
> For embedded systems the ability to simply cut features at compile 
> time to reduce code size is very important.

Thanks.

I'm working on integrating SQLite3 into OpenSolaris.  So I wonder if I
should enable this feature, and what the risks are.  The security risk is
already addressed via sqlite3_enable_load_extension().  Interface
instability might be a risk -- I don't know yet but I'll assume this is a
risk.

Finally, w.r.t. object code size, I see that the --enable-shared configure
option only enables building a shared library -- it does not actually build
the sqlite3 shell (nor sqlite3_analyzer) to use that shared library.  That'd
be a useful option, at least for us.  Would you welcome a patch?

Nico
--
___
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] Given sqlite3_enable_load_extension(), why omit loadable extensions?

[Michael Ruck]

This is most likely done to reduce compiled code size. For a lot of systems
this code probably is wasted space, as the feature is not used everywhere.
For embedded systems the ability to simply cut features at compile time to
reduce code size is very important.

Mike

-Ursprüngliche Nachricht-
Von: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Im Auftrag von Nicolas Williams
Gesendet: Dienstag, 19. Februar 2008 23:58
An: General Discussion of SQLite Database
Betreff: [sqlite] Given sqlite3_enable_load_extension(),why omit loadable
extensions?

I searched and found that SQLITE_OMIT_LOAD_EXTENSION is defined by default
to protect applications that might otherwise unwittingly allow SQL that
loads extensions.  I understand that, but, by default extensions cannot be
loaded until sqlite3_enable_load_extension() is called to enable them.  So
why still define SQLITE_OMIT_LOAD_EXTENSION by default?

Are loadable extensions an unstable part of SQLite3?  Some other reason?

Nico
--
___
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

AW: [sqlite] Does SQLite support modifying date through views?

[Michael Ruck]

It does not, but you can attach triggers to a view to achieve the same
effect. 

> -Ursprüngliche Nachricht-
> Von: Robert Smith [mailto:[EMAIL PROTECTED] 
> Gesendet: Sonntag, 9. Dezember 2007 08:31
> An: sqlite-users@sqlite.org
> Betreff: [sqlite] Does SQLite support modifying date through views?
> 
> 
> I am trying to implement an embedded application using 
> SQLite. I need to
> modify data through views. The update, insert, or delete 
> operations may
> refer to a computed column or a built-in function in a view. 
> I experienced
> problems. I am wondering if SQLite can support features of 
> modifying date
> through views.
> 
> Thanks,
> Robert
> -- 
> View this message in context: 
> http://www.nabble.com/Does-SQLite-support-modifying-date-throu
gh-views--tp14236459p14236459.html
> Sent from the SQLite mailing list archive at Nabble.com.
> 
> 
> --
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
> --
> ---
> 
> 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

AW: [sqlite] Transactional DDL

[Michael Ruck]

Are all CREATE ... statements transactional or is only CREATE TABLE
transactional?

Mike 

> -Ursprüngliche Nachricht-
> Von: Dan [mailto:[EMAIL PROTECTED] 
> Gesendet: Dienstag, 27. November 2007 15:59
> An: sqlite-users@sqlite.org
> Betreff: Re: [sqlite] Transactional DDL
> 
> 
> On Nov 27, 2007, at 9:47 PM, Igor Sereda wrote:
> 
> > I noticed that CREATE TABLE works well within a transaction, which  
> > was a pleasant surprise. I can create a table and insert some rows  
> > in it, all quite ACIDly - wow!
> >
> > My question is, is that a declared contract or just a peculiarity  
> > that may disappear in future versions? I couldn't find any 
> specs of  
> > that behavior in documentation. If I missed it, please point me to  
> > the URL. If there are no mentions of that in docs, well, it's  
> > probably worth mentioning.
> 
> It's a supported feature.
> 
> > Also, which statements are not transactional? VACUUM is obviously  
> > one of them, are there any other?
> 
> Some of the pragma statements. Can't think of anything else.
> 
> Dan.
> 
> 
> --
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
> --
> ---
> 
> 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

AW: [sqlite] Re: Performance tuning, and other (silly?) SQLitequestions.

[Michael Ruck]

I know that a natural join exists, but it is not automatic as
it seems to be in MySQL. 

> -Ursprüngliche Nachricht-
> Von: Dennis Cote [mailto:[EMAIL PROTECTED] 
> Gesendet: Dienstag, 20. November 2007 18:32
> An: sqlite-users@sqlite.org
> Betreff: Re: [sqlite] Re: Performance tuning, and other 
> (silly?) SQLitequestions.
> 
> Michael Ruck wrote:
> >>
> >> Ah.  I have been reading a PHP/MySQL book, that I thought 
> said a MySQL
> >> server would see the common column names and automagically 
> join the 2.
> >> Either I misremember what the book said (it's not with me 
> >> here), or this
> >> is a feature of MySQL, not present in SQLite.  Anyway, what 
> >> you suggest
> >> works just fine.
> >> 
> >
> > SQLite does not implement this feature. Its not in the SQL 
> standard AFAIK.
> >
> >   
> >   
> 
> This feature *is* part of the SQL standard and is implemented 
> by SQLite. 
> It is called a NATURAL JOIN.
> 
> select * from a_table natural join  b_table;
> 
> This will select all rows where all fields with the same name 
> in the two 
> tables are equal. See http://en.wikipedia.org/wiki/Join_(SQL) 
> for more 
> details. Note, only one column, with the same name as the matching 
> columns from the two tables, is generated in the result (and 
> this column 
> is not considered to be from either table in standard SQL).
> 
> HTH
> Dennis Cote
> 
> 
> 
> 
> 
> --
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
> --
> ---
> 
> 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: Performance tuning, and other (silly?) SQLitequestions.

[Michael Ruck]

> > > #A more complicated query...runs quite slowly.  How can 
> this be sped
> > > up?
> > > db eval {SELECT position.odo, data.x, data.y from 
> position, data WHERE
> > > position.odo BETWEEN 1 AND 10020;}
> > 
> > First, you want an index on position.odo. Second, you don't 
> specify any 
> > relation between position and data tables, so you generate a full 
> > cross-product. You want
> > 
> > SELECT position.odo, data.x, data.y
> > FROM position JOIN data ON (position.position_id = data.position_id)
> > WHERE position.odo BETWEEN 1 AND 10020;
> 
> Ah.  I have been reading a PHP/MySQL book, that I thought said a MySQL
> server would see the common column names and automagically join the 2.
> Either I misremember what the book said (it's not with me 
> here), or this
> is a feature of MySQL, not present in SQLite.  Anyway, what 
> you suggest
> works just fine.

SQLite does not implement this feature. Its not in the SQL standard AFAIK.

> > And for that to work efficiently, you want another index on 
> > data.position_id
> 
> I'm guessing that is with;
> CREATE INDEX odo_index ON data (position_id ASC);

Yes.

> If the data is streaming in, and insertions are being made on the fly,
> will an index need to be regenerated periodically, i.e. REINDEX?

No. Indexes are automatically updated.

HTH,
Mike


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()

[Michael Ruck]

Daniel,

My usecase is the following: I maintain a lot of tables, which are simply
catalogs of predefined or user entered values. I wanted to reduce the amount
of code and memory to maintain those tables, as the user can enter values as
free text in a lot of web forms and also choose from previously entered
values via ajax autocompletion. To simplify my code I wanted to use INSERT
OR IGNORE in those catalog tables, as I don't care if the value is already
there or not. I just need the rowids as foreign keys for other table(s).

An example would be:

- CREATE TABLE song (id INTEGER PRIMARY KEY, artist INTEGER, writer INTEGER,
title INTEGER)
- CREATE TABLE artists (id INTEGER PRIMARY KEY, value TEXT UNIQUE)
- CREATE TABLE writers (id INTEGER PRIMARY KEY, value TEXT UNIQUE)
- CREATE TABLE titles (id INTEGER PRIMARY KEY, value TEXT UNIQUE)

For an insert of a new song I wanted to do (pseudo code)

INSERT OR IGNORE INTO artists VALUES (NULL, 'user entered or chosen
artistname');
rowid-of-first-insert = sqlite3_last_insert_rowid();
INSERT OR IGNORE INTO writers VALUES (NULL, 'user entered or chosen writer
name');
rowid-of-second-insert = sqlite3_last_insert_rowid();
INSERT OR IGNORE INTO titles VALUES (NULL, 'user entered or chosen title');
rowid-of-third-insert = sqlite3_last_insert_rowid();
INSERT INTO song (NULL, rowid-of-first-insert, rowid-of-second-insert,
rowid-of-third-insert);

This is just a trivial example of what I want to do. My understanding of
INSERT OR IGNORE was that it always succeeds, even though the record is
already there (which is exactly what I want.) In contrast to INSERT OR
REPLACE it doesn't remove the old row and thus keeps the same rowid. In
conjunction with the documentation for sqlite3_last_insert_rowid(), which
states:

"This routine returns the rowid of the most recent INSERT into the database
from the database connection given in the first argument. If no inserts have
ever occurred on this database connection, zero is returned."

I was assuming that I'll receive the rowid even in case where the conflict
clause from INSERT OR IGNORE caused the insert not to happen. I was just a
bit surprised about this and that's why I asked if this was expected
behavior. Dr. Hipps answer cleared the reason for this up and I already
started looking for alternatives. The only thing I was asking for is that
this behavior (sqlite3_last_insert_rowid() and its results with CONFLICT
clauses) be explicitly mentioned in the documentation of
sqlite3_last_insert_rowid().

Unfortunately the solution proposed by Shawn Odekirk fails too, as I don't
have the rowids for insert statements. If i did, I wouldn't INSERT OR IGNORE
or INSERT OR REPLACE in the first place. The id column is automatically
maintained by SQlite and I don't want to mess with it. So I'll just do blind
inserts and check the return value and do a select if insert fails. Little
more code, but it works.

Mike


-Ursprüngliche Nachricht-
Von: Daniel Önnerby [mailto:[EMAIL PROTECTED] 
Gesendet: Dienstag, 30. Oktober 2007 12:03
An: sqlite-users@sqlite.org
Betreff: Re: AW: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()

Why are you using the INSERT OR IGNORE? If you read the
http://www.sqlite.org/lang_conflict.html it states that the IGNORE will not
return any errors. Isn't the default behavior INSERT OR ABORT (or just plain
INSERT) what you are looking for?
The default INSERT will return an error (SQLITE_CONSTRAINT) if there is a
conflict. If you get a conflict then do not trust the
sqlite3_last_insert_rowid since (I guess) it will return the last successful
insert rowid.

Best regards
Daniel

Michael Ruck wrote:
> I'm not blaming anyone. I just think it should be mentioned in the docs.
>
> Mike
>
> -Ursprüngliche Nachricht-
> Von: John Stanton [mailto:[EMAIL PROTECTED]
> Gesendet: Donnerstag, 29. November 2007 20:12
> An: sqlite-users@sqlite.org
> Betreff: Re: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()
>
> As has been carefully explained by several people, it is reliable.  
> You just did not think through your application.  You could make an 
> extension to Sqlite and implement an sqlite3_last_insert_or_ignore_id 
> function, but to blithely assume that you can use last_insert_id with 
> INSERT OR IGNORE is not logical and to blame others for your oversight 
> is not helpful.
>
> Michael Ruck wrote:
>   
>> I don't get an error code. So how should I decide if I should call
>> sqlite3_last_insert_rowid() or not? :) That's the problem - I don't 
>> have
>> 
> any
>   
>> indication if an insert
>> was actually performed or if it was simply ignored - thus I don't 
>> have any possibility to decide if the call is valid or not. This 
>> makes the OR
>> 
> IGNORE
>   
>> clause or the sqlite3_last_insert_rowid() function useless for *my 
>> purposes*. I wou

AW: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()

[Michael Ruck]

I'm not blaming anyone. I just think it should be mentioned in the docs.

Mike 

-Ursprüngliche Nachricht-
Von: John Stanton [mailto:[EMAIL PROTECTED] 
Gesendet: Donnerstag, 29. November 2007 20:12
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()

As has been carefully explained by several people, it is reliable.  You 
just did not think through your application.  You could make an 
extension to Sqlite and implement an sqlite3_last_insert_or_ignore_id 
function, but to blithely assume that you can use last_insert_id with 
INSERT OR IGNORE is not logical and to blame others for your oversight 
is not helpful.

Michael Ruck wrote:
> I don't get an error code. So how should I decide if I should call
> sqlite3_last_insert_rowid() or not? :) That's the problem - I don't have
any
> indication if an insert
> was actually performed or if it was simply ignored - thus I don't have any
> possibility to decide if the call is valid or not. This makes the OR
IGNORE
> clause or the sqlite3_last_insert_rowid() function useless for *my
> purposes*. I would have never pursued this path in tests, if I would've
> known beforehand that it is not reliable if used with ON CONFLICT clauses.
>
> Mike
>
> -Ursprüngliche Nachricht-
> Von: Odekirk, Shawn [mailto:[EMAIL PROTECTED] 
> Gesendet: Montag, 29. Oktober 2007 14:04
> An: sqlite-users@sqlite.org
> Betreff: RE: AW: AW: [sqlite] INSERT OR IGNORE and
> sqlite3_last_insert_rowid()
>
> The sqlite3_last_insert_rowid function is completely, 100% reliable in
your
> scenario.  The problem is that in your scenario you shouldn't be calling
> that function.
> The function is called sqlite3_last_insert_rowid, not
> sqlite3_last_insert_or_ignore_rowid, and not
> sqlite3_last_insert_or_fail_rowid.  It makes perfect sense that it returns
> the row id of the last row inserted successfully.  This function should
only
> be called after a successful insert.  In your scenario you have not
> performed a successful insert.  There is no reason to think that the
> function will return a meaningful row id after a failed insert attempt.
> I hope my response was not too harsh.  You seem so adamant that there is a
> problem with the function or documentation, and I completely disagree.
>
> Shawn
>
> -Original Message-
> From: Michael Ruck [mailto:[EMAIL PROTECTED]
> Sent: Sunday, October 28, 2007 12:55 PM
> To: sqlite-users@sqlite.org
> Subject: AW: AW: AW: [sqlite] INSERT OR IGNORE and
> sqlite3_last_insert_rowid()
>
> I'd suggest putting this into the documentation of
> sqlite3_last_insert_rowid(), that the call is not reliable in scenarios
such
> as this one. 
>
> -Ursprüngliche Nachricht-
> Von: D. Richard Hipp [mailto:[EMAIL PROTECTED]
> Gesendet: Sonntag, 28. Oktober 2007 17:48
> An: sqlite-users@sqlite.org
> Betreff: Re: AW: AW: [sqlite] INSERT OR IGNORE and
> sqlite3_last_insert_rowid()
>
>
> On Oct 28, 2007, at 10:59 AM, Michael Ruck wrote:
>
>   
>> Yes, I am well aware of this possibility as I've written in my  
>> initial mail.
>> It just doesn't fit with the
>> description of sqlite3_last_insert_rowid() in my understanding. I  
>> think this
>> is a bug - either in the documentation
>> or in the implementation. sqlite3_last_insert_rowid() should return  
>> the
>> correct id, no matter what and it doesn't.
>>
>> 
>
> Consider this scenario:
>
>  CREATE TABLE ex1(id INTEGER PRIMARY KEY, b UNIQUE, c UNIQUE);
>  INSERT INTO ex1 VALUES(1,1,1);
>  INSERT INTO ex1 VALUES(2,2,2);
>  INSERT INTO ex1 VALUES(3,3,3);
>
> Now you do your INSERT OR IGNORE:
>
>  INSERT OR IGNORE INTO ex1 VALUES(1,2,3);
>
> Three different constraints fail, one for each of three different
> rows.  So if sqlite3_last_insert_rowid() were to operate as you
> suggest and return the rowid of the failed insert, when rowid
> would it return?  1, 2, or 3?
>
>
> D. Richard Hipp
> [EMAIL PROTECTED]
>
>
>
>
>

> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>

> -
>
>
>
>

> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>

> -
>
>
>
>
>

> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
---

AW: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()

[Michael Ruck]

This does look like a solution indeed. I'll try this one later.

Thank you!
Mike 

-Ursprüngliche Nachricht-
Von: Simon Davies [mailto:[EMAIL PROTECTED] 
Gesendet: Montag, 29. Oktober 2007 16:40
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()

On 29/10/2007, Odekirk, Shawn <[EMAIL PROTECTED]> wrote:
> Adding "primary key" to column "a" results in the behavior I think you
> were first expecting.
>
> sqlite> create table tmp (a integer primary key, b integer);
> sqlite> create unique index tmpIndex on tmp (a, b);
> sqlite> insert into tmp values (1, 1);
> sqlite> insert into tmp values (2, 2);
> sqlite> select last_insert_rowid();
> 2
> sqlite> insert or replace into tmp values (1, 1);
> sqlite> select last_insert_rowid();
> 1
> sqlite> select * from tmp;
> 1|1
> 2|2
>
> I wonder if Michael could use OR REPLACE instead of OR IGNORE to solve
> his problem.
>
> Shawn
>

That was the result I was seeking (as a potential answer to Michael's
problem).

Looking at Michael's original post, he has declared an INTEGER PRIMARY
KEY column, so "OR REPLACE" instead of "OR IGNORE" could be the
solution as you suggest.

Rgds,
Simon


-
To unsubscribe, send email to [EMAIL PROTECTED]

-



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Re: INSERT OR IGNORE and sqlite3_last_insert_rowid()

[Michael Ruck]

Ok, thanks I haven't seen this function. I'll try it. 

Mike

-Ursprüngliche Nachricht-
Von: Igor Tandetnik [mailto:[EMAIL PROTECTED] 
Gesendet: Montag, 29. Oktober 2007 17:01
An: SQLite
Betreff: [sqlite] Re: INSERT OR IGNORE and sqlite3_last_insert_rowid()

Michael Ruck 
wrote: 
> I don't get an error code. So how should I decide if I should call
> sqlite3_last_insert_rowid() or not? :) That's the problem - I don't
> have any 
> indication if an insert
> was actually performed or if it was simply ignored

sqlite3_changes

Igor Tandetnik


-
To unsubscribe, send email to [EMAIL PROTECTED]

-



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()

[Michael Ruck]

I don't get an error code. So how should I decide if I should call
sqlite3_last_insert_rowid() or not? :) That's the problem - I don't have any
indication if an insert
was actually performed or if it was simply ignored - thus I don't have any
possibility to decide if the call is valid or not. This makes the OR IGNORE
clause or the sqlite3_last_insert_rowid() function useless for *my
purposes*. I would have never pursued this path in tests, if I would've
known beforehand that it is not reliable if used with ON CONFLICT clauses.

Mike

-Ursprüngliche Nachricht-
Von: Odekirk, Shawn [mailto:[EMAIL PROTECTED] 
Gesendet: Montag, 29. Oktober 2007 14:04
An: sqlite-users@sqlite.org
Betreff: RE: AW: AW: [sqlite] INSERT OR IGNORE and
sqlite3_last_insert_rowid()

The sqlite3_last_insert_rowid function is completely, 100% reliable in your
scenario.  The problem is that in your scenario you shouldn't be calling
that function.
The function is called sqlite3_last_insert_rowid, not
sqlite3_last_insert_or_ignore_rowid, and not
sqlite3_last_insert_or_fail_rowid.  It makes perfect sense that it returns
the row id of the last row inserted successfully.  This function should only
be called after a successful insert.  In your scenario you have not
performed a successful insert.  There is no reason to think that the
function will return a meaningful row id after a failed insert attempt.
I hope my response was not too harsh.  You seem so adamant that there is a
problem with the function or documentation, and I completely disagree.

Shawn

-Original Message-
From: Michael Ruck [mailto:[EMAIL PROTECTED]
Sent: Sunday, October 28, 2007 12:55 PM
To: sqlite-users@sqlite.org
Subject: AW: AW: AW: [sqlite] INSERT OR IGNORE and
sqlite3_last_insert_rowid()

I'd suggest putting this into the documentation of
sqlite3_last_insert_rowid(), that the call is not reliable in scenarios such
as this one. 

-Ursprüngliche Nachricht-
Von: D. Richard Hipp [mailto:[EMAIL PROTECTED]
Gesendet: Sonntag, 28. Oktober 2007 17:48
An: sqlite-users@sqlite.org
Betreff: Re: AW: AW: [sqlite] INSERT OR IGNORE and
sqlite3_last_insert_rowid()


On Oct 28, 2007, at 10:59 AM, Michael Ruck wrote:

> Yes, I am well aware of this possibility as I've written in my  
> initial mail.
> It just doesn't fit with the
> description of sqlite3_last_insert_rowid() in my understanding. I  
> think this
> is a bug - either in the documentation
> or in the implementation. sqlite3_last_insert_rowid() should return  
> the
> correct id, no matter what and it doesn't.
>

Consider this scenario:

 CREATE TABLE ex1(id INTEGER PRIMARY KEY, b UNIQUE, c UNIQUE);
 INSERT INTO ex1 VALUES(1,1,1);
 INSERT INTO ex1 VALUES(2,2,2);
 INSERT INTO ex1 VALUES(3,3,3);

Now you do your INSERT OR IGNORE:

 INSERT OR IGNORE INTO ex1 VALUES(1,2,3);

Three different constraints fail, one for each of three different
rows.  So if sqlite3_last_insert_rowid() were to operate as you
suggest and return the rowid of the failed insert, when rowid
would it return?  1, 2, or 3?


D. Richard Hipp
[EMAIL PROTECTED]





-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]

-





-
To unsubscribe, send email to [EMAIL PROTECTED]

-



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

AW: AW: AW: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()

[Michael Ruck]

I'd suggest putting this into the documentation of
sqlite3_last_insert_rowid(), that
the call is not reliable in scenarios such as this one. 

-Ursprüngliche Nachricht-
Von: D. Richard Hipp [mailto:[EMAIL PROTECTED] 
Gesendet: Sonntag, 28. Oktober 2007 17:48
An: sqlite-users@sqlite.org
Betreff: Re: AW: AW: [sqlite] INSERT OR IGNORE and
sqlite3_last_insert_rowid()


On Oct 28, 2007, at 10:59 AM, Michael Ruck wrote:

> Yes, I am well aware of this possibility as I've written in my  
> initial mail.
> It just doesn't fit with the
> description of sqlite3_last_insert_rowid() in my understanding. I  
> think this
> is a bug - either in the documentation
> or in the implementation. sqlite3_last_insert_rowid() should return  
> the
> correct id, no matter what and it doesn't.
>

Consider this scenario:

 CREATE TABLE ex1(id INTEGER PRIMARY KEY, b UNIQUE, c UNIQUE);
 INSERT INTO ex1 VALUES(1,1,1);
 INSERT INTO ex1 VALUES(2,2,2);
 INSERT INTO ex1 VALUES(3,3,3);

Now you do your INSERT OR IGNORE:

 INSERT OR IGNORE INTO ex1 VALUES(1,2,3);

Three different constraints fail, one for each of three different
rows.  So if sqlite3_last_insert_rowid() were to operate as you
suggest and return the rowid of the failed insert, when rowid
would it return?  1, 2, or 3?


D. Richard Hipp
[EMAIL PROTECTED]





-
To unsubscribe, send email to [EMAIL PROTECTED]

-



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

AW: AW: AW: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()

[Michael Ruck]

Good point.

Thanks.

-Ursprüngliche Nachricht-
Von: D. Richard Hipp [mailto:[EMAIL PROTECTED] 
Gesendet: Sonntag, 28. Oktober 2007 17:48
An: sqlite-users@sqlite.org
Betreff: Re: AW: AW: [sqlite] INSERT OR IGNORE and
sqlite3_last_insert_rowid()


On Oct 28, 2007, at 10:59 AM, Michael Ruck wrote:

> Yes, I am well aware of this possibility as I've written in my  
> initial mail.
> It just doesn't fit with the
> description of sqlite3_last_insert_rowid() in my understanding. I  
> think this
> is a bug - either in the documentation
> or in the implementation. sqlite3_last_insert_rowid() should return  
> the
> correct id, no matter what and it doesn't.
>

Consider this scenario:

 CREATE TABLE ex1(id INTEGER PRIMARY KEY, b UNIQUE, c UNIQUE);
 INSERT INTO ex1 VALUES(1,1,1);
 INSERT INTO ex1 VALUES(2,2,2);
 INSERT INTO ex1 VALUES(3,3,3);

Now you do your INSERT OR IGNORE:

 INSERT OR IGNORE INTO ex1 VALUES(1,2,3);

Three different constraints fail, one for each of three different
rows.  So if sqlite3_last_insert_rowid() were to operate as you
suggest and return the rowid of the failed insert, when rowid
would it return?  1, 2, or 3?


D. Richard Hipp
[EMAIL PROTECTED]





-
To unsubscribe, send email to [EMAIL PROTECTED]

-



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

AW: AW: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()

[Michael Ruck]

Yes, I am well aware of this possibility as I've written in my initial mail.
It just doesn't fit with the 
description of sqlite3_last_insert_rowid() in my understanding. I think this
is a bug - either in the documentation
or in the implementation. sqlite3_last_insert_rowid() should return the
correct id, no matter what and it doesn't.

Since I have a bunch of tables of this structure I don't want to waste
memory/processor time just to retrieve the
rowid I should have gotten in the first place from the insert.

Mike

-Ursprüngliche Nachricht-
Von: Kees Nuyt [mailto:[EMAIL PROTECTED] 
Gesendet: Sonntag, 28. Oktober 2007 15:36
An: sqlite-users@sqlite.org
Betreff: Re: AW: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()

[Default] On Sun, 28 Oct 2007 10:00:52 +0100, "Michael Ruck"
<[EMAIL PROTECTED]> wrote:

>Hi,
>
>I did specify UNIQUE for category. The id is also kept, so everything 
>is working
>*except* that I don't get the id of the record ignored from 
>sqlite3_last_insert_rowid().
>
>Mike

You could simply do a 
SELECT id FROM categories WHERE category = ''; to
retrieve the id.
After the (ignored) INSERT the database pages with the relevant parts of the
BTree for the UNIQUE index on category will still be in memory, so the
SELECT will be fast.

For even more speed you can prepare the SELECT statement during the init of
your program, and bind to the appropriate values every time you need it, so
it doesn't have to be parsed every time.

Regards,
--
  (  Kees Nuyt
  )
c[_]


-
To unsubscribe, send email to [EMAIL PROTECTED]

-



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

AW: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()

[Michael Ruck]

Hi,

I did specify UNIQUE for category. The id is also kept, so everything is
working 
*except* that I don't get the id of the record ignored from
sqlite3_last_insert_rowid().

Mike

-Ursprüngliche Nachricht-
Von: Kees Nuyt [mailto:[EMAIL PROTECTED] 
Gesendet: Samstag, 27. Oktober 2007 23:45
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()

[Default] On Sat, 27 Oct 2007 16:26:36 +0200, "Michael Ruck"
<[EMAIL PROTECTED]> wrote:

>Hi,
>
>I have a table of unique values in the following format:
>
>CREATE TABLE categories (id INTEGER PRIMARY KEY, category UNIQUE TEXT)
>
>I want inserts into this table to succeed, even though the corresponding
>entry already exists. So I use inserts in the following format:
>
>INSERT OR IGNORE INTO categories VALUES (NULL, ?)
>
>However, if I follow this successful execution with a call to
>sqlite3_last_insert_rowid() I don't get the rowid of row, which caused the
>insert to be ignored, but one I preformed previously (which doesn't
>necessarily have anything to do with this one.) This causes some relations
>in my database model to break.
>
>I know I could use INSERT OR FAIL and a subsequent SELECT, but that seems
>awkward and like unnecessary code bloat to me. Additionally I kind of
think,
>
>this breaks the description and sense of sqlite3_last_insert_rowid().
>
>SQlite version used is 3.3.16.
>
>Is this intentional? Any suggestions or should I file a ticket for this?
>
>Thanks!
>Mike

You supply NULL for the primary key, which in this case means
SQLite will make up a new id for you.

http://www.sqlite.org/lang_createtable.html :

Specifying a PRIMARY KEY normally just creates a UNIQUE index on
the corresponding columns. However, if primary key is on a
single column that has datatype INTEGER, then that column is
used internally as the actual key of the B-Tree for the table.
This means that the column may only hold unique integer values.
(Except for this one case, SQLite ignores the datatype
specification of columns and allows any kind of data to be put
in a column regardless of its declared datatype.) If a table
does not have an INTEGER PRIMARY KEY column, then the B-Tree key
will be a automatically generated integer.  The B-Tree key for a
row can always be accessed using one of the special names
"ROWID", "OID", or "_ROWID_". This is true regardless of whether
or not there is an INTEGER PRIMARY KEY. An INTEGER PRIMARY KEY
column can also include the keyword AUTOINCREMENT. The
AUTOINCREMENT keyword modified the way that B-Tree keys are
automatically generated. Additional detail on automatic B-Tree
key generation is available separately.


http://www.sqlite.org/autoinc.html :


When a new row is inserted into an SQLite table, the ROWID can
either be specified as part of the INSERT statement or it can be
assigned automatically by the database engine. To specify a
ROWID manually, just include it in the list of values to be
inserted. For example:

CREATE TABLE test1(a INT, b TEXT);
INSERT INTO test1(rowid, a, b) VALUES(123, 5, 'hello');

If no ROWID is specified on the insert, an appropriate ROWID is
created automatically. The usual algorithm is to give the newly
created row a ROWID that is one larger than the largest ROWID in
the table prior to the insert. 

And:
If a column has the type INTEGER PRIMARY KEY AUTOINCREMENT then
a slightly different ROWID selection algorithm is used. 


By supplying NULL as the key (ROWID) you actually don't specify
a value, so SQLite creates a new row with a new id.
If you want category to be unique, you will have to specify a
UNIQUE constraint for it.

HTH
-- 
  (  Kees Nuyt
  )
c[_]


-
To unsubscribe, send email to [EMAIL PROTECTED]

-



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()

[Michael Ruck]

Hi,

I have a table of unique values in the following format:

CREATE TABLE categories (id INTEGER PRIMARY KEY, category UNIQUE TEXT)

I want inserts into this table to succeed, even though the corresponding
entry already exists. So I use inserts in the following format:

INSERT OR IGNORE INTO categories VALUES (NULL, ?)

However, if I follow this successful execution with a call to
sqlite3_last_insert_rowid() I don't get the rowid of row, which caused the
insert to be ignored, but one I preformed previously (which doesn't
necessarily have anything to do with this one.) This causes some relations
in my database model to break.

I know I could use INSERT OR FAIL and a subsequent SELECT, but that seems
awkward and like unnecessary code bloat to me. Additionally I kind of think,

this breaks the description and sense of sqlite3_last_insert_rowid().

SQlite version used is 3.3.16.

Is this intentional? Any suggestions or should I file a ticket for this?

Thanks!
Mike


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

AW: [sqlite] Index size in file

[Michael Ruck]

If you're running under constraints so low, you should take care choosing
the right
tools for the job. Apparently sqlite isn't the right tool for this job.

Mike

-Ursprüngliche Nachricht-
Von: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] 
Gesendet: Freitag, 5. Oktober 2007 00:19
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] Index size in file




Let's assume that my whole database can be in the cache. If my indexes have
duplicate data, then I will either need a bigger cache or have to page out
row
data in favour of index data.
In that case it will either be slower or require more memory to keep
duplicate
data for the indexes as opposed to referencing the original data.

Clive





John Stanton <[EMAIL PROTECTED]> on 05/10/2007 00:54:21

Please respond to sqlite-users@sqlite.org

To:   sqlite-users@sqlite.org
cc:(bcc: clive/Emultek)

Subject:  Re: [sqlite] Index size in file



Trevor Talbot wrote:
> On 10/4/07, John Stanton <[EMAIL PROTECTED]> wrote:
>
>>A B-Tree index holds keys in sorted sequence.  They are in random
>>sequence in the database.  That requires holding the keys in the B-Tree
>>nodes.
>
>
> Actually, it doesn't strictly require that; it could store references
> to the keys.  An obvious tradeoff is I/O; an index walk is less useful
> if you have to do random seeks to the locations of row data just to
> get the keys to walk the tree in the first place.  IOW in simplistic
> terms, an index walk suddenly doubles in disk I/O.
>
> The information on SQL Server would be interesting, as I know it
> stores sort keys under some conditions, which is effectively duplicate
> data.
>
One would need to be a paleontologist to measure the performance of an
ordered index with indirect key references.


-
To unsubscribe, send email to [EMAIL PROTECTED]

-









This footnote confirms that this email message has been scanned by
PineApp Mail-SeCure for the presence of malicious code, vandals & computer
viruses.














-
To unsubscribe, send email to [EMAIL PROTECTED]

-



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

AW: [sqlite] Re: In Mem Query Performance

[Michael Ruck]

That's not what I meant. I meant the following:

Take the key in the format
'kkk
k490' and split it into
two(66 characters each) /three (44 characters each) colums of equal length,
e.g. key0_0 = '', key0_1 =
'', key0_2 =
'k490'. If you can then reduce the
select to compare only key0_2, you reduced the work required to find a row
by 2/3. If you could even reverse the keys, you'd benefit even more - as the
difference would occur in the first few bytes. (All this is just guessing
based on your examples - without knowing the actual structure of your data.)

So you would have 3 columns instead of one. The ones, which change most
frequently will go into the primary key as it benefits most by not having
duplicates.

Once you've done that you should be able to reduce your query times
fundamentally by only applying a comparison to a certain column.

If that doesn't help and you still have a lot of equality in your keys, I'd
throw them out of the actual data and put them into some kind of dictionary
table and use joins to map them together. 

Given your current examples of your dataset, there isn't much that can be
done without optimizing the data for storage and lookup - this isn't
something where a set of compiler flags will help a lot. The optimizers are
smart, but they can't correct bad design in the first place. You have to
reduce the actual code being executed by the CPU (e.g. bytes to compare) to
get faster.

Mike


-Ursprüngliche Nachricht-
Von: RaghavendraK 70574 [mailto:[EMAIL PROTECTED] 
Gesendet: Dienstag, 3. Juli 2007 18:21
An: sqlite-users@sqlite.org; [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Betreff: Re: [sqlite] Re: In Mem Query Performance

Hi,

As said, i used a seperate ID (hash) but it made the performance very bad.
Now it takes minutes.[Version:  3.4.0]

regards
ragha

unsigned long idGen(const string & id) 
{
const char* __s = id.c_str();
unsigned long __h = 0; 
for ( ; *__s; ++__s)
{
   __h = 5*__h + *__s;
}
return size_t(__h); 
}

gen: program
int main()
{
 fstream fout("data.sql",ios::out);
 fstream fout1("query.sql",ios::out);
  fout<<"Begin;"<>data[j];
   //cout<AW: [sqlite] Re: In Mem Query Performance


2007-07-03

Thread
Michael Ruck

As has been suggested numerous times, you should split the key. The keys
you've shown are very long and only differ in the last characters. You
should try yourself to split the key (maybe in two or three columns) and
order the key according to the change frequency. This way sqlite doesn't
have to run as many equality comparisons or detects difference earlier. Your
times are only as bad as they are due to the fact that most time is spent
searching for equality and hitting the differences after comparing 128
characters, which match most of the time (taken from your earlier mails.)

I would suggest splitting the key at least to two columns and changing the
order key according to change frequency in the columns. You won't have any
data loss, you just need to concatenate the columns again in your sql
queries to obtain results in the same format as previously.

Mike

-Ursprüngliche Nachricht-
Von: RaghavendraK 70574 [mailto:[EMAIL PROTECTED] 
Gesendet: Dienstag, 3. Juli 2007 12:12
An: sqlite-users@sqlite.org; [EMAIL PROTECTED]
Betreff: Re: [sqlite] Re: In Mem Query Performance

Hi Ken,

Thanks a lot.
But this would require the key to be short. Will Check if this is acceptable
to all as we may not be able to port old db data if the key format is
changed.

regards
ragha


**
 This email and its attachments contain confidential information from
HUAWEI, which is intended only for the person or entity whose address is
listed above. Any use of the information contained herein in any way
(including, but not limited to, total or partial disclosure, reproduction,
or dissemination) by persons other than the intended recipient(s) is
prohibited. If you receive this e-mail in error, please notify the sender by
phone or email immediately and delete it!
 

*

- Original Message -
From: Ken <[EMAIL PROTECTED]>
Date: Tuesday, July 3, 2007 0:46 am
Subject: Re: [sqlite] Re: In Mem Query Performance

> Timings:
> 
> Calling Test Now
> TIME_ms=1210
> start=1183394494
> end=1183394496
> 
> Calling Test Now
> TIME_ms=1164
> start=1183394504
> end=1183394505
> 
> (time in Ms is 1.2 and 1.1 ... )
> 
> Data generated using:
> #include 
> #include 
> #include 
> #include 
> 
> using namespace std;
> 
> 
> int main()
> {
>  fstream fout("data.sql",ios::out);
>  fstream fout1("query.sql",ios::out);
>   //Schema
>   fout<<"create table test (name text not null, ser text not null,doid 
> text,primary key (name, ser));"< 
>  for(int j=0; j<10;j++)
>  {
> char* key = tmpnam(0);
>  string ser =
> 
> ".
> ";
> 
>  fout1< fout<<"insert into test values
> ('"< 22
> 222');"<  }
> 
>  return 0;
> }
> 
> 
> ### Load data using:
> sqlite3  abc.db
>> pragma page_size=4096;
>> begin;
>> .read data.sql
>> commit;
>   
> = Performance code == 
> //Compile:g++ -g main.cpp -I. -L. -lsqlite3  -lpthread
> 
> #include 
> #include 
> #include 
> #include 
> 
> #include 
> 
> #include 
> #include 
> 
> #include "sqlite3.h"
> 
> using namespace std;
> 
> int main()
> {
>  struct timeb  startTime;
>  struct timeb  endTime;
>  doublems;
> 
>   
>   sqlite3* db;
>   int ret = sqlite3_open("abc.db",);
> 
>   char* errmsg;
>   char buf[1024];
> 
>   sprintf(buf,"create temporary table ttest as select * from test; ");
>ret = sqlite3_exec(db , buf, NULL ,NULL,);
>if (ret != SQLITE_OK) {
>   printf("failed to create temp table: %d\n", ret);
>   exit(ret);
>}
> 
>sprintf(buf,"create index tidx on ttest (name, ser);");
>ret = sqlite3_exec(db , buf, NULL ,NULL,);
>if (ret != SQLITE_OK) {
>   printf("failed to create index: %d\n", ret);
>   exit(ret);
>}
> 
> 
>   //read all the srvName from file and store it in arr and query.
>   fstream fin("query.sql",ios::in);
>   string data[10];
> 
>for(int j=0;!fin.eof();j++)
>{
>   fin>>data[j];
>   //cout<}
>fin.close();
> 
>cout<<"Calling Test Now"<sleep(1);
> 
>//Now Query Data.
>time_t start = time(0);
>ftime();
> 
> 
> 
>char* szError=0;
>const char* szTail=0;
>sqlite3_stmt* pVM;
> 
>sprintf(buf,"select * from ttest where name = ?" );
>ret = sqlite3_prepare_v2(db, buf, -1, , );
>if (ret != SQLITE_OK) exit(ret);
> 
>for(int k=0;k<10;k++)
>{
>   ret = sqlite3_bind_text(pVM, 1, data[k].c_str(), 
> data[k].length(), SQLITE_STATIC);
>   ret = sqlite3_step(pVM);
>   ret = sqlite3_reset(pVM);
>}
> 
> 
> 




AW: [sqlite] In Mem Query Performance


2007-06-25

Thread
Michael Ruck

Don't use sqlite_get_table. Use sqlite3_prepare(_v2), sqlite3_step and
sqlite3_finalize/sqlite3_reset.

Mike 

-Ursprüngliche Nachricht-
Von: RaghavendraK 70574 [mailto:[EMAIL PROTECTED] 
Gesendet: Montag, 25. Juni 2007 13:48
An: sqlite-users@sqlite.org
Betreff: [sqlite] In Mem Query Performance


Hi,

We are using Sqlite in "in Memory Mode" and we have around 200 tables.
Each table has 10 columns of type text.
Each table has around 1 records each column has around 128bytes data.
Select performance is around 2000records/sec. Pls suggest if there is a way
to improve further.

Table structure,Query style is as below,

create table test1 ...200
(
  key0 text,
  key1 text,
  key2 text,
  key3 text,
  key4 text,
  nonKey0 text,
  nonKey1 text,
  nonKey2 text,
  nonKey3 text,
  nonKey4 text,
  primary key(key0,key1,key2,key3,key4,key5)
);

  Query Used..

 SELECT * FROM TABLE136 WHERE
key0='kk
kk490' AND
key1='kk
kk491' AND
key2='kk
kk492' AND
key3='kk
kk493' AND
key4='kk
kk494'

API used sqlite_get_table...

regards
ragha


**
 This email and its attachments contain confidential information from
HUAWEI, which is intended only for the person or entity whose address is
listed above. Any use of the information contained herein in any way
(including, but not limited to, total or partial disclosure, reproduction,
or dissemination) by persons other than the intended recipient(s) is
prohibited. If you receive this e-mail in error, please notify the sender by
phone or email immediately and delete it!
 

*


-
To unsubscribe, send email to [EMAIL PROTECTED]

-



-
To unsubscribe, send email to [EMAIL PROTECTED]
-






[sqlite] SQLite used by Google Gears


2007-05-31

Thread
Michael Ruck

This will cause SQLite to spread to even more desktops around the world:
Google integrated SQLite into its new Google Gears tool, which allows web
applications to work offline. It provides several services, one of which is
a local client database.

Read more: http://gears.google.com/

Mike


-
To unsubscribe, send email to [EMAIL PROTECTED]
-






AW: [sqlite] ATTACH and sqlite3_open()


2007-05-09

Thread
Michael Ruck

Just call sqlite3_exec with the proper ATTACH as you would on the command
line. (Of course you could also do a prepare/step/finalize, but for ATTACH
sqlite3_exec is enough.)

Example:

sqlite3 *db = NULL;
/* ... */
sqlite3_exec(db, "ATTACH DATABASE 'filename' AS dbname", NULL, NULL,
);

Using SELECT sqlite_attach('filename','dbname',NULL) should also work -
haven't tried it though. This should have the benefit that the arguments can
be bound and the attach statement can be prepared. I'm not certain this is
possible with the ATTACH syntax. I'll try it soon though.

Mike

-Ursprüngliche Nachricht-
Von: Jon Scully [mailto:[EMAIL PROTECTED] 
Gesendet: Donnerstag, 10. Mai 2007 01:18
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] ATTACH and sqlite3_open()

Simpler than that.  I merely want to attach to two databases (files).
flash.db contains a set of tables that hold non-volatile data; ram.db
contains a set of tables that is re-built (volatile) on re-boot -- but
offers fast, read-only access.  No table-name overlaps, of course.

I want to access both sets of tables, seamlessly, as I should be able to do
using ATTACH, at the command prompt, but do so using the C API.

Just wondering how others do this (Using ATTACH?  Using sqlite3_open()?
Obviously I haven't looked very far into the
sqlite3_open() code to see how it's put together, etc.).

Thanks for the prompt reply.

On 5/9/07, Andrew Finkenstadt <[EMAIL PROTECTED]> wrote:
> On further inspection of your code fragment, it appears you aren't 
> really using (extra) attached  databases, but merely specifying an 
> alternative file to use if the first file is not available.  Calling 
> sqlite3_close(...) will do the right thing, by closing the actual 
> database that succeeded in opening.
>
> --andy


-
To unsubscribe, send email to [EMAIL PROTECTED]

-



-
To unsubscribe, send email to [EMAIL PROTECTED]
-






AW: [sqlite] sqlite and borland c++ builder


2007-04-29

Thread
Michael Ruck

If I understand him correctly, he's having issues including the original
sqlite3.h in his own sources too... He tried to build sqlite again to solve
that problem, but it remains there too.

I would recommed patching up sqlite3.h to conform to BC++ requirements -
changing those structs to something the compiler understands.

Mike 

-Ursprüngliche Nachricht-
Von: John Stanton [mailto:[EMAIL PROTECTED] 
Gesendet: Sonntag, 29. April 2007 18:31
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] sqlite and borland c++ builder

Why not use gcc to compile your library, or use a precompiled DLL?

Jonathan Kahn wrote:
> Hi Ulrik,
>   Thank you for responding.  I'll try anything!  The frustration that all
> this has brought me I am determined to solve it.  
> 
>   If I built SQLite with a C compiler what would be the result?  What
would
> I be able to work with from within c++?  Won't compiling leave me with an
> executable?  I am fairly new to dealing with different compilers and
things,
> so please forgive my ignorance. 
> 
> Thanks a lot,
> - Jon
> 
> 
> -Original Message-
> From: Ulrik Petersen [mailto:[EMAIL PROTECTED] 
> Sent: Sunday, April 29, 2007 2:29 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] sqlite and borland c++ builder
> 
> Hi Jon,
> 
> is it not an option to build SQLite with a C compiler, then call it from 
> within C++?
> 
> 
> Regards,
> 
> Ulrik Petersen
> 
> 
> Jonathan Kahn wrote:
> 
>>Even when I try to build a new dll I get errors with attach.c and it says
>>cannot convert 'void *' to 'Db *', no matter what route I take I always
> 
> hit
> 
>>a bump.  I'm just trying anything at this point
>>
>>- Jon
>>
>>-Original Message-
>>From: Joe Wilson [mailto:[EMAIL PROTECTED] 
>>Sent: Sunday, April 29, 2007 1:59 AM
>>To: sqlite-users@sqlite.org
>>Subject: Re: [sqlite] sqlite and borland c++ builder 
>>
>>I wrote too quickly - sqlite3.h correctly uses __cplusplus for extern "C".
>>
>>But the almalgomated sqlite3.c cannot be compiled from a C++ compiler
>>for the reasons described below.
>>
>>--- Joe Wilson <[EMAIL PROTECTED]> wrote:
>>  
>>
  When I try to use the header I get errors

 [C++ Error] sqlite3.h(1778): E2232 Constant member
'sqlite3_index_info::nConstraint' in class without constructors
  
>>>
>>>It appears it is trying to compile the sqlite header file as if it were
>>>
>>
>>C++.
>>  
>>
>>>Lemme guess - you're using the almalgomated sqlite3.c from 3.3.17, right?
>>>
>>>from the generated sqlite3.c:
>>>
>>> /*
>>> ** Make sure we can call this stuff from C++.
>>> */
>>> #if 0
>>> extern "C" {
>>> #endif
>>>
>>>See the #if 0? That's the problem. It should be:
>>>
>>> #if __cplusplus
>>>
>>>SQLite 3.3.17 has a bug in sqlite3.c generation.
>>>To work around this issue, do this:
>>>
>>> extern "C" {
>>> #include "sqlite3.h"
>>> }
>>>
>>>
>>>__
>>>Do You Yahoo!?
>>>Tired of spam?  Yahoo! Mail has the best spam protection around 
>>>http://mail.yahoo.com 
>>>
>>>
>>>
>>
>

> 
>>-
>>  
>>
>>>To unsubscribe, send email to [EMAIL PROTECTED]
>>>
>>>
>>
>

> 
>>-
>>  
>>
>>>
>>
>>
>>__
>>Do You Yahoo!?
>>Tired of spam?  Yahoo! Mail has the best spam protection around 
>>http://mail.yahoo.com 
>>
>>
> 
>

> 
>>-
>>To unsubscribe, send email to [EMAIL PROTECTED]
>>
> 
>

> 
>>-
>>
>>
>>
> 
>

> -
> 
>>To unsubscribe, send email to [EMAIL PROTECTED]
>>
> 
>

> -
> 
>>
>>  
> 
> 
> 
>

> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>

> -
> 
> 
>

-
> To unsubscribe, send email to [EMAIL PROTECTED]
>

-
> 



-
To unsubscribe, send email to [EMAIL PROTECTED]

-



-
To unsubscribe, send email to [EMAIL PROTECTED]
-






Re: [sqlite] Recursive triggers


2007-04-26

Thread
Michael Ruck

I've taken a further look. The main problem is that the cursor number is
hardcoded, as can be seen 
in the following example (A delete trigger causing further deletes.)

29|OpenRead|3|6|
30|SetNumColumns|3|1|
31|Rewind|3|38|
32|Column|3|0|
33|Rowid|0|0|
34|Ne|355|37|collseq(BINARY)
35|Rowid|3|0|
36|FifoWrite|0|0|
37|Next|3|32|
38|Close|3|0| 

The OpenRead, SetNumColumns, Rewind, Column, Rowid, Next and Close commands
in the example depend 
on fixed cursor identifiers. To be able to call a trigger recursively I'd
need to modify the opcodes to 
support passing the appropriate cursor identifiers around on the stack like
parameters in a C routine.

My thinking was to allow negative cursor identifiers to indicate the
location of the real cursor as an
offset into the VDBE stack. This of course would mean, that the cursors
oldIdx, newIdx need to be
pushed onto the stack by the caller before the gosub and popped afterwards.
In addition an opcode may 
be required to allocate a new cursor identifier dynamically and push it onto
the stack, if the trigger 
requires new cursors.

Do you see any possibility of supporting this without breaking other things?

Mike

-Ursprüngliche Nachricht-
Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Gesendet: Donnerstag, 26. April 2007 21:05
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] Recursive triggers

"Michael Ruck" <[EMAIL PROTECTED]> wrote:
> Hello,
> 
> I've been looking into the way triggers are implemented and was 
> thinking about adding support for recursive triggers, as they would 
> simplify my current project dramatically. What was/is the reason to leave
them out?
> 
> My thoughts were adding recursive triggers by calling them like 
> subroutines (via the VDBE Gosub and Return) on demand. I haven't 
> thought this through, but wanted to ask if there are limitations or 
> blocking points in doing recursive triggers this way? I know that the 
> VDBE stack is limited and queries with very deep triggers may abort, if
the stack is overflowed.
> 
> Is the function sqlite3CodeRowTrigger, the only place I'd need to 
> adjust to support recursive triggers?
> 

Recursive triggers are hard to implement correctly.
But if you think you can do so by tweaking sqlite3CodeRowTrigger, then by
all means give it a whirl.

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



-
To unsubscribe, send email to [EMAIL PROTECTED]

-



-
To unsubscribe, send email to [EMAIL PROTECTED]
-






[sqlite] Recursive triggers


2007-04-26

Thread
Michael Ruck

Hello,

I've been looking into the way triggers are implemented and was thinking
about adding support for recursive triggers, as they would simplify my
current project dramatically. What was/is the reason to leave them out?

My thoughts were adding recursive triggers by calling them like subroutines
(via the VDBE Gosub and Return) on demand. I haven't thought this through,
but wanted to ask if there are limitations or blocking points in doing
recursive triggers this way? I know that the VDBE stack is limited and
queries with very deep triggers may abort, if the stack is overflowed. 

Is the function sqlite3CodeRowTrigger, the only place I'd need to adjust to
support recursive triggers?

Thanks,
Mike


-
To unsubscribe, send email to [EMAIL PROTECTED]
-






AW: [sqlite] Re: DB design questions


2007-04-21

Thread
Michael Ruck

Thanks for your response. Do you have a recommendation for a simpler data
store, which supports only simple queries (like, equals, not equals on
attributes) and transactions?

Thanks,
Mike

-Ursprüngliche Nachricht-
Von: A. Pagaltzis [mailto:[EMAIL PROTECTED] 
Gesendet: Samstag, 21. April 2007 21:17
An: sqlite-users@sqlite.org
Betreff: [sqlite] Re: DB design questions

* Michael Ruck <[EMAIL PROTECTED]> [2007-04-20 16:15]:
> Is there anyone who has experience with this kind of design, do you 
> have better ideas on modelling this kind of data?

This is actually a very typical approach to storing arbitrarily structured
data entities in an SQL database that everyone discovers independently, much
like the adjancecy model is the first thing anyone comes up with for storing
trees in an SQL database.

The problem with this sort of schema (just as with the adjacency
model) is that it makes it very hard to formulate any kind of interesting
query over the data. You’d need a vendor-specific facility for recursive
queries in order to ask anything non- trivial of the database, but such
queries are expensive even where supported, which in SQLite they’re not.
Essentially, you are reducing the SQL engine to a dumb backend store
incapable of complex query logic; complex queries have to be performed in
application code after retrieving the entire set of possibly- relevant data.

You’re better off using some other kind of data store than an SQL database
if you really need storage for that kind of model.

Regards,
--
Aristotle Pagaltzis // <http://plasmasturm.org/>


-
To unsubscribe, send email to [EMAIL PROTECTED]

-



-
To unsubscribe, send email to [EMAIL PROTECTED]
-






[sqlite] DB design questions


2007-04-20

Thread
Michael Ruck

Hello,

I'm currently modelling and designing a new database according the following
specifications. The general idea is the ability to store arbitrary objects,
which have attributes of various kinds. The attributes themselves may be
multivalued. The objects being stored need to be versioned, so that there's
a way to go back to previous versions of an object. The objects represent
metadata of media files and the data itself comes from various automated
sources and manual editing by a user.

My current idea was the following:

- CREATE TABLE objects (id TEXT, version TEXT)
- CREATE TABLE attributes (object_id TEXT, version TEXT, name TEXT, order
INT, type INT, value TEXT)

Is there anyone who has experience with this kind of design, do you have
better ideas on modelling this kind of data?

Thanks,
Mike


-
To unsubscribe, send email to [EMAIL PROTECTED]
-






AW: [sqlite] Still getting "Insertion failed because database is full." errors


2007-04-17

Thread
Michael Ruck

Unfortunately DEBUG builds change timing entirely on windows platforms. I
would suggest creating a release build with symbols.

Mike

-Ursprüngliche Nachricht-
Von: Joel Cochran [mailto:[EMAIL PROTECTED] 
Gesendet: Dienstag, 17. April 2007 20:59
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] Still getting "Insertion failed because database is
full." errors

I've had him sit beside my in my office and attempt to recreate it, both
using his device and mine, but it never happens.  Actually, I did get it to
happen once on his machine, but I was not connected to my PC, so I connected
and tried to recreate it through DEBUG but could not.  The last time it
happened in the field, I had him stop using the device and bring it to me so
that I could see the Stack Trace (which I sent to the list).  With neither
his device nor mine can I recreate the problem in DEBUG.  It is very
frustrating.

Thanks,

Joel


On 4/17/07, Jonas Sandman <[EMAIL PROTECTED]> wrote:
>
> What is the guy on in the field doing that you are not? Are you using 
> his device for the testing?
> Since it takes minutes for him to encounter the error it can't be that 
> hard to recreate. Follow him around for an hour or so and see how he 
> uses the program. It could easily be something he's doing that you 
> aren't...
>
> On 4/17/07, Joel Cochran <[EMAIL PROTECTED]> wrote:
> >
> > The saga continues...
> >
> > I was very excited by the idea that there was something wrong with 
> > the
> CF
> > Card.  The theory fits all the facts: it explains why the original 
> > database threw unspecified errors, it explains why now SQLite is 
> > throwing errors, it explains why I can't reproduce the problem in 
> > house or on my
> machine.  It
> > just seemed to explain everything, so yesterday I went out and 
> > bought a brand-spankin' new SanDisk CF card.  I loaded it up with 
> > the database, installed it on my tester's machine, and this morning 
> > it went back out
> to
> > the field for testing.
> >
> > Within minutes, he encountered the same error.
> >
> > Now I just don't believe the problem is with the card, so I feel 
> > that I
> am
> > right back at square one.  I'm really at my wits end and don't know 
> > what to do next.  I am going to go ahead and install the database on 
> > the device memory instead of removable media, just to test it out, 
> > but I have no faith that it will change anything.  When that fails, 
> > I will send the tester
> out
> > with another device entirely, but again I expect the same results.
> >
> > I'm convinced now that the problem is with the application 
> > architecture, but I have no idea what to look at anymore.  I've 
> > stared and fiddled with
> this
> > code so much that I'm ready to throw in the towel.  But since I'd 
> > like
> to
> > keep my job that isn't an option.  If I had hair, I'd pull it out.
> >
> > Any help at all would be appreciated.
> >
> > --
> > Joel Cochran
> >
> >
> >
> > On 4/13/07, Michael Ruck <[EMAIL PROTECTED]> wrote:
> > >
> > > Unless things have changed recently, the following should still be
> valid
> > > for
> > >
> > > Windows Mobile/Windows CE devices: Usually these devices do not 
> > > power
> > off,
> > > but
> > > stay in a standby state where the memory is always powered. Check 
> > > if that's the case with your system and move as much as possible 
> > > into RAM or a RAM
> > disk,
> > > if that
> > > feature is provided by the windows mobile kernel built for your
> device.
> > >
> > > If that's not possible, I'd suggest replacing CF cards with micro
> drives
> > -
> > > these
> > > are regular hard drives in a CF card format. I'm not up to date on
> > storage
> > > space,
> > > but should be sufficient for your needs.
> > >
> > > To test the cards I'd put them in a card reader format it and fill 
> > > it completely up with zeros. When a flash card erases a byte, it 
> > > sets all bits to
> ones
> > > and
> > > upon
> > > write clears those, which need to be zero. So to test all bits you
> > really
> > > need to
> > > zero out the entire card. This will also give the controller in 
> > > the
> card
> > a
> > > chance
> > > to remap bad sectors with spares. Finally you determine the file 
> > > size
> of
> > > the
> > > card,
> > > when you receive the first write error. This is (approximat



AW: [sqlite] Still getting "Insertion failed because database is full." errors


2007-04-13

Thread
Michael Ruck

Unless things have changed recently, the following should still be valid for

Windows Mobile/Windows CE devices: Usually these devices do not power off,
but 
stay in a standby state where the memory is always powered. Check if that's
the 
case with your system and move as much as possible into RAM or a RAM disk,
if that 
feature is provided by the windows mobile kernel built for your device.

If that's not possible, I'd suggest replacing CF cards with micro drives -
these
are regular hard drives in a CF card format. I'm not up to date on storage
space,
but should be sufficient for your needs.

To test the cards I'd put them in a card reader format it and fill it
completely 
up with zeros. When a flash card erases a byte, it sets all bits to ones and
upon
write clears those, which need to be zero. So to test all bits you really
need to
zero out the entire card. This will also give the controller in the card a
chance
to remap bad sectors with spares. Finally you determine the file size of the
card,
when you receive the first write error. This is (approximately) the number
of bytes
the card can store (at that point in time) and falling.

It seems some cards even return "read errors", when they hit a defective
sector
upon read. Maybe the actual error code just gets lost/mangled on the way up
and the
actual error is just a simple read error ;) I've seen reports about this
with some
digital cameras, which would not even let people view the pictures taken a
minute
ago.

Mike

-Ursprüngliche Nachricht-
Von: John Stanton [mailto:[EMAIL PROTECTED] 
Gesendet: Freitag, 13. April 2007 23:44
An: [EMAIL PROTECTED]
Betreff: Re: [sqlite] Still getting "Insertion failed because database is
full." errors

You might find some joy in the baby disk drives such as installed in the
original ipods.

Can you substitute RAM with a battery backup if the memory card is always in
the device?

Joel Cochran wrote:
> Thanks John and Dennis,
> At least now I have something to look at.  I will look into the CF 
> problem next.
> 
> The database itself gets generated on a PC and then transferred to the 
> CF Card.  During testing and development, this could have been 20-30 
> times a day, constantly erasing and recreating the existing DB.  We 
> have also sent large numbers of JPGs along with the database in the 
> past (there are none now, but have been before).  So these cards have 
> been written over a lot, perhaps that is the problem.
> 
> I think to test this, I will send the device back to the field with a 
> brand new card and see if the problem persists.  If the user can go 
> several days of normal use without the problem, then I'll be convinced 
> that it is the card.  Out of curiosity I just checked the CF cards 
> we've been using: on the development machine (which has NEVER shown 
> the error) I have a SanDisk CF Card.  On the Testing machine that is 
> having the problem, there is a PNY Technologies CF Card.  I wouldn't 
> be surprised if the SanDisk card isn't simply better than the PNY 
> card, so there is something else to consider.
> 
> Once actual field use begins, the database will be replaced every week 
> or so, along with a fair number of images (like 100-300 a week).  The 
> purpose of the application would have every record in the database 
> being updated and some new ones created.  And it would be that way 
> week in and week out, essentially forever.  We may eventually port it 
> over to very small Tablet PCs, but right now it is all Windows Mobile 
> 5.  This is one of the reasons I went with SQLite, so that down the 
> road I wouldn;t have to reinvent the database piece of the software 
> for a different platform.
> 
> Given all this, I will definitely look into the link Dennis sent.  The 
> company is not going to be happy replacing CF cards all the time, so 
> if that can extend the wear then it will be welcome.
> 
> Thanks a lot,
> 
> Joel
> 
> On 4/13/07, Dennis Cote <[EMAIL PROTECTED]> wrote:
> 
>>
>> Joel Cochran wrote:
>> >
>> > Or do you mean over the course of the lifetime of a CF card it can 
>> > only be used so much?  That might apply to this scenario, these 
>> > cards have been written over continuously for the last 6 months.
>> >
>> Joel,
>>
>> Yes, that is exactly the problem. You should look at using a flash 
>> file system such as http://sourceware.org/jffs2/ that uses "wear
leveling"
>> algorithms to spread the writes over all the flash devices blocks if 
>> you are writing often.
>>
>> HTH
>> Dennis Cote
>>
>>
>> -
>> 
>>
>> To unsubscribe, send email to [EMAIL PROTECTED]
>>
>> -
>> 
>>
>>
>>
> 
> 



-
To unsubscribe, send email to [EMAIL PROTECTED]

-







Re: [sqlite] Still getting "Insertion failed because database is full." errors


2007-04-13

Thread
Michael Ruck

That's what I was implying by my question. 

-Ursprüngliche Nachricht-
Von: John Stanton [mailto:[EMAIL PROTECTED] 
Gesendet: Freitag, 13. April 2007 20:56
An: [EMAIL PROTECTED]
Betreff: Re: [sqlite] Still getting "Insertion failed because database is
full." errors

Regular flash memory has a limited number of write cycles before it fails.
Are you hitting this problem by using it for general processing?

Joel Cochran wrote:
> First, to answer John's question: the CF Card is a 1GB card, and the 
> only thing on the card is the SQLite Database (currently 509KB), so I 
> really don't think it is a space problem.  Unless you mean something 
> else by Flash memory?
> 
> Michael,
> 
> Yes, I am doing Selects, and using DataAdapters to fill DataTables 
> (which are then used to populate instances of objects).
> 
> Interesting question about the CF Card.  I have 2 devices right now, 
> one in house for development and one in the field for testing, so I 
> suppose it could be the memory card's problem.  But if it was bad 
> sector space, wouldn't it happen on the same record every time?  As it 
> is, the problem occurs sporadically and unpredictably.  I used the 
> field device here in the office and opened over a hundred records with 
> no error (hence my thinking I had fixed the problem).  The field 
> tester took the device to the field and the first record threw the error.
> 
> And I'm still hung up on why it reports an insertion error when there 
> is no insertion occurring (unless it is some sort of internal 
> mechanism used by the database itself).
> 
> For Mike's last question, and I hope this doesn't sound too 
> newbie-ish, but do you run Select statements in a Transaction?  The 
> short ansewr is no, they are not.  And actually, now that I think 
> about it, I don't think any of the updates/inserts on the device 
> itself are either.  The software that creates the database runs on a 
> PC, and I know all those are in transactions, but on the device I do 
> not think they are.  (This program was originally written for 
> SqlServerCE and was recently converted to SQLite).  Could that have 
> anything to do with it?  Could these database changes build up over 
> time or something like that?  I admit I am not very knowledgeable 
> about Transactions.  I will gladly go and put all the updates into 
> transactions, but would I do the same for Select statements?
> 
> Thanks for the responses,
> 
> Joel
> 
> On 4/13/07, Michael Ruck <[EMAIL PROTECTED]> wrote:
> 
>>
>> Hi,
>>
>> Is this the only device seeing this error or are *all* devices seeing 
>> this error? Have you checked the CF card? May be its just the card, 
>> which is corrupt and you're hitting these bugs at points, where the 
>> file system is hitting a bad sector.
>>
>> Is this running in a transaction?
>>
>> Mike
>>
>> -Ursprüngliche Nachricht-
>> Von: Joel Cochran [mailto:[EMAIL PROTECTED]
>> Gesendet: Freitag, 13. April 2007 17:46
>> An: [EMAIL PROTECTED]
>> Betreff: [sqlite] Still getting "Insertion failed because database is 
>> full."
>> errors
>>
>> Hi folks,
>>
>> I had sent this message out a couple of weeks ago, and I am still 
>> searching for a solution.  I looked at the application design and 
>> made a modest
>> change: I now create a single SQLiteConnection and use it from start 
>> to finish of the application.  Previously I was creating multiple 
>> connections (and disconnecting, disposing them and then running GC), 
>> but all that has been replaced with the single connection approach.
>>
>> At first I thought this had solved the problem, because all in house 
>> testing runs beautifully.  However, as soon as the device is sent to 
>> the field, the error starts again.  Unfortunately, it means that I 
>> have never been able to catch this in debug.  I did, however, change 
>> the error reporting a little and got some more information.  The 
>> SQLiteException I am not getting includes this information:
>>
>> Insertion failed because the database is full database or disk is 
>> full
>>
>> at System.Data.SQLite.SQLite3.Reset()
>> at System.Data.SQLite.SQLite3.Step()
>> at System.Data.SQLite.SQLiteDataReader.NextResult()
>> at System.Data.SQLite.SQLiteDataReader.ctor()
>> at System.Data.SQLite.SQLiteCommand.ExecuteReader()
>> at System.Data.SQLite.SQLiteCommand.ExecuteDbDataReader()
>> at ... etc etc
>>
>>
>> I downloaded the C source and tried to read through it, but honestly 
>> I am not a C programmer and didn't get very far.
>>
>> Other than a p



AW: [sqlite] Still getting "Insertion failed because database is full." errors


2007-04-13

Thread
Michael Ruck

Hi,

Is this the only device seeing this error or are *all* devices seeing this
error? Have you checked the CF card? May be its just the card, which is
corrupt and you're hitting these bugs at points, where the file system is
hitting a bad sector.

Is this running in a transaction?

Mike 

-Ursprüngliche Nachricht-
Von: Joel Cochran [mailto:[EMAIL PROTECTED] 
Gesendet: Freitag, 13. April 2007 17:46
An: [EMAIL PROTECTED]
Betreff: [sqlite] Still getting "Insertion failed because database is full."
errors

Hi folks,

I had sent this message out a couple of weeks ago, and I am still searching
for a solution.  I looked at the application design and made a modest
change: I now create a single SQLiteConnection and use it from start to
finish of the application.  Previously I was creating multiple connections
(and disconnecting, disposing them and then running GC), but all that has
been replaced with the single connection approach.

At first I thought this had solved the problem, because all in house testing
runs beautifully.  However, as soon as the device is sent to the field, the
error starts again.  Unfortunately, it means that I have never been able to
catch this in debug.  I did, however, change the error reporting a little
and got some more information.  The SQLiteException I am not getting
includes this information:

Insertion failed because the database is full database or disk is full

at System.Data.SQLite.SQLite3.Reset()
at System.Data.SQLite.SQLite3.Step()
at System.Data.SQLite.SQLiteDataReader.NextResult()
at System.Data.SQLite.SQLiteDataReader.ctor()
at System.Data.SQLite.SQLiteCommand.ExecuteReader()
at System.Data.SQLite.SQLiteCommand.ExecuteDbDataReader()
at ... etc etc


I downloaded the C source and tried to read through it, but honestly I am
not a C programmer and didn't get very far.

Other than a possible bug, the only thing I can think of is that there is
something fundamentally wrong with my architecture.  What I can't get, is
why the message has anything to do with inserting.  While there are several
actions in my product that Insert into the database, the error never occurs
at those points.  If I understood what was being inserted, perhaps I could
figure out a soultion.

If anyone can help, I'd greatly appreciate it.  The original message is
included below this one.

TIA,

Joel


-- Original message --
Hi all,

I have searched the web, the newsgroups, and the archives, but all I can
find is one reference to someone having this trouble with Python and a bunch
of references that list this as predefined error #13 according to
http://www.sqlite.org/capi3.html.

What I can't find is any help in determining why a program might receive
this error.  The database is only 203KB and has 6 tables (maybe 1,000 rows
in all the tables combined) running off a 1GB CompactFlash card on a Windows
Mobile 5 device with 256MB of onboard RAM: 50MB of that is dedicated to
programs and the rest is storage.  The only app running on the device is the
one in question.  The error occurs at seemingly random times and forces the
user to end the application and start over.

At this point I don't know much else: the user is in the field and I will
have the device back late this afternoon for debugging.  I was hoping to
have a head start before I get the device, because as it is I have no idea
what the cause could be.
-- End Original message --


-
To unsubscribe, send email to [EMAIL PROTECTED]
-






AW: [sqlite] Still getting "Insertion failed because database is full." errors


2007-04-13

Thread
Michael Ruck

Guessing from his call stack he's doing a select. ExecuteReader executes a
statement, which must return a resultset (aka select.) 

-Ursprüngliche Nachricht-
Von: John Stanton [mailto:[EMAIL PROTECTED] 
Gesendet: Freitag, 13. April 2007 17:57
An: [EMAIL PROTECTED]
Betreff: Re: [sqlite] Still getting "Insertion failed because database is
full." errors

Are you sure that you are not exceeding the capacity of the flash memory to
handle writes?

Joel Cochran wrote:
> Hi folks,
> 
> I had sent this message out a couple of weeks ago, and I am still 
> searching for a solution.  I looked at the application design and made 
> a modest
> change: I now create a single SQLiteConnection and use it from start 
> to finish of the application.  Previously I was creating multiple 
> connections (and disconnecting, disposing them and then running GC), 
> but all that has been replaced with the single connection approach.
> 
> At first I thought this had solved the problem, because all in house 
> testing runs beautifully.  However, as soon as the device is sent to 
> the field, the error starts again.  Unfortunately, it means that I 
> have never been able to catch this in debug.  I did, however, change 
> the error reporting a little and got some more information.  The 
> SQLiteException I am not getting includes this information:
> 
> Insertion failed because the database is full database or disk is full
> 
> at System.Data.SQLite.SQLite3.Reset()
> at System.Data.SQLite.SQLite3.Step()
> at System.Data.SQLite.SQLiteDataReader.NextResult()
> at System.Data.SQLite.SQLiteDataReader.ctor()
> at System.Data.SQLite.SQLiteCommand.ExecuteReader()
> at System.Data.SQLite.SQLiteCommand.ExecuteDbDataReader()
> at ... etc etc
> 
> 
> I downloaded the C source and tried to read through it, but honestly I 
> am not a C programmer and didn't get very far.
> 
> Other than a possible bug, the only thing I can think of is that there 
> is something fundamentally wrong with my architecture.  What I can't 
> get, is why the message has anything to do with inserting.  While 
> there are several actions in my product that Insert into the database, 
> the error never occurs at those points.  If I understood what was 
> being inserted, perhaps I could figure out a soultion.
> 
> If anyone can help, I'd greatly appreciate it.  The original message 
> is included below this one.
> 
> TIA,
> 
> Joel
> 
> 
> -- Original message -- Hi all,
> 
> I have searched the web, the newsgroups, and the archives, but all I 
> can find is one reference to someone having this trouble with Python 
> and a bunch of references that list this as predefined error #13 
> according to http://www.sqlite.org/capi3.html.
> 
> What I can't find is any help in determining why a program might 
> receive this error.  The database is only 203KB and has 6 tables 
> (maybe 1,000 rows in all the tables combined) running off a 1GB 
> CompactFlash card on a Windows Mobile 5 device with 256MB of onboard 
> RAM: 50MB of that is dedicated to programs and the rest is storage.  
> The only app running on the device is the one in question.  The error 
> occurs at seemingly random times and forces the user to end the 
> application and start over.
> 
> At this point I don't know much else: the user is in the field and I 
> will have the device back late this afternoon for debugging.  I was 
> hoping to have a head start before I get the device, because as it is 
> I have no idea what the cause could be.
> -- End Original message --
> 



-
To unsubscribe, send email to [EMAIL PROTECTED]

-



-
To unsubscribe, send email to [EMAIL PROTECTED]
-






AW: [sqlite] Exclusive Access


2007-04-10

Thread
Michael Ruck

You can only open one connection in exclusive mode - even in one process.

-Ursprüngliche Nachricht-
Von: John Stanton [mailto:[EMAIL PROTECTED] 
Gesendet: Dienstag, 10. April 2007 20:14
An: sqlite-users@sqlite.org
Betreff: [sqlite] Exclusive Access

A quick question in case someone has experience before I delve into the
source and write some test programs.

Is there any impediment for two threads to have concurrent read mode access
to a database opened exclusively by a process?  Is it feasible that they use
the same database connection or would they each need their own database
connection and use shared cache mode?

Ideally I should like them to share a connection and a common local cache
but can think of many reasons why that might not be possible.



-
To unsubscribe, send email to [EMAIL PROTECTED]

-



-
To unsubscribe, send email to [EMAIL PROTECTED]
-






AW: [sqlite] SQLite and nested transactions


2007-04-09

Thread
Michael Ruck

That Sybase and MS SQL match on their behavior is no surprise considering
their common heritage ;) I suppose MS (and sybase for that matter) hasn't
done anything on the transaction support since they've split their code
bases.

-Ursprüngliche Nachricht-
Von: Griggs, Donald [mailto:[EMAIL PROTECTED] 
Gesendet: Montag, 9. April 2007 19:50
An: sqlite-users@sqlite.org
Betreff: RE: [sqlite] SQLite and nested transactions

Regarding:  "...As Igor pointed out this does not resemble a full
implementation of transactions, as nested transactions can be committed and
rolled back independently of the outer parent transaction."


Nonetheless, it would seem, just from the couple of pages below, that some
DB vendors find the less-than-full implementation of nested
transactions to be useful for at least some purposes.   

(I.e., an implementation in which inner transactions do little more than
adjust counters.  If anything is rolled back, then entire outer transaction
is rolled back.) 

I make no claim to being an expert here -- I'm just a googler.  ;-)

http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__
BookTextView/53608;pt=53608;uf=0 

"Nested transactions
You can nest transactions within other transactions. When you nest begin
transaction and commit transaction statements, the outermost pair actually
begin and commit the transaction. The inner pairs just keep track of the
nesting level. Adaptive Server does not commit the transaction until the
commit transaction that matches the outermost begin transaction is issued.
Normally, this transaction "nesting" occurs as stored procedures or triggers
that contain begin/commit pairs call each other." 


http://msdn2.microsoft.com/en-us/library/ms189336.aspx

"SQL Server 2005 Books Online
Nesting Transactions  

Explicit transactions can be nested. This is primarily intended to support
transactions in stored procedures that can be called either from a process
already in a transaction or from processes that have no active transaction.

The following example shows the intended use of nested transactions. The
procedure TransProc enforces its transaction regardless of the transaction
mode of any process that executes it. If TransProc is called when a
transaction is active, the nested transaction in TransProc is largely
ignored, and its INSERT statements are committed or rolled back based on the
final action taken for the outer transaction. If TransProc is executed by a
process that does not have an outstanding transaction, the COMMIT
TRANSACTION at the end of the procedure effectively commits the INSERT
statements..."


[opinions are my own, not necessarily those of my company]


-
To unsubscribe, send email to [EMAIL PROTECTED]

-



-
To unsubscribe, send email to [EMAIL PROTECTED]
-






AW: [sqlite] SQLite and nested transactions


2007-04-09

Thread
Michael Ruck

Yes, but this violates ACID principles. As Igor pointed out this does not
resemble a full implementation of transactions, as nested transactions can
be commited and rolled back independently of the outer parent transaction.

Mike

-Ursprüngliche Nachricht-
Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Gesendet: Montag, 9. April 2007 18:38
An: sqlite-users@sqlite.org
Cc: Dennis Cote
Betreff: Re: [sqlite] SQLite and nested transactions


So. If i read this code right.
* The transaction_level keeps track of how many nested transactions have
occurred.
* Only the parent transaction allows the commit.

In this case, only a single journal is required (the parent journal).
Thanks,
Ray


 Dennis Cote <[EMAIL PROTECTED]> wrote: 
> Darren Duncan wrote:
> >
> > I will clarify that child transactions are just an elegant way of 
> > partitioning a larger task, and that parent transactions always 
> > overrule children; even if a child transaction commits successfully, 
> > a rollback of its parent means there are no lasting changes.
> Darren,
> 
> Because of this, and the fact that a transaction is basically a 
> guarantee that all or none of the enclosed statements are executed, it 
> is much simpler to implement nested transactions using a counter and 
> the existing transaction API in a set of wrapper functions. There is 
> no need to maintain all the intermediate state information. All you 
> need to know is the current nesting level, and if any enclosed 
> transaction was rolled back.
> 
> The following code shows the basic process.
> 
> int transaction_level = 0;
> bool transaction_failed;
> 
> void begin_nested_transaction()
> {
> if (transaction_level++ == 0) {
> transaction_failed = false;
> sqlite3_exec("begin");
> }
> }
> 
> void commit_nested_transaction()
> {
> if (--transaction_level == 0)
> if (transaction_failed)
> sqlite3_exec("rollback");
> else
> sqlite3_exec("commit");
> }
> 
> void rollback_nested_transaction()
> {
> transaction_failed = true;
> commit_transaction();
> }
> 
> HTH
> Dennis Cote
> 
> --
> --- To unsubscribe, send email to 
> [EMAIL PROTECTED]
> --
> ---
> 



-
To unsubscribe, send email to [EMAIL PROTECTED]

-



-
To unsubscribe, send email to [EMAIL PROTECTED]
-






Re: [sqlite] Master table with child FTS table


2007-04-07

Thread
Michael Ruck

Ok, then I'm out. I don't have an answer for the last one either - however
you could do this on a view, which joins both tables.

Mike 

-Ursprüngliche Nachricht-
Von: Paul Quinn [mailto:[EMAIL PROTECTED] 
Gesendet: Samstag, 7. April 2007 17:54
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] Master table with child FTS table

I did set up a trigger for DELETEs, but even then the fts_table would find a
way to have more rows than master after a short while. Some delete's are
simply being ignored/failed by the FTS module it seems.

I would have liked to also use triggers for INSERTs, but how do you create a
trigger when the data to be inserted on the trigger is not part of the 'NEW'

reference?

-PQ


- Original Message -
From: "Michael Ruck" <[EMAIL PROTECTED]>
To: <sqlite-users@sqlite.org>
Sent: Saturday, April 07, 2007 8:35 AM
Subject: AW: [sqlite] Master table with child FTS table


How about managing fts_table using triggers attached to the master table?
That should take care of synchronization issues IMHO.

Mike




-
To unsubscribe, send email to [EMAIL PROTECTED]

-



-
To unsubscribe, send email to [EMAIL PROTECTED]
-






AW: [sqlite] Master table with child FTS table


2007-04-07

Thread
Michael Ruck

How about managing fts_table using triggers attached to the master table?
That should take care of synchronization issues IMHO.

Mike

-Ursprüngliche Nachricht-
Von: Paul Quinn [mailto:[EMAIL PROTECTED] 
Gesendet: Samstag, 7. April 2007 09:08
An: sqlite-users@sqlite.org
Betreff: [sqlite] Master table with child FTS table

I have a database setup that creates a master table, and a child 'Full Text
Search' (FTS) table that matches it by rowid. My problem is with keeping the
FTS table in sync with the master. Let me explain in SQL:

setup like so:
CREATE TABLE master (a, b);
CREATE VIRTUAL TABLE fts_table USING fts2(words);

inserts like so:
INSERT INTO master (a, b) VALUES (blah, blah); INSERT INTO fts_table (rowid,
words) VALUES (last_insert_rowid(), 'some words');

searches like so:
SELECT a, b, words FROM master JOIN fts_table ON master.rowid ==
fts_table.rowid WHERE words MATCH 'word';


In my scenario, I need to do a number of updates and deletes to the master
table and the fts_table needs to follow suit. However a number of problems
have surfaced:

FTS does not work for INSERT OR REPLACE. At least that is what I have found,
didn't find any docs on this.
So I resort to using delete/insert like so:
DELETE FROM fts_table WHERE rowid = ;
INSERT INTO fts_table (rowid, words) VALUES (,
'some words');

However, I am finding that this is not reliable. The FTS table will still go
out of sync. Row counts will not match after some indeterminant amount of
updates. It seems either some DELETE's are failing, or the INSERTs into
existing rowid's are failing (silently - the return error is always ok). And
after the out of sync problems start occuring, sometimes the FTS goes to pot
and the SELECT sql's start failing with SQLITE_ERROR.

So, is there a better way to do what I am trying to do?

I'm using SQLite 3.3.13, and I've tried both FTS1 and FTS2 (using FTS2 right
now).
Thanks in advance for any help.

-PQ



-
To unsubscribe, send email to [EMAIL PROTECTED]
-






AW: AW: AW: [sqlite] Function Language


2007-04-06

Thread
Michael Ruck

If you come up with something, please share it.

Mike 

-Ursprüngliche Nachricht-
Von: John Stanton [mailto:[EMAIL PROTECTED] 
Gesendet: Freitag, 6. April 2007 20:49
An: sqlite-users@sqlite.org
Betreff: Re: AW: AW: [sqlite] Function Language

By using making the connection from browser to server an RPC model I have
mapped the interface to the database instead of trying to map the database
to the Javascript objects.  That also minimizes the network traffic.  My
application server design has a criterion that network traffic should be
minimized.  It is a great way to increase effective bandwidth.

The generated HTML/Javascript pages have no redundancy and are compressed if
they are above a threshold size.  The RPC between the browser and the server
uses sparse messages and not a great deal of redundancy.  It also minimizes
the extent of execution of the slow, interpreted Javascript by partitioning
as much basic housekeeping to the more efficient server side processing.

The server holds each database open as exclusive and shares it between users
and multiple user connections, minimizing database open and close actions,
keeping local cacheing and avoiding file locking.

Currently I am dreaming up ways of implementing the Javascript function
level in Sqlite.  Creating the JS VM when the DB opens and having one VM per
open DB instance seems to be a way of avoiding contentions and getting
fairly efficient execution and a reasonable route for an initial prototype.
Garbage collection should take care of stale objects.  I can store user
defined functions as text items in a dedicated table defining aggregate and
scalar functions in a database.  A syntax directed editor linked to JSLint
and RCS can maintain syntactically correct code with version control.

Javascript would not be a good way to implement simple functions in Sqlite,
the current custome function interface to native code is far prefereable for
that but it is appropriate for implementing larger functions and one which
require frequent user alterations.

Michael Ruck wrote:
> How do you treat objects containing other objects? JS has the 
> capabilities to build powerful object models with hashes etc. Objects, 
> such as these don't map nicely to the relational model.
> 
> I'm going a different way - I'm using static HTML, which requests JSON 
> from the server and uses this to update the UI on the client side. 
> However mapping JSON to SQL is still somewhat of an issue. I've 
> tackled it by including metadata in JSON, but that's not very clean 
> and I'm not really happy with it (yet.)
> 
> Mike
> 
> -Ursprüngliche Nachricht-
> Von: John Stanton [mailto:[EMAIL PROTECTED]
> Gesendet: Freitag, 6. April 2007 18:22
> An: sqlite-users@sqlite.org
> Betreff: Re: AW: [sqlite] Function Language
> 
> Thankyou for the thoughtful comments.  It strikes me that a JS object 
> and an Sqlite row map nicely.  When I was writing the part of my 
> application server which encapsulates Sqlite rows in JSON I was struck 
> by how simple the interface was, particularly compared to XML which 
> involves a little more attention to create well formed XML and a whole 
> lot more involvement to parse and generate on the client side.  Adding 
> JSON as an alternative to XML was a good idea.
> 
> I do not try to create dynamic HTML pages using JS and use a much 
> simpler and more efficent application specific language compiled to 
> byte code (somewhat analogous to Java or VDBE bytecode).  At that 
> level JS is merged into the page in such a way that the JS is matched 
> to the browser and locale to remove redundancy.
> 
> The sophistication in the otherwise simple application specific 
> language is an inference engine to resolve a knowledge base of rules 
> stored in the Sqlite database and an event driven capability linked to 
> the activity of the database.
> 
> The same capability which creates dynamic HTML/Javascript will also 
> generate PostScript to deliver PDF and no doubt other formats which 
> may show up in the future.
> 
> Michael Ruck wrote:
> 
>>I am all for it and am very interested in your project as I'm working 
>>on something similar. I've been using JS to create dynamic HTML pages 
>>in combination with SQLite using a JSON wrapper from this list. The 
>>only issue I see here is the treatment of JS objects - there's again 
>>the OO and relation mismatch involved. You may need some kind of OO 
>>mapper to map to SQLite tables/views.
>>
>>HTH,
>>Mike
>>
>>-Ursprüngliche Nachricht-
>>Von: John Stanton [mailto:[EMAIL PROTECTED]
>>Gesendet: Freitag, 6. April 2007 02:43
>>An: sqlite-users@sqlite.org
>>Betreff: [sqlite] Function Language
>>
>>I have been looking around at handy way to implement elaborate 
>



AW: [sqlite] Function Language


2007-04-06

Thread
Michael Ruck

I am all for it and am very interested in your project as I'm working on
something similar. I've been using JS to create dynamic HTML pages in
combination with SQLite using a JSON wrapper from this list. The only issue
I see here is the treatment of JS objects - there's again the OO and
relation mismatch involved. You may need some kind of OO mapper to map to
SQLite tables/views.

HTH,
Mike

-Ursprüngliche Nachricht-
Von: John Stanton [mailto:[EMAIL PROTECTED] 
Gesendet: Freitag, 6. April 2007 02:43
An: sqlite-users@sqlite.org
Betreff: [sqlite] Function Language

I have been looking around at handy way to implement elaborate functions in
Sqlite.  Implementing PL/SQL came to mind but recently it struck me that
Javascript has data rules very similar to Sqlite and has the useful property
that all executables are just data.

Does anyone have views for or against Javascript as an embedded language for
realizing functions?  I see as a positive its data typing affinity with
Sqlite and its widespread usage and a large base of active programmers.


-
To unsubscribe, send email to [EMAIL PROTECTED]

-



-
To unsubscribe, send email to [EMAIL PROTECTED]
-






AW: [sqlite] storing funky text in TEXT field


2007-04-05

Thread
Michael Ruck

Actually UTF-8 is the better choice compared to UTF-16. I would start
turning on UTF-8 as the character set on your web server and ensure that it
is also specified as the document character set in all generated HTML pages.
This gives browsers a hint about the text encoding to use to render pages.

Mike 

-Ursprüngliche Nachricht-
Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Im Auftrag von P
Kishor
Gesendet: Donnerstag, 5. April 2007 20:19
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] storing funky text in TEXT field

On 4/5/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> "P Kishor" <[EMAIL PROTECTED]> wrote:
> >
> > But, the line I quoted in my OP, namely "TEXT. The value is a text 
> > string, stored using the database encoding (UTF-8, UTF-16BE or 
> > UTF-16-LE)." is confusing me. What is this "database encoding" and 
> > where do I have to tackle this?
> >
> > Do I have to turn on some kind of magic pragma setting? How? Where?
> > And, which one is preferable? UTF-8 or UTF-16, or one of the 16 
> > variants, BE/LE?
> >
>
> http://www.sqlite.org/pragma.html#pragma_encoding
>


well, that was easy. My apologies for coming so close to the answer myself
but not really looking.

Ok, so here is the follow-up question, which hopefully will not be so
"clueless" as the first one. I entered "pragma encoding;" for a db I already
have -- it returns UTF-8, which seems like the default setting. Which
explains why when I first created the db by inserting data from an existing
file, I got all the funky letters. But, when I updated the same data via my
web app, those funky letters got clobbered. So, I have to create a better
web application. However, I do want a starting advice -- is UTF-8 good
enough for what I am concerned about or should I start learning about UTF-16
BE/LE?

--
Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies,
UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation
http://www.osgeo.org/education/
-
collaborate, communicate, compete
=


-
To unsubscribe, send email to [EMAIL PROTECTED]

-



-
To unsubscribe, send email to [EMAIL PROTECTED]
-






RE: [sqlite] How To Use ATTACH?


2007-02-01

Thread
michael . ruck

I don't know your Wrapper, but try this:

CppSQLite3DB db; 

db.open("Stocks.db"); 

db.execDML("ATTACH 'Options.db' AS OPT;"); 

sSQL = "UPDATE Stocks SET bOption=1 WHERE rowid IN "; 
sSQL += "(SELECT Stocks.rowid FROM Stocks, OPT.Options "; 
sSQL += "WHERE Stocks.sStockSymbol = OPT.Options.sStockSymbol); "; 


db.execDML(sSQL.c_str()); 

Note that I prepended the database name assigned in the Attach statement in 
your Update/Select statement.

Mike

>I know how to use ATTACH with sqlite3.exe but I am having problems using 
>it in C++.  I am using a wrapper and what I am trying to do is 
>illustrated by:
>
>CppSQLite3DB db;
>
>db.open("Stocks.db");
>
>db.execDML("ATTACH   'Options.db' AS OPT;");
>
>  sSQL = "UPDATE Stocks SET bOption=1 WHERE rowid IN ";
>  sSQL += "(SELECT Stocks.rowid FROM Stocks, Options ";
>  sSQL += "WHERE Stocks.sStockSymbol = Options.sStockSymbol); ";
>
>
>   db.execDML(sSQL.c_str());
>
>but this gives me an error saying table Options (in database file 
>Options.db) is not available.
>
>How do I get ATTACH to work in C++?
>
>Thanks,
>  Roger
>
>-
>To unsubscribe, send email to [EMAIL PROTECTED]
>-
>

-
To unsubscribe, send email to [EMAIL PROTECTED]
-






AW: [sqlite] Obtaining randomness on win32


2007-01-29

Thread
Michael Ruck

I'm not sure if this helps, but QueryPerformanceCounter could be
a source of semirandom 64-bit integers. It returns the processors
running time in nanoseconds. I'm not aware of anything, which returns
really random values.

On Windows itself you could use the CryptAcquireContext, CryptGenRandom 
and CryptReleaseContext. I think that doesn't work on WinCE though.

Michael

> -Ursprüngliche Nachricht-
> Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
> Gesendet: Montag, 29. Januar 2007 17:15
> An: sqlite-users@sqlite.org
> Betreff: [sqlite] Obtaining randomness on win32
> 
> The pseudo-random number generator (PRNG) in SQLite is 
> becoming more important so it seem good to make sure it is 
> well seeded.  On Unix this is easy - just open /dev/urandom 
> and read out as much randomness as you need.  But I do not 
> know how to do this on
> win32 and wince.  The current implementation seeds the random 
> number generator on these platforms by grabbing a copy of the 
> current system time.  See the sqlite3WinRandomSeed() function 
> in os_win.c for details.  This is not a very good method for 
> seeding a PRNG.
> 
> Can someone with more knowledge of win32 and wince please 
> suggest a better method for seeding the PRNG on those platforms?
> 
> --
> D. Richard Hipp  <[EMAIL PROTECTED]>
> 
> 
> --
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
> --
> ---
> 
> 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-






[sqlite] SQLite JSON interface?


2007-01-20

Thread
Michael Ruck

Hi,

Is there a JSON interface for SQLite (in C/C++)? Any GPL/LGPL compatible
license or public domain would do. Someone must have written something like
it. I need to export a set of tables to JSON .js files to serve on a
webserver, the files are generated off-line, not on demand.

Michael


-
To unsubscribe, send email to [EMAIL PROTECTED]
-






AW: [sqlite] Of shared cache, table locks and transactions


2007-01-14

Thread
Michael Ruck

Yes, I second this opinion. However I believe sqlite is ACID, just not when
shared cache mode is enabled...

Mike 

-Ursprüngliche Nachricht-
Von: Ken [mailto:[EMAIL PROTECTED] 
Gesendet: Sonntag, 14. Januar 2007 17:00
An: sqlite-users@sqlite.org; [EMAIL PROTECTED]
Betreff: Re: [sqlite] Of shared cache, table locks and transactions

Yes exactly my point for some time now!!!
   
  I think it would be nice if sqlite could optionally maintain a pre-write
state version of the cached page in memory. This really means maintaining
some form of page versioning, which is already done via the pager writing to
the journal. The pager must write the original page that undergoes
modification to the journal file before it can manipulate the cached
version. 
   
  To expedite performance the journaled pages could be maintained in memory
as well as written to disk. That way a writer and reader could co-exist. 
 Writer modifies a page, (acquiring a Write page lock). 
  Make a copy in memory, saving a reference to this via the cache as
well as its offset in the journal. 
 The origainal Page is then written to the disk journal.
 If the Journal cache exceeds memory capacity, Just release pages and
retain a file offset pointer in memory.
   
 The reader when encountering a locked page, could then check the cached
journal pages.  
 If not found in the cache use the file offset reference and read this
in from the journal file.
  
  This would take care of the simple case of writer blocking! As there is
only ever 1 writer. The original page is sufficient to take care of
(isolation) in ACID.
   
  As it stands today, sqlite imho, is  ACD, it is not have isolated
transactional capabilities.
   
  
Dan Kennedy <[EMAIL PROTECTED]> wrote:
  On Sat, 2007-01-13 at 23:55 -0800, Peter James wrote:
> Hey folks...
> 
> I have a situation that caused me a little head-scratching and I'm 
> wondering if it's intended behavior or not.
> 
> I'm running a server thread (roughly based on test_server.c in the 
> distro) on top of the 3.3.6 library. The effectve call sequence in 
> question (all from one thread) looks something like this:
> 
> sqlite3_open("/path/to/db", _one);
> 
> sqlite3_prepare(db_one, "CREATE TABLE foo (id);", -1, _one, 
> NULL); sqlite3_step(stmt_one); sqlite3_finalize(stmt_one);
> 
> sqlite3_prepare(db_one, "BEGIN DEFERRED;", -1, _one, NULL); 
> sqlite3_step(stmt_one); sqlite3_finalize(stmt_one);
> 
> sqlite3_prepare(db_one, "INSERT INTO foo VALUES (123);", -1, 
> _one, NULL); sqlite3_step(stmt_one); sqlite3_finalize(stmt_one);
> 
> sqlite3_prepare(db_one, "SELECT count(*) FROM foo;", -1, _one, 
> NULL); sqlite3_step(stmt_one); // point of interest #1 
> sqlite3_column_int(stmt_one, 0); sqlite3_finalize(stmt_one);
> 
> // new connection here, previous transaction still pending...
> sqlite3_open("/path/to/db", _two);
> 
> sqlite3_prepare(db, "SELECT count(*) FROM foo;", -1, _two, NULL); 
> // point of interest #2 sqlite3_step(stmt_two); // point of interest 
> #3 sqlite3_column_int(stmt_two, 0); sqlite3_finalize(stmt_two);
> 
> If shared cache is DISabled, then I get "1" on the first point of 
> interest and "0" on the third point of interest, which is what I'd 
> expect. The database file is at a RESERVED lock state in both 
> locations, and the first point of interest gets uncommitted data since 
> it's in the same connection, while the second point of interest can't 
> yet see that data since it's a different connection and the transaction is
not yet committed.
> 
> On the other hand, if shared cache is ENabled, then I get "1" on the 
> first point of interest and SQLITE_LOCKED at the second point of 
> interest. This would seem to indicate an actual degradation of 
> concurrency by using shared caching. Without shared caching, readers 
> in the same thread as a pending writer are allowed. With shared 
> caching, they are not. The EXPLAIN output seems to confirm that this 
> is a result of the binary nature of table locks vs. the staged nature of
sqlite file locks.

Here's how I figure it:

When the shared-cache was DISabled, the first connection loaded it's own
cache and then modified it (the INSERT statement). No writing to the disk
has occured at this point, only the cache owned by the first connection. 

The second connection then loaded up it's own cache (from the file on
disk) and queried it. No problem here.

However when the shared-cache was ENabled the second connection piggy-backed
onto (i.e shares) the cache opened by the first connection.
Since the pages corresponding to table "foo" in this cache contain
uncommitted modifications, SQLite prevents the second connection from
reading them - returning SQLITE_LOCKED. Otherwise, the second connection
would be reading uncommitted data.

So you're right, when you use shared-cache mode there is less concurrency in
some circumstances.

Dan.



-
To unsubscribe, 



AW: [sqlite] Equivalent of OLE object da


2007-01-09

Thread
Michael Ruck

An OLE object is persisted into a stream of bytes. You can store OLE objects
into SQLite as a BLOB, but you need to make your own (specialized)
implementation of one of the IPersistXXX interfaces (most likely
IPersistStream), which stores the object into an SQLite column/reads a
serialized object from an SQLite column.

Michael

-Ursprüngliche Nachricht-
Von: shivaranjani [mailto:[EMAIL PROTECTED] 
Gesendet: Dienstag, 9. Januar 2007 05:57
An: sqlite-users@sqlite.org
Betreff: [sqlite] Equivalent of OLE object da

Hi all,

 

 

Is there any equivalent of OLE object datatype of Access in SQlite dll.

 

 

Regards,

 

A. Shivaranjani



-
To unsubscribe, send email to [EMAIL PROTECTED]
-






AW: [sqlite] sqlite performance, locking & threading


2007-01-03

Thread
Michael Ruck

Hi Emerson,

I just hope you don't reinvent the wheel ;) I haven't yet had the need to
index things the way you describe it. May be I should take that as one of my
next pet projects to get a handle on this type of task.

The problem as I see it is basically, that any way you design this: If the
storage tasks take 90% of your indexing time, then any parallelization may
be a waste of effort. Even if you use a synchronization object you're
essentially serializing things in a (complicated) multithreaded way...

As far as static initialization: That it occurs before main() and is out of
your control was the point I was getting across. That's why I wrote that
this type of initialization should be avoided, unless there's no better
design for it.

Michael

-Ursprüngliche Nachricht-
Von: Emerson Clarke [mailto:[EMAIL PROTECTED] 
Gesendet: Mittwoch, 3. Januar 2007 20:31
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] sqlite performance, locking & threading

Michael,

Thanks for the advice.  During the indexing process i need to select and
optionally insert records into a table so i cant ignore the outcomes.

Basically the indexing process does compression, so for each document it
inserts words into a table and looks up keys.  Every word in the document
gets swapped with a key, and new keys are inserted as needed.

There are some problems with splitting the work up in a different way as you
suggested. I would either end up with a lot of queues or i would have to
stagger the work so that the entire data set gets processed in stages which
doesnt scale very well and isnt particularly fault tollerant.  When building
an index, you want the structure to be built up progressively, so that you
can pause the process and resume it later on whilst still having useful
results.

I would be worried that in a queued design, the overhead and bottlenecks
caused by the buffering, message passing, and context switching would reduce
the performance to that of a single thread.
Especially since the database operations represent 90% of the work, all you
would really be doing is attempting to serialise things in a multithreaded
way.

Im sure having worked on multithreaded systems you appreciate that sometimes
simple designs are better, and i think i have a pretty good handle on what
it is that im trying to do.

You never have control over static initialisation, it happens before main().
If i was writing very specific code to suit just this situation then maybe
as you say i wouldnt need to worry about it.  But im also writing a database
api, and that api is used for many different things.  My considderations are
not just for this one problem, but also for the best general way to code the
api so that it is safe and efficient in all circumstances.  So far the
client/server design is the only way i can achieve true thread safety.

If i could work out why sqlite3_step() causes problems across multiple
threads i could probably make things a little faster and i could do away
with the need for a client/server design.

Emerson


On 1/3/07, Michael Ruck <[EMAIL PROTECTED]> wrote:
> Emerson,
>
> Now I understand your current implementation.  You seemingly only 
> partially split up the work in your code. I'd schedule the database 
> operation and not wait on the outcome, but start on the next task. 
> When the database finishes and has retrieved its result, schedule some 
> work package on a third thread, which only processes the results etc. 
> Split up the work in to repetitive, non blocking tasks. Use multiple 
> queues and dedicated threads for parts of the operation or thread pools,
which process queues in parallel if possible.
> From what I can tell you're already half way there.
>
> I still don't see your static initialization problem, but that's 
> another story. Actually I'd avoid using static initialization or 
> static (singleton) instances, unless the design really requires it. 
> Someone must control startup of the entire process, have that one 
> (probably main/WinMain) take care that the work queues are available. 
> Afterwards the order of thread starts doesn't matter... Actually it is 
> non-deterministic anyway (unless you serialize this yourself.)
>
> Michael
>
> -Ursprüngliche Nachricht-
> Von: Emerson Clarke [mailto:[EMAIL PROTECTED]
> Gesendet: Mittwoch, 3. Januar 2007 15:14
> An: sqlite-users@sqlite.org
> Betreff: Re: [sqlite] sqlite performance, locking & threading
>
> Michael,
>
> Im not sure that atomic operations would be a suitable alternative.
> The reason why im using events/conditions is so that the client thread 
> blocks until the server thread has processed the query and returned 
> the result.  If i did not need the result then a simple queueing 
> system with atomic operations or critical sections would be fine i guess.
>
> The client thread must always block or spin until the 



AW: [sqlite] sqlite performance, locking & threading


2007-01-03

Thread
Michael Ruck

Emerson,

Now I understand your current implementation.  You seemingly only partially
split up the work in your code. I'd schedule the database operation and not
wait on the outcome, but start on the next task. When the database finishes
and has retrieved its result, schedule some work package on a third thread,
which only processes the results etc. Split up the work in to repetitive,
non blocking tasks. Use multiple queues and dedicated threads for parts of
the operation or thread pools, which process queues in parallel if possible.
>From what I can tell you're already half way there.

I still don't see your static initialization problem, but that's another
story. Actually I'd avoid using static initialization or static (singleton)
instances, unless the design really requires it. Someone must control
startup of the entire process, have that one (probably main/WinMain) take
care that the work queues are available. Afterwards the order of thread
starts doesn't matter... Actually it is non-deterministic anyway (unless you
serialize this yourself.)

Michael

-Ursprüngliche Nachricht-
Von: Emerson Clarke [mailto:[EMAIL PROTECTED] 
Gesendet: Mittwoch, 3. Januar 2007 15:14
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] sqlite performance, locking & threading

Michael,

Im not sure that atomic operations would be a suitable alternative.
The reason why im using events/conditions is so that the client thread
blocks until the server thread has processed the query and returned the
result.  If i did not need the result then a simple queueing system with
atomic operations or critical sections would be fine i guess.

The client thread must always block or spin until the server thread has
completed the query.  Critical sections cant be efficiently used to notify
other threads of status change.  I did try using critical sections in this
way, by spinning until the server thread takes a lock, then blocking and
eventually waiting for the server thread to finish.  But since there is no
way to block the server thread when there is no work to do both the client
and server thread must sleep which induces context switching anyway.

If you used atomic operations, how would you get the client thread to block
and the server thread to block when it is not processing ?

Events/conditions seemed to be the best solution, the server thread never
runs when it doesnt need to and always wakes up when there is processing to
be done.

The static initialisation problem occurs becuase the server thread must be
running before anything which needs to use it.  If you have a static
instance of a class which accesses a database and it is initalised before
the static instance which controls the server thread, you have a problem.
It can be overcome using the initialise on first use idiom, as long as your
careful to protect the initalisation with atomic operations, but its still a
bit complicated.

Emerson


On 1/3/07, Michael Ruck <[EMAIL PROTECTED]> wrote:
> Hi Emerson,
>
> Another remark: On Windows using Events synchronization objects 
> involves additional kernel context switches and thus slows you down 
> more than necessary. I'd suggest using a queue, which makes use of the 
> InterlockedXXX operations (I've implemented a number of those, 
> including priority based ones - so this is possible without taking a 
> single lock.) or to use critical sections - those only take the kernel 
> context switch if there really is lock contention. If you can reduce 
> the kernel context switches, you're performance will likely increase
drastically.
>
> I also don't see the static initialization problem: The queue has to 
> be available before any thread is started. No thread has ownership of 
> the queue, except may be the main thread.
>
> Michael
>
>
> -Ursprüngliche Nachricht-
> Von: Emerson Clarke [mailto:[EMAIL PROTECTED]
> Gesendet: Mittwoch, 3. Januar 2007 00:57
> An: sqlite-users@sqlite.org
> Betreff: Re: [sqlite] sqlite performance, locking & threading
>
> Nico,
>
> I have implemented all three strategies (thead specific connections, 
> single connection multiple threads, and single thread server with 
> multiple client threads).
>
> The problem with using thread specific contexts is that you cant have 
> a single global transaction which wraps all of those contexts.  So you 
> end up having to use fine grained transactions, which decreases
performance.
>
> The single connection multiple thread alternative apparently has 
> problems with sqlite3_step being active on more than one thread at the 
> same moment, so cannot easily be used in a safe way.  But it is by far 
> the fastest and simplest alternative.
>
> The single thread server solution involves message passing between 
> threads, and even when this is done optimally with condition variables 
> (or events on
> windows)



AW: [sqlite] sqlite performance, locking & threading


2007-01-03

Thread
Michael Ruck

Hi Emerson,

Another remark: On Windows using Events synchronization objects involves
additional kernel context switches and thus slows you down more than
necessary. I'd suggest using a queue, which makes use of the InterlockedXXX
operations (I've implemented a number of those, including priority based
ones - so this is possible without taking a single lock.) or to use critical
sections - those only take the kernel context switch if there really is lock
contention. If you can reduce the kernel context switches, you're
performance will likely increase drastically.

I also don't see the static initialization problem: The queue has to be
available before any thread is started. No thread has ownership of the
queue, except may be the main thread.

Michael


-Ursprüngliche Nachricht-
Von: Emerson Clarke [mailto:[EMAIL PROTECTED] 
Gesendet: Mittwoch, 3. Januar 2007 00:57
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] sqlite performance, locking & threading

Nico,

I have implemented all three strategies (thead specific connections, single
connection multiple threads, and single thread server with multiple client
threads).

The problem with using thread specific contexts is that you cant have a
single global transaction which wraps all of those contexts.  So you end up
having to use fine grained transactions, which decreases performance.

The single connection multiple thread alternative apparently has problems
with sqlite3_step being active on more than one thread at the same moment,
so cannot easily be used in a safe way.  But it is by far the fastest and
simplest alternative.

The single thread server solution involves message passing between threads,
and even when this is done optimally with condition variables (or events on
windows) and blocking ive found that it results in a high number of context
switches and decreased performance.  It does however make a robust basis for
a wrapper api, since it guarantees that things will always be synchronised.
But using this arrangement can also result in various static initialisation
problems, since the single thread server must always be up and running
before anything which needs to use it.

Emerson

On 1/2/07, Nicolas Williams <[EMAIL PROTECTED]> wrote:
> On Sat, Dec 30, 2006 at 03:34:01PM +, Emerson Clarke wrote:
> > Technically sqlite is not thread safe.  [...]
>
> Solaris man pages describe APIs with requirements like SQLite's as 
> "MT-Safe with exceptions" and the exceptions are listed in the man page.
>
> That's still MT-Safe, but the caller has to play by certain rules.
>
> Anyways, this is silly.  SQLite API is MT-Safe with one exception and 
> that exception is rather ordinary, common to other APIs like it that 
> have a context object of some sort (e.g., the MIT krb5 API), and not 
> really a burden to the caller.  In exchange for this exception you get 
> an implementation of the API that is lighter weight and easier to 
> maintain than it would have been without that exception; a good 
> trade-off IMO.
>
> Coping with this exception is easy.  For example, if you have a server 
> app with multiple worker threads each of which needs a db context then 
> you could use a thread-specific key to track a per-thread db context; 
> use pthread_key_create(3C) to create the key, pthread_setspecific(3C) 
> once per-thread to associate a new db context with the calling thread, 
> and pthread_getspecific(3C) to get the calling thread's db context 
> when you need it.  If you have a protocol where you have to step a 
> statement over multiple message exchanges with a client, and you don't 
> want to have per-client threads then get a db context 
> per-client/exchange and store that and a mutext in an object that 
> represents that client/exchange.  And so on.
>
> Nico
> --
>
> --
> --- To unsubscribe, send email to 
> [EMAIL PROTECTED]
> --
> ---
>
>


-
To unsubscribe, send email to [EMAIL PROTECTED]

-



-
To unsubscribe, send email to [EMAIL PROTECTED]
-






AW: [sqlite] sqlite performance, locking & threading


2006-12-30

Thread
Michael Ruck

Richard,

I believe his problem is this:

"Each query is allowed to complete before the other one starts, but each
thread may have multiple statements or result sets open."

The open resultsets/multiple started statements are causing him headaches.

Mike

-Ursprüngliche Nachricht-
Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Gesendet: Samstag, 30. Dezember 2006 17:32
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] sqlite performance, locking & threading

"Emerson Clarke" <[EMAIL PROTECTED]> wrote:
> Richard,
> 
> Are you sure we are not just getting into semantic knots here ?
> 
> Do we have the same definition of "at the same time".  I mean 
> concurrently, so that both threads use the same sqlite3 * structure, 
> within mutexes. Each query is allowed to complete before the other one 
> starts, but each thread may have multiple statements or result sets 
> open.
> 
> When i try to do this, i get api called out of sequence errors...
> 

There are around 50 test cases for this kind of behavior in the regression
test files thread1.test and thread2.test.  They all seem to work for me.

Perhaps your mutexes are not working as you expect and you are in fact
trying to use the same database connection simultaneously in two or more
threads.  SQLite attempts to detect this situation and when it sees it it
return SQLITE_MISUSE which generates the "API called out of sequence" error.


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



-
To unsubscribe, send email to [EMAIL PROTECTED]

-



-
To unsubscribe, send email to [EMAIL PROTECTED]
-






AW: [sqlite] sqlite performance, locking & threading


2006-12-30

Thread
Michael Ruck

I want to contribute my 0.02€ to this discussion. Basically I believe your 
(Emerson) design is flawed. I've been working for years with multithreaded and 
even multi-core systems. From my experience a design using threads for specific 
tasks is *always* performing better, than having multiple threads execute the 
same things in parallel. This experience is based on the facts about lock 
contention, thread starvation, caching and more. These always apply unless you 
have a realtime operating system, which guarantees you that every thread is 
scheduled and the scheduling order. On most systems this ain't the case AFAIK. 
At least not on Windows/Linux, if there's heavy threading and lock contention 
going on.

I really would suggest you to test out a design, where threads are assigned 
single tasks, such as retrieving data from db, writing data to db and queues, 
which provide these threads with work items. If you're really smart, you'll 
create queues using atomic operations so that no thread will take locks for 
these operations. 

To summarize my points:

- Create one Sqlite writer thread, one sqlite reader thread.
- Create queues for all worker threads, which provide them with work items.
- Do *not* take kernel locks on data structures or libraries, this *will* 
really hurt your performance.
- Use transactions coarse grained on the Sqlite writer thread. Either decide 
inside the thread, when to commit and start a new one or design a specific 
workitem to trigger this from the outside. This depends on having a consistent 
state in your data structures/the database.

But: Your limiting factor will always be the hard disk. Analyze your tasks to 
determine, what the bounding factor is: Is it the CPU? Is it the disk? Is it 
the network? Only then start changing something. Multithreading only makes 
sense if you can parallelize heavily and are not bound by disk/network, but by 
CPU and have multiple (unused) cores available.

And: Having multiple statements executing concurrently is only possible with 
multiple Sqlite connections. A connection can only keep one resultset open or a 
statement executing (unless that has changed recently.) 

Mike

-Ursprüngliche Nachricht-
Von: Emerson Clarke [mailto:[EMAIL PROTECTED] 
Gesendet: Samstag, 30. Dezember 2006 17:08
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] sqlite performance, locking & threading

Richard,

Are you sure we are not just getting into semantic knots here ?

Do we have the same definition of "at the same time".  I mean concurrently, so 
that both threads use the same sqlite3 * structure, within mutexes. Each query 
is allowed to complete before the other one starts, but each thread may have 
multiple statements or result sets open.

When i try to do this, i get api called out of sequence errors...

On 12/30/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> "Emerson Clarke" <[EMAIL PROTECTED]> wrote:
> > But why then can i not have a single transaction wrapping a single 
> > connection which is used within multiple threads, obvioulsy not at 
> > the same time.
>
> You can.  What makes you think you can't?
> --
> D. Richard Hipp  <[EMAIL PROTECTED]>
>
>
> --
> --- To unsubscribe, send email to 
> [EMAIL PROTECTED]
> --
> ---
>
>

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



-
To unsubscribe, send email to [EMAIL PROTECTED]
-






AW: [sqlite] SQLite and McAfee Anti-Virus


2006-10-31

Thread
Michael Ruck

I would actually remove the default or use the process name instead.

Just my $0.02.
Mike 

-Ursprüngliche Nachricht-
Von: John Stanton [mailto:[EMAIL PROTECTED] 
Gesendet: Mittwoch, 1. November 2006 01:28
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] SQLite and McAfee Anti-Virus

Why not make it McAfee and direct the complaints to the culprits?

Joe Wilson wrote:
> Even better - name the temp file prefix msaccess_ and no one will dare 
> touch it or question it.
> 
> - Original Message 
> From: James Berry <[EMAIL PROTECTED]>
> To: sqlite-users@sqlite.org
> Sent: Tuesday, October 31, 2006 6:14:11 PM
> Subject: Re: [sqlite] SQLite and McAfee Anti-Virus
> 
> On Oct 31, 2006, at 2:24 PM, [EMAIL PROTECTED] wrote:
> 
>>  only people in
>>the know will figure out 'sqlite' is 'etilqs' backwards and, as you 
>>point out,
> 
> 
> You're assuming that Google won't find this thread, which is now 
> public record. Soon a search for (that new word) will get back to 
> sqlite anyway... ;)
> 
> -jdb
> 
> 
> 
> 
> 
> 
> --
> --- To unsubscribe, send email to 
> [EMAIL PROTECTED]
> --
> ---
> 



-
To unsubscribe, send email to [EMAIL PROTECTED]

-



-
To unsubscribe, send email to [EMAIL PROTECTED]
-






AW: [sqlite] auxiliary threads in sqlite3


2006-09-27

Thread
Michael Ruck

I can't answer the question regarding SQLite for you, but Windows does start
auxiliary threads in some APIs. 

-Ursprüngliche Nachricht-
Von: Dixon [mailto:[EMAIL PROTECTED] 
Gesendet: Mittwoch, 27. September 2006 16:36
An: sqlite-users@sqlite.org
Betreff: [sqlite] auxiliary threads in sqlite3

I'm running with sqlite 3.3.6 in a windows environment.  I have NOT defined
"THREADSAFE=1".  My app is single threaded, however, multiple instances of
the app use sqlite3 on the same DB's, simultaneously.  My question -- Does
SQLite ever start auxiliary threads?


-
To unsubscribe, send email to [EMAIL PROTECTED]

-



-
To unsubscribe, send email to [EMAIL PROTECTED]
-






AW: AW: [sqlite] Re: Queries fail - I can't figure out why


2006-09-25

Thread
michael . ruck

Nevermind the issue. I've found my problem: I bound my string using character 
length instead of byte length for UTF-16.

Problem solved.

Mike

>Hi,
>
>Yes I showed an example query. The query I used for sqlite3_prepare
>is the following:
>
>SELECT * FROM Objects WHERE ObjectID = ?
>
>If I'd put quotes around the question mark, binding would have failed.
>
>Interestingly I've even had some queries fail in the SQLite shell 
>yesterday. Others worked. Looking at the EXPLAIN output the primary
>key index is used as it should - but somehow fails to locate the row,
>even though the where conditions are right and should return one row.
>
>The SQLite shell is the Windows EXE you can download on sqlite.org. I 
>compiled sqlite3.lib myself using Visual Studio 2005.
>
>Where does SQLite compare the condition with the index? I'd try to
>set a breakpoint and look further. Looking at the EXPLAIN output I 
>haven't figured that out yet, but it was late yesterday.
>
>Additionally I'll try to make a simple example program to find this 
>bug.
>
>Mike
>
>-Ursprüngliche Nachricht-
>Von: Igor Tandetnik [mailto:[EMAIL PROTECTED] 
>Gesendet: Sonntag, 24. September 2006 22:45
>An: SQLite
>Betreff: [sqlite] Re: Queries fail - I can't figure out why
>
>Michael Ruck 
>wrote:
>> I have the tables of the following style:
>>
>> CREATE TABLE Objects (ObjectID TEXT PRIMARY KEY, Class TEXT)
>>
>> And I'm executing the following statement in the sqlite3 shell:
>>
>> SELECT * FROM Objects WHERE ObjectID = 
>> '{08021C17-46DD-4d83-A6FE-DDF0F7EC0AAE}'
>>
>> In the shell this query succeeds. However if I try to do the same 
>> thing via sqlite3_prepare, sqlite3_bind_text16 and sqlite3_step, then 
>> sqlite3_step always returns 101 (SQLITE_DONE.)
>
>The query you show does not have any parameters - what are you using
>sqlite3_bind_text16 for? You are probably using a different query in your
>program - show it.
>
>As a wild guess, does your query look anything like this:
>
>SELECT * FROM Objects WHERE ObjectID = '?'
>
>(with question mark in quotes)? Note that '?' is a string literal consisting
>of one question mark character, not a parameter placeholder. 
>The correct parameterized query is
>
>SELECT * FROM Objects WHERE ObjectID = ?
>
>Igor Tandetnik 
>
>
>
>-
>To unsubscribe, send email to [EMAIL PROTECTED]
>
>-
>
>
>
>-
>To unsubscribe, send email to [EMAIL PROTECTED]
>-
>

-
To unsubscribe, send email to [EMAIL PROTECTED]
-






AW: RE: RE: [sqlite] How long can I keep a prepared statement around?


2006-09-25

Thread
michael . ruck

That's basically the way I do it. I have a factory method for all
prepared statements, where I wrap the (optional) recompilation in and aquire a 
named mutex for every method invocation in the class.

I think this should be a safe approach to prevent SQLITE_SCHEMA errors if the 
only schema changes occur on the same sqlite3 connection. (E.g. no other 
process, thread or connection changes the schema.)

Mike

>> Looking at the implementation of sqlite3_expired, its just a 
>> comparison of the expired flag in the statement or if the 
>> passed statement is NULL. The cost is an additional if 
>> statement before execution, however this does not save you 
>> from SQLITE_SCHEMA errors. In theory someone could modify the 
>> schema just after you called sqlite3_expired, but before you 
>> step the statement.
>
>I've acquired a mutex lock at this point so no danger of another thread
>clobbering the schema before the step.
>
>I'm also testing for NULL statements anyway (first pass). I figured 
>an ease way to avoid schema errors would be to change:
>
>if (db._insertStatement == NULL)
>{
>   // preparation
>
>to:
>
>if (sqlite3_expired(db._insertStatement))
>{
>   // preparation
>
>> 
>> The question should more likely be: When is the expired flag set?
>> 
>
>I agree.
>
>Daniel.
>
>==
>
>Please access the attached hyperlink for an important electronic 
>communications disclaimer: 
>
>http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
>==
>
>
>
>-
>To unsubscribe, send email to [EMAIL PROTECTED]
>-
>

-
To unsubscribe, send email to [EMAIL PROTECTED]
-






AW: RE: [sqlite] How long can I keep a prepared statement around?


2006-09-25

Thread
michael . ruck

Looking at the implementation of sqlite3_expired, its just a comparison of the 
expired flag in the statement or if the passed statement is NULL. The cost is 
an additional if statement before execution, however this does not save you 
from SQLITE_SCHEMA errors. In theory someone could modify the schema just after 
you called sqlite3_expired, but before you step the statement.

The question should more likely be: When is the expired flag set?

Mike

> 
>> 
>> Use the function sqlite3_expired to determine, when you need 
>> to recompile a prepared statement. That's the approach I use.
>> 
>> Mike
>
>How does this compare with the re-preparing statements that 
>have failed with a SQLITE_SCHEMA error during sqlite3_step?
>
>e.g. http://www.sqlite.org/faq.html#q17
>
>Using sqlite3_expired certainly seems simpler to me. I'm
>guessing it would be less efficient. Would Dr. Hipp (or
>anyone else) care to comment on the relative merits of
>these two methods?
>
>Daniel.
>
>
>
>==
>
>Please access the attached hyperlink for an important electronic 
>communications disclaimer: 
>
>http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
>==
>
>
>
>-
>To unsubscribe, send email to [EMAIL PROTECTED]
>-
>

-
To unsubscribe, send email to [EMAIL PROTECTED]
-






AW: [sqlite] Re: Queries fail - I can't figure out why


2006-09-25

Thread
Michael Ruck

Hi,

Yes I showed an example query. The query I used for sqlite3_prepare
is the following:

SELECT * FROM Objects WHERE ObjectID = ?

If I'd put quotes around the question mark, binding would have failed.

Interestingly I've even had some queries fail in the SQLite shell 
yesterday. Others worked. Looking at the EXPLAIN output the primary
key index is used as it should - but somehow fails to locate the row,
even though the where conditions are right and should return one row.

The SQLite shell is the Windows EXE you can download on sqlite.org. I 
compiled sqlite3.lib myself using Visual Studio 2005.

Where does SQLite compare the condition with the index? I'd try to
set a breakpoint and look further. Looking at the EXPLAIN output I 
haven't figured that out yet, but it was late yesterday.

Additionally I'll try to make a simple example program to find this 
bug.

Mike

-Ursprüngliche Nachricht-
Von: Igor Tandetnik [mailto:[EMAIL PROTECTED] 
Gesendet: Sonntag, 24. September 2006 22:45
An: SQLite
Betreff: [sqlite] Re: Queries fail - I can't figure out why

Michael Ruck 
wrote:
> I have the tables of the following style:
>
> CREATE TABLE Objects (ObjectID TEXT PRIMARY KEY, Class TEXT)
>
> And I'm executing the following statement in the sqlite3 shell:
>
> SELECT * FROM Objects WHERE ObjectID = 
> '{08021C17-46DD-4d83-A6FE-DDF0F7EC0AAE}'
>
> In the shell this query succeeds. However if I try to do the same 
> thing via sqlite3_prepare, sqlite3_bind_text16 and sqlite3_step, then 
> sqlite3_step always returns 101 (SQLITE_DONE.)

The query you show does not have any parameters - what are you using
sqlite3_bind_text16 for? You are probably using a different query in your
program - show it.

As a wild guess, does your query look anything like this:

SELECT * FROM Objects WHERE ObjectID = '?'

(with question mark in quotes)? Note that '?' is a string literal consisting
of one question mark character, not a parameter placeholder. 
The correct parameterized query is

SELECT * FROM Objects WHERE ObjectID = ?

Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]

-



-
To unsubscribe, send email to [EMAIL PROTECTED]
-






[sqlite] Queries fail - I can't figure out why


2006-09-24

Thread
Michael Ruck

Hi,

I have the tables of the following style:

CREATE TABLE Objects (ObjectID TEXT PRIMARY KEY, Class TEXT)

And I'm executing the following statement in the sqlite3 shell:

SELECT * FROM Objects WHERE ObjectID =
'{08021C17-46DD-4d83-A6FE-DDF0F7EC0AAE}'

In the shell this query succeeds. However if I try to do the same thing via
sqlite3_prepare, sqlite3_bind_text16 and sqlite3_step, then sqlite3_step
always returns 101 (SQLITE_DONE.) I've opened the database using
sqlite3_open16. No schema changes, no other connections are open at this
point. However the query always fails, even though the same query succeeds
in the shell. I've even tried to insert a row and immediately query for the
same row, but even that fails.

I have this issue with several tables. With several different code places -
all have the same style and probably the same bug.

Can anyone give me a hint at what's wrong?

Thanks,
Mike


-
To unsubscribe, send email to [EMAIL PROTECTED]
-






AW: [sqlite] what tier architecture?


2006-08-07

Thread
Michael Ruck

>From your description I would consider this a one-tier architecture.
Ussually the tiers are defined as follows:

- Presentation

GUI, all user interaction.

- Business Logic

Logic, which can not be expressed by constraints in the database.
Interaction with other (software) systems and some more logic.

- Data (access) layer

This one is hard IMHO. In most cases this resembles a mapper, which maps
from relational tables to objects used in the business logic. Additionally
you have a set of predefined functions, which perform queries upon the data
sources and return the results as objects. In most cases these are only
access layers, which use a database server and stored procedures.

HTH,
Mike

-Ursprüngliche Nachricht-
Von: John Newby [mailto:[EMAIL PROTECTED] 
Gesendet: Montag, 7. August 2006 16:43
An: sqlite-users@sqlite.org
Betreff: [sqlite] what tier architecture?

Hi, this is probably a stupid question and it has nothing to do with SQlite
per se so I apologise in advance for hijacking the emails withmy question
plus I don't even know if this is the right place to ask the question.

I have created a GUI to SQLite using VB.Net for my University project and it
interfaces with the SQLite .dll file.

I have to now write about the architecture of my product but I am unsure as
what it would be, after reading this website I am even more confused :-

http://www.javaworld.com/javaworld/jw-01-2000/jw-01-ssj-tiers.html

Could it be one tier as both the DBMS and GUI are as one

or could it be two tier as the GUI could be the client and the DBMS the
server

or could it be three tier as the GUI could be the client and the DBMS the
server and database?

I'm really confused, so any help or information on this subject would be
really appreciated.

Many thanks

John






AW: [sqlite] Major projects using SQLite


2006-07-14

Thread
Michael Ruck

You can add Orb (www.orb.com) to the list.

Mike

-Ursprüngliche Nachricht-
Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Gesendet: Freitag, 14. Juli 2006 17:04
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] Major projects using SQLite

=?iso-8859-1?Q?Jon_Garc=EDa_de_Salazar_Bilbao?= <[EMAIL PROTECTED]> wrote:
> 
> Could you give examples of some major software projects using SQLite?

* Apple OS-X
* Solaris 10
* Firefox
* Monotone
* SymbianOS (used in cellphones)
* YUM
* America Online (AOL)
* PHP
* Philips MP3 players
* POPFile
* Amarok
* Trac
* McAfee Anti-Virus
* SpamSieve
* DSPAM
* Songbird

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






AW: [sqlite] How do you find out the names of the fields within a table?


2006-07-13

Thread
Michael Ruck

If you're using VB.NET 2002, then look at the following classes:

System.Collections.ArrayList
System.Collections.SortedList
System.Collections.Hashtable

These are all dynamic container classes.

Mike 

-Ursprüngliche Nachricht-
Von: John Newby [mailto:[EMAIL PROTECTED] 
Gesendet: Donnerstag, 13. Juli 2006 18:21
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] How do you find out the names of the fields within a
table?

Hi Fred, yes I am using vb.net.  I have searched online for vb.net dynamic
arrays, and one site I came accross states the new features of the
2.0framework allows for dynamic arrays but sadly I am using the
1.1 version.
http://www.ondotnet.com/pub/a/dotnet/2004/05/25/whidbey_vbnet_pt2.html
I am going to try and give it a go though.

Many thanks

John



On 13/07/06, Fred Williams <[EMAIL PROTECTED]> wrote:
>
> Did not realize he was using VB when I sent my last message.  I'd bet 
> VB still can't do anything dynamic.  That's only one of the reasons I 
> walked away from VB's "Daddy" (Quick Basic) years ago.
>
> > -Original Message-
> > From: Martin Jenkins [mailto:[EMAIL PROTECTED]
> > Sent: Thursday, July 13, 2006 8:08 AM
> > To: sqlite-users@sqlite.org
> > Subject: Re: [sqlite] How do you find out the names of the fields 
> > within a table?
> >
> >
> > John Newby wrote:
> > > Yeah I can get the names, but I need to put them in an
> > array, and to put
> > > them in an array I need to know the size of the array to
> > store them in,
> > > so I
> > > need to get a count first, then store this number as the
> > size of the array
> > > before I store the values into the array.
> >
> > Are you sure there no dynamic container objects in VB that support 
> > an "append" method? Lists?
> >
> > If not (and I find that hard to believe) you could hack around it by 
> > appending the names to a string, then parsing the string and then 
> > dimensioning your array, or you could build a linked list but ... 
> > surely VB has more intelligent containers than statically sized 
> > arrays?
> >
> > Martin
>
>






AW: [sqlite] How do you find out the names of the fields within a table?


2006-07-13

Thread
Michael Ruck

If you're using VB6 your choices are VBs native Collection or the
Scripting.Dictionary class mentioned by Craig. Look them up in VBs online
help, there are examples on using them. I'd suggest get more familiar with
VB and its Container classes...

Mike 

-Ursprüngliche Nachricht-
Von: John Newby [mailto:[EMAIL PROTECTED] 
Gesendet: Donnerstag, 13. Juli 2006 18:10
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] How do you find out the names of the fields within a
table?

Hi Craig, thanks for your comments, but I can't seem to get that to work, it
says that add and count aren't part of the dictionaryBase or dictionaryEntry
(those being the only 2 dictionary things i could find in vb)

I think I'm nearly there with the array.

Thanks again for your help

John

On 13/07/06, Craig Morrison <[EMAIL PROTECTED]> wrote:
>
> John Newby wrote:
> > Hi Craig, I have looked into the dictionary that you suggested, but 
> > wouldn't this be the same as the array as I would still need to know 
> > how many
> times
> > to go through the while loop to add the names of the fields?  Also I 
> > need to get the names of the fields back out to populate a comboBox, 
> > which I already know how to do with an array.
>
> item = myDict("hash")
>
> Its pretty simple.
>
> Use your favorite search engine to look for "scripting.dictionary" and 
> you will find a wealth of information on VBScript's dictionary object.
>
> From an earlier message of yours:
>
> >' select all field names from the selected table
> >sqlite_cmd.CommandText = (strSQL)
> >' Now the SQLiteCommand object can give us a DataReader-Object:
> >sqlite_datareader = sqlite_cmd.ExecuteReader()
> >i = 0
> >While sqlite_datareader.Read()
> >'Try
> >fieldNames(i) = sqlite_datareader("name")
> >'atch es As Exception
> >'  MessageBox.Show(es.Message)
> >End Try
> >i += 1
> >End While
> >
> >sqlite_datareader.Close()
>
> You are already getting the field names on the fly, why do you insist on
>   knowing how many fields there are?
>
> After you populate the dictionary, you can get the count of items
> (fields) by doing:
>
> itemCount = myDict.Count
>
> To attempt to stave off any more questions:
>
> set fieldNames = CreateObject("Scripting.Dictionary")
> sqlite_cmd.CommandText = (strSQL)
> sqlite_datareader = sqlite_cmd.ExecuteReader() i = 0 While 
> sqlite_datareader.Read()
>  fieldNames.Add i, sqlite_datareader("name")
>  i += 1
> End While
> sqlite_datareader.Close()
> fieldCount = fieldNames.Count
>
> Your problem is *not* sqlite, but rather a command of VB itself. You 
> might want to spend some time getting up to speed on VB.
>
> Or better yet, why don't you just pass a reference to the list box and 
> add them directly to it?
>
> I'm not going to comment further on this topic as it is now QUITE far 
> off-topic.
>
> --
> Craig Morrison
> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
> http://pse.2cah.com
>Controlling pseudoephedrine purchases.
>
> http://www.mtsprofessional.com/
>A Win32 email server that works for You.
>






AW: [sqlite] How do you find out the names of the fields within a table?


2006-07-13

Thread
Michael Ruck

VB always had dynamic containers. Starting with Arrays things such as ReDim
helped. Later Collection(s) (actually a Dictionary/Hashtable) were
introduced. In VB.NET you of course have all containers, which the .NET
framework supplies. In fact there are classes for Lists (ArrayList,
LinkedList and template/generic versions...)

Mike

-Ursprüngliche Nachricht-
Von: Fred Williams [mailto:[EMAIL PROTECTED] 
Gesendet: Donnerstag, 13. Juli 2006 18:03
An: sqlite-users@sqlite.org
Betreff: RE: [sqlite] How do you find out the names of the fields within a
table?

Did not realize he was using VB when I sent my last message.  I'd bet VB
still can't do anything dynamic.  That's only one of the reasons I walked
away from VB's "Daddy" (Quick Basic) years ago.

> -Original Message-
> From: Martin Jenkins [mailto:[EMAIL PROTECTED]
> Sent: Thursday, July 13, 2006 8:08 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] How do you find out the names of the fields 
> within a table?
>
>
> John Newby wrote:
> > Yeah I can get the names, but I need to put them in an
> array, and to put
> > them in an array I need to know the size of the array to
> store them in,
> > so I
> > need to get a count first, then store this number as the
> size of the array
> > before I store the values into the array.
>
> Are you sure there no dynamic container objects in VB that support an 
> "append" method? Lists?
>
> If not (and I find that hard to believe) you could hack around it by 
> appending the names to a string, then parsing the string and then 
> dimensioning your array, or you could build a linked list but ... 
> surely VB has more intelligent containers than statically sized 
> arrays?
>
> Martin






AW: [sqlite] Problems with Multi-Threaded Application.


2006-07-12

Thread
Michael Ruck

Use a queue for the database operations in this case. You won't suffer from
lock or busy errors, if all access is serialized. Queues can scale very well
if done right.

Mike

-Ursprüngliche Nachricht-
Von: Gussimulator [mailto:[EMAIL PROTECTED] 
Gesendet: Mittwoch, 12. Juli 2006 20:55
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] Problems with Multi-Threaded Application.

I cannot delay my thread.

Also, if I made a thread per event, then that wouldnt work either, since
every thread would open the database to try a write on it.

We are talking about 100 events in a second. This thread handles the queries
as it gets the events.





- Original Message - 
From: "Jay Sprenkle" <[EMAIL PROTECTED]>
To: 
Sent: Wednesday, July 12, 2006 2:57 PM
Subject: Re: [sqlite] Problems with Multi-Threaded Application.


> On 7/12/06, Gussimulator <[EMAIL PROTECTED]> wrote:
>>
>> About the "cant read while writing", how to avoid this?, I cant stop my
>> system while using the database. There would be no point on having a
>> database then.
>
> check the return code from operation, if it says Busy, or Locked,
> the redo the operation. You may need to retry several times
>
> // here's an example with a 1/2 second delay:
>sqlite3_busy_timeout(db, 500);
>bool locked = true;
>for ( int i = 0; ( i < 10 ) && ( locked ); i++ )
>  switch ( sqlite3_step() )
>{
>  case SQLITE_BUSY:
>  case SQLITE_LOCKED:
>break;
>  case SQLITE_ROW:
>  case SQLITE_DONE:
>locked = false;
>break;
>  default:
>throw Exception( "Cannot execute sql" );
>break;
>} 






AW: [sqlite] Problems with Multi-Threaded Application.


2006-07-12

Thread
Michael Ruck

I would suggest using a queue approach. Its not as bad as it sounds. Have
that queue processed by a single thread, which controls the database writes.
That way you can save the time for other operations, such as database
open/close etc.

You can't avoid the "can't read while writing" though. You'll have to figure
out something for this. I'd use a named mutex (or similar IPC) for this. Or:
Serialize all database access via a queue and a single threaded queue
processor... The results are most likely better using a (properly written)
queue, rather than IPC.

Mike

-Ursprüngliche Nachricht-
Von: Gussimulator [mailto:[EMAIL PROTECTED] 
Gesendet: Mittwoch, 12. Juli 2006 19:47
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] Problems with Multi-Threaded Application.

Yes, in theory.

However, when about 100 queries are fired in a second, only a few get the
data populated..

I can not tell when to start or end the transactions, because events are
fired from a third-party system. So I used a timer, set to 5 seconds.. then
tried with 10 seconds, got the same result anyway (timer to a procedure that
would commit and begin transaction).

>From 100 events about 30 were "processed".

About the "cant read while writing", how to avoid this?, I cant stop my
system while using the database. There would be no point on having a
database then.






- Original Message -
From: "Jay Sprenkle" <[EMAIL PROTECTED]>
To: 
Sent: Wednesday, July 12, 2006 12:59 PM
Subject: Re: [sqlite] Problems with Multi-Threaded Application.


> On 7/12/06, Gussimulator <[EMAIL PROTECTED]> wrote:
>> Hello,
>>
>> I'm currently using SQLite3 on my multi-threaded software.
>>
>> I have tried several ways for dealing with my issue, however, I came to 
>> the conclusion that there must be some trick I havent been told of.
>
> It wasn't clear to me when I started that you can have as many readers as 
> you
> want, but only one process may write to the database AND no other process
> may read it while you are writing. If you use transactions and retry
> when the database is locked it works fine and no data will be lost.
>
>
> --
> SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
> http://www.reddawn.net/~jsprenkl/Sqlite
>
> Cthulhu Bucks!
> http://www.cthulhubucks.com 






AW: Re: [sqlite] Purging the mailing list roles.   Was: Please Restore Your Account Access


2006-05-29

Thread
michael . ruck

I second this. I don't mind having to post or refresh my subscription every 
once in a while. It would however be important for me to receive a notification 
to refresh my subscription instead of just silently removing my subscription.

HTH,
Mike

>Hi,
>
>I think many of the 1217 active subscribers are people like me who tune in to 
>
>the list but only contribute once in a blue moon.
>
>I do not have any objection to a "send email to keep your subscription active" 
>
>idea, but I have never seen that used in the other mailing lists that I 
>subscribe to.
>
>Regards,
>Eugene Wee
>
>[EMAIL PROTECTED] wrote:
>> "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote:
>>> dilettantes remain rude.Where we can almost borrow money from our 
>>> earring.Hugo, the friend of Hugo and earns frequent flier miles 
>>> with power drill near.
>> 
>> In order to be able to send messages to this mailing list,
>> the spammer above had to subscribe.  To subscribe means that
>> he had to respond to an email that was sent to the subscription
>> address.  Since his email address does not exist, I'm wondering
>> how he managed to pull this off.  Any ideas?
>> 
>> I have unsubscribed every account from "paypal.com" and "ebay.com".
>> All such accounts were of the form "[EMAIL PROTECTED]" or
>> "[EMAIL PROTECTED]", etc.  There were 7 such accounts.
>> 
>> After purging the accounts above, we are still left with 1217
>> active subscribers.  This seems like a lot to me.  I'm wondering
>> if some fraction of these might be inactive accounts, or accounts
>> belonging to people who have spam filters turned on to delete
>> incoming email from sqlite.org.  Does anybody have any ideas on
>> how we might remove people from the mailing list that do not
>> actually read messages from the mailing list?  When email bounces,
>> the user is removed automatically.  But email addresses that silently
>> absorb messages and never deliver them to a real human can linger
>> on the mailing list indefinitely.  
>> 
>> I wonder if I need to implement some kind of mechanism that requires
>> you to either send a message to the mailing list or else renew your
>> subscription every 3 months.  Does anybody have any experience with
>> other mailing lists that require such measures?
>> 
>> --
>> D. Richard Hipp   <[EMAIL PROTECTED]>
>> 
>





AW: Re: [sqlite] Direct use of SQLite btree functions / performance


2006-04-19

Thread
michael . ruck

Maybe I'm missing something here, but there should be an interface to page a 
blob into memory. Especially for large blobs, this would make sense to me to 
not load them into memory at once.

If sqlite doesn't provide this already, it would probably make a lot of sense 
to include this functionality or to propagate it to the SQL layer.

Mike

>--- [EMAIL PROTECTED] wrote:
>> Felix Schwarz <[EMAIL PROTECTED]> wrote:
>> > Hi,
>> > 
>> > I have just discovered these old message on the SQLite Yahoo! Group:
>> > 
>> > http://groups.yahoo.com/group/sqlite/message/2115
>> > http://groups.yahoo.com/group/sqlite/message/2117
>> > 
>> > This does sound very interesting! If I simply want to store binary  
>> > data that I want to access via an index number again (and do this  
>> > real quick), how big is the difference between using SQLite or its  
>> > btree functions directly?
>> > 
>> 
>> I'm guessing that you will not see a significant performance
>> improvement over using prepared statements in the SQL interface
>> and a table like this:
>> 
>>CREATE TABLE storage(
>>  id INTEGER PRIMARY KEY,
>>  data BLOB
>>);
>
>If you read the blob all in at once, true.
>But doesn't sqlite3BtreeData() allows you to read a partial chunk of 
>data from the blob at an arbitrary offset? This could be advantageous
>if you are dealing with multi-megabyte blobs and do not require/want
>the entire blob in memory at once.
>
>
>__
>Do You Yahoo!?
>Tired of spam?  Yahoo! Mail has the best spam protection around 
>http://mail.yahoo.com





AW: [sqlite] VB6 question


2005-07-27

Thread
michael . ruck

Hi,

unfortunately this has to do with the C calling convention used by sqlite3.dll. 
By default DLLs compiled with C have the cdecl calling convention, but VB only 
supports the stdcall calling convention.

You must recompile sqlite using MS Visual C++ or other compiler and switch the 
default calling convention from cdecl to stdcall in the compiler/linker 
settings.

HTH
Michael

>
>hello i'm trying to open a database with VB6 without wrapper
>
>i'm using sqlite3.dll
>
>and it's my code :
>
>Option Explicit
>Private Declare Function sqlite3_open Lib "sqlite3.dll" (ByVal filename 
>As String, ByRef dbHandle As Long) As Long
>Private Declare Function sqlite3_open16 Lib "sqlite3.dll" (ByVal 
>filename As String, ByRef dbHandle As Long) As Long
>Private Declare Sub sqlite3_close Lib "sqlite3.dll" (ByVal DB_Handle As 
>Long)
>
>Private Sub Form_Load()
>Dim lRet As Long
>Dim lDbHandle As Long
>Dim sFilename As String
>   
>sFilename = "c:\toto.db"  
>sqlite3_open sFilename, lDbHandle
>MsgBox ("lRet=" & lRet)
>MsgBox ("ldbhandle=" & lDbHandle)
>sqlite3_close (lDbHandle)
>End Sub
>
>when i launch it, i've an error 49 : Bad DLL calling convention
>
>anyone can help me ? where is my fault ?
>
>thx
>Gregory Letellier
>
>





[sqlite] Storing XML


2005-02-28

Thread
Michael Ruck

Has anyone used SQLite to store XML fragments or documents? Which approach
have you taken or what approach would you take? I am currently evaluating
the possibilities of storing arbitrary XML fragments using SQLite. The
fragments may or may not have schema information.








AW: [sqlite] Plans for SQLite version 3.2


2005-02-18

Thread
Michael Ruck

How about adding support to obtain the column type information, when
querying views? (I'm refering to the question I posted a couple of days
ago.)


 

-Ursprüngliche Nachricht-
Von: D. Richard Hipp [mailto:[EMAIL PROTECTED] 
Gesendet: Freitag, 18. Februar 2005 16:54
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] Plans for SQLite version 3.2

On Fri, 2005-02-18 at 16:45 +0100, Sijmen Mulder wrote:
> > Any words on what kind of new feature is going to be in 3.2?
> 
> I guess he ment the CREATE TABLE ADD COLUMN statement, which probably 
> adds a column to a table.

I *meant* to say "ALTER TABLE ADD COLUMN".


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






[sqlite] Questions regarding sqlite3_column_decltype


2005-02-13

Thread
Michael Ruck

Hi,

Maybe someone can answer me a couple of questions regarding above function:

I've seen that it doesn't return the declaration type for views in 3.0.8. 
Is this by design?

Are there any plans to change this so that queries against views return 
the column types of the tables? I've looked at the opcodes executed and 
the two are quite different, I can't (currently) figure out how to change 
this myself. (I'd be glad to post this as a patch for inclusion.)

As a workaround I'm currently executing the SELECT, which is performed by 
the view manually to gain the column type information. I'm wondering if 
there is any speed difference (any at all?) in doing so. I don't need exact 
timings just a rough understating of whats different would help me. (The
view 
joins together approx. 5 tables, pulling a total of 20 columns.)

Any help is appreciated,

Michael









AW: [sqlite] How to unite query results from two databases


2005-02-09

Thread
Michael Ruck

I didn't even think of the simplest solution...

Thanks,
Michael 

-Ursprüngliche Nachricht-
Von: Bob Dankert [mailto:[EMAIL PROTECTED] 
Gesendet: Mittwoch, 9. Februar 2005 19:53
An: sqlite-users@sqlite.org
Betreff: RE: [sqlite] How to unite query results from two databases

I havent worked with multiple databases before, but I would think you could
just union two queries together if nothing else.

Eg: select ... union select ... order by col

Bob

Envision Information Technologies
Associate
[EMAIL PROTECTED]
v. 608.256.5680
f. 608.279.3780
 
-Original Message-
From: Michael Ruck [mailto:[EMAIL PROTECTED]
Sent: Wednesday, February 09, 2005 12:49 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] How to unite query results from two databases

I need to perform a select, which queries two databases (same tables and
columns in both databases; both open at the same time in the same SQLite
session via ATTACH DATABASE.) I need the query result to appear as a single
result with sorting etc. performed on the entire result from both databases.
Does someone have an idea of how to perform this in SQLite?

Thanks,
Michael






[sqlite] How to unite query results from two databases


2005-02-09

Thread
Michael Ruck

I need to perform a select, which queries two databases (same tables and
columns in both databases; both open at the same time in the same SQLite
session via ATTACH DATABASE.) I need the query result to appear as a single
result with sorting etc. performed on the entire result from both databases.
Does someone have an idea of how to perform this in SQLite?

Thanks,
Michael






AW: [sqlite] Version 3.1.0


2005-01-21

Thread
Michael Ruck

You could replace the const void* with wchar_t on conforming compilers (such
as MCVC 6+) to simplify unicode development:

#if defined(wchar_t)
typedef const wchar_t* strw;
#else // #if defined(wchar_t)
typedef const void* strw;
#endif // #if defined(wchar_t)

I think this would allow easier usage of all sqlite...16() functions.

- Michael

-Ursprüngliche Nachricht-
Von: D. Richard Hipp [mailto:[EMAIL PROTECTED] 
Gesendet: Freitag, 21. Januar 2005 19:34
An: sqlite-users@sqlite.org
Betreff: [sqlite] Version 3.1.0

Version 3.1.0 (alpha) of SQLite is now available on the website.
Release notes are available from a link on the homepage.

This released is labeled "alpha" but it is still very well tested.  By being
"alpha" it means that there is still a small window of opportunity during
when users can suggest API changes.  Once we go to beta (in about a week) no
more changes will be accepted.  So if you want to suggest changes, please do
so quickly.

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