Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b
Use the one included in this archive: http://www.sqlite.org/sqlitedll-3_6_6_2.zip Mike > -Ursprüngliche Nachricht- > Von: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Im Auftrag von Ti Ny > Gesendet: Freitag, 28. November 2008 11:26 > An: sqlite-users@sqlite.org > Betreff: Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b > > > I don't have DEF file :( > > Ti Ny > > > From: [EMAIL PROTECTED] > > To: sqlite-users@sqlite.org > > Date: Fri, 28 Nov 2008 11:17:54 +0100 > > Subject: Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b > > > > Have you given the DEF file to the linker? Are the exports > visible in > > dependency walker? Is your DLL and the executable process type > > compatible (e.g. both 32 or 64-bits?) > > > > Mike > > > > > -Ursprüngliche Nachricht- > > > Von: [EMAIL PROTECTED] > > > [mailto:[EMAIL PROTECTED] Im Auftrag von Ti Ny > > > Gesendet: Freitag, 28. November 2008 10:57 > > > An: sqlite-users@sqlite.org > > > Betreff: Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b > > > > > > > > > >Are you sure that your sqlite.dll exports its API? > > > I downloaded sources available on the sqlite.org, is > there anything > > > special that is required to do to export API? > > > > > > > Like I said in my previous mail, I'd suggest using one of > > > the already > > > > available and well-tested .NET wrappers. > > > Unfortunately that's not possible for objective reasons. > > > > > > Ti Ny > > > > > > > From: [EMAIL PROTECTED] > > > > To: sqlite-users@sqlite.org > > > > Date: Fri, 28 Nov 2008 10:54:10 +0100 > > > > Subject: Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b > > > > > > > > Have you set ExactSpelling? EntryPointNotFound doesn't have > > > to do with > > > > CallingConvention. Are you sure that your sqlite.dll > > > exports its API? > > > > > > > > Like I said in my previous mail, I'd suggest using one of > > > the already > > > > available and well-tested .NET wrappers. > > > > > > > > Mike > > > > > > > > > -Ursprüngliche Nachricht- > > > > > Von: [EMAIL PROTECTED] > > > > > [mailto:[EMAIL PROTECTED] Im Auftrag von Ti Ny > > > > > Gesendet: Freitag, 28. November 2008 09:54 > > > > > An: sqlite-users@sqlite.org > > > > > Betreff: Re: [sqlite] sqlite3_open16 fails on Windows > Vista 64b > > > > > > > > > > > > > > > It doesn't work even if I set CallingConvention. It fails on > > > > > EntryPointNotFound exception. > > > > > > > > > > Ti Ny > > > > > > > > > > > From: [EMAIL PROTECTED] > > > > > > To: sqlite-users@sqlite.org > > > > > > Date: Thu, 27 Nov 2008 19:57:30 +0100 > > > > > > Subject: Re: [sqlite] sqlite3_open16 fails on Windows Vista > > > > > > 64b > > > > > > > > > > > > The given code is correct. The lower-case string is a C# > > > > > alias for the > > > > > > System.String class. System.Int64 is an opaque 64-bit > > > pointer value. > > > > > > > > > > > > The code so far is correct. What is missing though is > > > the calling > > > > > > convention, which by default is cdecl, but .NET doesn't use > > > > > that one > > > > > > by default for P/Invoke. It uses stdcall/winapi as the > > > > > default calling > > > > > > convention. Change the CallingConvention in the > DllImport line. > > > > > > > > > > > > Additionally using CharSet=CharSet.Unicode adds an > > > implicit W as > > > > > > per > > > > > > Win32 calling conventions to the function name. Since the > > > > > > sqlite3_open16 doesn't have that you need to use > > > ExactSpelling to > > > > > > prevent it from adding that W. > > > > > > > > > > > > Instead of rolling your own P/Invoke wrapper I'd suggest > > > > > using one of > > > > > > the available .NET wrappers for SQLite. > > > > > > > > > > > > Mike > > > > > > > > > > > > > -Ursprüngliche Nachricht- > > > > > > > Von: [EMAIL PROTECTED] > > > > > > > [mailto:[EMAIL PROTECTED] Im Auftrag von > > > > > Sherief N. > > > > > > > Farouk > > > > > > > Gesendet: Donnerstag, 27. November 2008 18:26 > > > > > > > An: 'General Discussion of SQLite Database' > > > > > > > Betreff: Re: [sqlite] sqlite3_open16 fails on Windows > > > Vista 64b > > > > > > > > > > > > > > > I am calling now this: > > > > > > > > > > > > > > > > [DllImport("sqlite3.dll", CharSet = > > > CharSet.Unicode)] > > > > > > > > internal static extern System.Int64 > > > > > sqlite3_open16(string > > > > > > > > filename, out IntPtr handle); > > > > > > > > > > > > > > > > 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
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. > > > > > > > > > > > > > > > > But
Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b
Have you given the DEF file to the linker? Are the exports visible in dependency walker? Is your DLL and the executable process type compatible (e.g. both 32 or 64-bits?) Mike > -Ursprüngliche Nachricht- > Von: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Im Auftrag von Ti Ny > Gesendet: Freitag, 28. November 2008 10:57 > An: sqlite-users@sqlite.org > Betreff: Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b > > > >Are you sure that your sqlite.dll exports its API? > I downloaded sources available on the sqlite.org, is there > anything special that is required to do to export API? > > > Like I said in my previous mail, I'd suggest using one of > the already > > available and well-tested .NET wrappers. > Unfortunately that's not possible for objective reasons. > > Ti Ny > > > From: [EMAIL PROTECTED] > > To: sqlite-users@sqlite.org > > Date: Fri, 28 Nov 2008 10:54:10 +0100 > > Subject: Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b > > > > Have you set ExactSpelling? EntryPointNotFound doesn't have > to do with > > CallingConvention. Are you sure that your sqlite.dll > exports its API? > > > > Like I said in my previous mail, I'd suggest using one of > the already > > available and well-tested .NET wrappers. > > > > Mike > > > > > -Ursprüngliche Nachricht- > > > Von: [EMAIL PROTECTED] > > > [mailto:[EMAIL PROTECTED] Im Auftrag von Ti Ny > > > Gesendet: Freitag, 28. November 2008 09:54 > > > An: sqlite-users@sqlite.org > > > Betreff: Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b > > > > > > > > > It doesn't work even if I set CallingConvention. It fails on > > > EntryPointNotFound exception. > > > > > > Ti Ny > > > > > > > From: [EMAIL PROTECTED] > > > > To: sqlite-users@sqlite.org > > > > Date: Thu, 27 Nov 2008 19:57:30 +0100 > > > > Subject: Re: [sqlite] sqlite3_open16 fails on Windows Vista 64b > > > > > > > > The given code is correct. The lower-case string is a C# > > > alias for the > > > > System.String class. System.Int64 is an opaque 64-bit > pointer value. > > > > > > > > The code so far is correct. What is missing though is > the calling > > > > convention, which by default is cdecl, but .NET doesn't use > > > that one > > > > by default for P/Invoke. It uses stdcall/winapi as the > > > default calling > > > > convention. Change the CallingConvention in the DllImport line. > > > > > > > > Additionally using CharSet=CharSet.Unicode adds an > implicit W as > > > > per > > > > Win32 calling conventions to the function name. Since the > > > > sqlite3_open16 doesn't have that you need to use > ExactSpelling to > > > > prevent it from adding that W. > > > > > > > > Instead of rolling your own P/Invoke wrapper I'd suggest > > > using one of > > > > the available .NET wrappers for SQLite. > > > > > > > > Mike > > > > > > > > > -Ursprüngliche Nachricht- > > > > > Von: [EMAIL PROTECTED] > > > > > [mailto:[EMAIL PROTECTED] Im Auftrag von > > > Sherief N. > > > > > Farouk > > > > > Gesendet: Donnerstag, 27. November 2008 18:26 > > > > > An: 'General Discussion of SQLite Database' > > > > > Betreff: Re: [sqlite] sqlite3_open16 fails on Windows > Vista 64b > > > > > > > > > > > I am calling now this: > > > > > > > > > > > > [DllImport("sqlite3.dll", CharSet = > CharSet.Unicode)] > > > > > > internal static extern System.Int64 > > > sqlite3_open16(string > > > > > > filename, out IntPtr handle); > > > > > > > > > > > > I now finds an entry point, but i returned to the > > > previous error: > > > > > > An attempt was made to read program in invalid format. > > > > > > > > > > > > But now everything is 64bit. > > > > > > > > > > > > Ti Ny > > > > > > > > > > > > > > > > No offense, but I'm beginning to believe you don't fully > > > grasp what > > > > > you're trying to do. First of all, if DW can't find the > > > entry point > > > > > then it's not there. Second, I don't think the CLR type > > > > > corresponding to the return value of > > > > > sqlite3_open16 is System.Int64, and I'm not sure what > string is > > > > > (CLR's string type is String, capital S). How about you > > > upload that > > > > > DLL of your somewhere, send a link and I wouldn't mind > > > checking it > > > > > for you. > > > > > > > > > > - Sherief > > > > > > > > > > ___ > > > > > sqlite-users mailing list > > > > > sqlite-users@sqlite.org > > > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > > > > > > > ___ > > > > 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&mkt=en-US > > > &form=QBRE > > > ___ > >
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&mkt=en-US > &form=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
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
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
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] Compile SQLite3 for MS Windows Driver Kit user-modeapplication
Dan, While I do understand the position chosen by SQLite, these warnings do try to prevent human error. In case of Visual C++, Microsoft has added lots of tests in order to make porting code from 32-bits to 64-bits easier. And those problems Mark wrote about were certainly reported as warnings since 2002 - maybe even earlier. The point I'm trying to make is that generally turning off warnings is a bad idea. If you dislike a warning for "stylistic purposes", that's fine - then only turn of those warnings with appropriate pragmas. Generally hiding warnings is a mistake IMHO. Mike > -Ursprüngliche Nachricht- > Von: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Im Auftrag von Dan > Gesendet: Dienstag, 7. Oktober 2008 16:30 > An: General Discussion of SQLite Database > Betreff: Re: [sqlite] Compile SQLite3 for MS Windows Driver > Kit user-modeapplication > > > The flood of warnings is a pain. SQLite dev claims they are all > > spurious, but with so many I wouldn't venture to guess how they can > > tell. > >http://www.sqlite.org/faq.html#q17 > > > ___ > 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
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
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
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
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
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
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
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
Re: [sqlite] Inserting data containing apostrope
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
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
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, &stat, 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, &stat, 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 Prob
Re: [sqlite] WinCE Memory Problem
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, &stat, 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, &stat, 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
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, &stat, 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
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
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?
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?
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?
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] -
Re: [sqlite] Help with C API - sqlite3
Don't use sqlite3_get_table. Use sqlite3_prepare_v2, sqlite3_step, sqlite3_finalize and related APIs instead. Mike > -Ursprüngliche Nachricht- > Von: Jonathan Hendler [mailto:[EMAIL PROTECTED] > Gesendet: Sonntag, 2. Dezember 2007 14:10 > An: sqlite-users@sqlite.org > Betreff: [sqlite] Help with C API - sqlite3 > > Hi All, > > I'm writing a C application which runs inside of FastCGI process. > > The problem is that I am getting poor performance... roughly 1000 > SELECTs in 20 seconds. > It's not an indexing issue (I promise). This is true even > when there are > only 2 rows. I'm not getting errors. > > PHP PDO sqlite runs about 1 equivalent SELECTS on the > SAME database > in about 2 seconds on my machine. > > What could I be doing so drastically wrong? > > I am using these defines to do the execution > > #define SQLITE_GET_RESULTS \ > char ** result;\ > char * err;\ > int rc, nrows,ncols;\ > rc = sqlite3_get_table(db,sql,&result,&nrows,&ncols,&err);\ > if (rc != SQLITE_OK){\ > if(err != NULL){\ > printf(" err %i: %s\n%s\n",rc,err,sql);\ > sqlite3_free(err);\ > sqlite3_free(sql);\ > }result = NULL; } > > #define RESULT_ITERATOR(result,ncols,nrows,i,j) \ > int i,j;\ > for(i=0;i< nrows ;i++){\ > for (j=0;j > #define SQLITE_RESULT_CLEANUP if (result) { > sqlite3_free_table(result);} > if (sql) { sqlite3_free(sql);} > > The SQL is created via sqlite3_vmprintf(). > > I've increased the pragmas for memory, it's a shared cache, > thread safe > - though tweaking these items has not appeared to affect performance. > > I can provide more relevant information with some hints about where to > start. > > Thanks in advance. > > Cheers, > Jonathan > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > > - To unsubscribe, send email to [EMAIL PROTECTED] -
AW: [sqlite] Transactional DDL
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.
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.
> > > #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()
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 > &g
AW: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()
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()
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()
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()
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()
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()
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()
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()
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()
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
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
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< lst; char* szError=0; const char* szTail=0; sqlite3_stmt* pVM; string clauseSt; //memset(buf,0,1024); //sprintf(buf,"select doid from test where id = :xyz"); sprintf(buf,"select column2 from ttest where id = :xyz"); ret = sqlite3_prepare(db, buf, strlen(buf), &pVM, &szTail); cout<<__LINE__<<" ret="< Would this be a simple change? > > Ken > > "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: > Hi > > >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. > > > > Perhaps the key can be modified only for comparation. You store > the > key as you want, but before compare it do a rle compression. You > can > store the rle compressed key in database too. Note that rle is a > one-to- > one transform, that is one key has only one compressed key and > one > compressed key has only one key. Working that way you can compare > 200,1,1,2 with 199,1,2,2 (four bytes) instead ..(196 > '1')..2 > with ..(195 '1')..22. > > > HTH > > >regards > >ragha > > > --- > -- > To unsubscribe, send email to [EMAIL PROTECTED] > --- > -- > > > - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
AW: [sqlite] Re: In Mem Query Performance
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",&db); > > char* errmsg; > char buf[1024]; > > sprintf(buf,"create temporary table ttest as select * from test; "); >ret = sqlite3_exec(db , buf, NULL ,NULL,&errmsg); >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,&errmsg); >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">//Now Query Data. >time_t start = time(0); >ftime(&startTime); > > > >char* szError=0; >const char* szTail=0; >sqlite3_stmt* pVM; > >sprintf(buf,"select * from ttest where name = ?" ); >ret = sqlite3_prepare_v2(db, buf, -1, &pVM, &szTail); >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); >} > > > >ret = sqlite3_finalize(pVM); >ftime(&endTime); > ms = ( >((
AW: [sqlite] In Mem Query Performance
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
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()
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, &errmsg); 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
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
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
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
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. Youd 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 theyre 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. Youre 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
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
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 > >
AW: [sqlite] Still getting "Insertion failed because database is full." errors
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
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 did
AW: [sqlite] Still getting "Insertion failed because database is full." errors
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
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
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
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: Re: [sqlite] SQLite and nested transactions
IMHO it depends on your needs. Usually the 4th will try some sort of error recovery and if that fails, rollback itself. This bubbles up until it reaches the top. -Ursprüngliche Nachricht- Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Gesendet: Montag, 9. April 2007 19:50 An: sqlite-users@sqlite.org Cc: [EMAIL PROTECTED] Betreff: Fwd: Re: [sqlite] SQLite and nested transactions I'm still learning how databases and transactions work so bear with me. How does error handling work? If I have 5 nested transactions and the 5th transaction fails is this transaction failure supposed to be communicated to the parent or does the fourth transaction try to do error recovery? Ray > Date: Mon, 9 Apr 2007 9:38:16 -0700 > From: <[EMAIL PROTECTED]> > To: sqlite-users@sqlite.org > Cc: Dennis Cote <[EMAIL PROTECTED]> > Subject: 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] - - To unsubscribe, send email to [EMAIL PROTECTED] -
AW: [sqlite] SQLite and nested transactions
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
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: 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
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
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
AW: AW: [sqlite] Function Language
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 > 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] > -- > --- > - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
AW: [sqlite] Function Language
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
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?
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
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] -
RE: Re: [sqlite] SQLite JSON interface?
Thanks, looks good! Michael >Michael Ruck wrote: >> >> 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. > >Maybe the untested unsupported source code from >http://www.ch-werner.de/sqliteodbc/sqlite3json.tgz >can be used for that purpose. > >Cheers, >Christian > >- >To unsubscribe, send email to [EMAIL PROTECTED] >- > - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] SQLite JSON interface?
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
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", &db_one); > > sqlite3_prepare(db_one, "CREATE TABLE foo (id);", -1, &stmt_one, > NULL); sqlite3_step(stmt_one); sqlite3_finalize(stmt_one); > > sqlite3_prepare(db_one, "BEGIN DEFERRED;", -1, &stmt_one, NULL); > sqlite3_step(stmt_one); sqlite3_finalize(stmt_one); > > sqlite3_prepare(db_one, "INSERT INTO foo VALUES (123);", -1, > &stmt_one, NULL); sqlite3_step(stmt_one); sqlite3_finalize(stmt_one); > > sqlite3_prepare(db_one, "SELECT count(*) FROM foo;", -1, &stmt_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", &db_two); > > sqlite3_prepare(db, "SELECT count(*) FROM foo;", -1, &stmt_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. -
AW: [sqlite] Equivalent of OLE object da
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
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. > >
AW: [sqlite] sqlite performance, locking & threading
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
AW: [sqlite] sqlite performance, locking & threading
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
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
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
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
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
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?
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?
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
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] -
Re: [sqlite] How long can I keep a prepared statement around?
Use the function sqlite3_expired to determine, when you need to recompile a prepared statement. That's the approach I use. Mike Am 24.09.2006 um 20:48 schrieb Kervin L. Pierre: Hello, I have a few queries that are executed very often. I would like to keep them around as much as possible. The problem is, I don't know what 'invalidates' a prepared statement. In other words, when can I expect to have to 're-'prepare a statement? How long can I keep a prepared statement? Can they be passed between threads? I take it they are tied to a specific sqlite3_db* handle? Do starting new transactions, or transaction rollbacks, etc. affect them? Ideally, for instance, for simple queries such as 'BEGIN' and 'COMMIT', I'd like to keep those prepared statements for the lifetime of the application if possible. Any information would be appreciated. Best regards, Kervin -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Queries fail - I can't figure out why
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?
>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
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?
Hi John, Use System.Collections.ArrayList if all you need is the list of names. Just call .Add for each field name, the container will grow automatically to accomodate for all fields. At the end you can call .Count to determine the total number and index the list from 0 to .Count - 1. Mike -Ursprüngliche Nachricht- Von: John Newby [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 13. Juli 2006 18:28 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] How do you find out the names of the fields within a table? Hi Mike, I am using vb.net 2002. I looked at the scripting.dictionary class as suggested earlier in the thread, but I could not understand it. I would think a count of the rows would be needed in order to know how many times the while loop would need to be executed to add the name of each field. On 13/07/06, Michael Ruck <[EMAIL PROTECTED]> wrote: > > 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?
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?
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?
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.
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.
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
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
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: Re[2]: [sqlite] MMAP
My experience differs on this one. When processing large amounts of data with a processing function, which takes longer than the I/O using a mapped file is faster than sequential I/O, as the Windows memory manager and caching system will pull in the I/O pages asynchronously. At least on Windows I'd say using mapped files for really large data transfers is reasonable and most likely faster, than using sequential I/O using any other method. Mike -Ursprüngliche Nachricht- Von: Teg [mailto:[EMAIL PROTECTED] Gesendet: Sonntag, 16. April 2006 16:55 An: John Stanton Cc: sqlite-users@sqlite.org Betreff: Re[2]: [sqlite] MMAP In my experience under windows, using memory mapped files is no faster than opening and reading the file into memory then processing it. The limiting factor is the disk drives. I use both methods and the choice of method is always a tossup. If the API I'm using will accept an entire buffer for the file, say a JPG decode, I'll use mmap (windows equivalent) otherwise I may just read it a chunk at a time. My impression is, he's asking a general MMAP versus fopen question and not planning on integrating MMAP into SQLite. C Sunday, April 16, 2006, 10:00:57 AM, you wrote: JS> John Stanton wrote: >> I wonder if members can help me with some advice. I have a program >> which is a multi-threaded application server with Sqlite embedded which >> runs on Unix and Windows. For an i/o buffer per thread I have the idea >> of using a mmap'd file so that it can be transferred using >> sendfile/TransmitFile with minimum overhead and no buffer shadowing. >> The program design is such that there is a pool of inactive threads >> waiting for a connection, and that pool could be quite large. Each one >> would have a mmap'd file with an open fd involved. >> >> Does anyone know the performance impact of having a considerable number >> of mmap'd files attached to a single process in Unix/Linux and Windows? >> Has anyone tried such a strategy? My guess is that the impact is not >> great, but I have no evidence in support. >> JS >> JS> You have a lot of work ahead of you if you want to modify SQLite to JS> use MMAP. SQLite was not designed for this. What are you trying to JS> accomplish? JS> I have no idea what the performance implications of using MMAP are. JS> The only way to know is to try it and see. JS> === JS> Thankyou for the response. I am not modifying Sqlite, just JS> embedding it in an application server, and looking at ways to optimally construct the JS> server. It seems to me ideal to use zero buffering capabilities JS> of the host OS if possible and to use a mmap'd file as a buffer and JS> hold its fd so that it can be sent to the network without further JS> buffer copying. Since it is difficult to set up realistic tests to JS> simulate high volumes and loads I was curious if others had tried JS> this approach and experienced any unexpected side effects in practice. JS> BTW, Sqlite fits very neatly into such an environment. This JS> particular application has hundreds of databases, each with a JS> handful of users, a situation where Sqlite's single file simplicity JS> is a great asset and no impediment to high performance. By having a JS> purpose-designed server and application specific language plus the JS> unbloated Sqlite I am looking to achieve higher performance and JS> efficiency than by cobbling it together from some general purpose language processor and generic web server. JS> The recent discussion about locality of reference made me cautious. JS> My concern is about having local memory references in the program JS> held up by thrashing in virtual memory and losing more than would be JS> gained by the more efficient sendfile type transfer. JS> The system I am replacing was put together on the basis that there JS> were plenty of resources to compensate for each added inefficiency, JS> until one day it did matter - the camel's back eventually broke. JS> JS -- Best regards, Tegmailto:[EMAIL PROTECTED]
AW: [sqlite] VB6 question
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
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
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
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
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