date:20100514

Re: [sqlite] prevent sqlite from interpreting string as numbers?

2010-05-14 Thread Igor Tandetnik

peter360 wrote:
> I see this in sqlite
> 
> sqlite> create table t1(c1 string);

You want t1(c1 text). "string" has no special meaning to SQLite, while "text" 
does.

Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] prevent sqlite from interpreting string as numbers?

2010-05-14 Thread peter360


I see this in sqlite

sqlite> create table t1(c1 string);
sqlite> insert into t1(c1) values('100.200');
sqlite> select * from t1;
100.2

but I don't want sqlite to treat 100.200 as a number.  I want to treat it as
a string (it is the first two octets of an ip address).  How do I achieve
that?
-- 
View this message in context: 
http://old.nabble.com/prevent-sqlite-from-interpreting-string-as-numbers--tp28565639p28565639.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite update with joins

2010-05-14 Thread Simon Hax

Oracle allows to assign an alias to a table in an update-statement like

update T x
   set x.col = .

in my eyes thats clear syntax.

so there is a better way.
I would prefer that syntax. Sqlite should have implemented that.

Simon

-Ursprüngliche Nachricht-
Von: Pavel Ivanov 
Gesendet: 02.05.2010 04:53:17
An: General Discussion of SQLite Database 
Betreff: Re: [sqlite] sqlite update with joins

> But it is ambigous ...

It is not ambiguous when you know how SQL works and it's the only
standard way of doing such things.


Pavel

On Sat, May 1, 2010 at 1:31 PM,   wrote:
> Yes that works.
> But it is ambigous ...
>
> -Ursprüngliche Nachricht-
> Von: Igor Tandetnik 
> Gesendet: 01.05.2010 16:00:01
> An: sqlite-users@sqlite.org
> Betreff: Re: [sqlite] sqlite update with joins
>
> wdl...@web.de wrote:
>> The general problem with this issue is that it is not possible
>>
>> to set an alias for the table in an update statement.
>> That should be implemented because otherwise some necessary statements are 
>> not possible.
>>
>> e.g.
>>
>> update T x
>>  set x.col1 = ( select  sum(c) from T y where y.id = x.id )
>
> update T set col1 = (select sum(c) from T y where y.id = T.id);
>
> Try it, it works. Identifier T binds to the first mention of the table, which 
> happens to be the one in the outer UPDATE statement. Other instances of T may 
> be referred to with aliases.
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> NEU: WEB.DE DSL für 19,99 EUR/mtl. und ohne Mindest-Laufzeit!
> http://produkte.web.de/go/02/
> ___
> 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
___
GRATIS: Movie-Flat mit über 300 Top-Videos. Für WEB.DE Nutzer
dauerhaft kostenlos! Jetzt freischalten unter http://movieflat.web.de
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Composite Foreign Key not enforced

2010-05-14 Thread George Somers

Thanks for your quick answer!
I was trying to provide a simple example.
Here is another example to demonstrate the problem.
I am using a lot "surrogate key with propagation",
and this is why I have many composite foreign keys.

PRAGMA foreign_keys = ON;

CREATE TABLE parent(
   parentID INTEGER PRIMARY KEY,   
   parentName   TEXT
);

CREATE TABLE child(
   childID INTEGER PRIMARY KEY,   
   fatherIDINTEGER,   
   motherIDINTEGER,   
   childName   TEXT,   
   FOREIGN KEY(fatherID) REFERENCES parent(parentID),   
   FOREIGN KEY(motherID) REFERENCES parent(parentID)
);
CREATE UNIQUE INDEX i1 ON child(childID, fatherID);
CREATE UNIQUE INDEX i2 ON child(childID, motherID);

CREATE TABLE fatherChildActivity(
   fcChildID   INTEGER,   
   fcParentID  INTEGER,
   fcPlaysWhat TEXT,   
   FOREIGN KEY(fcChildID, fcParentID) REFERENCES child(childID, fatherID)   
   -- This foreign key make sure that 1) child exist, 2) father exist,
   -- 3) father is really father of child.
);

INSERT INTO parent VALUES(1, 'parent #1');
INSERT INTO parent VALUES(2, 'parent #2');
INSERT INTO child VALUES(1, 1, 2, 'child #1');
INSERT INTO fatherChildActivity VALUES(1, 1, 'Baseball');

DELETE FROM child WHERE childID = 1;

A FK between
   fatherChildActivity and child
with another FK between
   fatherChildActivity and parent
does not constraint the parent to be the father of the child.

The FOREIGN KEY(fcChildID, fcParentID) REFERENCES child(childID, fatherID
is enforced while doing INSERT, but not while doing DELETE!

George Somers

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Composite Foreign Key not enforced

2010-05-14 Thread Richard Hipp

On Thu, May 13, 2010 at 5:50 PM, George Somers wrote:

> Hello,
>
> It seems that composite FK are not enforced on SQLite 3.6.23.1.
> The following script shows that the DELETE FROM table "artist" will work,
> even though
> there is a composite FK from table "track" toward  table "artist".
>
> PRAGMA foreign_keys = ON;
>
> CREATE TABLE artist(
>  artistidINTEGER PRIMARY KEY,
>  bandid  INTEGER
> );
> CREATE UNIQUE INDEX i1 ON artist(artistid, bandid);
>

The artistid column is already unique because it is the primary key.  This
index is pointless.  And it seems to be confusing SQLite in some way as
well.  Your work-around is to drop the above index.


> INSERT INTO artist VALUES(1, 10);
>
> CREATE TABLE track(
>  trackid INTEGER PRIMARY KEY,
>  trackartist INTEGER,
>  trackband   INTEGER,
>  --   FOREIGN KEY(trackartist) REFERENCES artist(artistid)  -- This FK will
> be enforced, as it should!
>  FOREIGN KEY(trackartist, trackband) REFERENCES artist(artistid, bandid)
>  -- This FK won't be enforced!
> );
> CREATE INDEX i2 ON track(trackartist, trackband);
> INSERT INTO track VALUES(100, 1, 10);
>
> -- This will execute, because SQLite will not check for composite FK,
> -- even when the parent table (artist) has the required indexes.
> -- Note here that even the child table (track) has an index (which is not
> -- required, but just speed up lookup) ... just to further demonstrate the
> problem.
> DELETE FROM artist WHERE artistid = 1;
>
> SELECT * FROM artist;
>
>
> Is there something I am doing wrong?
>
> Thanks!
> George Somers
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
-
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] data type from join multiple tables

2010-05-14 Thread Mikhail Terekhov

On Fri, May 14, 2010 at 12:38 PM, Fabio Spadaro wrote:

>
> No, i'm sure. Probably typeof in python's sqlite is not supported very mell
> or there is other explanation.
>

It works perfectly in Python:

In [2]: import sqlite3
In [3]: conn = sqlite3.connect(':memory:')
In [4]: c = conn.cursor()
In [5]: c.execute("CREATE TABLE tabA (a INTEGER, B INTEGER)")
In [6]: c.execute("INSERT INTO tabA VALUES (1,10)")
In [7]: c.execute("INSERT INTO tabA VALUES (2,20)")
In [8]: c.execute("CREATE TABLE tabB (theNum INTEGER, theName TEXT)")
In [9]: c.execute("INSERT INTO tabB VALUES (10,'albert')")
In [10]: c.execute("INSERT INTO tabB VALUES (20,'bertha')")
In [11]: res = c.execute("SELECT
tabA.a,tabB.theNum,tabB.theName,typeof(tabB.theNum),typeof(tabB.theName)
FROM tabA JOIN tabB ON tabB.theNum = tabA.b")
In [12]: for r in res.fetchall(): print r
   :
(1, 10, u'albert', u'integer', u'text')
(2, 20, u'bertha', u'integer', u'text')


Regards,
Mikhail Terekhov
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] data type from join multiple tables

2010-05-14 Thread P Kishor

On Fri, May 14, 2010 at 11:38 AM, Fabio Spadaro  wrote:
> Hi,
>
> 2010/5/14 P Kishor 
>
>> On Fri, May 14, 2010 at 10:11 AM, Fabio Spadaro 
>> wrote:
>> > Hi,
>> >
>> > 2010/5/14 P Kishor 
>> >
>> >> On Fri, May 14, 2010 at 9:49 AM, Fabio Spadaro > >
>> >> wrote:
>> >> > hi,
>> >> >
>> >> > 2010/5/14 P Kishor 
>> >> >
>> >> >> On Fri, May 14, 2010 at 9:01 AM, Fabio Spadaro <
>> fabiolinos...@gmail.com
>> >> >
>> >> >> wrote:
>> >> >> > I need to identify data types extracted from a
>> >> >> > join between multiple tables without using cross-checking
>> table_info
>> >> more
>> >> >> > pragmatic.
>> >> >> >
>> >> >>
>> >> >> Could you clarify what you really want to do? Your question is not
>> >> >> clear at all, at least to me. What do you mean by "using
>> >> >> cross-checking table_info more pragmatic"? More pragmatic than what?
>> >> >>
>> >> >> > Is there a faster way to do it?
>> >> >> >
>> >> >>
>> >> >> Faster than what?
>> >> >>
>> >> >> If you want data_types, you can use SELECT Typeof(column_name)
>> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >> >
>> >> > I use python sqlite embedded and in my application I have a panel
>> where
>> >> you
>> >> > can
>> >> > type a query and see the results in a panel's grid
>> >> > Now suppose that I type a query like "select
>> >> > a.field1, b.field2 from table1 a, table2 b " such data
>> >> > will be displayed in grid but I need to know what types of data I
>> >> obtained.
>> >> > How do it?
>> >>
>> >>
>> >> SELECT a.field1, Typeof(a.field1), b.field2, Typeof(b.field2)
>> >> FROM table1 a, table2 b...
>> >>
>> >>
>> >> >
>> >
>> > It does not work, It always return data type text.
>> >
>>
>> That is because your table has probably defined those columns as TEXT.
>> Consider the following --
>>
>> sqlite> CREATE TABLE t (a TEXT, b INTEGER);
>> sqlite> INSERT INTO t VALUES ('foo', 5);
>> sqlite> SELECT a, Typeof(a), b, Typeof(b) FROM t;
>> a           Typeof(a)   b           Typeof(b)
>> --  --  --  --
>> foo         text        5           integer
>> sqlite>
>>
>>
>>
>>
>>
>>
>>
>
> No, i'm sure. Probably typeof in python's sqlite is not supported very mell
> or there is other explanation.
>


Typeof() is a sqlite3 function. Maybe the Python implementation is
overriding it. But, given that you are getting back "text" and not an
error, shows that you are using some Typeof() function. Using the
example db that I showed above, and the following Perl script, I get
the desired and expected output

#!/usr/local/bin/perl
use DBI qw(:sql_types);

$dbh = DBI->connect("dbi:SQLite:dbname=/Users/punkish/Data/punkish/foo","","");
$sth = $dbh->prepare("SELECT a, Typeof(a), b, Typeof(b) FROM t");
$sth->execute;

while (my @row = $sth->fetchrow_array) {
print (join ", ", @row) . "\n";
}

 prints...

foo, text, 5, integer



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] data type from join multiple tables

2010-05-14 Thread Simon Slavin


On 14 May 2010, at 5:38pm, Fabio Spadaro wrote:

> No, i'm sure. Probably typeof in python's sqlite is not supported very mell
> or there is other explanation.

It works perfectly inside SQLite:

sqlite> CREATE TABLE tabA (a INTEGER, B INTEGER);
sqlite> INSERT INTO tabA VALUES (1,10);
sqlite> INSERT INTO tabA VALUES (2,20);
sqlite> CREATE TABLE tabB (theNum INTEGER, theName TEXT);
sqlite> INSERT INTO tabB VALUES (10,'albert');
sqlite> INSERT INTO tabB VALUES (20,'bertha');
sqlite> SELECT 
tabA.a,tabB.theNum,tabB.theName,typeof(tabB.theNum),typeof(tabB.theName) FROM 
tabA JOIN tabB ON tabB.theNum = tabA.b;
1|10|albert|integer|text
2|20|bertha|integer|text

Do the above in your platform and tell us what you get.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] data type from join multiple tables

2010-05-14 Thread Fabio Spadaro

Hi,

2010/5/14 P Kishor 

> On Fri, May 14, 2010 at 10:11 AM, Fabio Spadaro 
> wrote:
> > Hi,
> >
> > 2010/5/14 P Kishor 
> >
> >> On Fri, May 14, 2010 at 9:49 AM, Fabio Spadaro  >
> >> wrote:
> >> > hi,
> >> >
> >> > 2010/5/14 P Kishor 
> >> >
> >> >> On Fri, May 14, 2010 at 9:01 AM, Fabio Spadaro <
> fabiolinos...@gmail.com
> >> >
> >> >> wrote:
> >> >> > I need to identify data types extracted from a
> >> >> > join between multiple tables without using cross-checking
> table_info
> >> more
> >> >> > pragmatic.
> >> >> >
> >> >>
> >> >> Could you clarify what you really want to do? Your question is not
> >> >> clear at all, at least to me. What do you mean by "using
> >> >> cross-checking table_info more pragmatic"? More pragmatic than what?
> >> >>
> >> >> > Is there a faster way to do it?
> >> >> >
> >> >>
> >> >> Faster than what?
> >> >>
> >> >> If you want data_types, you can use SELECT Typeof(column_name)
> >> >>
> >> >>
> >> >>
> >> >>
> >> >
> >> > I use python sqlite embedded and in my application I have a panel
> where
> >> you
> >> > can
> >> > type a query and see the results in a panel's grid
> >> > Now suppose that I type a query like "select
> >> > a.field1, b.field2 from table1 a, table2 b " such data
> >> > will be displayed in grid but I need to know what types of data I
> >> obtained.
> >> > How do it?
> >>
> >>
> >> SELECT a.field1, Typeof(a.field1), b.field2, Typeof(b.field2)
> >> FROM table1 a, table2 b...
> >>
> >>
> >> >
> >
> > It does not work, It always return data type text.
> >
>
> That is because your table has probably defined those columns as TEXT.
> Consider the following --
>
> sqlite> CREATE TABLE t (a TEXT, b INTEGER);
> sqlite> INSERT INTO t VALUES ('foo', 5);
> sqlite> SELECT a, Typeof(a), b, Typeof(b) FROM t;
> a   Typeof(a)   b   Typeof(b)
> --  --  --  --
> foo text5   integer
> sqlite>
>
>
>
>
>
>
> --
> Puneet Kishor http://www.punkish.org
> Carbon Model http://carbonmodel.org
> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
> ---
> Assertions are politics; backing up assertions with evidence is science
> ===
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

No, i'm sure. Probably typeof in python's sqlite is not supported very mell
or there is other explanation.

-- 
Fabio Spadaro
www.fabiospadaro.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] data type from join multiple tables

2010-05-14 Thread P Kishor

On Fri, May 14, 2010 at 10:11 AM, Fabio Spadaro  wrote:
> Hi,
>
> 2010/5/14 P Kishor 
>
>> On Fri, May 14, 2010 at 9:49 AM, Fabio Spadaro 
>> wrote:
>> > hi,
>> >
>> > 2010/5/14 P Kishor 
>> >
>> >> On Fri, May 14, 2010 at 9:01 AM, Fabio Spadaro > >
>> >> wrote:
>> >> > I need to identify data types extracted from a
>> >> > join between multiple tables without using cross-checking table_info
>> more
>> >> > pragmatic.
>> >> >
>> >>
>> >> Could you clarify what you really want to do? Your question is not
>> >> clear at all, at least to me. What do you mean by "using
>> >> cross-checking table_info more pragmatic"? More pragmatic than what?
>> >>
>> >> > Is there a faster way to do it?
>> >> >
>> >>
>> >> Faster than what?
>> >>
>> >> If you want data_types, you can use SELECT Typeof(column_name)
>> >>
>> >>
>> >>
>> >>
>> >
>> > I use python sqlite embedded and in my application I have a panel where
>> you
>> > can
>> > type a query and see the results in a panel's grid
>> > Now suppose that I type a query like "select
>> > a.field1, b.field2 from table1 a, table2 b " such data
>> > will be displayed in grid but I need to know what types of data I
>> obtained.
>> > How do it?
>>
>>
>> SELECT a.field1, Typeof(a.field1), b.field2, Typeof(b.field2)
>> FROM table1 a, table2 b...
>>
>>
>> >
>
> It does not work, It always return data type text.
>

That is because your table has probably defined those columns as TEXT.
Consider the following --

sqlite> CREATE TABLE t (a TEXT, b INTEGER);
sqlite> INSERT INTO t VALUES ('foo', 5);
sqlite> SELECT a, Typeof(a), b, Typeof(b) FROM t;
a   Typeof(a)   b   Typeof(b)
--  --  --  --
foo text5   integer
sqlite>






-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] data type from join multiple tables

2010-05-14 Thread Fabio Spadaro

Hi,

2010/5/14 P Kishor 

> On Fri, May 14, 2010 at 9:49 AM, Fabio Spadaro 
> wrote:
> > hi,
> >
> > 2010/5/14 P Kishor 
> >
> >> On Fri, May 14, 2010 at 9:01 AM, Fabio Spadaro  >
> >> wrote:
> >> > I need to identify data types extracted from a
> >> > join between multiple tables without using cross-checking table_info
> more
> >> > pragmatic.
> >> >
> >>
> >> Could you clarify what you really want to do? Your question is not
> >> clear at all, at least to me. What do you mean by "using
> >> cross-checking table_info more pragmatic"? More pragmatic than what?
> >>
> >> > Is there a faster way to do it?
> >> >
> >>
> >> Faster than what?
> >>
> >> If you want data_types, you can use SELECT Typeof(column_name)
> >>
> >>
> >>
> >>
> >> >
> >>
> >>
> >>
> >> --
> >> Puneet Kishor http://www.punkish.org
> >> Carbon Model http://carbonmodel.org
> >> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
> >> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
> >> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
> >> ---
> >> Assertions are politics; backing up assertions with evidence is science
> >> ===
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@sqlite.org
> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >
> > I use python sqlite embedded and in my application I have a panel where
> you
> > can
> > type a query and see the results in a panel's grid
> > Now suppose that I type a query like "select
> > a.field1, b.field2 from table1 a, table2 b " such data
> > will be displayed in grid but I need to know what types of data I
> obtained.
> > How do it?
>
>
> SELECT a.field1, Typeof(a.field1), b.field2, Typeof(b.field2)
> FROM table1 a, table2 b...
>
>
> >
> > --
> > Fabio Spadaro
> > www.fabiospadaro.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
>

It does not work, It always return data type text.

-- 
Fabio Spadaro
www.fabiospadaro.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] data type from join multiple tables

2010-05-14 Thread P Kishor

On Fri, May 14, 2010 at 9:49 AM, Fabio Spadaro  wrote:
> hi,
>
> 2010/5/14 P Kishor 
>
>> On Fri, May 14, 2010 at 9:01 AM, Fabio Spadaro 
>> wrote:
>> > I need to identify data types extracted from a
>> > join between multiple tables without using cross-checking table_info more
>> > pragmatic.
>> >
>>
>> Could you clarify what you really want to do? Your question is not
>> clear at all, at least to me. What do you mean by "using
>> cross-checking table_info more pragmatic"? More pragmatic than what?
>>
>> > Is there a faster way to do it?
>> >
>>
>> Faster than what?
>>
>> If you want data_types, you can use SELECT Typeof(column_name)
>>
>>
>>
>>
>> >
>>
>>
>>
>> --
>> Puneet Kishor http://www.punkish.org
>> Carbon Model http://carbonmodel.org
>> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
>> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
>> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
>> ---
>> Assertions are politics; backing up assertions with evidence is science
>> ===
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> I use python sqlite embedded and in my application I have a panel where you
> can
> type a query and see the results in a panel's grid
> Now suppose that I type a query like "select
> a.field1, b.field2 from table1 a, table2 b " such data
> will be displayed in grid but I need to know what types of data I obtained.
> How do it?


SELECT a.field1, Typeof(a.field1), b.field2, Typeof(b.field2)
FROM table1 a, table2 b...


>
> --
> Fabio Spadaro
> www.fabiospadaro.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] data type from join multiple tables

2010-05-14 Thread Fabio Spadaro

hi,

2010/5/14 P Kishor 

> On Fri, May 14, 2010 at 9:01 AM, Fabio Spadaro 
> wrote:
> > I need to identify data types extracted from a
> > join between multiple tables without using cross-checking table_info more
> > pragmatic.
> >
>
> Could you clarify what you really want to do? Your question is not
> clear at all, at least to me. What do you mean by "using
> cross-checking table_info more pragmatic"? More pragmatic than what?
>
> > Is there a faster way to do it?
> >
>
> Faster than what?
>
> If you want data_types, you can use SELECT Typeof(column_name)
>
>
>
>
> >
>
>
>
> --
> Puneet Kishor http://www.punkish.org
> Carbon Model http://carbonmodel.org
> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
> ---
> Assertions are politics; backing up assertions with evidence is science
> ===
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

I use python sqlite embedded and in my application I have a panel where you
can
type a query and see the results in a panel's grid
Now suppose that I type a query like "select
a.field1, b.field2 from table1 a, table2 b " such data
will be displayed in grid but I need to know what types of data I obtained.
How do it?

-- 
Fabio Spadaro
www.fabiospadaro.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Incorrect assertion in mutexes plus pcache1Alloc

2010-05-14 Thread Pavel Ivanov

> Not sure if it's still important, but this is how it looks for me:

Thank you, Nikolaus. Your disassembly proves that our guess was correct.


Pavel

On Fri, May 14, 2010 at 9:45 AM, Nikolaus Rath  wrote:
> Pavel Ivanov  writes:
 Yeah, I'm around. I don't know what an "exact disassembly" is or how to
 provide one, but if someone tells me what to do then I'm most likely
 willing to do it.
>>
>> Nikolaus, you can do it like this:
>>> gdb your_application
>> (gdb) disassemble pthreadMutexEnter
>>
>> I made that for my application and see this:
>>
>> Dump of assembler code for function pthreadMutexEnter:
>> 0x005d60c0 :       push   %rbx
>> 0x005d60c1 :       mov    %rdi,%rbx
>> 0x005d60c4 :       callq  0x472750
>> 
>> 0x005d60c9 :       callq  0x471dc0
>> 
>> 0x005d60ce :      incl   0x2c(%rbx)
>> 0x005d60d1 :      mov    %rax,0x30(%rbx)
>> 0x005d60d5 :      pop    %rbx
>> 0x005d60d6 :      retq
>> End of assembler dump.
>
> Not sure if it's still important, but this is how it looks for me:
>
> (gdb) disassemble pthreadMutexEnter
> Dump of assembler code for function pthreadMutexEnter:
>   0x002f3090 <+0>:     push   %ebp
>   0x002f3091 <+1>:     mov    %esp,%ebp
>   0x002f3093 <+3>:     push   %esi
>   0x002f3094 <+4>:     push   %ebx
>   0x002f3095 <+5>:     sub    $0x20,%esp
>   0x002f3098 <+8>:     mov    0x8(%ebp),%esi
>   0x002f309b <+11>:    call   0x2dbd67 <__i686.get_pc_thunk.bx>
>   0x002f30a0 <+16>:    add    $0xb9f54,%ebx
>   0x002f30a6 <+22>:    cmpl   $0x1,0x18(%esi)
>   0x002f30aa <+26>:    je     0x2f30b8 
>   0x002f30ac <+28>:    mov    %esi,(%esp)
>   0x002f30af <+31>:    call   0x2f2e10 
>   0x002f30b4 <+36>:    test   %eax,%eax
>   0x002f30b6 <+38>:    je     0x2f3101 
>   0x002f30b8 <+40>:    mov    %esi,(%esp)
>   0x002f30bb <+43>:    call   0x2db980 
>   0x002f30c0 <+48>:    call   0x2dbc60 
>   0x002f30c5 <+53>:    mov    0x1c(%esi),%edx
>   0x002f30c8 <+56>:    add    $0x1,%edx
>   0x002f30cb <+59>:    mov    %edx,0x1c(%esi)
>   0x002f30ce <+62>:    mov    %eax,0x20(%esi)
>   0x002f30d1 <+65>:    mov    0x24(%esi),%eax
>   0x002f30d4 <+68>:    test   %eax,%eax
>   0x002f30d6 <+70>:    je     0x2f30fa 
>   0x002f30d8 <+72>:    mov    %eax,0xc(%esp)
>   0x002f30dc <+76>:    lea    -0x23258(%ebx),%eax
>   0x002f30e2 <+82>:    mov    %edx,0x10(%esp)
>   0x002f30e6 <+86>:    mov    %esi,0x8(%esp)
>   0x002f30ea <+90>:    mov    %eax,0x4(%esp)
>   0x002f30ee <+94>:    movl   $0x1,(%esp)
>   0x002f30f5 <+101>:   call   0x2db410 <__printf_...@plt>
>   0x002f30fa <+106>:   add    $0x20,%esp
>   0x002f30fd <+109>:   pop    %ebx
>   0x002f30fe <+110>:   pop    %esi
>   0x002f30ff <+111>:   pop    %ebp
>   0x002f3100 <+112>:   ret
>   0x002f3101 <+113>:   lea    -0x2b207(%ebx),%eax
>   0x002f3107 <+119>:   mov    %eax,0xc(%esp)
>   0x002f310b <+123>:   lea    -0x24b18(%ebx),%eax
>   0x002f3111 <+129>:   mov    %eax,0x4(%esp)
>   0x002f3115 <+133>:   lea    -0x2327c(%ebx),%eax
>   0x002f311b <+139>:   movl   $0x3d0f,0x8(%esp)
>   0x002f3123 <+147>:   mov    %eax,(%esp)
>   0x002f3126 <+150>:   call   0x2db530 <__assert_f...@plt>
> End of assembler dump.
>
>
>   -Nikolaus
>
> --
>  »Time flies like an arrow, fruit flies like a Banana.«
>
>  PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6  02CF A9AD B7F8 AE4E 425C
>
> ___
> 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] data type from join multiple tables

2010-05-14 Thread Israel Lins Albuquerque

Or you can use sqlite3_column_decltype in conjunction with 
sqlite3_column_table_name, sqlite3_column_database_name, 
sqlite3_column_origin_name but to use the last three functions you will need 
compile sqlite with -DSQLITE_ENABLE_COLUMN_METADATA directive on LCFLAGS or 
something like this! 

- Mensagem original - 
De: "P Kishor"  
Para: "General Discussion of SQLite Database"  
Enviadas: Sexta-feira, 14 de Maio de 2010 11:05:43 
Assunto: Re: [sqlite] data type from join multiple tables 

On Fri, May 14, 2010 at 9:01 AM, Fabio Spadaro  wrote: 
> I need to identify data types extracted from a 
> join between multiple tables without using cross-checking table_info more 
> pragmatic. 
> 

Could you clarify what you really want to do? Your question is not 
clear at all, at least to me. What do you mean by "using 
cross-checking table_info more pragmatic"? More pragmatic than what? 

> Is there a faster way to do it? 
> 

Faster than what? 

If you want data_types, you can use SELECT Typeof(column_name) 

> 

-- 
Puneet Kishor http://www.punkish.org 
Carbon Model http://carbonmodel.org 
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org 
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor 
Nelson Institute, UW-Madison http://www.nelson.wisc.edu 
--- 
Assertions are politics; backing up assertions with evidence is science 
=== 
___ 
sqlite-users mailing list 
sqlite-users@sqlite.org 
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 

-- 

Atenciosamente, 

Israel Lins Albuquerque 
Desenvolvimento 
Polibrás Brasil Software Ltda. 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Incorrect assertion in mutexes plus pcache1Alloc

2010-05-14 Thread Nikolaus Rath

Pavel Ivanov  writes:
>>> Yeah, I'm around. I don't know what an "exact disassembly" is or how to
>>> provide one, but if someone tells me what to do then I'm most likely
>>> willing to do it.
>
> Nikolaus, you can do it like this:
>> gdb your_application
> (gdb) disassemble pthreadMutexEnter
>
> I made that for my application and see this:
>
> Dump of assembler code for function pthreadMutexEnter:
> 0x005d60c0 :   push   %rbx
> 0x005d60c1 :   mov%rdi,%rbx
> 0x005d60c4 :   callq  0x472750
> 
> 0x005d60c9 :   callq  0x471dc0
> 
> 0x005d60ce :  incl   0x2c(%rbx)
> 0x005d60d1 :  mov%rax,0x30(%rbx)
> 0x005d60d5 :  pop%rbx
> 0x005d60d6 :  retq
> End of assembler dump.

Not sure if it's still important, but this is how it looks for me:

(gdb) disassemble pthreadMutexEnter
Dump of assembler code for function pthreadMutexEnter:
   0x002f3090 <+0>: push   %ebp
   0x002f3091 <+1>: mov%esp,%ebp
   0x002f3093 <+3>: push   %esi
   0x002f3094 <+4>: push   %ebx
   0x002f3095 <+5>: sub$0x20,%esp
   0x002f3098 <+8>: mov0x8(%ebp),%esi
   0x002f309b <+11>:call   0x2dbd67 <__i686.get_pc_thunk.bx>
   0x002f30a0 <+16>:add$0xb9f54,%ebx
   0x002f30a6 <+22>:cmpl   $0x1,0x18(%esi)
   0x002f30aa <+26>:je 0x2f30b8 
   0x002f30ac <+28>:mov%esi,(%esp)
   0x002f30af <+31>:call   0x2f2e10 
   0x002f30b4 <+36>:test   %eax,%eax
   0x002f30b6 <+38>:je 0x2f3101 
   0x002f30b8 <+40>:mov%esi,(%esp)
   0x002f30bb <+43>:call   0x2db980 
   0x002f30c0 <+48>:call   0x2dbc60 
   0x002f30c5 <+53>:mov0x1c(%esi),%edx
   0x002f30c8 <+56>:add$0x1,%edx
   0x002f30cb <+59>:mov%edx,0x1c(%esi)
   0x002f30ce <+62>:mov%eax,0x20(%esi)
   0x002f30d1 <+65>:mov0x24(%esi),%eax
   0x002f30d4 <+68>:test   %eax,%eax
   0x002f30d6 <+70>:je 0x2f30fa 
   0x002f30d8 <+72>:mov%eax,0xc(%esp)
   0x002f30dc <+76>:lea-0x23258(%ebx),%eax
   0x002f30e2 <+82>:mov%edx,0x10(%esp)
   0x002f30e6 <+86>:mov%esi,0x8(%esp)
   0x002f30ea <+90>:mov%eax,0x4(%esp)
   0x002f30ee <+94>:movl   $0x1,(%esp)
   0x002f30f5 <+101>:   call   0x2db410 <__printf_...@plt>
   0x002f30fa <+106>:   add$0x20,%esp
   0x002f30fd <+109>:   pop%ebx
   0x002f30fe <+110>:   pop%esi
   0x002f30ff <+111>:   pop%ebp
   0x002f3100 <+112>:   ret
   0x002f3101 <+113>:   lea-0x2b207(%ebx),%eax
   0x002f3107 <+119>:   mov%eax,0xc(%esp)
   0x002f310b <+123>:   lea-0x24b18(%ebx),%eax
   0x002f3111 <+129>:   mov%eax,0x4(%esp)
   0x002f3115 <+133>:   lea-0x2327c(%ebx),%eax
   0x002f311b <+139>:   movl   $0x3d0f,0x8(%esp)
   0x002f3123 <+147>:   mov%eax,(%esp)
   0x002f3126 <+150>:   call   0x2db530 <__assert_f...@plt>
End of assembler dump.


   -Nikolaus

-- 
 »Time flies like an arrow, fruit flies like a Banana.«

  PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6  02CF A9AD B7F8 AE4E 425C

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] data type from join multiple tables

2010-05-14 Thread P Kishor

On Fri, May 14, 2010 at 9:01 AM, Fabio Spadaro  wrote:
> I need to identify data types extracted from a
> join between multiple tables without using cross-checking table_info more
> pragmatic.
>

Could you clarify what you really want to do? Your question is not
clear at all, at least to me. What do you mean by "using
cross-checking table_info more pragmatic"? More pragmatic than what?

> Is there a faster way to do it?
>

Faster than what?

If you want data_types, you can use SELECT Typeof(column_name)




>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] data type from join multiple tables

2010-05-14 Thread Fabio Spadaro

I need to identify data types extracted from a
join between multiple tables without using cross-checking table_info more
pragmatic.

Is there a faster way to do it?

-- 
Fabio Spadaro
www.fabiospadaro.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] multi processes, so many errores of SQLITE_BUSY and SQLITE_MISUSE

2010-05-14 Thread Black, Michael (IS)

Also -- note that Pavel's "general" advice means "would work in every case".
 
My not be necessary for what you're doing if you don't have a deadlock 
condition that can occur.  But you would find that out if you just put a 
counter in your BUSY loop and bomb out if it gets too large or takes too long.
 
For the example you gave earlier I would just use the BUSY loops as doing a 
ROLLBACK every time it's busy is a waste.
 
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of liubin liu
Sent: Fri 5/14/2010 1:20 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] multi processes, so many errores of SQLITE_BUSY and 
SQLITE_MISUSE




How to rollback current transaction?





Pavel Ivanov-2 wrote:
>
>> I assume you want the sqllite3_stmt to work -- so you need to loop that
>> while it's busy.
>
> Michael, don't give bad advices.
> The most general advice when one gets SQLITE_BUSY is to reset/finalize
> all statements and rollback current transaction. It's only in certain
> type of transactions and certain type of statements one can loop while
> sqlite3_stmt returns SQLITE_BUSY. But even in this case one must call
> sqlite3_reset on the statement that returned SQLITE_BUSY. And this
> call to sqlite3_reset will return SQLITE_BUSY again.
>
>
> Pavel
>
> On Thu, May 13, 2010 at 7:20 AM, Black, Michael (IS)
>  wrote:
>> It also means you're not handling the SQLITE_BUSY from the sqlite3_stmt()
>> before it.
>> I assume you want the sqllite3_stmt to work -- so you need to loop that
>> while it's busy.
>>
>> Michael D. Black
>> Senior Scientist
>> Northrop Grumman Mission Systems
>>
>>
>> 
>>
>> From: sqlite-users-boun...@sqlite.org on behalf of liubin liu
>> Sent: Thu 5/13/2010 2:07 AM
>> To: sqlite-users@sqlite.org
>> Subject: Re: [sqlite] multi processes, so many errores of SQLITE_BUSY and
>> SQLITE_MISUSE
>>
>>
>>
>>
>> I see many SQLITE_BUSY returned from sqlite3_finalize(). Are they memory
>> leak
>> because it don't succeed in finalizing the sqlite3_stmt pointer?
>>
>>
>>
>>
>> Black, Michael (IS) wrote:
>>>
>>> SQLITE_BUSY is not an error...just a fact.
>>>
>>> All your processes cannot work on the database at the same time...at
>>> least
>>> not when one of them is doing an insert.  You could be changing the
>>> table
>>> while you're scanning it.  EXXCLUSIVE doesn't change that idea.
>>>
>>> Somebody please correct me if I'm wrong on this one...
>>> I think sqlite can work with multiple processes just doing read-onliy
>>> operations (like SELECT).It's just the write operations
>>> (INSERT/UPDATE) which will cause SQLITE_BUSY to occur.
>>>
>>> Michael D. Black
>>> Senior Scientist
>>> Northrop Grumman Mission Systems
>>>
>>>
>>> 
>>>
>>> From: sqlite-users-boun...@sqlite.org on behalf of liubin liu
>>> Sent: Tue 5/11/2010 9:20 PM
>>> To: sqlite-users@sqlite.org
>>> Subject: Re: [sqlite] multi processes, so many errores of SQLITE_BUSY
>>> and
>>> SQLITE_MISUSE
>>>
>>>
>>>
>>>
>>> Thank you very much!
>>>
>>> It may be because my system's resource is limited. It's a embedded
>>> system
>>> containing 32M RAM, ARM9 CPU.
>>>
>>> My "reiterating 20 times" is already using usleep().
>>>
>>> After I add the loop in the prepare statements, the system performance
>>> is
>>> still very bad... And there are still many errores of SQLITE_BUSY.
>>>
>>> The only improvement is the disappear of the error of SQLITE_MISUSE.
>>>
>>> And I tried to use the "BEGIN EXCLUSIVE TRANSACTION". The things are
>>> same
>>> with them without using it.
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> Black, Michael (IS) wrote:

 Your "reiterating 20 times" is not using a usleep so you'll blow by
 this
 most every time it's busy.

 Do this instead in all your proc's

 ret = sqlite3_step (p_stmt);
 if (SQLITE_BUSY == ret)
 {
 int n=0;
 usleep(10); // try one more time before error
 while ((ret=sqlite3_step(p_stmt))==SQLITE_BUSY) {
 printf("proc1 ret==BUSY %d\n",++n);
 usleep(10);
 }
 }

 And you'll also need to handle "database is locked" coming from your
 prepare statements.  I saw that error too.
 You'll need to loop there too.

 The more you drop the usleep time the more times it will show as busy.
 1/10th or 1/100th of second is about all you want I would think.

 And get rid of the usleep at the bottom of each proc -- it's pretty
 useless at 100 microseconds.  You don't need to sleep unless you're
 busy.

 I tested your code with this and got no errors at all -- just a bunch
 of
 BUSY messages.


 Not sure what your purpose is in sqlrun.c with looping and killing.
 Looks

Re: [sqlite] multi processes, so many errores of SQLITE_BUSY and SQLITE_MISUSE

2010-05-14 Thread Black, Michael (IS)

BEGIN
while stuff to do
   do insert,etc
   if error
  ROLLBACK
  goto begin
   end
emd
COMMIT
 
 
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of liubin liu
Sent: Fri 5/14/2010 1:20 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] multi processes, so many errores of SQLITE_BUSY and 
SQLITE_MISUSE




How to rollback current transaction?





Pavel Ivanov-2 wrote:
>
>> I assume you want the sqllite3_stmt to work -- so you need to loop that
>> while it's busy.
>
> Michael, don't give bad advices.
> The most general advice when one gets SQLITE_BUSY is to reset/finalize
> all statements and rollback current transaction. It's only in certain
> type of transactions and certain type of statements one can loop while
> sqlite3_stmt returns SQLITE_BUSY. But even in this case one must call
> sqlite3_reset on the statement that returned SQLITE_BUSY. And this
> call to sqlite3_reset will return SQLITE_BUSY again.
>
>
> Pavel
>
> On Thu, May 13, 2010 at 7:20 AM, Black, Michael (IS)
>  wrote:
>> It also means you're not handling the SQLITE_BUSY from the sqlite3_stmt()
>> before it.
>> I assume you want the sqllite3_stmt to work -- so you need to loop that
>> while it's busy.
>>
>> Michael D. Black
>> Senior Scientist
>> Northrop Grumman Mission Systems
>>
>>
>> 
>>
>> From: sqlite-users-boun...@sqlite.org on behalf of liubin liu
>> Sent: Thu 5/13/2010 2:07 AM
>> To: sqlite-users@sqlite.org
>> Subject: Re: [sqlite] multi processes, so many errores of SQLITE_BUSY and
>> SQLITE_MISUSE
>>
>>
>>
>>
>> I see many SQLITE_BUSY returned from sqlite3_finalize(). Are they memory
>> leak
>> because it don't succeed in finalizing the sqlite3_stmt pointer?
>>
>>
>>
>>
>> Black, Michael (IS) wrote:
>>>
>>> SQLITE_BUSY is not an error...just a fact.
>>>
>>> All your processes cannot work on the database at the same time...at
>>> least
>>> not when one of them is doing an insert.  You could be changing the
>>> table
>>> while you're scanning it.  EXXCLUSIVE doesn't change that idea.
>>>
>>> Somebody please correct me if I'm wrong on this one...
>>> I think sqlite can work with multiple processes just doing read-onliy
>>> operations (like SELECT).It's just the write operations
>>> (INSERT/UPDATE) which will cause SQLITE_BUSY to occur.
>>>
>>> Michael D. Black
>>> Senior Scientist
>>> Northrop Grumman Mission Systems
>>>
>>>
>>> 
>>>
>>> From: sqlite-users-boun...@sqlite.org on behalf of liubin liu
>>> Sent: Tue 5/11/2010 9:20 PM
>>> To: sqlite-users@sqlite.org
>>> Subject: Re: [sqlite] multi processes, so many errores of SQLITE_BUSY
>>> and
>>> SQLITE_MISUSE
>>>
>>>
>>>
>>>
>>> Thank you very much!
>>>
>>> It may be because my system's resource is limited. It's a embedded
>>> system
>>> containing 32M RAM, ARM9 CPU.
>>>
>>> My "reiterating 20 times" is already using usleep().
>>>
>>> After I add the loop in the prepare statements, the system performance
>>> is
>>> still very bad... And there are still many errores of SQLITE_BUSY.
>>>
>>> The only improvement is the disappear of the error of SQLITE_MISUSE.
>>>
>>> And I tried to use the "BEGIN EXCLUSIVE TRANSACTION". The things are
>>> same
>>> with them without using it.
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> Black, Michael (IS) wrote:

 Your "reiterating 20 times" is not using a usleep so you'll blow by
 this
 most every time it's busy.

 Do this instead in all your proc's

 ret = sqlite3_step (p_stmt);
 if (SQLITE_BUSY == ret)
 {
 int n=0;
 usleep(10); // try one more time before error
 while ((ret=sqlite3_step(p_stmt))==SQLITE_BUSY) {
 printf("proc1 ret==BUSY %d\n",++n);
 usleep(10);
 }
 }

 And you'll also need to handle "database is locked" coming from your
 prepare statements.  I saw that error too.
 You'll need to loop there too.

 The more you drop the usleep time the more times it will show as busy.
 1/10th or 1/100th of second is about all you want I would think.

 And get rid of the usleep at the bottom of each proc -- it's pretty
 useless at 100 microseconds.  You don't need to sleep unless you're
 busy.

 I tested your code with this and got no errors at all -- just a bunch
 of
 BUSY messages.


 Not sure what your purpose is in sqlrun.c with looping and killing.
 Looks
 pretty squirrely to me.  You're not waiting for the forks to finish so
 what is your logic here?

 Michael D. Black
 Senior Scientist
 Northrop Grumman Mission Systems


 

 From: sqlite-users-boun...@sqlite.org on behalf of liubin liu

Re: [sqlite] multi processes, so many errores of SQLITE_BUSY and SQLITE_MISUSE

2010-05-14 Thread Pavel Ivanov

> How to rollback current transaction?

Execute statement "ROLLBACK".


Pavel

On Fri, May 14, 2010 at 2:20 AM, liubin liu <7101...@sina.com> wrote:
>
> How to rollback current transaction?
>
>
>
>
>
> Pavel Ivanov-2 wrote:
>>
>>> I assume you want the sqllite3_stmt to work -- so you need to loop that
>>> while it's busy.
>>
>> Michael, don't give bad advices.
>> The most general advice when one gets SQLITE_BUSY is to reset/finalize
>> all statements and rollback current transaction. It's only in certain
>> type of transactions and certain type of statements one can loop while
>> sqlite3_stmt returns SQLITE_BUSY. But even in this case one must call
>> sqlite3_reset on the statement that returned SQLITE_BUSY. And this
>> call to sqlite3_reset will return SQLITE_BUSY again.
>>
>>
>> Pavel
>>
>> On Thu, May 13, 2010 at 7:20 AM, Black, Michael (IS)
>>  wrote:
>>> It also means you're not handling the SQLITE_BUSY from the sqlite3_stmt()
>>> before it.
>>> I assume you want the sqllite3_stmt to work -- so you need to loop that
>>> while it's busy.
>>>
>>> Michael D. Black
>>> Senior Scientist
>>> Northrop Grumman Mission Systems
>>>
>>>
>>> 
>>>
>>> From: sqlite-users-boun...@sqlite.org on behalf of liubin liu
>>> Sent: Thu 5/13/2010 2:07 AM
>>> To: sqlite-users@sqlite.org
>>> Subject: Re: [sqlite] multi processes, so many errores of SQLITE_BUSY and
>>> SQLITE_MISUSE
>>>
>>>
>>>
>>>
>>> I see many SQLITE_BUSY returned from sqlite3_finalize(). Are they memory
>>> leak
>>> because it don't succeed in finalizing the sqlite3_stmt pointer?
>>>
>>>
>>>
>>>
>>> Black, Michael (IS) wrote:

 SQLITE_BUSY is not an error...just a fact.

 All your processes cannot work on the database at the same time...at
 least
 not when one of them is doing an insert.  You could be changing the
 table
 while you're scanning it.  EXXCLUSIVE doesn't change that idea.

 Somebody please correct me if I'm wrong on this one...
 I think sqlite can work with multiple processes just doing read-onliy
 operations (like SELECT).    It's just the write operations
 (INSERT/UPDATE) which will cause SQLITE_BUSY to occur.

 Michael D. Black
 Senior Scientist
 Northrop Grumman Mission Systems


 

 From: sqlite-users-boun...@sqlite.org on behalf of liubin liu
 Sent: Tue 5/11/2010 9:20 PM
 To: sqlite-users@sqlite.org
 Subject: Re: [sqlite] multi processes, so many errores of SQLITE_BUSY
 and
 SQLITE_MISUSE




 Thank you very much!

 It may be because my system's resource is limited. It's a embedded
 system
 containing 32M RAM, ARM9 CPU.

 My "reiterating 20 times" is already using usleep().

 After I add the loop in the prepare statements, the system performance
 is
 still very bad... And there are still many errores of SQLITE_BUSY.

 The only improvement is the disappear of the error of SQLITE_MISUSE.

 And I tried to use the "BEGIN EXCLUSIVE TRANSACTION". The things are
 same
 with them without using it.







 Black, Michael (IS) wrote:
>
> Your "reiterating 20 times" is not using a usleep so you'll blow by
> this
> most every time it's busy.
>
> Do this instead in all your proc's
>
>         ret = sqlite3_step (p_stmt);
>         if (SQLITE_BUSY == ret)
>         {
>                 int n=0;
>                 usleep(10); // try one more time before error
>                 while ((ret=sqlite3_step(p_stmt))==SQLITE_BUSY) {
>                         printf("proc1 ret==BUSY %d\n",++n);
>                         usleep(10);
>                 }
>         }
>
> And you'll also need to handle "database is locked" coming from your
> prepare statements.  I saw that error too.
> You'll need to loop there too.
>
> The more you drop the usleep time the more times it will show as busy.
> 1/10th or 1/100th of second is about all you want I would think.
>
> And get rid of the usleep at the bottom of each proc -- it's pretty
> useless at 100 microseconds.  You don't need to sleep unless you're
> busy.
>
> I tested your code with this and got no errors at all -- just a bunch
> of
> BUSY messages.
>
>
> Not sure what your purpose is in sqlrun.c with looping and killing.
> Looks
> pretty squirrely to me.  You're not waiting for the forks to finish so
> what is your logic here?
>
> Michael D. Black
> Senior Scientist
> Northrop Grumman Mission Systems
>
>
> 
>
> From: sqlite-users-boun...@sqlite.org on behalf of liubin liu
> Sent: Tue 5/11/2010 4:57 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] multi processes, so many

[sqlite] JDBC for SQLite

2010-05-14 Thread Serdar Genc

I have been using Xerial for a while but I have noticed that some
implementations are missing for ResultSet meta . Could anyone advice me
another JDBC for SQlite? or should I continue with Xerial..

Thanks in advance,

Serdar Genc
web: http://www.iptakip.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] multi processes, so many errores of SQLITE_BUSY and SQLITE_MISUSE

2010-05-14 Thread liubin liu


How to rollback current transaction?





Pavel Ivanov-2 wrote:
> 
>> I assume you want the sqllite3_stmt to work -- so you need to loop that
>> while it's busy.
> 
> Michael, don't give bad advices.
> The most general advice when one gets SQLITE_BUSY is to reset/finalize
> all statements and rollback current transaction. It's only in certain
> type of transactions and certain type of statements one can loop while
> sqlite3_stmt returns SQLITE_BUSY. But even in this case one must call
> sqlite3_reset on the statement that returned SQLITE_BUSY. And this
> call to sqlite3_reset will return SQLITE_BUSY again.
> 
> 
> Pavel
> 
> On Thu, May 13, 2010 at 7:20 AM, Black, Michael (IS)
>  wrote:
>> It also means you're not handling the SQLITE_BUSY from the sqlite3_stmt()
>> before it.
>> I assume you want the sqllite3_stmt to work -- so you need to loop that
>> while it's busy.
>>
>> Michael D. Black
>> Senior Scientist
>> Northrop Grumman Mission Systems
>>
>>
>> 
>>
>> From: sqlite-users-boun...@sqlite.org on behalf of liubin liu
>> Sent: Thu 5/13/2010 2:07 AM
>> To: sqlite-users@sqlite.org
>> Subject: Re: [sqlite] multi processes, so many errores of SQLITE_BUSY and
>> SQLITE_MISUSE
>>
>>
>>
>>
>> I see many SQLITE_BUSY returned from sqlite3_finalize(). Are they memory
>> leak
>> because it don't succeed in finalizing the sqlite3_stmt pointer?
>>
>>
>>
>>
>> Black, Michael (IS) wrote:
>>>
>>> SQLITE_BUSY is not an error...just a fact.
>>>
>>> All your processes cannot work on the database at the same time...at
>>> least
>>> not when one of them is doing an insert.  You could be changing the
>>> table
>>> while you're scanning it.  EXXCLUSIVE doesn't change that idea.
>>>
>>> Somebody please correct me if I'm wrong on this one...
>>> I think sqlite can work with multiple processes just doing read-onliy
>>> operations (like SELECT).    It's just the write operations
>>> (INSERT/UPDATE) which will cause SQLITE_BUSY to occur.
>>>
>>> Michael D. Black
>>> Senior Scientist
>>> Northrop Grumman Mission Systems
>>>
>>>
>>> 
>>>
>>> From: sqlite-users-boun...@sqlite.org on behalf of liubin liu
>>> Sent: Tue 5/11/2010 9:20 PM
>>> To: sqlite-users@sqlite.org
>>> Subject: Re: [sqlite] multi processes, so many errores of SQLITE_BUSY
>>> and
>>> SQLITE_MISUSE
>>>
>>>
>>>
>>>
>>> Thank you very much!
>>>
>>> It may be because my system's resource is limited. It's a embedded
>>> system
>>> containing 32M RAM, ARM9 CPU.
>>>
>>> My "reiterating 20 times" is already using usleep().
>>>
>>> After I add the loop in the prepare statements, the system performance
>>> is
>>> still very bad... And there are still many errores of SQLITE_BUSY.
>>>
>>> The only improvement is the disappear of the error of SQLITE_MISUSE.
>>>
>>> And I tried to use the "BEGIN EXCLUSIVE TRANSACTION". The things are
>>> same
>>> with them without using it.
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> Black, Michael (IS) wrote:

 Your "reiterating 20 times" is not using a usleep so you'll blow by
 this
 most every time it's busy.

 Do this instead in all your proc's

         ret = sqlite3_step (p_stmt);
         if (SQLITE_BUSY == ret)
         {
                 int n=0;
                 usleep(10); // try one more time before error
                 while ((ret=sqlite3_step(p_stmt))==SQLITE_BUSY) {
                         printf("proc1 ret==BUSY %d\n",++n);
                         usleep(10);
                 }
         }

 And you'll also need to handle "database is locked" coming from your
 prepare statements.  I saw that error too.
 You'll need to loop there too.

 The more you drop the usleep time the more times it will show as busy.
 1/10th or 1/100th of second is about all you want I would think.

 And get rid of the usleep at the bottom of each proc -- it's pretty
 useless at 100 microseconds.  You don't need to sleep unless you're
 busy.

 I tested your code with this and got no errors at all -- just a bunch
 of
 BUSY messages.


 Not sure what your purpose is in sqlrun.c with looping and killing.
 Looks
 pretty squirrely to me.  You're not waiting for the forks to finish so
 what is your logic here?

 Michael D. Black
 Senior Scientist
 Northrop Grumman Mission Systems


 

 From: sqlite-users-boun...@sqlite.org on behalf of liubin liu
 Sent: Tue 5/11/2010 4:57 AM
 To: sqlite-users@sqlite.org
 Subject: [sqlite] multi processes, so many errores of SQLITE_BUSY and
 SQLITE_MISUSE

 ...

 --
 View this message in context:
 http://old.nabble.com/multi-processes%2C-so-many-errores-of-SQLITE_BUSY-and-SQLITE_MISUSE-tp28522127p28522127.html
 Sent from the SQLite mailing list archive at Nabble.com.

Re: [sqlite] prevent sqlite from interpreting string as numbers?

[sqlite] prevent sqlite from interpreting string as numbers?

Re: [sqlite] sqlite update with joins

Re: [sqlite] Composite Foreign Key not enforced

Re: [sqlite] Composite Foreign Key not enforced

Re: [sqlite] data type from join multiple tables

Re: [sqlite] data type from join multiple tables

Re: [sqlite] data type from join multiple tables

Re: [sqlite] data type from join multiple tables

Re: [sqlite] data type from join multiple tables

Re: [sqlite] data type from join multiple tables

Re: [sqlite] data type from join multiple tables

Re: [sqlite] data type from join multiple tables

Re: [sqlite] Incorrect assertion in mutexes plus pcache1Alloc

Re: [sqlite] data type from join multiple tables

Re: [sqlite] Incorrect assertion in mutexes plus pcache1Alloc

Re: [sqlite] data type from join multiple tables

[sqlite] data type from join multiple tables

Re: [sqlite] multi processes, so many errores of SQLITE_BUSY and SQLITE_MISUSE

Re: [sqlite] multi processes, so many errores of SQLITE_BUSY and SQLITE_MISUSE

Re: [sqlite] multi processes, so many errores of SQLITE_BUSY and SQLITE_MISUSE

[sqlite] JDBC for SQLite

Re: [sqlite] multi processes, so many errores of SQLITE_BUSY and SQLITE_MISUSE

23 matches

Site Navigation

Mail list logo

Footer information