Re: [sqlite] readfile() enhancement request
Rowan Worth, on Monday, May 20, 2019 03:02 AM, wrote...
>On Sat, 18 May 2019 at 00:34, Tony Papadimitriou wrote:
>
>> It’s quite often (for me, at least) the case I need to do something like
>> this from the command line:
>>
>> >sqlite3.exe my.db “insert into t values(‘simple field’,’multi-line text
>> copied from some other app’)
>>
>> The problem is the multi-line text cannot be copy-pasted directly into the
>> command line as the first newline will terminate the command. So, I’ve
>> been using readline() like so:
>>
>
>I haven't tested on windows, but FWIW the interactive sqlite3 shell has no
>such limitation - statements are free to span multiple lines. ie. you can
>type:
>
> sqlite> INSERT INTO T VALUES('simple field', '«paste-
> ...> multi-line-
> ...> content»')
> ...> ;
>
>I guess you'd also prefer to not have to type out the full INSERT statement
>each time, but this kind of seems like a shell problem more than an sqlite
>problem!
The only problem with this, and some of the previous copy and paste ideas is
that if there is a single quote (') in that pasted data, it can mess up the
data. If you know that there is not going to be a ' in there, you're ok.
Otherwise, I have been bitten by this a few times, where I have to do more work
than necessary. Just my $US 0.02. Thanks.
josé
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] readfile() enhancement request
On Sat, 18 May 2019 at 00:34, Tony Papadimitriou wrote:
> It’s quite often (for me, at least) the case I need to do something like
> this from the command line:
>
> >sqlite3.exe my.db “insert into t values(‘simple field’,’multi-line text
> copied from some other app’)
>
> The problem is the multi-line text cannot be copy-pasted directly into the
> command line as the first newline will terminate the command. So, I’ve
> been using readline() like so:
>
I haven't tested on windows, but FWIW the interactive sqlite3 shell has no
such limitation - statements are free to span multiple lines. ie. you can
type:
sqlite> INSERT INTO T VALUES('simple field', '«paste-
...> multi-line-
...> content»')
...> ;
I guess you'd also prefer to not have to type out the full INSERT statement
each time, but this kind of seems like a shell problem more than an sqlite
problem!
-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] readfile() enhancement request
On May 17, 2019, at 7:49 PM, sql...@zzo38computer.org wrote: > > (For Macintosh you may need to change "xclip -o" to the proper command on > Macintosh, pbpaste > For Windows, this extension is unlikely to work There are pipes in the NT line of kernels, and there are ways to tie that to stdin or stdout of a Windows Console process, but you’d have had to go out of your way in your extension to make use of these facilities. The easiest way is to use the Visual C++ _popen() function, which emulates the POSIX popen() function, but it looks like your extension isn’t written in terms of popen(). Plan B would be to run SQLite and your extension under Cygwin or WSL, either of which should work well with this extension. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] readfile() enhancement request
sqlite-users@mailinglists.sqlite.org wrote:
> It's quite often (for me, at least) the case I need to do something like this
> from the command line:
>
> >sqlite3.exe my.db "insert into t values(`simple field','multi-line text
> >copied
> >from some other app')
>
> The problem is the multi-line text cannot be copy-pasted directly into the
> command line as the first newline will terminate the command. So, I've been
> using readline() like so:
>
> First, save the copied text into some arbitrary file (e.g., xxx), and then do
>
> >sqlite3.exe my.db "insert into t values(`simple field',readfile(`xxx'))
If you are using a UNIX-based system, you can try my "pipe" extension, which
would allow you to write:
insert into t values('simple field',cast(pipe('','xclip -o') as text));
You can download this and other extensions at:
http://zzo38computer.org/sql/sqlext.zip
(For Macintosh you may need to change "xclip -o" to the proper command on
Macintosh, which I don't know. For Windows, this extension is unlikely to work,
but you can try if you want to.)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] readfile() enhancement request
Me neither. Thanks for that tip. It works great for my use case!
Thanks to all for their suggestions. I also liked the getclip.bat script.
-Original Message-
From: Donald Griggs
Sent: Friday, May 17, 2019 11:40 PM
To: SQLite mailing list
Subject: Re: [sqlite] readfile() enhancement request
Somehow I'd never noticed that. Great feature!
On Fri, May 17, 2019 at 2:46 PM David Raymond
wrote:
Are you on an OS with a working edit() function?
https://www.sqlite.org/cli.html#the_edit_sql_function
In Windows using notepad I can do this for example:
insert into t values ('simple field', edit('', 'notepad'));
You can even use it for multiple fields and it'll open one at a time
insert into t (field1, field2) values (edit('This is for field1',
'notepad'), edit('This is for field2', 'notepad'));
-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
On Behalf Of Tony Papadimitriou
Sent: Friday, May 17, 2019 12:34 PM
To: SQLite mailing list
Subject: [sqlite] readfile() enhancement request
It’s quite often (for me, at least) the case I need to do something like
this from the command line:
>sqlite3.exe my.db “insert into t values(‘simple field’,’multi-line text
copied from some other app’)
The problem is the multi-line text cannot be copy-pasted directly into the
command line as the first newline will terminate the command. So, I’ve
been using readline() like so:
First, save the copied text into some arbitrary file (e.g., xxx), and then
do
>sqlite3.exe my.db “insert into t values(‘simple field’,readfile(‘xxx’))
What would be much easier is for readfile to accept standard input when
provided with no empty name,
i.e., readfile(‘’).
Then, it would read standard input (i.e., wait for me to type/paste my
text) until CTRL-Z/D.
---
>sqlite3.exe my.db “insert into t values(‘simple field’,readfile(‘’))
My text typed/pasted here
...
...
CTRL-Z/D
---
This could also be used to capture directly into the db the output of some
other program without first having to save it to a file.
I’m not sure how multiple readfile(‘’) on the same command should be
handled, either not allowed, or they all get a copy of the same input.
Thanks.
___
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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] readfile() enhancement request
Somehow I'd never noticed that. Great feature!
On Fri, May 17, 2019 at 2:46 PM David Raymond
wrote:
> Are you on an OS with a working edit() function?
> https://www.sqlite.org/cli.html#the_edit_sql_function
>
> In Windows using notepad I can do this for example:
>
> insert into t values ('simple field', edit(' opens>', 'notepad'));
>
> You can even use it for multiple fields and it'll open one at a time
>
> insert into t (field1, field2) values (edit('This is for field1',
> 'notepad'), edit('This is for field2', 'notepad'));
>
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Tony Papadimitriou
> Sent: Friday, May 17, 2019 12:34 PM
> To: SQLite mailing list
> Subject: [sqlite] readfile() enhancement request
>
> It’s quite often (for me, at least) the case I need to do something like
> this from the command line:
>
> >sqlite3.exe my.db “insert into t values(‘simple field’,’multi-line text
> copied from some other app’)
>
> The problem is the multi-line text cannot be copy-pasted directly into the
> command line as the first newline will terminate the command. So, I’ve
> been using readline() like so:
>
> First, save the copied text into some arbitrary file (e.g., xxx), and then
> do
>
> >sqlite3.exe my.db “insert into t values(‘simple field’,readfile(‘xxx’))
>
> What would be much easier is for readfile to accept standard input when
> provided with no empty name,
> i.e., readfile(‘’).
>
> Then, it would read standard input (i.e., wait for me to type/paste my
> text) until CTRL-Z/D.
>
> ---
> >sqlite3.exe my.db “insert into t values(‘simple field’,readfile(‘’))
> My text typed/pasted here
> ...
> ...
> CTRL-Z/D
> ---
>
> This could also be used to capture directly into the db the output of some
> other program without first having to save it to a file.
>
> I’m not sure how multiple readfile(‘’) on the same command should be
> handled, either not allowed, or they all get a copy of the same input.
>
> Thanks.
> ___
> 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] readfile() enhancement request
Are you on an OS with a working edit() function?
https://www.sqlite.org/cli.html#the_edit_sql_function
In Windows using notepad I can do this for example:
insert into t values ('simple field', edit('', 'notepad'));
You can even use it for multiple fields and it'll open one at a time
insert into t (field1, field2) values (edit('This is for field1', 'notepad'),
edit('This is for field2', 'notepad'));
-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of Tony Papadimitriou
Sent: Friday, May 17, 2019 12:34 PM
To: SQLite mailing list
Subject: [sqlite] readfile() enhancement request
It’s quite often (for me, at least) the case I need to do something like this
from the command line:
>sqlite3.exe my.db “insert into t values(‘simple field’,’multi-line text copied
>from some other app’)
The problem is the multi-line text cannot be copy-pasted directly into the
command line as the first newline will terminate the command. So, I’ve been
using readline() like so:
First, save the copied text into some arbitrary file (e.g., xxx), and then do
>sqlite3.exe my.db “insert into t values(‘simple field’,readfile(‘xxx’))
What would be much easier is for readfile to accept standard input when
provided with no empty name,
i.e., readfile(‘’).
Then, it would read standard input (i.e., wait for me to type/paste my text)
until CTRL-Z/D.
---
>sqlite3.exe my.db “insert into t values(‘simple field’,readfile(‘’))
My text typed/pasted here
...
...
CTRL-Z/D
---
This could also be used to capture directly into the db the output of some
other program without first having to save it to a file.
I’m not sure how multiple readfile(‘’) on the same command should be handled,
either not allowed, or they all get a copy of the same input.
Thanks.
___
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] readfile() enhancement request
On Fri, May 17, 2019 at 9:34 AM Tony Papadimitriou wrote: > It’s quite often (for me, at least) the case I need to do something like > this from the command line: > > >sqlite3.exe my.db “insert into t values(‘simple field’,’multi-line text > copied from some other app’) > > type con | sqlite3 my.db insert into t values(‘simple field’,’multi-line text copied from some other app’) ctrl-z cat | sqlite3 my.db insert into t values(‘simple field’,’multi-line text copied from some other app’) ctrl-D > The problem is the multi-line text cannot be copy-pasted directly into the > command line as the first newline will terminate the command. So, I’ve > been using readline() like so: > > First, save the copied text into some arbitrary file (e.g., xxx), and then > do > > >sqlite3.exe my.db “insert into t values(‘simple field’,readfile(‘xxx’)) > > What would be much easier is for readfile to accept standard input when > provided with no empty name, > i.e., readfile(‘’). > > Then, it would read standard input (i.e., wait for me to type/paste my > text) until CTRL-Z/D. > > --- > >sqlite3.exe my.db “insert into t values(‘simple field’,readfile(‘’)) > My text typed/pasted here > ... > ... > CTRL-Z/D > --- > > This could also be used to capture directly into the db the output of some > other program without first having to save it to a file. > > I’m not sure how multiple readfile(‘’) on the same command should be > handled, either not allowed, or they all get a copy of the same input. > > Thanks. > ___ > 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] readfile() enhancement request
Hi Tony,
Regarding "The problem is the multi-line text cannot be copy-pasted
directly into the command line as the first newline will terminate the
command."
I don't know what operating system you're using. If it's not a tiny
embedded OS, I wondered if you might want to use one of the many sqlite GUI
utilities available.
If that's not suitable, and you're on windows, there's a chance a small
batch file along the lines below would help. It automatically pastes the
clipboard into a file.
First, make a general-purpose cmd file named "getClip.cmd" containing:
@echo off
(
echo set objHTML = CreateObject("htmlfile"^)
echo ClipboardText = objHTML.ParentWindow.ClipboardData.GetData("text"^)
echo set objFSO = CreateObject("Scripting.FileSystemObject"^)
echo set objFile = objFSO.OpenTextFile("clip.txt", 2, true^)
echo objFile.WriteLine ClipboardText
echo objFile.Close
) > "%temp%\clip.vbs"
"%temp%\clip.vbs"
=
Now create a little batch file for your particular purpose along the lines
of:
=
rem Send clipboard contents to the file "clip.txt"
call getClip.cmd
IF ERRORLEVEL 1 GOTO :ClipEmpty
rem Now invoke sqlite3.exe to use readfile() to import clip.txt into a blob.
etc. etc.
=
(I used to use a nice free utility named "ClipOut.exe" instead of the
"getClip" above, but it worked only with older Windows versions.)
Just a guess, Linux may have a more elegant solution.
Donald
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
