open2 or 3 run command using pty

klimenkoandrey has asked for the wisdom of the Perl Monks concerning the following question:

HI i ve got a third party DB. Fo that DB have no drivers at all. But have sql utilities i want to run tis command from perl script and parse output.

when i use just open

my $sql = "select * from users";
open (COMMAND, "| /opt/sql " ) || die $!;
print (COMMAND "connect credentials")
print (COMMAND "$sql\n");
[download]

output fine

--> 4, 4, Reporting, , F, 1, 0, 0, 0, 0, 0, F, 0, F, 0, F, , BLOB(0)
[download]

But when i use just open i can't grab output of $sql command (just in terminal). When i use open2 (and 3 too)

my $sql = "select * from users";
my $pid = open2 (OUT, IN, "/opt/sql") || die $!;
print (IN "connect credentials");
print (IN "$sql\n");
while (<OUT>) {
    print $_;
}
[download]

I've got almost same output but have ">"

--> 4, >4, Reporting, , F, 1, 0, 0, 0, 0, 0, F, 0, F, 0, F, , BLOB(0)
[download]

I guess this trouble in terminal environment (but maybe and not). Can anyone tell me can i grab clear output in this is situation?

Comment on open2 or 3 run command using pty Select or Download Code

Replies are listed 'Best First'.
Re: open2 or 3 run command using pty by Corion (Patriarch) on Jul 21, 2014 at 09:27 UTC
Before trying to run an interactive session, consider trying to run the session in batch mode: `my $tempfile= '/tmp/sql_output'; my $sql = "select * from users"; open (COMMAND, "\| /opt/sql >$tempfile" ) \|\| die $!; print (COMMAND "connect credentials") print (COMMAND "$sql\n"); open my $results, '<', $tempfile or die "Couldn't read '$tempfile': $!"; while( <$results> ) { print $_; };` [download] Trying to run a program interactively usually brings lots of problems when the program is buffering its output. So I prefer to stuff all commands into one string, send that string to the program, and then read the output from a file.	[reply] [d/l]
Re^2: open2 or 3 run command using pty by klimenkoandrey (Initiate) on Jul 21, 2014 at 09:34 UTC
with temp file same situation (i guess sql command insert stupid ">")	[reply]
Re^3: open2 or 3 run command using pty by Corion (Patriarch) on Jul 21, 2014 at 09:38 UTC
Yes, most likely the sql command inserts the "prompt signs" on its own. Maybe you can find in the manual how you can change (or suppress) those signs. Sybase and Oracle use "variables" in their shell which you can use to change the prompt and the output format. Maybe your vendor uses something similar. Alternatively, you could mention the vendor of your database then maybe somebody knows either the appropriate DBD to use or how to make the command line shell output data in a way that you can reuse from within Perl.	[reply]
Re^4: open2 or 3 run command using pty by klimenkoandrey (Initiate) on Jul 21, 2014 at 09:40 UTC
Re^2: open2 or 3 run command using pty by klimenkoandrey (Initiate) on Jul 21, 2014 at 10:03 UTC
i maybe find something: when i use open2 and close Handler (WRITE) at the end of using (end of file) output stopped on last clear line. If close after sending last command, then I've got all output with ">" on next sting after stopped. Think this is buffer troubles. Is possible change size of output buffers manually?	[reply]
Re^3: open2 or 3 run command using pty by Corion (Patriarch) on Jul 21, 2014 at 10:08 UTC
Not that I'm aware of. The size of the output buffers is determined by the child program, not by the parent.	[reply]
Re: open2 or 3 run command using pty by soonix (Chancellor) on Jul 21, 2014 at 10:51 UTC
your SQL Statement is `select * from users` [download] without a trailing semicolon, which means the SQL engine can't (yet) decide wether the statement is complete, or wether you want to go on with "`WHERE whatever IS NULL`" or so. Try `my $sql = "select * from users;";` [download] Update: ... and you didn't `close IN;` before looping over OUT, so even if your SQL engine recognizes EOF as end of statement, it hasn't received it at that point.	[reply] [d/l] [select]
Re^2: open2 or 3 run command using pty by klimenkoandrey (Initiate) on Jul 21, 2014 at 11:33 UTC
The NitroDB SQL engine work without trailing semicolon. The first time i try with it. Yes in example i didn't close HANDLERS. Result the same with semicolons or without Close Handlers or not Now i close HANDLERS right after last sent command. Trouble in the "Inserting > sign" I can't even write output from the read to file (Insert >) Now will try to dig perl files from McAfee (maybe have part to communicate with DB)	[reply]