Re: [sqlite] .import error: cannot open large file

[lpryszcz]

Hi, Often I pipe tables (from .gz or multiple files). I found it also work
for large files that otherwise fail with `Error: cannot open :huge.file"`:

cat huge.file | sqlite3 somedb '.import /dev/stdin hugetable'

But it could be slower that using `real` file import. Anyone have an idea?

L. 



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/import-error-cannot-open-large-file-tp27346p72364.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] SQLite ADO.NET for .Net Compact Framework 3.9

[Joe Mistachkin]

Paolo Patierno wrote:
>
> I want to compile SQLite managed code for .Net Compact Framework 3.9 (to
> run on Windows Embedded Compact 2013). What is the better way to do this ?
>

If you have the Windows Embedded Compact 2013 SDK installed, you may be able
to import the Visual Studio 2008 project into Visual Studio 2012.

--
Joe Mistachkin

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

Re: [sqlite] Could not load file or assembly 'System.Data.SQLite.dll' or one of its dependencies on Windows XP. Ideas?

[j . merrill]

My recollection is that in your first message (to which I replied [to this 
list] that your one crashing user might have been someone using 32-bit Windows; 
almost all XP installs are 32-bit) you said that you had built things 64-bit.

You cannot deploy a 64-bit build on 32-bit Windows XP and expect it to work. 
The error message "no matching native image" corresponds to that problem -- a 
64-bit native image cannot load (it will not "match") on a 32-bit system.

However, you could deploy a 32-bit build on 64-bit Windows. (Just as one 
example, Visual Studio is a 32-bit application and of course works fine on 
64-bit Windows.) Unless your application needs to access more than (at least) 
1.5gb of memory, building as 64-bit gains very little if anything. 

Do you know why the decision was made to build 64-bit? That removes the 
possibility of deploying to 32-bit Windows XP (unless you ALSO build a separate 
32-bit version) -- something that apparently is/was not obvious to the people 
who decided that.

J. Merrill

-Original Message-
Date: Wed, 13 Nov 2013 22:39:04 -0800
From: "Andreas Hofmann" 
To: "'General Discussion of SQLite Database'"
   
Subject: Re: [sqlite] Could not load file or assembly 'System.Data.SQLite.dll' 
or one of its dependencies on Windows XP. Ideas?

Even more information. I used fuslogvw.exe to see the binding errors.  It
seems that the assembly of System.SQLite.Data.dll was loaded fine:

LOG: GAC Lookup was unsuccessful.
LOG: Attempting download of new URL file:///C:/Program
Files/N1MMLogger+/System.Data.SQLite.DLL.
LOG: Assembly download was successful. Attempting setup of file: C:\Program
Files\N1MMLogger+\System.Data.SQLite.dll
LOG: Entering run-from-source setup phase.
LOG: Assembly Name is: System.Data.SQLite, Version=1.0.86.0,
Culture=neutral, PublicKeyToken=db937bc2d44ff139
LOG: Binding succeeds. Returns assembly from C:\Program
Files\N1MMLogger+\System.Data.SQLite.dll.
LOG: Assembly is loaded in default load context.

But the native image was not:

OG: Initial PrivatePath = NULL
LOG: Dynamic Base = NULL
LOG: Cache Base = NULL
LOG: AppName = N1MMLogger.net.exe
Calling assembly : N1MMLogger.net, Version=1.0.0.0, Culture=neutral,
PublicKeyToken=null.
===
LOG: Start binding of native image System.Data.SQLite, Version=1.0.86.0,
Culture=neutral, PublicKeyToken=db937bc2d44ff139.
WRN: No matching native image found.

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

Re: [sqlite] Simple string question

[RSmith]

Suppose I have a table with one text column. I insert like this:

INSERT INTO my_table (col1) VALUES ('arbitrary UTF-8 string');

* Isn't it true that the string must indeed be surrounded by single quotes as I 
do above?
* Isn't it true that I have to replace all occurrences of ' in the original 
string with '' (to escape each single quote)?
* Do I have to do anything else at all?



I never know how to answer questions starting in the negative.
Do I answer "Yes it isn't", or "No it isn't"?

Basically if you don't wish to or cannot use the SQLite Prepare-Bind-Step sequencing, a simple execute will do the trick (which is 
equal to the alternative except with you wielding less control).


One note of caution (since your question is very reminscent of one who is having UTF-8 troubles and double-checking his work) - Some 
systems / Platforms do not use UTF-8 natively or use some kind of intermediary thing that is typically ANSI-based and translates 
easily into UTF-8 or UTF-16 or Unicode. (unless it is really old). Windows/Linux/OSX/etc. all provide native routines for 
translating text into different code-pages, but I found some of those fail and simply yield an empty result as opposed to an error 
when encountering specific invalid characters (or more specifically, incorrect byte-sequence) for the requested formatting.

(I cannot speak much for the embedded community, but I'm sure parrallel issues 
exist.)

SQLite can bind values in both UTF-8 type character arrays or UTF-16 type (there are different functions for it) but it must be 
already correctly encoded and also be aware that a database file might be in a non-UTF-8 format and you cannot change this after a 
Schema is added [*Citation needed].

See: http://www.sqlite.org/pragma.html#pragma_encoding

I hope this helps your quest.

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

Re: [sqlite] GROUP BY

[Giuseppe Costanzi]

Yeah, something like

SELECT order_id,issued,reference, company, department_id, SUM(quantity) AS
quantity, SUM(dispatch) AS dispatch, SUM(surplus) AS surplus

FROM orders_surplus

GROUP BY order_id

I have however to sum quantity, dispatch and surplus fields to force the
view to calculate the right values

to get that this

order_idissuedcompany   dep_id  qty,dispatch   surplus
> "1""12-11-2013"  "Siemens"  "1" "6""4""2"
> "1""12-11-2013"  "Siemens"  "1" "2""2""0"

becomes this

order_idissuedcompany   dep_id  qty,dispatch   surplus
> "1""12-11-2013"  "Siemens"  "1" "8""6""2"

that is I want the total sum of quantity field (6+2=8), the total sum of
the dispatch field (4+2=2)
and the result of their subtraction (8-6=2)
thanks jim


On Thu, Nov 14, 2013 at 2:35 PM, Jim Callahan <
jim.callahan.orla...@gmail.com> wrote:

> How to query and/or group complex SQL?
>
> Add the language to create a SQL VIEW before your SELECT statement:
>
> CREATE VIEW viewname AS SELECT [your SQL]
> http://www.sqlite.org/lang_createview.html
>
> Then use the viewname in a second SELECT statement as you would a table.
>
> SELECT order_id, issued,  company,  dep_id,  qty, dispatch,  surplus
> FROM viewname
> WHERE order_id = 1;
>
> or, if necessary
>
> WHERE order_id = '1';
>
> How to query and/or group:
> ...complex SQL...
>
> order_idissuedcompany   dep_id  qty,dispatch   surplus
> > "1""12-11-2013"  "Siemens"  "1" "6""4""2"
> > "1""12-11-2013"  "Siemens"  "1" "2""2""0"
> > "2""13-11-2013"  "Siemens"  "2" "10"   "10"   "0"
> > "3""13-11-2013"  "Siemens"  "8" "3""3""0"
> > How I can group by order_id? In the example I'would return on order_id
>
> =1:
>
>
> I assume your SQL is already producing the output in your example.
> Hope I haven't missed your point and this helps.
>
> Jim
>
> On Thu, Nov 14, 2013 at 2:48 AM, Giuseppe Costanzi <
> giuseppecosta...@gmail.com> wrote:
>
> > hi to everybody,
> > you excuse for the preceding mails but I have had problems with this and
> I
> > have had to change provider.
> > However I propose my question.
> > I have this query, that you also see in attachment file.
> > SELECT
> > orders.order_id AS order_id,
> > strftime('%d-%m-%Y', orders.issued) AS issued,
> > suppliers.company AS company,
> > departments.department_id AS dep_id,
> > order_details.quantity AS qty,
> >
> > SUM(CASE WHEN transactions.category_id =  1  THEN 1  ELSE 0 END) AS
> > dispatch,
> >
> > order_details.quantity -  SUM(CASE WHEN transactions.category_id =  1
> > THEN 1  ELSE 0 END) AS surplus
> >
> > FROM orders
> > INNER JOIN departments ON (departments.department_id =
> > orders.department_id)
> >
> > INNER JOIN suppliers ON (suppliers.supplier_id = orders.supplier_id)
> > INNER JOIN order_details ON (orders.order_id = order_details.order_id)
> > INNER JOIN transactions ON order_details.order_detail_id =
> > transactions.order_detail_id
> >
> > WHERE  orders.state = 0 AND orders.enable =1
> > GROUP BY order_details.order_detail_id
> >
> > that return such as
> >
> > order_idissuedcompany   dep_id  qty,dispatch   surplus
> > "1""12-11-2013"  "Siemens"  "1" "6""4""2"
> > "1""12-11-2013"  "Siemens"  "1" "2""2""0"
> > "2""13-11-2013"  "Siemens"  "2" "10"   "10"   "0"
> > "3""13-11-2013"  "Siemens"  "8" "3""3""0"
> >
> > How I can group by order_id? In the example I'would return on order_id
> > =1:
> >
> > order_idissuedcompany   dep_id  qty,dispatch   surplus
> > "1""12-11-2013"  "Siemens"  "1" "8""6""2"
> >
> > any suggestions?
> >
> > regards beppe
> >
> > ___
> > 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] Foreign key vs index (continue)

[Igor Tandetnik]

On 11/13/2013 11:14 PM, Igor Korot wrote:

But then in order to speed up this query I need to create an index
leaguescorehitter(scoreid), right?


I don't know. My crystal ball is cloudy lately, I can't quite make out 
your database schema from here.

--
Igor Tandetnik

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

Re: [sqlite] Simple string question

[Simon Slavin]

On 14 Nov 2013, at 12:58pm, L. Wood  wrote:

> Suppose I have a table with one text column. I insert like this:
> 
> INSERT INTO my_table (col1) VALUES ('arbitrary UTF-8 string');
> 
> * Isn't it true that the string must indeed be surrounded by single quotes as 
> I do above?
> * Isn't it true that I have to replace all occurrences of ' in the original 
> string with '' (to escape each single quote)?
> * Do I have to do anything else at all? 

In addition to the other responses to your questions, if you do choose to 
supply the whole command as an executable string, then the answers to your 
questions are yes, yes and no.

If you want a way to test SQL command I advise you to download and use the 
SQLite shell tool:



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

Re: [sqlite] GROUP BY

[Jim Callahan]

How to query and/or group complex SQL?

Add the language to create a SQL VIEW before your SELECT statement:

CREATE VIEW viewname AS SELECT [your SQL]
http://www.sqlite.org/lang_createview.html

Then use the viewname in a second SELECT statement as you would a table.

SELECT order_id, issued,  company,  dep_id,  qty, dispatch,  surplus
FROM viewname
WHERE order_id = 1;

or, if necessary

WHERE order_id = '1';

How to query and/or group:
...complex SQL...

order_idissuedcompany   dep_id  qty,dispatch   surplus
> "1""12-11-2013"  "Siemens"  "1" "6""4""2"
> "1""12-11-2013"  "Siemens"  "1" "2""2""0"
> "2""13-11-2013"  "Siemens"  "2" "10"   "10"   "0"
> "3""13-11-2013"  "Siemens"  "8" "3""3""0"
> How I can group by order_id? In the example I'would return on order_id

=1:


I assume your SQL is already producing the output in your example.
Hope I haven't missed your point and this helps.

Jim

On Thu, Nov 14, 2013 at 2:48 AM, Giuseppe Costanzi <
giuseppecosta...@gmail.com> wrote:

> hi to everybody,
> you excuse for the preceding mails but I have had problems with this and I
> have had to change provider.
> However I propose my question.
> I have this query, that you also see in attachment file.
> SELECT
> orders.order_id AS order_id,
> strftime('%d-%m-%Y', orders.issued) AS issued,
> suppliers.company AS company,
> departments.department_id AS dep_id,
> order_details.quantity AS qty,
>
> SUM(CASE WHEN transactions.category_id =  1  THEN 1  ELSE 0 END) AS
> dispatch,
>
> order_details.quantity -  SUM(CASE WHEN transactions.category_id =  1
> THEN 1  ELSE 0 END) AS surplus
>
> FROM orders
> INNER JOIN departments ON (departments.department_id =
> orders.department_id)
>
> INNER JOIN suppliers ON (suppliers.supplier_id = orders.supplier_id)
> INNER JOIN order_details ON (orders.order_id = order_details.order_id)
> INNER JOIN transactions ON order_details.order_detail_id =
> transactions.order_detail_id
>
> WHERE  orders.state = 0 AND orders.enable =1
> GROUP BY order_details.order_detail_id
>
> that return such as
>
> order_idissuedcompany   dep_id  qty,dispatch   surplus
> "1""12-11-2013"  "Siemens"  "1" "6""4""2"
> "1""12-11-2013"  "Siemens"  "1" "2""2""0"
> "2""13-11-2013"  "Siemens"  "2" "10"   "10"   "0"
> "3""13-11-2013"  "Siemens"  "8" "3""3""0"
>
> How I can group by order_id? In the example I'would return on order_id
> =1:
>
> order_idissuedcompany   dep_id  qty,dispatch   surplus
> "1""12-11-2013"  "Siemens"  "1" "8""6""2"
>
> any suggestions?
>
> regards beppe
>
> ___
> 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] SQLite ADO.NET for .Net Compact Framework 3.9

[Paolo Patierno]

Hi,
I want to compile SQLite managed code for .Net Compact Framework 3.9 (to
run on Windows Embedded Compact 2013). What is the better way to do this ?

Paolo.

-- 
*Ing. Paolo Patierno*
*Software Engineer / Software Embedded Engineer*

Blogs on* DotNetCampania, TinyCLR & Embedded101*





   


Follow me on *LinkedIn, Twitter & MSDN*






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

Re: [sqlite] Simple string question

[Stephan Beal]

On Thu, Nov 14, 2013 at 2:14 PM, Richard Hipp  wrote:

> On Thu, Nov 14, 2013 at 7:58 AM, L. Wood  wrote:
>
> > Suppose I have a table with one text column. I insert like this:
> >
> > INSERT INTO my_table (col1) VALUES ('arbitrary UTF-8 string');
> >
> > * Isn't it true that the string must indeed be surrounded by single
> quotes
> > as I do above?
> > * Isn't it true that I have to replace all occurrences of ' in the
> > original string with '' (to escape each single quote)?
> ...



> It is safer and faster to use the sqlite3_bind_text() interface.
>

Also useful, if you can't use the bind() interfaces for some reason, is
sqlite3_mprintf(), which includes custom formatting specifiers which take
care of the quoting:

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

Specifically, see the %q and %Q format specifiers.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Since tyranny's the only guaranteed byproduct of those who insist on a
perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple string question

[Richard Hipp]

On Thu, Nov 14, 2013 at 7:58 AM, L. Wood  wrote:

> Suppose I have a table with one text column. I insert like this:
>
> INSERT INTO my_table (col1) VALUES ('arbitrary UTF-8 string');
>
> * Isn't it true that the string must indeed be surrounded by single quotes
> as I do above?
> * Isn't it true that I have to replace all occurrences of ' in the
> original string with '' (to escape each single quote)?
> * Do I have to do anything else at all?
>

It is safer and faster to use the sqlite3_bind_text() interface.

First prepare your statement like this:

   INSERT INTO my_table(col1) VALUES(?1);

Then run:

   sqlite3_bind_text(pStmt, 1, zYourString, -1, SQLITE_TRANSIENT);

Then run your statement:

   sqlite3_step(pStmt);

Further information: http://www.sqlite.org/c3ref/bind_blob.html


-- 
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] Simple string question

[L. Wood]

Suppose I have a table with one text column. I insert like this:

INSERT INTO my_table (col1) VALUES ('arbitrary UTF-8 string');

* Isn't it true that the string must indeed be surrounded by single quotes as I 
do above?
* Isn't it true that I have to replace all occurrences of ' in the original 
string with '' (to escape each single quote)?
* Do I have to do anything else at all? 
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Enable WAL on a QNX system

[Richard Hipp]

On Thu, Nov 14, 2013 at 4:42 AM, Sandu Buraga wrote:

> I am starting to believe that WAL is not a viable choice on a QNX system.
>

All Blackberry phones and tables use it.
-- 
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] GROUP BY

[Giuseppe Costanzi]

HI clemens I've resolve with

SELECT order_id,issued,reference, company, dep_id, SUM(qty) AS quantity,
SUM(dispatch) AS dispatch, SUM(surplus) AS surplus

FROM(
SELECT
  orders.order_id AS order_id,
orders.reference AS reference,
  strftime('%d-%m-%Y', orders.issued) AS issued,
suppliers.company AS company,
orders.department_id AS dep_id,
order_details.qty ,
SUM(CASE WHEN transactions.category_id =  1  THEN 1  ELSE 0 END) AS
dispatch,
  order_details.quantity -  SUM(CASE WHEN transactions.category_id =  1
 THEN 1  ELSE 0 END) AS surplus

FROM orders
INNER JOIN suppliers ON (suppliers.supplier_id = orders.supplier_id)
INNER JOIN order_details ON (orders.order_id = order_details.order_id)
LEFT JOIN transactions ON order_details.order_detail_id =
transactions.order_detail_id

WHERE  orders.state = 0 AND orders.enable =1
GROUP BY order_details.order_detail_id)
GROUP BY order_id


On Thu, Nov 14, 2013 at 11:04 AM, Clemens Ladisch wrote:

> Giuseppe Costanzi wrote:
> > order_idissuedcompany   dep_id  qty,dispatch   surplus
> > "1""12-11-2013"  "Siemens"  "1" "6""4""2"
> > "1""12-11-2013"  "Siemens"  "1" "2""2""0"
> > "2""13-11-2013"  "Siemens"  "2" "10"   "10"   "0"
> > "3""13-11-2013"  "Siemens"  "8" "3""3""0"
> >
> > How I can group by order_id?
>
> By writing "group by order_id".
>
> > In the example I'would return on order_id =1:
> >
> > order_idissuedcompany   dep_id  qty,dispatch   surplus
> > "1""12-11-2013"  "Siemens"  "1" "8""6""2"
>
> Also add SUM(...) where needed.
>
>
> Regards,
> Clemens
> ___
> 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] GROUP BY

[Clemens Ladisch]

Giuseppe Costanzi wrote:
> order_idissuedcompany   dep_id  qty,dispatch   surplus
> "1""12-11-2013"  "Siemens"  "1" "6""4""2"
> "1""12-11-2013"  "Siemens"  "1" "2""2""0"
> "2""13-11-2013"  "Siemens"  "2" "10"   "10"   "0"
> "3""13-11-2013"  "Siemens"  "8" "3""3""0"
>
> How I can group by order_id?

By writing "group by order_id".

> In the example I'would return on order_id =1:
>
> order_idissuedcompany   dep_id  qty,dispatch   surplus
> "1""12-11-2013"  "Siemens"  "1" "8""6""2"

Also add SUM(...) where needed.


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

Re: [sqlite] Enable WAL on a QNX system

[Sandu Buraga]

For a QNX system Richard suggested to set locking mode EXCLUSIVE before
trying to set the WAL journal mode.

When PRAGMA main.journal_mode=WAL is executed, the sqlite code checks
whether EXCLUSIVE lock is enabled, or if shared memory is supported. The
shared-memory implementation from sqlite is based on POSIX API. The QNX OS
is not 100% POSIX compliant even if the company behind says so.

If it is not set locking-mode EXCLUSIVE, WAL will not be enabled on a QNX
system, since there is not shared memory support.

For the SQLITE_OPEN_WAL you are right, it makes no difference.

I am starting to believe that WAL is not a viable choice on a QNX system.

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

Re: [sqlite] What takes the most time

[Zsbán Ambrus]

On Wed, Nov 13, 2013 at 11:50 PM, David de Regt  wrote:
> If a single _step() call takes a long time to execute (a few minutes), is my 
> only option to just wait for it? Does SQLite not allow any kind of callback 
> mechanism for each _step() to indicate how many percentages are done (or how 
> many bytes have been read/written), and allow for cancellation of the process?

See the sqlite3_progress_handler function, see
http://sqlite.org/c3ref/progress_handler.html ; also possibly the
sqlite3_update_hook function.

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