Re: [sqlite] Incremental BLOB IO

[Simon Slavin]

Plenty of examples on the web using the C interface, and the SQLite API is 
clear enough that people can figure out how to use it.  But you can’t use those 
lines of code with System.Data.Sqlite and C#.  I think the OP wants an example 
of using the blob-editing functions though System.Data.Sqlite.  I don’t do 
Windows myself and I have no idea how to do that but I hope someone can provide 
s short example.

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

Re: [sqlite] Incremental BLOB IO

[Keith Medcalf]

Insert the record.  (Use zeroblob(n) to on the blob field to create an empty n 
byte blob.)
Open the blob and do your I/O
Close the blob.
Carry on with the next record.

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Mike King
> Sent: Tuesday, 14 March, 2017 17:34
> To: SQLite mailing list
> Subject: [sqlite] Incremental BLOB IO
> 
> Hi,
> 
> I'm trying to understand incremental BLOB IO using the latest
> System.Data.Sqlite and C#.
> 
> I've got some test code working where I can execute a query and using a
> data reader get a SQLiteBlob object and read the blob back. However, I'm
> not clear as to how I can use incremental IO if I'm doing an Insert of a
> new record.
> 
> I appreciate it's a really cheeky thing to ask, but does anybody have an
> example of this?
> 
> Cheers,
> 
> Mike
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Incremental BLOB IO

[Darko Volaric]

I haven't got an example but how about inserting the record and then
updating the blob in question?

On Wed, Mar 15, 2017 at 12:33 AM, Mike King  wrote:

> Hi,
>
> I'm trying to understand incremental BLOB IO using the latest
> System.Data.Sqlite and C#.
>
> I've got some test code working where I can execute a query and using a
> data reader get a SQLiteBlob object and read the blob back. However, I'm
> not clear as to how I can use incremental IO if I'm doing an Insert of a
> new record.
>
> I appreciate it's a really cheeky thing to ask, but does anybody have an
> example of this?
>
> Cheers,
>
> Mike
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Incremental BLOB IO

[Mike King]

Hi,

I'm trying to understand incremental BLOB IO using the latest
System.Data.Sqlite and C#.

I've got some test code working where I can execute a query and using a
data reader get a SQLiteBlob object and read the blob back. However, I'm
not clear as to how I can use incremental IO if I'm doing an Insert of a
new record.

I appreciate it's a really cheeky thing to ask, but does anybody have an
example of this?

Cheers,

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

[sqlite] FTS5 not working MY MISTAKE FORGET

[Domingo Alvarez Duarte]

Hello !

Sorry by my previous message, it was my mistake when quering the fts5.

I was quering like normal sql referring to a specific column in the 
where clause instead of using the table name.


Cheers !

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

[sqlite] FTS5 not working

[Domingo Alvarez Duarte]

Hello !

I just downloaded the 
http://www.sqlite.org/snapshot/sqlite-snapshot-201703062044.tar.gz 
compiled it with fts5 enabled and then tested it with this:


===

CREATE VIRTUAL TABLE email USING fts5(body);
insert into email(body) values('hello over there');
select rowid, body from email where body match 'over';

===

And get this:

===

sqlite3 < test-fts.sql
Error: near line 3: unable to use function MATCH in the requested context

===

Cheers !

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

Re: [sqlite] A CTE to count the records (rows) for each table

[Marco Silva]

Excerpts from Richard Hipp's message of 2017-03-13 14:47:49 -0400:
> On 3/13/17, Marco Silva  wrote:
> > Hi,
> >
> >  Does anyone knows a Common Table Expression (CTE) to be used with the
> >  sqlite_master table so we can count for each table how many rows it
> >  has.
> 
> That is not possible.  Each table (potentially) has a different
> structure, and so table names cannot be variables in a query - they
> must be specified when the SQL is parsed.
> 
> But you could do this with an extension such as
> https://www.sqlite.org/src/artifact/f971962e92ebb8b0 that implements
> an SQL function that submits new SQL text to the SQLite parser.  For
> example:
> 
>   SELECT name, eval(printf('SELECT count(*) FROM "%w"',name))
>   FROM sqlite_master
>WHERE type='table' AND coalesce(rootpage,0)>0;
> 

Worked pretty well, with the extension you mentioned. Thanks, Dr. Hipp

-- 
Marco Arthur @ (M)arco Creatives
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use parameterized queries in SQLite.Net

[Keith Medcalf]

Cannot resist the classic response as to why one should use parameters rather 
than inline substitution:

https://xkcd.com/327/


> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Chris Locke
> Sent: Tuesday, 14 March, 2017 00:53
> To: SQLite mailing list
> Subject: Re: [sqlite] How to use parameterized queries in SQLite.Net
> 
> From a newbie's point of view, how is this better (if doing it in 'hard
> coded' format like below) than writing this code:
> 
> command.CommandText = string.format("INSERT INTO trend_data (tag_key,
> value, value_timestamp) VALUES ({0}, {1}, {2})",2,234.56,now);
> 
> I can sort of understand it if its in a subroutine, and I appreciate the
> example given was just an example, but whats the advantage of parametized
> queries?
> 
> Sorry if diverting the topic somewhat
> 
> 
> Thanks,
> Chris
> 
> I
> 
> On Mon, Mar 13, 2017 at 8:15 PM, Rob Richardson 
> wrote:
> 
> > To answer my own question:  this works:
> >
> > using (SQLiteCommand command = m_conn.CreateCommand())
> > {
> > command.CommandType = CommandType.Text;
> > command.CommandText = "INSERT INTO trend_data (tag_key,
> > value, value_timestamp) VALUES (?, ?, ?)";
> > SQLiteParameter param;
> > param = new SQLiteParameter();
> > param.Value = 2;
> > command.Parameters.Add(param);
> > param = new SQLiteParameter();
> > param.Value = 234.56;
> > command.Parameters.Add(param);
> > param = new SQLiteParameter();
> > param.Value = DateTime.Now;
> > command.Parameters.Add(param);
> > rowsAffected = command.ExecuteNonQuery();
> > }
> >
> > RobR
> >
> > -Original Message-
> > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> > On Behalf Of Rob Richardson
> > Sent: Monday, March 13, 2017 2:23 PM
> > To: General Discussion of SQLite Database (sqlite-users@mailinglists.
> > sqlite.org)
> > Subject: [sqlite] How to use parameterized queries in SQLite.Net
> >
> > Hello again.
> >
> > Since my attempt to find the official answer for myself has hit a snag,
> > I'll just ask here.
> >
> > The examples I've seen for parameterized queries used with the
> > SQLiteCommand class have shown named parameters, and the names usually
> > begin with an "@" character.  Is that character required for named
> > parameters?  Is that the correct leading character?  Is it required to
> > include that leading character in the name given to the SQLiteParameter
> > object?
> >
> > I'm used to using the System.Data.ODBC classes, which do not support
> named
> > parameters, but they do support unnamed parameters, represented by
> question
> > marks.  The order in which the parameters are attached to the command
> > object determines the association between the parameter object and the
> > query parameter.  Unnamed parameters would be easier for me to work with
> > than named ones.  Does SQlite.Net support unnamed parameters?
> >
> > Thank you.
> >
> > RobR
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] A CTE to count the records (rows) for each table

[Dominique Devienne]

On Tue, Mar 14, 2017 at 10:14 AM, Dominique Devienne 
wrote:

>
> On Mon, Mar 13, 2017 at 7:47 PM, Richard Hipp  wrote:
>
>> On 3/13/17, Marco Silva  wrote:
>> > Hi,
>> >
>> >  Does anyone knows a Common Table Expression (CTE) to be used with the
>> >  sqlite_master table so we can count for each table how many rows it
>> >  has.
>>
>> That is not possible.  Each table (potentially) has a different
>> structure, and so table names cannot be variables in a query - they
>> must be specified when the SQL is parsed.
>>
>> But you could do this with an extension such as
>> https://www.sqlite.org/src/artifact/f971962e92ebb8b0 that implements
>> an SQL function that submits new SQL text to the SQLite parser.  For
>> example:
>>
>>   SELECT name, eval(printf('SELECT count(*) FROM "%w"',name))
>>   FROM sqlite_master
>>WHERE type='table' AND coalesce(rootpage,0)>0;
>>
>
>
> You can also use SQLite to generate SQL text which you feed back into
> SQLite :)
> Below's my little experimentation on that. Nothing new, but was fun
> nonetheless. --DD
>

[...]

> C:\Users\ddevienne>sqlite3.18.0rc test.db "select group_concat('select
> '''||name||''' as name, count(*) from '|| name, ' union all ') from
> sqlite_master where type = 'table' group by type" | sqlite3.18.0rc test.db
> t|3
> u|2
> v|0
>
>

And thinking more about this, what we'd need is a new .eval command to the
official shell,
so we don't have to use two instances of SQLite on the same DB connected
via a pipe.

.eval would run the SQL that follows as usual, but each output row of that
SQL should start
with a text value being to SQL text to evaluate, and we could even imagine
optional extra columns
being the bind parameters of that SQL text, if bind placeholders are used.

Now that would be fun and powerful. That's not dynamic SQL in SQLite
itself, only the shell,
but that would already be very powerful IMHO. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A CTE to count the records (rows) for each table

[Dominique Devienne]

On Mon, Mar 13, 2017 at 7:47 PM, Richard Hipp  wrote:

> On 3/13/17, Marco Silva  wrote:
> > Hi,
> >
> >  Does anyone knows a Common Table Expression (CTE) to be used with the
> >  sqlite_master table so we can count for each table how many rows it
> >  has.
>
> That is not possible.  Each table (potentially) has a different
> structure, and so table names cannot be variables in a query - they
> must be specified when the SQL is parsed.
>
> But you could do this with an extension such as
> https://www.sqlite.org/src/artifact/f971962e92ebb8b0 that implements
> an SQL function that submits new SQL text to the SQLite parser.  For
> example:
>
>   SELECT name, eval(printf('SELECT count(*) FROM "%w"',name))
>   FROM sqlite_master
>WHERE type='table' AND coalesce(rootpage,0)>0;
>


You can also use SQLite to generate SQL text which you feed back into
SQLite :)
Below's my little experimentation on that. Nothing new, but was fun
nonetheless. --DD

C:\Users\ddevienne>sqlite3.18.0rc test.db
SQLite version 3.18.0 2017-03-06 20:44:13
Enter ".help" for usage hints.
sqlite> create table t (id);
sqlite> create table u (id);
sqlite> create table v (id);
sqlite> insert into t values (1), (2), (3);
sqlite> insert into u values (4), (5);
sqlite> .exit

C:\Users\ddevienne>sqlite3.18.0rc test.db "select count(*) from t";
3

C:\Users\ddevienne>sqlite3.18.0rc test.db "select count(*) from u";
2

C:\Users\ddevienne>sqlite3.18.0rc test.db "select count(*) from v";
0

C:\Users\ddevienne>sqlite3.18.0rc test.db "select 'select '''||name||''' as
name, count(*) from '|| name from sqlite_master where type = 'table'"
select 't' as name, count(*) from t
select 'u' as name, count(*) from u
select 'v' as name, count(*) from v

C:\Users\ddevienne>sqlite3.18.0rc test.db "select group_concat('select
'''||name||''' as name, count(*) from '|| name, ' union all ') from
sqlite_master where type = 'table' group by type"
select 't' as name, count(*) from t union all select 'u' as name, count(*)
from u union all select 'v' as name, count(*) from v

C:\Users\ddevienne>sqlite3.18.0rc test.db "select group_concat('select
'''||name||''' as name, count(*) from '|| name, char(10)||'union
all'||char(10)) from sqlite_master where type = 'table' group by type"
select 't' as name, count(*) from t
union all
select 'u' as name, count(*) from u
union all
select 'v' as name, count(*) from v

C:\Users\ddevienne>sqlite3.18.0rc test.db "select group_concat('select
'''||name||''' as name, count(*) from '|| name, ' union all ') from
sqlite_master where type = 'table' group by type" | sqlite3.18.0rc test.db
t|3
u|2
v|0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use parameterized queries in SQLite.Net

[Graham Holden]

The main reason you should parameterise queries is to protect against "SQL 
injection".  "Hardcoded" as below doesn't make much difference, but if the data 
being used comes in any way from an "untrusted" source, then this is 
particularly important.
If, instead of "234.56" below a malicious user could arrange to pass something 
like "2, '14/3/2017'); drop trend_data" then horrible things might happen!
Using parameters stops this, because no (SQL) parsing of the parameter value 
happens.
Graham. 


Sent from my Samsung Galaxy S7 - powered by Three
 Original message From: Chris Locke  
Date: 14/03/2017  06:52  (GMT+00:00) To: SQLite mailing list 
 Subject: Re: [sqlite] How to use 
parameterized queries in SQLite.Net 
From a newbie's point of view, how is this better (if doing it in 'hard
coded' format like below) than writing this code:

command.CommandText = string.format("INSERT INTO trend_data (tag_key,
value, value_timestamp) VALUES ({0}, {1}, {2})",2,234.56,now);

I can sort of understand it if its in a subroutine, and I appreciate the
example given was just an example, but whats the advantage of parametized
queries?

Sorry if diverting the topic somewhat
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use parameterized queries in SQLite.Net

[Clemens Ladisch]

Chris Locke wrote:
> From a newbie's point of view, how is this better (if doing it in 'hard
> coded' format like below) than writing this code:
>
> command.CommandText = string.format("INSERT INTO trend_data (tag_key,
> value, value_timestamp) VALUES ({0}, {1}, {2})",2,234.56,now);

Using parameters is not too much of an improvement in a case like this.

But when you have strings (or values that _could_ be strings because you
don't completely control their source), you have to format them
correctly (many people forget escaping quotes), and you run the risk of
SQL injections: .

And when you already have to use parameters for any query with strings,
it's a good habit to use them everywhere.


Handling parameters is excessively complex in .NET.  It might be a good
idea to write a helper format that is as simple as format():

  db.execute("INSERT INTO tab VALUES (?, ?, ?)", 123, name, now);


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

Re: [sqlite] How to use parameterized queries in SQLite.Net

[Hick Gunter]

A parameterized query enables you to run a fixed query with arbitrary data that 
is unknown during compile time, multiple times (once for each set of 
parameters), without re-preparing the statement (which is costly) in between.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Chris Locke
Gesendet: Dienstag, 14. März 2017 07:53
An: SQLite mailing list 
Betreff: Re: [sqlite] How to use parameterized queries in SQLite.Net

From a newbie's point of view, how is this better (if doing it in 'hard coded' 
format like below) than writing this code:

command.CommandText = string.format("INSERT INTO trend_data (tag_key, value, 
value_timestamp) VALUES ({0}, {1}, {2})",2,234.56,now);

I can sort of understand it if its in a subroutine, and I appreciate the 
example given was just an example, but whats the advantage of parametized 
queries?

Sorry if diverting the topic somewhat


Thanks,
Chris

I

On Mon, Mar 13, 2017 at 8:15 PM, Rob Richardson 
wrote:

> To answer my own question:  this works:
>
> using (SQLiteCommand command = m_conn.CreateCommand())
> {
> command.CommandType = CommandType.Text;
> command.CommandText = "INSERT INTO trend_data
> (tag_key, value, value_timestamp) VALUES (?, ?, ?)";
> SQLiteParameter param;
> param = new SQLiteParameter();
> param.Value = 2;
> command.Parameters.Add(param);
> param = new SQLiteParameter();
> param.Value = 234.56;
> command.Parameters.Add(param);
> param = new SQLiteParameter();
> param.Value = DateTime.Now;
> command.Parameters.Add(param);
> rowsAffected = command.ExecuteNonQuery();
> }
>
> RobR
>
> -Original Message-
> From: sqlite-users
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Rob Richardson
> Sent: Monday, March 13, 2017 2:23 PM
> To: General Discussion of SQLite Database (sqlite-users@mailinglists.
> sqlite.org)
> Subject: [sqlite] How to use parameterized queries in SQLite.Net
>
> Hello again.
>
> Since my attempt to find the official answer for myself has hit a
> snag, I'll just ask here.
>
> The examples I've seen for parameterized queries used with the
> SQLiteCommand class have shown named parameters, and the names usually
> begin with an "@" character.  Is that character required for named
> parameters?  Is that the correct leading character?  Is it required to
> include that leading character in the name given to the
> SQLiteParameter object?
>
> I'm used to using the System.Data.ODBC classes, which do not support
> named parameters, but they do support unnamed parameters, represented
> by question marks.  The order in which the parameters are attached to
> the command object determines the association between the parameter
> object and the query parameter.  Unnamed parameters would be easier
> for me to work with than named ones.  Does SQlite.Net support unnamed 
> parameters?
>
> Thank you.
>
> RobR
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

Re: [sqlite] How to use parameterized queries in SQLite.Net

[Chris Locke]

From a newbie's point of view, how is this better (if doing it in 'hard
coded' format like below) than writing this code:

command.CommandText = string.format("INSERT INTO trend_data (tag_key,
value, value_timestamp) VALUES ({0}, {1}, {2})",2,234.56,now);

I can sort of understand it if its in a subroutine, and I appreciate the
example given was just an example, but whats the advantage of parametized
queries?

Sorry if diverting the topic somewhat


Thanks,
Chris

I

On Mon, Mar 13, 2017 at 8:15 PM, Rob Richardson 
wrote:

> To answer my own question:  this works:
>
> using (SQLiteCommand command = m_conn.CreateCommand())
> {
> command.CommandType = CommandType.Text;
> command.CommandText = "INSERT INTO trend_data (tag_key,
> value, value_timestamp) VALUES (?, ?, ?)";
> SQLiteParameter param;
> param = new SQLiteParameter();
> param.Value = 2;
> command.Parameters.Add(param);
> param = new SQLiteParameter();
> param.Value = 234.56;
> command.Parameters.Add(param);
> param = new SQLiteParameter();
> param.Value = DateTime.Now;
> command.Parameters.Add(param);
> rowsAffected = command.ExecuteNonQuery();
> }
>
> RobR
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Rob Richardson
> Sent: Monday, March 13, 2017 2:23 PM
> To: General Discussion of SQLite Database (sqlite-users@mailinglists.
> sqlite.org)
> Subject: [sqlite] How to use parameterized queries in SQLite.Net
>
> Hello again.
>
> Since my attempt to find the official answer for myself has hit a snag,
> I'll just ask here.
>
> The examples I've seen for parameterized queries used with the
> SQLiteCommand class have shown named parameters, and the names usually
> begin with an "@" character.  Is that character required for named
> parameters?  Is that the correct leading character?  Is it required to
> include that leading character in the name given to the SQLiteParameter
> object?
>
> I'm used to using the System.Data.ODBC classes, which do not support named
> parameters, but they do support unnamed parameters, represented by question
> marks.  The order in which the parameters are attached to the command
> object determines the association between the parameter object and the
> query parameter.  Unnamed parameters would be easier for me to work with
> than named ones.  Does SQlite.Net support unnamed parameters?
>
> Thank you.
>
> RobR
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users