Atom-beautify: SQL beautify error

[heruan]

Just installed atom-beautify 0.29.12 on Atom 1.10.2, restarted Atom and trying to beautify this SQL query: select * from MyTable but it fails and shows an empty red error box (see image).

If I use sqlformat from a terminal, it works:

$ cat Query.sql | sqlformat
select
  *
from
  MyTable

[prettydiff]

Could you do us a huge favor and generate the debug.md file? https://github.com/Glavin001/atom-beautify/blob/master/docs/troubleshooting.md

[heruan]

I've tried, after I select Atom Beautify: Help Debug Editor from the command palette Atom shows this message:

Are you ready to debug Atom Beautify?

Warning: This will create an anonymous Gist on GitHub (publically accessible and cannot be easily deleted) containing the contents of your active Text Editor.
Be sure to delete any private text from your active Text Editor before continuing to ensure you are not sharing undesirable private information.

I click _Okay_ but nothing happens (empty clipboard).

[Glavin001]

nothing happens (empty clipboard).

It should create a Gist. There were issues that the Atom clipboard does not work sometimes so I commented that out ( https://github.com/Glavin001/atom-beautify/blob/master/src/beautify.coffee#L476 ) and switched to creating the Gist automatically and providing a message with the link.

An unexpected error may have occurred. Would you be able to try again with the JavaScript Developer Console open so you can see errors being logged to the console?

[heruan]

There is an error on the console, indeed:

[Glavin001]

Thanks! It looks like text or more likely result is not a string when https://github.com/Glavin001/atom-beautify/blob/master/src/beautify.coffee#L453 is executed.
This is likely also causing the strange error message with SQL as shown above.

I'll try to make the appropriate fixes tonight so you can send us a complete debug.md.
For now, I recommend you take a look at https://github.com/Glavin001/atom-beautify/blob/master/src/beautifiers/sqlformat.coffee#L17 and try using sqlformat with additional command-line arguments such as the ones Atom-Beautify uses.

[Glavin001]

I'm fixed the issue ( https://github.com/Glavin001/atom-beautify/issues/1224 ). Published to v0.29.13.

Please provide us with your debug.md at your convenience.

[heruan]

https://gist.github.com/625d4de6bb03a448c033f7ff2e2fe7a9

[Glavin001]

Looks like something went wrong running sqlformat (see bottoms of "Logs" section):

2016-09-26T08:41:08.464Z - debug: [beautifiers/beautifier.coffee] spawn /usr/local/bin/sqlformat 0=/var/folders/b1/y3by15bx4q34_43j85gxdprsc7rdgr/T/input116826-99463-1as2bgc.4fli35wmi, 1=--reindent, 2=--indent_width=4, 3=--keywords=upper
2016-09-26T08:41:08.520Z - debug: [beautifiers/beautifier.coffee] spawn done 1  Usage:
  sqlformat.phar [--no-format] [--no-highlight] [<sql>]
  sqlformat.phar [--no-comment] [--no-highlight] [<sql>]
  sqlformat.phar [-c|--compress] [<sql>]
  sqlformat.phar [-h | --help]
  sqlformat.phar [--version]

Instead of cat Query.sql | sqlformat can you try using the same way Atom-Beautify does.

/usr/local/bin/sqlformat Query.sql --reindent --indent_width=4 --keywords=upper

[heruan]

Seems like my sqlformat does not accept any of these options, I suspect my sqlformat is not the same script Beautify needs. This is my output:

$ /usr/local/bin/sqlformat Query.sql --reindent --indent_width=4 --keywords=upper
Usage:
  sqlformat.phar [--no-format] [--no-highlight] [<sql>]
  sqlformat.phar [--no-comment] [--no-highlight] [<sql>]
  sqlformat.phar [-c|--compress] [<sql>]
  sqlformat.phar [-h | --help]
  sqlformat.phar [--version]

Options:
  --no-highlight  Disable highlight.
  --no-format     Disable auto formatting.
  --no-comment    Remove comment.
  -c --compress   Compress SQL request.
  -h --help       Display this help.
  -V --version    Show the version

Which sqlformat do I need?

[heruan]

Using https://github.com/andialbrecht/sqlparse it started working. Thank you for your support!

[Glavin001]

You're very welcome! I hope to add version detection to future Atom-Beautify so as to resolve this issues. Glad to hear it is working for you now!

[twang2218]

@Glavin001 I run into the same issue, here is the debug.md: https://gist.github.com/twang2218/02054fda1a7a234291433fd1b569b817

@heruan I'm not quite clear, how you solve the problem? I'm using macOS BTW.

My sqlformat version is 1.0.0:

$ sqlformat --version
sqlformat.phar v1.0.0
$ brew info sqlformat
homebrew/php/sqlformat: stable 1.0.0 (bottled)
CLI adaptation of the SqlFormatter library
https://github.com/MattKetmo/sqlformat
/usr/local/Cellar/sqlformat/1.0.0 (4 files, 81K) *
  Poured from bottle on 2016-05-12 at 02:16:30
From: https://github.com/Homebrew/homebrew-php/blob/master/Formula/sqlformat.rb
==> Requirements
Required: phpmeta ✘, phar ✔

If I run sqlformat dump.sql directly, it just shows the help info:

$ sqlformat dump.sql --reindent --indent_width=4 --keywords=upper
Usage:
  sqlformat.phar [--no-format] [--no-highlight] [<sql>]
  sqlformat.phar [--no-comment] [--no-highlight] [<sql>]
  sqlformat.phar [-c|--compress] [<sql>]
  sqlformat.phar [-h | --help]
  sqlformat.phar [--version]

If I cat dump.sql | sqlformat without the arguments, it can format the sql.

 cat dump.sql | sqlformat                                                  [e42fa64]
SELECT
  *
FROM
  User
WHERE
  name = '大圣'

[twang2218]

I think I might got the wrong sqlformat. Maybe there should be a version detection, if it's sqlformat.phar then alert it should be sqlparse.

I solved the problem by:

$ brew uninstall sqlformat
$ pip install --upgrade sqlparse

and don't forget restart atom.

[prettydiff]

@twang2218 Thank you, that is some good FYI for future troubleshooting.