Support for oracle sqlcl ansiconsole format

[muhmud]

Support for oracle sqlcl ansiconsole format

[okbob]

Hi so 30. 10. 2021 v 11:08 odesílatel Muhmud Ahmad ***@***.***> napsal:

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.

Does sqlcl support PAGER setting? This is a basic prerequisite. This format is not too far to Postgres's format border=0. Unfortunately there are some empty lines more, and unsupported escape sequences. With this pipe, the pspg can recognize tabular format `cat oracle-sqlcl.output.txt | ansi2txt | grep '\S' | sed 's/_/-/g' | pspg` This pipe sequence is not too robust, but it can be good enough for testing, if pspg can be used from sqlcl oracle-sqlcl.output.txt

…

<https://github.com/okbob/pspg/files/7446143/oracle-sqlcl.output.txt> — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#194>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAEFO4ZM7W4WMPFHBTWBC3TUJOYZ5ANCNFSM5HA3GNWQ> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>.

[muhmud]

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]

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]

so 30. 10. 2021 v 12:02 odesílatel Muhmud Ahmad ***@***.***> napsal:

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.

It can be an interesting feature, I think. But I need to collect more informations about sqlcl —

…

You are receiving this because you commented. Reply to this email directly, view it on GitHub <#194 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAEFO47LECASLB6K64Q7RELUJO7CNANCNFSM5HA3GNWQ> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>.

[muhmud]

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]

so 30. 10. 2021 v 18:11 odesílatel Muhmud Ahmad ***@***.***> napsal:

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

yes, pspg expects this is the footer row (or this line is part of the footer). Footer has independent horizontal scrolling. There is some heuristic that tries to detect if the last row is a footer line or not. Postgres has a stricter format. Footer starts by "(", but there is some support for other databases without footer. The file from morning had a footer line "131 rows selected.". This has not, and it is a problem.

…

strange.txt <https://github.com/okbob/pspg/files/7447072/strange.txt> — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#194 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAEFO44NKQH3CEOSJHKNUHTUJQKLBANCNFSM5HA3GNWQ> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>.

[muhmud]

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

[okbob]

so 30. 10. 2021 v 18:26 odesílatel Muhmud Ahmad ***@***.***> napsal:

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

sqlcl has csv format. Probably it will be best. You need to switch pspg to csv format with --csv option set sqlformat csv

…

— You are receiving this because you commented. Reply to this email directly, view it on GitHub <#194 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAEFO46JLCEFEPTUBNTZK7TUJQMCBANCNFSM5HA3GNWQ> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>.

[okbob]

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]

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

[muhmud]

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

[muhmud]

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]

po 1. 11. 2021 v 7:01 odesílatel Muhmud Ahmad ***@***.***> napsal:

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!

Great. Thank you for checking. Please, can you write some sentences about how to use and configure SQLcl together with pspg to pspg's README? https://github.com/okbob/pspg/blob/master/README.md —

…

You are receiving this because you commented. Reply to this email directly, view it on GitHub <#194 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAEFO42C6AXQRPFYOI5XQRTUJYUKVANCNFSM5HA3GNWQ> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>.

[muhmud]

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]

po 1. 11. 2021 v 7:20 odesílatel Muhmud Ahmad ***@***.***> napsal:

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

super Thank you Pavel

…

— You are receiving this because you commented. Reply to this email directly, view it on GitHub <#194 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAEFO464WEFGRN4S7VGBTATUJYWRPANCNFSM5HA3GNWQ> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>.

[muhmud]

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

sqlcl.output.txt

[okbob]

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

[muhmud]

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

This is what I'm seeing:

[okbob]

pá 5. 11. 2021 v 7:31 odesílatel Muhmud Ahmad ***@***.***> napsal:

I've got $PSPG set to -XFb --no-bars --no-mouse --no-scrollbar --bold-labels -s 17. This is what I'm seeing:

with these options I am able to reproduce this bug [image: image]

…

<https://user-images.githubusercontent.com/7550982/140468134-7300c452-caf3-4021-90c4-5ccc6f99f78e.png> — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#194 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAEFO4ZMTEEP26NVR5APYILUKN233ANCNFSM5HA3GNWQ> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>.

[okbob]

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

[muhmud]

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]

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]

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]

ne 7. 11. 2021 v 8:21 odesílatel Muhmud Ahmad ***@***.***> napsal:

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.

Is possible remote access to some virtual comp, where this issue can be reproduced? I don't know OpenBSD, so some remote access to some test environments can help me. Or if you can publish an image somewhere for a virtual box?

…

pspg-new.log <https://github.com/okbob/pspg/files/7491883/pspg-new.log> pspg-orig.log <https://github.com/okbob/pspg/files/7491884/pspg-orig.log> make.log <https://github.com/okbob/pspg/files/7491885/make.log> configure.log <https://github.com/okbob/pspg/files/7491886/configure.log> — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#194 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAEFO46CMSSWQ77JYSKGDGLUKYSHHANCNFSM5HA3GNWQ> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>.

[muhmud]

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]

ne 7. 11. 2021 v 8:25 odesílatel Pavel Stehule ***@***.***> napsal:

ne 7. 11. 2021 v 8:21 odesílatel Muhmud Ahmad ***@***.***> napsal: > 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. > Is possible remote access to some virtual comp, where this issue can be reproduced? I don't know OpenBSD, so some remote access to some test environments can help me. Or if you can publish an image somewhere for a virtual box?

How are you starting pspg? It looks so streaming mode via pipe doesn't work on your platform Sun Nov 7 12:19:00 2021 [166435] force stream mode because input is FIFO Sun Nov 7 12:19:00 2021 [166435] started Sun Nov 7 12:19:00 2021 [166435] with utf8 support Sun Nov 7 12:19:00 2021 [166435] DataDesc is initialized Sun Nov 7 12:19:00 2021 [166435] read rows 0 Sun Nov 7 12:19:00 2021 [166435] read input 0 rows Sun Nov 7 12:19:00 2021 [166435] terminal size by TIOCGWINSZ rows: 23, cols: 112 Sun Nov 7 12:19:00 2021 [166435] quit due quit_if_one_screen option without ncurses init

…

> pspg-new.log <https://github.com/okbob/pspg/files/7491883/pspg-new.log> > pspg-orig.log <https://github.com/okbob/pspg/files/7491884/pspg-orig.log> > make.log <https://github.com/okbob/pspg/files/7491885/make.log> > configure.log <https://github.com/okbob/pspg/files/7491886/configure.log> > > — > You are receiving this because you commented. > Reply to this email directly, view it on GitHub > <#194 (comment)>, or > unsubscribe > <https://github.com/notifications/unsubscribe-auth/AAEFO46CMSSWQ77JYSKGDGLUKYSHHANCNFSM5HA3GNWQ> > . > Triage notifications on the go with GitHub Mobile for iOS > <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> > or Android > <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>. > >

[muhmud]

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]

ne 7. 11. 2021 v 8:27 odesílatel Muhmud Ahmad ***@***.***> napsal:

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 :)

The communication via pipe should work too. But in streaming mode, there can be problems with empty rows - in postgres, there are empty rows only at the end of one table, but in Oracle, there are empty rows at the beginning.

…

— You are receiving this because you commented. Reply to this email directly, view it on GitHub <#194 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAEFO42ER7KLQXNFKW6BEJLUKYS5PANCNFSM5HA3GNWQ> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>.

[okbob]

ne 7. 11. 2021 v 8:32 odesílatel Muhmud Ahmad ***@***.***> napsal:

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.

ok, now I can reproduce this issue, or minimally I found a new issue. So I'll try to fix it, and then you can recheck it. —

…

You are receiving this because you commented. Reply to this email directly, view it on GitHub <#194 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAEFO42SU343CE5ENHZGWD3UKYTP5ANCNFSM5HA3GNWQ> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>.

[muhmud]

👍 👍

[okbob]

please. can you check last commit?

[muhmud]

Looks good!

[okbob]

ne 7. 11. 2021 v 19:53 odesílatel Muhmud Ahmad ***@***.***> napsal:

Looks good!

Thank you unfortunately, the complexity of input processing is big already - there are three different sources PIPE, FIFO and file, a normal mode, and stream mode, and there are few hacks in streaming mode (continuous reading from input), because processing data has not easy detected report (table) begin and end.

…

— You are receiving this because you commented. Reply to this email directly, view it on GitHub <#194 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAEFO452T2QKRC22TBNNTDTUK3DMNANCNFSM5HA3GNWQ> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>.