Re: [sqlite] 3rd Call For Papers - 23rd Annual Tcl/Tk Conference (Tcl'2016)

[john herron]

Sent from Yahoo Mail on Android

From:"Richard Hipp" 
Date:Mon, Sep 5, 2016 at 2:52 PM
Subject:Re: [sqlite] 3rd Call For Papers - 23rd Annual Tcl/Tk Conference
(Tcl'2016)

On 9/5/16, akupr...@shaw.ca  wrote:
>
> Hello SQLite Users, fyi ...
>
> 23rd Annual Tcl/Tk Conference (Tcl'2016)
> http://www.tcl.tk/community/tcl2016/
>

To clarification the relevancy and appropriateness of Andreas's post
above:  SQLite is a TCL extension that has "escaped" into the wide -
perhaps the most famous of all TCL extensions.  And SQLite still
heavily depends on TCL for building from canonical sources and for
testing.

-- 
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] "Responsive" website revamp at www.sqlite.org

[john herron]

Sent from Yahoo Mail on Android

From:"Scott Robison" 
Date:Tue, Sep 6, 2016 at 1:16 PM
Subject:Re: [sqlite] "Responsive" website revamp at www.sqlite.org

Lamding page looks fine on my Galaxy Note 4 phone and cheap Amazon Fire
tablet, both landscape and portrait. Documents by category doesn't like
portrait mode on my tablet (at least).

On Sep 6, 2016 8:38 AM, "Eric Kestler"  wrote:

> Looks quite good and is very readable on my iPhone 6s and iPad Mini 4,
> both portrait and landscape modes.
>
> ..Eric
>
> __
> Plan A is always more effective when the device you are working on
> understands that Plan B involves either a large hammer or screwdriver
> ___
> 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] OutOfMemory exception when returning a small DataTable

[Mr A]

I have a SQLite database that has a single table with 18 million rows and
24 columns. I have written a SQL query function in C#, which I expose to
Excel with ExcelDNA.

This is the complete code,

string constr = constr = "Data Source=" + FilePath +
";Version=3;Synchronous=OFF;temp_store=memory;cache_size=
70;count_changes=off;";
DataTable dt = new DataTable();
try
{
SQLiteConnection conn = new SQLiteConnection(constr);
SQLiteCommand command = new SQLiteCommand(SQLStatement,conn);

conn.Open();
SQLiteDataAdapter sda = new SQLiteDataAdapter(command);

sda.Fill(dt);
sda.Dispose();
command.Dispose();
conn.Dispose();
int numRows = (IncludeHeaders ? dt.Rows.Count + 1 :
dt.Rows.Count);
object[,] ret = new object[numRows, dt.Columns.Count];
int rowCount = 0;
if (IncludeHeaders)
{
int colCount = 0;
foreach (DataColumn col in dt.Columns)
{
ret[rowCount, colCount] = col.ColumnName;
colCount++;
}
}
rowCount = (IncludeHeaders ? 1 : 0);
foreach (DataRow row in dt.Rows)
{
int colCount = 0;
foreach (DataColumn col in dt.Columns)
{
if (row[col] != DBNull.Value)
ret[rowCount, colCount] = row[col];
else
ret[rowCount, colCount] = "";
colCount++;
}
rowCount++;
}
return ret;
}
catch (Exception ex)
{
object[,] err = new object[1, 1];
err[0, 0] = ex.ToString();
return err;
}
finally
{
dt.Clear();
dt.Dispose();
dt = null;
}

If I run the query twice (two hits of Shift+F9), I get an
OutOfMemoryException. In the Task Manager I can see the Working Set(Memory)
of the EXCEL.EXE image go from 200MB to 1500MB before the exception is
thrown.

However, this behavior isn't entirely consistent. Other queries where I
return upto 5 columns and 1100 rows work just fine. I see the memory usage
tick up in the Task Manager and once the results are returned to Excel I
see the memory come back down.

Debugging the application above shows that it trips up at the
`sda.Fill(dt)` line.

Would appreciate any thoughts? Would I be better off using
`SQLiteDataReader` instead? Or are there any other tips or tricks I can
use? Thank you.

Incidentally if I run the exact query via Python I don't get this problem,
so I'd assume it's something to do with the garbage collection in C#.

Here are some details on the database and query. The schema is along the
lines of,

Date (VARCHAR)
CompanyName (VARCHAR)
Amount (REAL)
AggCode (VARCHAR)
Level1 ... Level20 (VARCHAR)

The queries are usually run combining the fields `Level9`, `Level5`,
`AggCode`, `Date`, `CompanyName` in the `WHERE` clause. So apart from the
raw table, I have also configured the following four indices,

CREATE INDEX idx1 on my(Level09, AggCode);
CREATE INDEX idx2 on my(Level05, AggCode);
CREATE INDEX idx3 on my(CompanyName, AggCode);
CREATE INDEX idx4 on my(Date, AggCode);

The query that returns 1100 rows and 2 columns successfully,

SELECT CompanyName, SUM(Amount) FROM my where Level09="T_EU" and
AggCode = "R_S_V" GROUP BY CompanyName ORDER BY SUM(Amount) DESC

The query that throws the memory exception,

SELECT Date, CompanyName, Sum(Amount) FROM my WHERE Level05 ="M_TO" AND
AggCode = "C_DTA" GROUP BY Date, CompanyName

The second query returns 163 rows and 3 columns in Python.

The full stack trace of the exception is below,

System.Data.SQLite.SQLiteException (0x80004005): out of memory
out of memory
at System.Data.SQLite.SQLite3.Reset(SQLiteStatement stmt)
at System.Data.SQLite.SQLite3.Step(SQLiteStatement stmt)
at System.Data.SQLite.SQLiteDataReader.NextResult()
at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd,
CommandBehavior behave)
at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.SQLite.SQLiteCommand.ExecuteDbDataReader(CommandBehavior
behavior)
at 
System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior
behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset,
DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String
srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32
startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at UtilXL.Utils.UtilsSQLite.RunQueryCSLite(String SQLStatement, String
FilePath, 

Re: [sqlite] sqlite3_changes() using 64 bit counters?

[Simon Slavin]

On 10 Dec 2016, at 4:53am, Gelin Yan  wrote:

>  It is my first time to know sqlite is used to store 2^32 rows data.

I can tell you I have a database in which one table takes up 39 Megabytes if 
that helps.

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

[sqlite] Ajqvue 1.11 Released

[dmp]

The Ajqvue project is pleased to release v1.11 to the public. The
release is a minor one to highlight an update to the QueryBuilder
plugin. In addition to the QueryBuilder plugin the application
comes with by the HeatMapper, JavaFX Charts, and Table Field
Profiler plugins. The SQLite JDBC is an included library along
with the World Factbook database.

Dana M. Proctor
Ajqvue Project Manager
http://ajqvue.com

Ajqvue provides an easy to use Java based user interface front-end
for viewing, adding, editing, or deleting entries in several mainstream
databases. A query frame allows the building of complex SQL statements
and a SQL Query Bucket for saving such. The application allows easy
sorting, searching, and import/export of table data. A plug-in framework
has allowed the inclusion of tools to visually build queries, profile and
plot data for analysis.

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

Re: [sqlite] Speaking of Ruby & Sqlite...a weekend oddity for someone

[Don V Nielsen]

You are correct, sir. I discovered in the rake tasks the following
little bit of code. It directs which version of sqlite to download and
compile.

# BINARY_VERSION = "3.8.11.1"
# URL_VERSION= "3081101"
# URL_PATH   = "/2015"

Now I just need to figure out how to reinitiate the rake compile task
after making the following update, then test.

BINARY_VERSION = "3.15.2.0"
URL_VERSION= "3150200"
URL_PATH   = "/2016"

On Fri, Dec 9, 2016 at 5:27 PM, Roger Binns  wrote:
> On 09/12/16 14:09, Don V Nielsen wrote:
>> However,
>> it fails using the sqlite3 gem. The specific exception is "in
>> 'initialize': near "with": syntax error
>
> That will be a SQLite version issue - older SQLite's didn't support
> with.  While you made some effort around versions, whatever is happening
> there is an older library is actually used.
>
> You can use this to find out exactly what library version is used:
>
>   SELECT sqlite_version(), sqlite_source_id();
>
> Also this will give a list of compilation options, to verify:
>
>   PRAGMA compile_options;
>
> Roger
>
>
>
> ___
> 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