Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Support for oracle sqlcl ansiconsole format #194

Closed
muhmud opened this issue Oct 30, 2021 · 33 comments
Closed

Support for oracle sqlcl ansiconsole format #194

muhmud opened this issue Oct 30, 2021 · 33 comments

Comments

@muhmud
Copy link

muhmud commented Oct 30, 2021

sqlcl is the modern command line tool from Oracle that is a much better alternative to sqlplus.

It would be great if pspg could support this format, as it's possible to use scripting to get it to output to a pager.

oracle-sqlcl.output.txt
@okbob
Copy link
Owner

okbob commented Oct 30, 2021 via email

@muhmud
Copy link
Author

muhmud commented Oct 30, 2021

sqlcl has some of the most advanced scripting functionality I've ever seen in a command line SQL client. I'm able to write JavaScript that uses the Java libraries and control everything, even getting it to output to a pager, which I've just tested successfully.

I'm doing this for my qsh project.

Thanks for the pipe, I'll give that a go for now!

@muhmud
Copy link
Author

muhmud commented Oct 30, 2021

I came up with this in the end:

sed '1d; 2s/\x1b\[[0-9;]*[mG]//g; 3s/_/-/g' oracle-sqlcl.output.txt | pspg

I think is probably good enough for what I need to do, can close this one also if you want.

@okbob
Copy link
Owner

okbob commented Oct 30, 2021 via email

@muhmud
Copy link
Author

muhmud commented Oct 30, 2021

Any ideas why this file would be weird in pspg? If you scroll the first row, the second row doesn't scroll.

strange.txt

@okbob
Copy link
Owner

okbob commented Oct 30, 2021 via email

@muhmud
Copy link
Author

muhmud commented Oct 30, 2021

OK, understand, will try & figure out where that line went, thanks

@okbob
Copy link
Owner

okbob commented Oct 30, 2021 via email

@okbob
Copy link
Owner

okbob commented Oct 31, 2021 •
edited
Loading

I checked more documentation, and I think so ANSICONSOLE format cannot be supported. SQLcl mix lines with column names with line with underscore together, and I cannot to emulate this effect in pspg. And printing the line with underscores on separate line doesn't look well (I'll implemented, but the it will not be officially supported). I think so DEFAULT format can be supported. Just the problem with missing footer line should be fixed. I checked documentation, and sometimes number of rows was printed, sometimes not.

@okbob
Copy link
Owner

okbob commented Oct 31, 2021

with last commit I can read all files that you sent. Please, can you check it?

@muhmud
Copy link
Author

muhmud commented Oct 31, 2021

Hey, that's great! Will take & look tomorrow and let you know

@muhmud
Copy link
Author

muhmud commented Nov 1, 2021

Just tried it out & everything is working great :)

I saw that when the column headers are repeated it kind of works, but I think this will be fine. There is a sqlcl (and sqlplus) setting called pagesize, however, the maximum value you can set it to is only 50000. I decompiled the Java code for sqlcl and found that this is used to actually populate a sqlcl context property called script.runner.setpagesize, which you can set directly to any value, so I update this to 1000000, which I think should work for most cases.

Having the footer not be a problem is great, and it's also really cool that it works with ansiconsole format. I wanted people to able to use a different pager if they didn't have pspg installed, so this will mean it works in less.

Top job!

@okbob
Copy link
Owner

okbob commented Nov 1, 2021 via email

@muhmud
Copy link
Author

muhmud commented Nov 1, 2021

Yes, absolutely, will do a pull request; this will reference qsh, as I currently haven't seen anybody else make paging work for it.

I thinking I could also do a very cutdown single script you could use just for the paging functionality, will include that also

@okbob
Copy link
Owner

okbob commented Nov 1, 2021 via email

@muhmud
Copy link
Author

muhmud commented Nov 3, 2021

Hey, got a slight issue with the attached file, the column name gets repeated.

sqlcl.output.txt

@okbob
Copy link
Owner

okbob commented Nov 5, 2021

Can me send a screenshots? I see correct output. What options do you use for pspg?

@muhmud
Copy link
Author

muhmud commented Nov 5, 2021

I've got $PSPG set to -XFb --no-bars --no-mouse --no-scrollbar --bold-labels -s 17.

This is what I'm seeing:

image

@okbob
Copy link
Owner

okbob commented Nov 5, 2021 via email

@okbob
Copy link
Owner

okbob commented Nov 5, 2021

last commit should to fix it. Please, check it.

@muhmud
Copy link
Author

muhmud commented Nov 6, 2021

I'm using the latest commit on master, but something is definitely wrong; I don't seem to get any output for all types of different files that normally work fine.

I tried with the current pspg release and they work, but with the build from master I get nothing. Try the sqlcl.output.txt file from before

@okbob
Copy link
Owner

okbob commented Nov 6, 2021 •
edited
Loading

There is something strange - I cannot to reproduce it. It is working on my comp without any problem.

Can you recheck compilation? Can you send me a log file? run pspg with parameter --log

@muhmud
Copy link
Author

muhmud commented Nov 7, 2021

Yep, not sure what is going on. I've recloned the repo and did a new build, but I'm getting the same thing.

I attached the configure and make logs for the build and the log files for both the original and new versions. The new version thinks there are no rows in the file for some reason.

pspg-new.log
pspg-orig.log
make.log
configure.log

@okbob
Copy link
Owner

okbob commented Nov 7, 2021 via email

@muhmud
Copy link
Author

muhmud commented Nov 7, 2021

Actually, I think I've found the issue: if I pipe into pspg I get the problem, but if I use the file as a parameter it's fine :)

@okbob
Copy link
Owner

okbob commented Nov 7, 2021 via email

@muhmud
Copy link
Author

muhmud commented Nov 7, 2021

Yep, I'm using a pipe, but this has always worked for me before. I've tried in xterm and I'm getting the same issue.

I'm running Manjaro linux, and I don't think my setup is crazy.

@okbob
Copy link
Owner

okbob commented Nov 7, 2021 via email

@okbob
Copy link
Owner

okbob commented Nov 7, 2021 via email

@muhmud
Copy link
Author

muhmud commented Nov 7, 2021

👍 👍

@okbob
Copy link
Owner

okbob commented Nov 7, 2021

please. can you check last commit?

@muhmud
Copy link
Author

muhmud commented Nov 7, 2021

Looks good!

@okbob
Copy link
Owner

okbob commented Nov 7, 2021 via email

@okbob okbob closed this as completed Nov 8, 2021
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@okbob @muhmud