Re: [sqlite] Borland command line compiler v 5.5 and SQLite
Juan, > Can you please provide instructions for this or publish it? Compiling > with bcc55 has eluded us for quite a while. Please include exact steps > and versions of each program used. I've published SQLite327makefiles.zip file on my site, section "Utilities" as resulting example of quite simple Makefile Script Generator utility (currently initial v 1.0.0 published as well on that page). File contain separated makefiles for EXE and DLL as well with DEF file whilch is required with cdecl calling convention for BCC without precisely defined exported functions. For any other version, makefile scripts can be created quite easily with provided Generator. Exact steps: 1. Extract file sources provided for windows 2. When createding DLL, make sure that all exproted functions in DEF file have this form: FunctionName = _FunctionName. Use this DEF file instead. That need to be done manually. 3. Start the Generator 4. Press "Select files" button and select all files 5. Make sure to pick a name in TEdit component for resulting file ("sqlite3.exe" for instance) 6. Set parameters according to desired script: - When create EXE, select "Console" and press "Generate Script" button - When create DLL, select "DLLs", check "Multi Thread" checkbox and press "Generate Script" button 8. Copy and paste results to desired .MAK file Sasa -- www.szutils.net
Re: [sqlite] How to determine if a column is autoincremented?
Actually, on SQLite, 'INTEGER PRIMARY KEY' does designate a special type of autoincremented column. The internal 64 bit rowid is used directly in that case, which is essentially 'free' storage. http://www.sqlite.org/faq.html#q1 http://www.sqlite.org/lang_createtable.html http://www.sqlite.org/autoinc.html -pmb At 12:19 PM -0700 10/22/05, Mario Gutierrez wrote: Thanks for the reply. This would not work as I could define a table like this CRETE TABLE my_table ( id INTEGER PRIMARY KEY, ... ) This would meet your criteria, but 'id' is not an autoincremented column. -- Mario Gutierrez mario.l.gutierrez @ hotmail.com From: "David M. Cook" <[EMAIL PROTECTED]> Reply-To: sqlite-users@sqlite.org To: sqlite-users@sqlite.org Subject: Re: [sqlite] How to determine if a column is autoincremented? Date: Sat, 22 Oct 2005 10:52:20 -0700 On Fri, Oct 21, 2005 at 05:38:28PM -0700, Mario Gutierrez wrote: I'm writing a SQLite adapter for a code generation tool. I'm a little stumped on how you query SQLite to determine if a column is autoincremented. I've tried An auto-increment column is defined as INTEGER PRIMARY KEY, so look for primary key columns of type 'INTEGER'. Dave Cook _ Express yourself instantly with MSN Messenger! Download today - it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
Re: [sqlite] How to determine if a column is autoincremented?
On Sat, Oct 22, 2005 at 12:19:04PM -0700, Mario Gutierrez wrote: > CRETE TABLE my_table ( > id INTEGER PRIMARY KEY, > ... > ) > > This would meet your criteria, but 'id' is not an autoincremented column. Sorry, I don't get it. Why is it not? Also, why would one do that? If I wanted an non-autoincremented primary key field I'd use INT PRIMARY KEY. Dave Cook
[sqlite] Receive error: database is full
with Return Value 13. I checked my partition space and its usage is just 2% (Platform is Linux using SQLite 3.2.2). Also I am using Temp tables and periodically move data into my Main Table. I wondered if my Temp Table is full because strace on my process gave me messages like: access("/var/tmp/sqlite_MyjXYCDJGFYkfnc-journal", F_OK) = -1 ENOENT (No such file or directory) However, I have some debug statements in my code which indicate that the failure is during insertion directly in the Main Table (Commit phase of that transaction). Any ideas? Thanks!
Re: [sqlite] Borland command line compiler v 5.5 and SQLite
Sasa Zeman wrote: Does someone have Make files to create exe and dll for > Borland command line compiler v 5.5? Problem have solve days ago. If anyone need these scripts and .def file for BCC, I will publish it on my site or author may consider to include it in distributed archives. Can you please provide instructions for this or publish it? Compiling with bcc55 has eluded us for quite a while. Please include exact steps and versions of each program used. Juan Perez
Re: [sqlite] built-in functrion suggestion: size of blob
Lloyd Dupont wrote: But to my disbelief there is (apparently) no way to get the size of a blob (other than loading it :-() I'd like to be corrected if I am wrong, but I don't think there is anyway to do this in SQLite, besides simply storing the size of the blob with the blob when you write it into the database. SQLite stores BLOBS 'in-row' so it has to read the entire BLOB into memory before it figures out the size. I believe even the 'column_bytes' function 'suffers' from this. Tried to find out the feasibility of 'out-of-row' BLOB in SQLite once, but I don't think there was much interest in that. Regards, Kervin
Re: [sqlite] built-in functrion suggestion: size of blob
I assume the wrapper has wrapped this particular function. I'm currently writing my own wrapper and it has wrapped it as well. This is my definition (in case your wrapper doesn't have it): /// /// Returns the lengh of data in a single column of the current result row of a query /// /// Statement handle /// Zero based column index. The left-most column has an index of 0 /// Column data length. If the SQL statement is not currently pointing to a valid row, or if the the column index is out of range, the result is undefined [DllImport("sqlite3.dll", CallingConvention=CallingConvention.Cdecl)] internal static extern Int32 sqlite3_column_bytes(IntPtr statementhandle, Int32 column); Rob - Original Message - From: "Lloyd Dupont" <[EMAIL PROTECTED]> To: Sent: Sunday, October 23, 2005 3:46 PM Subject: Re: [sqlite] built-in functrion suggestion: size of blob Isn't this what you are looking for? http://www.sqlite.org/capi3ref.html#sqlite3_column_bytes " If the result is a BLOB then the sqlite3_column_bytes() routine returns the number of bytes in that BLOB. " Or do you really need it inside an SQL statement? that's right! I'm not using SQLite C API. I'm using a .NET wrapper. I don't see how I could could call this function in a pratical way from the wrapper...
Re: [sqlite] built-in functrion suggestion: size of blob
Isn't this what you are looking for? http://www.sqlite.org/capi3ref.html#sqlite3_column_bytes " If the result is a BLOB then the sqlite3_column_bytes() routine returns the number of bytes in that BLOB. " Or do you really need it inside an SQL statement? that's right! I'm not using SQLite C API. I'm using a .NET wrapper. I don't see how I could could call this function in a pratical way from the wrapper...
Re: [sqlite] Re: Multithreading Question
Well, I guess I was not clear in my explanations. I'm already doing my own locking, that's the problem! But I'm not sure how to improve it. My problem here is the following: I am doing a search through all record, could be long. Do some string matching, cutting, etc with all text of all records. For a good user experience my application doesn't block but do that in a thread. Therefore the user still has the hand and could do other things such as update 1 value. At which stage the data will be saved it and. that will froze the application because the search/select already get the database lock. Now I wonder how to work around the problem? could I have 2 connection and the connection which runs the select could intermix with the update command of the other thread? is it thread unsafe only when moving the database cursor? is i.e. I jyst have to lock everytim I move the cursor so I could mix both the select and the update? any tips?
Re: [sqlite] built-in functrion suggestion: size of blob
Isn't this what you are looking for? http://www.sqlite.org/capi3ref.html#sqlite3_column_bytes " If the result is a BLOB then the sqlite3_column_bytes() routine returns the number of bytes in that BLOB. " Or do you really need it inside an SQL statement? Rob - Original Message - From: "Lloyd Dupont" <[EMAIL PROTECTED]> To: Sent: Sunday, October 23, 2005 3:15 PM Subject: [sqlite] built-in functrion suggestion: size of blob I look into the build in function of SQLite and saw there is a function to know the length of a string (in a record). Great! But to my disbelief there is (apparently) no way to get the size of a blob (other than loading it :-() And no, length() doesn't work on Blob. I think it would be a worthy addition! Or is there already an (undocumented) such addition?
[sqlite] built-in functrion suggestion: size of blob
I look into the build in function of SQLite and saw there is a function to know the length of a string (in a record). Great! But to my disbelief there is (apparently) no way to get the size of a blob (other than loading it :-() And no, length() doesn't work on Blob. I think it would be a worthy addition! Or is there already an (undocumented) such addition?
Re: [sqlite] Re: Multithreading Question
Have you thought of performing your own thread locking using the equivalent of a semaphore? It is a much better method than some form of busy wait and is not only less likely to unearth deep synchronisation problems but will result in a faster running application. Threads A and B wait on the semaphore before the BEGIN TRANSACTION and signal it after the COMMIT or a possible ROLLBACK. JS Michael J. Sviridov wrote: Thanks for the response Igor. I've added a few more introspection routines to my code to see what is going on while these two threads are working and it seems the following is happening: Again, with two threads (A and B), each thread does the following: BEGIN IMMEDIATE TRANSACTION; (60,000 INSERT OR REPLACE statements into the same table) COMMIT TRANSACTION; thread A acquires the lock and starts to write. Meanwhile, thread B waits for the lock to be released in a SQLITE_BUSY loop. The strange thing is, however, that thread B's wait loop seems to somehow affect thread A's writing. After many thousand SQLITE_BUSY's from thread B, thread A's sqlite3_step() returns SQLITE_ERROR "SQLite logic error or missing database" and sqlite3_finalize() returns SQLITE_IOERR "disk I/O error". Each thread runs perfectly when run in turn (i.e. one after the other) but when I try to run them concurrently, the first thread seems always to experience this problem. The second thread always behaves normally, i.e. it eventually breaks out of it's SQLITE_BUSY loop when thread A has finished (but failed) and happily commits it's changes to disk. I'm really battling with this, how can thread B's SQLITE_BUSY loop be affecting thread A's writing? I'm using Visual C++ 7.1 on Windows XP and using the sqlite3.dll from www.sqlite.org. Again, any help would be appreciated. Mike. -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: 21 October 2005 02:38 PM To: SQLite Subject: [sqlite] Re: Multithreading Question Michael J. Sviridov wrote: I've got two thread's (with unique db handles), each thread does the following: BEGIN EXCLUSIVE TRANSACTION; (60,000 INSERT OR REPLACE statements into the same table) COMMIT TRANSACTION; This works fine, as expected, one thread acquires the lock and the other thread wait's for it to be released in a SQLITE_BUSY loop. My question: Is the same thing possible/safe with a DEFERRED or IMMEDIATE transaction? When I use an EXCLUSIVE transaction all is well, but if I try to use a DEFERRED or IMMEDIATE transaction I randomly get SQLITE_ERROR from sqlite3_step() on one or two of the INSERT OR REPLACE statements. This is normal for DEFERRED transaction. Your transaction starts as read-only, and is converted to read/write when the first modifying statement is executed. But it is possible that another transaction has already expressed an intent to write, and is waiting for all readers to clear. The only way out of this situation is to roll back the transaction and restart it from the beginning. Just retrying the last statement is pointless, and will result in the same error. SQLITE_ERROR should not happen for IMMEDIATE transaction, but it is possible for it to get SQLITE_BUSY on the first modifying statement (as well as on BEGIN statement). My indexes are also sometimes corrupted after this. This should not happen under any circumstances. If this is indeed the case, it is probably a bug and you should report it. Igor Tandetnik