Re: [sqlite] TEMP_STORE not working ?(3.5.9)
Hi, Just want to bump this, I really need to get to the bottom of this. Thanks for any info, Kris. Kris Groves wrote: > Hi, > From what I understand : > - default behavior is to use files for temporary stuff. > - the directory that will be used for these temporary files can be > defined via pragma (temp_store_directory). If the pragma is not used, > it will default to the first hardcoded directory (linux), in the order > that follows: /var/tmp, /usr/tmp, /tmp, or finally current directory. > > So, in the environment I am running in, either those directories do not > exist, or are not writable to the user under which the process is > running. The result being an "error 14: unable to open database file" > as soon as temporary files are needed. > > After a little digging I discover SQLITE_TEMP_STORE compilation flag. > So I export CFLAGS=-DSQLITE_TEMP_STORE=3, run configure and remake, > figuring that the temp files will now reside in memory, and need no > writing into a directory. However, the problem remains. > > When I look through the code, there is no instance of SQLITE_TEMP_STORE, > only TEMP_STORE... So I repeat the above with -DTEMP_STORE. Same result. > > Then I add a path that I know is accessible to the user under which the > process runs, to the azDirs array in the unixGetTempname function. > Voila.. working now.. > > I've retested with default TEMP_STORE and TEMP_STORE compiled in a > 3(memory only). And regardless of the setting, it only works if there > is a readable/writable directory... > > I would think that if TEMP_STORE=3, then no directory is required ? Is > this a bug, or am I misunderstanding something ? > > Thanks, > Kris. > > ___ > 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] Bizarre sqlite3 idiocyncrasy
Hullo all, Including the following in my c-program: sql_rc = sqlite3_open_v2(database_name, &sql_db, SQLITE_OPEN_READONLY, NULL); if (sql_rc != SQLITE_OK) { fprintf(stderr, "Function:%s can't open database:%s, error:%s\n", FLAG_MAIN, database_name, sqlite3_errmsg(sql_db)); sqlite3_close(sql_db); return(FAIL); } Causes a seemingly unrelated optimization issue elsewhere in the program where I read in some data from flat files. I.e. if the database is opened, the time taken to read in data from the flat files is much longer. This is very bizarre. I have bench marked the times and as stated earlier, the time taken "seems" directly related to whether the database is open/not. Not sure at all how to go about unravelling this mystery. Ideas? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to check compiler options being used
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Joanne Pham wrote: > It didn't work What didn't work. > I am using version 3.5.9 and my application is used the library > libsqlite3.so.8.6 but I have no ideas what are the compiler option being used. That question was already answered (in short there is no way of knowing). http://www.catb.org/~esr/faqs/smart-questions.html Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAknvsucACgkQmOOfHg372QQUvACgpW97udhuMGpQ7LgdacTejhpO Dy8AnRk81kyxuoS2eAvFQnz3j/yhQUj8 =KBGV -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to check compiler options being used
Hi All, It didn't work - I am using version 3.5.9 and my application is used the library libsqlite3.so.8.6 but I have no ideas what are the compiler option being used. Any help please. Thanks, JP From: Roger Binns To: General Discussion of SQLite Database Sent: Wednesday, April 22, 2009 4:24:15 PM Subject: Re: [sqlite] How to check compiler options being used -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Roger Binns wrote: > However the actual compiler flags (eg -O, -DXXX) are not recorded > by default in object files or libraries. If you are using gcc 4.2 or later then you can add -frecord-gcc-switches to the compile line and they will be recorded in a special section in the object file. They will also be combined in any resulting library. For example: $ objdump --full-contents --section .GCC.command.line apsw.o apsw.o: file format elf64-x86-64 Contents of section .GCC.command.line: 2d492f75 73722f69 6e636c75 64652f70 -I/usr/include/p 0010 7974686f 6e322e36 002d492e 002d4973 ython2.6.-I..-Is 0020 716c6974 6533002d 49737263 002d445f qlite3.-Isrc.-D_ 0030 5245454e 5452414e 54002d44 45585045 REENTRANT.-DEXPE 0040 52494d45 4e54414c 002d4453 514c4954 RIMENTAL.-DSQLIT 0050 455f4445 42554700 2d445351 4c495445 E_DEBUG.-DSQLITE 0060 5f544852 45414453 4146453d 31002d44 _THREADSAFE=1.-D [.. it goes on and on ..] Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAknvpxsACgkQmOOfHg372QR/uACfZQVBZcaWWM0x2ioRVk2qKfzd m6oAoJ16RZrAnEFbC/SP8SAkbSmXnq7p =1F1J -END PGP SIGNATURE- ___ 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 to check compiler options being used
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Roger Binns wrote: > However the actual compiler flags (eg -O, -DXXX) are not recorded > by default in object files or libraries. If you are using gcc 4.2 or later then you can add -frecord-gcc-switches to the compile line and they will be recorded in a special section in the object file. They will also be combined in any resulting library. For example: $ objdump --full-contents --section .GCC.command.line apsw.o apsw.o: file format elf64-x86-64 Contents of section .GCC.command.line: 2d492f75 73722f69 6e636c75 64652f70 -I/usr/include/p 0010 7974686f 6e322e36 002d492e 002d4973 ython2.6.-I..-Is 0020 716c6974 6533002d 49737263 002d445f qlite3.-Isrc.-D_ 0030 5245454e 5452414e 54002d44 45585045 REENTRANT.-DEXPE 0040 52494d45 4e54414c 002d4453 514c4954 RIMENTAL.-DSQLIT 0050 455f4445 42554700 2d445351 4c495445 E_DEBUG.-DSQLITE 0060 5f544852 45414453 4146453d 31002d44 _THREADSAFE=1.-D [.. it goes on and on ..] Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAknvpxsACgkQmOOfHg372QR/uACfZQVBZcaWWM0x2ioRVk2qKfzd m6oAoJ16RZrAnEFbC/SP8SAkbSmXnq7p =1F1J -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to check compiler options being used
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Joanne Pham wrote: > How to check the compiler options in this case? You can use 'nm' to see which symbols are present so that will help track functions that have been included or excluded from which you can deduce some flags. gcc does embed some information to help. Use objdump -s -j SECTION dllname. Example $ objdump -s -j .comment sqlite.so Contents of section .comment: 00474343 3a202855 62756e74 7520342e .GCC: (Ubuntu 4. 0010 332e332d 35756275 6e747534 2920342e 3.3-5ubuntu4) 4. 0020 332e3300 00474343 3a202855 62756e74 3.3..GCC: (Ubunt [.. it goes on like this ..] At least I can identify which compiler was used. However the actual compiler flags (eg -O, -DXXX) are not recorded by default in object files or libraries. If you require SQLite to be compiled a particular way then use the amalgamation. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAknvovQACgkQmOOfHg372QSupACeMt1xw74Jcs1u2UctKTRybvs+ aOAAnR+YN1QgjJWLFfeKCfA/KU2C6d/H =sw5y -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to check compiler options being used
Hi All, I am current used the SQLite 3.5.9 and SQLite library are compiled by someone and I would like to check all what are the compiler options being used? How to check the compiler options in this case? Thanks, JP ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sporadic freezes of sqlite
Yes, this could be an option. Although as you pointed it yourself including rand() and doing it on all statements in code here and there is not very convenient. I thought myself about turning synchronous on and changing somehow sqlite3_io_methods functions where xSync will do its job once in every N calls. But it looks ugly for me too. So I thought maybe someone can come up with better idea... Pavel On Wed, Apr 22, 2009 at 4:58 PM, Doug Currie wrote: > > On Apr 22, 2009, at 4:38 PM, Pavel Ivanov wrote: > >> I've tried to set pragma synchronous = on (it's off by default for >> me), but it makes application to work 5 times slower which is not >> acceptable for me. I would be happy if there was some solution in >> between that, i.e. for example just a bit slower operation on every >> pwrite but without 8 seconds-peaks. > > Perhaps you can occasionally wrap a transaction with: > PRAGMA synchronous = NORMAL; > < do the transaction > > PRAGMA synchronous = OFF; > > which would flush OS allocated database cache buffers; do this one out > of N transactions. If you have threads performing transactions that > are not otherwise communicating, you base this decision on a random > number [rand() % N == 1]. Tune N to achieve the maximum pwrite time > you need. > > Caveat: I have never tried this; I don't use PRAGMA synchronous = OFF. > > e > > ___ > 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] Transaction commit performance
Alessandro Merolli wrote: > Most of the time, the statements requested are > grouped into a database transaction like this: > > BEGIN TRANSACTION; > INSERT INTO TBTEST (X,Y,Z) SELECT X,Y,Z FROM TBDATA JOIN ... ; > DELETE FROM TBTEST WHERE X NOT IN (SELECT ); > UPDATE TBTEST SET Y=y WHERE X=x; > COMMIT TRANSACTION; > > I'm already considering all the issues that this kind of design > has: database concurrency, database locks, etc... but, this is not the > thing. > I'm experiencing a behavior that seams strange to me and I'd like > to check if anybody can give me some tips to minimize this: The COMMIT > statement consumes 50% or more of the time of the whole transaction; > for example: if the whole transaction costs 2000 ms, only the COMMIT > operation took 1500 ms. Most likely, your transaction is small enough to be performed entirely within in-memory cache, without ever spilling to disk. Then, COMMIT has to actually write all the dirty pages to disk. Naturally, that's going to be the slowest part. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Transaction commit performance
Hi all, I'm working on a project that makes massively usage of SQL statements with many inserts/selects. It's a multi-threaded process that shares a certain amount of SQLite "connections" to be used by other process through an IPC layer. The connections are created at startup and 5 other database files are attached to them before being shared by this process. Most of the time, the statements requested are grouped into a database transaction like this: BEGIN TRANSACTION; INSERT INTO TBTEST (X,Y,Z) SELECT X,Y,Z FROM TBDATA JOIN ... ; DELETE FROM TBTEST WHERE X NOT IN (SELECT ); UPDATE TBTEST SET Y=y WHERE X=x; COMMIT TRANSACTION; I'm already considering all the issues that this kind of design has: database concurrency, database locks, etc... but, this is not the thing. I'm experiencing a behavior that seams strange to me and I'd like to check if anybody can give me some tips to minimize this: The COMMIT statement consumes 50% or more of the time of the whole transaction; for example: if the whole transaction costs 2000 ms, only the COMMIT operation took 1500 ms. I understood all the operations described in the SQLite documentation about transaction commit/rollback feature, but this scenario seems to me that some adjustments can be made to minimize this. I'm using: - The server is an Intel 2GHz, 2GB of RAM and a 80GB hard-disk SATA II (w/ 8MB of cache) 7200 RPM; - Windows XP SP3 professional using NTFS; - SQLite 3.6.13 (amalgamation version); - I'm using shared-cache with the wait-notify feature implemented in the previous 3.6.12; - Other compiler options being used are: SQLITE_THREADSAFE=1; TEMP_STORE=3; SQLITE_DEFAULT_CACHE_SIZE=65568; SQLITE_DEFAULT_TEMP_CACHE_SIZE=65568; SQLITE_MAX_ATTACHED=30; SQLITE_ENABLE_COLUMN_METADATA; SQLITE_ENABLE_UNLOCK_NOTIFY. - The database size is around 200MB. What I'm looking for is: - If someone has a similar environment, what can be done to optimize Windows XP disk writes? - What PRAGMA statements or compiler options can help me with this, without the risk of getting the database corrupted? Any suggestion is helpful, thanks for the attention. Best regards, Alessandro Merolli. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sporadic freezes of sqlite
On Apr 22, 2009, at 4:38 PM, Pavel Ivanov wrote: > I've tried to set pragma synchronous = on (it's off by default for > me), but it makes application to work 5 times slower which is not > acceptable for me. I would be happy if there was some solution in > between that, i.e. for example just a bit slower operation on every > pwrite but without 8 seconds-peaks. Perhaps you can occasionally wrap a transaction with: PRAGMA synchronous = NORMAL; < do the transaction > PRAGMA synchronous = OFF; which would flush OS allocated database cache buffers; do this one out of N transactions. If you have threads performing transactions that are not otherwise communicating, you base this decision on a random number [rand() % N == 1]. Tune N to achieve the maximum pwrite time you need. Caveat: I have never tried this; I don't use PRAGMA synchronous = OFF. e ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] create the trigger to delete the data from other database
On Wed, 22 Apr 2009 10:33:18 -0700 (PDT), Joanne Pham wrote: >Hi All, >Can we have the trigger to delete to data from different database? >My application has 2 databases and when ever the application is >deleting the rows in one of tables in DB1 I would like to have a >trigger to delete the rows in table in DB2. >Is this possible? >Thanks, >JP I added this question and the answer to the SQLite wiki FAQ: http://www.sqlite.org/cvstrac/wiki?p=SqliteWikiFaq Thanks drh for the text, I took it from: [sqlite] Foreign Constraint Triggers Across Attached Databases (2008-01-24 11:56:16 UTC). Cheers, -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sporadic freezes of sqlite
Hi, all! I have an application written using sqlite. It writes into the database very intensively. And I noticed that it works nice and very fast but from time to time it just freezes for several seconds (I've registered freezes up to 8 secs). After some tracing of sqlite code I've found that all these 8 secs of freeze sqlite spends inside pwrite() call. So evidently when everything works fast all pwrites return almost immediately because kernel writes everything to the cache. And on some calls kernel have just flush all disk buffers, and with intensive disk usage this flushing can last for pretty long time. I've tried to set pragma synchronous = on (it's off by default for me), but it makes application to work 5 times slower which is not acceptable for me. I would be happy if there was some solution in between that, i.e. for example just a bit slower operation on every pwrite but without 8 seconds-peaks. Does anybody know what solution I can apply here? Pavel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fixing a database
I think it would be a good idea for sqlite3 to display a message like "Database opened for read-only" if you don't have permission to write. I saw this problem myself where a rollback was necessary from a previous root process, and as a new SQLite user, it was confusing and made me think "great, the database is already corrupted". Jim On 4/22/09, Igor Tandetnik wrote: > Alberto Simoes > wrote: >> I am not sure what happened, but I have a database that opens >> correctly with sqlite3 shell, I can select and query it, but I can't >> edit it. I get Disk I/O error. The disk is not full. > > You don't have write permissions to the file, perhaps? > > Igor Tandetnik > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Software first. Software lasts! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fixing a database - solved
Hello On Wed, Apr 22, 2009 at 9:17 PM, Igor Tandetnik wrote: > Alberto Simoes > wrote: >> I am not sure what happened, but I have a database that opens >> correctly with sqlite3 shell, I can select and query it, but I can't >> edit it. I get Disk I/O error. The disk is not full. > > You don't have write permissions to the file, perhaps? It had to be mine stupidity. I was testing as root, and as root I have disk space. As user I don't (root quota). Thanks and sorry for the generated spam. Alberto -- Alberto Simões ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fixing a database
Alberto Simoes wrote: > I am not sure what happened, but I have a database that opens > correctly with sqlite3 shell, I can select and query it, but I can't > edit it. I get Disk I/O error. The disk is not full. You don't have write permissions to the file, perhaps? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Fixing a database
Hello. I am not sure what happened, but I have a database that opens correctly with sqlite3 shell, I can select and query it, but I can't edit it. I get Disk I/O error. The disk is not full. Is there any way to fix the database? I think some kind of flag somewhere in the database has a wrong value. But not sure :) Thank you Alberto -- Alberto Simões ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Run-Time Check Failure
D. Richard Hipp a écrit : > This is not error in the SQLite code. The code here is correct. The > bug is in your compiler. Sorry but I don't agree at all. > Adding a work-around so that this will work in your compiler makes the > code rather more complicated: > > wsdPrng.j = (wsdPrng.j + wsdPrng.s[i] + k[i]) & 0xff; > > I am opposed to obfuscating the code in this way because of your > compiler bug. Is there some command-line option or something on your > compiler that can turn off the silly overflow check? This makes code clearer. Adding unsigned char with value that exceeds maximum value (255) is a potential bug. Compiler doesn't know if it's done deliberately or not, and neither other people that read the code. So this option in compiler is useful to detect some bugs. If you say it's not one, that's fine, I'll add bit masking in my version, I just needed to know that. Thanks for help, Gérald ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] database is slowing down, caused by Sophos
hello, No question, just some information that might be useful to someone. We have a number of small SQLite databases, which are located on a network drive. Users are complaining the last months that these databases becomes slower every week. After investigating the case, we found that Sophos anti-virus software is causing this problem. The standard setting of Sophos is to scan on each file read, so every simple query to the database loads the whole database file to the local system for virus scanning. The data transport when Sophos is on is about 300 times larger than in normal use. After disabling Sophos for the SQLite database files, the system runs like a speer. cheers, Stef ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] create the trigger to delete the data from other database
Joanne Pham wrote: > Can we have the trigger to delete to data from different database? No. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Run-Time Check Failure
The compiler is not broken. If this behavior in the compiler bugs you, (pun intended) you can disable the warning locally or globally. This change is not obfuscating the code. It is not a work-around. It is making your intentions clear. When I read the line as written, I have to research for some non-trivial amount of time, to figure out if what it is doing is what is intended. When I read the line as modified with the mask, it is clear as a bell. A comment wouldn't hurt either. With all due respect, you really can't be complaining about obfuscating code that is filled with one character variable names, passes integers in pointer values, has a 4500+ line function, was composed with a keyboard missing a space key, etc, etc, etc. D. Richard Hipp wrote: > On Apr 22, 2009, at 11:49 AM, sql...@fauvelle.net wrote: > > >>> This is probably not a bug. There are places in the SQLite code >>> where >>> we deliberately discard all but the lower 8 bits of an integer. But, >>> if you like to tell us *where* in the code this occurs, I'll be happy >>> to verify it for you. >>> >> In sqlite3.c big file, it's in static u8 randomByte(void) function, on >> line 16707 : >> >> wsdPrng.j += wsdPrng.s[i] + k[i]; >> >> wsdPrng.j = 246, and wsdPrng.s[i] + k[i] = 28, so adding it will be >> more >> than 255. If it's deliberate, a bitmask 0xFF would solve the problem. >> > > > This is not error in the SQLite code. The code here is correct. The > bug is in your compiler. > > Adding a work-around so that this will work in your compiler makes the > code rather more complicated: > > wsdPrng.j = (wsdPrng.j + wsdPrng.s[i] + k[i]) & 0xff; > > I am opposed to obfuscating the code in this way because of your > compiler bug. Is there some command-line option or something on your > compiler that can turn off the silly overflow check? > > D. Richard Hipp > d...@hwaci.com > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] create the trigger to delete the data from other database
Hi All, Can we have the trigger to delete to data from different database? My application has 2 databases and when ever the application is deleting the rows in one of tables in DB1 I would like to have a trigger to delete the rows in table in DB2. Is this possible? Thanks, JP ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Columns in where clause and the index.
No, the order of terms in a WHERE clause does *not* effect index usage. On Apr 22, 2009, at 11:36 AM, Steve Friedman wrote: > See (possibly among others) http://www.sqlite.org/cvstrac/tktview?tn=3678 > > Steve Friedman > > Joanne Pham wrote: >> Hi All, >> I was wondering if we need to have the order of columns in the >> where clause need to be matched with the order of the indexes. >> Does the index will be used if the columns in the where clause >> didn't match with the columns in the defined index? >> Thanks, >> JP >> >> >> >> >> ___ >> 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 D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Run-Time Check Failure
On Apr 22, 2009, at 11:49 AM, sql...@fauvelle.net wrote: > >> This is probably not a bug. There are places in the SQLite code >> where >> we deliberately discard all but the lower 8 bits of an integer. But, >> if you like to tell us *where* in the code this occurs, I'll be happy >> to verify it for you. > > > In sqlite3.c big file, it's in static u8 randomByte(void) function, on > line 16707 : > > wsdPrng.j += wsdPrng.s[i] + k[i]; > > wsdPrng.j = 246, and wsdPrng.s[i] + k[i] = 28, so adding it will be > more > than 255. If it's deliberate, a bitmask 0xFF would solve the problem. This is not error in the SQLite code. The code here is correct. The bug is in your compiler. Adding a work-around so that this will work in your compiler makes the code rather more complicated: wsdPrng.j = (wsdPrng.j + wsdPrng.s[i] + k[i]) & 0xff; I am opposed to obfuscating the code in this way because of your compiler bug. Is there some command-line option or something on your compiler that can turn off the silly overflow check? D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Run-Time Check Failure
> This is probably not a bug. There are places in the SQLite code where > we deliberately discard all but the lower 8 bits of an integer. But, > if you like to tell us *where* in the code this occurs, I'll be happy > to verify it for you. In sqlite3.c big file, it's in static u8 randomByte(void) function, on line 16707 : wsdPrng.j += wsdPrng.s[i] + k[i]; wsdPrng.j = 246, and wsdPrng.s[i] + k[i] = 28, so adding it will be more than 255. If it's deliberate, a bitmask 0xFF would solve the problem. Gérald ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Columns in where clause and the index.
See (possibly among others) http://www.sqlite.org/cvstrac/tktview?tn=3678 Steve Friedman Joanne Pham wrote: > Hi All, > I was wondering if we need to have the order of columns in the where clause > need to be matched with the order of the indexes. > Does the index will be used if the columns in the where clause didn't match > with the columns in the defined index? > Thanks, > JP > > > > > ___ > 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] Columns in where clause and the index.
Hi All, I was wondering if we need to have the order of columns in the where clause need to be matched with the order of the indexes. Does the index will be used if the columns in the where clause didn't match with the columns in the defined index? Thanks, JP ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Run-Time Check Failure
On Apr 22, 2009, at 11:23 AM, sql...@fauvelle.net wrote: > Hello all, > > I'm compiling sqlite 3.6.13 with Visual 2003, and RunTime Check > activated, and it gives this error : > > Run-Time Check Failure #1 - > A cast to a smaller data type has caused a loss of data. If this was > intentional, you should mask the source of the cast with the > appropriate > bitmask. For example: > > char c = (i & 0xFF); > > Changing the code in this way will not affect the quality of the > resulting optimized code. > > Is it a known bug? Can I send more detailed informations (call stack, > source code) on this list, or on dev list? This is probably not a bug. There are places in the SQLite code where we deliberately discard all but the lower 8 bits of an integer. But, if you like to tell us *where* in the code this occurs, I'll be happy to verify it for you. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Run-Time Check Failure
Hello all, I'm compiling sqlite 3.6.13 with Visual 2003, and RunTime Check activated, and it gives this error : Run-Time Check Failure #1 - A cast to a smaller data type has caused a loss of data. If this was intentional, you should mask the source of the cast with the appropriate bitmask. For example: char c = (i & 0xFF); Changing the code in this way will not affect the quality of the resulting optimized code. Is it a known bug? Can I send more detailed informations (call stack, source code) on this list, or on dev list? Thanks, Gérald ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Find in SQLite database
On 22/04/2009 10:29 PM, anna_shahinyan wrote: > Thanks, > > I have created NSString and added the id value, then for creating > statement I have converted NSString to const char* by UTF8String > but the it seems it does no t work as sqlite3_prepare_v2 does not return > SQLITE_OK. Anna, show us the code that you now have after making those changes. Tell us what value sqlite3_prepare_v2 now returns. Then probably we can help you. Cheers, John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Find in SQLite database
"anna_shahinyan" wrote in message news:23175323.p...@talk.nabble.com > I have created NSString and added the id value, then for creating > statement I have converted NSString to const char* by UTF8String > but the it seems it does no t work as sqlite3_prepare_v2 does not > return SQLITE_OK. My crystal ball seems to be cloudy lately, I have difficulty reading the small font on your monitor. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Find in SQLite database
Thanks, I have created NSString and added the id value, then for creating statement I have converted NSString to const char* by UTF8String but the it seems it does no t work as sqlite3_prepare_v2 does not return SQLITE_OK. Thanks for response. Igor Tandetnik wrote: > > "anna_shahinyan" > wrote in > message news:23171280.p...@talk.nabble.com >> I have tried the following but does not help: >> >>sqlite3_stmt *stmt; >>const char *sql_check = "SELECT * FROM MY_DATABASE WHERE id=?"; >>NSString *name = nil; >> if(sqlite3_prepare_v2(myDatabase, sql_check, -1, &stmt, NULL) == >> SQLITE_OK) { >> if (sqlite3_step(stmt) == SQLITE_NOTFOUND) { >> NSLog(@"Not found"); >> } >> } > > You apparently want to check for a record with a particular ID - aren't > you surprised that your code doesn't mention the actual ID anywhere? > > Your query has a parameter placeholder (the '?' sign). You need to bind > its value before running the statement - see sqlite3_bind_int. > > Finally, sqlite3_step would return SQLITE_DONE when there are no > records, not SQLITE_NOTFOUND. > > Igor Tandetnik > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/Find-in-SQLite-database-tp23171280p23175323.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TEMP_STORE not working ?(3.5.9)
Additionally, I have just tried changing the TEMP_STORE define in the code to 3, (just in case the compiler options were not working to begin with), with the same results as already described. Kris Groves wrote: > Hi, > From what I understand : > - default behavior is to use files for temporary stuff. > - the directory that will be used for these temporary files can be > defined via pragma (temp_store_directory). If the pragma is not used, > it will default to the first hardcoded directory (linux), in the order > that follows: /var/tmp, /usr/tmp, /tmp, or finally current directory. > > So, in the environment I am running in, either those directories do not > exist, or are not writable to the user under which the process is > running. The result being an "error 14: unable to open database file" > as soon as temporary files are needed. > > After a little digging I discover SQLITE_TEMP_STORE compilation flag. > So I export CFLAGS=-DSQLITE_TEMP_STORE=3, run configure and remake, > figuring that the temp files will now reside in memory, and need no > writing into a directory. However, the problem remains. > > When I look through the code, there is no instance of SQLITE_TEMP_STORE, > only TEMP_STORE... So I repeat the above with -DTEMP_STORE. Same result. > > Then I add a path that I know is accessible to the user under which the > process runs, to the azDirs array in the unixGetTempname function. > Voila.. working now.. > > I've retested with default TEMP_STORE and TEMP_STORE compiled in a > 3(memory only). And regardless of the setting, it only works if there > is a readable/writable directory... > > I would think that if TEMP_STORE=3, then no directory is required ? Is > this a bug, or am I misunderstanding something ? > > Thanks, > Kris. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Kris Groves Project Manager / Software Engineer mm-lab GmbH Phone: +49 7154 827 323 Stammheimer Str. 10 Fax:+49 7154 827 350 D-70806 Kornwestheim kris.gro...@mmlab.de www.mmlab.de Domicile of Company: Kornwestheim, Germany District Court - Court of Registration Stuttgart HRB 207257 Managing Directors: Bernd Herrmann, Lothar Krank, Michael Meiser, Dr. Andreas Streit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] TEMP_STORE not working ?(3.5.9)
Hi, >From what I understand : - default behavior is to use files for temporary stuff. - the directory that will be used for these temporary files can be defined via pragma (temp_store_directory). If the pragma is not used, it will default to the first hardcoded directory (linux), in the order that follows: /var/tmp, /usr/tmp, /tmp, or finally current directory. So, in the environment I am running in, either those directories do not exist, or are not writable to the user under which the process is running. The result being an "error 14: unable to open database file" as soon as temporary files are needed. After a little digging I discover SQLITE_TEMP_STORE compilation flag. So I export CFLAGS=-DSQLITE_TEMP_STORE=3, run configure and remake, figuring that the temp files will now reside in memory, and need no writing into a directory. However, the problem remains. When I look through the code, there is no instance of SQLITE_TEMP_STORE, only TEMP_STORE... So I repeat the above with -DTEMP_STORE. Same result. Then I add a path that I know is accessible to the user under which the process runs, to the azDirs array in the unixGetTempname function. Voila.. working now.. I've retested with default TEMP_STORE and TEMP_STORE compiled in a 3(memory only). And regardless of the setting, it only works if there is a readable/writable directory... I would think that if TEMP_STORE=3, then no directory is required ? Is this a bug, or am I misunderstanding something ? Thanks, Kris. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Find in SQLite database
"anna_shahinyan" wrote in message news:23171280.p...@talk.nabble.com > I have tried the following but does not help: > >sqlite3_stmt *stmt; >const char *sql_check = "SELECT * FROM MY_DATABASE WHERE id=?"; >NSString *name = nil; > if(sqlite3_prepare_v2(myDatabase, sql_check, -1, &stmt, NULL) == > SQLITE_OK) { > if (sqlite3_step(stmt) == SQLITE_NOTFOUND) { > NSLog(@"Not found"); > } > } You apparently want to check for a record with a particular ID - aren't you surprised that your code doesn't mention the actual ID anywhere? Your query has a parameter placeholder (the '?' sign). You need to bind its value before running the statement - see sqlite3_bind_int. Finally, sqlite3_step would return SQLITE_DONE when there are no records, not SQLITE_NOTFOUND. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] insert into multiple table
hi thanks for the reply. yes, you are right. i am looking to last inserted id. i am new in sqlite. please exaplain briefly. can u give me a example ya code. thanks dipendra Eugene Wee-2 wrote: > > Hi, > > On Wed, Apr 22, 2009 at 3:53 PM, dipendra > wrote: >> i want to insert data in multiple table where one table depened upon the >> other table >> something like that--- >> >> 1-insert into phone table >> insert into phone(id,phone) values (auto incremented ,0); >> >> 2-take the phone id value in integer variable >> integer phoneid=value; >> >> 3-pass this value in to address table >> insert into address(phoneid)values(phoneid); > > You are probably looking to use sqlite3_last_insert_rowid(). > > Regards, > Eugene Wee > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/insert-into-multiple-table-tp23171437p23172579.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] insert into multiple table
Hi, On Wed, Apr 22, 2009 at 3:53 PM, dipendra wrote: > i want to insert data in multiple table where one table depened upon the > other table > something like that--- > > 1-insert into phone table > insert into phone(id,phone) values (auto incremented ,0); > > 2-take the phone id value in integer variable > integer phoneid=value; > > 3-pass this value in to address table > insert into address(phoneid)values(phoneid); You are probably looking to use sqlite3_last_insert_rowid(). Regards, Eugene Wee ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] insert into multiple table
hi i want to insert data in multiple table where one table depened upon the other table something like that--- 1-insert into phone table insert into phone(id,phone) values (auto incremented ,0); 2-take the phone id value in integer variable integer phoneid=value; 3-pass this value in to address table insert into address(phoneid)values(phoneid); so please let me know how will i do this thanks dipendra -- View this message in context: http://www.nabble.com/insert-into-multiple-table-tp23171437p23171437.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Find in SQLite database
Hello, please help me to find a solution to check if the record is already in database or not. I have tried the following but does not help: sqlite3_stmt *stmt; const char *sql_check = "SELECT * FROM MY_DATABASE WHERE id=?"; NSString *name = nil; if(sqlite3_prepare_v2(myDatabase, sql_check, -1, &stmt, NULL) == SQLITE_OK) { if (sqlite3_step(stmt) == SQLITE_NOTFOUND) { NSLog(@"Not found"); } } I want to find the text that is going to be inserted. Thanks in advance. -- View this message in context: http://www.nabble.com/Find-in-SQLite-database-tp23171280p23171280.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] ANN - DBD::SQLite version 1.24_01 - amalgamation
All, I am pleased to announce that DBD::SQLite (Self Contained RDBMS in a DBI Driver) version 1.24_01 has been released on CPAN (by Adam Kennedy). http://search.cpan.org/~adamk/DBD-SQLite-1.24_01/ The main feature of this release is that now DBD::SQLite also uses amalgamated source recommended at sqlite.org, meaning that the entire C source code of the SQLite library itself is now contained in a single file rather than being spread over several dozen files. Some advantages of this change include better performance due to cross-file optimization, and also an easier compilation on platforms with more limited make systems. The last DBD::SQLite release that doesn't use the amalgamated source is version 1.23, which was released 2 days earlier. Also the bundled SQLite library with both 1.23 and 1.24_01 has been updated to v3.6.13 from v3.6.12 that 1.20 had. Further improvements in 1.24_01 over 1.20 involve mainly a significant modernization of the whole test suite, so it uses Test::More, and also there were more bugs fixed, minor enhancements made, and RT items addressed. See http://cpansearch.perl.org/src/ADAMK/DBD-SQLite-1.24_01/Changes as well as http://sqlite.org/changes.html for details. Given that the switch to amalgamated SQLite sources is arguably a very large change (or arguably a very small change), mainly in subtle ways that might affect build/compile systems (though actual SQLite semantics should be identical), ... Please bash the hell out of the latest DBD::SQLite and report any outstanding bugs on RT. Test your dependent or compatible projects with it, which includes any DBMS-wrapping or object persistence modules, and applications. If you want in to DBD::SQLite development, then join the following email/IRC forums which MST created (the mailing list, I am administrating): http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbd-sqlite #dbd-sqlite on irc.perl.org And the canonical version control is at: http://svn.ali.as/cpan/trunk/DBD-SQLite/ Patches welcome. Ideas welcome. Testing welcome. Whining to /dev/null. Note that today's switch to amalgamated sources is the last major short term change to DBD::SQLite that I personally expected would happen (sans updates to the bundled SQLite library itself), but other developers probably have their own ideas for what directions the development will go next. Please do not reply to me directly with your responses. Instead send them to the forums or file with RT as is appropriate. Thank you. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users