Re: [sqlite] sqlite3_step to select and update the same table

[Simon Slavin]

On 8 Nov 2010, at 2:02am, cricketfan wrote:

> Simon, As per my understanding I am getting the result set and trying to
> change values in the table based on what I read from the result set up to
> that point. I see no reason why I should be stopped from updating the row I
> have already read in the result set.

Your update might change whether the row is in the result set.  Or it might 
change /where/ the row falls in the result set.  For instance, suppose you have 
the following rows:

A
B
C
D
F

You read as far as B, then decide to change the B to an E.  Now what should 
happen ?  Should that record appear again when you get past the D ?  But it 
might be a problem to include the same record twice.  How should SQL know what 
it should do ?  So SQL prevents you from making changes which effect an open 
SELECT.

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

Re: [sqlite] EXTERNAL: sqlite3_step to select and update the same table

[cricketfan]

I thought call to clear bindings and reset was working but I got confused and
am wherever I was before posting on the forum. Still have no conclusive
evidence as to why the database in my application is behaving in a weird
fashion. Any further input would be appreciated.

cricketfan wrote:
> 
> Mike I also was not able to reproduce this behavior with a small sample
> program and am puzzled by this behavior in my main application. What
> puzzled me is
> 1. Select has criteria a=?, b=?, c=? (prepare statement) - step through it
> 2. Get the value of d from database based on the above criteria
> 3. Update the value of d
> 4. The value of d has now been but the entire module within the step
> module is being re-run.
> 
> I have been able to get around this by calling sqlite3_clear_bindings and
> sqlite3_reset within the step.
> 
> 

-- 
View this message in context: 
http://old.nabble.com/sqlite3_step-to-select-and-update-the-same-table-tp30152284p30157406.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] sqlite3_step to select and update the same table

[cricketfan]

Simon, As per my understanding I am getting the result set and trying to
change values in the table based on what I read from the result set up to
that point. I see no reason why I should be stopped from updating the row I
have already read in the result set. 

Simon Slavin-3 wrote:
> 
> 
> On 7 Nov 2010, at 6:14pm, cricketfan wrote:
> I don't understand why you find this surprising.  You have two pieces of
> program: one is trying to look at something while the other is in the
> middle of changing it.  They are going to argue about which one can access
> those values.
> 
> You can complete the scan first, then make the changes you want.  Or if
> the changes depend on the scan, use an UPDATE ... WHERE command that does
> all the work for you.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/sqlite3_step-to-select-and-update-the-same-table-tp30152284p30157400.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

[sqlite] Idea for one of the testing suites

[Simon Slavin]

Several bugs I've been reading about here seem to be cases where the query 
optimizer works differently between one version of SQLite and the next.  So I 
wondered whether it would be possible for the testing suite to log the amount 
of time taken for each operation, leading to a long series of timings.  Then, 
once the build has passed all the tests, the timings for the new version could 
be compared to the timings for the previous version.  Any discrepancies 
unexpected in amount or direction could then be explored.

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

Re: [sqlite] EXTERNAL: sqlite3_step to select and update the same table

[cricketfan]

Mike I also was not able to reproduce this behavior with a small sample
program and am puzzled by this behavior in my main application. What puzzled
me is
1. Select has criteria a=?, b=?, c=? (prepare statement) - step through it
2. Get the value of d from database based on the above criteria
3. Update the value of d
4. The value of d has now been but the entire module within the step module
is being re-run.

I have been able to get around this by calling sqlite3_clear_bindings and
sqlite3_reset within the step.


Black, Michael (IS) wrote:
> 
> I think the quick answer is to use a different database handle for your
> update.  I think you may also be able to do this with WAL mode.
> http://www.sqlite.org/draft/wal.html
> I did some searching and couldn't find a definitive answer for doing an
> update inside a select loop (though I'm sure I've seen it on this list
> before).
>  
> I was unable to duplicate your problem...perhaps you can modify this to
> show it
>  
> #include 
> #include "sqlite3.h"
> main()
> {
>  sqlite3 *db;
>  sqlite3_stmt *stmt;
>  char *errmsg=NULL;
>  char *sql;
>  remove("update.db");
>  sqlite3_open("update.db",);
>  sqlite3_exec(db,"CREATE TABLE t (a int, b int)",NULL,NULL,);
>  sqlite3_exec(db,"insert into t values(1,1)",NULL,NULL,);
>  sqlite3_exec(db,"insert into t values(1,2)",NULL,NULL,);
>  sql = "SELECT * FROM t where a=1";
>  sqlite3_prepare_v2(db,sql,strlen(sql),,NULL);
>  while(sqlite3_step(stmt)==SQLITE_ROW) {
>   char sqlbuf[4096];
>   int ref;
>   ref = sqlite3_column_int(stmt,1);
>   printf("Before %d\n",ref);
>   sprintf(sqlbuf,"UPDATE t set b=b+1 where a=%d",ref);
>   puts(sqlbuf);
>  
>   sqlite3_exec(db,sqlbuf,NULL,NULL,);
>  }
>  sqlite3_finalize(stmt);
>  sqlite3_close(db);
>  
> }
>  
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Northrop Grumman Information Systems
>  
> 
> 
> 
> 

-- 
View this message in context: 
http://old.nabble.com/sqlite3_step-to-select-and-update-the-same-table-tp30152284p30156751.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

[sqlite] Query planner bug on "distinct" clause

[Alexey Pechnikov]

sqlite> .s object_record
CREATE TABLE object_record
(
  record_id INTEGER PRIMARY KEY,
  ts INTEGER NOT NULL DEFAULT (strftime('%s','now')),
  object_id INTEGER NOT NULL
);
CREATE INDEX object_id_ts_idx on object_record(object_id,ts);
CREATE INDEX object_ts_idx on object_record(ts);

The planner bug with "distinct":
sqlite> explain query plan select distinct object_id from (select
ts,object_id from object_record where rowid>1581369-1) as x;
0|0|TABLE object_record WITH INDEX object_id_ts_idx ORDER BY
CPU Time: user 0.00 sys 0.00
sqlite> select distinct object_id from (select ts,object_id from
object_record where rowid>1581369-1) as x;
31596
CPU Time: user 0.224014 sys 0.00

The plan without "distinct" is correct:
sqlite> explain query plan select object_id from (select ts,object_id from
object_record where rowid>1581369-1) as x;
0|0|TABLE object_record USING PRIMARY KEY
CPU Time: user 0.00 sys 0.00
sqlite> select object_id from (select ts,object_id from object_record where
rowid>1581369-1) as x;
31596
CPU Time: user 0.00 sys 0.00


The correct plan may be:
sqlite> drop  INDEX object_id_ts_idx;
CPU Time: user 0.024001 sys 0.00
sqlite> explain query plan select distinct object_id from (select
ts,object_id from object_record where rowid>1581369-1) as x;
0|0|TABLE object_record USING PRIMARY KEY
CPU Time: user 0.00 sys 0.00
sqlite> select distinct object_id from (select ts,object_id from
object_record where rowid>1581369-1) as x;
31596
CPU Time: user 0.00 sys 0.00

And bug again:
sqlite> CREATE INDEX object_id_idx on object_record(object_id);
CPU Time: user 4.540283 sys 0.016001
sqlite> explain query plan select distinct object_id from (select
ts,object_id from object_record where rowid>1581369-1) as x;
0|0|TABLE object_record WITH INDEX object_id_idx ORDER BY
CPU Time: user 0.00 sys 0.00
sqlite> select distinct object_id from (select ts,object_id from
object_record where rowid>1581369-1) as x;
31596
CPU Time: user 0.236015 sys 0.00


-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_step to select and update the same table

[Simon Slavin]

On 7 Nov 2010, at 6:14pm, cricketfan wrote:

> Just to make things clearer
> the value being fetched into ref from the database, is also the value being
> changed(ghi) in the update statement. When I change my query (just to debug)
> to update some other column in the table the whole thing runs fine and runs
> only once!
> Can someone throw some light on this?

I don't understand why you find this surprising.  You have two pieces of 
program: one is trying to look at something while the other is in the middle of 
changing it.  They are going to argue about which one can access those values.

You can complete the scan first, then make the changes you want.  Or if the 
changes depend on the scan, use an UPDATE ... WHERE command that does all the 
work for you.

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

Re: [sqlite] sqlite3_step to select and update the same table

[cricketfan]

Just to make things clearer
the value being fetched into ref from the database, is also the value being
changed(ghi) in the update statement. When I change my query (just to debug)
to update some other column in the table the whole thing runs fine and runs
only once!
Can someone throw some light on this?

cricketfan wrote:
> 
> rc = sqlite3_bind_text(stmt, 1, temp, (int)strlen(temp), NULL);
> while(sqlite3_step(stmt) == SQLITE_ROW) { 
>   ref = sqlite3_column_int(stmt,3);
>   delSql = sqlite3_mprintf("UPDATE table1 SET ghi = %d WHERE def = %d
> ;",1,ref);
>   if( (rc = sqlite3_exec(db, delSql, NULL, NULL, )) != SQLITE_OK )
> { sqlite3_free(zErrMsg); goto error; }
>   }
> Thanks
> 

-- 
View this message in context: 
http://old.nabble.com/sqlite3_step-to-select-and-update-the-same-table-tp30152284p30153073.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

[sqlite] Compiling SQLite on Solaris 64bit and SPARC?

[Lynton Grice]

Hi there,

 

I have just downloaded the latest version of SQLite
(sqlite-amalgamation-3.7.3.tar.gz) and am trying to compile it on my Solaris
10 64bit machine, but get an error for some reason (see below).

 

I ran a "./configure" (with no parameters) and it seemed to be fine, but
then the moment I tried to run "make install" it gave the following error
below (not too many details):

 


# make install

/bin/bash ./libtool --tag=CC --mode=link gcc -DSQLITE_THREADSAFE=1
-DSQLITE_ENA

BLE_FTS3 -DSQLITE_ENABLE_RTREE -g -O2   -o libsqlite3.la -rpath
/usr/local/lib -

no-undefined -version-info 8:6:8 sqlite3.lo

rm -fr  .libs/libsqlite3.so .libs/libsqlite3.so.0 .libs/libsqlite3.so.0.8.6

gcc -shared -Wl,-h -Wl,libsqlite3.so.0 -o .libs/libsqlite3.so.0.8.6
.libs/sqlit

e3.o  -lc

(cd .libs && rm -f libsqlite3.so.0 && ln -s libsqlite3.so.0.8.6
libsqlite3.so.0)

(cd .libs && rm -f libsqlite3.so && ln -s libsqlite3.so.0.8.6 libsqlite3.so)

false cru .libs/libsqlite3.a  sqlite3.o

make: *** [libsqlite3.la] Error 1

 

I really need to get SQLite compiled very soon on Solaris, does anyone have
any advise on how I can compile SQLite on my Solaris machine? Any help will
be greatly appreciated ;-)

 

Thanks

 

Lynton

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

Re: [sqlite] INSERT OR IGNORE with rtree virtual tables

[Peter Kolbus]

Any thoughts on this?

Thanks,
Peter Kolbus

On Sat, Oct 30, 2010 at 8:27 PM, Peter Kolbus  wrote:
> Hi,
>
> It seems that the OR IGNORE clause is not working with an RTREE
> virtual table.  The documentation (http://sqlite.org/rtree.html,
> section 3.2) implies that this should work, but is not absolutely
> clear on the point of conflict handling.  I've tried this with both
> 3.6.19 and 3.7.3 with the same result.
>
> In the shell:
>
> SQLite version 3.7.3
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> CREATE VIRTUAL TABLE a_rt USING rtree( _id, min_x, max_x,
> min_y, max_y );
> sqlite> INSERT OR IGNORE INTO a_rt ( _id, min_x, max_x, min_y, max_y )
> VALUES( 2, 3, 4, 5, 6 );
> sqlite> INSERT OR IGNORE INTO a_rt ( _id, min_x, max_x, min_y, max_y )
> VALUES( 2, 3, 4, 5, 6 );
> Error: constraint failed
> sqlite>
>
> Is this a bug, or operating as designed?
>
> Thanks,
> Peter Kolbus
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_step to select and update the same table

[Kees Nuyt]

On Sat, 6 Nov 2010 21:29:58 -0700 (PDT), cricketfan
 wrote:

>I am trying to select some columns from a table and use that to
>update another column in the same table using prepare/step/reset/finalize
>methods. However, when I use the update statement while stepping it is
>executing the flow 2 times. 

There is no need to do this in a loop with a cursor.
Whenever possible, use the power of SQL set operations.
As far as I can tell, an alternative solution to
your problem could be (pseudocode):

delSql = "UPDATE table1
   SET ghi = ?
 WHERE def IN (
SELECT ref 
  FROM table1
 WHERE abc = ?
);";
prepare(db, delSql, ..., stmt, ...);
bind_int(stmt, ..., ghivalue);
bind_text(stmt, ..., abcvalue);
step(stmt);
reset(stmt);
finalize(stmt);

Hope this helps.
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_step to select and update the same table

[Simon Slavin]

On 7 Nov 2010, at 4:29am, cricketfan wrote:

>I am trying to select some columns from a table and use that to
> update another column in the same table using prepare/step/reset/finalize
> methods. However, when I use the update statement while stepping it is
> executing the flow 2 times.

You can't UPDATE a table while you're in the middle of stepping through a 
SELECT.  Because the UPDATE might change one of the things that governs the 
results of the SELECT command and cause it to repeat or skip records.  The 
neatest way to do what you want is to first perform the SELECT, read all the 
rows into an array, and finalize it.  Then perform any UPDATE you need.

An alternative would be to address the database as if you're using two 
different processes with two different database handles.  But depending on how 
you're accessing the database this may merely delay your UDPATE thread until 
the SELECT thread is finished.

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

Re: [sqlite] EXTERNAL: sqlite3_step to select and update the same table

[Black, Michael (IS)]

I think the quick answer is to use a different database handle for your update. 
 I think you may also be able to do this with WAL mode.
http://www.sqlite.org/draft/wal.html
I did some searching and couldn't find a definitive answer for doing an update 
inside a select loop (though I'm sure I've seen it on this list before).
 
I was unable to duplicate your problem...perhaps you can modify this to show it
 
#include 
#include "sqlite3.h"
main()
{
 sqlite3 *db;
 sqlite3_stmt *stmt;
 char *errmsg=NULL;
 char *sql;
 remove("update.db");
 sqlite3_open("update.db",);
 sqlite3_exec(db,"CREATE TABLE t (a int, b int)",NULL,NULL,);
 sqlite3_exec(db,"insert into t values(1,1)",NULL,NULL,);
 sqlite3_exec(db,"insert into t values(1,2)",NULL,NULL,);
 sql = "SELECT * FROM t where a=1";
 sqlite3_prepare_v2(db,sql,strlen(sql),,NULL);
 while(sqlite3_step(stmt)==SQLITE_ROW) {
  char sqlbuf[4096];
  int ref;
  ref = sqlite3_column_int(stmt,1);
  printf("Before %d\n",ref);
  sprintf(sqlbuf,"UPDATE t set b=b+1 where a=%d",ref);
  puts(sqlbuf);
 
  sqlite3_exec(db,sqlbuf,NULL,NULL,);
 }
 sqlite3_finalize(stmt);
 sqlite3_close(db);
 
}
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of cricketfan
Sent: Sat 11/6/2010 11:29 PM
To: sqlite-users@sqlite.org
Subject: EXTERNAL:[sqlite] sqlite3_step to select and update the same table




Hello,
I am trying to select some columns from a table and use that to
update another column in the same table using prepare/step/reset/finalize
methods. However, when I use the update statement while stepping it is
executing the flow 2 times. In order to clarify, I am pasting some pseudo
code

zSql = sqlite3_mprintf("SELECT * FROM table1 WHERE abc=? ;");
if( (rc = sqlite3_prepare_v2(db, zSql, (int)strlen(zSql), , NULL)) !=
SQLITE_OK ) {
fprintf(stderr, "SQL error: preparing update statement\n");
goto error;
}
rc = sqlite3_bind_text(stmt, 1, temp, (int)strlen(temp), NULL);
if( rc != SQLITE_OK ) { goto error;}
/* Execute the query expression */
while(sqlite3_step(stmt) == SQLITE_ROW) {
ref = sqlite3_column_int(stmt,3);
printf("Before sql execution %d \n",ref);
delSql = sqlite3_mprintf("UPDATE table1 SET ghi = %d WHERE def = %d
;",1,ref);
if( (rc = sqlite3_exec(db, delSql, NULL, NULL, )) != SQLITE_OK 
) {
sqlite3_free(zErrMsg); goto error; }
}
sqlite3_reset(stmt);
sqlite3_finalize(stmt);
Output :
Before sql execution 5
Before sql execution 5
Before sql execution 6
Before sql execution 6

As you can see the print statement is being printed 2 times and this code is
not in a loop, apart from the while for stepping. If I replace the update
statement with another select statement I behaves normally i.e. just
executes the query once, prints just once.

Do I have to do something special If I want to do an update on the table I
am selecting from? I am new to SQLITE hope someone can help me resolve my
newbie problem.

Thanks
--
View this message in context: 
http://old.nabble.com/sqlite3_step-to-select-and-update-the-same-table-tp30152284p30152284.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


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

Re: [sqlite] Question about manifest typing/data affinity

[Tito Ciuro]

Hello everyone,

Sorry about my last email... I clicked Send too quickly.

Jay, the book is great, I have discovered quite a few details I had overlooked 
(or perhaps missed, since I worked with earlier versions of SQLite and some 
current features were not available yet).

Thank you and all who responded. It's definitely more clear to me how this 
works... excellent.

Best regards,

-- Tito

On 06/11/2010, at 21:28, Jay A. Kreibich wrote:

> On Sat, Nov 06, 2010 at 08:35:10PM -0300, Tito Ciuro scratched on the wall:
>> Hello,
>> 
>> I have a question about manifest typing/data affinity. Assume I have 
>> created this table:
>> 
>> CREATE TABLE foo (ROWID INTEGER PRIMARY KEY, key TEXT, attr TEXT, value 
>> NONE);
>> 
>> I was reading the Using SQLite book and came across page #38 (#60 on
>> the PDF version) where it states:
> 
> 
>  I hope the book is proving useful.
> 
> 
>> "None: A column with a none affinity has no preference over storage
>> class.  Each value is stored as the type provided, with no attempt
>> to convert anything."
>> 
>> So it seems that using NONE is a hint that different data types are
>> going to be stored in that column, correct?
> 
>  I suppose you could look at it that way, but it isn't meant to be
>  explicit.
> 
>  With the exception of an INTEGER PRIMARY KEY, any column can hold
>  row-values of any type.  The affinity will influence stored values
>  to specific types, but it does not prevent other types from being
>  stored if the conversion cannot be made.
> 
>  The NONE affinity simply indicates to the database that you always
>  store values in their provided type, and that no conversions should
>  be attempted, even if a conversion could be done in a loss-less way.
> 
>> My main question has to do with binding values to precompiled statements.
>> For the value column, should I:
>> 
>> a) use sqlite3_bind_value()?
> 
>  sqlite3_bind_value() is for binding "sqlite3_value" data structures.
>  If you have one of those (from an sqlite3_column_value(), for
>  example), and it is in the type representation you want (text,
>  int, etc), then go ahead and use it.
> 
>  If you're binding a more traditional value, such as a string or
>  integer, you should use one of the sqlite3_bind_text(),
>  sqlite3_bind_int(), or similar functions.
> 
>  The bind function you choose will set the "type" of the value.
>  If the column has a NONE affinity, no conversion will be attempted
>  and the value, in the representation you provided, will be stored
>  directly.
> 
>> b) store it as a string using sqlite3_bind_text()? Will
>> sqlite3_bind_text() allow SQLite to choose the proper data affinity
>> even though I'm binding it as text?
> 
>  An affinity is a property of a table column.  The affinity of a
>  column is defined by the "SQL type" passed to CREATE TABLE.  Once the
>  table has been created, the affinity of each column is set.  So the
>  only time SQLite "chooses" an affinity is when it parses the CREATE
>  TABLE statement.
> 
>  So, for example, if you have an INSERT statement that binds a text
>  value to a parameter used to set a column with a NONE affinity, the
>  value in the new row will *always* be stored as a text value,
>  regardless of the string value.  If you were to bind an integer, the
>  value would always be stored as an integer, and so on.
> 
>  This would work differently if the column had a NUMERIC affinity, for
>  example.  In that case, binding the string "abc" would result in
>  storage of a text value, while binding the string "132" would result
>  in an integer and "85.3" would result in a floating-point.  Binding
>  43 (the integer, not a two-character string, using sqlite3_bind_int())
>  would result in an integer as well.  
> 
> 
> 
>  As you might know, you can use different sqlite3_column_xxx() functions
>  to extract values in a specific representation (type), even if that 
>  returned representation does not match the type of the stored value.
>  For example, if you know you're going to print out a value, you can
>  use sqlite3_column_text() to get a text representation of the value,
>  even if that value is stored in the database as an integer.  The
>  conversions used for this are given in table 7.1 of Using SQLite.
> 
>  On the input side, you can use different sqlite3_bind_xxx() function
>  to provide values in a representation (type) that might not match the
>  type used to actually store the value.  The affinity defines your
>  "preferred" storage type, and is used as a hint to do input
>  conversions.  So if you're taking use input for a number, you can
>  take the text value passed in by your applications GUI and pass that
>  directly to sqlite3_bind_text(), even if the string represents a
>  number, and you want to store it as a number (and have told the
>  database this by using an INTEGER, REAL, or NUMERIC affinity).
> 
>  The NONE affinity is simply a way of saying that you will always
>  provide values in the representation that should be 

Re: [sqlite] Question about manifest typing/data affinity

[Tito Ciuro]

On 06/11/2010, at 21:28, Jay A. Kreibich wrote:

> On Sat, Nov 06, 2010 at 08:35:10PM -0300, Tito Ciuro scratched on the wall:
>> Hello,
>> 
>> I have a question about manifest typing/data affinity. Assume I have 
>> created this table:
>> 
>> CREATE TABLE foo (ROWID INTEGER PRIMARY KEY, key TEXT, attr TEXT, value 
>> NONE);
>> 
>> I was reading the Using SQLite book and came across page #38 (#60 on
>> the PDF version) where it states:
> 
> 
>  I hope the book is proving useful.
> 
> 
>> "None: A column with a none affinity has no preference over storage
>> class.  Each value is stored as the type provided, with no attempt
>> to convert anything."
>> 
>> So it seems that using NONE is a hint that different data types are
>> going to be stored in that column, correct?
> 
>  I suppose you could look at it that way, but it isn't meant to be
>  explicit.
> 
>  With the exception of an INTEGER PRIMARY KEY, any column can hold
>  row-values of any type.  The affinity will influence stored values
>  to specific types, but it does not prevent other types from being
>  stored if the conversion cannot be made.
> 
>  The NONE affinity simply indicates to the database that you always
>  store values in their provided type, and that no conversions should
>  be attempted, even if a conversion could be done in a loss-less way.
> 
>> My main question has to do with binding values to precompiled statements.
>> For the value column, should I:
>> 
>> a) use sqlite3_bind_value()?
> 
>  sqlite3_bind_value() is for binding "sqlite3_value" data structures.
>  If you have one of those (from an sqlite3_column_value(), for
>  example), and it is in the type representation you want (text,
>  int, etc), then go ahead and use it.
> 
>  If you're binding a more traditional value, such as a string or
>  integer, you should use one of the sqlite3_bind_text(),
>  sqlite3_bind_int(), or similar functions.
> 
>  The bind function you choose will set the "type" of the value.
>  If the column has a NONE affinity, no conversion will be attempted
>  and the value, in the representation you provided, will be stored
>  directly.
> 
>> b) store it as a string using sqlite3_bind_text()? Will
>> sqlite3_bind_text() allow SQLite to choose the proper data affinity
>> even though I'm binding it as text?
> 
>  An affinity is a property of a table column.  The affinity of a
>  column is defined by the "SQL type" passed to CREATE TABLE.  Once the
>  table has been created, the affinity of each column is set.  So the
>  only time SQLite "chooses" an affinity is when it parses the CREATE
>  TABLE statement.
> 
>  So, for example, if you have an INSERT statement that binds a text
>  value to a parameter used to set a column with a NONE affinity, the
>  value in the new row will *always* be stored as a text value,
>  regardless of the string value.  If you were to bind an integer, the
>  value would always be stored as an integer, and so on.
> 
>  This would work differently if the column had a NUMERIC affinity, for
>  example.  In that case, binding the string "abc" would result in
>  storage of a text value, while binding the string "132" would result
>  in an integer and "85.3" would result in a floating-point.  Binding
>  43 (the integer, not a two-character string, using sqlite3_bind_int())
>  would result in an integer as well.  
> 
> 
> 
>  As you might know, you can use different sqlite3_column_xxx() functions
>  to extract values in a specific representation (type), even if that 
>  returned representation does not match the type of the stored value.
>  For example, if you know you're going to print out a value, you can
>  use sqlite3_column_text() to get a text representation of the value,
>  even if that value is stored in the database as an integer.  The
>  conversions used for this are given in table 7.1 of Using SQLite.
> 
>  On the input side, you can use different sqlite3_bind_xxx() function
>  to provide values in a representation (type) that might not match the
>  type used to actually store the value.  The affinity defines your
>  "preferred" storage type, and is used as a hint to do input
>  conversions.  So if you're taking use input for a number, you can
>  take the text value passed in by your applications GUI and pass that
>  directly to sqlite3_bind_text(), even if the string represents a
>  number, and you want to store it as a number (and have told the
>  database this by using an INTEGER, REAL, or NUMERIC affinity).
> 
>  The NONE affinity is simply a way of saying that you will always
>  provide values in the representation that should be used to
>  store them.  This means that the specific sqlite3_bind_xxx() function
>  you choose more or less sets the type of the value.
> 
>-j
> 
> -- 
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
> 
> "Intelligence is like underwear: it is important that you have it,
> but showing it to the wrong people has the tendency to make them
> feel uncomfortable." -- Angela Johnson
> 

Re: [sqlite] sqlite.exe db_name .dump : It missed : table name is un quoted.(INSERT statement)

[ぽぽんＧＭ]

sqlite-2.8.17-47fee16ba9bd8ab2.diff
--- SQLite-47fee16ba9bd8ab2/src/shell.c
+++ SQLite-47fee16ba9bd8ab2/src/shell.c
@@ -421,7 +421,7 @@
p->zDestTable = 0;
}
if( zName==0 ) return;
- needQuote = !isalpha(*zName) && *zName!='_';
+ needQuote = *zName!='_';
for(i=n=0; zName[i]; i++, n++){
if( !isalnum(zName[i]) && zName[i]!='_' ){
needQuote = 1;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users