[sqlite] Store and retreive 0D0A (CRLF) in string field
When I have text pasted into an sqlite string field, it is stored okay but when I retrieve a string, it is truncated at the first CR (0D). Does anyone know how to handle this by any chance? -- View this message in context: http://www.nabble.com/Store-and-retreive-0D0A-%28CRLF%29-in-string-field-tf3461423.html#a9657638 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sub-query optimization question
"Kevin Alons" <[EMAIL PROTECTED]> wrote: > Using sqlite 3.3.13, I have the following query: > > SELECT v.tID > FROM (SELECT tID FROM Test WHERE funcFast(1, tID) = 1) v > WHERE v.tID IN (SELECT tID FROM Test WHERE funcSlow(tID) GLOB 'abc*'); > > WHERE funcFast is a function which does something that executes quickly, and > funcSlow is a function that takes significant resources to complete. > > My hope was that the above query would first implicitly create a temp table > containing only the subset of table Test which met the criteria based on > funcFast and sqlite would then execute funcSlow on just this temp table. > > This doesn't appear to be the case, but I believe sqlite is flattening this > query and the result is that funcSlow is being executed on every record in > table Test. > > Is there a workaround to achieve this optimization, preferably without > explicitly creating temp table for the inner select statement results? > I think you can trick the optimizer into not flattening the query by appending LIMIT -1 OFFSET 0 to the end of the subquery. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] sub-query optimization question
Using sqlite 3.3.13, I have the following query: SELECT v.tID FROM (SELECT tID FROM Test WHERE funcFast(1, tID) = 1) v WHERE v.tID IN (SELECT tID FROM Test WHERE funcSlow(tID) GLOB 'abc*'); WHERE funcFast is a function which does something that executes quickly, and funcSlow is a function that takes significant resources to complete. My hope was that the above query would first implicitly create a temp table containing only the subset of table Test which met the criteria based on funcFast and sqlite would then execute funcSlow on just this temp table. This doesn't appear to be the case, but I believe sqlite is flattening this query and the result is that funcSlow is being executed on every record in table Test. Is there a workaround to achieve this optimization, preferably without explicitly creating temp table for the inner select statement results? Kevin - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite segfault using libc library
On 3/24/07, John Stanton <[EMAIL PROTECTED]> wrote: Andrew Finkenstadt wrote: > On 3/24/07, John Stanton <[EMAIL PROTECTED]> wrote: > >> >> Compilers do not terminate strings, library functions do. > > > > You are guaranteed by the C standard that the string referred to by > >>> const char message[] = "this string"; > > > is null-terminated by the compiler. > Of course you are correct. However this string is read only and if you use GNU C and try to write to it you get a kick in the head. - To unsubscribe, send email to [EMAIL PROTECTED] - Yes, and I am not writing to it... I am binding a string like message[] to a sqlite query (using sqlite3_bind_text(stmt, bIndex, value, -1, SQLITE_TRANSIENT); ) requesting that SQL count the length of the string, and SQLite apparently calls strlen(). One of the calls to strlen segfaulted. I was wondering if anyone every had this same error. I figure either sqlite made a mistake, or there is a problem with the libc library (this is an ARM arch). If the consensus is that this is libc's problem I will move this discussion to that list. Of course, I guess I could determine if it is libc's fault by replacing the -1 with strlen(message) and seeing if I see the same problem. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite segfault using libc library
Andrew Finkenstadt wrote: On 3/24/07, John Stanton <[EMAIL PROTECTED]> wrote: Compilers do not terminate strings, library functions do. You are guaranteed by the C standard that the string referred to by const char message[] = "this string"; is null-terminated by the compiler. Of course you are correct. However this string is read only and if you use GNU C and try to write to it you get a kick in the head. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How does SQLite store data?
There are no stupid questions. only stupid answers. Sqlite stores the entire length of the string and never truncates. Its TEXT type handles every string, provided that it is text, otherwise it needs to be a BLOB (e.g. a JPEG). Sqlite is simpler than you can imagine to use because its manifest typing removes almost all of the concern about data types. Migrating from other SQL RDBMS's to Sqlite is straight forward but going the other way is not necessarily easy. Jonathon Blake wrote: John wrote: A TEXT string is stored at its actual length. You may declare a text column as 80 characters wide but you could store a string 32K long in that column. The 80 is stored by Sqlite but ignored. Stupid question. Does that mean that SQLite: * truncates the field at 80 characters? * only returns the first 80 characters in the field? * stores and returns the entire string? My impression was that it did that third option. [And that it didn't care what the data in that field was. It is up to the client program to verify the data.] xan jonathon - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite segfault using libc library
On 3/24/07, John Stanton <[EMAIL PROTECTED]> wrote: Compilers do not terminate strings, library functions do. You are guaranteed by the C standard that the string referred to by const char message[] = "this string"; is null-terminated by the compiler.
Re: [sqlite] How does SQLite store data?
John wrote: A TEXT string is stored at its actual length. You may declare a text column as 80 characters wide but you could store a string 32K long in that column. The 80 is stored by Sqlite but ignored. Stupid question. Does that mean that SQLite: * truncates the field at 80 characters? * only returns the first 80 characters in the field? * stores and returns the entire string? My impression was that it did that third option. [And that it didn't care what the data in that field was. It is up to the client program to verify the data.] xan jonathon - To unsubscribe, send email to [EMAIL PROTECTED] -
SV: [sqlite] sqlite segfault using libc library
I have install this application in 24 PC's and its only 3 PC's where the application not work it's creates map but not database? -Ursprungligt meddelande- Från: Rich Rattanni [mailto:[EMAIL PROTECTED] Skickat: den 24 mars 2007 17:37 Till: sqlite-users@sqlite.org Ämne: Re: [sqlite] sqlite segfault using libc library > Maybe using an invalid UTF-8 string as input to SQLite? > Maybe your wrapper uses strlen to find the end of the string and that > is different from the SQL string length function of SQLite, which may > be diferent on UTF-8 strings? My wrapper class never uses strlen(). Instead I bind the string to the statement, and I indicate the stringlength of -1 (which I assume means sqlite runs strlen() internally). And I do not change the strings while the program runs. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite segfault using libc library
Just because you have a pointer assigned to the string does not ensure that it is terminated. It only finds the start of the string. You need a null character at the end. I understand that John, but these strings I am writing to the database are declared as follows... const char message[] = "some message"; So the compiler will be null terminating these. Thats why I am confused, it is no like I am building a string manually and forgetting the \0 at the end. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite segfault using libc library
Just because you have a pointer assigned to the string does not ensure that it is terminated. It only finds the start of the string. You need a null character at the end. Rich Rattanni wrote: I agree, but all my strings are const char *, and I do not modify them during program execution. On 3/24/07, John Stanton <[EMAIL PROTECTED]> wrote: It looks like you might have an unterminated string. Rich Rattanni wrote: > All: >I am writing an application that heavily logs all activity to a > sqlite3 database. Last night, while running some extended testing, I > caught a segmentation fault. The core dump isnt of much help... > > (gdb) bt > #0 0x403d2934 in strlen () from /lib/libc.so.6 > #1 0x401add60 in ?? () from /usr/lib/libsqlite3.so.0 > (gdb) > > I do pass strings into sqlite to be logged, but these strings do not > change while the program is running. In fact all of these strings are > declared as const char * in most of the header files. > > I bind all my text to the sqlite statements I execute (I wrote a > simple sqlite3 wrapper class). The bind statement looks like so... > sqlite3_bind_text(stmt, bIndex, value, -1, SQLITE_TRANSIENT); > > I use SQLITE_TRANSIENT because since this is a generic wrapper, I may > from time to time write a character string that is lives on some > functions stack and I didnt want to take the chance. > > I realize there isnt much to go on here, but any suggestions or > guidance would be appreicated. > > -- > Rich > > - > > 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] sqlite segfault using libc library
Maybe using an invalid UTF-8 string as input to SQLite? Maybe your wrapper uses strlen to find the end of the string and that is different from the SQL string length function of SQLite, which may be diferent on UTF-8 strings? My wrapper class never uses strlen(). Instead I bind the string to the statement, and I indicate the stringlength of -1 (which I assume means sqlite runs strlen() internally). And I do not change the strings while the program runs. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite segfault using libc library
On 3/24/07, Rich Rattanni <[EMAIL PROTECTED]> wrote: I agree, but all my strings are const char *, and I do not modify them during program execution. Maybe using an invalid UTF-8 string as input to SQLite? Maybe your wrapper uses strlen to find the end of the string and that is different from the SQL string length function of SQLite, which may be diferent on UTF-8 strings? Regards, ~Nuno Lucas On 3/24/07, John Stanton <[EMAIL PROTECTED]> wrote: > It looks like you might have an unterminated string. > > Rich Rattanni wrote: > > All: > >I am writing an application that heavily logs all activity to a > > sqlite3 database. Last night, while running some extended testing, I > > caught a segmentation fault. The core dump isnt of much help... > > > > (gdb) bt > > #0 0x403d2934 in strlen () from /lib/libc.so.6 > > #1 0x401add60 in ?? () from /usr/lib/libsqlite3.so.0 > > (gdb) > > > > I do pass strings into sqlite to be logged, but these strings do not > > change while the program is running. In fact all of these strings are > > declared as const char * in most of the header files. > > > > I bind all my text to the sqlite statements I execute (I wrote a > > simple sqlite3 wrapper class). The bind statement looks like so... > > sqlite3_bind_text(stmt, bIndex, value, -1, SQLITE_TRANSIENT); > > > > I use SQLITE_TRANSIENT because since this is a generic wrapper, I may > > from time to time write a character string that is lives on some > > functions stack and I didnt want to take the chance. > > > > I realize there isnt much to go on here, but any suggestions or > > guidance would be appreicated. > > > > -- > > Rich > > > > - > > > > 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] sqlite and bcc32
On 3/24/07, stripe <[EMAIL PROTECTED]> wrote:
Build sqlite3.lib with Borland Free Tools 5.5.1 correctly.
Using it with someprog.c everithing is OK, but when I change to .cpp got
Error E2232 /sqlite/open/sqlite3.h 1712: Constant member
'sqlite3_index_info::nConstraint' in class without constructors
Error E2232 /sqlite/open/sqlite3.h 1712: Constant member
'sqlite3_index_info::aConstraint' in class without constructors
Error E2232 /sqlite/open/sqlite3.h 1712: Constant member
'sqlite3_index_info::nOrderBy' in class without constructors
Error E2232 /sqlite/open/sqlite3.h 1712: Constant member
'sqlite3_index_info::aOrderBy' in class without constructors
Error E2232 /sqlite/open/sqlite3.h 1712: Constant member
'sqlite3_index_info::aConstraintUsage' in class without constructors
Try to use:
extern "C" {
#include
}
in your C++ code.
If that doesn't work, seems like a bug in the compiler.
It would help to know the sqlite version you are using.
Regards,
~Nuno Lucas
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] sqlite segfault using libc library
I agree, but all my strings are const char *, and I do not modify them during program execution. On 3/24/07, John Stanton <[EMAIL PROTECTED]> wrote: It looks like you might have an unterminated string. Rich Rattanni wrote: > All: >I am writing an application that heavily logs all activity to a > sqlite3 database. Last night, while running some extended testing, I > caught a segmentation fault. The core dump isnt of much help... > > (gdb) bt > #0 0x403d2934 in strlen () from /lib/libc.so.6 > #1 0x401add60 in ?? () from /usr/lib/libsqlite3.so.0 > (gdb) > > I do pass strings into sqlite to be logged, but these strings do not > change while the program is running. In fact all of these strings are > declared as const char * in most of the header files. > > I bind all my text to the sqlite statements I execute (I wrote a > simple sqlite3 wrapper class). The bind statement looks like so... > sqlite3_bind_text(stmt, bIndex, value, -1, SQLITE_TRANSIENT); > > I use SQLITE_TRANSIENT because since this is a generic wrapper, I may > from time to time write a character string that is lives on some > functions stack and I didnt want to take the chance. > > I realize there isnt much to go on here, but any suggestions or > guidance would be appreicated. > > -- > Rich > > - > > To unsubscribe, send email to [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite segfault using libc library
It looks like you might have an unterminated string. Rich Rattanni wrote: All: I am writing an application that heavily logs all activity to a sqlite3 database. Last night, while running some extended testing, I caught a segmentation fault. The core dump isnt of much help... (gdb) bt #0 0x403d2934 in strlen () from /lib/libc.so.6 #1 0x401add60 in ?? () from /usr/lib/libsqlite3.so.0 (gdb) I do pass strings into sqlite to be logged, but these strings do not change while the program is running. In fact all of these strings are declared as const char * in most of the header files. I bind all my text to the sqlite statements I execute (I wrote a simple sqlite3 wrapper class). The bind statement looks like so... sqlite3_bind_text(stmt, bIndex, value, -1, SQLITE_TRANSIENT); I use SQLITE_TRANSIENT because since this is a generic wrapper, I may from time to time write a character string that is lives on some functions stack and I didnt want to take the chance. I realize there isnt much to go on here, but any suggestions or guidance would be appreicated. -- Rich - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Count of rows in every table
On 3/23/07, Noah Hart <[EMAIL PROTECTED]> wrote: Using only SQLite and SQL, is there a way to get a count of rows in each table in the database? I know I can do this via an external application to query sqlite_master and using that list, query a row count of each table, but would prefer to find a pure SQL way if possible. I believe what you want is something like a single select that takes the table name for the count on another sub-select to the sqlite_master table, but as far as I know (I also wanted something similar one time) that can't be done, because under SQL a table name isn't an expression, so can't be replaced with a sub-select. In other words: "SELECT count(*) FROM ?" is not valid (gives diferent compiled SQL for each table name), so you can't replace "?" for a sub-select. Regards, ~Nuno Lucas Thanks, Noah - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How does SQLite store data?
Sqlite has a concept called "manifest typing" where it makes decisons on how to store data. It does not have fixed length columns except for the ones which hold integer and real numbers and boolean values. A TEXT string is stored at its actual length. You may declare a text column as 80 characters wide but you could store a string 32K long in that column. The 80 is stored by Sqlite but ignored. [EMAIL PROTECTED] wrote: I created several tables that specified explicitly the size of each column. I put some bogus numbers and text into it but I didn't put numbers in it that would completely fill the column data. I was surprised to find that the resulting database file was smaller than I had expected. This implies that SQLite stores the data in its smallest space on the disk. Is this correct? Ray - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] sqlite segfault using libc library
All: I am writing an application that heavily logs all activity to a sqlite3 database. Last night, while running some extended testing, I caught a segmentation fault. The core dump isnt of much help... (gdb) bt #0 0x403d2934 in strlen () from /lib/libc.so.6 #1 0x401add60 in ?? () from /usr/lib/libsqlite3.so.0 (gdb) I do pass strings into sqlite to be logged, but these strings do not change while the program is running. In fact all of these strings are declared as const char * in most of the header files. I bind all my text to the sqlite statements I execute (I wrote a simple sqlite3 wrapper class). The bind statement looks like so... sqlite3_bind_text(stmt, bIndex, value, -1, SQLITE_TRANSIENT); I use SQLITE_TRANSIENT because since this is a generic wrapper, I may from time to time write a character string that is lives on some functions stack and I didnt want to take the chance. I realize there isnt much to go on here, but any suggestions or guidance would be appreicated. -- Rich - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] sqlite and bcc32
Build sqlite3.lib with Borland Free Tools 5.5.1 correctly. Using it with someprog.c everithing is OK, but when I change to .cpp got Error E2232 /sqlite/open/sqlite3.h 1712: Constant member 'sqlite3_index_info::nConstraint' in class without constructors Error E2232 /sqlite/open/sqlite3.h 1712: Constant member 'sqlite3_index_info::aConstraint' in class without constructors Error E2232 /sqlite/open/sqlite3.h 1712: Constant member 'sqlite3_index_info::nOrderBy' in class without constructors Error E2232 /sqlite/open/sqlite3.h 1712: Constant member 'sqlite3_index_info::aOrderBy' in class without constructors Error E2232 /sqlite/open/sqlite3.h 1712: Constant member 'sqlite3_index_info::aConstraintUsage' in class without constructors Thanks -- View this message in context: http://www.nabble.com/sqlite-and-bcc32-tf3458305.html#a9648859 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Count of rows in every table
select count(*) from tablename ... if sqlite supports count(*) ... Marten - To unsubscribe, send email to [EMAIL PROTECTED] -
