[sqlite] BLOB & Other Data Type

[techi eth]

Hi,

 What is difference by using Colum data type as blob or Any other
(Text,INT,REAL,NUMERIC)

http://www.sqlite.org/datatype3.html

According to above link I understand “The value is a blob of data, stored
exactly as it was input”.I beleive that it what required.

Ex : Please let me know difference between below two operration.

CREATE TABLE Test ( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE
INT)

INSERT INTO Test (ID,NAME,AGE)

VALUES (1, 'Test', 32);

 Or

CREATE TABLE Test ( ID BLOB PRIMARY KEY NOT NULL, NAME BLOB NOT NULL, AGE
BLOB)

INSERT INTO Test (ID,NAME,AGE)

VALUES (1, 'Test', 32);

Cheers -

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

Re: [sqlite] BETWEEN and explicit collation assignment

[James K. Lowden]

On Wed, 21 Aug 2013 20:26:30 +0100
Simon Slavin  wrote:

> My problem is not with COLLATE in general.  It's with expressions.
> Table definitions are fine.  Index definitions are fine.  It's purely
> that my understanding of the documention says that something like this
> 
> "ABC" COLLATE COL1 = "3F" COLLATE COL2

(I don't understand what that syntax is supposed to mean.  AFAIK, the
only operands to COLLATE are BINARY, NOCASE, and RTRIM.) 

> should be allowed, and I don't agree.

You're quite right to say that collation is property of the comparison,
not the data.  It's obvious from very fact that the same data can be
compared in different ways.  

A column's collation in CREATE TABLE is a property of the column, not
of the data.  That is, it's a property of the data *type*, the domain
to which the values in the column belong.  It informs the system how to
compare two values in the *same* column.  

The question arises: how to compare two columns with different
collations?  Because they're drawn from different domains -- per the
column defintions -- they have different types.  To compare two
different types requires conversion.  That conversion may be implicit
or explicit.  (And it many fail; some types are incommensuate.)  

The question then becomes whether to require explicit conversion and,
if so, what syntax would be clearest.  I think you'd say, and I agree,
that implicit conversion isn't attractive because any choice would be
idiosyncratic, and such choices are traps for the unwary.  

Regarding syntax, the current situation is less than idea.  I tried the
OP's query and got different results:

SQLite version 3.7.13 2012-06-11 02:05:22
...
sqlite> create table t(x);
sqlite> insert into t values('a');
sqlite> insert into t values('A');
sqlite> select * from t where x collate nocase between 'a' and 'b';
x 
--
a 
A 

Just for giggles, I tried another variation: 

sqlite> select * from t where x between 'a' and 'b' collate nocase;
x 
--
a 

The COLLATE operator is the sole postfix operator in SQLite's SQL, and
weird because it appears late but binds early.  I would suggest
instead it mimic or become part of CAST, or become its own operator.
For example:

1.  where COLLATE( x AS NOCASE )
between COLLATE( 'a' AS NOCASE )
and COLLATE( 'b' AS NOCASE )

Making it part of CAST emphasizes the fact that we're dealing in types:

2.  where CAST( x as COLLATE NOCASE ) 
between CASE( 'a' AS COLLATE NOCASE )
and CASE( 'b' AS COLLATE NOCASE )

But isn't this clearer in any case (no pun intended)? 

3.  where upper(x) between 'A' and 'B'

which suggests that each collation could become an operator in its own
right:

4.  where NOCASE(x) between NOCASE('a') and NOCASE('b')

because it allows for future expansion as more collations are added. 

--jkl


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

Re: [sqlite] Disable specific foreign key constraint sqlite3

[Simon Slavin]

On 21 Aug 2013, at 11:49pm, veeresh kumar  wrote:

> Is there a way to disable/enable specific foreign key constraint in sqlite3? 
> Below is the sqlserver query , would like to know similar query in sqlite 3
> 
> ALTER TABLE tablename WITH NOCHECK NOCHECK CONSTRAINT FK_Column

No, sorry.  You can disable all foreign key checking:



but not a specific key.

If you need to put data in but not have it checked until COMMIT you might be 
interested in deferred checking:



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

[sqlite] Disable specific foreign key constraint sqlite3

[veeresh kumar]

Hi,

Is there a way to disable/enable specific foreign key constraint in sqlite3? 
Below is the sqlserver query , would like to know similar query in sqlite 3

ALTER TABLE tablename WITH NOCHECK NOCHECK CONSTRAINT FK_Column

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

Re: [sqlite] BETWEEN and explicit collation assignment

[Igor Tandetnik]

On 8/21/2013 3:26 PM, Simon Slavin wrote:

My problem is not with COLLATE in general.  It's with expressions.  Table 
definitions are fine.  Index definitions are fine.  It's purely that my 
understanding of the documention says that something like this

"ABC" COLLATE COL1 = "3F" COLLATE COL2

should be allowed, and I don't agree.


Should you be able to write

create table t1(x1 collate col1);
create table t2(x2 collate col2);
select * from t1, t2 where x1 = x2;

If that is to be allowed, then SQLite would already need a mechanism to 
deal with your case, and then there would be no reason to explicitly 
prohibit it - just an added complexity.


Now, perhaps what you are trying to say is that it should be a syntax 
error to apply a comparison to two operands with different collations. I 
can see how such a rule can be weaved into the existing behavior in a 
consistent way. I don't think it would fly, if only for reasons of 
backward compatibility and compatibility with other database systems, 
but at least this argument is defensible.

--
Igor Tandetnik

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

Re: [sqlite] BETWEEN and explicit collation assignment

[Simon Slavin]

On 21 Aug 2013, at 8:22pm, Igor Tandetnik  wrote:

> On 8/21/2013 2:55 PM, Simon Slavin wrote:
>> 
>> On 21 Aug 2013, at 5:02pm, Igor Tandetnik  wrote:
>> 
>>> I imagine you'd still want to be able to put COLLATE clause on the column 
>>> definition, as in "create table t (x collate NOCASE);". How is this 
>>> supposed to work in your hypothetical new world?
>> 
>> It works the same as it does now, I think.
> 
> Precisely. So why would you want to invent a completely different mechanism, 
> when you already have one and have to maintain it anyway?

My problem is not with COLLATE in general.  It's with expressions.  Table 
definitions are fine.  Index definitions are fine.  It's purely that my 
understanding of the documention says that something like this

"ABC" COLLATE COL1 = "3F" COLLATE COL2

should be allowed, and I don't agree.

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

Re: [sqlite] BETWEEN and explicit collation assignment

[Igor Tandetnik]

On 8/21/2013 2:55 PM, Simon Slavin wrote:


On 21 Aug 2013, at 5:02pm, Igor Tandetnik  wrote:


I imagine you'd still want to be able to put COLLATE clause on the column definition, as 
in "create table t (x collate NOCASE);". How is this supposed to work in your 
hypothetical new world?


It works the same as it does now, I think.


Precisely. So why would you want to invent a completely different 
mechanism, when you already have one and have to maintain it anyway?



 The COLLATE clause in that position isn't used for storing the value itself.  
It's used later on for when two values in that column are compared with 
one-another.


... or with values in some other column of some other table, or indeed 
with arbitrary expressions.



Collation is a property of the value, similar to type and affinity - it must 
be, to allow this kind of annotation. Along with other properties, collation 
then affects the behavior of operators acting on the value.


I'm going to let the SQLite experts argue over that one.  I don't think a 
COLLATE can be a property of a value.  If it was you could compare two 
different values with different collations.  Perhaps it's a property of an 
index component.


I was imprecise. Collation is a property of an expression. It can be 
determined, for every expression, at statement prepare time, from the 
syntax of the statement and the database schema. In that, it's similar 
to affinity, but not to type (which is in fact a property of the value: 
the same expression may evaluate to values of different types at 
different points in the statement's execution).

--
Igor Tandetnik

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

Re: [sqlite] BETWEEN and explicit collation assignment

[Simon Slavin]

On 21 Aug 2013, at 5:02pm, Igor Tandetnik  wrote:

> I imagine you'd still want to be able to put COLLATE clause on the column 
> definition, as in "create table t (x collate NOCASE);". How is this supposed 
> to work in your hypothetical new world?

It works the same as it does now, I think.  The COLLATE clause in that position 
isn't used for storing the value itself.  It's used later on for when two 
values in that column are compared with one-another.  For example, as the 
default COLLATE for an index defined on that column.  Which would still work 
the same way.

> Collation is a property of the value, similar to type and affinity - it must 
> be, to allow this kind of annotation. Along with other properties, collation 
> then affects the behavior of operators acting on the value.

I'm going to let the SQLite experts argue over that one.  I don't think a 
COLLATE can be a property of a value.  If it was you could compare two 
different values with different collations.  Perhaps it's a property of an 
index component.


On 21 Aug 2013, at 5:11pm, Dan Kennedy  wrote:

> ORDER BY and GROUP BY clauses to consider as well. You want to do:
> 
>  SELECT ... FROM ... ORDER BY  COLLATE nocase;

Yes, those would both make sense.

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

Re: [sqlite] BETWEEN and explicit collation assignment

[Dan Kennedy]

On 08/21/2013 11:02 PM, Igor Tandetnik wrote:

On 8/21/2013 11:17 AM, Simon Slavin wrote:
Given that this level of implementation is correct I feel that any 
error in SQLite's handling of COLLATE is closer to the expression 
parsing level than the low level implementation.  The low level 
implementation is fine.  The problem would appear to be in




which states

"The COLLATE operator is a unary postfix operator that assigns a 
collating sequence to an expression."


This is bad.  It says that COLLATE modifies a value.  It shouldn't.  
COLLATE is not a value operator, it's an operator operator.  It 
modifies the '>', not a value one side of it.


I imagine you'd still want to be able to put COLLATE clause on the 
column definition, as in "create table t (x collate NOCASE);". How is 
this supposed to work in your hypothetical new world?


Collation is a property of the value, similar to type and affinity - 
it must be, to allow this kind of annotation. Along with other 
properties, collation then affects the behavior of operators acting on 
the value.


ORDER BY and GROUP BY clauses to consider as well. You want to do:

  SELECT ... FROM ... ORDER BY  COLLATE nocase;

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

Re: [sqlite] BETWEEN and explicit collation assignment

[Igor Tandetnik]

On 8/21/2013 11:17 AM, Simon Slavin wrote:

Given that this level of implementation is correct I feel that any error in 
SQLite's handling of COLLATE is closer to the expression parsing level than the 
low level implementation.  The low level implementation is fine.  The problem 
would appear to be in



which states

"The COLLATE operator is a unary postfix operator that assigns a collating sequence 
to an expression."

This is bad.  It says that COLLATE modifies a value.  It shouldn't.  COLLATE is 
not a value operator, it's an operator operator.  It modifies the '>', not a 
value one side of it.


I imagine you'd still want to be able to put COLLATE clause on the 
column definition, as in "create table t (x collate NOCASE);". How is 
this supposed to work in your hypothetical new world?


Collation is a property of the value, similar to type and affinity - it 
must be, to allow this kind of annotation. Along with other properties, 
collation then affects the behavior of operators acting on the value.

--
Igor Tandetnik

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

Re: [sqlite] [Bug] sqlite3_finalize() *DOES NOT* return most recent evaluation error code

[Dmitry Pashkevich]

Oh, now I see, thanks for the explanation.
By "next version of lsqlite3" do you mean next major version or next patch
release (0.9.2 presumably)? Looking forward to seeing that change.

In the meantime I just stopped relying on stmt:finalize() return code in
Lua and instead always use stmt:step() return code.


On Wed, Aug 21, 2013 at 7:33 PM, Doug Currie  wrote:

> > I'm unable to reproduce the problem using C.  Maybe it is in lsqlite3.
>
> Yes, lsqlite3 still uses the old sqlite3_prepare() API to maintain
> compatibility with some legacy systems. It is long past time that it should
> have changed to use sqlite3_prepare_v2().
>
> Running Richard's example with sqlite3_prepare_v2 changed to
> sqlite3_prepare gives this output:
>
> first step returns 101
> second step returns 1
> error message = SQL logic error or missing database
> finalize returns 19
>
> This doesn't match the output of lsqlite3 because the wrapper tries to be
> helpful, and when the second step fails, it calls sqlite_reset to get the
> error code. The equivalent C code is:
>
>
> #include 
> #include "sqlite3.h"
> int main(int argc, char **argv){
>   sqlite3 *db;
>   sqlite3_stmt *pStmt;
>   int rc;
>   sqlite3_open(":memory:", &db);
>   sqlite3_exec(db, "create table t(x unique);", 0, 0, 0);
>
>   //sqlite3_prepare_v2(db, "insert into t(x) values(?)", -1, &pStmt, 0);
>   sqlite3_prepare(db, "insert into t(x) values(?)", -1, &pStmt, 0);
>
>   sqlite3_bind_int(pStmt, 1, 123);
>   rc = sqlite3_step(pStmt);
>   printf("first step returns %d\n", rc);
>   sqlite3_reset(pStmt);
>   rc = sqlite3_step(pStmt);
>   printf("second step returns %d\n", rc);
>   printf("error message = %s\n", sqlite3_errmsg(db));
>
>
>   if (rc == SQLITE_ERROR)
>   {
> rc = sqlite3_reset(pStmt);
> printf("second step's reset returns %d\n", rc);
> printf("error message = %s\n", sqlite3_errmsg(db));
>   }
>
>   rc = sqlite3_finalize(pStmt);
>   printf("finalize returns %d\n", rc);
>   sqlite3_close(db);
>   return 0;
> }
>
>
>
> That prints
>
> first step returns 101
> second step returns 1
> error message = SQL logic error or missing database
> second step's reset returns 19
> error message = column x is not unique
> finalize returns 0
>
> which matches the output from the Lua script.
>
> The next version of lsqlite3 will use the recommended sqlite3_prepare_v2()
> API.
>
> e
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] [Bug] sqlite3_finalize() *DOES NOT* return most recent evaluation error code

[Doug Currie]

> I'm unable to reproduce the problem using C.  Maybe it is in lsqlite3.

Yes, lsqlite3 still uses the old sqlite3_prepare() API to maintain 
compatibility with some legacy systems. It is long past time that it should 
have changed to use sqlite3_prepare_v2().

Running Richard's example with sqlite3_prepare_v2 changed to sqlite3_prepare 
gives this output:

first step returns 101
second step returns 1
error message = SQL logic error or missing database
finalize returns 19

This doesn't match the output of lsqlite3 because the wrapper tries to be 
helpful, and when the second step fails, it calls sqlite_reset to get the error 
code. The equivalent C code is:


#include 
#include "sqlite3.h"
int main(int argc, char **argv){
  sqlite3 *db;
  sqlite3_stmt *pStmt;
  int rc;
  sqlite3_open(":memory:", &db);
  sqlite3_exec(db, "create table t(x unique);", 0, 0, 0);

  //sqlite3_prepare_v2(db, "insert into t(x) values(?)", -1, &pStmt, 0);
  sqlite3_prepare(db, "insert into t(x) values(?)", -1, &pStmt, 0);

  sqlite3_bind_int(pStmt, 1, 123);
  rc = sqlite3_step(pStmt);
  printf("first step returns %d\n", rc);
  sqlite3_reset(pStmt);
  rc = sqlite3_step(pStmt);
  printf("second step returns %d\n", rc);
  printf("error message = %s\n", sqlite3_errmsg(db));
  

  if (rc == SQLITE_ERROR)
  {
rc = sqlite3_reset(pStmt);
printf("second step's reset returns %d\n", rc);
printf("error message = %s\n", sqlite3_errmsg(db));
  }

  rc = sqlite3_finalize(pStmt);
  printf("finalize returns %d\n", rc);
  sqlite3_close(db);
  return 0;
}



That prints

first step returns 101
second step returns 1
error message = SQL logic error or missing database
second step's reset returns 19
error message = column x is not unique
finalize returns 0

which matches the output from the Lua script.

The next version of lsqlite3 will use the recommended sqlite3_prepare_v2() API.

e

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

Re: [sqlite] BETWEEN and explicit collation assignment

[Simon Slavin]

Problem
---

I feel that Clemens has brought up an important point, but I feel that rather 
than notice a specific error in the implementation of BETWEEN he has 
highlighted a conceptual error in SQLite.  Consider this comparison:

"Albert" > "albert"

It would be inappropriate to write something like

"Albert" COLLATE MYCOL1 > "albert" COLLATE MYCOL2

which means

("Albert" COLLATE MYCOL1) > ("albert" COLLATE MYCOL2)

Such a comparison, where each value is collated using a different function, is 
absurd.  The "COLLATE" mechanism is about the task of comparison, not the 
values.  It doesn't convert a value into another value, it tells SQLite how to 
compare two values.  In fact rather than the lines above the real way to 
express COLLATE would be

"Albert" > COLLATE MYCOL3 "albert"

which means something like

"Albert" (> COLLATE MYCOL3) "albert"

though you might prefer to express it

("Albert" > "303" COLLATE MYCOL3)

or perhaps

("Albert" > "303") COLLATE MYCOL3

The COLLATE clause affects the comparison, not the values.

So to extend this to the problem Clemens observed ...

x collate nocase between 'a' and 'b'

really means something like

x (between COLLATE NOCASE) 'a' and 'b'

though you might prefer to express it

(x between 'a' and 'b' COLLATE NOCASE)

or perhaps

(x between 'a' and 'b') COLLATE NOCASE

in all phrasings, the COLLATE clause applies to the comparison between x and 
'a', and to the comparison between x and 'b'.

Remedy
--

Looking at how COLLATE is implemented in SQLite



the key is the callback function.  And that works correctly: you pass it the 
two values, and it returns the result: negative, zero or positive.  And you 
pass it both values, you can't collate one value one way and another the other 
way.  Anything it might do to the values to arrive at the result is purely an 
internal matter.  This is the way I feel it should work.

Given that this level of implementation is correct I feel that any error in 
SQLite's handling of COLLATE is closer to the expression parsing level than the 
low level implementation.  The low level implementation is fine.  The problem 
would appear to be in



which states

"The COLLATE operator is a unary postfix operator that assigns a collating 
sequence to an expression."

This is bad.  It says that COLLATE modifies a value.  It shouldn't.  COLLATE is 
not a value operator, it's an operator operator.  It modifies the '>', not a 
value one side of it.

It's too late to contrafit this into SQLite3, but I'm wondering whether SQLite4 
might have the collation operator rethought along these lines.  I bet it 
results in a simpler parse tree and simpler code.

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

Re: [sqlite] [Bug] sqlite3_finalize() *DOES NOT* return most recent evaluation error code

[Richard Hipp]

On Wed, Aug 21, 2013 at 8:04 AM, Dmitry Pashkevich  wrote:

> I think I found a bug, tested with SQLite v3.7.15.2 and v3.7.9.
> Looks like sqlite3_finalize() always returns SQLITE_OK, even if most recent
> execution of prepared statement failed, which contradicts the
> documentation
> :
>
> If the most recent evaluation of the statement encountered no errors or if
> > the statement is never been evaluated, then sqlite3_finalize() returns
> > SQLITE_OK. If the most recent evaluation of statement S failed, then
> > sqlite3_finalize(S) returns the appropriate error code<
> http://www.sqlite.org/c3ref/c_abort.html>
> >  or extended error code<
> http://www.sqlite.org/c3ref/c_abort_rollback.html>
> > .
> >
>
> I'm using sqlite3 via lsqlite3  Lua wrapper
> library
> (so maybe the bug is there but I can't confirm).
>

I'm unable to reproduce the problem using C.  Maybe it is in lsqlite3.

My test case:

#include 
#include "sqlite3.h"
int main(int argc, char **argv){
  sqlite3 *db;
  sqlite3_stmt *pStmt;
  int rc;
  sqlite3_open(":memory:", &db);
  sqlite3_exec(db, "create table t(x unique);", 0, 0, 0);
  sqlite3_prepare_v2(db, "insert into t(x) values(?)", -1, &pStmt, 0);
  sqlite3_bind_int(pStmt, 1, 123);
  rc = sqlite3_step(pStmt);
  printf("first step returns %d\n", rc);
  sqlite3_reset(pStmt);
  rc = sqlite3_step(pStmt);
  printf("second step returns %d\n", rc);
  printf("error message = %s\n", sqlite3_errmsg(db));
  rc = sqlite3_finalize(pStmt);
  printf("finalize returns %d\n", rc);
  sqlite3_close(db);
  return 0;
}

Output of the test program:

first step returns 101
second step returns 19
error message = column x is not unique
finalize returns 19

And this sort of thing is extensively checked in the SQLite test suites (
http://www.sqlite.org/testing.html), so it is difficult to imagine how
something like this could go unnoticed.

Hence, pending additional contrary evidence, I'm going to assume this is a
problem with the lua bindings.


>
> Here's some sample code:
>
> > require "lsqlite3"
> > db = sqlite3.open_memory()
> > =db
> sqlite database (0x238e858)
> > =db:exec("create table t(x unique)")  *-- create a table with a
> constraint
> *
> 0
> > stmt = db:prepare("insert into t(x) values(?)")
> > =stmt
> sqlite virtual machine (0x23a23e8)
> > =stmt:bind_values(123)
> 0
> > =stmt:step()
> 101
> > =sqlite3.DONE
> 101
> > =stmt:reset()
> 0
> > =stmt:step()  *-- intentionally execute statement with same values to
> violate the constraint*
> 19
> *> =sqlite3.CONSTRAINT  -- step() returns the expected error code*
> *19*
> > =db:errmsg()
> column x is not unique
> *> =stmt:finalize() -- finalize returns OK!*
> *0*
> *> =db:errmsg() -- error message is still persisted, though*
> *column x is not unique*
>
>
> Somebody please confirm this...
>
> --
> Dmitry Pashkevich
> ___
> 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] [Bug] sqlite3_finalize() *DOES NOT* return most recent evaluation error code

[Dmitry Pashkevich]

I think I found a bug, tested with SQLite v3.7.15.2 and v3.7.9.
Looks like sqlite3_finalize() always returns SQLITE_OK, even if most recent
execution of prepared statement failed, which contradicts the
documentation
:

If the most recent evaluation of the statement encountered no errors or if
> the statement is never been evaluated, then sqlite3_finalize() returns
> SQLITE_OK. If the most recent evaluation of statement S failed, then
> sqlite3_finalize(S) returns the appropriate error 
> code
>  or extended error code
> .
>

I'm using sqlite3 via lsqlite3  Lua wrapper library
(so maybe the bug is there but I can't confirm).

Here's some sample code:

> require "lsqlite3"
> db = sqlite3.open_memory()
> =db
sqlite database (0x238e858)
> =db:exec("create table t(x unique)")  *-- create a table with a constraint
*
0
> stmt = db:prepare("insert into t(x) values(?)")
> =stmt
sqlite virtual machine (0x23a23e8)
> =stmt:bind_values(123)
0
> =stmt:step()
101
> =sqlite3.DONE
101
> =stmt:reset()
0
> =stmt:step()  *-- intentionally execute statement with same values to
violate the constraint*
19
*> =sqlite3.CONSTRAINT  -- step() returns the expected error code*
*19*
> =db:errmsg()
column x is not unique
*> =stmt:finalize() -- finalize returns OK!*
*0*
*> =db:errmsg() -- error message is still persisted, though*
*column x is not unique*


Somebody please confirm this...

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

[sqlite] BETWEEN and explicit collation assignment

[Clemens Ladisch]

Hi,

the documentation says (on ):
| The expression "x BETWEEN y and z" is logically equivalent to two
| comparisons "x >= y AND x <= z" and works with respect to collating
| functions as if it were two separate comparisons.

However, this is not true when the first operator has an explicit
collation assignment:

  SQLite version 3.7.17 2013-05-20 00:56:22
  Enter ".help" for instructions
  Enter SQL statements terminated with a ";"
  sqlite> create table t(x);
  sqlite> insert into t values('a');
  sqlite> insert into t values('A');
  sqlite> select * from t where x collate nocase between 'a' and 'b';
  a
  sqlite> select * from t where x collate nocase >= 'a' and x collate nocase <= 
'b';
  a
  A

It works only on the second and third operators:

  sqlite> select * from t where x between 'a' collate nocase and 'b' collate 
nocase;
  a
  A

And adding it to the first operator breaks it again:

  sqlite> select * from t where x collate nocase between 'a' collate nocase and 
'b' collate nocase;
  a


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

Re: [sqlite] SQLite Input with validation and lookup

[Stephen Hughes]

Thought I'd sent this yesterday but found it stuck in my Outbasket
today:-(

Thanks David & Kai - off to look at Kexi and Dabo. Will let you know how
I get on.

I have now downloaded both Kexi & Dabo.
Kexi - despite stating that a Windows version is available, when I try to
download all I get is the Calligra Package without Kexi.

Dabo - I've downloaded Dabo and all the dependencies I can fathom from
the instructions, including installing Python 2.7 (I already had Python
3.2) but Dabo still refuses to work; DaboDemo.py flashes what appears to
be a black background command line screen but does nothing else. I've
Googled until I'm google-eyed to try to find out what step I've missed or
what dependencies I may have overlooked but to no avail.   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3DbMallocRaw is crashing

[Ashok Pitambar]

Hi Larry,

Thanks , I knew that there is heap corruption and this is resulting due to
that.I just wanted to
confirm  that whether sqlite is corrupting the heap here or not. I will use
valgrind and see.

Regards,
Ashok



On Wed, Aug 21, 2013 at 7:12 AM, Larry Brasfield
wrote:

> Ashok wrote:
>
>> Hi All,
>>
>> Have to come across any such issues?
>>
>
> Most experienced developers have come across such issues.  As Richard
> suggested, you are likely seeing the effect of a heap corruption that is
> occurring somewhat earlier than the "crashing" which finally reveals a
> problem.  His question, "Have you run your application using valgrind?"
> merits your attention.  If you do not yet use any tools for the early
> detection of heap corruption, you should learn to do so if you are a
> serious developer.  If you are using such a tool to help diagnose this
> particular problem, it would help others to help you if you were to say so.
>  If you cannot be troubled to use such a tool, it makes little sense for
> others to speculate as to how your not-yet-shown code is corrupting the
> heap upon which sqlite3DbMallocRaw() depends.  To be blunt, your hope that
> somebody will recognize and help cure your bug is unrealistic.  You have
> some work to do, and use of valgrind or a similar tool is the best advice
> you are likely to get that will help you do that work.
>
> Best regards,
> --
> Larry Brasfield
>
>
> __**_
> 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