Re: [sqlite] Will someone be able to explain this weird outcome...

[tonyp]

Apparently, in trying to make numbers out of strings, it is interpreted as 
7 - 5 = 2 and the part after the comma is truncated.


-Original Message- 
From: jose isaias cabrera


sqlite> select "7,915" - "5,021";
2

But, would someone explain the result of 2?  Sorry for this child-like
question, but I can't find the how the result of 2 came to be displayed.

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

Re: [sqlite] Will someone be able to explain this weird outcome...

[tonyp]

Well, OK, but you attributed the quote to the wrong person. :)

-Original Message- 
From: Simon Slavin

Sent: Saturday, October 11, 2014 2:38 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Will someone be able to explain this weird outcome...


On 10 Oct 2014, at 9:27pm, to...@acm.org wrote:


sqlite> select "7,915" - "5,021";
2

But, would someone explain the result of 2?  Sorry for this child-like
question, but I can't find the how the result of 2 came to be displayed.


7 - 5 = 2

Simon.
___
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] Saving PDF Files in SQlite

[tonyp]

From the SQLite3 shell (recent version), use the readfile('filename') 
function to import into a blob field, and the writefile('filename',field) 
for exporting back to a file.


See here: http://www.sqlite.org/cli.html

-Original Message- 
From: John Payne

Sent: Thursday, January 15, 2015 10:24 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Saving PDF Files in SQlite



How do I save PDF files in SQLIte? Is there a preferred method? All the
online suggestions seem to require writing some custom code. Is there an
add-on for saving PDF or other digital objects? I'm not a programmer, do
not know php and rather clueless on how to proceed.

Thanks

John Payne
j...@pde-usa.net
630-815-8849

___
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] Proposed enhancement to the sqlite3.exe command-lineshell

[tonyp]

I second the idea of a kind of "WARNING: All your work will be lost, are you 
sure you want to quit? (y/N)" on trying to exit, but *ONLY* if the 
application was started by (double-)clicking on it, otherwise the warning 
will be a nuisance when running test scripts.


-Original Message- 
From: Richard Hipp

Sent: Monday, February 10, 2014 6:15 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Proposed enhancement to the sqlite3.exe 
command-lineshell


What if, instead of opening a standard database, the sqlite3.exe
command-line shell just issued a warning message reminding the user that
they are working on a transient in-memory database and suggesting the use
of the ".open" command to connect to a persistent on-disk database.  Like
in this patch:

http://www.sqlite.org/src/info/90e9deae4a


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

Re: [sqlite] SQLite version 3.8.4 release schedule

[tonyp]

I noticed the message:

Enter SQL statements terminated with a ";"

was removed from the shell.  Is this supposed to be in the right direction 
when a red message is added to warn newbies to SQLite about working with 
in-memory database, yet they are assumed to know that SQL commands end with 
";", when up to now everybody (even seasoned SQLiters) had to be reminded 
about this?


My €0.02 


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

[sqlite] Suggestion for shell .IMPORT improvement

[tonyp]

Hello,

When .importing from a csv file (for example) with a command like ".import 
data.csv data" if the table does not exist, the table is created using the 
first row of data.csv as the field names.
But if the same file is imported a second time, the first line is treated as 
data.


My suggestion is to recognize if the first line matches the current table 
field names, and only if not, treat it as data.  This is because one often 
gets the same CSV (for example) file format, and while the first run 
correctly creates the table, consequent updates insert dummy records (the 
first line which is the header.)


Thanks.

PS. By the way, any progress on the shell enhancement to load/save blobs? 
This will make the shell capable of handling any database without the need 
for external programs.


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

[sqlite] Suggestion for SHELL impovement (built-in scripting)

[tonyp]

Hi all,

First of all, this is not about using SQLite as an embedded library from C, 
or whatever other language.  It does not affect the sqlite3.c file at all. 
It only applies to the shell (so logically, it only affects shell.c)


So here’s my suggestion for what (I feel) is a significant improvement for 
the SHELL version of SQLite without being too much of a programming 
complication in my view.  (Those who usually attack any new concept, please 
pause a moment and give it some thought, then attack as usual!)


Because I primarily use SQLite as a tool (from the shell), and given that 
SQLite does not (yet, if ever) support stored procedures, I often find 
myself (as I believe most people on this list) writing scripts that will do 
a certain task, and then run those using the shell with something like 
“sqlite3 my.db < script.sql”


I think we can all agree that the single most important advantage of SQLite 
is the one-file-holds-everything deal (both for the database file, and for 
the application or library file).  On that principle,...


The problem with having all those scripts separate from the database file 
somehow violate the previous assertion.  Plus, there is the problem that 
scripts cannot be made to have parameters (AFAIK).

So, two birds with one stone, ...

Wouldn’t it be nice if we could have those scripts somehow saved in the 
sqlite_master table (or some other new system table, if this one would cause 
compatibility issues), and then be able to call them very easily from the 
shell with some special prefix (e.g., :SCRIPTNAME parm1 parm2 parm3 ... – or 
some similar simple syntax).  The : character could be some other special 
character (except for . used for built in commands.)


Then the shell, using the simplest of macro expansion techniques of plain 
text replacement, would read each line from the saved script, convert 
occurrence of the each parameter to the text appearing in the invocation and 
run it as if it was just typed on the keyboard.


For example, if my script was:

SELECT ~1~ from ~2~ where name like (‘%~3~%’);

giving the shell command:

:SCRIPTNAME * my_table some_name

would be executed as:

SELECT * from my_table where name like (‘%some_name%’);

I have used ~number~ as a parameter placeholder, but anything that works 
without ambiguities in the grammar would work.


And, then the next line of the script would be executing in a similar manner 
until the whole script is exhausted.


This capability would make the shell capable of supporting very complex 
script-based command-line applications all stored within the same single 
database file!


Thanks for listening (hopefully).

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

Re: [sqlite] Suggestion for SHELL impovement (built-in scripting)

[tonyp]

Stored procedure would be a great addition.  But they would be not in the 
shell, they belong in the library core.

Scripts are already a part of the current functionality.  Only they have to be 
external.  There no IFs, loops, or whatever other constructs.  Those who say 
soon we would want this and that, it is irrelevant.  Because we could want this 
and that even if scripts remain external to the database.

The idea is to simply move scripts inside the db and add parameters (like 
simple in macro processors.)  That's all.

Talking about anything more is a much more involved proposal, and when 
something adds too many complications it will inevitably take forever (if ever) 
to implement.

-Original Message- 
From: J Decker 
Sent: Thursday, June 19, 2014 3:45 AM 
To: General Discussion of SQLite Database 
Subject: Re: [sqlite] Suggestion for SHELL impovement (built-in scripting) 

Stored procedures

variables, a goto(jump/branch) and a conditional so loops can be made;

On the topic of shell results; isn't there a result value of like number of
records inserted ? So something like var a = insert... select $a; and then
test the output sort of?

went searching cause I really ended up avoiding stored procedures because
of the lack of consistency I don't think SQL standard defines such
things... what I saw was very linear  top-down sort of things, which makes
querying a recursive heirarchical table kinda hard to do in a stored
procedure but I guess I was wrong; but they are all different.

http://dev.mysql.com/doc/refman/5.0/en/flow-control-statements.html  (mysql
does have loop constructs)
http://technet.microsoft.com/en-us/library/ms180796(v=sql.105).aspx ( flow
control in M$ SQL  [tsql])

http://en.wikipedia.org/wiki/SQL#Procedural_extensions (standard?) ya ...
almost as many flavors of this as there are databases.  sad.
___
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] SQLite version 3.8.6 coming soon

[tonyp]

src/func.c: The comment on top refers to sqliteRegisterBuildinFunctions() 
but the actual call seems to have been renamed to 
sqliteRegisterGlobalFunctions()


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

Re: [sqlite] 50% faster than 3.7.17

[tonyp]

In trying to see if the new version breaks any of my queries, I ran several 
of them repeatedly, and they all appear to have produced the expected 
output.


The only thing I noticed which maybe of interest in relation to speed 
performance was (with .timer on) that although the first two run time 
numbers (real & user) were consistently smaller in 3.8.7 (when compared to 
3.8.6), the third number (sys) was consistently higher (or same in one 
occasion).  I guess the first number is the actual time (in seconds) it took 
to run the query.  I don't even know what the 2nd and 3rd numbers represent, 
and how or if they maybe related to the first one.  Is that increase in sys 
to be expected?


A few examples from many more I tried that all follow the same pattern (same 
query & database in each case):


3.8.6: Run Time: real 2.434 user 2.386815 sys 0.00
3.8.7: Run Time: real 1.856 user 1.778411 sys 0.062400
---
3.8.6: Run Time: real 584.465 user 560.293192 sys 1.638011
3.8.7: Run Time: real 518.227 user 430.469159 sys 53.617544
---
3.8.6: Run Time: real 2.449 user 2.340015 sys 0.046800
3.8.7: Run Time: real 1.935 user 1.794012 sys 0.046800

(Thank you for two great solutions I use daily -- SQLite3 and Fossil)

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

Re: [sqlite] CLI dump command ignores view dependencies

[tonyp]

On the other hand, creation of a view could ignore the dependencies, and only 
check them at run time.

It's curious that the following sequence seems to behave this way:

create view a as select 1;
create view b as select * from a;
select * from b;
.d
drop view a;
.d
select * from b;--Error: no such table: main.a
create view a as select 3;
select * from b;

---

Apparently, a view can exist without its dependencies, so the order of 
definition should not have to matter.
If it indeed does matter, then shouldn’t dropping view a (in the above example) 
also drop view b, automatically?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] CLI dump command ignores view dependencies

[tonyp]

Exactly!

-Original Message- 
From: John Hascall

Sent: Wednesday, October 01, 2014 5:15 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] CLI dump command ignores view dependencies

I think his point was:

...

But, the existing situation where the first is​ 
illegal but the second is legal has

a certain illogic.

JohnOn Wed, Oct 1, 2014 at 9:05 AM, Stephan Beal  wrote:


On Wed, Oct 1, 2014 at 3:53 PM,  wrote:

> If it indeed does matter, then shouldn’t dropping view a (in the above
> example) also drop view b, automatically?
>

That assumes view 'a' somehow knows that it is the only consumer of 'b',
which it cannot know. Views from other db files, possibly not attached,
might be consumers of 'b'.


--
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But 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


___
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] Shell: .IMPORT does not seem to obey .BAIL setting

[tonyp]

Trying to import data into a table the operation stops on first error (actually 
a rollback seems to be performed) because of column number mismatch.

Why does it not follow the .BAIL ON/OFF setting?  And, why rows before the 
error are removed?

So, in .BAIL OFF mode, I expected errors to display, but the rest of the file 
to be processed.  Actually, this a very nice way to filter somewhat a file from 
unwanted, badly formatted lines.  (Now, the only option seems to be manual 
editing of the text file for each error line, and try again.)

Is this operation by design or a bug?

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

[sqlite] v3.8 .import misbehaves

[tonyp]

When trying to load a data file with ,"", sequences (for empty field), there 
are quote escape related errors.
Manually converting ,"", to ,, allows the file to be loaded.  According to 
RFC4180, the double quote is an escaped quote if found inside a string.  The 
leading quote should not be considered an escaped quote.  The ,"", pattern 
is very common for denoting empty field. 


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

Re: [sqlite] v3.8 .import misbehaves

[tonyp]

For example, here's a sample (header + one line of data) that fails -- a lot 
more lines fail but I cut it down just to show the problem:


"Year","Debt","GDP1","GDP2","RGDP","dRGDP","Infl","debtgdp"
"1833","","49.3275923134","","118.3483703666","","",""

Then, doing
.sep ,
.import data tab

gives error(s).

Replacing double quotes with blanks lets it work.  (Running on Win7, if it 
matters.)


Another less important issue, I think it should put NULLs instead of blanks 
for blank columns.


-Original Message- 
From: Richard Hipp

Sent: Wednesday, August 28, 2013 2:58 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] v3.8 .import misbehaves

On Wed, Aug 28, 2013 at 7:24 AM,  wrote:


When trying to load a data file with ,"", sequences (for empty field),
there are quote escape related errors.
Manually converting ,"", to ,, allows the file to be loaded.  According to
RFC4180, the double quote is an escaped quote if found inside a string.
 The leading quote should not be considered an escaped quote.  The ,"",
pattern is very common for denoting empty field.



Unable to reproduce the problem.  CSV import works according to RFC4180
when I try 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 


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

Re: [sqlite] v3.8 .import misbehaves

[tonyp]

I did.  I just download the precompiled binaries for Windows, and this is 
what I see (for that sample data file):


C:\temp>sqlite3.exe
SQLite version 3.8.0 2013-08-26 04:50:08
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .sep ,
sqlite> .import data tab
data:2: unterminated "-quoted field
sqlite>

-Original Message- 
From: Richard Hipp

Sent: Wednesday, August 28, 2013 3:47 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] v3.8 .import misbehaves

On Wed, Aug 28, 2013 at 8:25 AM,  wrote:


For example, here's a sample (header + one line of data) that fails -- a
lot more lines fail but I cut it down just to show the problem:

"Year","Debt","GDP1","GDP2","**RGDP","dRGDP","Infl","debtgdp"
"1833","","49.3275923134","","**118.3483703666","","",""

Then, doing
.sep ,
.import data tab

gives error(s).



Unable to reproduce the problem.  The example above works correctly for me
using SQLite 3.8.0 on Linux and on Windows8.

Please verify that you are using 3.8.0 (where this problem has been fixed)
and not SQLite version 3.7.17 or earlier.

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

Re: [sqlite] v3.8 .import misbehaves

[tonyp]

OK, now copy the data line several times, and you'll see there are errors 
for several lines, unrelated to the final CRLF (which I removed this next 
sample).


-- data --
"Year","Debt","GDP1","GDP2","RGDP","dRGDP","Infl","debtgdp"
"1833","","49.3275923134","","118.3483703666","","",""
"1833","","49.3275923134","","118.3483703666","","",""
"1833","","49.3275923134","","118.3483703666","","",""
"1833","","49.3275923134","","118.3483703666","","",""
"1833","","49.3275923134","","118.3483703666","","",""
"1833","","49.3275923134","","118.3483703666","","",""
---

C:\temp>sqlite3.exe
SQLite version 3.8.0 2013-08-26 04:50:08
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .sep ,
sqlite> .import data tab
data:3: unescaped " character
data:4: unescaped " character
data:5: unescaped " character
data:6: unescaped " character
data:7: unescaped " character
data:2: expected 8 columns but found 43 - extras ignored
sqlite>

-Original Message- 
From: Simon Davies

Sent: Wednesday, August 28, 2013 4:06 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] v3.8 .import misbehaves

On 28 August 2013 13:51,   wrote:

I did.  I just download the precompiled binaries for Windows, and this is
what I see (for that sample data file):

C:\temp>sqlite3.exe
SQLite version 3.8.0 2013-08-26 04:50:08
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .sep ,
sqlite> .import data tab
data:2: unterminated "-quoted field
sqlite>





I can confirm the behaviour with
C:\>xd -c data.txt
0: 22 59 65 61 72 22 2C 22  44 65 62 74 22 2C 22 47 | "Year","Debt","G
   10: 44 50 31 22 2C 22 47 44  50 32 22 2C 22 52 47 44 | DP1","GDP2","RGD
   20: 50 22 2C 22 64 52 47 44  50 22 2C 22 49 6E 66 6C | P","dRGDP","Infl
   30: 22 2C 22 64 65 62 74 67  64 70 22 0D 0A 22 31 38 | ","debtgdp".."18
   40: 33 33 22 2C 22 22 2C 22  34 39 2E 33 32 37 35 39 | 33","","49.32759
   50: 32 33 31 33 34 22 2C 22  22 2C 22 31 31 38 2E 33 | 23134","","118.3
   60: 34 38 33 37 30 33 36 36  36 22 2C 22 22 2C 22 22 | 483703666","",""
   70: 2C 22 22 0D 0A   | ,""..
C:> sqlite3
SQLite version 3.8.0 2013-08-26 04:50:08
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .sep ,
sqlite>
sqlite> .import data.txt tab
data.txt:2: unterminated "-quoted field

But change file to:

C:\Joinerysoft\Software\sqlite\3_8_0>xd -c data.txt
0: 22 59 65 61 72 22 2C 22  44 65 62 74 22 2C 22 47 | "Year","Debt","G
   10: 44 50 31 22 2C 22 47 44  50 32 22 2C 22 52 47 44 | DP1","GDP2","RGD
   20: 50 22 2C 22 64 52 47 44  50 22 2C 22 49 6E 66 6C | P","dRGDP","Infl
   30: 22 2C 22 64 65 62 74 67  64 70 22 0D 0A 22 31 38 | ","debtgdp".."18
   40: 33 33 22 2C 22 22 2C 22  34 39 2E 33 32 37 35 39 | 33","","49.32759
   50: 32 33 31 33 34 22 2C 22  22 2C 22 31 31 38 2E 33 | 23134","","118.3
   60: 34 38 33 37 30 33 36 36  36 22 2C 22 22 2C 22 22 | 483703666","",""
   70: 2C 22 22 | ,""

(remove trailing newline) and the import works with no errors.

Regards,
Simon
___
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] v3.8 .import misbehaves

[tonyp]

It turns out that CRLF may have something to do with it.

I can get rid of the errors, either:

1. if I replace "" with nothing,
OR 
2. if I save the file as Linux style (LF only).


-Original Message- 
From: to...@acm.org 
Sent: Wednesday, August 28, 2013 4:16 PM 
To: General Discussion of SQLite Database 
Subject: Re: [sqlite] v3.8 .import misbehaves 

OK, now copy the data line several times, and you'll see there are errors 
for several lines, unrelated to the final CRLF (which I removed this next 
sample).


-- data --
"Year","Debt","GDP1","GDP2","RGDP","dRGDP","Infl","debtgdp"
"1833","","49.3275923134","","118.3483703666","","",""
"1833","","49.3275923134","","118.3483703666","","",""
"1833","","49.3275923134","","118.3483703666","","",""
"1833","","49.3275923134","","118.3483703666","","",""
"1833","","49.3275923134","","118.3483703666","","",""
"1833","","49.3275923134","","118.3483703666","","",""
---

C:\temp>sqlite3.exe
SQLite version 3.8.0 2013-08-26 04:50:08
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .sep ,
sqlite> .import data tab
data:3: unescaped " character
data:4: unescaped " character
data:5: unescaped " character
data:6: unescaped " character
data:7: unescaped " character
data:2: expected 8 columns but found 43 - extras ignored
sqlite>

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