Re: [sqlite] System.Data.SQLite: Leading zeros being stripped off
Am 15.07.2013 22:30, schrieb Gerry Snyder: On 7/15/2013 1:18 PM, Bernd wrote: I'm reading that text out of an Oracle-DB into a SQLite table which has the affected column defined as 'String' - which maps to TEXT in native SQLite No. Look at section 2.1 of http://sqlite.org/datatype3.html Only CHAR, CLOB, or TEXT cause the column to have TEXT affinity. HTH, Gerry Snyder If I understand correctly, this is right for the C API or rather SQL queries without any interpreting layer in between, but the .NET wrapper explicitly maps type String to affinity TEXT in SQLiteConvert.cs. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] System.Data.SQLite: Leading zeros being stripped off
Am 15.07.2013 22:26, schrieb Simon Slavin: On 15 Jul 2013, at 9:18pm, Bernd wrote: I know that SQLite is inherently type-less, but I'm using System.Data.SQLite which tries it very best to force that into the common ADO.NET schema. I'm having troubles with some text that has leading zeros, like '01', '02' and so on. I'm reading that text out of an Oracle-DB into a SQLite table which has the affected column defined as 'String' - which maps to TEXT in native SQLite - via a parameterized insert query and a parameter DbType of 'String' as well. Nonetheless the leading zero gets stripped off the text. Any ideas how I could preserve those leading zeros? The following two statements do different things. INSERT INTO myTable VALUES (01) INSERT INTO myTable VALUES ('01') Can you tell what's being done in your setup ? Is there a way using your API that you can stress that the value you're binding or inserting is text, not a number ? Simon. As I use a parameterized query, I'm pretty certain that it should be '01' - the second case. Stepping through the code in VS Debugger also shows that DbType of that parameter is String and Value is '01'. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] System.Data.SQLite: Leading zeros being stripped off
On 7/15/2013 1:18 PM, Bernd wrote: I'm reading that text out of an Oracle-DB into a SQLite table which has the affected column defined as 'String' - which maps to TEXT in native SQLite No. Look at section 2.1 of http://sqlite.org/datatype3.html Only CHAR, CLOB, or TEXT cause the column to have TEXT affinity. HTH, Gerry Snyder ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] System.Data.SQLite: Leading zeros being stripped off
On 15 Jul 2013, at 9:18pm, Bernd wrote: > I know that SQLite is inherently type-less, but I'm using System.Data.SQLite > which tries it very best to force that into the common ADO.NET schema. > I'm having troubles with some text that has leading zeros, like '01', '02' > and so on. I'm reading that text out of an Oracle-DB into a SQLite table > which has the affected column defined as 'String' - which maps to TEXT in > native SQLite - via a parameterized insert query and a parameter DbType of > 'String' as well. Nonetheless the leading zero gets stripped off the text. > Any ideas how I could preserve those leading zeros? The following two statements do different things. INSERT INTO myTable VALUES (01) INSERT INTO myTable VALUES ('01') Can you tell what's being done in your setup ? Is there a way using your API that you can stress that the value you're binding or inserting is text, not a number ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_auto_extension - unloaded DLL issue
Thank you MR. Hipp... Dušan 2013/7/15 Richard Hipp > On Sat, Jul 13, 2013 at 5:14 AM, Dušan Paulovič > wrote: > > > Hello, > > we are currently facing problem with Access violation exception caused by > > function sqlite3_open_v2 trying to load extensions from unloaded DLLs. > > > > New interface added: sqlite3_cancel_auto_extension(X). You can use this to > cancel prior calls to sqlite3_auto_extension() before unloading the DLL. > > http://www.sqlite.org/draft/c3ref/cancel_auto_extension.html > > Download snapshots containing this change from > > http://www.sqlite.org/draft/download.html > > > > > > > How it happens: > > - There are 2 (or more) plugin DLLs in main application which are linked > to > > SQLite. > > - These plugins can be loaded and uloaded by user. > > - Main application itself does not use SQLite, so each plugin using > SQLite > > must be linked to it. > > - When any plugin is loaded, it registers its entry point using interface > > sqlite3_auto_extension. > > - So when 2 (or more) plugins are loaded, each registers its entry > point(s) > > to the same SQLite module instance. > > - When one of these plugins is going to be unloaded by user, it can not > use > > sqlite3_reset_auto_extension, > > because that would uninstall also all entry points of another plugins. > > So it happens, that SQlite holds entry point(s) to DLL which is > unloaded. > > - Now, if user executes any command from remaining DLL, it invokes > > sqlite3_open_v2, > > SQLite invokes all registered entry points including those pointing to > > released memory, > > so system raises Access Viloation exception. > > > > Possible solution of this issue would be a way to 'uninstall' entry > point. > > something like: > > int sqlite3_remove_auto_extension(void (*xEntryPoint)(void)); > > > > Also it would be fine to be able to load static extensions to separate > > connections: > > something like: > > int sqlite3_load_static_extension(sqlite3 *db, void > (*xEntryPoint)(void)); > > > > If you would need any additional information about issue, > > please contact me at: paulo...@gisoft.cz > > > > Additional info: > > SQLite Version: 3.7.14.1, Source ID: 2012-10-04 19:37:12 > > 091570e46d04e84b67228e0bdbcd6e1fb60c6bdb > > > > Perhaps irrelevant in this case: > > OS: Windows Vista > > Main Application: Bentley MicroStation V8i > > Plugins: MDL (MicroStation Development Library) > > > > Regards, > > Dusan Paulovic > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- > D. Richard Hipp > d...@sqlite.org > ___ > 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] System.Data.SQLite: Leading zeros being stripped off
I know that SQLite is inherently type-less, but I'm using System.Data.SQLite which tries it very best to force that into the common ADO.NET schema. I'm having troubles with some text that has leading zeros, like '01', '02' and so on. I'm reading that text out of an Oracle-DB into a SQLite table which has the affected column defined as 'String' - which maps to TEXT in native SQLite - via a parameterized insert query and a parameter DbType of 'String' as well. Nonetheless the leading zero gets stripped off the text. Any ideas how I could preserve those leading zeros? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Memory usage of sqlite3
On Mon, Jul 15, 2013 at 9:08 PM, Eduardo Morras wrote: > On Mon, 15 Jul 2013 20:49:52 +0200 > Paolo Bolzoni wrote: > >> From 35-40MB to 940MB; I would put massif result but I think the >> list deletes attachments. > > What does PRAGMA temp_store show? Set it to 0 and recheck. Did you compile > with SQLITE_TEMP_STORE set to 3? This ones, so no... -DSQLITE_ENABLE_FTS3=1 -DSQLITE_ENABLE_COLUMN_METADATA=1 -DSQLITE_ENABLE_UNLOCK_NOTIFY -DSQLITE_SECURE_DELETE "Recheck" needs some time. The test lasted almost 7 hours... I start it now... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Memory usage of sqlite3
On Mon, 15 Jul 2013 20:49:52 +0200 Paolo Bolzoni wrote: > From 35-40MB to 940MB; I would put massif result but I think the > list deletes attachments. What does PRAGMA temp_store show? Set it to 0 and recheck. Did you compile with SQLITE_TEMP_STORE set to 3? --- --- Eduardo Morras ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Memory usage of sqlite3
On Mon, Jul 15, 2013 at 8:59 PM, Simon Slavin wrote: > > On 15 Jul 2013, at 7:49pm, Paolo Bolzoni > wrote: > >> From 35-40MB to 940MB; I would put massif result but I think the >> list deletes attachments. > > Do you have in-memory tables ? No. > Do you use sqlite3_exec() ? Twice, to activate the pragma and to create the db. > Do you have SELECTs for which there is no good index, forcing sqlite3 to make > up its own ? I checked with EXPLAIN and no. Seems not. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Memory usage of sqlite3
On Mon, Jul 15, 2013 at 08:49:52PM +0200, Paolo Bolzoni scratched on the wall: > >From 35-40MB to 940MB; I would put massif result but I think the > list deletes attachments. By default, the page-cache is 2000. Pages are typically 1KB, but have some minor overhead in the cache. Assuming you haven't turned the page cache down, until you top ~3MB there is nothing unusual at all. -j > On Mon, Jul 15, 2013 at 8:41 PM, Stephan Beal wrote: > > On Mon, Jul 15, 2013 at 8:39 PM, Paolo Bolzoni < > > paolo.bolzoni.br...@gmail.com> wrote: > > > >> So, sorry if the question sounds very vague. But what can > >> cause high memory usage in sqlite? A large transaction > >> maybe? > >> > > > > What is "high"? In my apps sqlite tends to use 200-400kb or so, which i > > don't consider to be all that high considering what it's capable of doing > > for me. > > > > -- > > - stephan beal > > http://wanderinghorse.net/home/stephan/ > > http://gplus.to/sgbeal > > ___ > > 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 -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Memory usage of sqlite3
On 15 Jul 2013, at 7:49pm, Paolo Bolzoni wrote: > From 35-40MB to 940MB; I would put massif result but I think the > list deletes attachments. Do you have in-memory tables ? Do you use sqlite3_exec() ? Do you have SELECTs for which there is no good index, forcing sqlite3 to make up its own ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Memory usage of sqlite3
>From 35-40MB to 940MB; I would put massif result but I think the list deletes attachments. On Mon, Jul 15, 2013 at 8:41 PM, Stephan Beal wrote: > On Mon, Jul 15, 2013 at 8:39 PM, Paolo Bolzoni < > paolo.bolzoni.br...@gmail.com> wrote: > >> So, sorry if the question sounds very vague. But what can >> cause high memory usage in sqlite? A large transaction >> maybe? >> > > What is "high"? In my apps sqlite tends to use 200-400kb or so, which i > don't consider to be all that high considering what it's capable of doing > for me. > > -- > - stephan beal > http://wanderinghorse.net/home/stephan/ > http://gplus.to/sgbeal > ___ > 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] Memory usage of sqlite3
On Mon, Jul 15, 2013 at 8:39 PM, Paolo Bolzoni < paolo.bolzoni.br...@gmail.com> wrote: > So, sorry if the question sounds very vague. But what can > cause high memory usage in sqlite? A large transaction > maybe? > What is "high"? In my apps sqlite tends to use 200-400kb or so, which i don't consider to be all that high considering what it's capable of doing for me. -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Memory usage of sqlite3
I wrote an C++ application that uses sqlite3 to save part of the data when it become larger than a known threshold. The idea is to use at most a known quantity of memory; to check if it is working I executed a relevant test using valgrind's massif. It worked fairly well most of the time, but in two cases there is a peak of memory usage from sqlite. So, sorry if the question sounds very vague. But what can cause high memory usage in sqlite? A large transaction maybe? Thanks for any insight. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_auto_extension - unloaded DLL issue
On Sat, Jul 13, 2013 at 5:14 AM, Dušan Paulovič wrote: > Hello, > we are currently facing problem with Access violation exception caused by > function sqlite3_open_v2 trying to load extensions from unloaded DLLs. > New interface added: sqlite3_cancel_auto_extension(X). You can use this to cancel prior calls to sqlite3_auto_extension() before unloading the DLL. http://www.sqlite.org/draft/c3ref/cancel_auto_extension.html Download snapshots containing this change from http://www.sqlite.org/draft/download.html > > How it happens: > - There are 2 (or more) plugin DLLs in main application which are linked to > SQLite. > - These plugins can be loaded and uloaded by user. > - Main application itself does not use SQLite, so each plugin using SQLite > must be linked to it. > - When any plugin is loaded, it registers its entry point using interface > sqlite3_auto_extension. > - So when 2 (or more) plugins are loaded, each registers its entry point(s) > to the same SQLite module instance. > - When one of these plugins is going to be unloaded by user, it can not use > sqlite3_reset_auto_extension, > because that would uninstall also all entry points of another plugins. > So it happens, that SQlite holds entry point(s) to DLL which is unloaded. > - Now, if user executes any command from remaining DLL, it invokes > sqlite3_open_v2, > SQLite invokes all registered entry points including those pointing to > released memory, > so system raises Access Viloation exception. > > Possible solution of this issue would be a way to 'uninstall' entry point. > something like: > int sqlite3_remove_auto_extension(void (*xEntryPoint)(void)); > > Also it would be fine to be able to load static extensions to separate > connections: > something like: > int sqlite3_load_static_extension(sqlite3 *db, void (*xEntryPoint)(void)); > > If you would need any additional information about issue, > please contact me at: paulo...@gisoft.cz > > Additional info: > SQLite Version: 3.7.14.1, Source ID: 2012-10-04 19:37:12 > 091570e46d04e84b67228e0bdbcd6e1fb60c6bdb > > Perhaps irrelevant in this case: > OS: Windows Vista > Main Application: Bentley MicroStation V8i > Plugins: MDL (MicroStation Development Library) > > Regards, > Dusan Paulovic > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Array Accessing in SQLite3
They are all the same to SQLite and declare a column with TEXT affinity. -Ursprüngliche Nachricht- Von: techi eth [mailto:techi...@gmail.com] Gesendet: Montag, 15. Juli 2013 06:35 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Array Accessing in SQLite3 I found so many Type name to be used while creating table for array type.What is significance of size under bracket ?.What is differences among all ? http://www.sqlite.org/datatype3.html --- CHARACTER(20) VARCHAR(255) VARYING CHARACTER(255) NCHAR(55) NATIVE CHARACTER(70) NVARCHAR(100) TEXT --- Thanks On Fri, Jul 12, 2013 at 1:07 PM, Hick Gunter wrote: > Please read up on SQL, there are numerous tutorials available online. > > There is no "array" in SQL other than that a table may be considered > as an array of records. > > Your example creates a table with two fields named 'test' and 'name' > and with declared datatypes of '10' and '50' respectively. > > -Ursprüngliche Nachricht- > Von: techi eth [mailto:techi...@gmail.com] > Gesendet: Freitag, 12. Juli 2013 09:13 > An: General Discussion of SQLite Database > Betreff: [sqlite] Array Accessing in SQLite3 > > I have query regarding accessing single & multidimensional array in > SQLite3. > > Example: I have created table with (test [10] INTEGER, name [50] TEXT). > > How do I pass a value to insert each element of array? > > How do I read back? (I am using callback function for read back) > > > Please cover answer by considering multidimensional array case as well. > > Thanks-- > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > -- > > Gunter Hick > Software Engineer > Scientific Games International GmbH > Klitschgasse 2 - 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien > Tel: +43 1 80100 0 > E-Mail: h...@scigames.at > > This e-mail is confidential and may well also be legally privileged. > If you have received it in error, you are on notice as to its status > and accordingly please notify us immediately by reply e-mail and then > delete this message from your system. Please do not copy it or use it > for any purposes, or disclose its contents to any person as to do so > could be a breach of confidence. Thank you for your cooperation. > ___ > 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 -- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users