Re: [sqlite] Implicit indices on TEMP tables

[Alex Ousherovitch]

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

[Lukáš Petrovický]

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

[Igor Tandetnik]

"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

[Igor Tandetnik]

"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

[John Machin]

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

[Wenton Thomas]

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

[Joanne Pham]

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

[John Machin]

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

[Alex Ousherovitch]

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

[Lukáš Petrovický]

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?

[Igor Tandetnik]

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?

[P Kishor]

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
>> 

Re: [sqlite] PRAGMA doesn't support parameter binds?

[Mark Spiegel]

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?

[Julian Bui]

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

Re: [sqlite] Whoops! Huge misunderstanding of multi-threaded database

[Igor Tandetnik]

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

[jan]

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?

[P Kishor]

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

[Vinnie]

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?

[Julian Bui]

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

[D. Richard Hipp]

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

[Igor Tandetnik]

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?

[D. Richard Hipp]

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

[jan]

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?

[Julian Bui]

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?

[mw]

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

[mw]

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

[Marcus Grimm]

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

[Zaher Dirkey]

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

[Kees Nuyt]

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?

[Dan]

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?

[Vinnie]

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

[D. Richard Hipp]

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

[Wenton Thomas]

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

[Kees Nuyt]

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