Re: [sqlite] how many rows deleted

2012-07-11 Thread deltagam...@gmx.net 

Am 12.07.2012 01:36, schrieb Igor Tandetnik:

On 7/11/2012 7:21 PM, deltagam...@gmx.net wrote:

Can I retrieve how many rows  are affected from a delete statement


sqlite3_changes



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

Re: [sqlite] how many rows deleted

2012-07-11 Thread Simon Davies 
On 12 July 2012 00:21, deltagam...@gmx.net  wrote:
> Can I retrieve how many rows  are affected from a delete statement like
> std::string delete_stmt("DELETE FROM mylog WHERE
> strftime('%Y-%m-%d',thedate) BETWEEN ? AND ? ");
>

http://www.sqlite.org/c3ref/changes.html

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

Re: [sqlite] how many rows deleted

2012-07-11 Thread Igor Tandetnik 

On 7/11/2012 7:21 PM, deltagam...@gmx.net wrote:

Can I retrieve how many rows  are affected from a delete statement


sqlite3_changes

--
Igor Tandetnik

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

[sqlite] how many rows deleted

2012-07-11 Thread deltagam...@gmx.net 

Can I retrieve how many rows  are affected from a delete statement like
std::string delete_stmt("DELETE FROM mylog WHERE 
strftime('%Y-%m-%d',thedate) BETWEEN ? AND ? ");





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

Re: [sqlite] Selecting NULL

2012-07-11 Thread Igor Tandetnik 

On 7/11/2012 2:00 PM, deltagam...@gmx.net wrote:

If mydetails contains a value, and I delete this value with "SQLite
Database Browser"
it seems not to be a "real NULL", has someone an explanation for this
behaviour ?


The tool probably sets the field to an empty string, which is not the 
same as null.

--
Igor Tandetnik

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

Re: [sqlite] Selecting NULL

2012-07-11 Thread Pavel Ivanov 
On Wed, Jul 11, 2012 at 2:00 PM, deltagam...@gmx.net
 wrote:
> If mydetails contains a value, and I delete this value with "SQLite Database
> Browser"
> it seems not to be a "real NULL", has someone an explanation for this
> behaviour ?

It depends on your meaning of word "delete". But maybe when you clear
the value in SQLite Database Browser it writes empty string?

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

Re: [sqlite] Selecting NULL

2012-07-11 Thread deltagam...@gmx.net 

Am 11.07.2012 19:45, schrieb Pavel Ivanov:

 // here maybe NULL is returned ?
 mydetails = (char*)sqlite3_column_text(stmt, 0 );

Check sqlite3_column_type() before calling sqlite3_column_text(). If
it returns SQLITE_NULL then you have NULL.

Pavel


On Wed, Jul 11, 2012 at 1:40 PM, deltagam...@gmx.net
 wrote:

Hello,

how to process if the select-statement selects a column with NULL  ?


==
char *mydetails;
char *sql;
sqlite3_stmt *stmt;
sqlite3 *db;
const char dbname[] = "mysqlite.db";


sql = "Select mydetails from mytable";

rc = sqlite3_prepare(db, sql, strlen(sql), , NULL);

rc = sqlite3_step(stmt);


while(rc == SQLITE_ROW) {

 // here maybe NULL is returned ?
 mydetails = (char*)sqlite3_column_text(stmt, 0 );
 rc = sqlite3_step(stmt);
}
===
___
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


Pavel, thanks a lot.


If mydetails contains a value, and I delete this value with "SQLite 
Database Browser"
it seems not to be a "real NULL", has someone an explanation for this 
behaviour ?



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

Re: [sqlite] Selecting NULL

2012-07-11 Thread Pavel Ivanov 
> // here maybe NULL is returned ?
> mydetails = (char*)sqlite3_column_text(stmt, 0 );

Check sqlite3_column_type() before calling sqlite3_column_text(). If
it returns SQLITE_NULL then you have NULL.

Pavel


On Wed, Jul 11, 2012 at 1:40 PM, deltagam...@gmx.net
 wrote:
> Hello,
>
> how to process if the select-statement selects a column with NULL  ?
>
>
> ==
> char *mydetails;
> char *sql;
> sqlite3_stmt *stmt;
> sqlite3 *db;
> const char dbname[] = "mysqlite.db";
>
>
> sql = "Select mydetails from mytable";
>
> rc = sqlite3_prepare(db, sql, strlen(sql), , NULL);
>
> rc = sqlite3_step(stmt);
>
>
> while(rc == SQLITE_ROW) {
>
> // here maybe NULL is returned ?
> mydetails = (char*)sqlite3_column_text(stmt, 0 );
> rc = sqlite3_step(stmt);
> }
> ===
> ___
> 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] IP address support (was: Consequences of lexicographic sorting of keys in SQLite4?)

2012-07-11 Thread Nico Williams 
On Wed, Jul 11, 2012 at 12:36 PM, Valentin Davydov
 wrote:
> Individual IP addresses are nicely supported in the form of unsigned
> integers, and prefixes/ranges - as contiguous ranges of such integers.
> For example, to determine whether given IP address belongs to a particular
> subnet, one can calculate "IP between NETWORK_MIN and NETWORK_MAX", which
> sqlite does quite efficiently. This is for IPv4 at least.

Using ranges instead of prefixes is fraught with peril.  One has to
have triggers to ensure that ranges start and end on proper
boundaries, and then checking for overlap, or sorting ranges is much
harder than with the bit string approach.  And, as you point out, this
only works for IPv4 (since v6 addresses are 128-bit but SQLite3 only
has 64-bit integers).

That said, it's easy to represent bit strings as BLOBs that sort
properly, so perhaps all we need is a set of user-defined functions
for converting between IP addresses in string form and bit strings
encoded as BLOBs.

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

[sqlite] Selecting NULL

2012-07-11 Thread deltagam...@gmx.net 

Hello,

how to process if the select-statement selects a column with NULL  ?


==
char *mydetails;
char *sql;
sqlite3_stmt *stmt;
sqlite3 *db;
const char dbname[] = "mysqlite.db";


sql = "Select mydetails from mytable";

rc = sqlite3_prepare(db, sql, strlen(sql), , NULL);

rc = sqlite3_step(stmt);


while(rc == SQLITE_ROW) {

// here maybe NULL is returned ?
mydetails = (char*)sqlite3_column_text(stmt, 0 );
rc = sqlite3_step(stmt);
}
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] IP address support (was: Consequences of lexicographic sorting of keys in SQLite4?)

2012-07-11 Thread Valentin Davydov 
On Mon, Jul 02, 2012 at 05:05:25PM +0100, Niall O'Reilly wrote:
> 
> On 2 Jul 2012, at 16:13, Nico Williams wrote:
> 
> > That reminds me: it'd be nice to have a bit string type, since the
> > correct way to sort IPv4 CIDR blocks is as bit strings.
> 
>   Nice, definitely!
> 
> > This is also
> > a proper way to sort IPv6 blocks.  Alternatively, it'd be nice to have
> > native IP address types in SQLite4, as otherwise one has to jump
> > through hoops to handle IP addresses properly.
> 
>   Bit strings would be more general.
>   Native IP would remove a sometimes-asserted motivation for preferring
>   PostgreSQL.
> 
>   As I see it, ranges, as well as single addresses and CIDR prefixes, 
>   need to be supported, perhaps like the Perl Net::IP module does.

Individual IP addresses are nicely supported in the form of unsigned
integers, and prefixes/ranges - as contiguous ranges of such integers.
For example, to determine whether given IP address belongs to a particular
subnet, one can calculate "IP between NETWORK_MIN and NETWORK_MAX", which 
sqlite does quite efficiently. This is for IPv4 at least.

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

Re: [sqlite] Parsing sqlitedb file.

2012-07-11 Thread Richard Hipp 
On Wed, Jul 11, 2012 at 9:29 AM, Alex Caithness <
acaithn...@ccl-forensics.com> wrote:

> Actually, you can recover deleted data from SQLite databases - we've been
> doing it for a while (a number of years). There's at least one product to
> do it for you (http://ccl-forensics.com/epilog)  and there have been a
> number of presentations I have given and been at which discussed the
> subject.
>

The secure_delete
pragmacan be
used to mostly block the recovery of deleted data, for those
applications that really want their content to be irrevocably deleted.


>
>
>
> On 11 Jul 2012, at 7:32am, rsharnagate 
> wrote:
>
> > Actually I want to recover deleted data from sqlitedb file. Deleted data
> > means data from freelist page and unallocated space.
> > That's why I want to parse it.
>
> Sorry, but it's not possible to parse unused areas of pages because you
> lack framing information.  The data may have been written to that page when
> the page was used for something else.  Once that data was deleted, the page
> would have been reused for some other purpose, but not all the bits inside
> the page would have been overwritten, so unused area of the page might
> contain all sorts of remnants from pages of other types, written in any
> order, truncated strangely, and completely unparsable.
>
> Simon.
>
> -
> CCL-Forensics Ltd
> Payton House
> Packwood Court
> Guild Street
> Stratford-upon-Avon
> CV37 6RP
> Registered in England No: 5314495
>
> Legally privileged/Confidential Information may be contained in this
> message. If you are not the addressee(s) legally indicated in this
> message (or responsible for delivery of the message to such person), you
> may not copy or deliver this message to anyone. In such case,
> you should destroy this message, and notify us immediately.
>
> If you or your employer does not consent to Internet e-mail messages of
> this kind, please advise us immediately. Opinions, conclusions
> and other information expressed in this message are not given or endorsed
> by my firm or employer unless otherwise indicated by an
> authorised representative independent of this message.
>
> Please note that neither my employer nor I accept any responsibility for
> viruses and it is your responsibility to scan attachments (if any).
>
> -
> ___
> 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] Parsing sqlitedb file.

2012-07-11 Thread Alex Caithness 
Actually, you can recover deleted data from SQLite databases - we've been doing 
it for a while (a number of years). There's at least one product to do it for 
you (http://ccl-forensics.com/epilog)  and there have been a number of 
presentations I have given and been at which discussed the subject.



On 11 Jul 2012, at 7:32am, rsharnagate  wrote:

> Actually I want to recover deleted data from sqlitedb file. Deleted data
> means data from freelist page and unallocated space.
> That's why I want to parse it.

Sorry, but it's not possible to parse unused areas of pages because you lack 
framing information.  The data may have been written to that page when the page 
was used for something else.  Once that data was deleted, the page would have 
been reused for some other purpose, but not all the bits inside the page would 
have been overwritten, so unused area of the page might contain all sorts of 
remnants from pages of other types, written in any order, truncated strangely, 
and completely unparsable.

Simon.
-
CCL-Forensics Ltd
Payton House
Packwood Court
Guild Street
Stratford-upon-Avon
CV37 6RP
Registered in England No: 5314495

Legally privileged/Confidential Information may be contained in this message. 
If you are not the addressee(s) legally indicated in this 
message (or responsible for delivery of the message to such person), you may 
not copy or deliver this message to anyone. In such case, 
you should destroy this message, and notify us immediately.

If you or your employer does not consent to Internet e-mail messages of this 
kind, please advise us immediately. Opinions, conclusions 
and other information expressed in this message are not given or endorsed by my 
firm or employer unless otherwise indicated by an 
authorised representative independent of this message.

Please note that neither my employer nor I accept any responsibility for 
viruses and it is your responsibility to scan attachments (if any). 
-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite4 locking and concurrency

2012-07-11 Thread Marco Bambini 
Hello,
I am wondering if someone is able to provide some details (or directions) about 
locking and concurrency in sqlite 4.
I guess there should be improvements over sqlite 3 but I am looking for 
information.

Thanks.
--
Marco Bambini
http://www.sqlabs.com








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

Re: [sqlite] Parsing sqlitedb file.

2012-07-11 Thread Simon Slavin 

On 11 Jul 2012, at 7:32am, rsharnagate  wrote:

> Actually I want to recover deleted data from sqlitedb file. Deleted data
> means data from freelist page and unallocated space.
> That's why I want to parse it.

Sorry, but it's not possible to parse unused areas of pages because you lack 
framing information.  The data may have been written to that page when the page 
was used for something else.  Once that data was deleted, the page would have 
been reused for some other purpose, but not all the bits inside the page would 
have been overwritten, so unused area of the page might contain all sorts of 
remnants from pages of other types, written in any order, truncated strangely, 
and completely unparsable.

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

Re: [sqlite] Parsing sqlitedb file.

2012-07-11 Thread rsharnagate 
Hi Andy,
Actually I want to recover deleted data from sqlitedb file. Deleted data
means data from freelist page and unallocated space.
That's why I want to parse it.

On Tue, Jul 10, 2012 at 11:18 PM, Andrew Barnes-6 [via SQLite] <
ml-node+s1065341n63221...@n5.nabble.com> wrote:

>
>
> >
> > Message: 6
> > Date: Mon, 9 Jul 2012 22:17:02 -0700 (PDT)
> > From: rsharnagate <[hidden 
> > email]>
>
> > To: [hidden email] 
> > Subject: Re: [sqlite] Parsing sqlitedb file.
> > Message-ID:
> >

Re: [sqlite] cann't work

2012-07-11 Thread Hector Guilarte 
I am going to quote Drake Wilson, from a thread a couple days old
("[sqlite] DELETE only deletes some records, not others"):

"Aside from the more immediately relevant aspects the other posters
already mentioned, remember that double quotation marks in SQL are
normally used for _identifiers_.  For string literals, use single
quotation marks.  SQLite will sort of autocorrect the former into the
latter sometimes, but it is not good practice to rely on this."



On Wed, Jul 11, 2012 at 2:36 PM, Rob Richardson wrote:

> Does SQLite care about the use of double quotes instead of single quotes?
>
> RobR
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:
> sqlite-users-boun...@sqlite.org] On Behalf Of Simon Davies
> Sent: Wednesday, July 11, 2012 5:10 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] cann't work
>
> On 11 July 2012 10:00, YAN HONG YE  wrote:
> > two same structure tables, when use this sql:
> > insert into table2  values(select * from table1 where filename like
> "%55");
> >
> > but show error: sqlite error 1 - near "select": syntax error
>
> insert into table2 select * from table1 where filename like "%55";
>
> http://www.sqlite.org/lang_insert.html
>
> Regards,
> Simon
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] cann't work

2012-07-11 Thread Rob Richardson 
Does SQLite care about the use of double quotes instead of single quotes?

RobR

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Simon Davies
Sent: Wednesday, July 11, 2012 5:10 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] cann't work

On 11 July 2012 10:00, YAN HONG YE  wrote:
> two same structure tables, when use this sql:
> insert into table2  values(select * from table1 where filename like "%55");
>
> but show error: sqlite error 1 - near "select": syntax error

insert into table2 select * from table1 where filename like "%55";

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

Regards,
Simon
___
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] cann't work

2012-07-11 Thread Simon Davies 
On 11 July 2012 10:00, YAN HONG YE  wrote:
> two same structure tables, when use this sql:
> insert into table2  values(select * from table1 where filename like "%55");
>
> but show error: sqlite error 1 - near "select": syntax error

insert into table2 select * from table1 where filename like "%55";

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

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

[sqlite] cann't work

2012-07-11 Thread YAN HONG YE 
two same structure tables, when use this sql:
insert into table2  values(select * from table1 where filename like "%55");

but show error: sqlite error 1 - near "select": syntax error
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users