Re: [sqlite] importing data from file with 3 colums to table with 4 columns

[Simon Davies]

On 9 July 2010 06:18,   wrote:
>
>
> Thank you very much Simon.
>
> That worked very slick.
>
>
>
> Say, is there a way to put all of the SQLite3 commands  I used into a script 
> and have SQLite3 execute them in the script sequentially?
>

Yes, several ways:

use .read command (sqlite3 tst.db ".read script.sql")
use redirection to script file when invoking sqlite (sqlite3 tst.db <
script.sql)

scripts can use .read to invoke other scripts

>
>
> -Chris
>

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

[sqlite] Retrieve Specific record number in one shot.

[Piyush Verma]

Hello All,

I want to navigate to specific position in table for example I want
row number 210 inspite of nevigating one by one how can get that row.

One way could be create a Index and use where clause to get that But
it's not useful in my case.

I have table which have primary key, and product name(there is another
index for ProductName). Now I sort by Product name and want to access
row number 210.

Is that a way to get it directly?

like

>>"Select * From Product order by ProductName"
>>move_to_row(210);
>>read row;


something like that.




-- 
Thanks & Regards

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

[sqlite] Query critique

[Ian Hardingham]

Hey guys.

I have a query which is very slow, and was wondering if there was any 
advice you guys had on it.

Here are two table definitions:

CREATE TABLE IF NOT EXISTS globalRankingTable (id INTEGER PRIMARY KEY 
AUTOINCREMENT, name TEXT NOT NULL UNIQUE, ranking TEXT, score REAL, 
record TEXT);

CREATE TABLE IF NOT EXISTS friendTable (id INTEGER PRIMARY KEY 
AUTOINCREMENT, simpleId TEXT NOT NULL UNIQUE, player TEXT, friend TEXT);

And here is my query (written in a script language):

db.query("SELECT * FROM (SELECT DISTINCT * FROM globalRankingTable WHERE 
upper(name) = upper('?') OR id < ? union all SELECT a.* FROM 
globalRankingTable a, friendTable b WHERE upper(b.player) = upper('?') 
AND upper(b.friend) = upper(a.name)) ORDER BY score DESC", 0, 
%client.username, %globId, %client.username);

The intention of this query is to, basically:

- get the top 10 scores in rankingTable
- get "my" score in rankingTable
- get all of my friends' scores in rankingTable

The query works, but is very slow.  This may just be because it's a 
complex query, but all feedback would be much appreciated.

For your extra information:

- globalRankingTable has several thousand rows
- friendTable has several thousand rows also, but each user will only 
have 0-10 friends (usually on the low part of that scale)
- yes I know I'm an idiot with the upper stuff, a refactor is needed on 
that - I know all of my "name" fields should really be integers.

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

Re: [sqlite] Re trieve Specific record number in one shot.

[Igor Sereda]

Would

  Select * From Product order by ProductName LIMIT 1 OFFSET 209

help?


-- Igor
 

Piyush Verma-3 wrote:
> 
> Hello All,
> 
> I want to navigate to specific position in table for example I want
> row number 210 inspite of nevigating one by one how can get that row.
> 
> One way could be create a Index and use where clause to get that But
> it's not useful in my case.
> 
> I have table which have primary key, and product name(there is another
> index for ProductName). Now I sort by Product name and want to access
> row number 210.
> 
> Is that a way to get it directly?
> 
> like
> 
>>>"Select * From Product order by ProductName"
>>>move_to_row(210);
>>>read row;
> 
> 
> something like that.
> 
> 
> 
> 
> -- 
> Thanks & Regards
> 
> Piyush Verma
> ___
> 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/Retrieve-Specific-record-number-in-one-shot.-tp29115356p29115896.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] Query critique

[Benoit Mortgat]

On Fri, Jul 9, 2010 at 11:08, Ian Hardingham  wrote:
> Hey guys.
>
> I have a query which is very slow, and was wondering if there was any
> advice you guys had on it.
>
> Here are two table definitions:
>
> CREATE TABLE IF NOT EXISTS globalRankingTable (id INTEGER PRIMARY KEY
> AUTOINCREMENT, name TEXT NOT NULL UNIQUE, ranking TEXT, score REAL,
> record TEXT);
>
> CREATE TABLE IF NOT EXISTS friendTable (id INTEGER PRIMARY KEY
> AUTOINCREMENT, simpleId TEXT NOT NULL UNIQUE, player TEXT, friend TEXT);
>
> And here is my query (written in a script language):
>
> db.query("SELECT * FROM (SELECT DISTINCT * FROM globalRankingTable WHERE
> upper(name) = upper('?') OR id < ? union all SELECT a.* FROM
> globalRankingTable a, friendTable b WHERE upper(b.player) = upper('?')
> AND upper(b.friend) = upper(a.name)) ORDER BY score DESC", 0,
> %client.username, %globId, %client.username);
>

Create an index either on player column or friend column in your second table.
CREATE INDEX i_friendTable ON friendTable (player ASC);


-- 
Benoit Mortgat
20, avenue Marcel Paul
69200 Vénissieux, France
+33 6 17 15 41 58
+33 4 27 11 61 23
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Retrieve Specific record number in one shot.

[Israel Lins Albuquerque]

Maybe do you want this! 

http://www.sqlite.org/syntaxdiagrams.html#select-stmt 

Select * From Product order by ProductName LIMIT 210 OFFSET 210; 



- "Piyush Verma"  escreveu: 
> Hello All, 
> 
> I want to navigate to specific position in table for example I want 
> row number 210 inspite of nevigating one by one how can get that row. 
> 
> One way could be create a Index and use where clause to get that But 
> it's not useful in my case. 
> 
> I have table which have primary key, and product name(there is another 
> index for ProductName). Now I sort by Product name and want to access 
> row number 210. 
> 
> Is that a way to get it directly? 
> 
> like 
> 
> >>"Select * From Product order by ProductName" 
> >>move_to_row(210); 
> >>read row; 
> 
> 
> something like that. 
> 
> 
> 
> 
> -- 
> Thanks & Regards 
> 
> Piyush Verma 
> ___ 
> sqlite-users mailing list 
> sqlite-users@sqlite.org 
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 
> 

-- 

Atenciosamente/Regards, 

Israel Lins Albuquerque 
Desenvolvimento/Development 
Polibrás Brasil Software Ltda. 


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

Re: [sqlite] Retrieve Specific record number in one shot.

[Igor Tandetnik]

Piyush Verma  wrote:
> I want to navigate to specific position in table for example I want
> row number 210 inspite of nevigating one by one how can get that row.

See if this helps:

http://www.sqlite.org/cvstrac/wiki/wiki?p=ScrollingCursor

-- 
Igor Tandetnik

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

Re: [sqlite] Retrieve Specific record number in one shot.

[Jay A. Kreibich]

On Fri, Jul 09, 2010 at 08:12:20AM -0300, Israel Lins Albuquerque scratched on 
the wall:
> Maybe do you want this! 
> 
> http://www.sqlite.org/syntaxdiagrams.html#select-stmt 
> 
> Select * From Product order by ProductName LIMIT 210 OFFSET 210; 

  Close, but not quite.  This will return rows 211 through 420.


  If you want row 210, you need "LIMIT 1 OFFSET 209".  Or "LIMIT 209, 1"

   -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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query critique

[Tim Romano]

If you had the following tables


Table PEOPLERANKINGS(personid INTEGER PRIMARY KEY, personname TEXT, ranking)

Table FRIENDS(personid1, personid2)

and an index on

PEOPLERANKINGS.ranking

and  FRIENDS.personid1,FRIENDS.personid2  is a composite unique primary key


You could  get the top 10 ranked people

select * from PEOPLERANKINGS order by ranking desc limit 10


and get your own ranking and the ranking of your  friends:

select  peoplerankings.* from PEOPLERANKINGS
where personid  IN
  (select personid2 from FRIENDS where personid1 = ?yourId? )

NOTE: befriend yourself by default in the FRIENDS table.


Regards
Tim Romano
Swarthmore PA





On Fri, Jul 9, 2010 at 5:52 AM, Benoit Mortgat  wrote:

> On Fri, Jul 9, 2010 at 11:08, Ian Hardingham  wrote:
> > Hey guys.
> >
> > I have a query which is very slow, and was wondering if there was any
> > advice you guys had on it.
> >
> > Here are two table definitions:
> >
> > CREATE TABLE IF NOT EXISTS globalRankingTable (id INTEGER PRIMARY KEY
> > AUTOINCREMENT, name TEXT NOT NULL UNIQUE, ranking TEXT, score REAL,
> > record TEXT);
> >
> > CREATE TABLE IF NOT EXISTS friendTable (id INTEGER PRIMARY KEY
> > AUTOINCREMENT, simpleId TEXT NOT NULL UNIQUE, player TEXT, friend TEXT);
> >
> > And here is my query (written in a script language):
> >
> > db.query("SELECT * FROM (SELECT DISTINCT * FROM globalRankingTable WHERE
> > upper(name) = upper('?') OR id < ? union all SELECT a.* FROM
> > globalRankingTable a, friendTable b WHERE upper(b.player) = upper('?')
> > AND upper(b.friend) = upper(a.name)) ORDER BY score DESC", 0,
> > %client.username, %globId, %client.username);
> >
>
> Create an index either on player column or friend column in your second
> table.
> CREATE INDEX i_friendTable ON friendTable (player ASC);
>
>
> --
> Benoit Mortgat
> 20, avenue Marcel Paul
> 69200 Vénissieux, France
> +33 6 17 15 41 58
> +33 4 27 11 61 23
> ___
> 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] Corrupted sqlite journal

[Ke Tao]

HI All , 

I have sqlite db name "wdb" and "wdb-journal" file was created by power
failure something , when I do any db operation sqlite always prompt "disk
I/O error" , but when I delete the "wdb-journal" ,there is no errors
prompted. I think maybe the wdb-journal file was corrupted , does anyone
have any idea on this ?
I used sqlite on linux system.

Best Regards,
Ke Tao

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

[sqlite] WAL: Wrong error "database disk image is malformed"

[Alexey Pechnikov]

I have long running test in WAL mode (inserting millions of records in
autocommit mode). In other connection by sqlite3 shell I see these errors:

sqlite> select (select count(*) from role_exist)/1000/1000;
12
sqlite> select (select count(*) from role_exist)/1000/1000;
Error: file is encrypted or is not a database
sqlite> select (select count(*) from role_exist)/1000/1000;
12

The database is correct:
sqlite> pragma integrity_check;
ok

And test continue works:
sqlite> select (select count(*) from role_exist)/1000/1000;
Error: database disk image is malformed
sqlite> select (select count(*) from role_exist)/1000/1000;
Error: database disk image is malformed
sqlite> select (select count(*) from role_exist)/1000/1000;
Error: database disk image is malformed
sqlite> select (select count(*) from role_exist)/1000/1000;
13




-- 
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

[sqlite] Null character problem

[Kavita Raghunathan]

Hello,
I’m storing encrypted passwords in the sqlite database. The encryption 
algorithm generates “null” character, and therefore the password strings can 
have nulls in them.


 1.  Is this an issue for storing in database ? If strcpy is used anywhere, it 
would be a problem
 2.  I’m using sprintf to generate the SQL statement as shown below. This 
causes a problem because sprintf stops printing when it encounters “null”.

Please advice.

sprintf(SqlStr, "INSERT INTO %s (AttrName, AttrEnum, AttrType, AttrValue, 
ReadWrite, Entity_id) VALUES('%s', %d, %d, '%s', %d, %d);", tbl_name, 
db[i]->attr_name, db[i]->attr_num, db[i]->attr_type, db[i]->attr_value, 
db[i]->attr_src, entity_id);

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

Re: [sqlite] Null character problem

[Eric Smith]

Kavita Raghunathan wrote: 

> sprintf(SqlStr, "INSERT INTO %s (AttrName, AttrEnum, AttrType, 
> AttrValue, ReadWrite, Entity_id) VALUES('%s', %d, %d, '%s', %d, 
> %d);", tbl_name, db[i]->attr_name, db[i]->attr_num, db[i]->attr_type, 
> db[i]->attr_value, db[i]->attr_src, entity_id); 

Don't do that.  What if attr_name contains a ' character (or, as you
say, some other weird character)?

Instead, prepare a statement with sqlite variables, and bind values to
those variables using the sqlite3_bind* family of interfaces:
http://sqlite.org/c3ref/bind_blob.html

I have no specific knowledge on whether sqlite handles null characters
within the variables' values--but if I were a bettin man, I'd bet that
it handles them quite cleanly.

-- 
Eric A. Smith

I think there's a world market for about five computers.
-- attr. Thomas J. Watson (Chairman of the Board, IBM), 1943
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Null character problem

[Jay A. Kreibich]

On Fri, Jul 09, 2010 at 11:24:19AM -0500, Kavita Raghunathan scratched on the 
wall:
> Hello,
> I?m storing encrypted passwords in the sqlite database. The encryption
> algorithm generates ?null? character, and therefore the password
> strings can have nulls in them.
> 
>  1.  Is this an issue for storing in database ? If strcpy is used
>  anywhere, it would be a problem

  They can't be stored as text values without some type of encoding
  (like base64), but they can be stored as BLOBs.

>  2.  I?m using sprintf to generate the SQL statement as shown below.
>  This causes a problem because sprintf stops printing when it
>  encounters ?null?.

  Don't do that.  Use statement parameters and bind the data directly.
   
   -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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Null character problem

[Simon Slavin]

On 9 Jul 2010, at 5:29pm, Eric Smith wrote:

> I have no specific knowledge on whether sqlite handles null characters
> within the variables' values--but if I were a bettin man, I'd bet that
> it handles them quite cleanly.

You win.  BLOBs can handle any sequences of bytes without problems, including 
nulls, ETX, and sequences which be illegal if they were used to express Unicode 
characters.  You can put anything you like in a BLOB.

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

Re: [sqlite] Null character problem

[Eric Smith]

Simon Slavin wrote: 

> BLOBs can handle any sequences of bytes without problems, 
> including nulls, ETX, and sequences which be illegal if they were used to 
> express Unicode characters.  You can put anything you like in a BLOB.  

I assume, due to the manifest typing semantics of the library, that 
the declared type of the column will make no difference when I bind a 
weird datum to a variable (as long as it's not an 'INTEGER PRIMARY KEY' 
column).  

Will sqlite3_bind_text work properly if the string contains (internal) 
nulls?  What if I did something like: 

char zText[100];
memset(zText, 0, sizeof(zText)); 
sqlite3_bind_text(stmt, idx, zText, sizeof(zText), SQLITE_TRANSIENT); 

According to a strict reading of the doc, sqlite will blindly copy
sizeof(zText) characters (starting from zText[0]) into the column.  
That is, this will store 100 null bytes into the column.  Is that 
right?

Eric 

-- 
Eric A. Smith

When you come to a fork in the road, take it.
-- Yogi Berra
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Null character problem

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/09/2010 09:24 AM, Kavita Raghunathan wrote:
>  1.  Is this an issue for storing in database ? If strcpy is used anywhere, 
> it would be a problem

SQLite quite happily stores/retrieves null bytes in strings.  It is part of
my test suite.  If you use the SQLite API correctly then it is fine.  (ie
*you* must not use strcpy etc).

Some of the SQL level functions will however stop at the first null such as
string concatenation or replace.

Note however that strings are to store Unicode strings, encoded as UTF-8 or
UTF-16 depending on the API flavour you use.  Do not save binary data into a
string.

>  2.  I’m using sprintf to generate the SQL statement as shown below. 

As others have pointed out this is a really bad idea as it allows for SQL
injection bugs and attacks.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkw3WAcACgkQmOOfHg372QSNigCgpxjYX4Rktm7qePeZB/bKrZHs
tHkAoOIvWAhNMFjI5P8F5sy7ZuXfZkD2
=T/px
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Null character problem

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/09/2010 09:30 AM, Jay A. Kreibich wrote:
>   They can't be stored as text values without some type of encoding
>   (like base64), but they can be stored as BLOBs.

Nulls can be stored in text values.  Behind the scenes SQLite treats strings
and blobs almost identically and you'll notice most functions operate on
both.  The major difference is that strings can be transformed into UTF-8 or
UTF-16 encoding.  Other than that they are both a bucket of bytes.

As an example look at the implementation of sqlite3_bind_{blob,text} or
trimFunc and replaceFunc.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkw3WrsACgkQmOOfHg372QTzbACfVVfmAsmSBdvv6WwPtd00DzHt
/z4AoMORO0XCFsc3g3csCL6QA5meBzmM
=g/YJ
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Null character problem

[Simon Slavin]

On 9 Jul 2010, at 5:48pm, Eric Smith wrote:

> Simon Slavin wrote: 
> 
>> BLOBs can handle any sequences of bytes without problems, 
>> including nulls, ETX, and sequences which be illegal if they were used to 
>> express Unicode characters.  You can put anything you like in a BLOB.  
> 
> I assume, due to the manifest typing semantics of the library, that 
> the declared type of the column will make no difference when I bind a 
> weird datum to a variable (as long as it's not an 'INTEGER PRIMARY KEY' 
> column).

You can use the _bind_blob routine to bind something which will eventually be 
stored in a column with TEXT affinity.  (Sorry, I hate that sentence but I 
can't figure out how to improve it.)  There are no problems doing this.

> Will sqlite3_bind_text work properly if the string contains (internal) 
> nulls?

There are many different meanings for 'string' and I can't answer any question 
about 'properly' until you describe in great detail what you mean by 'string'.  
On the other hand _bind_blob doesn't refer to strings at all.

> What if I did something like: 
> 
> char zText[100];
> memset(zText, 0, sizeof(zText)); 
> sqlite3_bind_text(stmt, idx, zText, sizeof(zText), SQLITE_TRANSIENT);

Read the documentation for memset().  It does not take chars as parameters and 
does not use them internally.

You are using a _text routine with an array of 'char's.  Those lines will work 
perfectly together.  But they has nothing to do with using BLOBs in SQLite.  
For that you would not use _bind_text, you'd use _bind_blob with things that 
probably do not represent characters.  Possibly 'unsigned int' would be a more 
appropriate type than 'char'.

> According to a strict reading of the doc, sqlite will blindly copy
> sizeof(zText) characters (starting from zText[0]) into the column.  
> That is, this will store 100 null bytes into the column.  Is that 
> right?

Text is not bytes, it's characters.  Text involves interpretation.  BLOBs, on 
the other hand, are just sequences of bytes which are treated without any 
attempt to interpret or convert them at all.  Since this is what your data 
actually is (according to your earlier post) you should be declaring your 
column as a BLOB column and using _blob routines to handle them.

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

Re: [sqlite] Null character problem

[H. Phil Duby]

On Fri, Jul 9, 2010 at 10:48 AM, Eric Smith  wrote:
> Will sqlite3_bind_text work properly if the string contains (internal)
> nulls?  What if I did something like:
>
> char zText[100];
> memset(zText, 0, sizeof(zText));
> sqlite3_bind_text(stmt, idx, zText, sizeof(zText), SQLITE_TRANSIENT);

If you *really* want to work with your password information as a
string / using string functions, convert / encode it to regular text
first.  The trivial case would encode to hex characters, changing your
null characters to '00', which any of the string functions would
happily handle.  That encoding would double the storage space needed
for the password, but string handling would be *safe*.  Other
encodings [that produce standard text characters] would also work.
These would still require more storage than the raw 'binary' data you
have, but most would be less than the double storage needed by the hex
characters.
--
Phil
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Null character problem

[Eric Smith]

Simo Slavin wrote: 

> (according to your earlier post) 

I'm not OP.  I'm Eric.  OP was someone else.  In this context, I don't 
care about blobs or about the right way of doing anything.

> Read the documentation for memset().  

I know quite well how memset works.  I know character!=byte.  These 
matters are irrelevant to my question.  

My question came purely from a mild curiosity.  I was wondering about 
the behavior of sqlite call sqlite3_bind_text when it is passed a range 
of BYTES that includes nulls.

-- 
Eric A. Smith

It's up.  It sorta works.  That's a start.
-- BJ Premore
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] More WAL questions and concerns.

[A. H. Ongun]

A few questions that are my application specific that is an embedded 
application.

I have two databases, the first one is a fixed size configuration database 
where 
all writes are UPDATES, and the database does not get that many writes.  Does 
this mean that because of WAL the database itself will not get updated and 
changes will be residing in WAL for a very long time due to lack of traffic?

The second is an archive database that is written to once a second, and once a 
month (size grows to 335MB), the database is closed, renamed, a template 
database is copied over, and the process starts again.  Does this mean that I 
have to worry about renaming the WAL file as well now?

Thanks in advance.



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

[sqlite] sqlite query with c

[smengl90]

-- 
View this message in context: 
http://old.nabble.com/sqlite-query-with-c-tp29120975p29120975.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] Null character problem

[Virgilio Alexandre Fornazin]

>> My question came purely from a mild curiosity.  I was wondering about 
>> the behavior of sqlite call sqlite3_bind_text when it is passed a range 
>> of BYTES that includes nulls.

See this snipper for documentation:

"The third argument is the value to bind to the parameter.

In those routines that have a fourth argument, its value is the number of
bytes in the parameter. To be clear: the value is the number of bytes in the
value, not the number of characters. If the fourth parameter is negative,
the length of the string is the number of bytes up to the first zero
terminator.

The fifth argument to sqlite3_bind_blob(), sqlite3_bind_text(), and
sqlite3_bind_text16() is a destructor used to dispose of the BLOB or string
after SQLite has finished with it. If the fifth argument is the special
value SQLITE_STATIC, then SQLite assumes that the information is in static,
unmanaged space and does not need to be freed. If the fifth argument has the
value SQLITE_TRANSIENT, then SQLite makes its own private copy of the data
immediately, before the sqlite3_bind_*() routine returns."

http://sqlite.org/c3ref/bind_blob.html

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Eric Smith
Sent: sexta-feira, 9 de julho de 2010 14:54
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Null character problem

Simo Slavin wrote: 

> (according to your earlier post) 

I'm not OP.  I'm Eric.  OP was someone else.  In this context, I don't 
care about blobs or about the right way of doing anything.

> Read the documentation for memset().  

I know quite well how memset works.  I know character!=byte.  These 
matters are irrelevant to my question.  

My question came purely from a mild curiosity.  I was wondering about 
the behavior of sqlite call sqlite3_bind_text when it is passed a range 
of BYTES that includes nulls.

-- 
Eric A. Smith

It's up.  It sorta works.  That's a start.
-- BJ Premore
___
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] Null character problem

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/09/2010 10:54 AM, Eric Smith wrote:
> My question came purely from a mild curiosity.  I was wondering about 
> the behavior of sqlite call sqlite3_bind_text when it is passed a range 
> of BYTES that includes nulls.

bind_text and bind_blob use the same backend function, passing it their
parameters (byte pointer and length) as well as an encoding parameter (0 for
blob, SQLITE_UTF8 or SQLITE_UTF16NATIVE).  If the encoding doesn't match the
database encoding then it is changed.

The important thing is that the bytes are taken exactly as is.  They are not
looked at unless the encoding needs to be changed.  The routine to change
encoding (sqlite3VdbeMemTranslate) does not return an error if the bytes are
not actually valid UTF8/16.

In other words if you give SQLite something claiming it is a string then
SQLite takes you at your word and does not do a round of double checking.
It will return that same "string" when retrieving that value (ie same length
and byte sequence).  This is why you can include embedded nulls.  It is also
why you can feed it bytes in IS8859-1 and things will seem to be okay.  (One
of the Python wrappers allows that!)

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkw3aWoACgkQmOOfHg372QT3vACgtVRoq/l8HZeDLd/QYwdt50NN
qZ0AoM48Qu3ubM3Ld3FfQMjnyxv/WJkA
=i6MM
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite query with c++ variable

[smengl90]

Hi, I want to compose a query that will use a c++ variable. For example I
have:

int i= 5;
char * query = "SELECT * from userInfo WHERE count<'i'".

The syntax does not work. How do I do that?

Thanks 
-- 
View this message in context: 
http://old.nabble.com/sqlite-query-with-c%2B%2B-variable-tp29121237p29121237.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] concat 2 const chars ?

[rollerueckwaerts]

Hello, 
I try to get an sql query string from 2 const chars.

const char *language;
language = "'6'";
const char *sql2 = "SELECT key,name,text FROM uebersetzungen WHERE sprach_id
= ";

const char *sql = strcpy(sql2,language);
// or
const char *sql = "SELECT key,name,text FROM uebersetzungen WHERE sprach_id
= " + language;
//or
const char *sql = "SELECT key,name,text FROM uebersetzungen WHERE sprach_id
= " & language;


nothing works :)

How can i do this ?

Hoping for help :)
tobi

-- 
View this message in context: 
http://old.nabble.com/concat-2-const-chars---tp29121393p29121393.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] WAL: no longer able to use read-only databases?

[Matthew L. Creech]

In testing the latest SQLite snapshot with WAL enabled, it seems that
there's no way to use a database in a read-only location.  For
example, let's say I've created a database as root, then closed it
(cleanly):

$ ls -l /flash/alarms.db*
-rw-r--r--1 root root 36864 Jan  1 00:14 /flash/alarms.db

If I try as another user to use that database, I get an error:

$ sqlite3 /flash/alarms.db
SQLite version 3.7.0
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> PRAGMA journal_mode;
Error: unable to open database file
sqlite>

However, if I create a symlink to that database in a location that I
have write access to, then everything works fine:

$ ln -s /flash/alarms.db ./alarms.db
$ ls -l alarms.db*
lrwxrwxrwx1 adminadmin   16 Jan  1 00:15 alarms.db ->
/flash/alarms.db
$ sqlite3 alarms.db
SQLite version 3.7.0
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> PRAGMA journal_mode;
wal
sqlite> .quit
$ ls -l alarms.db*
lrwxrwxrwx1 adminadmin   16 Jan  1 00:15 alarms.db ->
/flash/alarms.db
-rw-r--r--1 adminadmin32768 Jan  1 00:15 alarms.db-shm
-rw-r--r--1 adminadmin0 Jan  1 00:15 alarms.db-wal

So clearly this is a side-effect of WAL creation, which happens in the
same directory as the database file.

This doesn't seem like it should fundamentally be any different than
normal journaling mode, in that opening a database in read-only mode
makes the creation of a journal / WAL unnecessary.  But I'm not
familiar with the WAL internals, so maybe there's more to it.  FYI,
this works fine with normal journaling mode (we bumped in existing
code after changing the journal_mode).

Any additional flags or ways of doing this that I'm missing?  Or is it a bug?

Thanks!

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

Re: [sqlite] Corrupted sqlite journal

[Jim Wilcoxson]

On Fri, Jul 9, 2010 at 10:38 AM, Ke Tao  wrote:

>
> HI All ,
>
> I have sqlite db name "wdb" and "wdb-journal" file was created by power
> failure something , when I do any db operation sqlite always prompt "disk
> I/O error" , but when I delete the "wdb-journal" ,there is no errors
> prompted. I think maybe the wdb-journal file was corrupted , does anyone
> have any idea on this ?
> I used sqlite on linux system.
>
> Best Regards,
> Ke Tao
>

Permission problems can cause this.  The "disk I/O error" actually means "I
can't do a rollback", maybe because root created the journal and owns the
database, then another user is trying to do things with it.  Just a guess.
This "disk I/O error" message is very confusing.  Been there!

Jim
--
HashBackup: easy onsite and offsite Unix backup
http://sites.google.com/site/hashbackup
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Corrupted sqlite journal

[Jim Wilcoxson]

On Fri, Jul 9, 2010 at 3:21 PM, Jim Wilcoxson  wrote:

> On Fri, Jul 9, 2010 at 10:38 AM, Ke Tao  wrote:
>
>>
>> HI All ,
>>
>> I have sqlite db name "wdb" and "wdb-journal" file was created by power
>> failure something , when I do any db operation sqlite always prompt "disk
>> I/O error" , but when I delete the "wdb-journal" ,there is no errors
>> prompted. I think maybe the wdb-journal file was corrupted , does anyone
>> have any idea on this ?
>> I used sqlite on linux system.
>>
>> Best Regards,
>> Ke Tao
>>
>
> Permission problems can cause this.  The "disk I/O error" actually means "I
> can't do a rollback", maybe because root created the journal and owns the
> database, then another user is trying to do things with it.  Just a guess.
> This "disk I/O error" message is very confusing.  Been there!
>
> Jim
> --
> HashBackup: easy onsite and offsite Unix backup
> http://sites.google.com/site/hashbackup
>

Also, by deleting the journal, you are likely to corrupt your database.  You
can't delete the journal!

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

Re: [sqlite] How to concat column

[Peng Yu]

On Sun, Jul 4, 2010 at 7:15 PM, P Kishor  wrote:
> On Sun, Jul 4, 2010 at 6:15 PM, Peng Yu  wrote:
>> Hi,
>>
>> I only find row-wise concatenation by not column-wise.
>>
>> For example, I have table
>>
>> x1 y1
>> x1 y2
>> x2 y3
>> x4 y4
>>
>> I want to have the second column concatenated based on the value in
>> the first column to get the new table
>> x1 y1y2
>> x2 y3y4
>>
>> Moreover, I want to have a spectator (e.g., ',') in the second column.
>> x1 y1,y2
>> x2 y3,y4
>>
>> Could you show me if it is possible to do this in sqlite3?
>
>
> sqlite> CREATE TABLE t (a, b);
> sqlite> INSERT INTO t VALUES ('x1', 'y1');
> sqlite> INSERT INTO t VALUES ('x1', 'y2');
> sqlite> INSERT INTO t VALUES ('x2', 'y3');
> sqlite> INSERT INTO t VALUES ('x4', 'y4');
> sqlite> SELECT * FROM t;
> a           b
> --  --
> x1          y1
> x1          y2
> x2          y3
> x4          y4
> sqlite> SELECT a, Group_concat(b) FROM t GROUP BY a;
> a           Group_concat(b)
> --  ---
> x1          y1,y2
> x2          y3
> x4          y4
> sqlite> UPDATE t SET a = 'x2' WHERE a = 'x4';
> sqlite> SELECT a, Group_concat(b) FROM t GROUP BY a;
> a           Group_concat(b)
> --  ---
> x1          y1,y2
> x2          y3,y4
> sqlite>

Is there a way to reverse the operation done by Group_concat.

x1  y1,y2
x2  y3,y4

Suppose that I start with the above table, how to convert it to the
following table?

x1  y1
x1  y2
x2  y3
x2  y4

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

Re: [sqlite] Null character problem

[Kavita Raghunathan]

I'm apologise for the reminder, but I think I have become a victim of
"thread takeover".

Would someone please kindly answer my questions ?

Kavita


On 7/9/10 11:41 AM, "Kavita Raghunathan" 
wrote:

> Thanks. Sounds like I have to use BLOBs which is not what I'm doing
> currently. 
> 
> 1) I wonder about backward compatibility when I start using this BLOB
> method. There are previous databases out there that don't use BLOBs. Can I
> mix and match ? Or does this require the previous database to be deleted on
> an upgrade ?
> 
> 2) When you say " Use statement paramters and bind the data directly" Are
> you referring to the examples in http://sqlite.org/c3ref/bind_blob.html as
> pointed to by Eric Smith?
> 
> Thanks,
> Kavita
> 
> 
> On 7/9/10 11:30 AM, "Jay A. Kreibich"  wrote:
> 
>> On Fri, Jul 09, 2010 at 11:24:19AM -0500, Kavita Raghunathan scratched on the
>> wall:
>>> Hello,
>>> I?m storing encrypted passwords in the sqlite database. The encryption
>>> algorithm generates ?null? character, and therefore the password
>>> strings can have nulls in them.
>>> 
>>>  1.  Is this an issue for storing in database ? If strcpy is used
>>>  anywhere, it would be a problem
>> 
>>   They can't be stored as text values without some type of encoding
>>   (like base64), but they can be stored as BLOBs.
>> 
>>>  2.  I?m using sprintf to generate the SQL statement as shown below.
>>>  This causes a problem because sprintf stops printing when it
>>>  encounters ?null?.
>> 
>>   Don't do that.  Use statement parameters and bind the data directly.
>>
>>-j
> 
> ___
> 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] concat 2 const chars ?

[Adam DeVita]

>From the point of view of a C question, make your array of characters large
enough to hold the characters you want (and terminating null) before copying
them in.

>From the point of view of an SQL: if you want to change the comparison
constant in a where clause, look up bind parameters.

read through
http://www.sqlite.org/c3ref/bind_blob.html

There are lots of examples on the list of binding.

regards,
Adam


On Fri, Jul 9, 2010 at 3:06 PM, rollerueckwaerts
wrote:

>
> Hello,
> I try to get an sql query string from 2 const chars.
>
> const char *language;
> language = "'6'";
> const char *sql2 = "SELECT key,name,text FROM uebersetzungen WHERE
> sprach_id
> = ";
>
> const char *sql = strcpy(sql2,language);
> // or
> const char *sql = "SELECT key,name,text FROM uebersetzungen WHERE sprach_id
> = " + language;
> //or
> const char *sql = "SELECT key,name,text FROM uebersetzungen WHERE sprach_id
> = " & language;
>
>
> nothing works :)
>
> How can i do this ?
>
> Hoping for help :)
> tobi
>
> --
> View this message in context:
> http://old.nabble.com/concat-2-const-chars---tp29121393p29121393.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
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
7100 Warden Ave, Unit 3
Markham ON, L3R 8B5
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] concat 2 const chars ?

[Adam DeVita]

and don't use strcpy

here is why
https://buildsecurityin.us-cert.gov/bsi-rules/home/g1/848-BSI.html

On Fri, Jul 9, 2010 at 3:06 PM, rollerueckwaerts
wrote:

>
> Hello,
> I try to get an sql query string from 2 const chars.
>
> const char *language;
> language = "'6'";
> const char *sql2 = "SELECT key,name,text FROM uebersetzungen WHERE
> sprach_id
> = ";
>
> const char *sql = strcpy(sql2,language);
> // or
> const char *sql = "SELECT key,name,text FROM uebersetzungen WHERE sprach_id
> = " + language;
> //or
> const char *sql = "SELECT key,name,text FROM uebersetzungen WHERE sprach_id
> = " & language;
>
>
> nothing works :)
>
> How can i do this ?
>
> Hoping for help :)
> tobi
>
> --
> View this message in context:
> http://old.nabble.com/concat-2-const-chars---tp29121393p29121393.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
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
7100 Warden Ave, Unit 3
Markham ON, L3R 8B5
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] EXTERNAL: concat 2 const chars ?

[Black, Michael (IS)]

You must be thinking of Java or such -- C doesn't do concatenation that way.  
There is a strcat() function but that's an ugly way to do it.
 
Here's how I'd do it:
 
int language=6;
int nbytes;
char sql[65535]; // big sql buffer to store whatever we need
nbytes=snprintf(sql,sizeof(sql),"SELECT key,name,test FROM uebersetzungen WHERE 
sprach_id='%d'", language);
if (nbytes >= sizeof(sql)) {
fprintf(stderr,"SQL buffer overflow!!\n");
}
 
How is sprach_id defined in your table?  As an integer or character?  It looks 
to me like you're using it as an integer.
 
 
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of rollerueckwaerts
Sent: Fri 7/9/2010 2:06 PM
To: sqlite-users@sqlite.org
Subject: EXTERNAL:[sqlite] concat 2 const chars ?




Hello,
I try to get an sql query string from 2 const chars.

const char *language;
language = "'6'";
const char *sql2 = "SELECT key,name,text FROM uebersetzungen WHERE sprach_id
= ";   

const char *sql = strcpy(sql2,language);
// or
const char *sql = "SELECT key,name,text FROM uebersetzungen WHERE sprach_id
= " + language;
//or
const char *sql = "SELECT key,name,text FROM uebersetzungen WHERE sprach_id
= " & language;


nothing works :)

How can i do this ?

Hoping for help :)
tobi

--
View this message in context: 
http://old.nabble.com/concat-2-const-chars---tp29121393p29121393.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] sqlite query with c++ variable

[Sam Carleton]

On Fri, Jul 9, 2010 at 2:48 PM, smengl90
 wrote:
>
> Hi, I want to compose a query that will use a c++ variable. For example I
> have:
>
> int i= 5;
> char * query = "SELECT * from userInfo WHERE count<'i'".
>
> The syntax does not work. How do I do that?

smengl90,

You are asking a question that would be answered in an decent tutorial
on C/C++ programming.  Based on your earlier questions about how to
compile/link C/C++ code and now this question, I have to say:

You might get a lot farther, a lot faster, if you step back from
sqlite for a little bit and dig into basic C/C++ programming.

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

Re: [sqlite] sqlite query with c++ variable

[Eric Smith]

smengl90 wrote:

> Hi, I want to compose a query that will use a c++ variable. For example I
> have:
> 
> int i= 5;
> char * query = "SELECT * from userInfo WHERE count<'i'".
> 
> The syntax does not work. How do I do that?

I think your primary problem is lack of experience in C++.  I 
strongly recommend that you read up on the language itself before
attempting to write SQLite applications.

Are you required to use C++?  You may find it (much, much) easier to 
use Tcl at first.

If I may ask: are you a student, or doing this for fun in your spare 
time, or is this a part of your job?

Eric

--
Eric A. Smith

Windows is *NOT* a virus - viruses are small and efficient.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to concat column

[P Kishor]

On Fri, Jul 9, 2010 at 2:35 PM, Peng Yu  wrote:
> On Sun, Jul 4, 2010 at 7:15 PM, P Kishor  wrote:
>> On Sun, Jul 4, 2010 at 6:15 PM, Peng Yu  wrote:
>>> Hi,
>>>
>>> I only find row-wise concatenation by not column-wise.
>>>
>>> For example, I have table
>>>
>>> x1 y1
>>> x1 y2
>>> x2 y3
>>> x4 y4
>>>
>>> I want to have the second column concatenated based on the value in
>>> the first column to get the new table
>>> x1 y1y2
>>> x2 y3y4
>>>
>>> Moreover, I want to have a spectator (e.g., ',') in the second column.
>>> x1 y1,y2
>>> x2 y3,y4
>>>
>>> Could you show me if it is possible to do this in sqlite3?
>>
>>
>> sqlite> CREATE TABLE t (a, b);
>> sqlite> INSERT INTO t VALUES ('x1', 'y1');
>> sqlite> INSERT INTO t VALUES ('x1', 'y2');
>> sqlite> INSERT INTO t VALUES ('x2', 'y3');
>> sqlite> INSERT INTO t VALUES ('x4', 'y4');
>> sqlite> SELECT * FROM t;
>> a           b
>> --  --
>> x1          y1
>> x1          y2
>> x2          y3
>> x4          y4
>> sqlite> SELECT a, Group_concat(b) FROM t GROUP BY a;
>> a           Group_concat(b)
>> --  ---
>> x1          y1,y2
>> x2          y3
>> x4          y4
>> sqlite> UPDATE t SET a = 'x2' WHERE a = 'x4';
>> sqlite> SELECT a, Group_concat(b) FROM t GROUP BY a;
>> a           Group_concat(b)
>> --  ---
>> x1          y1,y2
>> x2          y3,y4
>> sqlite>
>
> Is there a way to reverse the operation done by Group_concat.
>
> x1          y1,y2
> x2          y3,y4
>
> Suppose that I start with the above table, how to convert it to the
> following table?
>
> x1          y1
> x1          y2
> x2          y3
> x2          y4
>


Use a programming language.


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



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

Re: [sqlite] concat 2 const chars ?

[Sam Carleton]

I really don't mean to be a jerk, but this does seem to be really off
topic for this mailing list.  Isn't the concatination of two string a
general C/C++ question rather then a sqlite question?  Don't you think
you might be better off asking this question on a C/C++ mailing list
or forum, maybe somewhere like http://www.codeguru.com?

As to stay close to the topic [sqlite], aka that of general sql, I
would HIGHLY recommend looking at this link:
http://en.wikipedia.org/wiki/SQL_injection

And finally to stay on topic [sqlite], don't do what you are trying to
do, use a prepared statement and bind your variables to the prepared
statement to prevent sql injection.

Sam

On Fri, Jul 9, 2010 at 3:06 PM, rollerueckwaerts
 wrote:
>
> Hello,
> I try to get an sql query string from 2 const chars.
>
> const char *language;
> language = "'6'";
> const char *sql2 = "SELECT key,name,text FROM uebersetzungen WHERE sprach_id
> = ";
>
> const char *sql = strcpy(sql2,language);
> // or
> const char *sql = "SELECT key,name,text FROM uebersetzungen WHERE sprach_id
> = " + language;
> //or
> const char *sql = "SELECT key,name,text FROM uebersetzungen WHERE sprach_id
> = " & language;
>
>
> nothing works :)
>
> How can i do this ?
>
> Hoping for help :)
> tobi
>
> --
> View this message in context: 
> http://old.nabble.com/concat-2-const-chars---tp29121393p29121393.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] concat 2 const chars ?

[Martin Engelschalk]

Hi,

i think you are asking a C question, and not an sql question.
Is your example C code? If this is the case, you should read up on C basics.
Feel free to contact me directly (also in German)

Martin

Am 09.07.2010 21:06, schrieb rollerueckwaerts:
> Hello,
> I try to get an sql query string from 2 const chars.
>
> const char *language;
> language = "'6'";
> const char *sql2 = "SELECT key,name,text FROM uebersetzungen WHERE sprach_id
> = ";  
>
> const char *sql = strcpy(sql2,language);
> // or
> const char *sql = "SELECT key,name,text FROM uebersetzungen WHERE sprach_id
> = " + language;
> //or
> const char *sql = "SELECT key,name,text FROM uebersetzungen WHERE sprach_id
> = "&  language;
>
>
> nothing works :)
>
> How can i do this ?
>
> Hoping for help :)
> tobi
>
>


Fax: +49 (0) 3212 / 1001404
engelsch...@codeswift.com
www.codeswift.com / www.swiftcash.at

Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309

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

Re: [sqlite] open db cx to fork(2)d children

[Eric Smith]

> I strongly recommend that you always make the child side of fork(2)
> either exit(2) or exec(2) immediately.  

Sorry Nico, I never saw this response -- I appreciate it!

What do you mean, "immediately"?  As I said, my child comes to life,
does some work without touching (its copy of) existing SQLite strucures,
and then calls exit(2).  The lifetime of the child is small wrt the
lifetime of the parent.

Let's assume for the moment that I don't care about safety wrt
non-sqlite libraries (except of course any libraries on which 
sqlite depends).

> With respect to SQLite3, there are two sets of fork-safety issues: file
> descriptor offsets (use USE_PREAD to avoid this issue), 

I take you to mean that the child and parent's fds point to the same
underlying file description, and if the child changes the file
description then it will have a side effect in the parent.

But I have assumed that the child does not make any sqlite api calls
against existing sqlite structures.  I believe this assumption allows 
me to conclude that sqlite will not touch any existing fd, and hence
will not bear such an impact on the parent (even if the child makes
sqlite api calls against structures the child creates on its own).  
Am I right? 

> and POSIX file byte range locks.

I'm not using POSIX locks, so I'm good to go there.  But even if I were,
I believe my above reasoning applies equally well here, since I believe
your reason for being concerned about it is similar.  The fds that were 
duplicated across the fork refer to the same underlying file
description, so we are technically in a "dangerous" state: the child
*could*, at its whim, release the parent's lock (for example).  But if 
it guarantees not to do so (by guaranteeing to make no sqlite calls
against existing structures), then no harm will result.

Thanks,
Eric

--
Eric A. Smith

Impartial, adj.:
Unable to perceive any promise of personal advantage from
espousing either side of a controversy or adopting either of two
conflicting opinions.
-- Ambrose Bierce, "The Devil's Dictionary"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] open db cx to fork(2)d children

[Nicolas Williams]

On Fri, Jul 09, 2010 at 04:52:35PM -0400, Eric Smith wrote:
> 
> > I strongly recommend that you always make the child side of fork(2)
> > either exit(2) or exec(2) immediately.  
> 
> Sorry Nico, I never saw this response -- I appreciate it!
> 
> What do you mean, "immediately"?

Good question.  One answer: "before ever utilizing state inherited from
the parent for libraries such as libsqlite3".

A much more conservative answer: "the exit(2) or exec(2) call should be
lexically close to the fork(2) call, and it should be obvious from
lexical context that the child will not ever utilize state inherited
from the parent for libraries such as libsqlite3".

(What a mouthful.  If there's a good English-language word to describe
the above without superfluously referring to 'time', as 'immediately'
does, I'd love to hear what it is.)

>   As I said, my child comes to life,
> does some work without touching (its copy of) existing SQLite strucures,
> and then calls exit(2).  The lifetime of the child is small wrt the
> lifetime of the parent.

That's fine.  The child could run forever then.  Of course, if you
design an architecture like that you end up creating the temptation
(for subsequent developers) to do more in the child process later,
so I'd not recommend it.

> Let's assume for the moment that I don't care about safety wrt
> non-sqlite libraries (except of course any libraries on which 
> sqlite depends).

Good luck!  :)

> > With respect to SQLite3, there are two sets of fork-safety issues: file
> > descriptor offsets (use USE_PREAD to avoid this issue), 
> 
> I take you to mean that the child and parent's fds point to the same
> underlying file description, and if the child changes the file
> description then it will have a side effect in the parent.

Exactly.

> But I have assumed that the child does not make any sqlite api calls
> against existing sqlite structures.  I believe this assumption allows 
> me to conclude that sqlite will not touch any existing fd, and hence
> will not bear such an impact on the parent (even if the child makes
> sqlite api calls against structures the child creates on its own).  
> Am I right? 

Correct.

> > and POSIX file byte range locks.
> 
> I'm not using POSIX locks, so I'm good to go there.  But even if I were,
> I believe my above reasoning applies equally well here, since I believe
> your reason for being concerned about it is similar.  The fds that were 
> duplicated across the fork refer to the same underlying file
> description, so we are technically in a "dangerous" state: the child
> *could*, at its whim, release the parent's lock (for example).  But if 
> it guarantees not to do so (by guaranteeing to make no sqlite calls
> against existing structures), then no harm will result.

No, I meant that if you stepped, in the child, a statement inherited
from the parent then the child would go ahead believing it has a lock
when in fact it does not (because the parent got the lock).

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

Re: [sqlite] open db cx to fork(2)d children

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/09/2010 01:52 PM, Eric Smith wrote:
> What do you mean, "immediately"?  As I said, my child comes to life,
> does some work without touching (its copy of) existing SQLite strucures,
> and then calls exit(2).

I'll bet you are actually getting exit(3) which means anything registered
with atexit will be run.  (SQLite does not register with atexit.)

In my wrapper I provide functionality that can check SQLite objects are not
being used across processes.  The way it does this is by providing an
alternate mutex implementation (almost every SQLite operation acquires and
releases mutexes) and verifies the mutex is used in the same process id it
was allocated in.  In a benchmark doing only SQLite operations I found a 1%
performance hit.

A little tricky thing is that you can't change mutex implementations after
SQLite has initialised.  Consequently the code ensures SQLite is initialised
to get the current mutex implementation, then does a shutdown, installs the
new implementation and initialises SQLite again.  You should not shutdown
SQLite unless all items are fully closed and freed.  Therefore this code is
best run as close to the beginning of main() as possible.

I'd recommend doing something like this, even if it is only enabled during
testing because other people may write code, or after time has passed you
may forget about the whole forking issue.

You can see example code at:

  http://code.google.com/p/apsw/source/browse/src/apsw.c#858

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkw3kxkACgkQmOOfHg372QTomgCg1g8LUOQnJK7z3B0j2tsax6FN
2OsAn1K6eO1zHZx+eR66PVasjaugm9tp
=Hm6K
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] open db cx to fork(2)d children

[Nicolas Williams]

On Fri, Jul 09, 2010 at 02:22:37PM -0700, Roger Binns wrote:
> On 07/09/2010 01:52 PM, Eric Smith wrote:
> > What do you mean, "immediately"?  As I said, my child comes to life,
> > does some work without touching (its copy of) existing SQLite strucures,
> > and then calls exit(2).
> 
> I'll bet you are actually getting exit(3) which means anything registered
> with atexit will be run.  (SQLite does not register with atexit.)

Oh, duh.  I forgot that distinction.  Yes, exit(3), not exit(2).  (Any
library with atexit handlers should check the process' PID if fork-
safety is an issue, and do nothing when called on the child side of a
fork().)

> In my wrapper I provide functionality that can check SQLite objects are not
> being used across processes.  The way it does this is by providing an
> alternate mutex implementation (almost every SQLite operation acquires and
> releases mutexes) and verifies the mutex is used in the same process id it
> was allocated in.  In a benchmark doing only SQLite operations I found a 1%
> performance hit.

The trick to making that go fast is to use pthread_atfork() to get the
new PID on the child side of fork() and store the PID in a global
variable so that you don't need to call getpid().

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

Re: [sqlite] open db cx to fork(2)d children

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/09/2010 02:31 PM, Nicolas Williams wrote:
> The trick to making that go fast is to use pthread_atfork() to get the
> new PID on the child side of fork() and store the PID in a global
> variable so that you don't need to call getpid().

That assumes that pthreads is in use, that getpid is poorly implemented/slow
and that enough of the 1% slowdown was due to getpid calls that changing it
would make a difference :-)

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkw3lrsACgkQmOOfHg372QRlvACePV+jbjnvVxC6BsLZq0mvJOiG
AHAAn0yjpqdhxd/MaYnk/lRbtIeMcb/K
=S41l
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] open db cx to fork(2)d children

[Nicolas Williams]

On Fri, Jul 09, 2010 at 02:38:08PM -0700, Roger Binns wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> 
> On 07/09/2010 02:31 PM, Nicolas Williams wrote:
> > The trick to making that go fast is to use pthread_atfork() to get the
> > new PID on the child side of fork() and store the PID in a global
> > variable so that you don't need to call getpid().
> 
> That assumes that pthreads is in use, that getpid is poorly implemented/slow
> and that enough of the 1% slowdown was due to getpid calls that changing it
> would make a difference :-)

I'm spoiled by Solaris, where (since Solaris 10) there's a unified
process model and every process has pthreads (not necessarily more than
one thread, mind you), with pthreads folded into libc.  :)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users