Re: [sqlite] extracting domain names from website addresses efficiently
Hello Peter, Monday, December 11, 2017, 9:16:27 AM, you wrote: PDS> This seems like a job for regular expressions. PDS> PDS> ___ PDS> sqlite-users mailing list PDS> sqlite-users@mailinglists.sqlite.org PDS> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users I'm with you. This seems like using the wrong tool for the job. Sure it can work but it's probably not the most efficient way. -- Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Seeking advice on improving batch sql update performance on zipvfs
Hello Yue, Is it a phone or tablet? I'd wonder if the CPU is going into thermal throttle mode. As I understand it most phones and tablets can't keep the CPU's running full speed without eventually throttling the CPU. To me the fact uncompressed, while moving much more data, is more than 2 times faster suggests the bottleneck isn't the fileIO. Thursday, September 7, 2017, 3:13:04 PM, you wrote: YW> Hello, YW> As mentioned in the subject, our goal is to improve performance regarding YW> to batch sql updates. YW> The update sql as follow, >> UPDATE ITEM SET FIELD4 =? WHERE DS=? YW> We run 100,000 updates in a single transaction. The zipvfs version takes YW> about 20 min while uncompressed version takes about 7 min. YW> I've also attached results of "EXPLAIN UPDATE ITEM SET FIELD4 = 10 WHERE DS YW> = 15;". YW> Any suggestions is very helpful. YW> Some background: YW> We have an app running on Android 4.2 using zipvfs. YW> The page size is 4096 YW> The cache size is - 2000 YW> Table schema for ITEM >> CREATE TABLE ITEM ( >> FIELD0 NUMERIC, >> FIELD1 NUMERIC, >> DS TEXT, >> FIELD2 TEXT, >> FIELD3 TEXT, >> FIELD4 NUMERIC, >> FIELD5 NUMERIC, >> FIELD6 NUMERIC, >> FIELD7 NUMERIC, >> FIELD8 NUMERIC, >> FIELD9 NUMERIC, >> FIELD10 NUMERIC, >> FIELD11 TEXT); YW> The third column: "DS" is what we query by almost all the time. We also YW> created index: >> CREATE INDEX DS_INDEX ON ITEM(DS); YW> There are 30 million records. Zipvfs size of 471mb vs uncompressed db (the YW> one shipped with Android 4.2) size of 1.39gb. YW> Zipvfs db using zlib and aes128, which are default. YW> Thanks -- Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to get row number of an ID in sorted results
Hello Mike, What I do is I note the currently selected ID prior to the sort, then sort using a query and return the ID's in the new sorted order. Then in the program I search for the ID and display the selected line again in my list control. Basically, I maintain in memory a complete list of the ID's in a vector in the currently selected order. The vector is also used when it's time to resolve because I have a 1:1 mapping between the index of the item and the ID of the item. The demand-load happens for the visible page so, when I navigate to the selected item, that page resolves against the DB. This only works because I control the program. It probably wouldn't work for a web page or something like that. Sunday, November 13, 2016, 12:29:12 PM, you wrote: MK> I have a table (test) with 3 columns (ID - auto incrementing, Value1 - Text MK> and Value2 - Text). After doing an order by in a select query I'd like to MK> know the row number that contains a particular ID. MK> (The real world use is this: I have an application which displays paged MK> lists of results. If you change the sort order I'd like the application to MK> go to the page that contains the current selected ID. To do this I need to MK> know what is the position in the sorted list of the ID). MK> So, after some experimentation, I'm using a temporary table to hold the MK> ordered IDs and then getting the rowid of the row with the ID I want. (in MK> this example the list is sorted by Value2 and the selected ID=1): MK> create temporary table TempIDs as select ID from Test order by Value2; MK> select rowid from TempIDs where ID = 1; MK> drop Table TempIDs; MK> I know SQL light doesn't support rownum (like Oracle) but is there any way MK> I could simplify this using a CTE so I don't have to create the temp table? MK> All I really want is the number of the row with the ID in the sorted list. MK> Cheers, MK> Reply MK> Forward MK> ___ MK> sqlite-users mailing list MK> sqlite-users@mailinglists.sqlite.org MK> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Protecting databases
Hello Damien, I use the "encrypt specific fields" method of encrypting data in sqlite. For you to use that with Basic you'd probably have to find or write a wrapper DLL which handled the field encryption. So your basic would only see unencrypted data after it sent a password to the DLL. I know this probably makes it a non-starter for you. C Saturday, October 8, 2016, 1:46:02 AM, you wrote: DSL> Hi there, DSL> My name is Damien Lindley, and I am, among other things, an DSL> independent, hobbiest programmer. I have been blind since birth DSL> and thus all my computer work relies on screenreader software and keyboard. DSL> I have only just come through the brink of scripting into DSL> compiled programming and so I guess I am still a beginner in many DSL> respects. However I don’t work in C or C++, so most of my DSL> programming, if using a library, relies on precompiled static or DSL> dynamic libraries. Or of course libraries that are written or DSL> converted specifically for the language I work in (FreeBASIC). DSL> Recently, I decided I needed to create a piece of software that DSL> could manage family trees, since there seems to be a lack of DSL> screenreader accessible genealogy managers out there. I was DSL> advised the best way to do this is to use a database engine. I DSL> was also informed that SQLite is always a good choice for databases. DSL> I must admit, I have never worked with databases before and so DSL> now I am in the process of learning SQL. However looking at the DSL> programming API for SQLite I cannot see any means of password DSL> protecting the database without either buying a commercial DSL> extension to do this, or recompiling SQLite with the DSL> authentication extension. Due to financial constraints and DSL> unfamiliarity with compiling in C both of these are not an option DSL> for me. Also I need a secure way to do this, as I think I read DSL> that the SQLite version simply uses a table to store the user DSL> data, which of course can be read and accessed elsewhere. DSL> Are there any other options available for doing this? DSL> Any help appreciated. DSL> Thanks. DSL> Damien. DSL> ___ DSL> sqlite-users mailing list DSL> sqlite-users@mailinglists.sqlite.org DSL> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using too much memory to execute an update query
Hello Domingo,
DAD>db.exec_dml("PRAGMA synchronous = 0;");
DAD>db.exec_dml("PRAGMA journal_mode = WAL");
DAD>//db.exec_dml("PRAGMA journal_mode = MEMORY;");
DAD>//db.exec_dml("PRAGMA journal_mode = OFF;");
DAD>//db.exec_dml("PRAGMA locking_mode = EXCLUSIVE;");
DAD>db.exec_dml("PRAGMA temp_store = MEMORY;");
DAD>//db.exec_dml("PRAGMA threads = 4;");
Here are some of the pragma's you reported this morning. Any place
you have "MEMORY" can consume most of your memory when doing a bunch of
transactions.
I'm going to guess you're still using some of these "use memory"
pragma's.
Sunday, October 2, 2016, 4:35:23 PM, you wrote:
DAD> Hello !
DAD> I'm still fighting to use sqlite with a 20GB database and now I'm trying
DAD> to update a table with 980M rows but sqlite is eating all my memory
DAD> (2GB) and making blocking my computer (too much swap).
DAD> I'm in doubt if sqlite can really manage databases above 1GB.
DAD> Have someone any experience with big databases ?
DAD> Cheers !
DAD> The query:
DAD> --explain query plan
DAD> update bolsas_familia set favorecido_id=(select id from favorecidos
DAD> where nis=favorecido_id);
DAD> Query plan:
DAD> selectid|order|from|detail
DAD> 0|0|0|SCAN TABLE bolsas_familia
DAD> 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 0
DAD> 0|0|0|SEARCH TABLE favorecidos USING COVERING INDEX favorecidos_nis_idx
DAD> (nis=?)
DAD> The schema is:
DAD> CREATE TABLE bolsas_familia(
DAD> id integer primary key,
DAD> favorecido_id integer not null,
DAD> valor_parcela numeric,
DAD> mes_competencia integer
DAD> ); //980M rows
DAD> CREATE TABLE "favorecidos"(
DAD> id integer primary key,
DAD> nis integer,
DAD> nome varchar collate nocase_slna,
DAD> municipio_id integer,
DAD> programa_id integer not null,
DAD> fonte_finalidade_id integer not null,
DAD> first_ano_mes integer
DAD> ); //14M rows
DAD> CREATE UNIQUE INDEX favorecidos_nis_idx ON favorecidos(nis);
DAD> Cheers !
DAD> ___
DAD> sqlite-users mailing list
DAD> sqlite-users@mailinglists.sqlite.org
DAD> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
--
Tegmailto:t...@djii.com
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow Sqlite3 Select Query while Insert/Update/Delete has no issue
ring.str().c_str(),strlen(MyString.str().c_str()),_bid_stmt,NULL); S> Here Side = 66 stands for price of Buyers and Price desc says price S> sorted in decreasing order. S> One more is there which has Side = 83 which stands for Sellers and Price S> Asc says price sorted in ascending mode S> If Insert/Modify data comes for a token then one of the query is raised S> either with Side = 66 or Side = 83 depending on the Side received in the S> incoming data Packet. S> If Delete packet is received then Both of the query has to be released S> back to back. S> If I run my executable with Insert/Replace/delete every thing goes well S> i.e : No packet drop, but the moment I start using Select query either S> single after Insert/Replace or both after Delete, packet drop starts. S> You can find the same discussion on stackoverflow S> <http://stackoverflow.com/questions/39438156/slow-sqlite3-select-query-while-insert-update-delete-has-no-issue> S> but I am not able exactly understand the implementation. S> I hope I have been able to describe my whole situation. Running Select S> query is a must for me. Please help. S> Regards S> Shailendra Kumar S> +91-9015602289 S> ___ S> sqlite-users mailing list S> sqlite-users@mailinglists.sqlite.org S> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] newbie has waited days for a DB build to complete. what's up with this.
Hello Kevin, I'd write a utility to do it instead of using the command line tool then add logging to the program in order to note progress. I like the idea of chopping it into smaller parts too. "ON CONFLICT ROLLBACK" You're doing one large transaction and if it rolls back it'll have to undo everything right? I wonder if that's what you're seeing here. You might want to try "IGNORE" to see if you can even run through all the data. Writing a utility for this would let you manage the rollbacks too. C Wednesday, August 3, 2016, 11:00:12 PM, you wrote: KOG> I'm working on a hobby project, but the data has gotten a bit out of hand. KOG> I thought I'd put it in a real database rather than flat ASCII files. KOG> I've got a problem set of about 1 billion game KOG> positions and 187GB to work KOG> on (no, I won't have to solve them all) that took about 4 hours for a KOG> generator program just to write. I wrote code to turn them into something KOG> SQLite could import. Actually, it's import, build a non-primary index, and KOG> alter table to add a column, all in sqlite3. KOG> The database was growing for about 1-1/2 days. Then its journal KOG> disappeared, the file size dropped to zero, but sqlite3 is still running KOG> 100% CPU time, now for a total of 3800+ minutes (63+ hours). The database KOG> is still locked, but I have no idea what sqlite3 is doing, or if it will KOG> ever stop. All partitions still have lots of space left (most of this is KOG> running in a RAID partition of 11 TiB). Here's what I gave to sqlite3 on KOG> my Linux system: KOG> time sqlite3 qubic.db < BEGIN EXCLUSIVE TRANSACTION; KOG> DROP TABLE IF EXISTS qmoves; KOG> CREATE TABLE qmoves ( KOG> qfrom CHAR(64), KOG> qmove INT, KOG> qto CHAR(64), KOG> qweight INT, KOG> PRIMARY KEY (qfrom, qmove) ON CONFLICT ROLLBACK KOG> ); KOG> CREATE INDEX IF NOT EXISTS qmoves_by_dest ON qmoves ( KOG> qto, KOG> qweight KOG> ); KOG> CREATE TABLE IF NOT EXISTS qposn ( KOG> qposn CHAR(64) PRIMARY KEY ON CONFLICT ROLLBACK, KOG> qmaxval INT, KOG> qmove INT, KOG> qminval INT, KOG> qstatus INT KOG> ); KOG> .separator " " KOG> .import am.all qmoves KOG> ALTER TABLE qmoves ADD COLUMN qstatus INT DEFAULT NULL; KOG> .schema KOG> COMMIT TRANSACTION; KOG> EOF KOG> Any clues, hints, or advice? -- Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Using SQLite for storing photo's
Hello Cecil, I have multiple 30-40 GB Sqlite DB's which contain images as blobs. I back them up by copying them to my backup drive. Unless your application runs 24x7, I see no reason to do anything complicated with the backup. My image blobs are in a table which contains an image and a rowid to ID the image. The meta data is all in a different table. It would be simply to split this out into multiple DB's but from a performance perspective, I don't see the point. It's plenty fast enough for my usage. I'm not using the Blob IO routines. I just copy them into memory in one go when I need them. If my machine was slower I might do it differently. As long as I can retrieve and view the images as fast as I can page them, I'm satisfied. I do have export routines to export them as images or CBR/CBZ files. I like them all in one file too. C Thursday, April 14, 2016, 4:18:58 PM, you wrote: CW> 2016-04-14 22:07 GMT+02:00 Clemens Ladisch : >> Cecil Westerhof wrote: >> > what is the best way to make a backup? >> >> With the backup API: <http://www.sqlite.org/backup.html>. >> (Also available as .backup in the shell.) >> CW> ?I should be more precise in my communication. :'-( CW> I prefer to make my backup with .dump. Then I have just a text based CW> backup. This works fine for the regular data, but not for photos: they can CW> be 13 MB big. (And I am not even using raw. When I am going to do that, CW> they will become much bigger.) So I would like to have my photos split out CW> when making the dump. CW> But maybe it will be better to use external blobs. CW> Something to think about. -- Tegmailto:Teg at djii.com
[sqlite] Encrypt the SQL query
Hello Admin, I have all my important strings encrypted using AES. They get decrypted at runtime. Well, they get decrypted at run time just when used and then get over-written by other decrypted strings. It's a circular queue of strings. I just run a post process step on one of my libs to actually encrypt the strings. It happens automatically when I build the lib. Someone with a debugger could find then eventually but no more than 5 decrypted strings are in active memory at a time. I should probably add a function to flush them too... Thursday, February 25, 2016, 1:01:31 AM, you wrote: asn> Hi, asn> In my C++ program, I will invoke SQLite to execute SQL queries. But these asn> queries are just stored as normal string constants in C++ so it is easy to asn> be decoded via reverse engineering method. Does SQLite provide a good way to asn> encrypt the SQL query strings while does not affect the performance when asn> executing the queries? asn> Thanks asn> ___ asn> sqlite-users mailing list asn> sqlite-users at mailinglists.sqlite.org asn> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- Tegmailto:Teg at djii.com
[sqlite] Can SQLite be used from DLL
Hello Igor, I use Sqlite through a DLL interface. I mean I have another DLL that includes Sqlite static linked inside it. All opening, closing and access to Sqlite takes place inside the context of the DLL. The application never makes Sqlite calls directly. Instead it uses higher level functions inside the DLL which talk to the Database through Sqlite. I've not attempted to do what you're doing exactly. I don't like sharing memory across the DLL boundary because I mostly use static linkage. It causes issues. I'll use allocated memory as handles to a class inside the DLL but to the caller it's just a black box. The caller doesn't know what the handle contains (like a file handle). Thursday, February 25, 2016, 10:31:49 AM, you wrote: IK> Hi, IK> I'm trying to design/make a program where I will use SQLite from the DLL. IK> What I mean is I will establish a connection inside a DLL but disconnect from IK> the database in my main application. IK> It turns out that doing so I am getting the crash when I try to disconnect from IK> the database file. IK> Connecting to the DB and issuing the query works OK and the data is retrieved. IK> I put up some small demo if you need a code to look at. IK> Thank you. IK> ___ IK> sqlite-users mailing list IK> sqlite-users at mailinglists.sqlite.org IK> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- Tegmailto:Teg at djii.com
[sqlite] Storing Images in Blobs - Best Practices
Hello Joe, I'm inclined to think the problem is in the client side too. I store many GB's of images as blobs and have no issues. It sounds like you're re-using a buffer before the insert has completed. I'd wonder if it's related to how you're binding the parameters to the update. Assuming you're using parameters for the insert and update. C Wednesday, February 17, 2016, 5:23:04 PM, you wrote: RJ> I've wanted to use SQLite Blobs for storing images for some time, RJ> and a new update to an iOS app gave me the opportunity - our RJ> Corporate Directory mobile app. I had considered it a few years RJ> before but after reading some posts that suggested a better way RJ> was to store links in SQLite and the images as normal files, I RJ> avoided the process. Now, I'm doing it in Blobs. RJ> However, there have been some hiccups - occasional corrupted data RJ> - so I'd like to get this group's collective thought on best practices. RJ> I will outline the process without code, which I'll post later if there is a request to do so. RJ> 1. The database table has four fields: Employee GUID RJ> (varchar), EmployeePic (Blob), isPicAvail (Bool) and picDate (int). RJ> 2. When a employee is selected from a search list, a new RJ> record is added to the table with only the GUID and a NO for the isPicAvail. RJ> 3. If there is connectivity to our intranet via VPN, we send RJ> out a web service call to get the employee's photo from RJ> SharePoint, async. If no connectivity, we will batch it when connected. RJ> 4. When a image comes back as a mime string, it is converted RJ> to NSData and stored using UPDATE into the Blob field, along with RJ> the unix time in picDate, and we change the isPicAvail to YES. RJ> 5. Whenever the app is restarted with connectivity, I do a RJ> refresh all photos, which walks the table and fetches images for RJ> every record, regardless of whether there is one already stored. I RJ> use the same UPDATE call, as above in 4. I don't delete the RJ> previous Blob, just assuming that SQLite will take care of those RJ> details. NOTE: this process will be optimized later, using RJ> modified dates to fetch photos only when needed. RJ> The process works well except that occasionally two images end up RJ> getting corrupted. Not just one, it always happens in pairs. I get RJ> the upper part of both images, with just the lower part showing garbage. RJ> The questions: RJ> * Is this general approach wise? RJ> * Should I clear the old Blob before rewriting the new image, RJ> or should I simply delete the old rec and write a new one? RJ> * Should I store the mime string coming back in the JSON RJ> return rather than converting the string to an image, and then RJ> converting it every time we need to display the image. RJ> * Should I simply store a string link to the image and load that whenever it is needed. RJ> Any thoughts are gratefully accepted. RJ> RJ> Joseph Rooney RJ> Team Lead - Mobile Development RJ> CommScope, Inc. RJ> Hickory, NC RJ> 828-315-2920 Ext: 52920 RJ> ___ RJ> sqlite-users mailing list RJ> sqlite-users at mailinglists.sqlite.org RJ> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- Tegmailto:Teg at djii.com
[sqlite] problem on Windows using filenames with non-ASCII characters
Hello Holger, The question is how does Sqlite.exe see the passed in filename? One thing you can do is use "procmon" and watch the filename of the file Sqlite.exe is trying to open. That'll probably give you a clue as to where the conversion is going wrong. This is what procmon told me: 11:30:46.3770663 PM sqlite3.exe 1556QueryOpen W:\Test.db3 NAME NOT FOUND 11:30:46.3771904PMsqlite3.exe1556 CreateFile C:\Users\Bla\.sqlitercNAME NOT FOUND Desired Access: Generic Read, Disposition: Open,Options: Synchronous IO Non-Alert, Non-Directory File, Attributes: N, ShareMode: Read, Write, AllocationSize: n/a 11:35:09.0408960 PM sqlite3.exe 7272QueryOpen W:\Dat?\Test.db3 NAME NOT FOUND 11:35:09.0410219 PM sqlite3.exe 7272CreateFile C:\Users\Bla\.sqlitercNAME NOT FOUND Desired Access: Generic Read, Disposition: Open, Options: Synchronous IO Non-Alert, Non-Directory File, Attributes: N, ShareMode: Read, Write, AllocationSize: n/a C Sunday, February 7, 2016, 2:09:19 PM, you wrote: HJ> Hello! HJ> When trying to open a db file with a name containing non-ASCII HJ> characters on Windows, I get an error that the file couldn't be found. HJ> Actually, the error message doesn't contain the character itself, HJ> because it gets changed by the sqlite3.exe program. HJ> The file name I tried contains the letter '?', but umlauts like ?? HJ> cause the same trouble. HJ> On Linux everything is fine. Since other programs, like tclsh or wish HJ> from the Tcl/Tk suite, have not problem, I wonder whether sqlite3.exe HJ> handles the file name correctly. HJ> Regards, HJ> Holger -- Tegmailto:Teg at djii.com
[sqlite] SQLite crashing
Hello Igor, I'm probably go the opposite direction and make sure everything is built /MDd or /MD. Basically dynamic link the whole thing. It means all of your projects need to be rebuilt using the same RTL though. You just don't want 1/2 to be /MT and the other 1/2 to be /MD. C Thursday, January 28, 2016, 11:25:10 PM, you wrote: IK> Hi, ALL, IK> On Tue, Jan 26, 2016 at 3:08 PM, Clemens Ladisch wrote: >> Igor Korot wrote: >>> sqlite.dll!sqlite3_mutex_enter(sqlite3_mutex * p) Line 19996 + 0xc >>> bytesC >>> sqlite.dll!sqlite3Close(sqlite3 * db, int forceZombie) Line 726 + 0xc >>> bytesC >>> sqlite.dll!sqlite3_close(sqlite3 * db) Line 772 + 0xe bytesC >>> sqlite.dll!SQLiteDatabase::Disconnect(...) Line 51 + 0xc bytesC++ >>> dialogs.dll!DisconnectFromDb(Database * db) Line 108 + 0x13 bytes >>> C++ >>> docview.exe!MainFrame::~MainFrame() Line 73 + 0xf bytesC++ >>> docview.exe!MainFrame::`scalar deleting destructor'() + 0x16 bytes >>> C++ >>> docview.exe!wxAppConsoleBase::DeletePendingObjects() Line 637 + 0x23 >>> bytesC++ >>> docview.exe!wxAppConsoleBase::ProcessIdle() Line 445C++ >> >> This looks OK. >> >> Are you ever calling sqlite3_shutdown()? >> Are you calling sqlite3_close() from more than one place, or more than once? >> (Add logging to find out.) IK> I tried to recompile the project with the /MTd, but I got this: 1>>odbccp32.lib(dllload.obj) : error LNK2019: unresolved external IK> symbol __imp___vsnprintf referenced in function IK> _StringVPrintfWorkerA at 20 IK> And here is my Linker Project Settings: IK> odbccp32.lib;kernel32.lib;user32.lib;gdi32.lib;comdlg32.lib;winspool.lib;winmm.l? IK> ib;shell32.lib;shlwapi.lib;comctl32.lib;ole32.lib;oleaut32.lib;uuid.lib;rpcrt4.li? IK> b;advapi32.lib;version.lib;wsock32.lib;wininet.lib;%(AdditionalDependencies) IK> Is there an easy way to fix this? IK> Thank you. >> >> >> Regards, >> Clemens >> ___ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users IK> ___ IK> sqlite-users mailing list IK> sqlite-users at mailinglists.sqlite.org IK> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- Tegmailto:Teg at djii.com
[sqlite] SQLite crashing
Hello Igor,
https://msdn.microsoft.com/en-us/library/ms235460.aspx?f=255=-2147217396
This discussed in more details what I originally posted about.
>>A related problem can occur when you allocate memory (either
>>explicitly with new or malloc, or implicitly with strdup,
>>strstreambuf::str, and so on) and then pass a pointer across a DLL
>>boundary to be freed. This can cause a memory access violation or heap
>>corruption if the DLL and its users use different copies of the CRT
>>libraries.
Are you sure the DLL and the main application are built with the same
RTL? This is almost certainly not an Sqlite problem. It's probably an
Sqlite mis-use problem.
How are you compiling the code? What run time library are you using? I
use /MT and /MTd for mostly static linking. Then I "getprocaddress"
on any DLL's I load. So, in my case each DLL has its own copy of the
RTL.I'm not clear how using /MD or /MDd impacts this.
I don't use Sqlite the way you're trying to. I static link a copy and
the wrapper into each application so there are no DLL boundaries to
contend with.
Monday, January 25, 2016, 11:30:00 PM, you wrote:
IK> Teg,
IK> On Mon, Jan 25, 2016 at 9:25 PM, Igor Korot wrote:
>> Hi, Teg,
>>
>> On Mon, Jan 25, 2016 at 5:21 PM, Igor Korot wrote:
>>> Hi, Teg,
>>>
>>> On Mon, Jan 25, 2016 at 4:51 PM, Teg wrote:
>>>> Hello Igor,
>>>>
>>>> Then I'd note the address of the object (make a copy of the pointer
>>>> right as it's allocated) and then verify that the address you're
>>>> deleting is the same as the address that was allocated. I've verify
>>>> that the correct calling convention is being used throughout too.
>>>
>>> Yes, pointers (addresses) are the same.
>>>
>>>>
>>>>
>>>> I'd single step into the destructor and see what's actually happening to
>>>> make it crash. I'd pay attention to the "this" pointer and see if it
>>>> makes sense.
>>
>> I just ran the program under the debugger step-by-step and everything
>> looks "normal".
>> All addresses are the same. and the opening db executes successfully.
>>
>> The stack trace look like this:
>>
>>>sqlite.dll!sqlite3_mutex_enter(sqlite3_mutex * p) Line 19996 + 0xc
>>> bytesC
>> sqlite.dll!sqlite3Close(sqlite3 * db, int forceZombie) Line 726
>> + 0xc bytesC
>> sqlite.dll!sqlite3_close(sqlite3 * db) Line 772 + 0xe bytesC
>>
>> sqlite.dll!SQLiteDatabase::Disconnect(std::vector<std::basic_string<wchar_t,std::char_traits,std::allocator
>>>,std::allocator<std::basic_string<wchar_t,std::char_traits,std::allocator
>>> > > & errorMsg) Line 49 + 0xc bytesC++
>> dialogs.dll!DisconnectFromDb(Database * db) Line 108 + 0x13 bytes
>> C++
>> docview.exe!MainFrame::~MainFrame() Line 73 + 0xf bytesC++
>>
>> The DisconnectFromDb() function is a function from DLL. It calls
>> SQLiteDatabase::Disconnect().
>> Then everything is sqlite.
>>
>> Any idea of what is going on?
IK> [code]
IK> SQLITE_API void SQLITE_STDCALL sqlite3_mutex_enter(sqlite3_mutex *p){
IK> if( p ){
IK> assert( sqlite3GlobalConfig.mutex.xMutexEnter );// This is the
IK> line that fails
IK> sqlite3GlobalConfig.mutex.xMutexEnter(p);
IK> }
IK> }
IK> [/code]
IK> Thank you.
>>
>> Thank you.
>>
>>>>
>>>> Assuming you're using visual studio and have the source to the wrapper
>>>> if should be easy to simply step in and see exactly what line of code
>>>> makes it crash.
>>>
>>> Yes, MSVC 2010 and the code is mine. ;-)
>>>
>>> Thank you.
>>>>
>>>> C
>>>>
>>>>
>>>> Monday, January 25, 2016, 1:00:53 PM, you wrote:
>>>>
>>>> IK> Hi, Teg,
>>>>
>>>> IK> On Mon, Jan 25, 2016 at 11:31 AM, Teg wrote:
>>>>>> Hello Igor,
>>>>>>
>>>>>> MainFrame::~MainFrame()
>>>>>> {
>>>>>> delete m_db; // this is where the crash happens
>>>>>> }
>>>>>>
>>>>>> I suspect you need to add a "Close" or destroy function to the DLL and
>>>>>> pass the handle back to the DLL to let it get deleted in the DLL
>>>>>> context and not in the context of the caller.
>>>>
>>>> IK> Does not make a difference.
>>>> IK> I
[sqlite] SQLite crashing
Hello Igor,
Then I'd note the address of the object (make a copy of the pointer
right as it's allocated) and then verify that the address you're
deleting is the same as the address that was allocated. I've verify
that the correct calling convention is being used throughout too.
I'd single step into the destructor and see what's actually happening to
make it crash. I'd pay attention to the "this" pointer and see if it
makes sense.
Assuming you're using visual studio and have the source to the wrapper
if should be easy to simply step in and see exactly what line of code
makes it crash.
C
Monday, January 25, 2016, 1:00:53 PM, you wrote:
IK> Hi, Teg,
IK> On Mon, Jan 25, 2016 at 11:31 AM, Teg wrote:
>> Hello Igor,
>>
>> MainFrame::~MainFrame()
>> {
>> delete m_db; // this is where the crash happens
>> }
>>
>> I suspect you need to add a "Close" or destroy function to the DLL and
>> pass the handle back to the DLL to let it get deleted in the DLL
>> context and not in the context of the caller.
IK> Does not make a difference.
IK> I added that function and call it from the MainFrame destructor.
IK> It still crashed.
IK> Thank you.
>>
>>
>> extern "C" __declspec(dllexport) void DestroyObject(Database *db)
>> {
>> delete db;
>> }
>>
>> It was my impression that each DLL got it's own heap so, memory
>> allocated inside the DLL needs to be free'd inside the DLL. I use
>> Sqlite in a static lib in my applications.
>>
>> I treat memory allocated in DLL's as being owned by the DLL so, I
>> typically pass it back to the DLL to be cleaned up. It believe it
>> depends on what run time library you're using though. If you're using
>> an RTL where all the DLL's end up using a DLL supplied allocator, this
>> probably isn't an issue. I tend to dynamic load my DLL's so they don't
>> all use the same allocator.
>>
>> C
>>
>>
>>
>> Monday, January 25, 2016, 11:16:57 AM, you wrote:
>>
>> IK> Hi, Peter,
>>
>> IK> On Mon, Jan 25, 2016 at 10:50 AM, Peter Aronson
>> wrote:
>>>> Igor,
>>>>
>>>> You can't safely pass a SQLite handle between different SQL DLLs that way
>>>> if
>>>> they're both built with their own copy of the amalgamation (or link to
>>>> things built with different copies). SQLite uses a handful of global
>>>> variables, but each DLL has its own copy of each of these global variables
>>>> and they can and will have different values, which can mess things up. I
>>>> ran into a version of this problem when I tried to load a 2nd DLL built
>>>> with
>>>> its own copy of the sqlite3.c amalgamation. I fixed that by exposing the
>>>> SQLite3 entrypoints in the first DLL and linking the second DLL against it
>>>> so there was only one copy of the amalgamation used for that SQLite3
>>>> handle.
>>
>> IK> The SQLite is built only once and with just one version of the code.
>>
>> IK> Consider following pseudo-code:
>>
>> IK> In DLL:
>>
>> IK> BOOL APIENTRY DLLMain()
>> IK> {
>> IK> }
>>
>> IK> extern "C" __declspec(dllexport) Database *CreateObject(Database *db)
>> IK> {
>> IK> db = new SQLiteDatabase();
>> IK> db->Connect();
>> IK> return db;
>> IK> }
>>
>> IK> In the main application:
>>
>> IK> mainframe.h:
>>
>> IK> class MainFrame
>> IK> {
>> IK> public:
>> IK> MainFrame();
>> IK> ~MainFrame();
>> IK> void ConnectToDb();
>> IK> private:
>> IK> Database *m_db;
>> IK> };
>>
>> IK> mainframe.cpp:
>>
>> IK> void MainFrame::ConnectToDb()
>> IK> {
>> IK> Database *db = NULL;
>> IK> LoadLibrary();
>> IK> func = GetProcAddress();
>> IK> m_db = func( db );
>> IK> }
>>
>> IK> MainFrame::~MainFrame()
>> IK> {
>> IK> delete m_db; // this is where the crash happens
>> IK> }
>>
>> IK> The pointer address are the same in DLL and main application MainFrame
>> class.
>> IK> And as I said the crash occurs when it tries to acquire the mutex lock.
>>
>> IK> Thank you.
>>
>>>>
>>>> Peter
>>>>
>>>>
>>>>
>>>>
>>>> On 1/24/2016 10:18 PM, Igor Korot wrote:
>>>>&
[sqlite] SQLite crashing
Hello J, I don't typically use "new" in my C++ applications other than in startup to allocate things that last the life of the application. So, I don't have any issues with double-freeing. I never manually "new" objects after startup. Instead I use containers to contains my objects and leave the allocation and de-allocation to them. >>Are you sure you're not somehow double-freeing the sqlite handle? >>Especially at close I've seen exit() end up calling atexit() methods >>multiple times in some circumstances... "newed" objects never fires the destructor unless you manually destruct it or use an auto-pointer even when you exit. In fact I use this as a method to get around destructor order issues when my applications exit. I essentially just abandon the "new"ed startup objects. It also makes applications exit faster just to abandon memory and depend on the OS to reclaim all the memory.
[sqlite] SQLite crashing
Hello Igor,
MainFrame::~MainFrame()
{
delete m_db; // this is where the crash happens
}
I suspect you need to add a "Close" or destroy function to the DLL and
pass the handle back to the DLL to let it get deleted in the DLL
context and not in the context of the caller.
extern "C" __declspec(dllexport) void DestroyObject(Database *db)
{
delete db;
}
It was my impression that each DLL got it's own heap so, memory
allocated inside the DLL needs to be free'd inside the DLL. I use
Sqlite in a static lib in my applications.
I treat memory allocated in DLL's as being owned by the DLL so, I
typically pass it back to the DLL to be cleaned up. It believe it
depends on what run time library you're using though. If you're using
an RTL where all the DLL's end up using a DLL supplied allocator, this
probably isn't an issue. I tend to dynamic load my DLL's so they don't
all use the same allocator.
C
Monday, January 25, 2016, 11:16:57 AM, you wrote:
IK> Hi, Peter,
IK> On Mon, Jan 25, 2016 at 10:50 AM, Peter Aronson
wrote:
>> Igor,
>>
>> You can't safely pass a SQLite handle between different SQL DLLs that way if
>> they're both built with their own copy of the amalgamation (or link to
>> things built with different copies). SQLite uses a handful of global
>> variables, but each DLL has its own copy of each of these global variables
>> and they can and will have different values, which can mess things up. I
>> ran into a version of this problem when I tried to load a 2nd DLL built with
>> its own copy of the sqlite3.c amalgamation. I fixed that by exposing the
>> SQLite3 entrypoints in the first DLL and linking the second DLL against it
>> so there was only one copy of the amalgamation used for that SQLite3 handle.
IK> The SQLite is built only once and with just one version of the code.
IK> Consider following pseudo-code:
IK> In DLL:
IK> BOOL APIENTRY DLLMain()
IK> {
IK> }
IK> extern "C" __declspec(dllexport) Database *CreateObject(Database *db)
IK> {
IK> db = new SQLiteDatabase();
IK> db->Connect();
IK> return db;
IK> }
IK> In the main application:
IK> mainframe.h:
IK> class MainFrame
IK> {
IK> public:
IK> MainFrame();
IK> ~MainFrame();
IK> void ConnectToDb();
IK> private:
IK> Database *m_db;
IK> };
IK> mainframe.cpp:
IK> void MainFrame::ConnectToDb()
IK> {
IK> Database *db = NULL;
IK> LoadLibrary();
IK> func = GetProcAddress();
IK> m_db = func( db );
IK> }
IK> MainFrame::~MainFrame()
IK> {
IK> delete m_db; // this is where the crash happens
IK> }
IK> The pointer address are the same in DLL and main application MainFrame
class.
IK> And as I said the crash occurs when it tries to acquire the mutex lock.
IK> Thank you.
>>
>> Peter
>>
>>
>>
>>
>> On 1/24/2016 10:18 PM, Igor Korot wrote:
>>>
>>> Hi, ALL,
>>> I have a strange problem.
>>>
>>> I am trying to use sqlite in my program. It has a main application and
>>> couplef DLLs.
>>>
>>> I am getting the connection in one of the DLL, then the pointer is passed
>>> up
>>> to the main application.
>>>
>>> Upon exiting from the application I'm trying to close the connection and
>>> delete all the memory.
>>>
>>> Unfortunately upon exiting the application it crashes inside
>>> sqlite3_mutex_enter().
>>> The comment above the function says:
>>>
>>> [quote]
>>> /*
>>> ** Obtain the mutex p. If some other thread already has the mutex, block
>>> ** until it can be obtained.
>>> */
>>> [/quote]
>>>
>>> The DLL does not start any threads, in fact the application will be 1
>>> thread only.
>>> So is there some compile-time switch I should use to mitigate the issue?
>>>
>>> Moreover I don't understand why am I getting the assertion - there is no
>>> MT
>>> involved.
>>>
>>> Can someone shed some lights?
>>>
>>> Thank you.
>>> _______
>>> sqlite-users mailing list
>>> sqlite-users at mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
IK> ___
IK> sqlite-users mailing list
IK> sqlite-users at mailinglists.sqlite.org
IK> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
--
Tegmailto:Teg at djii.com
[sqlite] Performance problems on windows
Hello Jakub, Windows user here. I use Sqlite to contain some fairly bulky data but the tables that store this data are simple and the queries and indexes are also simple. Using a 300 meg test file, I fed data into fresh DB's using different versions of Sqlite including some of the ones you listed in your testing. In all cases the latest version was marginally faster. Say 3 seconds in a 75 second run. Since we don't use the same DB's, I'm not surprised I couldn't reproduce your results but, it does suggest that raw speed to disk is close between the versions. I tried without journal, with journal and WAL mode.WAL mode was slightly faster than journal mode. Not using a journal file at all knocked 18 seconds off a run. I was able to knock 10 seconds off the feed in all modes by increasing my Sqlite cache size to 10,000 pages. My users had reported that older versions were faster than new too but my testing suggests, even if it's true, the problem isn't Sqlite in my case. I see your new post about multiple threads. Typically my access pattern is one thread at a time so, again my testing probably doesn't apply to your particular case. C Wednesday, August 26, 2015, 7:51:08 AM, you wrote: JZ> Hi, JZ> also nope. I start with an empty (completely new) database. Then JZ> inserted 100K what we call "objects" and the same amount of JZ> "events". Inserting "events" takes place in one big commit. As JZ> soon as events are detected, the processing starts. I measure time JZ> since beginning of the first process till the end of the last. It JZ> is quite a lot of transactions, selects, and updates. Slightly less inserts and deletions. JZ> As a rule we do a VACUUM at each startup but clients expect to JZ> run this thing for months (if not years) without restart. JZ> -- JZ> Gruesse, JZ> Jakub JZ> -Original Message- JZ> From: sqlite-users-bounces at mailinglists.sqlite.org JZ> [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Rowan Worth JZ> Sent: Mittwoch, 26. August 2015 13:33 JZ> To: General Discussion of SQLite Database JZ> Subject: Re: [sqlite] Performance problems on windows JZ> What about fragmentation of the database itself? Does running JZ> VACUUM on the database affect performance? JZ> -Rowan JZ> On 26 August 2015 at 16:16, Jakub Zakrzewski wrote: >> Hi, >> >> nope. The defragmentation job runs every Wednsday night and the >> fragmentation is very low. >> >> PS: I'm putting together a small program to replay the queries I got >> from sqlite trace. If that will behave the same way as the original, >> I'll post it here. >> >> -- >> Gruesse, >> Jakub >> >> -Original Message- >> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto: >> sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Scott >> Robison >> Sent: Dienstag, 25. August 2015 18:55 >> To: General Discussion of SQLite Database >> Subject: Re: [sqlite] Performance problems on windows >> >> On Aug 25, 2015 5:21 AM, "Jakub Zakrzewski" wrote: >> > >> > Hi, >> > >> > The slowdown is confirmed by one of our customers. He uses Win 2008 >> Server x64. I'm testing on Win7 x64. >> >> This might be obvious in which case my apologies for bringing it up but: >> are these systems demonstrating slowness perhaps heavily fragmented? >> Either the database is fragmented, other non-SQLite files required by >> the system, or especially the master file table? I've seen truly >> abysmal performance on such systems myself, depending on IO patterns. >> >> > >> > I'm sure that you're testing each release carefully. We do too. And >> > we >> still have weird bugs ;) >> > >> > Thanks for interest. If I can provide any useful information, just >> > tell >> me, what you need. I cannot reveal the source code but some profiling >> results or SQL statements are not a secret. >> > >> > -- >> > Gruesse, >> > Jakub >> > >> > -Original Message- >> > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto: >> sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Richard >> Hipp >> > Sent: Dienstag, 25. August 2015 03:19 >> > To: General Discussion of SQLite Database >> > Subject: Re: [sqlite] Performance problems on windows >> > >> > I don't have any clues. >> > >> > While most of our work happens on Linux, we do measure performance >> > on >> Windows from one release to the next (see for example item 23-b on the >> most recent release check-list >> > https://www.sqlite.org/checklists/private/3081100/index#c23) and it >> > gets >> better from one release to the next. So I don't know why you are >> seeing a slowdown. >> > >> > How do you measure? >> > >> > On 8/24/15, Jakub Zakrzewski wrote: >> > > Hi All, >> > > >> > > I finally got a chance to upgrade SQLite for our product from >> > > ancient 3.7.16.2. Initial tests on Linux were very promising - >> > > ranging from 33% to even 300% (for one degenerated case) speed >> > > improvement. So far >>
[sqlite] SQLite3 to 64-bit DLL
Hello Papa, I just link it in as static library. Just include the code in each project or create a static lib project. It depends on the rest of your application though. If you're trying to static link the rest of the application then a static linked Sqlite makes sense. If you're dynamic linking the run time library, you might as well dynamic link Sqlite. I find that generally, statically linked applications are more reliable in Windows. The problem isn't windows so much as the poor state of most of my user's PC's. C Wednesday, August 26, 2015, 1:03:18 PM, you wrote: pac> On my Win10, I'd like to compile SQLite3 to create a 64-bit pac> DLL. The intention is to use this DLL instead of the pac> amalgamation, this way more than one C++ application can access pac> the same DLL. Please note that I am an aficionado, not an expert pac> when it comes to C++ and for SQL, well, this would be my second pac> attempt to get an application to use a database, as opposed to flat files. pac> How would you suggest I should proceed? is this even a good idea? pac> ___ pac> sqlite-users mailing list pac> sqlite-users at mailinglists.sqlite.org pac> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- Tegmailto:Teg at djii.com
[sqlite] Fwd: Problem with SQLite in C++. DB is BUSY (Multithread)
Hello ch,
I'd say yes it'll block. As to whether this is a problem I guess it
depends on how much writing you do. I break my writing down into
sections so, there's time in between transactions for readers to get
in and read.
WAL mode should work. It used to work for me a number of versions back.
If I was going to look deeper into this I'd use a fresh simple table
put the new DB into WAL mode and experiment with 2-3 processes trying
to access the fresh DB.
Tuesday, August 11, 2015, 8:08:39 PM, you wrote:
c> We didn't try journal mode but the WAL mode doesn't work for us
c> even if we set busy timeout handler to try to go across this.
c> Unfortunately, the busy timeout handler doesn't get called every
c> time and after that we still cannot get the file recovered
c> correctly. The issue is still shown in 3.8.8.3. Thanks for letting
c> me know journal mode working for you! However, is reading gonna
c> blocked by writing in journal mode? I thought that is the sqlite documents
mentioned.
>> Date: Tue, 11 Aug 2015 00:41:50 -0400
>> From: Teg at djii.com
>> To: chaihua_sina at hotmail.com
>> CC: sqlite-users at mailinglists.sqlite.org
>> Subject: Re: [sqlite] Fwd: Problem with SQLite in C++. DB is BUSY
>> (Multithread)
>>
>> Hello ch,
>>
>> I ran into a similar problem when I was trying to use WAL mode. I
>> ended up just turning it off. It seemed that once there was contention
>> for the file it never recovered. This was in windows. I didn't look
>> into it any deeper than this. The same code works fine in journal
>> mode. I haven't tried it again with the latest.I think I last
>> tested this in 3.8.8.2.
>>
>> There's typically not much more than 2-3 connections contending for
>> file access in my program. Most of the time it's only one connection
>> at a time.
>>
>> I assumed it was something I was doing because I wasn't reading any
>> other reports of it.
>>
>> C
>>
>>
>> Monday, August 10, 2015, 9:28:03 PM, you wrote:
>>
>> c> Hi,
>> c> I have similar problems. We have multiple connections to write to
>> c> the database and the updates are surrounded by create
>> c> savepoints. The issue is when two updates from different
>> c> connections try to modify the database, the second operation gonna
>> c> fail and it either returned database is busy or locked, while
>> c> another operation just keeps occupying the database lock until it
>> c> finishes. After the first operation finishes, the failed operation
>> c> (a short update) tries again with creating a new savepoint but it
>> c> still fails and after that any further operation cannot use the
>> c> database anymore and all of them fail with SQLITE_BUSY or database
>> c> locked. Is this because we don't create and handle savepoints
>> c> correct? One way to avoid the continuous abort is we use begin
>> c> transaction IMMEDIATE to wrap all our operations but it looks like
>> c> our solution is bandage. I don't know what will be the best
>> c> solution to solve our issue. If anyone could help answer it, I appreciate.
>> >> Date: Mon, 10 Aug 2015 15:40:17 +0300
>> >> From: dm3chip at gmail.com
>> >> To: sqlite-users at mailinglists.sqlite.org
>> >> Subject: [sqlite] Fwd: Problem with SQLite in C++. DB is BUSY
>> >> (Multithread)
>> >>
>> >> -- Forwarded message --
>> >> From: ??? ???
>> >> Date: 2015-08-10 0:37 GMT+03:00
>> >> Subject: Problem with SQLite in C++. DB is BUSY (Multithread)
>> >> To: sqlite-users at mailinglists.sqlite.org
>> >>
>> >>
>> >> Hello!
>> >> I've got a problem. I'm using sqlite3 in my C++ project. In the log I've
>> >> got error's *DB is locked error code 5*. As I know error code 5 means,
>> >> that
>> >> DB is busy. To solve this I started to use WAL journal mode. But it
>> >> doesn't
>> >> help.
>> >>
>> >> In my program I've got 2 connections to the same db. I use mutexes for
>> >> both
>> >> DB connections.
>> >> I'm opening connections with this code:
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >> *if (sqlite3_open_v2(db_path.c_str(), >db, SQLITE_OPEN_READWRITE |
>> >> SQLITE_OPEN_CREATE | SQLITE_OPEN_NOMUTEX, 0) ) {
>> >> LOG4CPLUS_FATAL(this->logger, "Ca
[sqlite] Fwd: Problem with SQLite in C++. DB is BUSY (Multithread)
Hello ch,
I ran into a similar problem when I was trying to use WAL mode. I
ended up just turning it off. It seemed that once there was contention
for the file it never recovered. This was in windows. I didn't look
into it any deeper than this. The same code works fine in journal
mode. I haven't tried it again with the latest.I think I last
tested this in 3.8.8.2.
There's typically not much more than 2-3 connections contending for
file access in my program. Most of the time it's only one connection
at a time.
I assumed it was something I was doing because I wasn't reading any
other reports of it.
C
Monday, August 10, 2015, 9:28:03 PM, you wrote:
c> Hi,
c> I have similar problems. We have multiple connections to write to
c> the database and the updates are surrounded by create
c> savepoints. The issue is when two updates from different
c> connections try to modify the database, the second operation gonna
c> fail and it either returned database is busy or locked, while
c> another operation just keeps occupying the database lock until it
c> finishes. After the first operation finishes, the failed operation
c> (a short update) tries again with creating a new savepoint but it
c> still fails and after that any further operation cannot use the
c> database anymore and all of them fail with SQLITE_BUSY or database
c> locked. Is this because we don't create and handle savepoints
c> correct? One way to avoid the continuous abort is we use begin
c> transaction IMMEDIATE to wrap all our operations but it looks like
c> our solution is bandage. I don't know what will be the best
c> solution to solve our issue. If anyone could help answer it, I appreciate.
>> Date: Mon, 10 Aug 2015 15:40:17 +0300
>> From: dm3chip at gmail.com
>> To: sqlite-users at mailinglists.sqlite.org
>> Subject: [sqlite] Fwd: Problem with SQLite in C++. DB is BUSY (Multithread)
>>
>> -- Forwarded message --
>> From: ??? ???
>> Date: 2015-08-10 0:37 GMT+03:00
>> Subject: Problem with SQLite in C++. DB is BUSY (Multithread)
>> To: sqlite-users at mailinglists.sqlite.org
>>
>>
>> Hello!
>> I've got a problem. I'm using sqlite3 in my C++ project. In the log I've
>> got error's *DB is locked error code 5*. As I know error code 5 means, that
>> DB is busy. To solve this I started to use WAL journal mode. But it doesn't
>> help.
>>
>> In my program I've got 2 connections to the same db. I use mutexes for both
>> DB connections.
>> I'm opening connections with this code:
>>
>>
>>
>>
>>
>>
>>
>>
>> *if (sqlite3_open_v2(db_path.c_str(), >db, SQLITE_OPEN_READWRITE |
>> SQLITE_OPEN_CREATE | SQLITE_OPEN_NOMUTEX, 0) ) {
>> LOG4CPLUS_FATAL(this->logger, "Can not open/create DB " <<
>> sqlite3_errmsg(db));sqlite3_close(this->db);}if (sqlite3_exec(this->db,
>> "PRAGMA journal_mode = WAL;", 0, 0, )) {
>> LOG4CPLUS_ERROR(this->logger, "SQL det journal mode error: " << err);
>> sqlite3_free(err);}*
>>
>> First connection is used for inserting data to the DB. It happens with 4
>> time every second.
>> Second connection is used for starting transaction, selecting, updating,
>> deleting data and committing. It happens every 5 seconds.
>>
>> I'm getting errors from the first connection.
>>
>> Please help me to solve this problem.
>>
>> P.S. Sorry for my bad English
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
c>
c> ___
c> sqlite-users mailing list
c> sqlite-users at mailinglists.sqlite.org
c> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
--
Tegmailto:Teg at djii.com
[sqlite] Could frequent 'database is locked' errors mean SQLite is not a good fit for my application?
Hello Kathleen, I'd set it to 5 minutes and see what happens. The timeout is worst case. It's not going to slow normal processing. I use infinite here. Is there ever a situation where you don't want to wait for it to finish? If you have hard real time requirements. I'd probably re-design it to be client server and have only one process actually talk to the DB and act like a server to the other processes. If you're going down that route though, a big database might be a better solution. C Thursday, July 2, 2015, 6:22:52 PM, you wrote: KA> Thanks so much for the help. I set the timeout to 5 seconds (at each KA> instance of a db open connection) and was still seeing the database locked KA> errors with some frequency (haven't quantified it yet), so I will try KA> extending it to 30 seconds, as you suggest, and hopefully that makes a KA> difference. KA> Failing that, PostgreSQL looks promising (thanks so much for the KA> suggestion!), so I will start trying to implement it. KA> Thanks Again! KA> On Thu, Jul 2, 2015 at 6:06 PM, Simon Slavin wrote: >> >> On 2 Jul 2015, at 3:59pm, Kathleen Alexander wrote: >> >> > I don't explicitly set sqlite3_busy_timeout(sqlite3*, int ms) so I will >> try >> > adding that after the database connection is opened to see if it limits >> > those errors. >> >> This will have an extreme effect. The default timeout for SQLite is not >> to use a timeout at all: any clash in access will result in immediate >> failure. Set your timeout to 30 seconds and see what happens. >> >> Worth noting that the timeout needs to be set by each application for each >> connection. If you set it for the first application which opens the >> database it won't automatically be applied by other connections. >> >> Simon. >> ___ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> KA> ___ KA> sqlite-users mailing list KA> sqlite-users at mailinglists.sqlite.org KA> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- Tegmailto:Teg at djii.com
[sqlite] Sqlite download page links AWOL
Hello Stephen, Yeah it's back to normal for me too. Basically the whole top section was missing. C Saturday, May 30, 2015, 8:55:18 PM, you wrote: SC> Something JUST changed within the last 60 seconds. I saw what the OP said, SC> but, it reverted back to the list of download links. Weird. SC> On Sat, May 30, 2015 at 8:44 PM, Jeff Steinkamp wrote: >> Yep, You will need to download the version best suited for your version >> of .NET. If you have a current version of Visual Studio, then I'd use >> NuGet to install the correct version and the associated libraries along >> with the correct interop dlls >> -- >> >> Jeff K. Steinkamp (N7YG) >> Tucson, AZ >> Scud Missile Coordinates >> N32.2319 W110.8477 >> >> < >> https://www.postbox-inc.com/?utm_source=email_medium=siglink_campaign=reach >> > >> >> >> Teg <mailto:Teg at djii.com> >>> Saturday, May 30, 2015 17:38via Postbox < >>> https://www.postbox-inc.com/?utm_source=email_medium=sumlink_campaign=reach >>> > >>> >>> Hello 'General, >>> >>> Used to be I could go to the download page: >>> >>> http://www.sqlite.org/download.html >>> >>> Click a link then download a zip file with the current version. >>> >>> What I see now is: >>> >>> Build Product Names >>> >>> ...followed by some text. >>> >>> Source Code Repositories >>> >>> ...with links to other pages that still don't have the ZIP files. Is >>> this the new way we have to download it? Digging through version >>> control? >>> >>> I've disabled add blockers and privacy controls. Looking at it with >>> the latest chrome. >>> >>> C >>> >>> >>> >>> ___ >>> sqlite-users mailing list >>> sqlite-users at mailinglists.sqlite.org >>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >>> >>> >>> >> ___ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> SC> ___ SC> sqlite-users mailing list SC> sqlite-users at mailinglists.sqlite.org SC> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- Tegmailto:Teg at djii.com
[sqlite] Sqlite download page links AWOL
Hello 'General, Used to be I could go to the download page: http://www.sqlite.org/download.html Click a link then download a zip file with the current version. What I see now is: Build Product Names ...followed by some text. Source Code Repositories ...with links to other pages that still don't have the ZIP files. Is this the new way we have to download it? Digging through version control? I've disabled add blockers and privacy controls. Looking at it with the latest chrome. C
[sqlite] sqlite3 and Unicode
Hello Peter, I use UTF-8 exclusively in the engine parts my windows applications. I just translate to UTF-16 for the GUI layer. In that way, no matter what platform I support, the DB's remain the same. C Tuesday, May 5, 2015, 3:29:54 PM, you wrote: PH> Thanks for all the replies. I'm still confused on this. It sounds like PH> maybe UTF8 is the answer on OSX but UTF-16LE on Windows. PH> Whatever the answer to that, it seems there isn't a way to control the PH> encoding within sqlite3 so I guess I won't worry about it. PH> Pete PH> lcSQL Software <http://www.lcsql.com> PH> Home of lcStackBrowser <http://www.lcsql.com/lcstackbrowser.html> and PH> SQLiteAdmin <http://www.lcsql.com/sqliteadmin.html> PH> ___ PH> sqlite-users mailing list PH> sqlite-users at mailinglists.sqlite.org PH> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- Tegmailto:Teg at djii.com
[sqlite] problem compiling std_call dll
Hello Bart,
dumpbin /all sqlite3.obj > sqlite.txt
You can then look at the sqlite.txt file with a text editor. I found
_sqlite3_db_filename listed in there a couple times. That tells you it
was probably compiled in. If you can't find it then that could be part
of the problem.
Then I might dump the obj for the Stdcall file and see how it mentions
the function too. I have no idea how long this function has been in
Sqlite. It makes me wonder if you're using an older version where the
function didn't exist.
As an experiment, I'd probably comment out the function in the
stdcall wrapper that calls _sqlite3_db_filename then rebuild. See if
the error goes away.
Sunday, March 1, 2015, 5:15:32 AM, you wrote:
BS> Iooked at that underscore (in _sqlite3_db_filename) as well, but that is
BS> just how VS presents the error, it didn't find a faulty underscore in the
BS> code. So, that is not it. As far as I can see the SQLite3 functions in this
BS> project are only in 3 places: sqlite3.h, SQLite3_StdCall.c and in the .def
BS> file. sqlite3_db_filename is in all 3 and all seems to be spelled right.
BS> Not sure if it gives any more information, but this is the build output:
1>>-- Build started: Project: SQLite3_StdCall, Configuration: Release
BS> Win32 --
1>> SQLite3_StdCall.c
1>> Creating library
BS>
C:\Users\Bart\SQLiteForExcel\Source\SQLite3_StdCall\Release\SQLite3_StdCall.lib
BS> and object
BS>
C:\Users\Bart\SQLiteForExcel\Source\SQLite3_StdCall\Release\SQLite3_StdCall.exp
1>>SQLite3_StdCall.obj : error LNK2001: unresolved external symbol
BS> _sqlite3_db_filename
1>>C:\Users\Bart\SQLiteForExcel\Source\SQLite3_StdCall\Release\SQLite3_StdCall.dll
BS> : fatal error LNK1120: 1 unresolved externals
BS> == Build: 0 succeeded, 1 failed, 0 up-to-date, 0 skipped ==
BS> RBS
BS> On Sun, Mar 1, 2015 at 7:10 AM, Amit Chaudhuri
BS> wrote:
>> Bart,
>>
>> you say "don't know anything about C or C++ or compiling C code"
>> Perhaps this will help.
>>
>> The error message (unresolved) external means that the linker is
>> trying to find something (_sqlite3_db_filename in this case) but it
>> cannot.
>>
>> Possible common causes include - it has been misspelt (I'm looking at
>> that underscore) or you have failed to point it at a library.
>>
>> I'm afraid I'm not fluent in the windows tools to track this down and
>> don't have a system I can follow along with. But on Linux one might
>> use the nm utility to examine a contents of library files for symbols.
>>
>> Regards,
>>
>> Amit
>>
>> On Sat, Feb 28, 2015 at 11:22 AM, Bart Smissaert
>> wrote:
>> > Trying to compile a std_call dll to be used with VB6 and VBA.
>> > Using instructions and files from this website:
>> > https://sqliteforexcel.codeplex.com/
>> > All works well and have added a few SQLite functions that weren't in the
>> > compiled dll
>> > as in the download section from that website.
>> > However having problems adding sqlite3_db_filename.
>> >
>> > I added this to sqlite3.h:
>> >
>> > const char *sqlite3_db_filename(sqlite3 *db, const char *zDbName);
>> >
>> > and this to SQLite3_StdCall.c:
>> >
>> > SQLITE3_STDCALL_API const char * __stdcall
>> > sqlite3_stdcall_db_filename(sqlite3 *pDb, const char *zDbName)
>> > {
>> > return sqlite3_db_filename(pDb, zDbName);
>> > }
>> >
>> > It doesn't compile and gives linking errors:
>> >
>> > Error 1 error LNK2001: unresolved external symbol
>> > _sqlite3_db_filename
>> > C:\Users\Bart\SQLiteForExcel\Source\SQLite3_StdCall\SQLite3_StdCall.obj
>> > SQLite3_StdCall
>> > Error 2 error LNK1120: 1 unresolved
>> > externals
>> C:\Users\Bart\SQLiteForExcel\Source\SQLite3_StdCall\Release\SQLite3_StdCall.dll
>> > 1 1 SQLite3_StdCall
>> >
>> > Strangely it doesn't point to a line in the block SQLITE3_STDCALL_API but
>> > points to line 1.
>> > This is at the start of SQLite3_StdCall.c:
>> >
>> > #include "stdafx.h"
>> > #include "SQLite3_StdCall.h"
>> >
>> > Compiling this on Windows 7 with Microsoft Visual Studio Professional
>> > Studio 2013.
>> > Admittedly, don't know anything about C or C++ or compiling C code, so I
>> am
>> > sure I must
>> > be overlooking something simple and silly, but just can't figure it out.
>> > Any ideas what could be the problem here?
>> >
>> >
>> > RBS
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users at mailinglists.sqlite.org
>> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
BS> ___
BS> sqlite-users mailing list
BS> sqlite-users at mailinglists.sqlite.org
BS> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
--
Tegmailto:Teg at djii.com
Re: [sqlite] Uncached performance
Hello Donald, DG> 1) Might there be further performance gains by placing the blobs in a DG> separate table? DG> E.g. DG> CREATE TABLE myBlobs ( DG> idINTEGER PRIMARY KEY REFERENCES global (id), DG> value BLOB DG> ); DG> Then (if you haven't rebuilt a new db, and perhaps only once ever) run DG> VACUUM and ANALYZE. This is how I do it. Seems to give me the best performance. I sometimes put them in their own DB file and then attach this file too. I have DB's with 30+ gigs of blobs in them. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Without ROWID and Autoincrement question
Hello General, I was going to say I thought the docs were ambiguous but, on my 3rd reading I see they aren't. Still, I think this statement could be re-written to reduce confusion (at least mine). https://www.sqlite.org/autoinc.html >>Because AUTOINCREMENT keyword changes the behavior of the ROWID >>selection algorithm, AUTOINCREMENT is not allowed on WITHOUT ROWID >>tables or on any table column other than INTEGER PRIMARY KEY. Any >>attempt to use AUTOINCREMENT on a WITHOUT ROWID table or on a column >>other than the INTEGER PRIMARY KEY column results in an error.-- I think this comment would be clearer if the "without rowid" part was pulled out and made absolute. >>The AUTOINCREMENT keyword is not allowed on WITHOUT ROWID tables. >>Because the AUTOINCREMENT keyword changes the behavior of the ROWID >>selection algorithm, Any attempt to use AUTOINCREMENT on a column >>other than the INTEGER PRIMARY KEY column results in an error.-- Best regards, Conrad mailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performing a SELECT on an enormous database...
Hello Ross, Is H11 indexed? 15 minutes suggests to me that it's doing a linear scan and you need an index. I have databases that big and performance is pretty decent most of the time. I'm sure the boys here will ask you for a schema probably an "explain" on the query. C Friday, October 24, 2014, 7:09:05 PM, you wrote: RA> Hi guys, RA> I'm currently working with a pretty gigantic database (116 Gb at the RA> moment, and growing). Performing a simple SELECT routine with one filter RA> takes between 7 and 15 minutes, which is starting to become a problem. The RA> command I'm using is the following: RA> SELECT NVERTS FROM ToricCY WHERE H11=2; RA> Pretty basic. I just want the NVERTS column of the table ToricCY where RA> another column labeled H11 is 2, nothing fancy. Because of the huge size of RA> the database, I would expect it to take a while, but I was wondering if you RA> guys have any recommendations on how to speed it up (if possible). RA> Also, because this problem will only get worse as the database grows, I'm RA> looking for alternative approaches to storing this data. Does anyone have RA> experience working with databases this big? RA> Thanks! RA> Best, RA> Ross RA> ___ RA> sqlite-users mailing list RA> sqlite-users@sqlite.org RA> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please fix the EBCDIC support
Hello John, I feed everything to Sqlite in UTF-8. If it's coming from Windows, that means I have to do a UTF-16 to UTF-8 conversion. I know Sqlite has UTF-16 support but, I want things to be consistent across all OS's I work with. The less I have to think about things like this, the better. C Thursday, October 16, 2014, 10:49:52 AM, you wrote: JM> On Thu, Oct 16, 2014 at 9:09 AM, Richard Hipp <d...@sqlite.org> wrote: >> On Thu, Oct 16, 2014 at 9:53 AM, John McKown <john.archie.mck...@gmail.com> >> wrote: >> >>> On Wed, Sep 24, 2014 at 9:46 PM, Richard Hipp <d...@sqlite.org> wrote: >>> > Please try the latest version of SQLite on trunk to see if that works >>> > better. Specifically, apply the patch at >>> > >>> > http://www.sqlite.org/src/vpatch?from=b2c89ef49cd1=ef30e0352b3d >>> > >>> > -- >>> > D. Richard Hipp >>> > d...@sqlite.org >>> >>> Unfortunately, the ASCII vs. EBCDIC issued _does_ make it >>> impossible to share a single SQLite data base file between z/OS and >>> other ASCII-based SQLite systems. >> >> >> If you store content in EBCDIC, it is retrieved in EBCDIC and if you store >> content in ASCII it is retrieved in ASCII, regardless of which platform you >> do the storing and retrieving on. I wonder if this is the right approach. >> Perhaps the zOS patch should be amended to simply transform EBCDIC->ASCII >> on input and ASCII->EBCDIC on output. >> JM> Hum, I'll need to look more closely at the code to see where this JM> would need to be implemented. As I indicated previously, making it JM> work on z/OS was so easy that I didn't need to really look closely at JM> the code itself. I basically compiled and it worked. And this was my JM> first attempt to port something to the z/OS environment, so I was not JM> very knowledgeable about it. I am somewhat more knowledgeable today JM> due to some other porting work that was somewhat more involved. I JM> guess that instead of saying ASCII<->EBCDIC, I need to say more JM> exactly ISO8859-1<->IBM-1047 since there are many "ASCII' and "EBCDIC" JM> code pages. Or does SQLite use UTF-8 internally? Hum, something else JM> to look into. I am not really that familiar with SQLite's internals. JM> Thanks for the thoughts. I appreciate your help. If I have more JM> questions, I guess that I would go over to the sqlite-dev forum. >> >> >>> I haven't looked closely enough at >>> the code to see if the big-endian (z/OS) vs. little-endian (Intel) >>> storage of integers would also be a problem. >>> >>> >> We do cross-platform database portability tests between x86, sparc, and >> PPC, to verify that big-endian vs little-endian is not a factor. JM> Thanks for telling me that. I won't worry about it again. >> >> -- >> D. Richard Hipp >> d...@sqlite.org >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UTF support
Hello J,
string_tsTest;
int nLengthNeeded = WideCharToMultiByte(CP_UTF8, 0, pszWide,nLength, 0,
0, 0, 0);
if( !nLengthNeeded )
{
ASSERT(0);
return(E_ABORT);
}
sTest.resize(nLengthNeeded + 16);
nLength = WideCharToMultiByte(CP_UTF8, 0, pszWide,nLength,
reinterpret_cast<char*>([0]),(uint32_t)sTest.size(),0, 0);
sTest[nLength] = 0;
ASSERT(!strcmp(sTest.c_str(),(char*)(*this)));
Is what I used to use to convert from UTF-16 to UTF-8 in Windows.
There are similar functions for converting in the opposite direction.
Internally my program is 100% UTF8. I do translations to UTF-16 right
at the point I display the strings in Windows.
This code is actually some test code I use today to compare the
conversions I do manually to what Windows generates. In debug mode, it
does two conversions and compares the two.
Tuesday, October 7, 2014, 8:59:07 AM, you wrote:
JD> On Tue, Oct 7, 2014 at 5:39 AM, Richard Hipp <d...@sqlite.org> wrote:
>> On Tue, Oct 7, 2014 at 12:06 AM, J Decker <d3c...@gmail.com> wrote:
>>
>> > I saw a few things go by about unicode... and understand that it should
>> > just work to store the data as characters...
>> >
>> > I'm getting a unrecognized token... and think this page isn't right...
>> > I was playing with greek translation of 'mary had a little lamb'
>> >
>> >
>> I ran the following script through the sqlite3 command-line shell and it
>> works fine:
>>
>> CREATE TABLE option4_values(option_id, string, segment);
>> REPLACE INTO option4_values(`option_id`,`string`,`segment`)
>> VALUES('8b377a68-4358-11e4-ace4-3085a9903449','Μαίρη είχε ένα μικρό
>> αρνί',0);
>> SELECT * FROM option4_values;
>>
>> Hmm... wonder what it's getting
>> I suggest that the problem is in your programming language, or in the
>> wrapper that links your programming language to SQLite, not in SQLite
>> itself. Can you tell us what programming language and what operating
>> system you are using?
>>
>> C, visual studio 2012 build, windows.
JD> built with UNICODE enabled... instead of multi-byte character set
JD> it could be my conversion routine... I'm using wcstombs_s with _MSC_VER
JD> set... before it was just faililng, because wcstombs_s doesn't convert
JD> anything with a high bit set... so I added a handler to replace it with a
JD> utf-8 16 bit character encode (expands to 3 bytes as described here
JD> http://en.wikipedia.org/wiki/UTF-8#Description )
JD> if( err == 42 )
JD> {
JD> (*ch++) = 0xE0 | ((unsigned char*)wch)[1] >> 4;
JD> (*ch++) = 0x80 | ( ( ((unsigned char*)wch)[1] & 0xF ) << 2 ) | ( (
JD> ((unsigned char*)wch)[0] ) >> 6 );
JD> (*ch++) = 0x80 | ( ((unsigned char*)wch)[0] & 0x3F );
JD> }
JD> which works... if I mouse-over on char * string it shows the right unicode
JD> characters.
JD> The logging that I included in the first message was converted from
JD> wchar_t* to char* and then the sqlite3_strerror() is expanded from char *
JD> to wchar_t * and still shows the right characters
JD> I just cannot identify the unrecognized token... it's obviously not at
JD> character 0... (that's gotten by comparing the pzTail result of
JD> sqlite3_prepare_v2 )...
>> --
>> D. Richard Hipp
>> d...@sqlite.org
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
JD> ___
JD> sqlite-users mailing list
JD> sqlite-users@sqlite.org
JD> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Best regards,
Tegmailto:t...@djii.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reinstalling My Build Environment
Hello Paul, What programming language are you using to devel with? Using C++, I "deploy" sqlite by building the lib and static linking it into my program. No DLL's or install is needed. Sounds like you're doing something significantly more complicated than that though. C Wednesday, September 10, 2014, 7:36:35 PM, you wrote: PB> SQLite is a great database, but I've had nothing but problems deploying it PB> to a clean machine. There is supposed to be nothing extra to do when moving PB> the files from the bin directory to the target machine, but then I hear PB> rumblings about an unknown missing C++ redistribution package that may need PB> to be added. All my deployment problems must be something that I'm doing PB> or not doing. Since I need to use Entity Framework, I started off by PB> installing sqlite-netFx451-setup-bundle-x86-2013-1.0.92.0.exe. Since the PB> deployment has been such a nightmare, I looked around and saw some PB> instructions on using the NuGet package. I installed the Nuget package, and PB> that pretty much corrupted my project and maybe my Visual Studio PB> installation, so I've decided to start fresh. PB> PB> It would be tremendously helpful if you could point me in the correct PB> direction for installing SQLite into my Visual Studio 2013 installation. PB> There seems to be so many pieces and different instructions that I'm PB> confused. Do I use the sqlite-net bundle (maybe I'm using the wrong one) to PB> install, or do I use NuGet or perhaps both? It would seem that using the PB> NuGet package would be best, but when I install the NuGet package into a PB> clean environment (yes I've done this several times), it's never compatible PB> with Entity Framework, or maybe I'm implementing it incorrectly. When using PB> the NuGet package SQLite never comes up as a database option for the ADO.net PB> Entity Data Model. PB> PB> Please help me. At this point, I'm desperate for an installation where I PB> can copy the compiled files from the bin directory to a clean machine and PB> have it run. PB> PB> I'm running: Windows 7 - 64 bit and Visual Studio 2013 Ultimate. My target PB> machines could be either 32 bit or 64 bit Windows 7 machines. PB> PB> Your help is tremendously appreciated, PB> PDB PB> PB> ___ PB> sqlite-users mailing list PB> sqlite-users@sqlite.org PB> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] GUI INfo
Hello Maxine, It's totally unrelated to Sqlite though. Pick a programming language that works with Sqlite and make a GUI with this programming language. C Wednesday, September 10, 2014, 4:56:39 PM, you wrote: MN> I am an experienced Access VBA programmer. I know about the SQLite commands MN> to create and manipulate data in tables and queries. MN> MN> What I want to know is where do I find info on creating a graphical user MN> interface such as menus, forms and reports. What additional programs are MN> required to do this? MN> MN> Thanks in advance, MN> Max MN> MN> ___ MN> sqlite-users mailing list MN> sqlite-users@sqlite.org MN> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What if OpenDocument were built atop SQLite?
Hello Richard, Monday, September 8, 2014, 8:35:30 AM, you wrote: RH> See the essay at: RH>http://www.sqlite.org/affcase1.html RH> Comments, criticism, and feedback are welcomed. I use Sqlite as a container for images because it's superior to CBR/CBZ files (which are rar and zip files). I think this is a great idea. >>Finally, let us reiterate that this essay is a thought experiment. The >>OpenDocument format is well-established and already well-designed. >>Nobody really believes that OpenDocument should be changed to use >>SQLite as its container instead of ZIP. Nor is this article a >>criticism of OpenDocument for not choosing SQLite as its container >>since OpenDocument predates SQLite. Rather, the point of this article >>is to use OpenDocument as a concrete example of how SQLite can be used >>to build better application file formats for future projects. It might be too late in the game but, I would have preferred this to zips for sure. Teg mailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Mixing journal modes from different threads
Hello George, I use different journal modes within the same application to different database files. My files don't all have the same protection level. Some files I turn it off completely for better speed. Others, I can't afford to lose the data. C Thursday, August 21, 2014, 3:17:05 PM, you wrote: GI> Good evening Dr. Hipp, GI> ok, got it. Thank you for the quick clarification. GI> I only asked on the mailing list because I couldn't find this kind of GI> information in the docs (or I didn't look thoroughly enough); if it's the GI> first case, perhaps it should be mentioned somewhere... GI> Thanks again, GI> George. GI> On Thu, Aug 21, 2014 at 10:07 PM, Richard Hipp <d...@sqlite.org> wrote: >> On Thu, Aug 21, 2014 at 3:05 PM, George Ionescu <geoione...@gmail.com> >> wrote: >> >> > Hello dear sqlite users, >> > >> > is it ok to mix journal modes from different threads accessing the same >> > database? >> > >> >> SQLite does not allow you to mix WAL mode with other journal modes. >> Wal-mode is a property of the database file. If you change to WAL-mode, >> then *all* database connections must go to WAL-mode. >> >> -- >> D. Richard Hipp >> d...@sqlite.org >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> GI> _______ GI> sqlite-users mailing list GI> sqlite-users@sqlite.org GI> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Questions from a novice - basic browsing of records in a listview.
ed SS> for them (apart from *), and you have to go to extra effort to SS> find the names of the columns of the values that were returned. SS> So all you have to do is remember what you asked for. SS> Simon. SS> ___ SS> sqlite-users mailing list SS> sqlite-users@sqlite.org SS> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Load time performance
Hello Rinat, I found that if I opened the DB and read it using normal file IO, throwing away the data I read, then closing it and opening it with Sqlite could reduce this initial delay. It depends on the size of the DB though. It's mostly useful for small DB's. C Wednesday, May 28, 2014, 3:22:49 AM, you wrote: HR> Hi, HR> I work in READ ONLY mode. HR> My application connects the DB only once, at the beginning. HR> I can't really work with local files. (I log to any machine and get my files from the network.) HR> Perhaps there are some intermediate files generated in the first HR> load that I can prepare in advance? HR> Rinat HR> -Original Message- HR> From: sqlite-users-boun...@sqlite.org HR> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Stephen Chrzanowski HR> Sent: Thursday, May 22, 2014 4:05 PM HR> To: General Discussion of SQLite Database HR> Subject: Re: [sqlite] Load time performance HR> You shouldn't access a SQLite database remotely, except maybe for HR> read only access, but that could come under fire. Any network HR> file action can't guarantee that write locks happen. HR> If you attempt to access a file in READ-ONLY mode, you'll be greeted with a HR> 1-5 second delay (I don't remember what the delay is right now) HR> per connection. If you're using one connection to the DB, then HR> you'll experience the delay. If you've got one connection going HR> for the life of your application, you'll get hit with the first HR> delay, but all subsequent queries will work. HR> On Thu, May 22, 2014 at 8:12 AM, Hadashi, Rinat <rinat.hada...@intel.com>wrote: >> Hi >> >> The first time I access a database takes significantly more time than >> subsequent accesses. >> I am looking for ideas to shorten the time required for the first access. >> >> I work on Linux, my db. file is "somewhere" in the file system, not >> locally on the machine from which I am running sqlite3 >> >> Thanks >> >> Rinat Hadashi >> >> HR> - HR> Intel Israel (74) Limited HR> This e-mail and any attachments may contain confidential material for HR> the sole use of the intended recipient(s). Any review or distribution HR> by others is strictly prohibited. If you are not the intended HR> recipient, please contact the sender and delete all copies. HR> _______ HR> sqlite-users mailing list HR> sqlite-users@sqlite.org HR> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reading compressed database files
Hello Rinat, I compress my data into blobs using Gzip before insert, and decompress when I read the blobs back out but, leave the other data in the DB un-compressed. In that way, I get compression but, normal operations remain fast. This works if the data to compress > 4Kish. My blobs can be 200K to 20megs in size. If it's a bunch of small text columns, this technique won't be useful. C Wednesday, May 28, 2014, 5:26:29 AM, you wrote: HR> Hi Roger HR> Where can I learn how to characterize my database? HR> I work read-only, single connection. HR> I have 13 tables, of which 2 are huge, 2 are medium and the rest are very small. HR> My huge tables have 3 columns: numeric, numeric and varchar with millions of rows. HR> I keep an index on the numeric columns. HR> Does that bring up any column ordering suggestion? HR> Thanks HR> Rinat HR> -Original Message- HR> From: sqlite-users-boun...@sqlite.org HR> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Roger Binns HR> Sent: Wednesday, May 28, 2014 12:06 PM HR> To: General Discussion of SQLite Database HR> Subject: Re: [sqlite] Reading compressed database files HR> -BEGIN PGP SIGNED MESSAGE- HR> Hash: SHA1 HR> On 28/05/14 00:25, Hadashi, Rinat wrote: >> My databases are very big (almost 100 GB). I am looking for a >> compression solution. >> >> Did anyone have an experience with reading a compressed database? HR> It would be helpful if you characterise your data and queries. HR> For example if the size is due to blobs, then careful ordering of HR> columns, or moving them to separate tables will likely be very useful. HR> You can get compression external to SQLite by using a compressing HR> filesystem like NTFS or btrfs (make sure to pick an appropriate HR> page size), or by internal compression with cerod: HR> http://www.hwaci.com/sw/sqlite/cerod.html HR> Roger HR> -BEGIN PGP SIGNATURE- HR> Version: GnuPG v1 HR> iEYEARECAAYFAlOFpvsACgkQmOOfHg372QRSRACfcDqTprcD//n9yYXcGPl9yQfo HR> sTIAoLkIaQHR4JAwk1LbuRzCyQsx/5aN HR> =tYeT HR> -END PGP SIGNATURE- HR> ___ HR> sqlite-users mailing list HR> sqlite-users@sqlite.org HR> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users HR> - HR> Intel Israel (74) Limited HR> This e-mail and any attachments may contain confidential material for HR> the sole use of the intended recipient(s). Any review or distribution HR> by others is strictly prohibited. If you are not the intended HR> recipient, please contact the sender and delete all copies. HR> _______ HR> sqlite-users mailing list HR> sqlite-users@sqlite.org HR> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Retrieve a int or a sqlite_int64
Hello Charles, Tuesday, May 13, 2014, 3:12:09 PM, you wrote: CS> Load it with sqlite3_value_int64 every time. If the number fits in a 32 bit CS> integer, then you can store it in one. This is what I do. Everything is 64 bits to be future proof. CS> Charles CS> ___ CS> sqlite-users mailing list CS> sqlite-users@sqlite.org CS> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using SQLite for write only workload, splitting databases into smaller files, but need to query it as a whole
Hello Hayden, If I was doing this, I'd use a single database as the core then periodically from within the app, write out a new database with the last 5 minutes worth of data. Then let them run off the copy while you maintain a master copy of the DB. In that way this splitting and multiple DB thing can go away while you still give them access to the data without letting them touch the master. If they need to read the master, I'd probably write a little client/server application to let them look at but, not change the data (assuming that's a requirement). I'd avoid multiple databases with the core data like the plague. Obviously you can't always do it but, I see it as a positive design goal. C Saturday, May 3, 2014, 9:40:37 AM, you wrote: HL> This looks promising. I sooo wish it didn't have a limit to number of HL> databases. But I think I could reasonably do something like coalesce the HL> databases into a new database once every 2 hours. I also need to generate HL> some code to figure out how to address the tables which I guess means I'll HL> have to do an N way JOIN? HL> On Sat, May 3, 2014 at 6:16 AM, Petite Abeille <petite.abei...@gmail.com>wrote: >> >> On May 3, 2014, at 2:59 PM, Hayden Livingston <halivings...@gmail.com> >> wrote: >> >> > Thoughts? >> >> Take a look at ‘ATTACH’, it might help: >> >> http://www.sqlite.org/lang_attach.html >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> HL> ___ HL> sqlite-users mailing list HL> sqlite-users@sqlite.org HL> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Long table fails to create with exec sql on windows 8.1
Hello Mark, >>System just stops responding. It's not clear if you're talking about the program or your PC. If the PC is hanging, it's broken and this isn't a software problem (well not a Sqlite problem). Anytime a PC running windows hangs or crashes, you know it's a driver or hardware problem (or V scanner). I just thought your comment was a bit ambiguous. I have plenty of windows customers who have PC's that hang or crash so, I'm pretty aware of these issues. C ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Noticed crash on windows
>>Quite unlikely is the prospect for somebody here to say "Yes, we >>routinely see crashes on Windows in the sqlite3_mutex_enter code." >>You are going to have to debug this. This. I run the hell out of Sqlite under 32 and 64 bit windows with no issues. 20-40 GB databases are common with multi-thread access. Typically only one writer though but, multiple threaded readers. Try running your code in debug build mode. I've run into cases where the release would crash and the debug version wouldn't. Typically, it's caused by a variable I forgot to initialize, which is zero'd in debug mode and not in release. I've not had any luck with "Full Program Optimization" either. Might try not using that if you currently do. C ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Windows service with SQLITE
Hello tejas, You're using windows so, you should be using "Procmon" to watch file IO. You need to add and look at logging. This is sort of trouble-shooting 101. If you can't get the debugger on it, you'll have to debug with logging. I'd generate a log file and log everything to do with opening and accessing the DB. C Sunday, February 16, 2014, 11:45:27 PM, you wrote: tp> Hi Teg, tp> I tried putting delay of 3 seconds and 30 seconds but nothing happens. tp> Still not able to access database and store anything. tp> When i create a file to store data it stores. Strange.. tp> Need more views please. tp> Thanks, tp> Tejas tp> On Sun, Feb 16, 2014 at 12:35 AM, Teg <t...@djii.com> wrote: >> Hello tejas, >> >> I'm thinking it sounds like the processing collides and you don't have >> retry's built into the process. Basically a race condition. The fact >> you can run one process and it works, and then later run a second >> process and it works suggests the problem is in the startup when you >> have two starting at the same time. >> >> As an experiment, I might put a long delay in the thread of the second >> process so, the first process gets good and started before the second >> one tries. >> >> C >> >> Saturday, February 15, 2014, 7:29:14 AM, you wrote: >> >> tp> Hi All, >> >> tp> We have a small issue with sqlite database. >> >> tp> Program structure: >> tp> one windows service calls two windows processes process-1 and >> process-2. >> tp> both processes use common database db1.s3db file to store data. >> >> tp> When windows services starts and calls two processes p-1 and p-2 none >> of >> tp> the process is able to store data in the database. >> tp> But if i call only one p-1 with service then everything works well >> tp> meanwhile if i run p-2 manually with double click then also two >> processes >> tp> are able to send data in to database. >> >> tp> Please help i am clueless. >> >> tp> Thanks, >> >> tp> Tejas >> tp> ___ >> tp> sqlite-users mailing list >> tp> sqlite-users@sqlite.org >> tp> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> >> >> -- >> Best regards, >> Tegmailto:t...@djii.com >> >> -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Windows service with SQLITE
Hello tejas, I'm thinking it sounds like the processing collides and you don't have retry's built into the process. Basically a race condition. The fact you can run one process and it works, and then later run a second process and it works suggests the problem is in the startup when you have two starting at the same time. As an experiment, I might put a long delay in the thread of the second process so, the first process gets good and started before the second one tries. C Saturday, February 15, 2014, 7:29:14 AM, you wrote: tp> Hi All, tp> We have a small issue with sqlite database. tp> Program structure: tp> one windows service calls two windows processes process-1 and process-2. tp> both processes use common database db1.s3db file to store data. tp> When windows services starts and calls two processes p-1 and p-2 none of tp> the process is able to store data in the database. tp> But if i call only one p-1 with service then everything works well tp> meanwhile if i run p-2 manually with double click then also two processes tp> are able to send data in to database. tp> Please help i am clueless. tp> Thanks, tp> Tejas tp> ___ tp> sqlite-users mailing list tp> sqlite-users@sqlite.org tp> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] System.Data.SQLite Preloading and ASP.Net debugging
I'm not a ".net" guy but, I do use visual studio. One thing I do is have several post-build steps that copy files around to where I need them before I debug. You can define "post build" steps in the project options. Thursday, January 30, 2014, 7:16:55 PM, you wrote: JM> Eric Schultz wrote: >> >> When I debug in Visual Studio, IIS Express copies all of the >> Assemblies in my "bin" directory to a folder into the "AppData\Temporary >> ASP.Net Files" folder. Unfortunately, it does not copy the >> System.Data.SQLite.dll.config file or the x86 and amd64 subfolders of bin >> which contain my interop files. >> JM> I've seen this behavior before as well. This appears to be a limitation of JM> Visual Studio. Ideally, it *should* copy the configuration file and all the JM> sub-folders as well. >> >> Is there any way so the preloading base directory is from where the >> System.Data.SQLite.dll file originally came from when I can't set the >> environment variables or have a config file in the same folder as the >> executing assembly (which I can't in this case). >> JM> Why can't you set environment variables? The currently supported workaround JM> for this limitation of Visual Studio is to use the JM> "PreLoadSQLite_BaseDirectory" JM> environment variable (or configuration file setting). JM> Of course, I'm always open to alternative suggestions on how to work around JM> issues like these in a portable and robust way. JM> -- JM> Joe Mistachkin JM> _______ JM> sqlite-users mailing list JM> sqlite-users@sqlite.org JM> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to Troubleshoot Disk I/O Error
Hello Akintoye, A common problem I ran into with the transition from XP to anything newer is that the user folders changed. So, what was a legal read/write folder in XP wasn't in Vista+. I had the folder embedded in the configuration file so, when they brought their old configurations forward, they were pointed at the wrong folder. Easy way to determine if this is the case it to right click the application and "Run as Administrator". If it starts working then you know the problem is permissions. For my users, the choice is to move the file, change the permissions, re-install or just "Run as Administrator" (which I suggested they not do). Thursday, January 30, 2014, 10:52:18 AM, you wrote: AOB7L> Thanks, will try this suggestions and post results. AOB7L> - Akintoye AOB7L> - Original Message - AOB7L> From: sqlite-users@sqlite.org AOB7L> To: sqlite-users@sqlite.org AOB7L> At: Jan 30 2014 10:44:45 AOB7L> On 01/30/2014 10:19 PM, Akintoye Olorode (BLOOMBERG/ 731 LEXIN) wrote: >> Hi, >> >> We have one client that recently upgraded from WinXP SP3 to Win7 sp1 >> (Japanese Language). With new OS, attempts to access read from our sqlite >> database fails with disk I/O error. >> >> Can anyone help with suggestions on how to troubleshoot disk I/O error ? >> Adding call to sqlite3_config(SQLITE_CONFIG_LOG, ...) produces no additional >> information. >> >> We have not been able to reproduce the error in-house.The user has HP ENVY >> laptop with hybrid drive. User has tried : >> >> 1. Install latest file system drivers >> 2. reformat the hard-drive & reinstall Win7 OS >> >> >> all to no avail. Client does not report problems with any other applications. AOB7L> Immediately after the error occurs, what value is returned by AOB7L> the sqlite3_extended_errcode() function? Often the extended error AOB7L> code identifies the specific IO operation that failed, which can AOB7L> shed light on the problem. AOB7L> Another thing to do is to try compiling with the following defines: AOB7L>-DSQLITE_DEBUG=1 -DSQLITE_DEBUG_OS_TRACE=1 AOB7L> This causes the Windows VFS module to print various messages to AOB7L> standard output that should help to figure out what is happening. AOB7L> Dan. AOB7L> ___ AOB7L> sqlite-users mailing list AOB7L> sqlite-users@sqlite.org AOB7L> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users AOB7L> _______ AOB7L> sqlite-users mailing list AOB7L> sqlite-users@sqlite.org AOB7L> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Application statically linked with sqlite3.6 creates sqlite2.1 files
Hello Joachim, If you run it in the debugger, break it then look at the loaded modules. That'll tell you if the Sqlite dll got loaded somehow. I'd suspect some other part of the program might be loading the DLL manually and hooking the function calls. You could load DLL through ordinals (basically the function index in the DLL) and not have any Sqlite related strings in the EXE. I tend to load my own DLL's through ordinals. If you see nothing, I might stick an Sqlite dll in the program folder and then run a second time and check again. Maybe something is trying to be smart and load the DLL when available but failing that, falling back on using internal static linked. How large is the .lib you're loading? My release build, 32 bit Sqlite lib is 2.1 megs. Release build 64 bits is 2.7 Megs C Thursday, January 23, 2014, 10:38:10 AM, you wrote: JB> Hello Simon, JB> thank you for your fast response. >> On 23 Jan 2014, at 1:58pm, Joachim Bürmann <jbuerm...@iftools.com> wrote: >> >> > On a certain customer system (Windows7 64bit) the application cannot >> > read the example project files (created with sqlite3.6 library). And >> > when the user stores his own settings in a new project, the project file >> > is saved as a version 2.1 file (** This file contains an SQLite 2.1 >> > database...) instead of a sqlite3 compatible file (SQLite format 3...) >> >> The calls to anything that can make a version 2.1 file have different names. >> It can't just be a case of calling the wrong 'sqlite_open()' function >> because the function is actually called 'sqlite3_open()' with the '3' right >> there in the name. >> >> > But because the sqlite functionality is part of the application (static >> > linked), the program should never able to access an external sqlite DLL. >> >> >> I don't think that that logic is correct. Can you run 'strings' (or >> whatever the Windows7 equivalent is) on the compiled file and see whether >> there are any mentions of 'sqlite2' ? Is there a debugging tool which will >> list the names of all routines which are called ? Ignore whether it is >> internal or external, just look at the routine names. >> JB> ok, my wording could missunderstand. Of course, also a statically linked JB> program can open a shared lib to access function from that. But our JB> application doesn't do that - not for sqlite. I only thought, that JB> accessing an external sqlite DLL could cause such a behaviour. But with JB> different names - as you mentioned - it's hard to believe that a older JB> DLL is the reason. JB> I don't know 'strings' for windows. I tried it with dumpbin (outputs all JB> dependencies and imports) and wintrc (similar to strace) and didn't find JB> any sqlite access (neither a function nor a library/dll open call with JB> sqlite in its name). That's why I'm so confused. >> I suspect that that particular customer computer has something weird loaded. JB> yes, I suppose that there is something different in that computer. More JB> than this: The customer told me, that he can reproduce the effect on JB> another notebook. So I think it isn't a matter of the OS but of some JB> other application he runs on both computers. JB> Anyway, thanks a lot for your response JB> Best regards JB> Joachim >> >> Simon. >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users JB> ___ JB> sqlite-users mailing list JB> sqlite-users@sqlite.org JB> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] store image file as blob
Hello d, I prefer them in the DB so, I can move them as a package. I have 30-40 GB DB's filled with image files. Performance is decent. I do keep the blobs in one table and meta-data in another. C Wednesday, October 30, 2013, 7:30:58 AM, you wrote: db> Hi, db> My sqlite database module has to store images (max. 100 KB) on client db> machine. db> 1. store images on file system and have reference in database db> 2. store image as blob in database. db> Which is the best way to store these images? db> Any suggestions are welcome. db> Best Regards, db> va. db> ___ db> sqlite-users mailing list db> sqlite-users@sqlite.org db> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite and Virtual PC
Hello Stephen, My experience is that shared folders under Virtual PC are completely unreliable. SMB actually works better, other than the fact Sqlite doesn't seem to like SMB. C Sunday, September 8, 2013, 4:46:04 PM, you wrote: SC> You're using it via a network which is a no-no for SQLite. ANY remote SC> access of a file under any networking infrastructure isn't guaranteed with SC> SQLite. SC> On Sun, Sep 8, 2013 at 12:31 AM, Gintaras Didzgalvis < SC> supp...@quickmacros.com> wrote: >> Hi, >> >> SQLite running on Microsoft Virtual PC 2007 cannot read and write >> databases that are on host PC. >> >> Details: >> sqlite3_open_v2(SQLITE_OPEN_**READWRITE) succeeds, but sqlite3_exec >> returns SQLITE_BUSY, even with SELECT. >> Using the official downloaded sqlite3.dll, version 3.8.0. >> OS on guest PC, where SQLite is used: Windows XP, SP3. >> OS on host PC: Windows 7, 32-bit. >> Accessing the database file using the "shared folders" feature of Virtual >> PC. >> Windows API functions can read and write the file. >> No problems on Vmware player (accessing the file through its virtual >> network). >> >> -- >> -- >> >> Gintaras >> http://www.quickmacros.com >> __ >> >> __**_ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users> >> SC> ___ SC> sqlite-users mailing list SC> sqlite-users@sqlite.org SC> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_bind_text truncates strings with "."
Hello Nickye, I insert strings with "." all the time. In fact I'd say most insertions I do have "."s in them so, I don't think there's an inherent problem in Sqlite. I convert everything to UTF8 before I insert though the UTF8 of "." is ".". C Monday, July 15, 2013, 3:13:46 PM, you wrote: nmr> Hello, nmr> Prepared statement SQL: "INSERT INTO tbl VALUES(?1);" nmr> Column type:TEXT nmr> String to bind (ASCII, zero terminated): "abcd.ef" nmr> nBytes parameter: -1 nmr> destructor parameter: SQLITE_STATIC nmr> Result after call sqlite3_step: "abcd" nmr> With nBytes==(len(String)) there's no problem, result=="abcd.ef" nmr> But then there's no way to bind parameter only once with the same text nmr> buffer. nmr> ni...@mail.ru nmr> ___ nmr> sqlite-users mailing list nmr> sqlite-users@sqlite.org nmr> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to save live data into sqlite database using c language?
Hello Newbie89,
Sunday, April 28, 2013, 3:32:07 AM, you wrote:
N> Thanks for the correction
N> ok...I will check first.
N> --
N> View this message in context:
N>
http://sqlite.1065341.n5.nabble.com/How-to-save-live-data-into-sqlite-database-using-c-language-tp68519p68521.html
N> Sent from the SQLite mailing list archive at Nabble.com.
N> ___
N> sqlite-users mailing list
N> sqlite-users@sqlite.org
N> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
I suspect you want one protocol field
enum _protocols
{
C_PROTOCOL_TCP,
C_PROTOCOL_UDP,
..
};
CREATE TABLE host
(
Src_MAC char(18),
Src_IP char(16),
Cap_Bytes integer,
Protocol integer,
);
With a single protocol tag per insert. Then you tag each packet insert
with the protocol field.
--
Best regards,
Tegmailto:t...@djii.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Writing in a blob
>>Imagine what could happen if, for example, git where to start using this >>library. Instead of the ".git" directory containing lots and lots of >>separate files, your repository would be a single ".git" file which was >>really an SQLite database accessed through the "sqlite3fs" wrapper. I use Sqlite for a git like version control system for my own projects. I also use it for storing image files similar to CBR/CBZ files. Having random access to the files makes it a better solution than CBR/CBZ files because, they're basically RAR and ZIP files with no random access. In general, there are many benefits to keeping a bunch of smallish files in a DB you can move around and change atomically. I do tend to keep blobs in their own database files and the meta-data in another file. The blobs seem to impact the performance of the meta data tables when they're combined into a single DB. I like the idea of a general purpose File IO wrapper over Sqlite, have no need for it to be built in though. C ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.7.17 preview - 2x faster?
Hello Richard, How much do you map at a time? I've virtually abandoned memory mapped files in Win32 because of address space limitations. There's a 2 GB address space limit in Win32 (most of the time) so, if the combination of allocated RAM and memory mapped file size bump into the limit, the memory map will fail. Win64 doesn't have this limit. It'll fail if it can't get a contiguous block of address space too. C Thursday, April 4, 2013, 8:02:34 AM, you wrote: RH> By making use of memory-mapped I/O, the current trunk of SQLite (which will RH> eventually become version 3.7.17 after much more refinement and testing) RH> can be as much as twice as fast, on some platforms and under some RH> workloads. We would like to encourage people to try out the new code and RH> report both success and failure. Snapshots of the amalgamation can be RH> found at RH>http://www.sqlite.org/draft/download.html RH> Links to the relevant documentation can bee seen at RH>http://www.sqlite.org/draft/releaselog/3_7_17.html RH> The memory-mapped I/O is only enabled for windows, linux, mac OS-X, and RH> solaris. We have found that it does not work on OpenBSD, for reasons we RH> have not yet been able to uncove; but as a precaution, memory mapped I/O is RH> disabled by default on all of the *BSDs until we understand the problem. RH> The biggest performance gains occur on windows, mac, and solaris. The new RH> code is also faster on linux, but not by as big a factor. The speed RH> improvement is also heavily dependent upon workload. Some operations can RH> be almost twice as faster. For others, there is no measurable speed RH> improvement. RH> Your feedback on whether or not the new code is faster for you, and whether RH> or not it even works for you, is very important to us. Thanks for giving RH> the new code a try. -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Saving ubyte[] zlib data in sqlite and calling it back
Hello jose, I compress/encrypt to a vector, then write the vector as a blob and reverse. Read the blob as a vector then reverse the process. Monday, February 11, 2013, 1:45:34 PM, you wrote: jic> Greetings. jic> I have this table, jic> CREATE TABLE Test (login primary key, password); jic> and I would like to save zlib data using ubyte[] and also call jic> it back in. I have things like this: jic> 1. To save in SQLite, jic> cmd = jic> " UPDATE Test password = " ~ jic> cast(char[])cast(ubyte[])std.zlib.compress(cast(void[])"password") jic> "WHERE login = 'test';"; jic> This works, or at least, it saves in SQlite, but when I call it back, it does not. jic> 2. To call it in SQLite, jic> char[] passWord = jic> cast(char[])cast(ubyte[])std.zlib.uncompress(cast(void[])r[0]["password"]); jic> where r[0]["password"] is what I just SELECTED from SQlite. I jic> am getting an UTF8 error. This is a D program, so, the syntax jic> may not be known to some or all of you. However, the more jic> realistic SQLite question would be, how can I save ubyte data in jic> SQLite and also call it back. Thoughts? Comments? Jokes? :-) jic> thanks. jic> josé jic> ___ jic> sqlite-users mailing list jic> sqlite-users@sqlite.org jic> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] bug report: out of memory error for most operations on a large database
CL> This looks as if all temporary data is stored in memory (of which you
CL> don't have too much in a 32-bit process.)
Yeah, this. Typically you only have 2 gigs of address space to work
with, in 32 bit windows. I've run out of RAM by having
"temp_store=memory" before. I also have 30-60 GB DB files that I
operate on that don't have any issues like this.
CL> This looks as if all temporary data is stored in memory (of which you
CL> don't have too much in a 32-bit process.)
CL> What is the value of "PRAGMA temp_store;" and the default setting of
CL> TEMP_STORE ("PRAGMA compile_options;")?
CL> Regards,
CL> Clemens
CL> ___
CL> sqlite-users mailing list
CL> sqlite-users@sqlite.org
CL> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Best regards,
Tegmailto:t...@djii.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Running on windows 98
I think you kick '98 to the curb and let the 5 people who use it maintain their own support for it. Bit-rot is a real problem with any code that goes mostly unexercised. I'm thinking maybe the '98 people should simply fork their own version of Sqlite. Then find their own '98 maintainer, instead of trying to force the job onto Dr Hipp. Thursday, January 24, 2013, 3:18:15 PM, you wrote: GG> On Thu, Jan 24, 2013 at 2:21 PM, Richard Hipp <d...@sqlite.org> wrote: >> On Thu, Jan 24, 2013 at 2:01 PM, Jose F. Gimenez <jfgime...@wanadoo.es>wrote: >> >>> Richard, >>> >>> thanks for replying. >>> >>> >>> We have no way of testing SQLite on Win9x and so we do not intend to >>>> support Win9x moving forward. Some older versions of SQLite are known to >>>> work on Win9x. If you are still supporting Win9x applications, I suggest >>>> you use those older versions of SQLite. >>>> GG> How about just supporting a compile time option to turn on or off that GG> optimization (on by default)? Then those compiling for Win9x could GG> just turn it off yet it would not require explicit support and testing GG> of Win9x since its the option being supported rather than the platform GG> support. GG> ___ GG> sqlite-users mailing list GG> sqlite-users@sqlite.org GG> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] performance regression when using "insert or replace"
--
>>
>>
>> LOWER LEVEL DETAILS OF WHY subJRequiresPage IS CALLED WHEN LINE 74643 IS IN
>> THE CODE
>> when running sample test_batch_insert.c file (included in this bug)
>> The reason that subjRequiresPage() returns true is that when
>> sqlite3GenerateConstraintChecks is called from sqlite3Insert,
>> this calls the guilty line,(74643)sqlite3MultiWrite(pParse);
>>
>> - This sets pParse->isMultiWrite to 1
>> - then sqlite3FinishCoding calls sqlite3VdbeMakeReady with
>> pParse->isMultiWrite =1
>>causing 3rd param of call to be TRUE.
>> - This causes Vdbe.usesStmtJournal to be set to TRUE in
>> sqliteVdbeMakeReady
>> - Then sqlite3BtreeBeginStmt is called from sqlite3VdbeExec (case
>> OP_Transaction:)
>> here, p->iStatement is set to 1 because p->usesStmtJournal is 1
>>54698 if( pOp->p2&& p->usesStmtJournal<--- we go INTO
>> this if statement
>>54699&& (db->autoCommit==0 || db->activeVdbeCnt>1)
>>54700 ){
>>54701 assert( sqlite3BtreeIsInTrans(u.as.pBt) );
>>54702 if( p->iStatement==0 ){
>>54703 assert( db->nStatement>=0&& db->nSavepoint>=0
>> );
>>54704 db->nStatement++;< this sets to 1, causing
>> next line to set iStatement to 1
>>54705 p->iStatement = db->nSavepoint + db->nStatement;
>>54706 }
>>54707 rc = sqlite3BtreeBeginStmt(u.as.pBt,
>> p->iStatement);
>>
>> - sqlite3BtreeBeginStmt calls sqlite3PagerOpenSavepoint using
>> iStatement as 2nd parameter,
>> therefore nSavepoint is set to 1
>>
>> - eventually we call sqlite3BtreeInsert which calls insertCell which
>> calls sqlite3PagerWrite which
>>calls pager_write a second time for the SAME pPg->pgno (see below for
>> example page 6)
>> - because we've gone through once for the same page, we do not
>> enter the if clause
>>on line 35464,
>>35464 /* The transaction journal now exists and we have a
>> RESERVED or an
>>35465 ** EXCLUSIVE lock on the main database file. Write
>> the current page to
>>35466 ** the transaction journal if it is not there
>> already.
>>35467 */
>>35468 if( !pageInJournal(pPg)&& isOpen(pPager->jfd) ){
>>
>>- therefore we DO NOT add this page to the savepoint bitVec on line
>> 35517
>>35517 rc = sqlite3BitvecSet(pPager->pInJournal,
>> pPg->pgno);
>>
>>- therefore we DO go into the if(subjRequiresPage(pPg)), because
>> nSavepoint is 1 but the bit is NOT set
>>35536 /* If the statement journal is open and the page is
>> not in it,
>>35537 ** then write the current page to the statement
>> journal. Note that
>>35538 ** the statement journal format differs from the
>> standard journal format
>>35539 ** in that it omits the checksums and the header.
>>35540 */
>>35541 if( subjRequiresPage(pPg) ){
>> 35542 rc = subjournalPage(pPg);
>>35543 }
>> from gdb...
>> - (gdb) p *pPg
>>$17= {pPage = 0x2b84618, pData = 0x2b83618, pExtra =
>> 0x2b84698, pDirty = 0x0, pPager = 0x2b666c8,
>> pgno = 6, flags = 6, nRef = 1, pCache = 0x2b667d0,
>> pDirtyNext = 0x2b83540, pDirtyPrev = 0x0}
>>(gdb) p pageInJournal(pPg)
>>$18 = 1
>>(gdb) p
>> sqlite3BitvecTest(pPager->aSavepoint[0]->pInSavepoint,6)
>>$19 = 0
>>
>>- therefore we end up CALLING subjournalPage(pPg) from line 35542,
>> causing temp file to be created and lots of small writes to the
>> file
>>
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
DK> ___
DK> sqlite-users mailing list
DK> sqlite-users@sqlite.org
DK> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Best regards,
Tegmailto:t...@djii.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Windows (slow) vs. iOS/OSX (fast) Performance
Hello David, In /control panel/system/device manager/disk drives/properties/Policies you can disable the windows "write cache buffer flush" to the drive. I wonder if this would make a difference? My machine is on a UPS so, I always disable it. I tend to disable it on laptops too. I know it's probably not a real solution but, I wonder if the lack of write buffering is why you see what you see. C Friday, November 30, 2012, 12:50:30 PM, you wrote: DdR> Nope, I ran the tests both in Parallels and rebooting directly DdR> into boot camp (basically native windows), and had essentially DdR> identical performance (+/- 2%, within noise level differences). DdR> It also echoes the performance difference I'd been seeing on the DdR> database side just watching the real app run on iOS and on my DdR> other non-Apple native windows box. Interesting little find, DdR> nonetheless, thanks for that. :) DdR> To Alex: Unfortunately, Windows is a core platform for us. We DdR> can't really just tell them to buzz off, so it's either figure DdR> out how to improve SQLite performance or switch DB engines, at least on that platform... DdR> -David DdR> DdR> From: sqlite-users-boun...@sqlite.org DdR> [sqlite-users-boun...@sqlite.org] on behalf of Black, Michael (IS) [michael.bla...@ngc.com] DdR> Sent: Friday, November 30, 2012 9:46 AM DdR> To: General Discussion of SQLite Database DdR> Subject: Re: [sqlite] Windows (slow) vs. iOS/OSX (fast) Performance DdR> Could this be your problem? DdR> http://mattgadient.com/2011/02/18/mac-os-x-slow-for-10-15-minutes-after-boot-the-fix/ DdR> Michael D. Black DdR> Senior Scientist DdR> Advanced Analytics Directorate DdR> Advanced GEOINT Solutions Operating Unit DdR> Northrop Grumman Information Systems DdR> DdR> From: sqlite-users-boun...@sqlite.org DdR> [sqlite-users-boun...@sqlite.org] on behalf of David de Regt [dav...@mylollc.com] DdR> Sent: Friday, November 30, 2012 11:41 AM DdR> To: General Discussion of SQLite Database DdR> Subject: EXT :[sqlite] Windows (slow) vs. iOS/OSX (fast) Performance DdR> Hey all. I've been struggling with a basic perf issue running DdR> the same code on Windows vs. iOS and OSX. DdR> Basic query set: DdR> CREATE TABLE test (col1 int, col2 text); DdR> [loop 500 times]: INSERT INTO TEST (col1,col2) VALUES (4,'test4') DdR> I'm coding this using the default C amalgamation release and DdR> using prepare/etc. on all platforms in the exact same way (same DdR> very simple DB-access class I made). I realize that using a DdR> transaction around this would vastly improve perf, but given the DdR> atomic nature of the app that this test is simulating, it won't DdR> work to wrap it into transactions, so my goal is to improve the DdR> atomic performance. These are all being run on the same Macbook DdR> Pro, with an SSD, running Windows via boot camp, OSX natively, and iOS via the iOS simulator: DdR> With defaults (pragma sync = on, default journal_mode): DdR> Windows: 2500ms DdR> iOS: 300ms DdR> OSX: 280ms DdR> With pragma sync = off, journal_mode = memory: DdR> Windows: 62ms DdR> iOS: 25ms DdR> OSX: 25ms DdR> Turning off sync doesn't make me feel warm and fuzzy about our DdR> lost-power scenario, so with sync on, it seems like something DdR> must be fishy for it to be ~8-9x slower than the other platforms. DdR> Is there something ridiculous about the windows file system DdR> performance that hoses sqlite's open/read/write/close transaction DdR> cycle? Is there anything I can do, or just accept it and move DdR> on? With how that scales up, we may need to move to something DdR> like using embedded MySQL or LocalDB on Windows to get the same DdR> performance as we see with SQLite on other platforms, which seems quite ridiculous. DdR> Thanks! DdR> -David DdR> ___ DdR> sqlite-users mailing list DdR> sqlite-users@sqlite.org DdR> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users DdR> ___ DdR> sqlite-users mailing list DdR> sqlite-users@sqlite.org DdR> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users DdR> ___ DdR> sqlite-users mailing list DdR> sqlite-users@sqlite.org DdR> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VC++ and SQLite
Hello Arbol, There's nothing special about Stdafx.h or Stdafx.cpp. Even if you switch to a different compiler, they just compile with no PCH. You do have to create them if you're going to use them though. I typically copy them in from another project. I even use them in Linux projects because it's a nice place to centralize headers used by a project. Once you understand the in's and outs of PCH in VS it's simply not that hard. I don't use PCH with Sqlite.c. It's in it's own library project which my main project links to. In that way, each project can compile however works best for it. Everything I write uses PCH, everything I inherit, might or might not use PCH. AO> What a nightmare Visual Studio is >:( You know what they say. It's a poor craftsman who blames his tools. Your problems with VS seem to stem from general noobishness. Learning devel tools is no different than learning a foreign language. You need to research it and learn how it all works. I think things would be alot easier for you if you made a new static lib project just for Sqlite, compile it there with no PCH and link it into your program. If you add the project as a "Reference" VS will link it in automatically. Tuesday, November 13, 2012, 8:33:48 AM, you wrote: AO> I would also keep this feature, however, in the case of SQLite3 AO> amalgamation, I am really confused. You know how we have to AO> #include the 'stdafx.h' in every declaration file (making it AO> non-portable code), i.e. .c, .cpp, etc., well, I tried doing the AO> same thing with sqlite.c, but VS10 complains about it. AO> What a nightmare Visual Studio is >:( AO> Genius might have limitations, but stupidity is no handicap AO> Eat Kosher AO> -Original Message- AO> From: sqlite-users-boun...@sqlite.org AO> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of John Drescher AO> Sent: Monday, November 12, 2012 7:44 PM AO> To: General Discussion of SQLite Database AO> Subject: Re: [sqlite] VC++ and SQLite AO> On Mon, Nov 12, 2012 at 1:52 PM, Doug Nebeker <pa...@poweradmin.com> wrote: >> You might be surprised at the speed increase you see in compile time >> if you've got large projects. The time isn't lost to CPU as much, but >> disk I/O time adds up when hitting many hundreds of small (header) >> files (even with an SSD). >> AO> This is why I use PCH. Building some of my projects take a long AO> time even on a 12 threaded processor with multiple SSDs. AO> -- AO> John M. Drescher AO> ___ AO> sqlite-users mailing list AO> sqlite-users@sqlite.org AO> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users AO> ___ AO> sqlite-users mailing list AO> sqlite-users@sqlite.org AO> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] First time poster: need advise
Hello Igor, Why don't you download the C and H file from the amalgamation and then compile it into your program? That's how I do it. No fuss no muss. No worry about DLL version problems too. C Friday, September 21, 2012, 4:40:35 PM, you wrote: IK> Igor, IK> On Fri, Sep 21, 2012 at 5:40 AM, Igor Tandetnik <itandet...@mvps.org> wrote: >> Igor Korot <ikoro...@gmail.com> wrote: >>> When I tried to download the precompiled developmental package for >>> Windows I saw the sqlite-dll-win32-x86-3071400.zip. However this file >>> contains DLL file and no .h. >>> >>> Where can I get the headers? Do I need to download amalgamation archive? >> >> Yes. You would also need an import library - a LIB file. You make one as >> described at http://support.microsoft.com/kb/131313 . Simply run this >> command: >> >> lib /DEF:sqlite.def >> >> LIB tool is found in /bin subfolder of your Visual Studio installation. IK> Stupid question: why not provide .h and .lib files as well in one IK> archive along with .dll? IK> The comment on those files says: This is all that is needed to do the IK> development. IK> Thank you. >> -- >> Igor Tandetnik >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users IK> _______ IK> sqlite-users mailing list IK> sqlite-users@sqlite.org IK> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Store error messages in thread local memory
gt; > > > >> > > > >> > > >> > > >> > > not >> > > > > sure whether thread local memory even exists in C. It does in C# and >> > > > >> > > >> > > >> > > Java >> > > > > though.) >> > > > >> > > > >> > > > >> > > > >> > > > Thread local storage has been available to C code since long before >> > > > Java >> > > > and C# were even invented. But it is accessed through library routines >> > > > that are not cross-platform, so we are not interested in using it in >> > > > SQLite. Furthermore, making such a change would break backwards >> > > > compatibility, which is a huge no-no with SQLite. >> > > > >> > > > Best regards >> > > > > Sebastian >> > > > > >> > > > > ___ >> > > > > sqlite-users mailing list >> > > > > sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org) >> > > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > > > >> > > > >> > > > >> > > > >> > > > >> > > > >> > > > >> > > > -- >> > > > D. Richard Hipp >> > > > d...@sqlite.org (mailto:d...@sqlite.org) >> > > > ___ >> > > > sqlite-users mailing list >> > > > sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org) >> > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > > >> > > >> > > >> > > >> > > >> > > ___ >> > > sqlite-users mailing list >> > > sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org) >> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > >> > >> > >> > >> > >> > >> > -- >> > D. Richard Hipp >> > d...@sqlite.org (mailto:d...@sqlite.org) >> > ___ >> > sqlite-users mailing list >> > sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org) >> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > >> >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org) >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org) >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> SK> ___ SK> sqlite-users mailing list SK> sqlite-users@sqlite.org SK> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Compile Bug for Windows 8
Hello Igor, Friday, September 14, 2012, 8:59:54 AM, you wrote: IT> Chang Li <changli...@hotmail.com> wrote: >> At line 14573 require add (char *) for convert from (void *) >> >> z = (char *)sqlite3DbMallocRaw(db, (int)n); IT> Are you, by any chance, trying to compile in C++ mode? SQLite is IT> written in C, where such a cast is not required. Yeah, I'm building under Win8 with no issue. -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE in a Win7 Service Application
Hello markus, Every Windows programmer needs to have "Procmon" installed on their system so, they can watch their program's disk IO. I'd suggest installing it, then using a filter to watch just your service. Then, you can simply watch what disk IO is failing. I use it on a daily basis. Almost as much as my debugger. It'll probably show you a permission problem. C ... Test.exe 6312 ReadFileE:\Test\Db.db3SUCCESS Offset: 0, Length: 100, Priority: Normal Test.exe 6312 ReadFileE:\Test\Db.db3SUCCESS Offset: 0, Length: 4,096, I/O Flags: Non-cached, Paging I/O, Priority: Normal Test.exe 6312 QueryOpen E:\Test\Db.db3SUCCESS CreationTime: 6/19/2012 9:06:58 PM, LastAccessTime: Test.exe 6312 LockFileE:\Test\Db.db3SUCCESS Exclusive: True, Offset: 1,073,741,824, Length: 1, Fail Immediately: True Test.exe 6312 LockFileE:\Test\Db.db3SUCCESS Exclusive: False, Offset: 1,073,741,826, Length: 510, Fail Immediately: True Test.exe 6312 UnlockFileSingle E:\Test\Db.db3SUCCESS Offset: 1,073,741,824, Length: 1 Test.exe 6312 QueryOpen E:\Test\Db.db3-journalSUCCESS CreationTime: 6/19/2012 9:06:58 PM, Test.exe 6312 QueryStandardInformationFile E:\Test\Db.db3SUCCESS AllocationSize: 106,496, EndOfFile: 106,496, NumberOfLinks: 1, Test.exe 6312 QueryOpen E:\Test\Db.db3-wal NAME NOT FOUND Test.exe 6312 QueryStandardInformationFileE:\Test\Db.db3SUCCESS AllocationSize: 106,496, EndOfFile: 106,496, Test.exe 6312 ReadFileE:\Test\Db.db3SUCCESS Offset: 0, Length: 8,192 ... and so on. Wednesday, August 15, 2012, 11:19:52 AM, you wrote: mr> Hi, mr> We are facing problems when the sqlite database () is used within an mr> application that runs as a Win7 service under the local system account. mr> The application is written in Java and we use the sqlite-jdbc-3.7.2 JDBC mr> driver. mr> How ever we can create a new database, reading and writing to it withount mr> any problem. The only thing that is not working is the ATTACH DATABASE SQL mr> command. We constantly receive a CAN_NOT_OPEN error code. mr> When we start the same application under a local user everything works mr> fine. I have to add the both databases that are to be attached are in the mr> same folder and have been created by the Application itself. There are no mr> access restrictions on the database folders or files. mr> Does anyone know about this and / or has a solution? mr> Kind regards mr> Markus mr> ___ mr> sqlite-users mailing list mr> sqlite-users@sqlite.org mr> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] I consider this a bug. Anyone else?
In windows, I believe you want to use the "function level linking" option to get it to drop the most functions when they aren't used. Particularly for the amalgamation, I think this will give you the tightest link. By default, I believe the optimization only operates on compilation units, meaning the amalgamation would always include everything in the link stage. http://msdn.microsoft.com/en-us/library/xsa71f43(v=vs.80).aspx C Tuesday, June 26, 2012, 8:52:58 PM, you wrote: SS> On 27 Jun 2012, at 1:48am, Stephen Chrzanowski <pontia...@gmail.com> wrote: >> When you compile the amalgamation with your source code, doesn't the >> compiler reject/not include/remove/doesn't consider the code that could >> have been generated from the actual final build? In other words, if you >> just have one function being used out of 10,000 functions, the final binary >> would only include the code for that single function and not the 10,000? SS> Your compiler will probably be set to do optimization. It will SS> not include any functions which are not called. SS> Simon. SS> ___ SS> sqlite-users mailing list SS> sqlite-users@sqlite.org SS> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with including sqlite3.c into c++ project
AD> Do not use precompiled headers on sqlite3.c This. My Sqlite is in it's own library project so, it can have different precompiled header settings than my other projects. About 80% of my projects use procompiled headers, the other 20 are typically made of code like Sqlite which don't have built in support for PCH. You can probably make it work if you really want to by including "Stdafx.h" at the top of the amalgamation C file. You'll have to do this every time you upgrade though. C ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] intial database creation
Hello Brett, All of my DB's are generated in code. When I version control the code, I'm also version controlling the schema. If my users end up with bad DB's (it's windows, it happens fairly frequently). They just delete them and they're then re-created as needed by the program. I have specific unit tests built as part of the class itself to test access to the DB and test all the queries and inserts. I just use the command line tool for trouble-shooting. C Thursday, May 10, 2012, 4:26:29 PM, you wrote: MBE> How does one go about creating a database initially in SQLite? MBE> Do you create a schema file for it to read or similar? MBE> ___ MBE> sqlite-users mailing list MBE> sqlite-users@sqlite.org MBE> 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] Visual Studio 2008 Express and sqlite3.c
Hello Marco,
You have to make sure it's building as C and not C++. This suggests
you might have your project set to build everything as C++. In my
case, I made a new subproject for Sqlite that builts it into a Lib.
Then I set the build dependencies so the lib is used automatically at
link time.
Friday, March 30, 2012, 8:32:55 AM, you wrote:
MB> Please note that if I use the same compiler to compile sqlite3.c
MB> as a single c file than everything is compiled without errors.
MB> Errors occurs only when sqlite3.c is part of a c++ project.
MB> I also manually set sqlite3.c to be compiled as C file instead of Default
without any luck.
MB> Seems like something confuse the Visual C++ compiler.
MB> Any idea?
MB> --
MB> Marco Bambini
MB> http://www.sqlabs.com
MB> On Mar 30, 2012, at 1:08 PM, Nick Shaw wrote:
>> -Original Message-
>>> I am trying to compile the latest sqlite 3.7.11 amalgamation C file within
>>> a Visual Studio 2008 Express C++ project.
>>> sqlite3.c is correctly set as to be compiled as C code but I am unable to
>>> find out a way to fix some compilation errors:
>>>
>>> Compiling...
>>> sqlite3.c
>>> ..\Sources\sqlite3.c(107831) : error C2143: syntax error : missing ':'
>>> before '!'
>>> ..\Sources\sqlite3.c(107831) : error C2059: syntax error : ';'
>>> ..\Sources\sqlite3.c(107832) : error C2059: syntax error : '}'
>>> ..\Sources\sqlite3.c(107994) : error C2079: 'yy318' uses undefined struct
>>> 'LikeOp'
>>> ..\Sources\sqlite3.c(110530) : error C2224: left of '.eOperator' must have
>>> struct/union type
>>> ..\Sources\sqlite3.c(110530) : error C2059: syntax error : '!'
>>> ..\Sources\sqlite3.c(110534) : error C2224: left of '.eOperator' must have
>>> struct/union type
>>> ..\Sources\sqlite3.c(110534) : error C2059: syntax error : '!'
>>> ..\Sources\sqlite3.c(110538) : error C2275: 'ExprList' : illegal use of
>>> this type as an expression
>>>..\Sources\sqlite3.c(8133) : see declaration of 'ExprList'
>>> ..\Sources\sqlite3.c(110541) : error C2224: left of '.eOperator' must have
>>> struct/union type
>>> ..\Sources\sqlite3.c(110541) : error C2198: 'sqlite3ExprFunction' : too few
>>> arguments for call
>>> ..\Sources\sqlite3.c(110542) : error C2059: syntax error : '!'
>>> ..\Sources\sqlite3.c(110554) : error C2224: left of '.eOperator' must have
>>> struct/union type
>>> ..\Sources\sqlite3.c(110554) : error C2198: 'sqlite3ExprFunction' : too few
>>> arguments for call
>>> ..\Sources\sqlite3.c(110555) : error C2059: syntax error : '!'
>>
>> Looks like something's not right with the definition of the LikeOp struct in
>> your copy of the file. I've got the exact same amalgamation in my VS2008
>> project and it builds fine. Can you check what you have for the LikeOp
>> structure definition? Mine looks like this, and starts on line 107829:
>>
>> struct LikeOp {
>> Token eOperator; /* "like" or "glob" or "regexp" */
>> int not; /* True if the NOT keyword is present */
>> };
>>
>>
>> Thanks,
>> Nick.
>> --
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
MB> ___
MB> sqlite-users mailing list
MB> sqlite-users@sqlite.org
MB> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Best regards,
Tegmailto:t...@djii.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Could/should Windows build of SQLite use #define WIN32_LEAN_AND_MEAN?
Hello Jeff, Isn't that just a build time thing? Include fewer includes during the compile? I just don't build sqlite often enough to seem to think this matters. Pretty sure this has no impact on the ultimate size of the code generated. Friday, March 23, 2012, 7:51:36 AM, you wrote: JR> When building using the SQLite amalgamation, I noticed Windows.h JR> being included without #define WIN32_LEAN_AND_MEAN. This includes JR> a lot of extraneous "cruft". Any reason not to trim down the windows build this way? JR> ___ JR> sqlite-users mailing list JR> sqlite-users@sqlite.org JR> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VIsual Studio 2005. slow and high CPU in debug
Hello Juan, Debug mode is unusable for production code. Even it you add optimization, MS builds in a debug memory allocator/deallocator that's at least 10 times slower than the regular memory allocator. Running my application under debug, the memory allocations completely dominate processing performance. It's more than assertions, the debug memory allocator tracks every allocation and de-allocation. Your alternative is to build for release but, tell the linker and the compiler to add debug code to the exe. Even if you turn the optimizer off, you still end up with faster code. C Monday, March 19, 2012, 5:05:49 AM, you wrote: JP> Hi all: JP>I´m using SQLite amalgamation in various exe files with Visual Studio JP> 2005. JP>In debug mode the exe files are very slow and have high CPU usage. JP>In release the problem dissapears. JP>Is there some option in SQLite to avoid this problem? JP> Thank you very much in advance. JP> ___ JP> sqlite-users mailing list JP> sqlite-users@sqlite.org JP> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Cyrillic support
Hello Jean-Christophe, Good point. My application is 100% UTF-8 so, I probably think in just UTF-8. C Saturday, March 10, 2012, 4:38:00 PM, you wrote: >>You just need to convert it to UTF-8 before insert, then convert it >>back when you pull the data. >> >>UTF-16->UTF8 - insert >>Select - UTF8->UTF16 JCD> There are 16-bit SQLite APIs for that, no conversion needed JCD> actually. But of course it all depends of the wrapper you use, if any. -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Cyrillic support
Hello Mite, You just need to convert it to UTF-8 before insert, then convert it back when you pull the data. UTF-16->UTF8 - insert Select - UTF8->UTF16 Or whatever encoding you use. I insert and retrieve full asian languages using UTF-8 conversions. You should be using UTF-8 for you filenames when opening the DB too. C Saturday, March 10, 2012, 12:07:23 PM, you wrote: M> I am using the newest version of SQLite. How do I enable support for M> Cyrillic letters? Whenever I enter something in the DB with Cyrillic M> letters it gets saved like this ?? M> Thanks M> ___ M> sqlite-users mailing list M> sqlite-users@sqlite.org M> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug hunting in SQLite
Hello Patrik,
As a trouble-shooting step. I'd add a second table with a checksum of
the blob. Maybe an MD5 and then every time I accessed the row of the
DB for any reason, I'd re-check the MD5 of the blob. Then break/assert
when the program notices the blob is no longer correct.
I use gigs of blobs and have no issues with corruption. Assuming the
blob actually gets entered correctly, I imagine either the DB is
becoming corrupt or something in your program is writing to the blob
when you don't expect it.
C
Sunday, February 26, 2012, 3:47:18 PM, you wrote:
PN> I only load "data" from a particular row when I need to display it. The
PN> "data" that got corrupted isn't read for some time and even if I close
PN> down the program and restarts the computer the same corruption is there
PN> on the same row. It is saved into "data", although I don't modify it in
PN> any way.
PN> Patrik
PN> On 02/26/2012 09:12 PM, Pavel Ivanov wrote:
>> My guess is you read blob data incorrectly. You get pointer to the data
>> from SQLite, but actually read the data when memory has been already reused
>> for other purposes.
>>
>> Pavel
>>
>>
>> On Sunday, February 26, 2012, Patrik Nilsson <nipatriknils...@gmail.com>
>> wrote:
>>> Dear All,
>>>
>>> I have a table called page (defined below) and have noticed that the
>>> data-variable (blob) sometimes get corrupted. How do I set up a log to
>>> debug what is happening. What I want is a log file saved with my file,
>>> so I can backtrace the error.
>>>
>>> This is also to be able to send in a good bug report to the developers
>>> of SQLite.
>>>
>>> I don't know the exact steps to reproduce the error, but the following
>>> might give someone a clue:
>>>
>>> 1) Add rows (one by one) with data.
>>> 2) Update sortorder for the whole table with begin/commit
>>> 3) After these and maybe some other steps (i.e. updating a row's data
>>> with new data), I discover that a random row of the table has a
>>> corrupted data-blob.
>>>
>>> I have made a verify function for the whole table of the data-blobs. The
>>> data-blob can contain images varying in size from a few hundred bytes to
>>> some (about 5) megabytes of images. (Currently only png-images.)
>>>
>>> Best regards,
>>> Patrik
>>>
>>>
>>> create table if not exists page ( id integer primary key autoincrement,
>>> type integer, sortorder integer, width integer, height integer, size
>>> integer, deleted integer default 0, zoom integer default 0, modified
>>> datetime default (datetime('now','localtime')), comment text, data blob )
>>>
>>> ___
>>> 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
>>
PN> ___
PN> sqlite-users mailing list
PN> sqlite-users@sqlite.org
PN> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Best regards,
Tegmailto:t...@djii.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] insert image into db - windows batch
Hello Petr, You could Mime encode them to text, insert as a string. Pull them back out as strings, un-mime them. Should be able to do that from a batch file. I keep a bunch of images in Sqlite DB files. It's reasonably fast and I like having them all in one place. Some of the DB files are > 10 GB. C Wednesday, January 18, 2012, 10:14:17 AM, you wrote: PL> Because of packing. The script should be distributable as one PL> .cmd file, the database engine and database itself will be embeded PL> inside od script. Dealing with embeded files in the shell script PL> is not easy thus I want to minimize its number. PL> L. >> On Jan 18, 2012, at 12:00 PM, Petr Lázňovský wrote: >>> have windows batch working with sqlite, may I insert image into database >>> and than read this images from? >> As pointed out, you might want to use the 'blob' type to store binary data. >> That said, why bother storing these images inside the database itself? Any >> benefit in doing so? >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users PL> ___ PL> sqlite-users mailing list PL> sqlite-users@sqlite.org PL> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.7.9 amalgamation file in VS2005
Yeah, I was wrong. Color syntax highlighting does work. It's single stepping into the code that doesn't. Sorry. C Wednesday, December 28, 2011, 3:04:39 AM, you wrote: AN> Dear Michael, AN> AN> thanks for trying this out. Not at the moment but I will consider upgrading for the future. AN> AN> In fact I was surprised about this problem because amalgamation AN> file version 3.7.7.1 still *has* correct highlighting in both AN> VS2005 and 2008. The 3.7.9 amalgamation fle is, of course, larger, AN> but the difference does not seem to be so big so that the AN> highlighting should fail. So maybe there might be some syntax AN> elements in 3.7.9 (large comments or whatever) causing this AN> behaviour or there is really a maximum source file size that AN> VS2005 and 2008 syntax highlighter can support, which was reached between 3.7.7.1 and 3.7.9. AN> AN> Thanks AN> AN> Alex AN> __ >> Od: "Black, Michael (IS)" >> Komu: General Discussion of SQLite Database >> Datum: 27.12.2011 23:36 >> Předmět: Re: [sqlite] 3.7.9 amalgamation file in VS2005 >> AN> I duplicated your problem on C++ 2005 Express and C++ 2008 Express. AN> C++ 2010 Express does the syntax highlighting correctly (or at least a lot better). AN> Can you upgrade? AN> Michael D. Black AN> Senior Scientist AN> Advanced Analytics Directorate AN> Advanced GEOINT Solutions Operating Unit AN> Northrop Grumman Information Systems AN> AN> From: sqlite-users-boun...@sqlite.org AN> [sqlite-users-boun...@sqlite.org] on behalf of Alexandr Němec [a.ne...@atlas.cz] AN> Sent: Tuesday, December 27, 2011 8:55 AM AN> To: sqlite-users@sqlite.org AN> Subject: EXT :[sqlite] 3.7.9 amalgamation file in VS2005 AN> Dear all, AN> I have one question that is not strictly a SQLite question AN> (sorry), but maybe someone encountered this problem and found the AN> solution. I have upgraded from an older SQLite release to 3.7.9 AN> and loaded the amalgamation file into a Visual Studio 2005 AN> project. But the syntax code highlighter does not behave correctly AN> with the 3.7.9 amalgamation file because it greys out not only the AN> sections of code that are not compiled at all (because of AN> if(n)def's) but also other sections that DO compile. Well, I think AN> that this is a bug of the VS 2005 syntax highlighter for such a AN> large source file, because the file compiles ok, but working with AN> such a file in VS 2005 is frustrating. AN> Did anyone see (did anyone find a solution for) this problem? I AN> have not seen this for older versions of the amalgamation file. AN> Best regards AN> Alex AN> ___ AN> sqlite-users mailing list AN> sqlite-users@sqlite.org AN> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users AN> ___ AN> sqlite-users mailing list AN> sqlite-users@sqlite.org AN> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.7.9 amalgamation file in VS2005
VS2010 has the same problem. The file is simply too big for it. I'm not a fan of the amalgamation because of this. It's essentially impossible to trace into the SQlite code now. That said, it compiles and works fine. C Tuesday, December 27, 2011, 10:50:14 AM, you wrote: JJD> In my experience I find that most syntax highlighters fail JJD> on occasion, especially with large files. Trust the compiler not the JJD> editor. JJD> 2011/12/27 Alexandr Němec <a.ne...@atlas.cz> >> >> Dear all, >> >> I have one question that is not strictly a SQLite question (sorry), but >> maybe someone encountered this problem and found the solution. I have >> upgraded from an older SQLite release to 3.7.9 and loaded the amalgamation >> file into a Visual Studio 2005 project. But the syntax code highlighter >> does not behave correctly with the 3.7.9 amalgamation file because it greys >> out not only the sections of code that are not compiled at all (because of >> if(n)def's) but also other sections that DO compile. Well, I think that >> this is a bug of the VS 2005 syntax highlighter for such a large source >> file, because the file compiles ok, but working with such a file in VS 2005 >> is frustrating. >> >> Did anyone see (did anyone find a solution for) this problem? I have not >> seen this for older versions of the amalgamation file. >> >> Best regards >> >> Alex >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL file size
Hello Richard, Thanks again. Wednesday, November 30, 2011, 5:37:32 PM, you wrote: RH> On Wed, Nov 30, 2011 at 5:31 PM, Teg <t...@djii.com> wrote: >> Since we're on this topic >> >> If I open a 30 GB DB file, then attach a new empty DB and then >> select/insert all the data from the full DB to the empty one, the WAL >> file grows to 4 times the size of the source file. Why is that? >> >> I don't believe I use any manual checkpointing. Just let WAL do it's >> thing. >> RH> The WAL file won't get so big if you break the transfer up into multiple RH> transactions. RH> Or, you can temporarily disable WAL mode for certain unusual operations RH> (like this one) for which is not well suited. >> >> >> >> >> >> Wednesday, November 30, 2011, 4:36:00 PM, you wrote: >> >> >> SS> On 30 Nov 2011, at 7:01pm, Richard Hipp wrote: >> >> >> The wal file persists until the last connection to the database closes, >> >> then the wal file is deleted. >> >> SS> Actually this is the key to a lot of questions about this. If >> SS> your WAL file is taking up too much space, quit and restart your >> SS> app. Or have your app close and reopen the database connection. >> >> SS> It's a little more complicated in multi-user or multi-process >> situations, of course. >> >> SS> Simon. >> SS> ___ >> SS> sqlite-users mailing list >> SS> sqlite-users@sqlite.org >> SS> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> >> >> >> -- >> Best regards, >> Tegmailto:t...@djii.com >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL file size
Since we're on this topic If I open a 30 GB DB file, then attach a new empty DB and then select/insert all the data from the full DB to the empty one, the WAL file grows to 4 times the size of the source file. Why is that? I don't believe I use any manual checkpointing. Just let WAL do it's thing. Wednesday, November 30, 2011, 4:36:00 PM, you wrote: SS> On 30 Nov 2011, at 7:01pm, Richard Hipp wrote: >> The wal file persists until the last connection to the database closes, >> then the wal file is deleted. SS> Actually this is the key to a lot of questions about this. If SS> your WAL file is taking up too much space, quit and restart your SS> app. Or have your app close and reopen the database connection. SS> It's a little more complicated in multi-user or multi-process situations, of course. SS> Simon. SS> ___ SS> sqlite-users mailing list SS> sqlite-users@sqlite.org SS> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL file size
Hello Richard, That's what I was hoping to hear. Thanks, Wednesday, November 30, 2011, 2:01:46 PM, you wrote: RH> On Wed, Nov 30, 2011 at 1:39 PM, Teg <t...@djii.com> wrote: >> Hello Richard, >> >> Appreciate the reply but, it doesn't address my question (unless I'm >> missing something). I get that checkpointing doesn't reduce the size >> of the WAL. Assuming a single writer and no readers, If I perform a >> transaction that generates a WAL file. Does it eventually get deleted >> when I finish my transaction? How about when I close the DB? RH> The wal file persists until the last connection to the database closes, RH> then the wal file is deleted. Meanwhile, the wal file is recycled (meaning RH> that SQLite starts reusing it again from the beginning) after each RH> successful checkpoint. Checkpoints are automatically run by transactions RH> that cause the size of the wal file to exceed 1000 pages (though this can RH> be tuned or disabled if desired). RH> For most applications, WAL mode just works and you don't have to think RH> about how. >> I haven't >> updated to the most current SQlite because I was unclear about how >> persistent the WAL files actually were. Some of my DB's are 30 GB+. I >> don't mind the WAL's growing as long as they go away when the process >> is finished. >> >> >> >> >> >> Wednesday, November 30, 2011, 1:10:50 PM, you wrote: >> >> RH> On Wed, Nov 30, 2011 at 1:04 PM, Teg <t...@djii.com> wrote: >> >> >> Hello Richard, >> >> >> >> When does the WAL get trimmed down? If my transaction completes, does >> >> the WAL file get deleted? I've seen the WAL grow to 4 times my actual >> >> DB size so, I don't really want 160 Gigs of WAL hanging out when the >> >> process that generated it is only run once a month. >> >> >> >> RH> SQLite starts writing the WAL file from the beginning again on the >> first >> RH> write transaction that follows a checkpoint that ran to completion and >> RH> where there are no readers using the WAL file. A checkpoint will >> normally >> RH> run to completion if there are no readers still using prior >> transactions. >> RH> If there are readers on prior transactions, then the checkpoint cannot >> run >> RH> to completion since that would delete content out from under the >> readers. >> >> >> >> >> >> >> >> -- >> Best regards, >> Tegmailto:t...@djii.com >> >> -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL file size
Hello Richard, Appreciate the reply but, it doesn't address my question (unless I'm missing something). I get that checkpointing doesn't reduce the size of the WAL. Assuming a single writer and no readers, If I perform a transaction that generates a WAL file. Does it eventually get deleted when I finish my transaction? How about when I close the DB? I haven't updated to the most current SQlite because I was unclear about how persistent the WAL files actually were. Some of my DB's are 30 GB+. I don't mind the WAL's growing as long as they go away when the process is finished. Wednesday, November 30, 2011, 1:10:50 PM, you wrote: RH> On Wed, Nov 30, 2011 at 1:04 PM, Teg <t...@djii.com> wrote: >> Hello Richard, >> >> When does the WAL get trimmed down? If my transaction completes, does >> the WAL file get deleted? I've seen the WAL grow to 4 times my actual >> DB size so, I don't really want 160 Gigs of WAL hanging out when the >> process that generated it is only run once a month. >> RH> SQLite starts writing the WAL file from the beginning again on the first RH> write transaction that follows a checkpoint that ran to completion and RH> where there are no readers using the WAL file. A checkpoint will normally RH> run to completion if there are no readers still using prior transactions. RH> If there are readers on prior transactions, then the checkpoint cannot run RH> to completion since that would delete content out from under the readers. -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL file size
Hello Richard, When does the WAL get trimmed down? If my transaction completes, does the WAL file get deleted? I've seen the WAL grow to 4 times my actual DB size so, I don't really want 160 Gigs of WAL hanging out when the process that generated it is only run once a month. Wednesday, November 30, 2011, 10:02:34 AM, you wrote: RH> On Wed, Nov 30, 2011 at 4:41 AM, Sreekumar TP <sreekumar...@gmail.com>wrote: RH> Because we have experimentally determined that it is faster to overwrite an RH> existing file than to append to a file. So we don't truncate the WAL file RH> on a checkpoint, which makes subsequent writes to the WAL file go faster. RH> SQLite will truncate the WAL file down to the size specified by the "PRAGMA RH> journal_size_limit" setting, if you have set the journal_size_limit. >> >> -Sreekumar >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems with SQLite when used with MPI
Hello Gaurav, What does a debugger show you? C Tuesday, November 22, 2011, 2:07:38 AM, you wrote: GV> Here is the situation. I am using a cluster on which you can use MPI to GV> split the processing time on to different processors. I was using GV> PostgreSQL but since the data was on different server than the one I am GV> running the code, it was slowing down the simulation due to connection GV> overhead on PostgreSQL server. Then I switched to on-disk management GV> software and so I chose SQLite. I first ran it without MPI on server, and GV> it ran (compile g++). Then I switched to MPI (mpicc compiler), it is not GV> even running for one node (on which I am using one thread). It gives error GV> "Database or Disk is full". Further debugging revlealed that sqlite3_step() GV> was returning 1 and that means "SQL error or Database missing". It was GV> able to get the number of columns with the same database and SQL querry. I GV> dont understand why I am getting this error. GV> Gaurav GV> ___ GV> sqlite-users mailing list GV> sqlite-users@sqlite.org GV> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL and local UNC filenames
What I've seen is that windows makes a local copy of the memory mapped file. Perhaps in swap. If you watch your application using "ProcMon" you can see all the disk IO. That's how I noticed the copy. For my usage, only one process opens the DB so, maybe it's a non-issue. C Tuesday, November 8, 2011, 10:05:17 PM, you wrote: PI> On Mon, Nov 7, 2011 at 5:48 PM, Jean-Christophe Deschamps PI> <j...@antichoc.net> wrote: >> Pavel, >> >>> This is not a local file. Even if you use your hostname as netname >>> file is still retrieved through network stack. And I guess SAMBA >>> doesn't work well with memory mapped files (in addition to all >>> problems with locking). >> >> True but why did I get no error? Since MMF don't cope with network it would >> be good to get a no-no somewhere. I really don't know which layer should >> bark however. PI> I can't answer this question. Maybe it's something like all operations PI> are successful but memory mapping is not actually shared between PI> processes. So every process sees its own copy and assumes that no PI> other process works with database, thus database can be corrupted in PI> many possible ways. PI> Pavel PI> ___ PI> sqlite-users mailing list PI> sqlite-users@sqlite.org PI> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] does sqlite3_reset have a performance impact onqueries?
I'd like this clarified too. I specifically don't use transactions when I'm selecting. In fact, I'll select, then start a transaction later for inserting the results. Would I be better off wrapping the whole thing in a transaction? Wednesday, November 2, 2011, 9:13:20 AM, you wrote: BMI> Maybe my memory is fading but this is the first time I've heard BMI> anybody say the wrapping a BEBIN around a SELECT was needed. I'd BMI> swear it was always said it wasn't ever needed. >>From the docs BMI> http://www.sqlite.org/lang_transaction.html BMI> basically, any SQL command other than BMI> SELECT<http://www.sqlite.org/lang_select.html>) will BMI> automatically start a transaction if one is not already in effect BMI> Now you're saying SELECT always starts a transaction? BMI> Michael D. Black BMI> Senior Scientist BMI> Advanced Analytics Directorate BMI> Advanced GEOINT Solutions Operating Unit BMI> Northrop Grumman Information Systems BMI> ___ BMI> sqlite-users mailing list BMI> sqlite-users@sqlite.org BMI> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] core dump happened in sqlite3_step for 30 statements in BEGIN TRANSACTION executing every 10 Millisecond
Hello ChingChang,
How big is tempString? What kind of type is it? I wonder if you're
trashing the stack with the sprintf.
What happens if you replace all the sprinfs with some simple inserts
inserted directly into vector?
dbStatements.push_back("INSERT INTO...");
You need to simplify your test till you figure out what's wrong.
C
Tuesday, November 1, 2011, 7:09:09 PM, you wrote:
CH> Do you know why it goes to core dump?
CH> ChingChang
CH> The source code is shown as below,
CH> vector dbStatements;
CH> dbStatements.push_back( "BEGIN TRANSACTION;" );
CH> for ( int x = 0; x < 10; x++ ) {
CH> sprintf( tempString,
CH> "update utilization_table set
CH> utilization=%5.2f,sample=%d where slot='0' and device='cavium' and
CH> resource='bus' and sample='%d';",
CH> ntohd(msg->bus_util[x]),
CH> x,
CH> x );
CH> dbStatements.push_back( tempString );
CH> sprintf( tempString,
CH> "update utilization_table set
CH> utilization=%5.2f,sample=%d where slot='0' and device='cavium' and
CH> resource='icache' and sample='%d';",
CH> 100.00-ntohd(msg->inst_hit_rate[x]), // Convert to misses
CH> x,
CH> x );
CH> dbStatements.push_back( tempString );
CH> sprintf( tempString,
CH> "update utilization_table set
CH> utilization=%5.2f,sample=%d where slot='0' and device='cavium' and
CH> resource='dcache' and sample='%d';",
CH> 100.00-ntohd(msg->data_hit_rate[x]), // Convert to misses
CH> x,
CH> x );
CH> dbStatements.push_back( tempString );
CH> }
CH> dbStatements.push_back( "COMMIT;" );
CH> // populate the DB
CH> vector::iterator dbStatementsIter;
CH> SqlQuery oper_db(operDatabase, __FILE__, __LINE__);
CH> for ( dbStatementsIter = dbStatements.begin(); dbStatementsIter
CH> != dbStatements.end(); dbStatementsIter++ ) {
CH> oper_db.execw( *(dbStatementsIter) );
CH> }
CH> dbStatements.clear();
CH> The core dump is shown as below.
CH> #0 0x32e94b04 in raise () from /lib/libc.so.6
CH> #1 0x32e962f4 in abort () from /lib/libc.so.6
CH> #2 0x32e8c2a4 in __assert_fail () from /lib/libc.so.6
CH> #3 0x32ae60cc in ?? () from /ovn/lib/libsqlite3.mgmt-crd.so
CH> #4 0x32b4c324 in ?? () from /ovn/lib/libsqlite3.mgmt-crd.so
CH> #5 0x32ba12c0 in ?? () from /ovn/lib/libsqlite3.mgmt-crd.so
CH> #6 0x32b7926c in sqlite3_step () from /ovn/lib/libsqlite3.mgmt-crd.so
CH> #7 0x32b7a2c4 in sqlite3_exec () from /ovn/lib/libsqlite3.mgmt-crd.so
CH> #8 0x329a9630 in SqlQuery::execw () from /ovn/lib/libPlatform.so
CH> #9 0x329a98e8 in SqlQuery::execw () from /ovn/lib/libPlatform.so
CH> #10 0x10010290 in NpuMessageHandler::processUtilReport
CH> (this=, msg=,
CH> nbytes=) at cavium_driver.cpp:1387
CH> #11 0x10012808 in NpuMessageHandler::run (this=0x38be1008) at
cavium_driver.cpp:954
CH> #12 0x328a65b0 in Thread::start_thread () from
/ovn/lib/libCommon.mgmt-crd.so
CH> #13 0x3278b5cc in ?? () from /lib/libpthread.so.0
CH> #14 0x32f39b88 in clone () from /lib/libc.so.6
CH> ___
CH> sqlite-users mailing list
CH> sqlite-users@sqlite.org
CH> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Best regards,
Tegmailto:t...@djii.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using SQLite on Windows 64bit
As a programmer, I just build my own 64 bit version of Sqlite. You should try it, it's easy. Just a matter of selecting "x64" when I compile. For my customers, 64 bit installs now outnumber 32 bit. Sqlite works just fine... C Saturday, October 29, 2011, 8:03:55 AM, you wrote: AO> Yes, compiling to 32-bit is nice. It is like dancing at the tunes of "Earth AO> Wind and Fire", h, those were the days. Like the old good songs, the AO> 32bit apps are a good memory, many people like it, but they are a thing of AO> the past. We are developers and anyone of us who stays behind, is left AO> behind. Not having the option of SQLite-64 is a major drawback and will AO> leave us behind. So, come on folks, pull up your socks and let's get the AO> SQLite apps into the 21st century, shall we? AO> AO> ___ AO> sqlite-users mailing list AO> sqlite-users@sqlite.org AO> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How about a proper forum rather than an e-mail list
Hello Frank, I love forums and consider them far superior to email if only because it's easier to follow a topic with less quoting needed. The downside is that someone has to manage the forum. I've managed a forum for the past 10 years and there's a daily spam cleanup process and constant attacks and required upgrades. You have to set the tone and be pretty ruthless about flaming too. I'd like to see a forum. I just wouldn't want to manage it. C Tuesday, October 18, 2011, 5:32:46 AM, you wrote: FM> I think that the sqlite-users e-mail list has enough traffic to warrant a FM> proper forum. FM> Has this been considered? FM> FM> A proper forum also can contain several subject forums, e.g. FM> USER FORUMS: FM> Announcements FM> General FM> Help with SQL FM> News FM> DEVELOPERS CORNER: FM> News FM> General FM> OS specific FM> . FM> FM> There is a quite widespread, free Forum software that could be used: FM> http://www.simplemachines.org/ FM> http://en.wikipedia.org/wiki/Simple_Machines_Forum FM> FM> They support MySQL, SQLite and PostgreSQL as the underlying database. FM> FM> Well, just a thought. FM> FM> FM> /Frank Missel FM> ___ FM> sqlite-users mailing list FM> sqlite-users@sqlite.org FM> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Internal v. External BLOBs
My experience is that having larger blobs impacts performance too. It seems like SQlite struggles to seek over the blobs when traversing non-blob containing tables. I haven't characterized it beyond that. It might even be disk caching. I tend to keep my blobs in a different DB file than the tables that describe the blob contents though because of this. I have some 30 GB blob containing files. I use these DB's just for organization of the blobs. Performance wasn't that important. Wednesday, September 21, 2011, 5:29:16 AM, you wrote: AP> There is a problem with access to file in directory with big amount of files. AP> FS directory indicies are not really good. I did test 100 millions of 1k files AP> in SQLite and results were better than reading from set of directories in FS. AP> But for files about 1 Mb and more the SQLIte performance is not good. AP> Is any reason why SQLite big blobs reading may be slowly? The AP> performance of the BLOBS may limit performance of FTS and AP> other custom storage/index realizations (Spatialite, etc). And it's more AP> important I think. Especially when we need FTS index as fast hash index. AP> 2011/9/21 Richard Hipp <d...@sqlite.org>: >> If you are storing large BLOBs in SQLite, can you read them faster if they >> are stored directly in the database file, or can you get to them quicker if >> you store just a filename in the database and read the BLOB content from a >> separate file? >> >> We did some experiments to try to answer this question, and the results >> seemed interesting enough to share with the community at large. Bottom >> line: On Linux workstations, it is faster to store BLOBs in the database if >> they are less than about 100KB in size, and faster to store them in a >> separate file if they are larger than about 100KB. This is on Ubuntu with >> EXT4 and a fast SATA disk - your mileage may vary with different operating >> systems, filesystems, and hardware. >> >> The complete report is here: >> http://www.sqlite.org/intern-v-extern-blob.html >> >> -- >> D. Richard Hipp >> d...@sqlite.org >> _______ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strategies for protecting sql queries
PI> If someone was ambitious enough they could attach a debugger to your PI> application, extract key used to decrypt the database and obtain all PI> the sensitive information you have. So if you really want to protect PI> your database against those who is able to attach a debugger you PI> shouldn't use SQLite. PI> Pavel This is the key. It depends on how secure you need it to be. If it really needs to be secure then having the encryption key in memory or in the application means it's not all that secure. This isn't a limitation of SQLite though. It's a limitation of running a program on someone else's hardware. You could encrypt the SQL and then decrypt right before usage then clear it from memory. Won't stop a debugger but, it will stop them from pulling the strings out of your application directly. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite + unicode
Hello NOCaut,
I convert everything to UTF-8 for insert and then back to ascii or
unicode when I pull the data from the DB.
C
Thursday, August 11, 2011, 4:20:36 AM, you wrote:
N> I now how work with sqlite guys
N> my problem: in const char * and i wont wchar_t*. becouse wchar_t* -
N> unicode type understand
N> int sqlite3_exec(
N> sqlite3*, /* An open database */
N> const char *sql, /* SQL to be executed */
N> sqlite3_callback, /* Callback function */
N> void *, /* 1st argument to callback function */
N> char **errmsg /* Error msg written here */
N> );
N>sqlite3 *db;
N> char *zErrMsg = 0;
N> int rc;
N> rc = sqlite3_open("c:\\test.db", );
N>
N> rc = sqlite3_exec(db, "Select * from table ", callback, 0, );
N> this code return char I want use wchar_t* for read unicode.
N> Thanks!
--
Best regards,
Tegmailto:t...@djii.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Read only scaling optimization
Hello Drew, Why multiple threads? What kind of performance do you get if you only use a single thread? Is it one thread per database perhaps? C Tuesday, August 9, 2011, 4:46:13 PM, you wrote: DK> We have an application that does not scale well especially 8 and 16 DK> threads we are getting about 10% at best to -50% at worst (2 and 4 are okay DK> aprox. 40-50%). DK> We have narrowed the problem down to the SQLite code. DK> Looking for best practices from anyone that overcame scaling issues. DK> First we generate 35 different SQLite databases, this is done in a separate DK> program and performance is not an issue. DK> The application is a C++ app. that accesses all the databases in a read DK> only style. Size of database range from 1MB to 10GB. DK> Each thread does the following in a loop DK> 1. Grab a record from an external system (not SQLite) DK> 2. Runs several SQLite queries to process the record DK> We are currently using 3.6.14 (has this been improved in new versions?) DK> Is there any compile time options or pragma's that are specifically DK> engineered at optimizing for read only and/or scaling? DK> Thank you in advance, DK> Drew DK> ___ DK> sqlite-users mailing list DK> sqlite-users@sqlite.org DK> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Deep copy of 2 sqlite3*?
Hello Nikki, I'd suggest either not doing it (prevent the copy constructor from compiling by declaring it private) or doing it by passing ownership of the connection around as if it was a socket or file handle. That's how I do sockets. If my socket class gets copy constructed to a new class, that class becomes the "owner" of the socket and no further socket operations are permitted in the original class. The problem with opening a new connection in the new class constructor is that it's not really in the same state as the original connection. C Tuesday, August 2, 2011, 10:20:21 PM, you wrote: NT> Hi sqlite-users! NT> It's just that I'm writing a copy constructor of my own mini sqlite NT> wrapper, and I ended up having to do a deep copy of a sqlite3* points to a NT> SQLite connection. And I'm just wondering is it okay that I just do it with NT> memcpy(), by digging into the code for the definition of struct sqlite3 and NT> count the total bytes of this struct? NT> Any ideas or suggestions are appreciated! NT> ___ NT> sqlite-users mailing list NT> sqlite-users@sqlite.org NT> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Field drop work around
Hello Jack, I just migrate the tables forward. Generate a new one in my program and do a bulk insert from the old to the new, then drop the old one. At other times, I've just re-purposed the unused field. C Tuesday, August 2, 2011, 7:03:52 AM, you wrote: JH> Hello all, JH> Is there a workaround for the lack of support for dropping JH> fields? I have an application and need to evolve the database JH> schema as features are added and removed. Leaving fields sitting JH> inside the database that are no longer used will lead to a schema JH> that is difficult to understand. Especially so when the field is JH> marked as NOT NULL. Years from now I will need to supply a value JH> for a field when inserting a row that has long ago ceased to be used. JH> Any ideas how I can remove unused fields from the database would be appreciated. JH> Regards, JH> Jack Hughes JH> ___ JH> sqlite-users mailing list JH> sqlite-users@sqlite.org JH> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite3.dll for Win 64
Hello Everton, EV> Don't have any easy way to make this dll? There's a lot of 64bits systems EV> out there that will need this dll. I just compile it into my program and don't bother with DLL's. I find that DLL's in general make my software less reliable so, I avoid them. Static linking all the way C ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Selected string differs to Inserted one
Hello Andrew, I convert all my strings the UTF-8 before insert or selecting. You probably need to look into something like that too. Filenames for the DB files have to be UTF-8 too or you'll have problems opening files sometimes. My test folder has an umlaut in the path so, this code gets exercised every time I run my program. C Sunday, July 24, 2011, 10:39:51 AM, you wrote: AL> Dear All AL> AL> I am very new to SQLite and am trying to convert a Windows Forms AL> C#.NET (VS2010 SP1) application from an Access database to an AL> SQLite one. All seems to have gone extremely well but I have come AL> across one problem that has held me up for several days now. AL> AL> I have a table with a TEXT field that does not return the same AL> string that was inserted. The character that misbehaves is Greek AL> letter "Ø". In fact any character from the high end of the ANSII AL> or ASCII table shows the problem. AL> AL> Since I have done nothing special in creating the database I AL> believe it is encoded UTF-8 by default. I assume that my inserted AL> string is similarly encoded UTF-8 since I have done nothing AL> special in my C# code to change this. The data in the database AL> seems to be correct since the SQLite Administrator program correctly displays the data. AL> AL> Why does the Selected data come back wrong, and how can I correct AL> this. Clearly this is a bit of a non-problem otherwise every AL> other .NET user would be screaming but I have searched for several AL> days now without finding a solution. AL> AL> Can anyone help me please? AL> Thanks in advance AL> ~A AL> Andrew Leeder AL> AL> . AL> AL> ___ AL> sqlite-users mailing list AL> sqlite-users@sqlite.org AL> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PRAGMA bug in 3.7.7 (but fine in 3.7.6.3)
I had to back away from 3.7.7 too because my program hung during
startup. Wonder if it's related to this? I haven't looked any deeper
at it. It hung, I switched back to my older library and it worked.
C
Sunday, June 26, 2011, 3:59:33 PM, you wrote:
GS> Hey all,
GS> There is a bug in 3.7.7 that affects Apache Subversion. We've verified
GS> that it is this latest release, as prior releases have worked just
GS> fine for us.
GS> Below is the reproduction script, thanks to Daniel Shahaf. With this
GS> pragma not working, we cannot even start up :-(
GS> Please help!
GS> Thanks,
GS> -g
GS> ps. maybe this belongs on -dev, but I'm pending on its moderated
subscription...
GS> [[[
GS> #include
GS> #include
GS> #include "sqlite3.h"
GS> #define BUSY_TIMEOUT 1
GS> int main(void)
GS> {
GS> sqlite3 *db3;
GS> const char *path = "foo.db";
GS> int flags = SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE;
GS> #ifdef SQLITE_OPEN_NOMUTEX
GS> flags |= SQLITE_OPEN_NOMUTEX;
GS> #endif
GS> assert(SQLITE_OK == sqlite3_open_v2(path, , flags, NULL));
GS> assert(SQLITE_OK == sqlite3_busy_timeout(db3, BUSY_TIMEOUT));
GS> assert(SQLITE_OK == sqlite3_busy_timeout(db3, BUSY_TIMEOUT));
GS> {
GS>char *errmsg;
GS>int err = sqlite3_exec(db3, "PRAGMA case_sensitive_like=1;", NULL,
GS> NULL, );
GS>if (err != SQLITE_OK)
GS> printf("Error %d: %s\n", err, errmsg), sqlite3_free(errmsg);
GS> }
GS> return 0;
GS> }
GS> ]]]
GS> _______
GS> sqlite-users mailing list
GS> sqlite-users@sqlite.org
GS> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Best regards,
Tegmailto:t...@djii.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Do I need to migrate to MySQL?
We use MySQL on a fairly high end PC server running Linux. The database is about 300-400 gigs. Front end of PHP and apache. It's been reliable for a couple years now and seems to have a bunch of headroom. I wrote a wrapper so my SQLite code (with minor modification) can feed MySQL instead of SQlite. In fact, the thing that feeds the MySQL DB reads Sqlite DB's and then feeds this data into MySQL every 15 minutes or so. I'd have no problem recommending MySQL for mission critical uses. Postgres might be better but, MySQL's been good enough. C Friday, June 3, 2011, 2:19:44 PM, you wrote: DD> MySQL should be avoided like the plague. Use Postgres instead if you have to DD> switch to a larger SQL DBMS. But hopefully the help you've gotten so far will DD> extend your mileage with SQLite and you won't have to switch to anything yet. -- DD> Darren Duncan DD> Ian Hardingham wrote: >> Guys, the server for this game - >> >> http://www.frozensynapse.com >> >> uses SQLite. We've had an unexpectedly successful launch which has >> resulted in the server being swamped with players, and I'm trying to >> optimise everywhere I can. I've always been under the impression that >> SQLite is pefectly fast and it's the scripting language I wrote the >> server in which is too blame. (Yes, I know writing a back-end in a >> single-threaded scripting language is an absolutely terrible idea). >> However, everyone in the industry I talk to says that SQLite must be one >> of the problems. >> >> I may be looking at a complete re-write. I may also need to have a >> solution which scales beyond one machine. Can anyone give me advice on >> this matter specifically? >> >> (The video on that website at 2.04 gives a good idea of what kind of >> functions are being powered by the database). >> >> Thanks, >> Ian DD> _______ DD> sqlite-users mailing list DD> sqlite-users@sqlite.org DD> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compile error's on Visual Studio 2010
Hello Jay, I haven't found this to be the case. I have numerous C only library's I compile and I don't have to change the defaults to compile them. There is an option to force C++ compiles but, I don't believe it's on by default. JAK> In most default setups, Visual Studio insists on compiling .c files JAK> with the C++ compiler, even though-- as this very example points JAK> out-- the languages are different enough that this behavior seems JAK> questionable. -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compile error's on Visual Studio 2010
Hello Jan, Move it into it's own project, make it a static lib and turn down the warning level just for this project. That's what I do anyway. I'm not willing to touch the code but, that seems to be the only other solution. Sunday, May 8, 2011, 6:50:31 AM, you wrote: JB> Hi, JB> JB> I am new to sqlite3 and just downloaded the code trying to test it from a JB> C++ application using Visual Studio 2010. I just created a class and JB> included the sqlite3.c and sqlite3.h from amalgamation-3070602 directly. JB> JB> The header compiles fine, but on the sqlite3.c I get typecast errors. This JB> is because my compiler is set to a high warning level and sqlite3 code does JB> things like converting from void* to struct pointers. JB> JB> Is there any way I can get around this without lowering the compiler's JB> warning level? JB> JB> Jan JB> ___ JB> sqlite-users mailing list JB> sqlite-users@sqlite.org JB> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to reuse a prepared statement
I got bit by this the other day. I typically just used "reset" but, if you have a case where you're looping through an insert and you don't always set all values (say a string is empty sometimes so, you forgo the bind), the previous value set will get inserted where you expected blanks to go. Clearly, it was my fault because I assumed reset cleared the old values. I've since changed my wrapper to clear with each reset. >> If >> I understand things correctly, you call sqlite3_reset() to reuse a >> prepaired statement, why do you call sqlite3_clear_bindings()? IT> You don't have to. Personally, I've never once found a use for it. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite3 memory leaks in my c++ dll application
Hello Khanh,
Until you confirm the memory leak, I doubt anyone will respond. For
example, when my program exits, I abandon a bunch of allocated memory
to speed exit. VC says that's a memory leak but, it's not, because
it's not leaking memory while I'm using it. I purposely abandon
allocated memory on exit to speed exit processing (deallocating 2 gigs
of RAM when I'm exiting anyway is pointless).
The question is whether it's leaking RAM while you use it, not if it
leaks it when you exit (which is when VC checks).
C
Wednesday, April 20, 2011, 12:00:57 PM, you wrote:
KN> Hi SQLite3 community,
KN> Please forgive me this panic, incorrect bug report. The cause of memory
KN> leaks was from another my software component. The SQLite3.dll is working
KN> greatly with my solution. However, it was memory leaks detection from VS2008
KN> when I built the SQLite3 with Sqlite3.c in my solution.
KN> If you know about this, please give me some hints on how to set flags in my
KN> database module(vcproj) to eliminate the memory leaks. I would greatly
KN> appreciate your kindness.
KN> The database vcproj DLL built with VS2008 C++ with this flag: Multi-threaded
KN> Debug DLL (/MDd). Accessing the DLL via GetProcAddress() calls.
KN> Sincerely and Thank you,
KN> Khanh
KN> On Mon, Apr 18, 2011 at 8:56 AM, Khanh Nguyen <kn95...@gmail.com> wrote:
>> Hi,
>>
>>
>>
>> My name is Khanh Nguyen and currently using SQLite3.DLL 3.7.6 in my
>> application (DLL built with VS2008 C++ with this flag: Multi-threaded Debug
>> DLL (/MDd). This DLL is called by other DLL via GetProcAddress to access a
>> blob database. The access and blob database is in very good operation.
>> However, whenever I exited the application, the VS2008 debugger reported
>> this memory leak dectection:
>>
>>
>>
>> The DLL has some memory leaks that I have captured here:
>>
>>
>>
>> The thread 'Win32 Thread' (0x368) has exited with code 0 (0x0).
>>
>> Detected memory leaks!
>>
>> Dumping objects ->
>>
>> {8390} normal block at 0x01364C70, 64 bytes long.
>>
>> Data: 63 3A 2F 44 45 56 2F 50 6C 61 74 66 6F 72 6D 2F
>>
>> {8382} normal block at 0x0138C9C0, 32 bytes long.
>>
>> Data: <7Zl7ji/F9x+bOgbG> 37 5A 6C 37 6A 69 2F 46 39 78 2B 62 4F 67 62 47
>>
>> {8380} normal block at 0x0138B938, 32 bytes long.
>>
>> Data: 50 6F 6C 69 63 79 44 61 74 61 50 61 63 6B 2E 69
>>
>> {8377} normal block at 0x01391F48, 448 bytes long.
>>
>> Data: <> 10 1F F3 02 00 00 00 00 00 00 00 00 CD CD CD CD
>>
>> {7174} normal block at 0x01391DC8, 64 bytes long.
>>
>> Data: 63 3A 2F 44 45 56 2F 50 6C 61 74 66 6F 72 6D 2F
>>
>> {7166} normal block at 0x013621D8, 32 bytes long.
>>
>> Data: <7Zl7ji/F9x+bOgbG> 37 5A 6C 37 6A 69 2F 46 39 78 2B 62 4F 67 62 47
>>
>> {7164} normal block at 0x01386690, 32 bytes long.
>>
>> Data: 50 6F 6C 69 63 79 44 61 74 61 50 61 63 6B 2E 69
>>
>> {7161} normal block at 0x01390430, 448 bytes long.
>>
>> Data: <> 10 1F F3 02 00 00 00 00 00 00 00 00 CD CD CD CD
>>
>> {7108} normal block at 0x0138CDC8, 64 bytes long.
>>
>> Data: 63 3A 2F 44 45 56 2F 50 6C 61 74 66 6F 72 6D 2F
>>
>> {7100} normal block at 0x01386320, 32 bytes long.
>>
>> Data: 45 4E 74 35 4B 6B 59 48 52 71 65 55 2F 69 4C 52
>>
>> {7098} normal block at 0x0138BCD8, 32 bytes long.
>>
>> Data: 50 6F 6C 69 63 79 44 61 74 61 50 61 63 6B 2E 62
>>
>> {7095} normal block at 0x0138D530, 448 bytes long.
>>
>> Data: <> 10 1F F3 02 00 00 00 00 00 00 00 00 CD CD CD CD
>>
>> {7033} normal block at 0x013796D8, 64 bytes long.
>>
>> Data: 63 3A 2F 44 45 56 2F 50 6C 61 74 66 6F 72 6D 2F
>>
>> {7025} normal block at 0x0136DED0, 32 bytes long.
>>
>> Data: <5U/jyx2txHeUQUe/> 35 55 2F 6A 79 78 32 74 78 48 65 55 51 55 65 2F
>>
>> {7023} normal block at 0x013658D0, 32 bytes long.
>>
>> Data: 4E 65 74 77 6F 72 6B 53 65 74 74 69 6E 67 33 2E
>>
>> {7020} normal block at 0x01389BF0, 448 bytes long.
>>
>> Data: <> 10 1F F3 02 00 00 00 00 00 00 00 00 CD CD CD CD
>>
>> Object dump complete.
>>
>> The program '[5432] DebugConsole.exe: Native' has exited with code 0 (0x0).
>>
>>
>> Please help me resolve this memory leak issue.
>>
>> Sincerely,
>>
>> Khanh
>>
KN> ___
KN> sqlite-users mailing list
KN> sqlite-users@sqlite.org
KN> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Best regards,
Tegmailto:t...@djii.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
