Re: [sqlite] Writing an image to the database as a BLOB
Have you confirmed that the pBuffer that would read out is byte for byte the same as the bmBytes that you used to insert in to the database? If they are the same, then sqlite did its job (or rather you called it correctly...), and the trouble is somewhere else. David On Fri, 2008-08-29 at 14:13 -0400, Jared Miller wrote: > Hello, > > I am having trouble figuring out how to successfully write an image to the > SQLite database as a Blob, using C++. > > I have an HBITMAP that I would like to be able to store to and retrieve from > the DB. If I understand what I have read correctly, I am supposed to write > out the actual byte data to the DB. Here is what I have done so far. > > I pass in pbyBitmap as the bmBytes parameter to this function: > ImportPageImage(CPage* pPage, BYTE* bmBytes, DWORD bmSize), which calls the > code below. . . > > //prepare query > static const WCHAR tblInsertBlob[] = _T("Insert into [tblBlob] ([Data]) > values (?)"); > SQL_HANDLE blobHandle = m_pSqliteDB->PrepareQuery16(tblInsertBlob); //calls > sqlite3_prepare16() > > //bind blob > m_pSqliteDB->BindBlob(blobHandle, 1, (void *)bmBytes, bmSize); //calls > sqlite3_bind_blob (bmBytes is the pData param) > > Then I call StepQuery to execute it, and then I close the query. > sqlite3_bind_blob() returns SQLITE_OK when I run it, so it does not seem to > be encountering an error there. > > Something apparently gets written to the database, but it does not seem to be > correct. When I try to retrieve and display my image, it is all black (which > is how bitmaps look when there is no data). > > I think that the problem is coming from writing the BLOB to the database, but > I am not entirely sure. Just in case it is getting written properly and I am > not reading it from the database correctly, I will show you how I pulled it > from the DB. > > //prepare blob > sqlite3_blob* pBlob = NULL; > sqlite3_blob_open(m_sqliteDB, "main", "tblBlob", "Data", iBlobID, FALSE, > &pBlob); > > BLOB_HANDLE hBlob = pBlob; > int size = sqlite3_blob_bytes(hBlob); //works correctly, returns 998058 > BYTE* pBuffer = g_MemMgr.AllocDataBuffer(size); > > sqlite3_blob_read(hBlob, pBuffer, size, 0); //returns SQLITE_OK > sqlite3_blob_close(hBlob); > > I then try to make a bitmap out of the bytes in pBuffer, but when I do, it > turns out all black (as I mentioned earlier). > > Do I have the concept correct? And if so, what part of my current code should > I change to be able to use my DB to store image data? > > Thanks a lot. > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite 3.6.1 memory leak?
Hi, Since I updated to SQLite 3.6.1 I have a memory leak when my application exits. If I compile using SQLite 3.5.8 I don't have the memory leak. VS 2005 dump: Detected memory leaks! Dumping objects -> c:\dev\mescruiser\lib\sqlite\sqlite3.c(11938) : {4754} normal block at 0x01BFC460, 48 bytes long. Data: <( > 28 00 00 00 00 00 00 00 B0 B8 15 00 FF FF FF FF Object dump complete. Using LeakFinder/StackWalker it seem that the memory leak originate from sqlite3_open. Is this a real memory leak or I'm doing something wrong? Thanks Ulric ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Writing an image to the database as a BLOB
And what is the type of bmpFile? I guess what I'm getting at here is that your real issue is more the problem of "How do I serialize a HBITMAP structure to an array of bytes and restore it." more than "how do I save it to the database". I think if you can take the database out of the picture for a moment and simply get the saving of the bitmap to an array of bytes in memory and re-loading working, you're going to be fine. On Fri, Aug 29, 2008 at 2:38 PM, Jared Miller <[EMAIL PROTECTED]> wrote: > Yes I am able to do that. I fill out the BITMAPFILEHEADER and > BITMAPINFOHEADER information for the image, and then I do this: > > bmpFile.Write(&bitmapfileheader, sizeof(BITMAPFILEHEADER)); > bmpFile.Write(&bitmapinfoheader, sizeof(BITMAPINFOHEADER)); > bmpFile.Write(pbyBitmap, size); //pbyBitmap is the actual byte data > > This has worked correctly for me. I can load it from the file using > SHLoadDIBitmap(). > > That is the way I wrote it to a .bmp file. > > Jeffrey Becker wrote: >> Are you able to load and save the bitmap to a file? >> >> On Fri, Aug 29, 2008 at 2:13 PM, Jared Miller <[EMAIL PROTECTED]> wrote: >> >>> Hello, >>> >>> I am having trouble figuring out how to successfully write an image to the >>> SQLite database as a Blob, using C++. >>> >>> I have an HBITMAP that I would like to be able to store to and retrieve >>> from the DB. If I understand what I have read correctly, I am supposed to >>> write out the actual byte data to the DB. Here is what I have done so far. >>> >>> I pass in pbyBitmap as the bmBytes parameter to this function: >>> ImportPageImage(CPage* pPage, BYTE* bmBytes, DWORD bmSize), which calls the >>> code below. . . >>> >>> //prepare query >>> static const WCHAR tblInsertBlob[] = _T("Insert into [tblBlob] ([Data]) >>> values (?)"); >>> SQL_HANDLE blobHandle = m_pSqliteDB->PrepareQuery16(tblInsertBlob); //calls >>> sqlite3_prepare16() >>> >>> //bind blob >>> m_pSqliteDB->BindBlob(blobHandle, 1, (void *)bmBytes, bmSize); //calls >>> sqlite3_bind_blob (bmBytes is the pData param) >>> >>> Then I call StepQuery to execute it, and then I close the query. >>> sqlite3_bind_blob() returns SQLITE_OK when I run it, so it does not seem to >>> be encountering an error there. >>> >>> Something apparently gets written to the database, but it does not seem to >>> be correct. When I try to retrieve and display my image, it is all black >>> (which is how bitmaps look when there is no data). >>> >>> I think that the problem is coming from writing the BLOB to the database, >>> but I am not entirely sure. Just in case it is getting written properly and >>> I am not reading it from the database correctly, I will show you how I >>> pulled it from the DB. >>> >>> //prepare blob >>> sqlite3_blob* pBlob = NULL; >>> sqlite3_blob_open(m_sqliteDB, "main", "tblBlob", "Data", iBlobID, FALSE, >>> &pBlob); >>> >>> BLOB_HANDLE hBlob = pBlob; >>> int size = sqlite3_blob_bytes(hBlob); //works correctly, returns 998058 >>> BYTE* pBuffer = g_MemMgr.AllocDataBuffer(size); >>> >>> sqlite3_blob_read(hBlob, pBuffer, size, 0); //returns SQLITE_OK >>> sqlite3_blob_close(hBlob); >>> >>> I then try to make a bitmap out of the bytes in pBuffer, but when I do, it >>> turns out all black (as I mentioned earlier). >>> >>> Do I have the concept correct? And if so, what part of my current code >>> should I change to be able to use my DB to store image data? >>> >>> Thanks a lot. >>> >>> >>> >>> ___ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >>> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> >> > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Writing an image to the database as a BLOB
Yes I am able to do that. I fill out the BITMAPFILEHEADER and BITMAPINFOHEADER information for the image, and then I do this: bmpFile.Write(&bitmapfileheader, sizeof(BITMAPFILEHEADER)); bmpFile.Write(&bitmapinfoheader, sizeof(BITMAPINFOHEADER)); bmpFile.Write(pbyBitmap, size); //pbyBitmap is the actual byte data This has worked correctly for me. I can load it from the file using SHLoadDIBitmap(). That is the way I wrote it to a .bmp file. Jeffrey Becker wrote: > Are you able to load and save the bitmap to a file? > > On Fri, Aug 29, 2008 at 2:13 PM, Jared Miller <[EMAIL PROTECTED]> wrote: > >> Hello, >> >> I am having trouble figuring out how to successfully write an image to the >> SQLite database as a Blob, using C++. >> >> I have an HBITMAP that I would like to be able to store to and retrieve from >> the DB. If I understand what I have read correctly, I am supposed to write >> out the actual byte data to the DB. Here is what I have done so far. >> >> I pass in pbyBitmap as the bmBytes parameter to this function: >> ImportPageImage(CPage* pPage, BYTE* bmBytes, DWORD bmSize), which calls the >> code below. . . >> >> //prepare query >> static const WCHAR tblInsertBlob[] = _T("Insert into [tblBlob] ([Data]) >> values (?)"); >> SQL_HANDLE blobHandle = m_pSqliteDB->PrepareQuery16(tblInsertBlob); //calls >> sqlite3_prepare16() >> >> //bind blob >> m_pSqliteDB->BindBlob(blobHandle, 1, (void *)bmBytes, bmSize); //calls >> sqlite3_bind_blob (bmBytes is the pData param) >> >> Then I call StepQuery to execute it, and then I close the query. >> sqlite3_bind_blob() returns SQLITE_OK when I run it, so it does not seem to >> be encountering an error there. >> >> Something apparently gets written to the database, but it does not seem to >> be correct. When I try to retrieve and display my image, it is all black >> (which is how bitmaps look when there is no data). >> >> I think that the problem is coming from writing the BLOB to the database, >> but I am not entirely sure. Just in case it is getting written properly and >> I am not reading it from the database correctly, I will show you how I >> pulled it from the DB. >> >> //prepare blob >> sqlite3_blob* pBlob = NULL; >> sqlite3_blob_open(m_sqliteDB, "main", "tblBlob", "Data", iBlobID, FALSE, >> &pBlob); >> >> BLOB_HANDLE hBlob = pBlob; >> int size = sqlite3_blob_bytes(hBlob); //works correctly, returns 998058 >> BYTE* pBuffer = g_MemMgr.AllocDataBuffer(size); >> >> sqlite3_blob_read(hBlob, pBuffer, size, 0); //returns SQLITE_OK >> sqlite3_blob_close(hBlob); >> >> I then try to make a bitmap out of the bytes in pBuffer, but when I do, it >> turns out all black (as I mentioned earlier). >> >> Do I have the concept correct? And if so, what part of my current code >> should I change to be able to use my DB to store image data? >> >> Thanks a lot. >> >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Writing an image to the database as a BLOB
Are you able to load and save the bitmap to a file? On Fri, Aug 29, 2008 at 2:13 PM, Jared Miller <[EMAIL PROTECTED]> wrote: > Hello, > > I am having trouble figuring out how to successfully write an image to the > SQLite database as a Blob, using C++. > > I have an HBITMAP that I would like to be able to store to and retrieve from > the DB. If I understand what I have read correctly, I am supposed to write > out the actual byte data to the DB. Here is what I have done so far. > > I pass in pbyBitmap as the bmBytes parameter to this function: > ImportPageImage(CPage* pPage, BYTE* bmBytes, DWORD bmSize), which calls the > code below. . . > > //prepare query > static const WCHAR tblInsertBlob[] = _T("Insert into [tblBlob] ([Data]) > values (?)"); > SQL_HANDLE blobHandle = m_pSqliteDB->PrepareQuery16(tblInsertBlob); //calls > sqlite3_prepare16() > > //bind blob > m_pSqliteDB->BindBlob(blobHandle, 1, (void *)bmBytes, bmSize); //calls > sqlite3_bind_blob (bmBytes is the pData param) > > Then I call StepQuery to execute it, and then I close the query. > sqlite3_bind_blob() returns SQLITE_OK when I run it, so it does not seem to > be encountering an error there. > > Something apparently gets written to the database, but it does not seem to be > correct. When I try to retrieve and display my image, it is all black (which > is how bitmaps look when there is no data). > > I think that the problem is coming from writing the BLOB to the database, but > I am not entirely sure. Just in case it is getting written properly and I am > not reading it from the database correctly, I will show you how I pulled it > from the DB. > > //prepare blob > sqlite3_blob* pBlob = NULL; > sqlite3_blob_open(m_sqliteDB, "main", "tblBlob", "Data", iBlobID, FALSE, > &pBlob); > > BLOB_HANDLE hBlob = pBlob; > int size = sqlite3_blob_bytes(hBlob); //works correctly, returns 998058 > BYTE* pBuffer = g_MemMgr.AllocDataBuffer(size); > > sqlite3_blob_read(hBlob, pBuffer, size, 0); //returns SQLITE_OK > sqlite3_blob_close(hBlob); > > I then try to make a bitmap out of the bytes in pBuffer, but when I do, it > turns out all black (as I mentioned earlier). > > Do I have the concept correct? And if so, what part of my current code should > I change to be able to use my DB to store image data? > > Thanks a lot. > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Writing an image to the database as a BLOB
Hello, I am having trouble figuring out how to successfully write an image to the SQLite database as a Blob, using C++. I have an HBITMAP that I would like to be able to store to and retrieve from the DB. If I understand what I have read correctly, I am supposed to write out the actual byte data to the DB. Here is what I have done so far. I pass in pbyBitmap as the bmBytes parameter to this function: ImportPageImage(CPage* pPage, BYTE* bmBytes, DWORD bmSize), which calls the code below. . . //prepare query static const WCHAR tblInsertBlob[] = _T("Insert into [tblBlob] ([Data]) values (?)"); SQL_HANDLE blobHandle = m_pSqliteDB->PrepareQuery16(tblInsertBlob); //calls sqlite3_prepare16() //bind blob m_pSqliteDB->BindBlob(blobHandle, 1, (void *)bmBytes, bmSize); //calls sqlite3_bind_blob (bmBytes is the pData param) Then I call StepQuery to execute it, and then I close the query. sqlite3_bind_blob() returns SQLITE_OK when I run it, so it does not seem to be encountering an error there. Something apparently gets written to the database, but it does not seem to be correct. When I try to retrieve and display my image, it is all black (which is how bitmaps look when there is no data). I think that the problem is coming from writing the BLOB to the database, but I am not entirely sure. Just in case it is getting written properly and I am not reading it from the database correctly, I will show you how I pulled it from the DB. //prepare blob sqlite3_blob* pBlob = NULL; sqlite3_blob_open(m_sqliteDB, "main", "tblBlob", "Data", iBlobID, FALSE, &pBlob); BLOB_HANDLE hBlob = pBlob; int size = sqlite3_blob_bytes(hBlob); //works correctly, returns 998058 BYTE* pBuffer = g_MemMgr.AllocDataBuffer(size); sqlite3_blob_read(hBlob, pBuffer, size, 0); //returns SQLITE_OK sqlite3_blob_close(hBlob); I then try to make a bitmap out of the bytes in pBuffer, but when I do, it turns out all black (as I mentioned earlier). Do I have the concept correct? And if so, what part of my current code should I change to be able to use my DB to store image data? Thanks a lot. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] problem using random() in queries
mySQL differs from MS SQL in it random function handling: SELECT i, RAND() AS R1, RAND() AS R2 FROM z where RAND() < .4; iR1R2 1 0.531666 0.692986 3 0.743755 0.906643 4 0.789811 0.04321 6 0.977431 0.576784 8 0.284047 0.336876 Different values for R1 and R2 (each call to RAND() produces different values), as Noah expects, including the RAND() criterion. However, the following output differs from the MS SQL results: SELECT T1.i, T1.RNDValue FROM (select i, RAND() AS RNDValue from z) T1 WHERE T1.RNDValue > .7 i RNDValue 5 0.902673 9 0.799401 This apparently only calls RAND() once. It consistently returns only distinct values for RNDValue, all being greater than .7. Maybe it is a matter of documentation rather than conforming to some standard practice. Chris On Fri, 29 Aug 2008, Noah Hart wrote: > Richard, Before you "fix" it, I'm not convinced it is broken. > > From MS SQL server > > create table _names (N varchar(5)); > insert into _names values('a'); > insert into _names values('b'); > insert into _names values('c'); > insert into _names values('d'); > insert into _names values('e'); > > select N, RAND() as RNDValue from _names > a 0.301745013642105 > b 0.301745013642105 > c 0.301745013642105 > d 0.301745013642105 > e 0.301745013642105 > > > select N, RAND() as RNDValue from _names where RAND() >= 0.5; > a 0.0427909435260437 > b 0.0427909435260437 > c 0.0427909435260437 > d 0.0427909435260437 > e 0.0427909435260437 > > From the SQL manual: > " When you use an algorithm based on GETDATE to generate seed values, > RAND can still generate duplicate values if the calls to RAND are made > within the interval of the smallest datepart used in the algorithm. This > is especially likely when the calls to RAND are included in a single > batch. Multiple calls to RAND in a single batch can be executed within > the same millisecond. This is the smallest increment of DATEPART. In > this case, incorporate a value based on something other than time to > generate the seed values." > > > So, it comes down to definition: > I would expect that multiple calls to random always return different > values, even if in the same line. > > So the following should give different results for each call to random() > > Select random(), random(), random() > > And the following as well: > > Select random() where random() >0 > > The fact that we are aliasing random by a column name makes no > difference to me. > > Therefore: If the "random" function in sqlite is defined as having a > different value every time it is called, and we explain that this is > true, even if aliased, then the current implementation works correctly > and no work, other than documentation is needed. > > > Regards, > > Noah > > > -Original Message- > > > OK. Even though this kind of thing is probably an abuse of SQL, I'm > working on ticket #3343. Just for the record, I'd like everybody to > know that the following is really, really hard to do correctly and is > going to require a lot of extra code in SQLite - code that nobody will > ever use in practice: > > SELECT random()%5 AS x, count(*) FROM tab GROUP BY x; > > D. Richard Hipp > [EMAIL PROTECTED] > > > > > CONFIDENTIALITY NOTICE: > This message may contain confidential and/or privileged information. If you > are not the addressee or authorized to receive this for the addressee, you > must not use, copy, disclose, or take any action based on this message or any > information herein. If you have received this message in error, please advise > the sender immediately by reply e-mail and delete this message. Thank you for > your cooperation. > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Pending Queue?
In Section 7.0, Transaction Control At The SQL Level, at http://www.sqlite.org/lockingv3.html, it says: "If the SQL COMMIT command turns autocommit on and the autocommit logic then tries to commit change but fails because some other process is holding a SHARED lock, then autocommit is turned back off automatically. This allows the user to retry the COMMIT at a later time after the SHARED lock has had an opportunity to clear." Also in section 3.0 it says: "Only one EXCLUSIVE lock is allowed on the file and no other locks of any kind are allowed to coexist with an EXCLUSIVE lock." So, if process A has an EXCLUSIVE lock for writing and process B attempts a COMMIT, process B immediately receives an SQLITE_BUSY error, and has to start over again. There is no queue then. Does this create a problem where process B never gets to write because process A (C, D, etc.) are continually getting in ahead of B? B Can't get a PENDING lock to keep it's position in line. It seems that there should be a queue of Pending locks so processes can keep their place in line? So this could be implemented as a COMMIT and there would be a specified timeout as a PRAGMA command? If the timeout was set to 0 (default), then there would be no queue and it would be backwards compatible. Of course, the Pending queue would work against all other lock conditions currently in effect on the database, not just EXCLUSIVE's. Good idea, bad idea, false alarm? Scott The information contained in this message and any attachment may be proprietary, confidential, and privileged or subject to the work product doctrine and thus protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify me immediately by replying to this message and deleting it and all copies and backups thereof. Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] problem using random() in queries
I agree with you here. It is a temporary table that should "FIX" the values. Interestingly select name, RNDValue from ( select 'name', random() as RNDValue ) where RNDValue > 0; only calls random once and works as expected. 0|Trace|0|0|0|explain select name, RNDValuefrom (select 'name', random() as RNDValue)where RNDValue > 0;|00| 1|OpenEphemeral|0|2|0||00| 2|Goto|0|17|0||00| 3|String8|0|1|0|name|00| 4|Function|0|0|2|random(-1)|00| 5|MakeRecord|1|2|3||00| 6|NewRowid|0|4|0||00| 7|Insert|0|3|4||08| 8|Integer|0|5|0||00| 9|Rewind|0|16|0||00| 10|Column|0|1|3||00| 11|Le|5|15|3|collseq(BINARY)|6a| 12|Column|0|0|6||00| 13|Column|0|1|7||00| 14|ResultRow|6|2|0||00| 15|Next|0|10|0||00| 16|Halt|0|0|0||00| 17|Goto|0|3|0||00| Noah -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Igor Tandetnik Sent: Friday, August 29, 2008 9:39 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] problem using random() in queries Noah Hart <[EMAIL PROTECTED]> wrote: > I would expect that multiple calls to random always return different > values, even if in the same line. > > The fact that we are aliasing random by a column name makes no > difference to me. What about this: select name, RNDValue from ( select name, random() as RNDValue from names ) where RNDValue > 0; Conceptually, the inner select produces a temporary table, and the outer works on that table. Personally, I found it surprising that random() was still called twice per row in this case. Igor Tandetnik CONFIDENTIALITY NOTICE: This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] problem using random() in queries
Noah Hart <[EMAIL PROTECTED]> wrote: > I would expect that multiple calls to random always return different > values, even if in the same line. > > The fact that we are aliasing random by a column name makes no > difference to me. What about this: select name, RNDValue from ( select name, random() as RNDValue from names ) where RNDValue > 0; Conceptually, the inner select produces a temporary table, and the outer works on that table. Personally, I found it surprising that random() was still called twice per row in this case. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] problem using random() in queries
Richard, Before you "fix" it, I'm not convinced it is broken. >From MS SQL server create table _names (N varchar(5)); insert into _names values('a'); insert into _names values('b'); insert into _names values('c'); insert into _names values('d'); insert into _names values('e'); select N, RAND() as RNDValue from _names a 0.301745013642105 b 0.301745013642105 c 0.301745013642105 d 0.301745013642105 e 0.301745013642105 select N, RAND() as RNDValue from _names where RAND() >= 0.5; a 0.0427909435260437 b 0.0427909435260437 c 0.0427909435260437 d 0.0427909435260437 e 0.0427909435260437 >From the SQL manual: " When you use an algorithm based on GETDATE to generate seed values, RAND can still generate duplicate values if the calls to RAND are made within the interval of the smallest datepart used in the algorithm. This is especially likely when the calls to RAND are included in a single batch. Multiple calls to RAND in a single batch can be executed within the same millisecond. This is the smallest increment of DATEPART. In this case, incorporate a value based on something other than time to generate the seed values." So, it comes down to definition: I would expect that multiple calls to random always return different values, even if in the same line. So the following should give different results for each call to random() Select random(), random(), random() And the following as well: Select random() where random() >0 The fact that we are aliasing random by a column name makes no difference to me. Therefore: If the "random" function in sqlite is defined as having a different value every time it is called, and we explain that this is true, even if aliased, then the current implementation works correctly and no work, other than documentation is needed. Regards, Noah -Original Message- OK. Even though this kind of thing is probably an abuse of SQL, I'm working on ticket #3343. Just for the record, I'd like everybody to know that the following is really, really hard to do correctly and is going to require a lot of extra code in SQLite - code that nobody will ever use in practice: SELECT random()%5 AS x, count(*) FROM tab GROUP BY x; D. Richard Hipp [EMAIL PROTECTED] CONFIDENTIALITY NOTICE: This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Incorporating FTS into existing database schema
On Thu, Aug 28, 2008 at 8:15 PM, Andreas Ntaflos <[EMAIL PROTECTED]> wrote: > Notice the snippet bit: it takes the virtual table name (fts_paper in the case > of your examples) instead of the column name as an argument, which I find > curious. Your query would fail with the following error message: > > SQL error: illegal first argument to html_snippet The snippet thing is because fts needs to pass a bit of magic between the results of the MATCH and the snippet function. At creation, fts3 adds a hidden column with the same name as the table, and then it feeds the bit of magic out through that. There's no way SQLite proper can see this, and fts3 has no way to see the table aliases (the snippet implementation sees the value passed, not the name of the value passed). snippet() can only operate off of the results of the MATCH, so specifying the column could be ambiguous (what if you passed a column which didn't participate in the MATCH?). So, in the examples you gave, b.paper_text will be passing in the TEXT from that column in the row, and snippet() doesn't have the info it needs to operate. Passing b doesn't work, because b is a table, and you cannot pass tables to functions. fts_paper is passing the magic hidden column, NOT the table named fts_paper, it could also be written b.fts_paper. I almost wonder if it wouldn't make sense to add an additional hidden column called match, and maybe another called all. Then you could write things like: SELECT docid, snippet(match) FROM fts_table WHERE all MATCH 'foo'; SELECT t.docid, snippet(t.match) FROM fts_table AS t WHERE t.all MATCH 'foo'; I think that would be less surprising to people, because using a table-named column is ambiguous. When it works, it looks like you managed to use the table name, but when it doesn't, it just makes it harder to figure out what really _is_ going on. -scott ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Broken indexes ...
Hello! В сообщении от Friday 29 August 2008 02:29:37 D. Richard Hipp написал(а): > Run this command: > > sqlite3 old.db .dump | sqlite3 new.db This command lost information about page size ans some other. Best regards, Alexey. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3: floating point behaviour has changed
Package: sqlite3 Version: 3.5.9-3 Severity: normal Sending to Debian Bug Tracking System and the sqlite-users list. On the sqlite-users mailing list, bakers wrote: > Igor Tandetnik wrote: > > Scott Baker <[EMAIL PROTECTED]> wrote: > >> Did I do something wrong? > >> > >> SQLite version 3.5.9 > >> Enter ".help" for instructions > >> sqlite> select 1219441430151/1000, 1219441430151/1000.0; > >> 1219441430| > > > > Works for me. Did you perhaps compile without floating point support, or > > something like that? I'm running Windows pre-built binaries. > > I'm using the fedora packages: > sqlite-3.5.9-1.fc9.i386 > > It works for small numbers: > sqlite> select 1.0 / 2.0; > 0.5 Debian's 3.5.9 shows similar behaviour, although older versions work. Is this something inate to the sqlite code or a problem with the build? $ cat /etc/debian_version 3.1 $ uname -m i686 $ dpkg -l sqlite3 | awk '/^.i/{print $3}' 3.2.8-1+zeno : no code changes from debian package, just rebuilt under sarge $ echo 'select 1219441430151/2.0, 1219441430151/20.0;' | sqlite3 609720715075.5|60972071507.55 $ cat /etc/debian_version 4.0 $ uname -m x86_64 $ dpkg -l sqlite3 | awk '/^.i/{print $3}' 3.3.8-1.1 $ echo 'select 1219441430151/2.0, 1219441430151/20.0;' | sqlite3 609720715075.5|60972071507.55 $ cat /etc/debian_version lenny/sid $ uname -m i686 $ dpkg -l sqlite3 | awk '/^.i/{print $3}' 3.5.9-3 $ echo 'select 1219441430151/2.0, 1219441430151/20.0;' | sqlite3 609720715075.5| -jonathan -- Jonathan H N Chin, 2 dan | deputy computer | Newton Institute, Cambridge, UK <[EMAIL PROTECTED]> | systems mangler | tel/fax: +44 1223 767091/330508 "respondeo etsi mutabor" --Rosenstock-Huessy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3: floating point behaviour has changed
forcemerge 488864 497047 thanks I suck. I should have looked at the existing bugs. It is a problem. It has already been fixed. See: http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=488864 Perhaps fedora has an updated package too? I wrote: > On the sqlite-users mailing list, bakers wrote: > > Igor Tandetnik wrote: > > > Scott Baker <[EMAIL PROTECTED]> wrote: > > >> sqlite> select 1219441430151/1000, 1219441430151/1000.0; > > >> 1219441430| > > > > > > Works for me. Did you perhaps compile without floating point support, or > > > something like that? I'm running Windows pre-built binaries. > > > > I'm using the fedora packages: > > sqlite-3.5.9-1.fc9.i386 [...] > Debian's 3.5.9 shows similar behaviour, although older versions work. > Is this something inate to the sqlite code or a problem with the build? [...] > $ cat /etc/debian_version > lenny/sid > $ uname -m > i686 > $ dpkg -l sqlite3 | awk '/^.i/{print $3}' > 3.5.9-3 > $ echo 'select 1219441430151/2.0, 1219441430151/20.0;' | sqlite3 > 609720715075.5| -jonathan -- Jonathan H N Chin, 2 dan | deputy computer | Newton Institute, Cambridge, UK <[EMAIL PROTECTED]> | systems mangler | tel/fax: +44 1223 767091/330508 "respondeo etsi mutabor" --Rosenstock-Huessy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] ParseSchema's virtual machine instructions
Is there a feature (like explain) which returns the sequence of virtual machine instructions that ParseSchema creates? -Mrinal ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Incorporating FTS into existing database schema
> > Any advice will be greatly appreciated. If there is any FM I > should R kindly point me to it :) > FTS information is difficult to find. Try http://www.sqlite.org/cvstrac/wiki?p=FtsOne I can't remember how I came across this link because I can never find it on the SQLite website. Ignore references to FTS1 as the SQL syntax is the same for FTS3 (I believe it is only the internals that have changed). One tip is to read the document a number of times. There are a number of important but subtle concepts that you need to grasp to effectively use FTS. In particular understand how the tokeniser works. Words with hyphens or UTF8 may not work as you might expect. This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How can I load big file into a sqlite database?
That's what I do. I was looking for a kind of pre-built solution that could have better performance in loading a table than to do a INSERT for each line inside a transaction. But thanks Filipe Madureira Alexandre Courbot wrote: >> I am interested in this issue also. >> I didn't understand the first part of your answer. "sqlite3 databasefile >> < infile" ?? >> >> The ".import FILE TABLE" works, but it is from CLI. How can I do it in >> my C++ application using the sqlite3? >> > > An equivalent would be to read the file line by line and execute the > statements it contains. > > Alex. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How can I load big file into a sqlite database?
> I am interested in this issue also. > I didn't understand the first part of your answer. "sqlite3 databasefile > < infile" ?? > > The ".import FILE TABLE" works, but it is from CLI. How can I do it in > my C++ application using the sqlite3? An equivalent would be to read the file line by line and execute the statements it contains. Alex. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How can I load big file into a sqlite database?
Hi, I am interested in this issue also. I didn't understand the first part of your answer. "sqlite3 databasefile < infile" ?? The ".import FILE TABLE" works, but it is from CLI. How can I do it in my C++ application using the sqlite3? Thanks Filipe Madureira Alexandre Courbot wrote: >> I usually used "load data infile" command in mysql to insert long list of >> data. >> But I could not find this kind of command in sqlite. >> How do you load big file into a sqlite database?? >> > > I guess what you want to do is "sqlite3 databasefile < infile" > > See also the ".import FILE TABLE" command to inport a file into a single > table. > > Alex. > ___ > 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