readfile() enhancement request

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

readfile() enhancement request

Tony Papadimitriou
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: readfile() enhancement request

Donald Griggs
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: readfile() enhancement request

J Decker
In reply to this post by Tony Papadimitriou
On Fri, May 17, 2019 at 9:34 AM Tony Papadimitriou <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: readfile() enhancement request

David Raymond
In reply to this post by Tony Papadimitriou
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('<paste over this text when it 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:[hidden email]] 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: readfile() enhancement request

Donald Griggs
Somehow I'd never noticed that.   Great feature!

On Fri, May 17, 2019 at 2:46 PM David Raymond <[hidden email]>
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('<paste over this text when it
> 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:[hidden email]]
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: readfile() enhancement request

Tony Papadimitriou
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 <[hidden email]>
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('<paste over this text when it
> 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:[hidden email]]
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users 

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: readfile() enhancement request

sqlite-10
In reply to this post by Tony Papadimitriou
[hidden email] 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: readfile() enhancement request

Warren Young
On May 17, 2019, at 7:49 PM, [hidden email] 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: readfile() enhancement request

Rowan Worth-2
In reply to this post by Tony Papadimitriou
On Sat, 18 May 2019 at 00:34, Tony Papadimitriou <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: readfile() enhancement request

Jose Isaias Cabrera-4

Rowan Worth, on Monday, May 20, 2019 03:02 AM, wrote...

>On Sat, 18 May 2019 at 00:34, Tony Papadimitriou <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users