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

2017-03-13 Thread J. King 
On March 13, 2017 4:15:57 PM EDT, 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

In case it's useful, see here for all your options:

-- 
J. King
___
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

2017-03-13 Thread Rob Richardson 
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

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

2017-03-13 Thread Brian Curley 
Statistics, even if generated at run-time, might be useful.

One option, at least per the CLI, might be to output a variant of DRH's
last SQL to be read back in. I've no idea as to the portability of this
onto embedded systems, but it works "okay" on the CLI and on desktop
apps...my test file was fairly large and I'm including views, which add to
the overhead.

SELECT

CASE WHEN rowid == (select max(rowid) from sqlite_master where type in
('table','view') and name not like 'sqlite_%')

THEN printf('SELECT "%w", count(*) FROM "%w" ',name,name)

ELSE printf('SELECT "%w", count(*) FROM "%w" UNION',name,name)

END counted

FROM

sqlite_master

WHERE

type

IN ('table','view')

AND name

NOT LIKE 'sqlite_%'

;


Dynamic SQL would be very helpful here, but I haven't seen it on SQLite.
Maybe if an attached db could be leveraged...?

Regards.

Brian P Curley



On Mon, Mar 13, 2017 at 2: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;
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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

2017-03-13 Thread Richard Hipp 
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;


-- 
D. Richard Hipp
d...@sqlite.org
___
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

2017-03-13 Thread Simon Slavin 

On 13 Mar 2017, at 6:40pm, Marco Silva  wrote:

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

The way SQLite stores its data is not helpful to counting the rows.  The number 
of rows isn’t stored anywhere and to find out the number of rows in a table 
SQLite has to iterate through them all.

The fastest way to find the number of rows in a table is

SELECT COUNT(*) FROM MyTable

and I’m afraid there is no simple command which does this for more than one 
table.

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

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

2017-03-13 Thread Marco Silva 
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.

-- 
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] Help file has no information

2017-03-13 Thread Rob Richardson 
Thank you.  That worked.  (The button was labelled "unblock", not "unlock")

RobR

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Graham Holden
Sent: Monday, March 13, 2017 2:26 PM
To: SQLite mailing list
Subject: Re: [sqlite] Help file has no information

Either don't store it on a network drive or right click, select properties and 
unlock (see 
https://social.technet.microsoft.com/Forums/en-US/20700886-2000-4c52-b706-aa1fb32d3dfb/cant-view-chm-file-contents?forum=W8ITProPreRel).
 I think.
Graham

Sent from my Samsung Galaxy S7 - powered by Three
 Original message From: Rob Richardson 
 Date: 13/03/2017  18:14  (GMT+00:00) To: "General 
Discussion of SQLite Database (sqlite-users@mailinglists.sqlite.org)" 
 Subject: [sqlite] Help file has no 
information The help file that is linked from the System.Data.SQLite home page 
appears to have a table of contents but no information.  No matter what page I 
select, the page does not appear.

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

Re: [sqlite] Help file has no information

2017-03-13 Thread Graham Holden 
Either don't store it on a network drive or right click, select properties and 
unlock (see 
https://social.technet.microsoft.com/Forums/en-US/20700886-2000-4c52-b706-aa1fb32d3dfb/cant-view-chm-file-contents?forum=W8ITProPreRel).
 I think.
Graham

Sent from my Samsung Galaxy S7 - powered by Three
 Original message From: Rob Richardson 
 Date: 13/03/2017  18:14  (GMT+00:00) To: "General 
Discussion of SQLite Database (sqlite-users@mailinglists.sqlite.org)" 
 Subject: [sqlite] Help file has no 
information 
The help file that is linked from the System.Data.SQLite home page appears to 
have a table of contents but no information.  No matter what page I select, the 
page does not appear.

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

Re: [sqlite] Help file has no information

2017-03-13 Thread Simon Slavin 

On 13 Mar 2017, at 6:14pm, Rob Richardson  wrote:

> The help file that is linked from the System.Data.SQLite home page appears to 
> have a table of contents but no information.  No matter what page I select, 
> the page does not appear.

This is the result of a bug in Microsoft’s display code.  Please see



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

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

2017-03-13 Thread Rob Richardson 
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] Help file has no information

2017-03-13 Thread Rob Richardson 
The help file that is linked from the System.Data.SQLite home page appears to 
have a table of contents but no information.  No matter what page I select, the 
page does not appear.

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

Re: [sqlite] Where is the official document for System.Data.SQLite?

2017-03-13 Thread Rob Richardson 
Blindness cured.  I had visited that page but missed the super-sized, bold 
headline that said "Documentation for System.Data.SQLite".  

Thanks for opening my eyes.

RobR

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Donald Griggs
Sent: Monday, March 13, 2017 1:57 PM
To: SQLite mailing list
Subject: Re: [sqlite] Where is the official document for System.Data.SQLite?

On Mon, Mar 13, 2017 at 1:44 PM, Rob Richardson 
 wrote:

> Where is the official document for System.Data.SQLite?  And is there a 
> better list than this one to ask questions about System.Data.SQlite?


Hi Rob,

I don't use system.data.sqlite, but I believe you'll find the documentation
at:

http://system.data.sqlite.org

>
>
___
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] Where is the official document for System.Data.SQLite?

2017-03-13 Thread Donald Griggs 
On Mon, Mar 13, 2017 at 1:44 PM, Rob Richardson 
 wrote:

> Where is the official document for System.Data.SQLite?  And is there a
> better list than this one to ask questions about System.Data.SQlite?


Hi Rob,

I don't use system.data.sqlite, but I believe you'll find the documentation
at:

http://system.data.sqlite.org

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