Re: [sqlite] System.Data.SQLite - Exception Calling SQLiteModule.DeclareTable

[Hick Gunter]

I have always wondered why people will insist on using human readable column 
names (with embedded spaces and special characters) in the implementation layer 
(SQL code) instead of the presentation layer (user interface). The clutter 
introduced into queries by having to quote the column names by far outweighs 
any gain from having "some strange field name" displayed instead of 
some_strange_field_name...

-Ursprüngliche Nachricht-
Von: Mike Nicolino [mailto:mike.nicol...@centrify.com]
Gesendet: Donnerstag, 22. Jänner 2015 02:17
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] System.Data.SQLite - Exception Calling 
SQLiteModule.DeclareTable

Figured this one out.  DeclareTable doesn't like any 'quoting' around the 
column names in the sql.  It works fine with just straight column names.


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Mike Nicolino
Sent: Saturday, January 17, 2015 6:17 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] System.Data.SQLite - Exception Calling 
SQLiteModule.DeclareTable

I'm getting an exception calling SQLiteModule.DeclareTable that seems to imply 
the 'create table' sql being passed is invalid: "SQL logic error or missing 
database".  Yet using that same sql on the same connection as a create table 
call succeeds.  Reviewing the virtual table docs don't imply there are 
restrictions on the create table sql for virtual tables so I'm at a loss to 
what's wrong.

The create table sql (the line breaks here are for readability and not present 
in the actual string send to DeclareTable):

create table xxx(
"Username" text,
"DisplayName" text,
"Email" text,
"LastLogin" integer,
"LastInvite" integer,
"Status" text,
"SourceDs" text,
"Data" text,
"SourceDsLocalized" text
)

Anyone have any input on what might be wrong?
Thanks!

___
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


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

Re: [sqlite] Shell tool question

[Hick Gunter]

Yes. I'm using several layers of .read files to load the appropriate subset of 
extensions for each class of process (OLTP, user query, subsystem, specific 
tools,...)

-Ursprüngliche Nachricht-
Von: Simon Slavin [mailto:slav...@bigfraud.org]
Gesendet: Mittwoch, 21. Jänner 2015 17:35
An: General Discussion of SQLite Database
Betreff: [sqlite] Shell tool question

Quickie:

In the Shell Tool, is '.read' recursive ?  In other words, if I use .read to 
read a script and I have .read in a script, will the shell tool finish both 
scripts correctly ?

I ran a quick test and I know what I got, but I wanted to be reassured I wasn't 
just lucky.

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

[sqlite] regarding sqlite3_prepare_v2() func

[Sairam Gaddam]

I have one doubt regarding sqlite code.
I have 2 programs-one with sqlite3_exec() included in the code and in other
it is not included.I included those files which are zmain.c and zmain1.c
respectively.
First i created a database and added a table "em" and added some contents
to it,then i executed and prepared the sqlite select query.
I added "printf("result");" in the "case op_resultrow" of the function
sqlite3vdbeexec().
Here when i executed zmain.c,
sqlite3_prepare_v2(db, sql, strlen(sql) + 1, , NULL);
the above statement didn't print anything.

OUTPUT:
  database opened successfully
  result
  result
  result
  result
  Operation done successfully
  Before
  Afterprep
(clearly there is nothing between before and afterprep)

But when i commented the sqlite3_exec() in zmain1.c
due to sqlite_prepare_v2() function,the program somehow entered
sqlite3vdbeexec() and printed what i gave in my printf statement.
OUTPUT:
database opened successfully
Operation done successfully
Before
result
result
Afterprep
(clearly there are some output between before and afterprep)

My doubt is why the function call sqlite3_prepare_v2() called
sqlite3vdbeexec in second case and why not in first program.

zmain.c :-   http://pastebin.com/ggmw9VTE
zmain1.c :- http://pastebin.com/xbgVLAyL
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] regarding sqlite3_prepare_v2() func

[Dan Kennedy]

On 01/22/2015 11:53 AM, Sairam Gaddam wrote:

I have one doubt regarding sqlite code.
I have 2 programs-one with sqlite3_exec() included in the code and in other
it is not included.I included those files which are zmain.c and zmain1.c
respectively.
First i created a database and added a table "em" and added some contents
to it,then i executed and prepared the sqlite select query.
I added "printf("result");" in the "case op_resultrow" of the function
sqlite3vdbeexec().
Here when i executed zmain.c,
sqlite3_prepare_v2(db, sql, strlen(sql) + 1, , NULL);
the above statement didn't print anything.

OUTPUT:
   database opened successfully
   result
   result
   result
   result
   Operation done successfully
   Before
   Afterprep
(clearly there is nothing between before and afterprep)

But when i commented the sqlite3_exec() in zmain1.c
due to sqlite_prepare_v2() function,the program somehow entered
sqlite3vdbeexec() and printed what i gave in my printf statement.
OUTPUT:
 database opened successfully
 Operation done successfully
 Before
 result
 result
 Afterprep
(clearly there are some output between before and afterprep)

My doubt is why the function call sqlite3_prepare_v2() called
sqlite3vdbeexec in second case and why not in first program.


This mailing list strips attachments. So you will need to upload code to 
pastebin or similar or inline it in the mail so that we can see it.


The first call to sqlite3_prepare_v2() needed to load the database 
schema into memory. It does this by executing a regular "SELECT ..." 
statement on the sqlite_master table, which involves calling 
sqlite3VdbeExec(). Via sqlite3_exec(), as it happens.


Dan.







___
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] regarding sqlite3_prepare_v2() func

[Sairam Gaddam]

I have one doubt regarding sqlite code.
I have 2 programs-one with sqlite3_exec() included in the code and in other
it is not included.I included those files which are zmain.c and zmain1.c
respectively.
First i created a database and added a table "em" and added some contents
to it,then i executed and prepared the sqlite select query.
I added "printf("result");" in the "case op_resultrow" of the function
sqlite3vdbeexec().
Here when i executed zmain.c,
sqlite3_prepare_v2(db, sql, strlen(sql) + 1, , NULL);
the above statement didn't print anything.

OUTPUT:
  database opened successfully
  result
  result
  result
  result
  Operation done successfully
  Before
  Afterprep
(clearly there is nothing between before and afterprep)

But when i commented the sqlite3_exec() in zmain1.c
due to sqlite_prepare_v2() function,the program somehow entered
sqlite3vdbeexec() and printed what i gave in my printf statement.
OUTPUT:
database opened successfully
Operation done successfully
Before
result
result
Afterprep
(clearly there are some output between before and afterprep)

My doubt is why the function call sqlite3_prepare_v2() called
sqlite3vdbeexec in second case and why not in first program.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] System.Data.SQLite - Exception Calling SQLiteModule.DeclareTable

[Mike Nicolino]

Figured this one out.  DeclareTable doesn't like any 'quoting' around the 
column names in the sql.  It works fine with just straight column names.


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Mike Nicolino
Sent: Saturday, January 17, 2015 6:17 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] System.Data.SQLite - Exception Calling 
SQLiteModule.DeclareTable

I'm getting an exception calling SQLiteModule.DeclareTable that seems to imply 
the 'create table' sql being passed is invalid: "SQL logic error or missing 
database".  Yet using that same sql on the same connection as a create table 
call succeeds.  Reviewing the virtual table docs don't imply there are 
restrictions on the create table sql for virtual tables so I'm at a loss to 
what's wrong.

The create table sql (the line breaks here are for readability and not present 
in the actual string send to DeclareTable):

create table xxx(
"Username" text,
"DisplayName" text,
"Email" text,
"LastLogin" integer,
"LastInvite" integer,
"Status" text,
"SourceDs" text,
"Data" text,
"SourceDsLocalized" text
)

Anyone have any input on what might be wrong?
Thanks!

___
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] excel vba use sqlite

[Simon Slavin]

On 22 Jan 2015, at 1:04am, YAN HONG YE  wrote:

> I don't know how to use sqlite in EXCEL vba?  Need I install sqlite connect 
> driver?



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

[sqlite] excel vba use sqlite

[YAN HONG YE]

I don't know how to use sqlite in EXCEL vba?  Need I install sqlite connect 
driver?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Shell tool question

[Simon Slavin]

On 21 Jan 2015, at 5:15pm, Richard Hipp  wrote:

> It appears to be, after a quick glance at the code.  It uses a new
> stack frame for each level of recursion.  So if you create a recursion
> loop, you'll overflow the stack and segfault.

That's great.  Thanks for the fast answer.  I will be careful not to create a 
loop.

As to why I need this ... I am using a language which has no SQLite library.  
It writes a script and then makes the shell tool run it.  But the script it 
makes has a long standard part to it I wanted to leave in another text file.  
This lets me do it.

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

Re: [sqlite] Shell tool question

[Richard Hipp]

On 1/21/15, Simon Slavin  wrote:
> Quickie:
>
> In the Shell Tool, is '.read' recursive ?  In other words, if I use .read to
> read a script and I have .read in a script, will the shell tool finish both
> scripts correctly ?

It appears to be, after a quick glance at the code.  It uses a new
stack frame for each level of recursion.  So if you create a recursion
loop, you'll overflow the stack and segfault.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Shell tool question

[Simon Slavin]

Quickie:

In the Shell Tool, is '.read' recursive ?  In other words, if I use .read to 
read a script and I have .read in a script, will the shell tool finish both 
scripts correctly ?

I ran a quick test and I know what I got, but I wanted to be reassured I wasn't 
just lucky.

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

Re: [sqlite] Huge WAL log

[Jan Slodicka]

Here is my final report.

The problem was definitely in indexes. Simply the larger is the index table
as compared to the page cache size, the faster growths the WAL log.

My solution (all these measures were important):
- Drop the index before the bulk insert, create it at the end.
- Intermediate commit after each 20,000 inserts.
- Page cache was increased 10x.

After applying above changes the WAL size decreased to less than 1/2 of the
DB size.

Secondary improvements:
- Speed increase by some 25%
- Better responsiveness: Max. time a single SQLite operation takes is 80
secs now. (Was 30+ minutes.)

My suggestion to SQLite developers:
Please update the WAL documentation (namely the place where you warn against
large transactions) by explaining potential risks. These things are not
obvious, at least for me.

To illustrate the last point here is a single SQL commands that caused WAL
log to grow 5x larger than the DB size:

DELETE FROM discount WHERE discounttypeid NOT IN (SELECT discounttypeid FROM
discounttype)






--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Huge-WAL-log-tp79991p80185.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] Update Statements using Sub query

[MikeSnow]

thanks Simon



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Update-Statements-using-Sub-query-tp80175p80181.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] Whish List for 2015

[Dominique Devienne]

On Mon, Jan 19, 2015 at 2:01 AM, Stefan Keller  wrote:

> Adding JSON to SQLite (like in PostgreSQL) ultimately needs a
> JavaScript parser - and that seems to be against the goal of SQLite
> being slim.
>

It needs a JSON parser, not a JavaScript parser. That's much smaller.

Plus DRH already wrote one for UnQL.

But many people are not asking for SQLite to have built-in JSON support
(although I'd welcome it!), but to support low-level mechanisms to allow
such support to be built externally *and* efficiently.

Custom functions already allow some support, but efficient indexing cannot
be emulated (AFAIK) w/o resorting to vtables, which is impractical (IMHO).

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