[sqlite] SQLite Trace Log

[Winston Brummer]

Hi

I saw in the version history that SQLite makes use of trace listeners.
Could anyone give me an example of how to attach a trace listener to an
application that uses System.Data.SQLite? Specifically related to the
compact frame work version for Windows Mobile and Pocket PC.

Any help would be greatly appreciated.
Winston
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to get the RecNo ???

[Mohit Sindhwani]

Hi Igor, Keith,

I think my explanation wasn't very clear.

I just meant to say that ROWID is not a sequence number of insertion in 
the case when an INTEGER PRIMARY KEY is used - it comes across as a 
sequence number when we don't have an integer primary key.


Rest of the answers less relevant now - but inline.

On 11/2/2013 11:45 AM, Igor Tandetnik wrote:

On 2/10/2013 10:06 PM, Mohit Sindhwani wrote:

The OP reads records in a loop. I imagine he or she may simply use a 
loop counter as a sequence number, if that's what is needed. However, 
I too read the original question as asking for a unique identifier, 
such as ROWID, rather than a sequence number (which, as I said, is 
easy to obtain). The OP is asking for "record number that sqlite 
creates when it creates your record" - that is, the ID generated on 
row insertion, also known as ROWID.

That is, of course, correct.




2. The problem with INTEGER PRIMARY KEY.. it is not representative of
what we expect from a ROWID.  We expect ROWID to be a sequential number
that increases with every record insertion.


Who do you mean "we", Kemo Sabe?


By "we", I probably meant myself :)  When I saw ROWID first, I assumed 
it was a sequence number of sorts.. and that lead to all sorts of 
problems.  My intention was to warn someone else to not make that 
assumption.






 However, if you have
something like num INTEGER PRIMARY KEY, you will be able to do this:
insert with num=1  #rowid = 1
insert with num=2  #rowid = 2
insert with num=4  #rowid = 4
insert with num=3  #rowid = 3

It could be argued that the OP wanted the 3rd record to have a row
number of 3, rather than 4.


Why then would the OP insert the row with num=4, rather than 3? Why do 
the extra work for the sole purpose of hurting your goal? That doesn't 
make much sense.


Was meant to be an illustration that ROWID is not a sequence number...




So, it doesn't give the concept of sequence at all since an INTEGER
PRIMARY KEY needs to be unique, but does not have to be monotonically
increasing.


Well, if you go out of your way to intentionally break the 
monotonically increasing sequence, then you end up with a broken 
sequence. Gun, meet foot.


Again, meant to illustrate that when you are not using an INTEGER 
PRIMARY KEY, the ROWID is monotonically increasing.  When using an 
INTEGER PRIMARY KEY, that will not be so.  So, just be careful if you 
intend to do something like select * from mytable ORDER by ROWID DESC 
LIMIT 1; and hope to select the last inserted record if you have an 
INTEGER PRIMARY KEY on that table.





I have been caught out by this - I read what the documentation says but
just did not carefully understand it.  What the above means is this:
* You do an insert in the sequence as above, you say that I should not
sort by id ASC because you want it in insertion order
* You decide then to do a sort by ROWID ASC - expecting that ROWID is
maintaining the sequence since when you do a general SELECT * from
mytable; you often get it in insertion sequence (if I'm not wrong).


You are wrong. Normally, with a simple SELECT like this, you get the 
results ordered by ROWID. SQLite table is stored as a b-tree, with 
ROWID as its key, so that's the natural order of traversal.


Learn something every day :)

Thanks!

Best Regards,
Mohit.


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

Re: [sqlite] How to get the RecNo ???

[Keith Medcalf]

> I have been caught out by this - I read what the documentation says but
> just did not carefully understand it.  What the above means is this:
> * You do an insert in the sequence as above, you say that I should not
> sort by id ASC because you want it in insertion order

> * You decide then to do a sort by ROWID ASC - expecting that ROWID is
> maintaining the sequence since when you do a general SELECT * from
> mytable; you often get it in insertion sequence (if I'm not wrong).

It may *appear* that a "select * from table" returns rows in insertion order 
however, this is not the case.  A table-scan traverses, inorder, the btree 
structure which comprises the table.  The key to this b-tree is the ROWID and 
the rest of the table row is the payload.  "select * from table" will return 
the rows in insertion order if and only if you have not every explicitly set a 
ROWID when inserting a row, and if and only if you have never overflowed the 
ROWID.

An SQL database is not a sequential file and the concept of "Record Number" as 
in a sequential file simply does not apply.

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org




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

Re: [sqlite] How to get the RecNo ???

[Mohit Sindhwani]

Hi Peter,

I have been caught out on this.

On 11/2/2013 8:40 AM, Peter Aronson wrote:
You can add it to the select list as OID, ROWID or _ROWID_ or, if the 
table has a column defined INTEGER PRIMARY KEY (but not INTEGER 
PRIMARY KEY DESC) it'll also be this value.  See: 
http://www.sqlite.org/lang_createtable.html#rowid


There are a couple of very fine points here:

1. If I'm not wrong, ROWID does not change when you delete records. So, 
if the OP wants the ability to always say "it's the 9th record in the 
table right now", it won't work.  Given that an RDBMS table is a set and 
in theory, sets don't have that property, actually, I don't think SQLite 
by itself can give you that number.  What you need is actually something 
like a monotonically increasing number (say id) and then your query will 
need to take into account a way to count the number of records where id 
< my_record_id - an alternative could be to use the time stamp of 
insertion and do the same.


2. The problem with INTEGER PRIMARY KEY.. it is not representative of 
what we expect from a ROWID.  We expect ROWID to be a sequential number 
that increases with every record insertion.  However, if you have 
something like num INTEGER PRIMARY KEY, you will be able to do this:

insert with num=1  #rowid = 1
insert with num=2  #rowid = 2
insert with num=4  #rowid = 4
insert with num=3  #rowid = 3

It could be argued that the OP wanted the 3rd record to have a row 
number of 3, rather than 4.
In these small numbers, it can still be understood because you could say 
that num=3 comes before num=4 and that's what the user should expect to 
get.  But remember that SQLite will behave the same way with large numbers

insert with num=2383831  #rowid = 2383831
insert with num=2343832  #rowid = 2343832
insert with num=4273  #rowid = 4273
insert with num=300029393  #rowid = 300029393

So, it doesn't give the concept of sequence at all since an INTEGER 
PRIMARY KEY needs to be unique, but does not have to be monotonically 
increasing.


I have been caught out by this - I read what the documentation says but 
just did not carefully understand it.  What the above means is this:
* You do an insert in the sequence as above, you say that I should not 
sort by id ASC because you want it in insertion order
* You decide then to do a sort by ROWID ASC - expecting that ROWID is 
maintaining the sequence since when you do a general SELECT * from 
mytable; you often get it in insertion sequence (if I'm not wrong).


Depending on what problem the OP wants to solve, the solution is likely 
to be a bit more complex.


Best Regards,
Mohit.



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

Re: [sqlite] "default value of column [name] is not constant." error in table creation when using double quoted string literal in parenthesis

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 10/02/13 14:27, Simon Slavin wrote:
> Any chance of killing it in SQLite 4 ?

Continuing to beat a truly dead horse, it would be nice to help developers
fix their existing codebase via something like an additional "lint mode".

  http://www.sqlite.org/src/info/25e09aa2ab

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlEYS6EACgkQmOOfHg372QR+kgCePgJMHJ55coumXdEIsIOlOhJr
Ns8An1bmLOFeJ/5vYMBa8nQJSw31MmLC
=czW1
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to get the RecNo ???

[Peter Aronson]

You can add it to the select list as OID, ROWID or _ROWID_ or, if the 
table has a column defined INTEGER PRIMARY KEY (but not INTEGER PRIMARY 
KEY DESC) it'll also be this value.  See: 
http://www.sqlite.org/lang_createtable.html#rowid


Peter
On 2/10/2013 5:23 PM, roystonja...@comcast.net wrote:

After you do a retrieve from the database, how would to access the RecNo for 
each record?  I can get to all the fields but I don't know how to access the 
record number that sqlite creates when it creates your record.  I am not 
looking for the last record number created.

I will be populating a ListView and I want to add the RecNo in the caption.

"SELECT * FROM TestTable"

~while(sqlite3_step(stmt) == SQLITE_ROW)
~{
~~sName = (char*)sqlite3_column_text(stmt, 0);
   sLName = (char*)sqlite3_column_text(stmt, 1);



Thanks



___
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.

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



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

[sqlite] How to get the RecNo ???

[roystonjames]

After you do a retrieve from the database, how would to access the RecNo for 
each record?  I can get to all the fields but I don't know how to access the 
record number that sqlite creates when it creates your record.  I am not 
looking for the last record number created.

I will be populating a ListView and I want to add the RecNo in the caption.

"SELECT * FROM TestTable"

~while(sqlite3_step(stmt) == SQLITE_ROW)
~{
~~sName = (char*)sqlite3_column_text(stmt, 0);
  sLName = (char*)sqlite3_column_text(stmt, 1);



Thanks



___
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.

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

Re: [sqlite] Deletion slow?

[Jason Gauthier]

>Ah, I did not understand this.   I ran three tests after enabling this:
>root@raspberrypi:/opt/obdpi/sql# sqlite3 trip.db 'PRAGMA journal_mode=WAL'
>wal
># time sqlite3 trip.db "delete from trip where key<=200"
>real0m0.642s


[edited]
Sqlite4
>time /root/sqlite4/sqlite4 trip.db "delete from trip where key<=1358697579"

>real0m0.623s
># time /root/sqlite4/sqlite4 trip.db "delete from trip where key<=1358697779"

>Thanks for reading, this was a long one!


I just wanted to follow up on this.  It sounds like from our discussion I am 
doing everything I can, and at this point this is probably a limitation of the 
hardware.

I reduced the indexes from 4 to 1, and realized some performance increase. I 
also tested with sqlite4, and also had some more performance increase.

Should I call this day, and leave it like this with sqlite3, or preferably, 
maybe compile against sqlite4, if this speed is a requirement?

Thanks!

Jason


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

Re: [sqlite] "default value of column [name] is not constant." error in table creation when using double quoted string literal in parenthesis

[Richard Hipp]

On Sun, Feb 10, 2013 at 5:27 PM, Simon Slavin  wrote:

>
> On 10 Feb 2013, at 8:39pm, Richard Hipp  wrote:
>
> > The fact that SQLite will treat a double-quoted string as a string
> literal
> > rather than as a quoted identifier is a horrible mis-feature.  It was
> added
> > 10 years or so ago in an attempt to be more MySQL-compatible.  I have
> come
> > to sorely regret that change.  I'd love to get rid of this mis-feature
>
> Any chance of killing it in SQLite 4 ?  I'm a big fan of getting rid of
> things which were once (understandably) implemented for reasons like this.
>

Yes.  One of the big purposes of SQLite4 is to get rid of cruft like this.



>
> Simon.
> ___
> 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] "default value of column [name] is not constant." error in table creation when using double quoted string literal in parenthesis

[Simon Slavin]

On 10 Feb 2013, at 8:39pm, Richard Hipp  wrote:

> The fact that SQLite will treat a double-quoted string as a string literal
> rather than as a quoted identifier is a horrible mis-feature.  It was added
> 10 years or so ago in an attempt to be more MySQL-compatible.  I have come
> to sorely regret that change.  I'd love to get rid of this mis-feature

Any chance of killing it in SQLite 4 ?  I'm a big fan of getting rid of things 
which were once (understandably) implemented for reasons like this.

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

Re: [sqlite] "default value of column [name] is not constant." error in table creation when using double quoted string literal in parenthesis

[Bogdan Ureche]

Hi Richard,

Thank you for taking the time to reply. Personally I never use
double-quoted strings as string literals, but this issue was reported by
users of a tool that generates table creation SQL based on user input, and
that encloses in parenthesis any default values entered by the user to
accommodate expressions. This is no longer an issue in this case as I
implemented a workaround.

Perhaps this could be fixed in SQLite 4 if backwards compatibility is less
a concern?

Regards,
Bogdan Ureche






On Sun, Feb 10, 2013 at 2:39 PM, Richard Hipp  wrote:

> The fact that SQLite will treat a double-quoted string as a string literal
> rather than as a quoted identifier is a horrible mis-feature.  It was added
> 10 years or so ago in an attempt to be more MySQL-compatible.  I have come
> to sorely regret that change.  I'd love to get rid of this mis-feature, but
> cannot do so now, since there are millions of applications in the wild that
> use SQLite and some percentage of those (hopefully a very small percentage,
> but still non-zero) will break if I remove the mis-feature.
>
> The point is that allowing double-quoted strings is a tragic design error.
>
> You should never make use of this feature.  Ever.  All of your string
> literals should use the SQL-standard single-quote notation.
>
> You are correct that the mis-feature is inconsistent in its application.
> But remember that it is a mis-feature.  You shouldn't be using it, and so
> inconsistencies in its implementation shouldn't matter to you.
> Furthermore, since the only reason for preserving this mis-feature is for
> backwards compatibility in legacy applications, I see no need to try to
> make it more consistent.  Any attempts at making it consistent would likely
> just break a few legacy applications, and if I were willing to do that I
> would simply remove the mis-feature all together.
>
> On Sun, Feb 10, 2013 at 2:52 PM, Bogdan Ureche 
> wrote:
>
> > Using SQLite 3.7.15.2. The following statements execute with no error:
> >
> > CREATE TABLE [test1] (
> >   [id] INTEGER,
> >   [name] CHAR DEFAULT 'test');
> >
> > CREATE TABLE [test2] (
> >   [id] INTEGER,
> >   [name] CHAR DEFAULT ('test'));
> >
> > CREATE TABLE [test3] (
> >   [id] INTEGER,
> >   [name] CHAR DEFAULT "test");
> >
> >
> > However, the following returns "default value of column [name] is not
> > constant."
> >
> > CREATE TABLE [test4] (
> >   [id] INTEGER,
> >   [name] CHAR DEFAULT ("test"));
> >
> >
> > There are no identifiers in this context named "test" - no other tables
> or
> > columns, and "test" is not a keyword. Changing "test" to a keyword like
> > "key" or "glob" returns the same error.
> >
> > The SQLite documentation reads:
> >
> > "If a keyword in double quotes (ex: "key" or "glob") is used in a context
> > where it cannot be resolved to an identifier but where a string literal
> is
> > allowed, then the token is understood to be a string literal instead of
> an
> > identifier."
> >
> > So in the last statement, "test" (or "key") is used in a context where it
> > cannot be resolved to an identifier but the token is *not* understood to
> be
> > a string literal.
> >
> >  To resume: while "test" is evaluated correctly as a string literal,
> after
> > enclosing it in parenthesis ("test") it no longer is.
> >
> > Does this work as intended?
> >
> > BTW, in my opinion the above quote should be changed to:
> >
> > "If a string in double quotes (be it keyword or not) is used in a context
> > where it cannot be resolved to an identifier but where a string literal
> is
> > allowed, then the token is understood to be a string literal instead of
> an
> > identifier."
> >
> > Regards,
> > Bogdan Ureche
> > ___
> > 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "default value of column [name] is not constant." error in table creation when using double quoted string literal in parenthesis

[Richard Hipp]

The fact that SQLite will treat a double-quoted string as a string literal
rather than as a quoted identifier is a horrible mis-feature.  It was added
10 years or so ago in an attempt to be more MySQL-compatible.  I have come
to sorely regret that change.  I'd love to get rid of this mis-feature, but
cannot do so now, since there are millions of applications in the wild that
use SQLite and some percentage of those (hopefully a very small percentage,
but still non-zero) will break if I remove the mis-feature.

The point is that allowing double-quoted strings is a tragic design error.

You should never make use of this feature.  Ever.  All of your string
literals should use the SQL-standard single-quote notation.

You are correct that the mis-feature is inconsistent in its application.
But remember that it is a mis-feature.  You shouldn't be using it, and so
inconsistencies in its implementation shouldn't matter to you.
Furthermore, since the only reason for preserving this mis-feature is for
backwards compatibility in legacy applications, I see no need to try to
make it more consistent.  Any attempts at making it consistent would likely
just break a few legacy applications, and if I were willing to do that I
would simply remove the mis-feature all together.

On Sun, Feb 10, 2013 at 2:52 PM, Bogdan Ureche  wrote:

> Using SQLite 3.7.15.2. The following statements execute with no error:
>
> CREATE TABLE [test1] (
>   [id] INTEGER,
>   [name] CHAR DEFAULT 'test');
>
> CREATE TABLE [test2] (
>   [id] INTEGER,
>   [name] CHAR DEFAULT ('test'));
>
> CREATE TABLE [test3] (
>   [id] INTEGER,
>   [name] CHAR DEFAULT "test");
>
>
> However, the following returns "default value of column [name] is not
> constant."
>
> CREATE TABLE [test4] (
>   [id] INTEGER,
>   [name] CHAR DEFAULT ("test"));
>
>
> There are no identifiers in this context named "test" - no other tables or
> columns, and "test" is not a keyword. Changing "test" to a keyword like
> "key" or "glob" returns the same error.
>
> The SQLite documentation reads:
>
> "If a keyword in double quotes (ex: "key" or "glob") is used in a context
> where it cannot be resolved to an identifier but where a string literal is
> allowed, then the token is understood to be a string literal instead of an
> identifier."
>
> So in the last statement, "test" (or "key") is used in a context where it
> cannot be resolved to an identifier but the token is *not* understood to be
> a string literal.
>
>  To resume: while "test" is evaluated correctly as a string literal, after
> enclosing it in parenthesis ("test") it no longer is.
>
> Does this work as intended?
>
> BTW, in my opinion the above quote should be changed to:
>
> "If a string in double quotes (be it keyword or not) is used in a context
> where it cannot be resolved to an identifier but where a string literal is
> allowed, then the token is understood to be a string literal instead of an
> identifier."
>
> Regards,
> Bogdan Ureche
> ___
> 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] match on single column but with multiple value

[e-mail mgbg25171]

That's great Mike. Thanks very much!

On 10 February 2013 20:26, Mike King  wrote:

> Select * from tbl where col1 in ('a', 'b', 'c')
>
>
>
> On Sunday, 10 February 2013, e-mail mgbg25171 wrote:
>
> > Sorry if this is a very basic question but I'm just wondering if there's
> a
> > more elegant way of doing this
> >
> > select * from tbl where col1 = 'a' or col1 = 'b' or col1 = 'c'
> >
> > i.e. selecting rows if a particular column has one of SEVERAL values.
> >
> > Any help much appreciated.
> > ___
> > 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] match on single column but with multiple value

[Mike King]

Select * from tbl where col1 in ('a', 'b', 'c')



On Sunday, 10 February 2013, e-mail mgbg25171 wrote:

> Sorry if this is a very basic question but I'm just wondering if there's a
> more elegant way of doing this
>
> select * from tbl where col1 = 'a' or col1 = 'b' or col1 = 'c'
>
> i.e. selecting rows if a particular column has one of SEVERAL values.
>
> Any help much appreciated.
> ___
> 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] Sqlite and AS400

[Mauro Bertoli]

>> Hi all, I'm a new user in this list. Is possible to connect
>> to a SQLITE database from AS400? Any help will be appreciated.
>> Mauro
>
>There appears to be a sqlite3 port to AS400 
>embedded in "iSeries Python".
>
>http://www.iseriespython.com/app/ispMain.py/Start?job=Posts==ViewSubject=LastPost=6=20=Y=598
>
>If this is not what you are looking for, please specify your problem.


This is a good starting point. I googled a lot but don't found it!
Thanks a lot!
Mauro
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] match on single column but with multiple value

[e-mail mgbg25171]

Sorry if this is a very basic question but I'm just wondering if there's a
more elegant way of doing this

select * from tbl where col1 = 'a' or col1 = 'b' or col1 = 'c'

i.e. selecting rows if a particular column has one of SEVERAL values.

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

Re: [sqlite] To import csv file in C#

[Brad Hards]

On 09/02/13 13:49, mukesh kumar mehta wrote:

Is there any option to import csv file into sqlite database with the
help of System.Data.Sqlite.dll. As like shell command ".import
file_name table_name".

SpatiaLite can do this (either as a virtual table, or an import). There
are probably other extensions that can do this too.


As like "bulk insert" which uses in sqlserver. As like "Load Data"
which uses in mysql.

This doesn't make as much sense in SQLite, because you'd be better off
just creating a new SQLite database and moving that around rather than
some dump format.

Brad

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

Re: [sqlite] Sqlite and AS400

[Kees Nuyt]

On Sun, 10 Feb 2013 18:42:08 + (GMT), Mauro Bertoli
 wrote:

> Hi all, I'm a new user in this list. Is possible to connect
> to a SQLITE database from AS400? Any help will be appreciated.
> Mauro

There appears to be a sqlite3 port to AS400 
embedded in "iSeries Python".

http://www.iseriespython.com/app/ispMain.py/Start?job=Posts==ViewSubject=LastPost=6=20=Y=598

If this is not what you are looking for, please specify your problem.

-- 
Groet, Cordialement, Pozdrawiam, Regards,

Kees Nuyt

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

[sqlite] "default value of column [name] is not constant." error in table creation when using double quoted string literal in parenthesis

[Bogdan Ureche]

Using SQLite 3.7.15.2. The following statements execute with no error:

CREATE TABLE [test1] (
  [id] INTEGER,
  [name] CHAR DEFAULT 'test');

CREATE TABLE [test2] (
  [id] INTEGER,
  [name] CHAR DEFAULT ('test'));

CREATE TABLE [test3] (
  [id] INTEGER,
  [name] CHAR DEFAULT "test");


However, the following returns "default value of column [name] is not
constant."

CREATE TABLE [test4] (
  [id] INTEGER,
  [name] CHAR DEFAULT ("test"));


There are no identifiers in this context named "test" - no other tables or
columns, and "test" is not a keyword. Changing "test" to a keyword like
"key" or "glob" returns the same error.

The SQLite documentation reads:

"If a keyword in double quotes (ex: "key" or "glob") is used in a context
where it cannot be resolved to an identifier but where a string literal is
allowed, then the token is understood to be a string literal instead of an
identifier."

So in the last statement, "test" (or "key") is used in a context where it
cannot be resolved to an identifier but the token is *not* understood to be
a string literal.

 To resume: while "test" is evaluated correctly as a string literal, after
enclosing it in parenthesis ("test") it no longer is.

Does this work as intended?

BTW, in my opinion the above quote should be changed to:

"If a string in double quotes (be it keyword or not) is used in a context
where it cannot be resolved to an identifier but where a string literal is
allowed, then the token is understood to be a string literal instead of an
identifier."

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

Re: [sqlite] random, infrequent disk I/O errors

[Patrik Nilsson]

Hello,

This letter is a help getting you started to finding your error.

There are many SQLITE_IOERR-errors, for example SQLITE_IOERR_NOMEM which
means out of memory.

I have noticed in my application that I sometimes get out of memory when
calling g_string_new() after modifying the whole table, i.e. adding a
new column.

After thinking about what it is I compiled the sqlite-code with
SQLITE_ENABLE_MEMORY_MANAGEMENT and called sqlite3_release_memory()
after doing much change to the database. I haven't got that error since.

Even though this change is relatively recently I have made test
stresses, but as for now it seems to work.

"The sqlite3_release_memory() interface attempts to free N bytes of heap
memory by deallocating non-essential memory allocations held by the
database library."

Line 23566 in sqlite.c v3.7.15.1:
/*
** This routine translates a standard POSIX errno code into something
** useful to the clients of the sqlite3 functions.  Specifically, it is
** intended to translate a variety of "try again" errors into SQLITE_BUSY
** and a variety of "please close the file descriptor NOW" errors into
** SQLITE_IOERR
**
** Errors during initialization of locks, or file system support for locks,
** should handle ENOLCK, ENOTSUP, EOPNOTSUPP separately.
*/

Line 1021 in sqlite.c v3.7.15.1:
#define SQLITE_IOERR_READ  (SQLITE_IOERR | (1<<8))
#define SQLITE_IOERR_SHORT_READ(SQLITE_IOERR | (2<<8))
#define SQLITE_IOERR_WRITE (SQLITE_IOERR | (3<<8))
#define SQLITE_IOERR_FSYNC (SQLITE_IOERR | (4<<8))
#define SQLITE_IOERR_DIR_FSYNC (SQLITE_IOERR | (5<<8))
#define SQLITE_IOERR_TRUNCATE  (SQLITE_IOERR | (6<<8))
#define SQLITE_IOERR_FSTAT (SQLITE_IOERR | (7<<8))
#define SQLITE_IOERR_UNLOCK(SQLITE_IOERR | (8<<8))
#define SQLITE_IOERR_RDLOCK(SQLITE_IOERR | (9<<8))
#define SQLITE_IOERR_DELETE(SQLITE_IOERR | (10<<8))
#define SQLITE_IOERR_BLOCKED   (SQLITE_IOERR | (11<<8))
#define SQLITE_IOERR_NOMEM (SQLITE_IOERR | (12<<8))
#define SQLITE_IOERR_ACCESS(SQLITE_IOERR | (13<<8))
#define SQLITE_IOERR_CHECKRESERVEDLOCK (SQLITE_IOERR | (14<<8))
#define SQLITE_IOERR_LOCK  (SQLITE_IOERR | (15<<8))
#define SQLITE_IOERR_CLOSE (SQLITE_IOERR | (16<<8))
#define SQLITE_IOERR_DIR_CLOSE (SQLITE_IOERR | (17<<8))
#define SQLITE_IOERR_SHMOPEN   (SQLITE_IOERR | (18<<8))
#define SQLITE_IOERR_SHMSIZE   (SQLITE_IOERR | (19<<8))
#define SQLITE_IOERR_SHMLOCK   (SQLITE_IOERR | (20<<8))
#define SQLITE_IOERR_SHMMAP(SQLITE_IOERR | (21<<8))
#define SQLITE_IOERR_SEEK  (SQLITE_IOERR | (22<<8))
#define SQLITE_IOERR_DELETE_NOENT  (SQLITE_IOERR | (23<<8))

Best Regards,
Patrik


On 02/10/2013 05:28 PM, Greg Janée wrote:
> Hello, I'm running a web service that uses SQLite that throws a disk I/O
> exception every once in a while, meaning once every few weeks.
> 
> Details: SQLite 3.7.0.1, being called from an Apache/Django/Python
> multi-threaded application running on Solaris 10.  The database file is
> on a local filesystem, and is <200MB.  Disk space is not an issue on
> that filesystem.
> 
> Everything appears fine, and then one transaction out of the blue gets a
> disk I/O error when doing a "BEGIN IMMEDIATE".  The database passes an
> integrity check.  These disk I/O errors don't seem to correlate with
> anything.  When one happens, there isn't a lot of activity on the system
> (individual transactions occurring once every few seconds, say, so
> threading/locking/contention wouldn't seem to be an issue), and the
> transactions immediately before and after the failed transaction
> succeeded just fine.  If there is any correlation, they all seem to
> happen in the middle of the night.  I've checked with my sysadmin, and
> he can't think of anything (backups, virus scans, etc.) that might be
> happening at that time.
> 
> I've searched for help on this topic and have come up with nothing.  It
> should be clear from the above that directory permissions are not the
> problem.
> 
> Any ideas?  Unfortunately, the (standard) Python SQLite wrapper I'm
> using doesn't provide access to any more information (if there is any to
> be had).
> 
> Thanks in advance,
> -Greg
> 
> ___
> 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] Sqlite and AS400

[Mauro Bertoli]

Hi all, I'm a new user in this list. Is possible to connect to a SQLITE 
database from AS400? Any help will be appreciated.
Mauro
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] random, infrequent disk I/O errors

[Dominique Pellé]

Greg Janée wrote:

> Hello, I'm running a web service that uses SQLite that throws a disk I/O
> exception every once in a while, meaning once every few weeks.
...snip...
> Any ideas?  Unfortunately, the (standard) Python SQLite wrapper I'm using
> doesn't provide access to any more information (if there is any to be had).


I've never tried it, but doing a Google search
for "python errno" suggests that you can access
errno in Python:

http://stackoverflow.com/questions/661017/access-to-errno-from-python

Hopefully that can give better information about the error
(file system full, etc.)

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

Re: [sqlite] random, infrequent disk I/O errors

[Simon Slavin]

On 10 Feb 2013, at 4:28pm, Greg Janée  wrote:

> Any ideas?  Unfortunately, the (standard) Python SQLite wrapper I'm using 
> doesn't provide access to any more information (if there is any to be had).

That would make diagnosis difficult.  Please check to see whether you can 
enable extended result codes:



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