Re: [sqlite] Read-only media

2012-09-06 Thread Baruch Burstein 
On Thu, Sep 6, 2012 at 5:26 PM, Simon Slavin  wrote:

>
> On 6 Sep 2012, at 3:13pm, Richard Hipp  wrote:
>
> > If the last writer to the database file crashed and left a hot
> > journalthen the
> > next reader to come along must rollback that journal before it can
> > start reading, and that will require write access.  We have encountered
> > cases where companies accidently publish a gazillion copies of a CDROM
> that
> > contains a hot journal.  The database on such CDROMs are unreadable.
>
> Ouch.  Yeah, I guess that's going to happen occasionally.
>
Not that I think this is necessarily a good idea, but maybe if opening a
database with a hot journal from read-only media (or with _READONLY), the
database pages in the journal can be loaded into the page cache and marked
as never_remove_from_cache or some such, effectively giving read-only
access to the database (I think this would work, but might not be a good
idea. But that company would have saved their reputation if this existed)

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] C++ - WHERE clause - update

2012-09-06 Thread Arbol One 
Is this a tricky question?
int sqlite3_step(sqlite3_stmt*);

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rob Richardson
Sent: Thursday, September 06, 2012 12:21 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] C++ - WHERE clause - update

What is the value returned from sqlite3_step()?

RobR

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Arbol One
Sent: Thursday, September 06, 2012 12:14 PM
To: 'General Discussion of SQLite Database'
Subject: [sqlite] C++ - WHERE clause - update

rc = sqlite3_step(mystmt);
if(rc == SQLITE_ROW ) {
The code, in this case, does not process this statement!!?? <<--
apstr = (const char*)sqlite3_column_text(mystmt,pos);
std::cout << apstr << std::endl;  //<<-- this is not executed }
Table:
id | tile | fname | mname | lname |
---
___
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] How to determine the effective cache size

2012-09-06 Thread Roger Binns 
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 28/08/12 12:48, Foster, Kristina (CIV) wrote:
> Thank you for the clarification.  I was wondering if I missed something
> with the command line or Python interface.  I will stay tuned for the
> sqlite3_db_status method in future releases.

A new APSW version has been released including sqlite3_db_status.

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

iEYEARECAAYFAlBJN7AACgkQmOOfHg372QQuwACgwSOcRPZKFnrGDozxCIHZIq6v
nmsAoJ+JtTTSdcx812Gz22a2FP6/e1S2
=Rmx6
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] bus errror on sparc machines

2012-09-06 Thread Richard Hipp 
On Thu, Sep 6, 2012 at 1:02 PM, Tal Tabakman  wrote:

>  Hi Guys,
>  I get a bus error while running a sqlite based app on sparc machine (64
> bits mode)
>
>  here is my stack trace ,
>
>  74ab2dec  sqlite3_set_authorizer  + 505c
>  74adb8cc  sqlite3_vtab_config  + 99c0
>  74adc184  sqlite3_vtab_config  + a278
>  74add0f4  sqlite3_vtab_config  + b1e8
>  74ac536c  sqlite3_reset_auto_extension  + 38b4
>  74ac5628  sqlite3_reset_auto_extension  + 3b70
>  74ac5734  sqlite3_prepare  + 1c
>  74ac0f3c  sqlite3_exec  + b4
>

That stack looks impossible to me.  Are you sure you have the right symbol
file and that the stack is not corrupt.

Can you compile with -g and run the app in gdb until it crashes, so that we
can poke around and get better information?

FWIW, we do 100% branch coverage testing on sparc64 prior to every
release.  See http://www.sqlite.org/checklists/3071400#c12 for example.


>
> any idea what causes that ?
> cheers
> Tal
> ___
> 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] bus errror on sparc machines

2012-09-06 Thread Tal Tabakman 
 Hi Guys,
 I get a bus error while running a sqlite based app on sparc machine (64
bits mode)

 here is my stack trace ,

 74ab2dec  sqlite3_set_authorizer  + 505c
 74adb8cc  sqlite3_vtab_config  + 99c0
 74adc184  sqlite3_vtab_config  + a278
 74add0f4  sqlite3_vtab_config  + b1e8
 74ac536c  sqlite3_reset_auto_extension  + 38b4
 74ac5628  sqlite3_reset_auto_extension  + 3b70
 74ac5734  sqlite3_prepare  + 1c
 74ac0f3c  sqlite3_exec  + b4

any idea what causes that ?
cheers
Tal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] offset from matchinfo

2012-09-06 Thread E. Timothy Uy 
Hi, it doesn't look like it is possible to get offset information from
matchinfo. Is that right? It seems like matchinfo should be the uber set of
data for matching. Sometimes early occurrence of a match may mean more
significance.

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

Re: [sqlite] EXT : C++ - WHERE clause - update

2012-09-06 Thread Black, Michael (IS) 
You need to :

cout << this->SQLStatement.c_str() << endl;

Then put that SQL into the sqlite3 shell against your database and ensure you 
actually get rows back.

You also need to be sure you're looking at the same database.  Many times 
people have multiple copies and the one the program uses is not the same as 
what they think.

Is your database path complete or relative when you open the database file?

Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Arbol One [arbol...@gmail.com]
Sent: Thursday, September 06, 2012 11:14 AM
To: 'Gen eral Discussion of SQLite Database'
Subject: EXT :[sqlite] C++ - WHERE clause - update

rc = sqlite3_step(mystmt);
if(rc == SQLITE_ROW ) {
The code, in this case, does not process this statement!!?? <<--
apstr = (const char*)sqlite3_column_text(mystmt,pos);
std::cout << apstr << std::endl;  //<<-- this is not executed
}
Table:
id | tile | fname | mname | lname |
---


void ClassforSQLite3::getList(
const Glib::ustring& db_name, // Database name
const Glib::ustring& token) // Key word to search in the database
throw(jme::Exception) {
Glib::ustring apstr;
Glib::ustring sName;
int apint;
mySQLite3* db;
try {
db = new mySQLite3(db_name.c_str());
} catch(somexception& e) {
...
}

   // SQL statement
apstr = "SELECT fname FROM ";
apstr += this->db_table_name;
apstr += " WHERE title = \'";
apstr += token;
apstr += "\' ";
apint = 1;
db->setStmt(apstr);
sName = db->read_str(apint); // here is where the problem is see the method 
below



const Glib::ustring& mySQLite3::read_str(const int pos)
throw(jme::Exception) {

rc = sqlite3_prepare_v2(db, this->SQLStatement.c_str(), -1, , NULL);
if(rc != SQLITE_OK) {
// do something
}
rc = sqlite3_step(mystmt);
if(rc == SQLITE_ROW ) {
// The code, in this case, does not process this statement!!?? <<--
apstr = (const char*)sqlite3_column_text(mystmt,pos);
}
try {
this->finalize();
} catch(somexception& e) {
throw e;
}
return apstr;
}

What am I doing wrong?

___
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] C++ - WHERE clause - update

2012-09-06 Thread Igor Tandetnik 

On 9/6/2012 12:14 PM, Arbol One wrote:

rc = sqlite3_step(mystmt);
if(rc == SQLITE_ROW ) {
The code, in this case, does not process this statement!!??


It's possible that no row actually matches the condition, so 
sqlite3_step returns SQLITE_DONE on the first call.

--
Igor Tandetnik

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

Re: [sqlite] C++ - WHERE clause - update

2012-09-06 Thread Rob Richardson 
What is the value returned from sqlite3_step()?

RobR

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Arbol One
Sent: Thursday, September 06, 2012 12:14 PM
To: 'General Discussion of SQLite Database'
Subject: [sqlite] C++ - WHERE clause - update

rc = sqlite3_step(mystmt);
if(rc == SQLITE_ROW ) {
The code, in this case, does not process this statement!!?? <<--
apstr = (const char*)sqlite3_column_text(mystmt,pos);
std::cout << apstr << std::endl;  //<<-- this is not executed 
}
Table:
id | tile | fname | mname | lname |
--- 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] C++ - WHERE clause - update

2012-09-06 Thread Arbol One 
rc = sqlite3_step(mystmt);
if(rc == SQLITE_ROW ) {
The code, in this case, does not process this statement!!?? <<--
apstr = (const char*)sqlite3_column_text(mystmt,pos);
std::cout << apstr << std::endl;  //<<-- this is not executed 
}
Table:
id | tile | fname | mname | lname |
--- 


void ClassforSQLite3::getList(
const Glib::ustring& db_name, // Database name
const Glib::ustring& token) // Key word to search in the database
throw(jme::Exception) {
Glib::ustring apstr;
Glib::ustring sName;
int apint;
mySQLite3* db; 
try {
db = new mySQLite3(db_name.c_str());
} catch(somexception& e) {
...
}

   // SQL statement
apstr = "SELECT fname FROM ";
apstr += this->db_table_name;
apstr += " WHERE title = \'";
apstr += token;
apstr += "\' ";
apint = 1;
db->setStmt(apstr);
sName = db->read_str(apint); // here is where the problem is see the method 
below



const Glib::ustring& mySQLite3::read_str(const int pos)
throw(jme::Exception) {

rc = sqlite3_prepare_v2(db, this->SQLStatement.c_str(), -1, , NULL);
if(rc != SQLITE_OK) { 
// do something
}
rc = sqlite3_step(mystmt);
if(rc == SQLITE_ROW ) {
// The code, in this case, does not process this statement!!?? <<--
apstr = (const char*)sqlite3_column_text(mystmt,pos);
}
try {
this->finalize();
} catch(somexception& e) {
throw e;
}
return apstr;
}

What am I doing wrong?

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

Re: [sqlite] Read-only media

2012-09-06 Thread Simon Slavin 

On 6 Sep 2012, at 3:40pm, Richard Hipp  wrote:

> When SQLITE_OPEN_READONLY is used, then the file might as well be on CDROM
> as far as SQLite is concerned.  It won't write to it.  Even to rollback a
> hot journal.

Thanks.  That makes sense.

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

Re: [sqlite] classic update join question

2012-09-06 Thread Jim Morris 
This analysis is a good candidate for inclusion in a FAQ or similar 
document.


On 9/5/2012 7:28 PM, Keith Medcalf wrote:

sqlite> create table alpha (frequency, term);
sqlite> create table beta (term, frequency);
sqlite> create index betaterm on beta(term);
sqlite> .explain
sqlite> explain query plan update alpha set frequency = (select frequency from 
beta where beta.term >= alpha.term);
sele  order  from  deta
  -    
0 0  0 SCAN TABLE alpha (~100 rows)
0 0  0 EXECUTE CORRELATED SCALAR SUBQUERY 0
0 0  0 SEARCH TABLE beta USING INDEX betaterm (term>?) 
(~25 rows)
sqlite> explain update alpha set frequency = (select frequency from beta where 
beta.term >= alpha.term);
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 000
1 Goto   0 38000
2 Null   0 1 200
3 OpenRead   0 2 0 2  00
4 Rewind 0 8 000
5 Rowid  0 2 000
6 RowSetAdd  1 2 000
7 Next   0 5 001
8 Close  0 0 000
9 OpenWrite  0 2 0 2  00
10RowSetRead 1 36200
11NotExists  0 10200
12Null   0 3 400
13Null   0 5 000
14Integer1 6 000
15OpenRead   1 3 0 2  00
16OpenRead   2 4 0 keyinfo(1,BINARY)  00
17Column 0 1 700
18IsNull 7 28000
19SeekGe 2 287 1  00
20Column 2 0 800
21IsNull 8 27000
22IdxRowid   2 8 000
23Seek   1 8 000
24Column 1 1 900
25Move   9 5 100
26IfZero 6 28-1   00
27Next   2 20000
28Close  1 0 000
29Close  2 0 000
30SCopy  5 3 000
31Column 0 1 400
32NotExists  0 33200
33MakeRecord 3 2 8 bb 00
34Insert 0 8 2 alpha  05
35Goto   0 10000
36Close  0 0 000
37Halt   0 0 000
38Transaction0 1 000
39VerifyCookie   0 3 000
40TableLock  0 2 1 alpha  00
41TableLock  0 3 0 beta   00
42Goto   0 2 000
sqlite>

sqlite> explain query plan select alpha.term, beta.frequency from alpha, beta 
where beta.term >= alpha.term;
SELECT item[0] = {0:1}
item[1] = {1:1}
FROM {0,*} = alpha
  {1,*} = beta
WHERE GE({1:0},{0:1})
END
sele  order  from  deta
  -    
0 0  0 SCAN TABLE alpha (~100 rows)
0 1  1 SEARCH TABLE beta USING INDEX betaterm (term>?) 
(~25 rows)
sqlite> explain select alpha.term, beta.frequency from alpha, beta where beta.term 
>= alpha.term;
SELECT item[0] = {0:1}
item[1] = {1:1}
FROM {0,*} = alpha
  {1,*} = beta
WHERE GE({1:0},{0:1})
END
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 000
1 Goto   0 22000
2 OpenRead   0 2 0 2  00
3 OpenRead   1 3 0 2  00
4 OpenRead   2 4 0 keyinfo(1,BINARY)  00
5 Rewind 0 18000
6 Column 0 1 100
7 IsNull 1 17000
8 SeekGe 2 171 1  00
9 Column 2 0 200
10IsNull 2 16000
11IdxRowid   2 2 000
12Seek   1 2 000
13Column 0 1 300
14Column 1 1

Re: [sqlite] Read-only media

2012-09-06 Thread Richard Hipp 
On Thu, Sep 6, 2012 at 10:26 AM, Simon Slavin  wrote:

>
> On 6 Sep 2012, at 3:13pm, Richard Hipp  wrote:
>
> Suppose you open a database in read-only mode (using SQLITE_OPEN_READONLY)
> and SQLite finds a hot journal for it.  Is tidying up the hot journal done
> even in _READONLY, or do you immediately get a result indicating an error,
> or do you get an error result later ?
>

When SQLITE_OPEN_READONLY is used, then the file might as well be on CDROM
as far as SQLite is concerned.  It won't write to it.  Even to rollback a
hot journal.

-- 
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] Read-only media

2012-09-06 Thread Simon Slavin 

On 6 Sep 2012, at 3:13pm, Richard Hipp  wrote:

> If the last writer to the database file crashed and left a hot
> journalthen the
> next reader to come along must rollback that journal before it can
> start reading, and that will require write access.  We have encountered
> cases where companies accidently publish a gazillion copies of a CDROM that
> contains a hot journal.  The database on such CDROMs are unreadable.

Ouch.  Yeah, I guess that's going to happen occasionally.

Suppose you open a database in read-only mode (using SQLITE_OPEN_READONLY) and 
SQLite finds a hot journal for it.  Is tidying up the hot journal done even in 
_READONLY, or do you immediately get a result indicating an error, or do you 
get an error result later ?

(I could test this on my Mac, but I don't know whether the results are 
documented and cross-platform or the result of my particular setup.)

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

Re: [sqlite] Read-only media

2012-09-06 Thread Richard Hipp 
On Thu, Sep 6, 2012 at 9:39 AM, Baruch Burstein wrote:

> Can sqlite databases be read from a read-only media? I seem to remember
> seeing something about this on the website, but can't find it.
>

Usually.

If the last writer to the database file crashed and left a hot
journalthen the
next reader to come along must rollback that journal before it can
start reading, and that will require write access.  We have encountered
cases where companies accidently publish a gazillion copies of a CDROM that
contains a hot journal.  The database on such CDROMs are unreadable.

In WAL mode, readers usually need access to the -shm file.  And they
certainly need to be able to create the -shm file if it doesn't already
exist.  So make sure the databases you put onto read-only media (ex: CDROM)
are in rollback mode.



>
> --
> ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
> ___
> 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] Read-only media

2012-09-06 Thread Keith Medcalf 

> Can sqlite databases be read from a read-only media? I seem to remember
> seeing something about this on the website, but can't find it.

Adding on to Michael's reply, you may need to ensure that you have a writeable 
location for the temp_store depending on what queries you issue.

http://www.sqlite.org/pragma.html#pragma_temp_store
http://www.sqlite.org/pragma.html#pragma_temp_store_directory

---
()  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] Read-only media

2012-09-06 Thread Black, Michael (IS) 
'twould appear so...the shell even knows about a read-only database.

$ sqlite3 test.db
SQLite version 3.7.9 2011-11-01 00:52:41
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table test(a,b);
sqlite> insert into table values(1,2);
Error: near "table": syntax error
sqlite> insert into test values(1,2);
sqlite> insert into test values(3,4);
sqlite> select * from test;
1|2
3|4
sqlite> .quit
$ chmod a-w .
$ !sq
sqlite3 test.db
SQLite version 3.7.9 2011-11-01 00:52:41
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select * from test;
1|2
3|4
sqlite> .quit

$ chmod -w test.db
$ !sq
sqlite3 test.db
SQLite version 3.7.9 2011-11-01 00:52:41
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select * from test;
1|2
3|4
sqlite> insert into test values(5,6);
Error: attempt to write a readonly database


Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Baruch Burstein [bmburst...@gmail.com]
Sent: Thursday, September 06, 2012 8:39 AM
To: General Discussion of SQLite Database
Subject: EXT :[sqlite] Read-only media

Can sqlite databases be read from a read-only media? I seem to remember
seeing something about this on the website, but can't find it.

--
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
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] Read-only media

2012-09-06 Thread Baruch Burstein 
Can sqlite databases be read from a read-only media? I seem to remember
seeing something about this on the website, but can't find it.

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] C++ - WHERE clause

2012-09-06 Thread Black, Michael (IS) 
Yeah -- I should've been in a better teaching mode

Trying to keep things simple opens up these type of security problemsthough 
there are lots of situations where this works just fine and is no problem at 
all (e.g. when you don't have user input or it's completely under your own 
control like a learning program).

It's good to get in the habit of not doing it the simple and possibly insecure 
way.


Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Richard Hipp 

Bad idea.  See SQL Injection
Attackfor a detailed
explanation of why this is so bad.

A far better approach is to use query parameters with sqlite3_bind_().
Or, failing that, to use sqlite3_mprintf() with the %q or %Q substitutions.


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

Re: [sqlite] classic update join question

2012-09-06 Thread Rob Richardson 
Many thanks to all of you who took the time to correct my misunderstanding of 
basic SQL.

I ran a little test in PostgreSQL (which is the quickest thing I have to play 
with), and of course, you are all correct and the query does work as designed.  
I was trying to figure out how to think about it, and I think I understand now. 
  In general, the database engine will walk through the target table,  evaluate 
the right side of the equal sign, and assign the result to the target column 
specified in the left side.  Simple.  I don't know why it didn't seem so simple 
yesterday.

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

Re: [sqlite] C++ - WHERE clause

2012-09-06 Thread Richard Hipp 
On Thu, Sep 6, 2012 at 7:52 AM, Black, Michael (IS)
wrote:

> And, when you have problems, you should always examine your SQL by running
> the EXACT same string you generate in your program through the sqlite3
> shell.  This will help you to figure out if your SQL is wrong or your C++
> is wrong.
>
> For example even just your SELECT portion generates the wrong SQL.
>
> You end up with this (I'm already assuming you get rid of the your last +=
> of " (n_id,..." as you don't need it for a SELECT statement as already
> pointed out.
>
> SELECT fname FROM table WHERE title = token
> And what you want is
> SELECT fname FROM table WHERE title = 'token'
> So you need to add a single quote on both sides of adding your token.
> apstr += "'";
>

Bad idea.  See SQL Injection
Attackfor a detailed
explanation of why this is so bad.

A far better approach is to use query parameters with sqlite3_bind_().
Or, failing that, to use sqlite3_mprintf() with the %q or %Q substitutions.



>
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Advanced GEOINT Solutions Operating Unit
> Northrop Grumman Information Systems
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> on behalf of Baruch Burstein [bmburst...@gmail.com]
> Sent: Thursday, September 06, 2012 3:45 AM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] C++ - WHERE clause
>
> VALUES is used for INSERTing into a table, not for SELECTing. This is not
> valid SQL (I would help you fix it, but I can't figure out what you were
> trying to achieve.)
> Here is a great reference: http://sqlite.org/lang_select.html
>
> On Thu, Sep 6, 2012 at 11:18 AM, Arbol One  wrote:
>
> > As many of you know, I am trying to learn SQL using C++.
> >
> > Below is an error I get when I try using the C++ example below it.
> >
> >
> >
> > Error Code: 1
> >
> > Error Message: near "VALUES": syntax error
> >
> > 
> >
> >
> >
> > Glib::ustring apstr;
> >
> > Glib::ustring sName;
> >
> > int apint;
> >
> > mySQLite3* db;
> >
> > try {
> >
> > db = new mySQLite3(db_name.c_str());
> >
> > } catch(somexception& e) {
> >
> > //do something
> >
> > }
> >
> >
> >
> >// SQL statement
> >
> > apstr = "SELECT fname FROM ";
> >
> > apstr += this->db_table_name;
> >
> > apstr += " WHERE title = ";
> >
> > apstr += token;
> >
> > apstr += " (n_id, title, fname, mname, lname) VALUES (?, ?, ?, ?,
> ?)";
> > // here is where the problem is see the method below
> >
> > apint = 1;
> >
> > db->setStmt(apstr);
> >
> > sName = db->read_str(apint);
> >
> >
> >
> > 
> >
> > const Glib::ustring& mySQLite3::read_str(const int pos)
> >
> > throw(jme::Exception) {
> >
> >
> >
> > rc = sqlite3_prepare_v2(db, this->SQLStatement.c_str(), -1, ,
> > NULL);
> >
> > if(rc != SQLITE_OK) {
> >
> > // do something
> >
> > }
> >
> > rc = sqlite3_step(mystmt);
> >
> > if(rc == SQLITE_ROW ) {
> >
> > apstr = (const char*)sqlite3_column_text(mystmt,pos);
> >
> > }
> >
> > try {
> >
> > this->finalize();
> >
> > } catch(somexception& e) {
> >
> > throw e;
> >
> > }
> >
> > return apstr;
> >
> > }
> >
> > What am I doing wrong?
> >
> >
> >
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
> ___
> 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
>



-- 
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] indexing for integer column

2012-09-06 Thread Durga D 
Thank you Kees.

On Thu, Sep 6, 2012 at 3:34 PM, Kees Nuyt  wrote:

> On Thu, 6 Sep 2012 12:02:03 +0400, Durga D  wrote:
>
> >Hi All,
> >
> >Somehow  "original author" missed one INTETER column as searchable
> >field ( like unique or primary key) in the table. When execute queries
> >based on this integer field in where clause/joins, huge performance hit.
> >So, I am planning to add INDEXING for this integer column.
> >
> >Is there any alternative/best solution?
>
> Make it a UNIQUE INDEX if possible.
>
> Yet, the best thing to do is to refactor the schema.
>
> sqlite3 olddbfile .dump >dumpfile
> edit dumpfile to adjust schema
> sqlite3 newdbfile  rename olddbfile to olddbfile.ORIGINAL
> rename newdbfile to olddbfile
> start the application, test thoroughly
>
> Inform the original author, so a new release is correct from the
> beginning.
>
>
> --
> Regards,
>
> Kees Nuyt
>
> ___
> 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] C++ - WHERE clause

2012-09-06 Thread Black, Michael (IS) 
And, when you have problems, you should always examine your SQL by running the 
EXACT same string you generate in your program through the sqlite3 shell.  This 
will help you to figure out if your SQL is wrong or your C++ is wrong.

For example even just your SELECT portion generates the wrong SQL.

You end up with this (I'm already assuming you get rid of the your last += of " 
(n_id,..." as you don't need it for a SELECT statement as already pointed out.

SELECT fname FROM table WHERE title = token
And what you want is
SELECT fname FROM table WHERE title = 'token'
So you need to add a single quote on both sides of adding your token.
apstr += "'";


Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Baruch Burstein [bmburst...@gmail.com]
Sent: Thursday, September 06, 2012 3:45 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] C++ - WHERE clause

VALUES is used for INSERTing into a table, not for SELECTing. This is not
valid SQL (I would help you fix it, but I can't figure out what you were
trying to achieve.)
Here is a great reference: http://sqlite.org/lang_select.html

On Thu, Sep 6, 2012 at 11:18 AM, Arbol One  wrote:

> As many of you know, I am trying to learn SQL using C++.
>
> Below is an error I get when I try using the C++ example below it.
>
>
>
> Error Code: 1
>
> Error Message: near "VALUES": syntax error
>
> 
>
>
>
> Glib::ustring apstr;
>
> Glib::ustring sName;
>
> int apint;
>
> mySQLite3* db;
>
> try {
>
> db = new mySQLite3(db_name.c_str());
>
> } catch(somexception& e) {
>
> //do something
>
> }
>
>
>
>// SQL statement
>
> apstr = "SELECT fname FROM ";
>
> apstr += this->db_table_name;
>
> apstr += " WHERE title = ";
>
> apstr += token;
>
> apstr += " (n_id, title, fname, mname, lname) VALUES (?, ?, ?, ?, ?)";
> // here is where the problem is see the method below
>
> apint = 1;
>
> db->setStmt(apstr);
>
> sName = db->read_str(apint);
>
>
>
> 
>
> const Glib::ustring& mySQLite3::read_str(const int pos)
>
> throw(jme::Exception) {
>
>
>
> rc = sqlite3_prepare_v2(db, this->SQLStatement.c_str(), -1, ,
> NULL);
>
> if(rc != SQLITE_OK) {
>
> // do something
>
> }
>
> rc = sqlite3_step(mystmt);
>
> if(rc == SQLITE_ROW ) {
>
> apstr = (const char*)sqlite3_column_text(mystmt,pos);
>
> }
>
> try {
>
> this->finalize();
>
> } catch(somexception& e) {
>
> throw e;
>
> }
>
> return apstr;
>
> }
>
> What am I doing wrong?
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



--
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
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] indexing for integer column

2012-09-06 Thread Kees Nuyt 
On Thu, 6 Sep 2012 12:02:03 +0400, Durga D  wrote:

>Hi All,
>
>Somehow  "original author" missed one INTETER column as searchable
>field ( like unique or primary key) in the table. When execute queries
>based on this integer field in where clause/joins, huge performance hit.
>So, I am planning to add INDEXING for this integer column.
>
>Is there any alternative/best solution?

Make it a UNIQUE INDEX if possible.

Yet, the best thing to do is to refactor the schema.

sqlite3 olddbfile .dump >dumpfile
edit dumpfile to adjust schema
sqlite3 newdbfile http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] C++ - WHERE clause

2012-09-06 Thread Stephan Beal 
On Thu, Sep 6, 2012 at 10:18 AM, Arbol One  wrote:

> As many of you know, I am trying to learn SQL using C++.
>

FWIW: it's much easier to learn SQL in its "native environment" (e.g. by
using the sqlite shell app) and then apply that learning to your
programming language of choice.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] C++ - WHERE clause

2012-09-06 Thread Baruch Burstein 
VALUES is used for INSERTing into a table, not for SELECTing. This is not
valid SQL (I would help you fix it, but I can't figure out what you were
trying to achieve.)
Here is a great reference: http://sqlite.org/lang_select.html

On Thu, Sep 6, 2012 at 11:18 AM, Arbol One  wrote:

> As many of you know, I am trying to learn SQL using C++.
>
> Below is an error I get when I try using the C++ example below it.
>
>
>
> Error Code: 1
>
> Error Message: near "VALUES": syntax error
>
> 
>
>
>
> Glib::ustring apstr;
>
> Glib::ustring sName;
>
> int apint;
>
> mySQLite3* db;
>
> try {
>
> db = new mySQLite3(db_name.c_str());
>
> } catch(somexception& e) {
>
> //do something
>
> }
>
>
>
>// SQL statement
>
> apstr = "SELECT fname FROM ";
>
> apstr += this->db_table_name;
>
> apstr += " WHERE title = ";
>
> apstr += token;
>
> apstr += " (n_id, title, fname, mname, lname) VALUES (?, ?, ?, ?, ?)";
> // here is where the problem is see the method below
>
> apint = 1;
>
> db->setStmt(apstr);
>
> sName = db->read_str(apint);
>
>
>
> 
>
> const Glib::ustring& mySQLite3::read_str(const int pos)
>
> throw(jme::Exception) {
>
>
>
> rc = sqlite3_prepare_v2(db, this->SQLStatement.c_str(), -1, ,
> NULL);
>
> if(rc != SQLITE_OK) {
>
> // do something
>
> }
>
> rc = sqlite3_step(mystmt);
>
> if(rc == SQLITE_ROW ) {
>
> apstr = (const char*)sqlite3_column_text(mystmt,pos);
>
> }
>
> try {
>
> this->finalize();
>
> } catch(somexception& e) {
>
> throw e;
>
> }
>
> return apstr;
>
> }
>
> What am I doing wrong?
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] C++ - WHERE clause

2012-09-06 Thread Arbol One 
As many of you know, I am trying to learn SQL using C++.

Below is an error I get when I try using the C++ example below it.

 

Error Code: 1

Error Message: near "VALUES": syntax error



 

Glib::ustring apstr;

Glib::ustring sName;

int apint;

mySQLite3* db; 

try {

db = new mySQLite3(db_name.c_str());

} catch(somexception& e) {

//do something

}

 

   // SQL statement

apstr = "SELECT fname FROM ";

apstr += this->db_table_name;

apstr += " WHERE title = ";

apstr += token;

apstr += " (n_id, title, fname, mname, lname) VALUES (?, ?, ?, ?, ?)";
// here is where the problem is see the method below 

apint = 1;

db->setStmt(apstr);

sName = db->read_str(apint); 

 



const Glib::ustring& mySQLite3::read_str(const int pos)

throw(jme::Exception) {

 

rc = sqlite3_prepare_v2(db, this->SQLStatement.c_str(), -1, ,
NULL);

if(rc != SQLITE_OK) { 

// do something

}

rc = sqlite3_step(mystmt);

if(rc == SQLITE_ROW ) {

apstr = (const char*)sqlite3_column_text(mystmt,pos);

}

try {

this->finalize();

} catch(somexception& e) {

throw e;

}

return apstr;

}

What am I doing wrong?

 

 

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

[sqlite] indexing for integer column

2012-09-06 Thread Durga D 
Hi All,

Somehow  "original author" missed one INTETER column as searchable
field ( like unique or primary key) in the table. When execute queries
based on this integer field in where clause/joins, huge performance hit.
So, I am planning to add INDEXING for this integer column.

Is there any alternative/best solution?

   Thanks in advance.

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