Re: [sqlite] Implicit indices on TEMP tables
Igor, yes, deletes do occur on , but before every insert on I delete all records from mods_log_ and nevertheless get duplicates. I would expect the implicit index on modrowid to be refreshed too, when mods_log_ gets emptied. Thank you, Alex - Date: Tue, 14 Apr 2009 00:08:38 -0400 From: "Igor Tandetnik" Subject: Re: [sqlite] Implicit indices on TEMP tables To: sqlite-users@sqlite.org Message-ID: "Alex Ousherovitch" wrote in message news:5ee1928d06817b4788b64caf1a8517b00332a...@sfo-ex-01.ad.opentv.local > I am creating a set of the following TEMP tables > >CREATE TEMP TABLE IF NOT EXISTS mods_log_ (modrowid > INTEGER NOT NULL PRIMARY KEY) > > for every connection on my database, using the same name. These TEMP > tables AND their indices are supposed to be only visible within that > same database connection. > > But it appears that when populated from TEMP triggers, also created > for each connection - > >CREATE TEMP TRIGGER IF NOT EXISTS mods_log__trigger >AFTER INSERT ON >FOR EACH ROW BEGIN >INSERT INTO mods_log_ VALUES (new.rowid); >END > > -I get SQLITE_CONSTRAINT error for attempting to add non-unique > elements. Do you ever delete from ? When you do so, do you also delete a matching record from mods_log_? It's possible that a ROWID from a deleted record is reused for new record in , which would be a duplicate in mods_log_. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with ordering
2009/4/14 John Machin : >> As you can see, it takes ages and it shouldn't. > > I can't see ... why are you measuring CPU time instead of elapsed time? > what is the unit of measure? nanoseconds or centuries? Oh, my apologies. The actual measuring happens in the code that queries the database and it shows that the original query takes cca. 0.03 seconds. Is there any way to measure time spent on a query inside the SQLite command line interface? I though the ".timer on" was just it. > What do you get when you do > SELECT COUNT(*) FROM Event WHERE bringsSchedule = 0 AND sourceMachine_id > = 9 AND virtualClock <= 1000 AND parent_fk IS NOT NULL > ? > Is that a small number or a large number? If large, consider the > possibility that in your problem query it is finding all those rows, > sorting them on "id" then throwing away all but the row with the highest > "id". It is a number circa in between 0 and 200, depends on what exactly the sourceMachine_id and virtualClock values in the query are. In this case, it's 16. > SELECT * FROM Event WHERE id = ( > SELECT MAX(id) > FROM Event > WHERE bringsSchedule = 0 > AND sourceMachine_id = 9 > AND virtualClock <= 1000 > AND parent_fk IS NOT NULL > ); > > This may well run much faster. It runs circa three times faster (thank you!), but still - 0.01 seconds for such a query? I must be doing something else wrong... -- Lukáš Petrovický http://www.petrovicky.net/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Implicit indices on TEMP tables
"Alex Ousherovitch" wrote in message news:5ee1928d06817b4788b64caf1a8517b00332a...@sfo-ex-01.ad.opentv.local > I am creating a set of the following TEMP tables > >CREATE TEMP TABLE IF NOT EXISTS mods_log_ (modrowid > INTEGER NOT NULL PRIMARY KEY) > > for every connection on my database, using the same name. These TEMP > tables AND their indices are supposed to be only visible within that > same database connection. > > But it appears that when populated from TEMP triggers, also created > for each connection - > >CREATE TEMP TRIGGER IF NOT EXISTS mods_log__trigger >AFTER INSERT ON >FOR EACH ROW BEGIN >INSERT INTO mods_log_ VALUES (new.rowid); >END > > -I get SQLITE_CONSTRAINT error for attempting to add non-unique > elements. Do you ever delete from ? When you do so, do you also delete a matching record from mods_log_? It's possible that a ROWID from a deleted record is reused for new record in , which would be a duplicate in mods_log_. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PRAGMA read_uncommitted = 1
"Joanne Pham" wrote in message news:432996.36668...@web90306.mail.mud.yahoo.com > I have set my database connection to "PRAGMA read_uncommitted = 1". > Is this allow the dirty read? http://sqlite.org/sharedcache.html Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] fail to drop table in transaction
On 14/04/2009 12:21 PM, Wenton Thomas TOP-POSTED: > I have to drop the table,because I will use the same table name with > different table struct. Ever see those signs facing out from the end of a freeway exit: WRONG WAY! GO BACK! ? > > From: Kees Nuyt [snip] > What are you trying to accomplish? Try answering Kees's question -- you appear to have a *SERIOUS* design problem. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] fail to drop table in transaction
Well,It works in command tool. Maybe there exists some error in my code. I have to drop the table,because I will use the same table name with different table struct. Thanks for your help. From: Kees Nuyt To: sqlite-users@sqlite.org Sent: Monday, April 13, 2009 8:10:12 PM Subject: Re: [sqlite] fail to drop table in transaction On Mon, 13 Apr 2009 02:35:46 -0700 (PDT), Wenton Thomas wrote: > I didn't test it from command tool yet. Well, that is the first thing to try. > I have a table (call it table A) to record other > table's information. When I delete a table, I also > delete all its information recorded in table A. > I wonder whether it is legal to drop a table and > delete another table's records in one transaction. That shouldn't be a problem. But it is a weird construction, unless tableB is an application table, and tableA part of a dictionary. For a normal application, you wouldn't have to drop tableB, but just insert / delete rows. What are you trying to accomplish? -- ( Kees Nuyt ) c[_] ___ 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] PRAGMA read_uncommitted = 1
Hi All, I have set my database connection to "PRAGMA read_uncommitted = 1".Is this allow the dirty read? Thanks, JP ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with ordering
On 14/04/2009 6:29 AM, Lukáš Petrovický wrote: > Hello! > > I've been trying probably for ages to make the following work. I have > a table with following schema (most of the columns are irrelevant for > now): > > sqlite> .schema Event > CREATE TABLE Event (id integer, assignedCPUs varchar, bringsSchedule > integer not null, clock integer, deadline integer, expectedEnd > integer, expectedStart integer, job integer, jobHint integer, > neededCPUs integer, neededHDD integer, neededPlatform varchar, > neededRAM integer, virtualClock integer, parent_fk integer, > sourceMachine_id integer, targetMachine_id integer, type_id integer, > primary key (id)); > CREATE INDEX tIndex ON Event (type_id); > CREATE INDEX testIndex ON Event(sourceMachine_id, parent_fk, virtualClock); > > And I've been trying to run the following query: > > sqlite> SELECT * FROM Event WHERE bringsSchedule = 0 AND > sourceMachine_id = 9 AND virtualClock <= 1000 AND parent_fk IS NOT > NULL ORDER BY id DESC LIMIT 1; > 835|29|0|309493|0|334|834|9|| > CPU Time: user 0.027995 sys 0.00 > > As you can see, it takes ages and it shouldn't. I can't see ... why are you measuring CPU time instead of elapsed time? what is the unit of measure? nanoseconds or centuries? > When I don't use any > "ORDER BY" clause, I get: > > sqlite> SELECT * FROM Event WHERE bringsSchedule = 0 AND > sourceMachine_id = 9 AND virtualClock <= 1000 AND parent_fk IS NOT > NULL LIMIT 1; > 238||0|146026|0|95|236|9|| > CPU Time: user 0.00 sys 0.00 > > Please note that this one is lightning fast, while the one above it is > not. I've been messing around with indexes literally for hours and I > wasn't able to get it running properly. Perhaps I'm just missing > something. > > Would you please show me the light or perhaps just tell me where the > torch is? :-) I even found this [1] mailing, but there was no > explanation as to why the solution is better and thus I couldn't have > tweaked it for my scenario. What do you get when you do SELECT COUNT(*) FROM Event WHERE bringsSchedule = 0 AND sourceMachine_id = 9 AND virtualClock <= 1000 AND parent_fk IS NOT NULL ? Is that a small number or a large number? If large, consider the possibility that in your problem query it is finding all those rows, sorting them on "id" then throwing away all but the row with the highest "id". Perhaps you need something like: [untested] SELECT * FROM Event WHERE id = ( SELECT MAX(id) FROM Event WHERE bringsSchedule = 0 AND sourceMachine_id = 9 AND virtualClock <= 1000 AND parent_fk IS NOT NULL ); This may well run much faster. Bonus: it says exactly what I presume that you are trying to do, rather than relying on a side effect of ORDER BY and LIMIT used in conjunction. HTH, John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Implicit indices on TEMP tables
Hello list, I am creating a set of the following TEMP tables CREATE TEMP TABLE IF NOT EXISTS mods_log_ (modrowid INTEGER NOT NULL PRIMARY KEY) for every connection on my database, using the same name. These TEMP tables AND their indices are supposed to be only visible within that same database connection. But it appears that when populated from TEMP triggers, also created for each connection - CREATE TEMP TRIGGER IF NOT EXISTS mods_log__trigger AFTER INSERT ON FOR EACH ROW BEGIN INSERT INTO mods_log_ VALUES (new.rowid); END -I get SQLITE_CONSTRAINT error for attempting to add non-unique elements. The mods log temp tables get cleared from all records before every INSERT on , so they do not contain duplicates, so it must be generated by the index. So it seems that the index on modrowid on the various mods_log_ ends up being shared. Any ideas what is going on? Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Problem with ordering
Hello! I've been trying probably for ages to make the following work. I have a table with following schema (most of the columns are irrelevant for now): sqlite> .schema Event CREATE TABLE Event (id integer, assignedCPUs varchar, bringsSchedule integer not null, clock integer, deadline integer, expectedEnd integer, expectedStart integer, job integer, jobHint integer, neededCPUs integer, neededHDD integer, neededPlatform varchar, neededRAM integer, virtualClock integer, parent_fk integer, sourceMachine_id integer, targetMachine_id integer, type_id integer, primary key (id)); CREATE INDEX tIndex ON Event (type_id); CREATE INDEX testIndex ON Event(sourceMachine_id, parent_fk, virtualClock); And I've been trying to run the following query: sqlite> SELECT * FROM Event WHERE bringsSchedule = 0 AND sourceMachine_id = 9 AND virtualClock <= 1000 AND parent_fk IS NOT NULL ORDER BY id DESC LIMIT 1; 835|29|0|309493|0|334|834|9|| CPU Time: user 0.027995 sys 0.00 As you can see, it takes ages and it shouldn't. When I don't use any "ORDER BY" clause, I get: sqlite> SELECT * FROM Event WHERE bringsSchedule = 0 AND sourceMachine_id = 9 AND virtualClock <= 1000 AND parent_fk IS NOT NULL LIMIT 1; 238||0|146026|0|95|236|9|| CPU Time: user 0.00 sys 0.00 Please note that this one is lightning fast, while the one above it is not. I've been messing around with indexes literally for hours and I wasn't able to get it running properly. Perhaps I'm just missing something. Would you please show me the light or perhaps just tell me where the torch is? :-) I even found this [1] mailing, but there was no explanation as to why the solution is better and thus I couldn't have tweaked it for my scenario. Any help greatly appreciated. Best regards! [1] http://www.mail-archive.com/sqlite-users@sqlite.org/msg29676.html -- Lukáš Petrovický http://www.petrovicky.net/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Storage of blobs: to encode or not to encode?
Julian Bui wrote: > One thing I don't understand is, you wrote: > > sqlite> CREATE TABLE foo (a INTEGER PRIMARY KEY, b NOTABLOB); >> > > I take it that "NOTABLOB" is any data type I want, since "NOTABLOB" > is not a > keyword/datatype. Doesn't that mean I will restrict myself to a > single > datatype? SQLite will accept pretty much any sequence of words as a column type. It doesn't really mean much to the engine. You could do CREATE TABLE foo (a LOREM IPSUM); and it would work. For more details, see http://sqlite.org/datatype3.html Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Storage of blobs: to encode or not to encode?
On Mon, Apr 13, 2009 at 2:00 PM, Julian Bui wrote: > Thanks for your response Puneet. > > I was originally just converting my non-blobs (ints, text, reals) to byte > arrays since I need to do that in my program anyway. > > But now since you've mentioned it, I will look into storing multiple types > in the same column.. > > One thing I don't understand is, you wrote: > >> sqlite> CREATE TABLE foo (a INTEGER PRIMARY KEY, b NOTABLOB); > > I take it that "NOTABLOB" is any data type I want, since "NOTABLOB" is not a > keyword/datatype. Doesn't that mean I will restrict myself to a single > datatype? If you don't like the datatype NOTABLOB, try datatype THINGAMAJIG Short answer, SQLite doesn't care other than what it states in the docs (see the page dataypes on sqlite.org). And, even if you declare the legit datatypes that it understands (INTEGER, TEXT, REAL and BLOB), it will croak only if there is a CHECK constraint (for how that works, see Richard's email on that subject from a few minutes ago). It does care about "INTEGER PRIMARY KEY," a magical combination of keywords that makes it croak if the inserted value is not an INTEGER or not unique. Consider sqlite> CREATE TABLE foo (a INTEGER PRIMARY KEY, b SLUMDOGMILLIONAIRE); sqlite> INSERT INTO foo (b) VALUES (1); sqlite> SELECT * FROM foo; 1|1 sqlite> INSERT INTO foo (a, b) VALUES (1, 'one'); SQL error: PRIMARY KEY must be unique sqlite> INSERT INTO foo (a, b) VALUES ('one', 'one'); SQL error: datatype mismatch > > Thanks, > Julian > > On Mon, Apr 13, 2009 at 11:47 AM, P Kishor wrote: >> >> On Mon, Apr 13, 2009 at 1:43 PM, Julian Bui wrote: >> > Oops didn't read your other comment: >> > >> > >> >> Why? SQLite is perfectly capable of storing multiple datatypes in a >> >> single column. It sounds to me like you are making your problem much >> >> harder than it needs to be. >> > >> > >> > Are you talking about the column affinity option? >> >> What Richard is saying is that SQLite will happily take whatever kind >> of value you want to stuff in a column (except, of course, for an >> INTEGER PRIMARY KEY column). So, why bother BLOBbing your inputs if >> they are not binary... from your original post, they are not binary, >> but could be INTEGER, TEXT, REAL well, just stuff them in the >> column. No need to convert them to BLOB. Consider the following -- >> >> sqlite> CREATE TABLE foo (a INTEGER PRIMARY KEY, b NOTABLOB); >> sqlite> INSERT INTO foo (b) VALUES (1); >> sqlite> INSERT INTO foo (b) VALUES ('one'); >> sqlite> INSERT INTO foo (b) VALUES (1.001); >> sqlite> SELECT * FROM foo; >> 1|1 >> 2|one >> 3|1.001 >> >> >> >> > >> > -Julian >> > >> > On Mon, Apr 13, 2009 at 11:41 AM, Julian Bui >> > wrote: >> > >> >> By encoding I mean using some function to transform my binary data in >> >> such >> >> a way that removes the terminators and single quote characters. >> >> However, >> >> from what you said, it sounds like I do not need to worry about >> >> encoding. >> >> >> >> I am using ...VALUES(?) in a prepared statement and I will be using >> >> either >> >> .setBytes() or .setBlob() in my JDBC driver. And again, it sounds like >> >> I >> >> will not need to encode, from your reply. >> >> >> >> Thanks, >> >> Julian >> >> >> >> >> >> On Mon, Apr 13, 2009 at 11:26 AM, D. Richard Hipp >> >> wrote: >> >> >> >>> >> >>> On Apr 13, 2009, at 2:14 PM, Julian Bui wrote: >> >>> >> >>> > Hi all, >> >>> > >> >>> > I have a question about encoding blob bytes before inserting a >> >>> > record into >> >>> > SQLite. >> >>> > >> >>> > CONTEXT: >> >>> > I have a base java (I'm using JDBC) class that has a many children. >> >>> > Each >> >>> > child may store a different data type (such as String, int, long, >> >>> > etc.) in >> >>> > its VALUE field. Since this value field could be many different >> >>> > data types, >> >>> > I will be storing the field's bytes into the database as a blob. >> >>> >> >>> Why? SQLite is perfectly capable of storing multiple datatypes in a >> >>> single column. It sounds to me like you are making your problem much >> >>> harder than it needs to be. >> >>> >> >>> > >> >>> > >> >>> > QUESTIONS: >> >>> > >> >>> > -I have seen a couple of places on the internet saying that SQLite >> >>> > cannot >> >>> > inserting binary data unless it has been encoded. Can someone >> >>> > confirm this >> >>> > or preferrably point me to an OFFICIAL sqlite statement/page saying >> >>> > that >> >>> > this is true? >> >>> >> >>> I'm not sure what you mean by "encoded". If you do: >> >>> >> >>> INSERT INTO sometable VALUES(?) >> >>> >> >>> And then bind a blob to the ?, you do not need to make any >> >>> transformations to the blob ahead of time. On the other hand, if you >> >>> say: >> >>> >> >>> INSERT INTO sometable VALUES(x'0123456789abcdef'); >> >>> >> >>> Then clearly you have had to convert your 8-byte blob into hexadecimal >> >>> in order to insert it into the middle of your INSERT statement. >> >>> >> >>> The first technique (
Re: [sqlite] PRAGMA doesn't support parameter binds?
As an alternative, you can use the WinDgb tool from MSFT. It is not the nicest debugger in the world, but understands all the MSFT PDB formats. It is free for download from the MSDN site. While it is a bit slow with large source files like the SQLite amalgamation, it does handle them fine. m...@mwlabs.de wrote: > How to debug the SQLite amalgation with Visual Studio 2008. > > To get the debugger going you need to strip out the comments and empty lines > from the sqlite.c source file. > This can be done easily with two regular expressions for search and replace: > > First replace (Ctrl+H) > > (/\*(\n|.)@\*/)|(//.*$) > > With to get rid of comments, then replace > > ^$\n > > With to get rid of the empty lines. > > This brings down the sqlite.c to less than 60,000 lines, and the debugger > will work again. > > > 'Hope this helps. > > -- Mario > > > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Vinnie > Sent: Monday, April 13, 2009 12:53 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] PRAGMA doesn't support parameter binds? > > > Sorry for only posting when I have a problem...but... > > I'm doing > > PRAGMA user_version=?; > > And getting result code SQLITE_ERROR (1) from sqlite3_prepare_v2(). Of > course I can't step into the sqlite3.c code because the Visual Studio 2008 > debugger gets hopelessly confused when confronted with a file whose line > number representations exceed the capacity of an unsigned 16 bit integer. > > ___ > 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] Storage of blobs: to encode or not to encode?
Thanks for your response Puneet. I was originally just converting my non-blobs (ints, text, reals) to byte arrays since I need to do that in my program anyway. But now since you've mentioned it, I will look into storing multiple types in the same column.. One thing I don't understand is, you wrote: sqlite> CREATE TABLE foo (a INTEGER PRIMARY KEY, b NOTABLOB); > I take it that "NOTABLOB" is any data type I want, since "NOTABLOB" is not a keyword/datatype. Doesn't that mean I will restrict myself to a single datatype? Thanks, Julian On Mon, Apr 13, 2009 at 11:47 AM, P Kishor wrote: > On Mon, Apr 13, 2009 at 1:43 PM, Julian Bui wrote: > > Oops didn't read your other comment: > > > > > >> Why? SQLite is perfectly capable of storing multiple datatypes in a > >> single column. It sounds to me like you are making your problem much > >> harder than it needs to be. > > > > > > Are you talking about the column affinity option? > > What Richard is saying is that SQLite will happily take whatever kind > of value you want to stuff in a column (except, of course, for an > INTEGER PRIMARY KEY column). So, why bother BLOBbing your inputs if > they are not binary... from your original post, they are not binary, > but could be INTEGER, TEXT, REAL well, just stuff them in the > column. No need to convert them to BLOB. Consider the following -- > > sqlite> CREATE TABLE foo (a INTEGER PRIMARY KEY, b NOTABLOB); > sqlite> INSERT INTO foo (b) VALUES (1); > sqlite> INSERT INTO foo (b) VALUES ('one'); > sqlite> INSERT INTO foo (b) VALUES (1.001); > sqlite> SELECT * FROM foo; > 1|1 > 2|one > 3|1.001 > > > > > > > -Julian > > > > On Mon, Apr 13, 2009 at 11:41 AM, Julian Bui > wrote: > > > >> By encoding I mean using some function to transform my binary data in > such > >> a way that removes the terminators and single quote characters. > However, > >> from what you said, it sounds like I do not need to worry about > encoding. > >> > >> I am using ...VALUES(?) in a prepared statement and I will be using > either > >> .setBytes() or .setBlob() in my JDBC driver. And again, it sounds like > I > >> will not need to encode, from your reply. > >> > >> Thanks, > >> Julian > >> > >> > >> On Mon, Apr 13, 2009 at 11:26 AM, D. Richard Hipp > wrote: > >> > >>> > >>> On Apr 13, 2009, at 2:14 PM, Julian Bui wrote: > >>> > >>> > Hi all, > >>> > > >>> > I have a question about encoding blob bytes before inserting a > >>> > record into > >>> > SQLite. > >>> > > >>> > CONTEXT: > >>> > I have a base java (I'm using JDBC) class that has a many children. > >>> > Each > >>> > child may store a different data type (such as String, int, long, > >>> > etc.) in > >>> > its VALUE field. Since this value field could be many different > >>> > data types, > >>> > I will be storing the field's bytes into the database as a blob. > >>> > >>> Why? SQLite is perfectly capable of storing multiple datatypes in a > >>> single column. It sounds to me like you are making your problem much > >>> harder than it needs to be. > >>> > >>> > > >>> > > >>> > QUESTIONS: > >>> > > >>> > -I have seen a couple of places on the internet saying that SQLite > >>> > cannot > >>> > inserting binary data unless it has been encoded. Can someone > >>> > confirm this > >>> > or preferrably point me to an OFFICIAL sqlite statement/page saying > >>> > that > >>> > this is true? > >>> > >>> I'm not sure what you mean by "encoded". If you do: > >>> > >>> INSERT INTO sometable VALUES(?) > >>> > >>> And then bind a blob to the ?, you do not need to make any > >>> transformations to the blob ahead of time. On the other hand, if you > >>> say: > >>> > >>> INSERT INTO sometable VALUES(x'0123456789abcdef'); > >>> > >>> Then clearly you have had to convert your 8-byte blob into hexadecimal > >>> in order to insert it into the middle of your INSERT statement. > >>> > >>> The first technique (the use of ? and sqlite3_bind_blob()) is > >>> preferred since it is both faster and less error-prone. > >>> > >>> > >>> 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 > > > > > > -- > Puneet Kishor http://www.punkish.org/ > Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ > Carbon Model http://carbonmodel.org/ > Open Source Geospatial Foundation http://www.osgeo.org/ > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-us
Re: [sqlite] Whoops! Huge misunderstanding of multi-threaded database
Vinnie wrote: > So I'm getting SQLITE_BUSY now. I have one thread inserting rows > while another thread tries to read a row from the same table. I had a > bad implementation where I was keeping the transaction open far > longer than necessary so I think I went over some 5 second rule? Does > SQLite wait up some length of time before returning a "busy" error? Not unless you ask it to - see sqlite3_busy_timeout. > How can I tell SQLite to wait forever? You could specify a really large value in sqlite3_busy_timeout. Not forever, but 0x7FFF is about 24 days in milliseconds. > Is this something desirable? That's for you to decide. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "PRAGMA" Check constraints
Thank you for the fast and clear answers! Cheers Jan D. Richard Hipp schrieb: > On Apr 13, 2009, at 2:19 PM, jan wrote: > >> Hi, >> >> is there a way to query the check constraints added to a column >> definition? >> > > No. > > Actually, SQLite does not support CHECK constraints assigned to > individual columns. Sure, you can include the CHECK constraint on an > individual column when you create the table, but what SQLite does > internally is coalesce all of the column CHECK constraints into one > big whole-table CHECK constraint. In other words, if you type this: > > CREATE TABLE t1( > a INTEGER CHECK( a<10 ), > b VARCHAR(10) CHECK( length(b)>5 > ); > > SQLite will actually implemented it as: > > CREATE TABLE t1( > a INTEGER, > b TEXT, > CHECK( a<10 AND length(b)>5 ) > ); > > To put it another way, all CHECK constraints on a table are gathered > together into a single boolean expression that is evaluated after > every INSERT or UPDATE and fails the operation if that single > expression is false. SQLite does not keep track of where the > individual terms in the CHECK constraint originally came from. > > 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
Re: [sqlite] Storage of blobs: to encode or not to encode?
On Mon, Apr 13, 2009 at 1:43 PM, Julian Bui wrote: > Oops didn't read your other comment: > > >> Why? SQLite is perfectly capable of storing multiple datatypes in a >> single column. It sounds to me like you are making your problem much >> harder than it needs to be. > > > Are you talking about the column affinity option? What Richard is saying is that SQLite will happily take whatever kind of value you want to stuff in a column (except, of course, for an INTEGER PRIMARY KEY column). So, why bother BLOBbing your inputs if they are not binary... from your original post, they are not binary, but could be INTEGER, TEXT, REAL well, just stuff them in the column. No need to convert them to BLOB. Consider the following -- sqlite> CREATE TABLE foo (a INTEGER PRIMARY KEY, b NOTABLOB); sqlite> INSERT INTO foo (b) VALUES (1); sqlite> INSERT INTO foo (b) VALUES ('one'); sqlite> INSERT INTO foo (b) VALUES (1.001); sqlite> SELECT * FROM foo; 1|1 2|one 3|1.001 > > -Julian > > On Mon, Apr 13, 2009 at 11:41 AM, Julian Bui wrote: > >> By encoding I mean using some function to transform my binary data in such >> a way that removes the terminators and single quote characters. However, >> from what you said, it sounds like I do not need to worry about encoding. >> >> I am using ...VALUES(?) in a prepared statement and I will be using either >> .setBytes() or .setBlob() in my JDBC driver. And again, it sounds like I >> will not need to encode, from your reply. >> >> Thanks, >> Julian >> >> >> On Mon, Apr 13, 2009 at 11:26 AM, D. Richard Hipp wrote: >> >>> >>> On Apr 13, 2009, at 2:14 PM, Julian Bui wrote: >>> >>> > Hi all, >>> > >>> > I have a question about encoding blob bytes before inserting a >>> > record into >>> > SQLite. >>> > >>> > CONTEXT: >>> > I have a base java (I'm using JDBC) class that has a many children. >>> > Each >>> > child may store a different data type (such as String, int, long, >>> > etc.) in >>> > its VALUE field. Since this value field could be many different >>> > data types, >>> > I will be storing the field's bytes into the database as a blob. >>> >>> Why? SQLite is perfectly capable of storing multiple datatypes in a >>> single column. It sounds to me like you are making your problem much >>> harder than it needs to be. >>> >>> > >>> > >>> > QUESTIONS: >>> > >>> > -I have seen a couple of places on the internet saying that SQLite >>> > cannot >>> > inserting binary data unless it has been encoded. Can someone >>> > confirm this >>> > or preferrably point me to an OFFICIAL sqlite statement/page saying >>> > that >>> > this is true? >>> >>> I'm not sure what you mean by "encoded". If you do: >>> >>> INSERT INTO sometable VALUES(?) >>> >>> And then bind a blob to the ?, you do not need to make any >>> transformations to the blob ahead of time. On the other hand, if you >>> say: >>> >>> INSERT INTO sometable VALUES(x'0123456789abcdef'); >>> >>> Then clearly you have had to convert your 8-byte blob into hexadecimal >>> in order to insert it into the middle of your INSERT statement. >>> >>> The first technique (the use of ? and sqlite3_bind_blob()) is >>> preferred since it is both faster and less error-prone. >>> >>> >>> 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 > -- Puneet Kishor http://www.punkish.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Carbon Model http://carbonmodel.org/ Open Source Geospatial Foundation http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Whoops! Huge misunderstanding of multi-threaded database
So I'm getting SQLITE_BUSY now. I have one thread inserting rows while another thread tries to read a row from the same table. I had a bad implementation where I was keeping the transaction open far longer than necessary so I think I went over some 5 second rule? Does SQLite wait up some length of time before returning a "busy" error? How can I tell SQLite to wait forever? Is this something desirable? Its not convenient for me to check for a busy result in every line of code that makes a database call. In all cases I would want to re-execute the statement over and over again until it goes through. I was under the impression that SQLite would simply block until the other operations completed. How do you get this behavior? Thanks! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Storage of blobs: to encode or not to encode?
Oops didn't read your other comment: > Why? SQLite is perfectly capable of storing multiple datatypes in a > single column. It sounds to me like you are making your problem much > harder than it needs to be. Are you talking about the column affinity option? -Julian On Mon, Apr 13, 2009 at 11:41 AM, Julian Bui wrote: > By encoding I mean using some function to transform my binary data in such > a way that removes the terminators and single quote characters. However, > from what you said, it sounds like I do not need to worry about encoding. > > I am using ...VALUES(?) in a prepared statement and I will be using either > .setBytes() or .setBlob() in my JDBC driver. And again, it sounds like I > will not need to encode, from your reply. > > Thanks, > Julian > > > On Mon, Apr 13, 2009 at 11:26 AM, D. Richard Hipp wrote: > >> >> On Apr 13, 2009, at 2:14 PM, Julian Bui wrote: >> >> > Hi all, >> > >> > I have a question about encoding blob bytes before inserting a >> > record into >> > SQLite. >> > >> > CONTEXT: >> > I have a base java (I'm using JDBC) class that has a many children. >> > Each >> > child may store a different data type (such as String, int, long, >> > etc.) in >> > its VALUE field. Since this value field could be many different >> > data types, >> > I will be storing the field's bytes into the database as a blob. >> >> Why? SQLite is perfectly capable of storing multiple datatypes in a >> single column. It sounds to me like you are making your problem much >> harder than it needs to be. >> >> > >> > >> > QUESTIONS: >> > >> > -I have seen a couple of places on the internet saying that SQLite >> > cannot >> > inserting binary data unless it has been encoded. Can someone >> > confirm this >> > or preferrably point me to an OFFICIAL sqlite statement/page saying >> > that >> > this is true? >> >> I'm not sure what you mean by "encoded". If you do: >> >> INSERT INTO sometable VALUES(?) >> >> And then bind a blob to the ?, you do not need to make any >> transformations to the blob ahead of time. On the other hand, if you >> say: >> >> INSERT INTO sometable VALUES(x'0123456789abcdef'); >> >> Then clearly you have had to convert your 8-byte blob into hexadecimal >> in order to insert it into the middle of your INSERT statement. >> >> The first technique (the use of ? and sqlite3_bind_blob()) is >> preferred since it is both faster and less error-prone. >> >> >> 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
Re: [sqlite] Storage of blobs: to encode or not to encode?
By encoding I mean using some function to transform my binary data in such a way that removes the terminators and single quote characters. However, from what you said, it sounds like I do not need to worry about encoding. I am using ...VALUES(?) in a prepared statement and I will be using either .setBytes() or .setBlob() in my JDBC driver. And again, it sounds like I will not need to encode, from your reply. Thanks, Julian On Mon, Apr 13, 2009 at 11:26 AM, D. Richard Hipp wrote: > > On Apr 13, 2009, at 2:14 PM, Julian Bui wrote: > > > Hi all, > > > > I have a question about encoding blob bytes before inserting a > > record into > > SQLite. > > > > CONTEXT: > > I have a base java (I'm using JDBC) class that has a many children. > > Each > > child may store a different data type (such as String, int, long, > > etc.) in > > its VALUE field. Since this value field could be many different > > data types, > > I will be storing the field's bytes into the database as a blob. > > Why? SQLite is perfectly capable of storing multiple datatypes in a > single column. It sounds to me like you are making your problem much > harder than it needs to be. > > > > > > > QUESTIONS: > > > > -I have seen a couple of places on the internet saying that SQLite > > cannot > > inserting binary data unless it has been encoded. Can someone > > confirm this > > or preferrably point me to an OFFICIAL sqlite statement/page saying > > that > > this is true? > > I'm not sure what you mean by "encoded". If you do: > > INSERT INTO sometable VALUES(?) > > And then bind a blob to the ?, you do not need to make any > transformations to the blob ahead of time. On the other hand, if you > say: > > INSERT INTO sometable VALUES(x'0123456789abcdef'); > > Then clearly you have had to convert your 8-byte blob into hexadecimal > in order to insert it into the middle of your INSERT statement. > > The first technique (the use of ? and sqlite3_bind_blob()) is > preferred since it is both faster and less error-prone. > > > 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
Re: [sqlite] "PRAGMA" Check constraints
On Apr 13, 2009, at 2:19 PM, jan wrote: > Hi, > > is there a way to query the check constraints added to a column > definition? > No. Actually, SQLite does not support CHECK constraints assigned to individual columns. Sure, you can include the CHECK constraint on an individual column when you create the table, but what SQLite does internally is coalesce all of the column CHECK constraints into one big whole-table CHECK constraint. In other words, if you type this: CREATE TABLE t1( a INTEGER CHECK( a<10 ), b VARCHAR(10) CHECK( length(b)>5 ); SQLite will actually implemented it as: CREATE TABLE t1( a INTEGER, b TEXT, CHECK( a<10 AND length(b)>5 ) ); To put it another way, all CHECK constraints on a table are gathered together into a single boolean expression that is evaluated after every INSERT or UPDATE and fails the operation if that single expression is false. SQLite does not keep track of where the individual terms in the CHECK constraint originally came from. 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] "PRAGMA" Check constraints
jan wrote: > is there a way to query the check constraints added to a column > definition? Not really. The best you can do is select sql from sqlite_master where type='table' and name='your_table_name'; then parse raw SQL. Note that there may be table level check constraints involving more than one column (in fact, nothing says that a check constraint attached to a column must actually refer to that column and that column only). Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Storage of blobs: to encode or not to encode?
On Apr 13, 2009, at 2:14 PM, Julian Bui wrote: > Hi all, > > I have a question about encoding blob bytes before inserting a > record into > SQLite. > > CONTEXT: > I have a base java (I'm using JDBC) class that has a many children. > Each > child may store a different data type (such as String, int, long, > etc.) in > its VALUE field. Since this value field could be many different > data types, > I will be storing the field's bytes into the database as a blob. Why? SQLite is perfectly capable of storing multiple datatypes in a single column. It sounds to me like you are making your problem much harder than it needs to be. > > > QUESTIONS: > > -I have seen a couple of places on the internet saying that SQLite > cannot > inserting binary data unless it has been encoded. Can someone > confirm this > or preferrably point me to an OFFICIAL sqlite statement/page saying > that > this is true? I'm not sure what you mean by "encoded". If you do: INSERT INTO sometable VALUES(?) And then bind a blob to the ?, you do not need to make any transformations to the blob ahead of time. On the other hand, if you say: INSERT INTO sometable VALUES(x'0123456789abcdef'); Then clearly you have had to convert your 8-byte blob into hexadecimal in order to insert it into the middle of your INSERT statement. The first technique (the use of ? and sqlite3_bind_blob()) is preferred since it is both faster and less error-prone. 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] "PRAGMA" Check constraints
Hi, is there a way to query the check constraints added to a column definition? Thanks and bye Jan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Storage of blobs: to encode or not to encode?
Hi all, I have a question about encoding blob bytes before inserting a record into SQLite. CONTEXT: I have a base java (I'm using JDBC) class that has a many children. Each child may store a different data type (such as String, int, long, etc.) in its VALUE field. Since this value field could be many different data types, I will be storing the field's bytes into the database as a blob. QUESTIONS: -I have seen a couple of places on the internet saying that SQLite cannot inserting binary data unless it has been encoded. Can someone confirm this or preferrably point me to an OFFICIAL sqlite statement/page saying that this is true? -Will I need to encode my data? I do not think the String.getBytes() command returns bytes including a terminator. However, since the terminator is just 0, I assume that even storing the integer 0 as bytes in the blob would be a problem. Is it not? -It also looks like the single quote character ' (ascii decimal 39) is a problem for binary? So this also means storing integer 39 is a problem? Storing these two numbers doesn't seem like it should be a problem to sqlite, which is why I need to make sure I actually need to encode or not. Please help me out. Thanks, Julian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PRAGMA doesn't support parameter binds?
How to debug the SQLite amalgation with Visual Studio 2008. To get the debugger going you need to strip out the comments and empty lines from the sqlite.c source file. This can be done easily with two regular expressions for search and replace: First replace (Ctrl+H) (/\*(\n|.)@\*/)|(//.*$) With to get rid of comments, then replace ^$\n With to get rid of the empty lines. This brings down the sqlite.c to less than 60,000 lines, and the debugger will work again. 'Hope this helps. -- Mario -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Vinnie Sent: Monday, April 13, 2009 12:53 PM To: sqlite-users@sqlite.org Subject: [sqlite] PRAGMA doesn't support parameter binds? Sorry for only posting when I have a problem...but... I'm doing PRAGMA user_version=?; And getting result code SQLITE_ERROR (1) from sqlite3_prepare_v2(). Of course I can't step into the sqlite3.c code because the Visual Studio 2008 debugger gets hopelessly confused when confronted with a file whose line number representations exceed the capacity of an unsigned 16 bit integer. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] step() fails with SQLITE_BUSY after BEGIN EXCLUSIVETRANSACTION - SOLVED
Hi, Marcus I'm absolutely sure that the problem is in my code and not a SQLite problem. SQLite works well in MT, if one respects the documented limits. I just have no handle currently to find the problem. As we all know, Multi-threading is tricky ;-) I will strip down my MT code until I get it running, and then add more threads to find out which one causes the problem. My guess is that somewhere a prepared statement or other "per-thread" SQlite element is created in one thread and then used from another. Or something like that. -- Mario -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Marcus Grimm Sent: Monday, April 13, 2009 7:21 PM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] step() fails with SQLITE_BUSY after BEGIN EXCLUSIVETRANSACTION Mario, I'm sorry but I'm running out of ideas... I can only repeat that sqlite works well with this kind of approach. I'm using it in a database server without a problem so far and there I use the exclusive mode to block the threads. However, when I started using sqlite for this I also run into this kind of problems but all of them where related to bug and missusage of some sqlite api functions. I also was lost in one case and decided to build a little test-code that reflects my implementation-style and allmost immediately I got the right tip by the mailing list. The result can be seen here: http://www.sqlite.org/cvstrac/wiki?p=SampleCode I still suggest, if nothing else helps, that you try to make an extraction of your implementation, as simple as possible, and if this still blocks after a thread has obtained the exclusive lock you may post it here and I'm sure you will get a quick reply about what's wrong... Marcus ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] step() fails with SQLITE_BUSY after BEGIN EXCLUSIVETRANSACTION
Mario, I'm sorry but I'm running out of ideas... I can only repeat that sqlite works well with this kind of approach. I'm using it in a database server without a problem so far and there I use the exclusive mode to block the threads. However, when I started using sqlite for this I also run into this kind of problems but all of them where related to bug and missusage of some sqlite api functions. I also was lost in one case and decided to build a little test-code that reflects my implementation-style and allmost immediately I got the right tip by the mailing list. The result can be seen here: http://www.sqlite.org/cvstrac/wiki?p=SampleCode I still suggest, if nothing else helps, that you try to make an extraction of your implementation, as simple as possible, and if this still blocks after a thread has obtained the exclusive lock you may post it here and I'm sure you will get a quick reply about what's wrong... Marcus > > Marcus > > thanks for your suggestions. I have of course checked the obvious things > before posting here. > > Both the BEGIN EXCLUSIVE and the COMMIT return SQLITE_OK. > > Each thread opens its own db handle with sqlite_open and operates on it. > These are completely isolated, they don't know about each other and they > do > not share any data or sqlite constructs. Both threads work flawlessly as > long as they don't operate in parallel. > > I have compiled SQLite with SQLITE_THREADSAFE=1 and the checks in the > source > code of SQLite use the mutexes to protect the library. Looks good that > far. > > > I cannot post the source code easily because I use my wrapper class, and I > would have to strip it down to the core 'C' SQLite calls to show you what > actually is done. This wrapper is in use for quite some time and has been > tested for a year now. > > But what I do in these two threads which cause the trouble is really > simple: > > > 1: if BEGIN EXCLUSIVE TRANSACTION successful then { > 2: INSERT INTO... > 3: DELETE FROM... > 4: COMMIT > 5: } > > Thread A blocks in 1: because it waits for the transaction. > Thread B blocks in line 2: but has successfully opened an exclusive > transaction. > > I now wonder why it can block in the INSERT after successfully opening an > exclusive transaction? > I could not find an explanation in the online docs for this behavior. My > impression was that if the BEGIN EXCLUSIVE succeeds, further operations on > the same db handle cannot block. > > The threads may even work on different tables, and still the deadlock > occurs. > > > -- Mario > > > > -Original Message- > > I have no idea why it doesn't work in that way, it used to in my > application. however, just a few points: > > maybe your BEGIN wasn't successful and you didn't realize ? > maybe you run the INSERT on a DB handle that was not the one that invoked > the BEGIN ? > maybe your COMMIT wasn't successful and you didn't realize ? > maybe your are not in threadsave mode ? > > I suggest that you post at least a part of your code here, or even better > a > short example that shows the problem. > This usually gives the people here a better chance to provide useful > hints. > > hope this helps > > Marcus > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.13
Where can i found a WinCE/ARM precompiled for this version?, there is no official one. -- Zaher Dirkey ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] fail to drop table in transaction
On Mon, 13 Apr 2009 02:35:46 -0700 (PDT), Wenton Thomas wrote: > I didn't test it from command tool yet. Well, that is the first thing to try. > I have a table (call it table A) to record other > table's information. When I delete a table, I also > delete all its information recorded in table A. > I wonder whether it is legal to drop a table and > delete another table's records in one transaction. That shouldn't be a problem. But it is a weird construction, unless tableB is an application table, and tableA part of a dictionary. For a normal application, you wouldn't have to drop tableB, but just insert / delete rows. What are you trying to accomplish? -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PRAGMA doesn't support parameter binds?
On Apr 13, 2009, at 5:52 PM, Vinnie wrote: > > Sorry for only posting when I have a problem...but... > > I'm doing > > PRAGMA user_version=?; > > And getting result code SQLITE_ERROR (1) from sqlite3_prepare_v2(). > Of course I can't step into the sqlite3.c code because the Visual > Studio 2008 debugger gets hopelessly confused when confronted with a > file whose line number representations exceed the capacity of an > unsigned 16 bit integer. > > So I check the syntax diagram and a pragma-value only has { signed- > number, name, string-literal } as choices. Whereas an "expression" > in the syntax diagram (used in a SELECT statement for example) has > { ..., bind-parameter, ... }. > > I would prefer to use parameter binds to keep the number of > functions in my wrapper down (and eliminate the need for a printf- > style API to sqlite3) so can anyone confirm or deny that parameter > binds do in fact not work for PRAGMA statements? Bound parameters do not work with pragma statements. Dan. > > > Thanks! > > ___ > 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] PRAGMA doesn't support parameter binds?
Sorry for only posting when I have a problem...but... I'm doing PRAGMA user_version=?; And getting result code SQLITE_ERROR (1) from sqlite3_prepare_v2(). Of course I can't step into the sqlite3.c code because the Visual Studio 2008 debugger gets hopelessly confused when confronted with a file whose line number representations exceed the capacity of an unsigned 16 bit integer. So I check the syntax diagram and a pragma-value only has { signed-number, name, string-literal } as choices. Whereas an "expression" in the syntax diagram (used in a SELECT statement for example) has { ..., bind-parameter, ... }. I would prefer to use parameter binds to keep the number of functions in my wrapper down (and eliminate the need for a printf-style API to sqlite3) so can anyone confirm or deny that parameter binds do in fact not work for PRAGMA statements? Thanks! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite version 3.6.13
SQLite version 3.6.13 is now available on the SQLite website: http://www.sqlite.org/ Version 3.6.13 is a bug-fix release only. There are no new features or enhancements. Upgrading is optional. 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] fail to drop table in transaction
I didn't test it from command tool yet. I have a table (call it table A) to record other table's information. When I delete a table, I also delete all its information recorded in table A. I wonder whether it is legal to drop a table and delete another table's records in one transaction. From: Kees Nuyt To: sqlite-users@sqlite.org Sent: Monday, April 13, 2009 3:22:58 PM Subject: Re: [sqlite] fail to drop table in transaction On Sun, 12 Apr 2009 20:46:40 -0700 (PDT), Wenton Thomas wrote: >I created two tables A and B. >There exists a record which contains B's information. > >Now I need to drop table B and delete all its information in table A. >The two actions were wrapped in a transaction,but dropping table always >fail. >Error no is >SQLITE_CANTOPENwhich means"Unable to open the database file". >The sqlite version is 3.5.9. > >Could anyone help me? It's hard to tell from your description what you are doing exactly. Does the same SQL work correctly from the command line tool? It is not very common to drop a table when data changes, usually the schema is stable and rows are inserted into and deleted from tables, or column values changed. It might indicate a flaw in your database schema design. -- ( Kees Nuyt ) c[_] ___ 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] fail to drop table in transaction
On Sun, 12 Apr 2009 20:46:40 -0700 (PDT), Wenton Thomas wrote: >I created two tables A and B. >There exists a record which contains B's information. > >Now I need to drop table B and delete all its information in table A. >The two actions were wrapped in a transaction,but dropping table always >fail. >Error no is >SQLITE_CANTOPENwhich means"Unable to open the database file". >The sqlite version is 3.5.9. > >Could anyone help me? It's hard to tell from your description what you are doing exactly. Does the same SQL work correctly from the command line tool? It is not very common to drop a table when data changes, usually the schema is stable and rows are inserted into and deleted from tables, or column values changed. It might indicate a flaw in your database schema design. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users