Skip to content

Update SQL formatter bundle for new API #1988

New issue
New issue
Open
Open
Update SQL formatter bundle for new API#1988
Labels
Bug
@karthicraghupathi

Description

@karthicraghupathi
karthicraghupathi
opened on Sep 17, 2014

I'm using Sequel Pro 1.0.2 Build 4096. For the last couple of days, the format SQL bundle has stopped working with the following error message:

Unauthorized request, please contact support@dpriver.com a free account.

Here is a screenshot:

image

Can you provide some pointers as to what could have gone wrong and what might be a possible fix?

Activity

sqlparser

sqlparser commented on Sep 18, 2014

@sqlparser
sqlparser
on Sep 18, 2014

Format SQL bundle in Sequel Pro use free sql formatter service from:
http://www.dpriver.com/pp/sqlformat.htm

Access to this free sql formatter service via webpage is available as usual, however Sequel Pro access this service via API which was move to a new site:
https://github.com/sqlparser/sql-pretty-printer/wiki/SQL-FaaS#2-api

So something changes should be made in this file:
https://github.com/sequelpro/Bundles/blob/master/core/Format%20SQL.spBundle/command.plist

I'm author of this sql formatter from dpriver.com, feel free to let me know if anything I can do when migrate to use this new sql formatter service, btw, this sql formatter service is free as usual.

James

dmoagx
changed the title [-]Format SQL Bundle Error[/-] [+]Update endpoint URL for SQL formatter[/+] on Sep 18, 2014
dmoagx
changed the title [-]Update endpoint URL for SQL formatter[/-] [+]Update SQL formatter bundle for new API[/+] on Sep 18, 2014
dmoagx

dmoagx commented on Sep 18, 2014

@dmoagx
dmoagx
on Sep 18, 2014
Member

I wouldn't say we use an API, but rather parse the result from the HTML response page, so this might need a substantial rewrite.

James: BTW Thanks for your e-mail reminder, Sequel Pro development just isn't very active at the moment.
I hope this isn't causing any problems for you.

dmoagx
added
Priority-High
on Sep 18, 2014
gerrior

gerrior commented on Oct 9, 2014

@gerrior
gerrior
on Oct 9, 2014

The problem has morphed into "Please refresh this page to load the latest version 4.0"

screen shot 2014-10-09 at 11 11 51 am

minaevd

minaevd commented on Oct 27, 2014

@minaevd
minaevd
on Oct 27, 2014

Here is the fix, no development required.
All you have to do is to change the clientid key in the Bundles editor:

*) open Sequel Pro application
*) go to Bundles -> Bundle Editor
*) Use left pane, Select "Input field" (Show), choose Format SQL
*) find the following row in the "Command" textarea:

*) replace the value of the input to "dpriver-9094-8133-2031", so the new line is:

*) Click "Save"

It works for me.

Dmitry

Mirocow

Mirocow commented on Nov 7, 2014

@Mirocow
Mirocow
on Nov 7, 2014
# check for empty STDIN
SQL=$(cat)
if [ -z "$SQL" ]; then
    echo "No SQL statements passed."
    exit $SP_BUNDLE_EXIT_SHOW_AS_TEXT_TOOLTIP
fi

# check if connected to the internet and if server is up
connected=$(/sbin/ping -t 8 -c 1 -on www.dpriver.com 2> /dev/null | grep '69\.5\.11\.169' | wc -l)
if [ $connected -eq "0" ]; then
    echo "<font color=red>You are probably not connected to the internet or http://www.dpriver.com/ server is down.</font>"
    exit $SP_BUNDLE_EXIT_SHOW_AS_HTML_TOOLTIP
fi

# show info alert about sending the SQL statements to an online service
if [ ! -e infoShowed ]; then
    touch infoShowed
    osascript -e 'tell app "Sequel Pro" to display dialog "This command will send the SQL statement(s) - unencrypted - to an online service (http://www.dpriver.com).  Please consider this before sending confidential data!\n\nThis message will only be displayed once." with icon caution' 2> 1
    if [ `cat 1 | wc -c` -ne 0 ]; then
        rm -f 1
        exit $SP_BUNDLE_EXIT_NONE
    fi
    rm -f 1
fi

# send SQL to www.dpriver.com and replace the formatted SQL string in first responder; if a parser error occurred
# show error message and try to jump to the error
cat <<HTML
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
    <base href="http://www.dpriver.com/pp/">
    <title>Connecting www.dpriver.com</title>
    <script>
        function lookForResult() {
            if(document.all.outputsql.value.length > 8) {
                if(document.all.errorhint.style.display == "inline") {
                    var errormes = document.all.outputsql.value;
                    var re = /.*?\(\d+\s*,\s*(\d+)\).*/;
                    re.exec(errormes);
                    var pos = parseInt(RegExp.\$1) - 1;
                    re = /.*?(\d+).*/;
                    re.exec('$SP_SELECTED_TEXT_RANGE');
                    var offset = parseInt(RegExp.\$1);
                    pos = pos + offset;
                    alert(document.all.outputsql.value);
                    window.system.setSelectedTextRange(pos+'');
                } else {
                    var txt = document.all.outputsql.value;
                    var len = txt.length;
                    window.system.setSelectedTextRange('$SP_SELECTED_TEXT_RANGE');
                    window.system.insertText(txt.slice(0,len-2));
                }
                window.system.closeHTMLOutputWindow();  
            } else {
                setTimeout("lookForResult()",50);
            }
        }

        function waitForResult() {
            window.system.suppressExceptionAlert();
            setTimeout("lookForResult()",50);
        }
    </script>
    <script language="JavaScript" type="text/javascript" src="pp.js"></script>
</head>
<body onload='window.resizeTo(300,200);SQLFMT.format(document.all.sp_submit);waitForResult()'>
<div id="retvalues"></div>
<p><b>Copyright &copy; 2001-2010 Gudu Software<br>All Rights Reserved<br><a href="mailto:support@dpriver.com">Contact US</a></b></p>
<br><br><br><br><br>
<p align=right>Please wait…</p>
<div class="page-container-1" style="display:none;">
    <div id="container">
        <div id="wrapper">
            <div id="content">
                <form id="SqlFmtForm" name="frm_sqlformat" method="post" action="/cgi-bin/ppserver" onsubmit="return false;">
                    <div id="secondpanel"> 
                        <div id="sp_database">
                            <label class="desc" style="display:inline;">Database</label>
                            <select name="dbvendor">
                                <option value="mysql" selected>MySQL</option>
                            </select>
                            <label class="desc" style="display:inline;padding:0 0 0px 40px; border:0px solid;">Output:</label>
                            <select name="outputfmt">
                                <option value="SQL" selected>SQL(Text)</option>
                            </select>
                        </div>
                    </div>
                    <div id="sqlpanel">
                        <div id="sp_inputsql">
                            <textarea id ="inputsql" name="inputsql" cols="1" rows="1" wrap="off">
                            $SQL
                            </textarea>
                        </div>
                        <div id="sp_submit">
                            <input type="button" id="btnformat" name="print" value="Format SQL" onclick="SQLFMT.format(this)" value="Format SQL">
                            <label style="display:none;">(Time used: <span id="timestamp">0</span> seconds)</label>
                        </div>
                        <div id="sp_submit" style="text-align:left;">
                        </div>
                        <label id="errorhint" class="desc" style="color:#DF0000 !important;display:none;">
                            Can't format input sql, make sure there is no syntax error and select correct database.
                            <a href="" style="display:none;">OR Report a bug of this sql beautifier</a>
                        </label>
                        <iframe id="ioutputsql" ></iframe>
                        <div id="sp_outputsql" style="display:none;">
                            <textarea id = "outputsql" name="outputsql" cols="80" rows="15" wrap="off" >
                            </textarea>
                        </div>
                        <div id="sp_errormsg" style="display:none;">
                            Error message goes to here!
                        </div>
                    </div>
                    <div id="formatoptions">
                        <select name="keywordcs">
                            <option value="Uppercase" selected >Uppercase</option>
                            <option value="Lowercase" >Lowercase</option>
                            <option value="InitCap" >InitCap</option>
                            <option value="Unchanged" >Unchanged</option>
                        </select>
                        <select name="identifiercs">
                            <option value="Uppercase" >Uppercase</option>
                            <option value="Lowercase" selected >Lowercase</option>
                            <option value="InitCap" >InitCap</option>
                            <option value="Unchanged" >Unchanged</option>
                            <option value="initcapeachword" >Init cap each word</option>
                        </select>
                        <select name="functioncs">
                            <option value="Uppercase" >Uppercase</option>
                            <option value="Lowercase" >Lowercase</option>
                            <option value="InitCap" selected >InitCap</option>
                            <option value="Unchanged" >Unchanged</option>
                        </select>
                        <input type="radio" name="lnbrwithcomma" value="after" checked > After
                        <input type="radio" name="lnbrwithcomma" value="before" > Before
                        <input type="radio" name="lnbrwithcomma" value= "beforewithspace" > Before with space
                        <input type="radio" name="liststyle" value="stack" checked>Stacked
                        <input type="radio" name="liststyle" value="nostack">Not Stacked
                        <input type="radio" name="salign" value="sleft" checked> Align left
                        <input type="radio" name="salign" value="sright" > Align right
                        <input type="checkbox" name="andorunderwhere" value="yes" checked> And/Or under Where
                        <input type="checkbox" name="removelnbr" value="no" checked> Remove Linebreak before beautify
                        <input type="checkbox" name="trimquotechar" value="yes"> Trim Quoted Char of Each Line quoted char of eachline: <input type="text" name="quotechar" value= "&#34" size = 5 > 
                        <input type="checkbox" name="compactmode" value="yes"> Compact the output of sql output
                        <input type="text" name="maxlenincm" value= "80" size = 5 > 
                    </div>
                    <input type="hidden" name="clientid" value="dpriver-9094-8133-2031" /> 
                </form>
            </div>
        </div>
    </div>
</div>

</body>
</html>
HTML

Replace for this code

temirov

temirov commented on Nov 26, 2014

@temirov
temirov
on Nov 26, 2014

Tnx, @minaevd ! replace the value of the input to "dpriver-9094-8133-2031", so the new line is:

<input type="hidden" name="clientid" value="dpriver-9094-8133-2031" />

That did it for me

jwg2s

jwg2s commented on Jan 26, 2015

@jwg2s
jwg2s
on Jan 26, 2015

👍 this would be awesome if it could be pushed out! One of my favorite, no-longer-working sequel pro features.

mrardon

mrardon commented on Jan 26, 2015

@mrardon
mrardon
on Jan 26, 2015

@jwg2s Just follow directions in workaround above by @minaevd. Works for me.

jwg2s

jwg2s commented on Jan 26, 2015

@jwg2s
jwg2s
on Jan 26, 2015

I copy pasted the code from @minaevd but now it just deletes the entire query.

jwg2s

jwg2s commented on Jan 26, 2015

@jwg2s
jwg2s
on Jan 26, 2015

Copied from a fresh install, must be a formatting issue with Github or something. All well now!

mrardon

mrardon commented on Jan 26, 2015

@mrardon
mrardon
on Jan 26, 2015

Dont think you are looking at @minaevd's response.
Shouldn't be much formatting. On line 149 of a clean install change that line to be: <input type="hidden" name="clientid" value="dpriver-9094-8133-2031" /> The only thing you are replacing is the part before -9094-8133-2031 and that becomes dpriver instead of the number in there currently.

gagaboy

gagaboy commented on Apr 14, 2015

@gagaboy
gagaboy
on Apr 14, 2015

now the code below does work!!

# check for empty STDIN
SQL=$(cat)
if [ -z "$SQL" ]; then
    echo "No SQL statements passed."
    exit $SP_BUNDLE_EXIT_SHOW_AS_TEXT_TOOLTIP
fi

# send SQL to www.dpriver.com and replace the formatted SQL string in first responder; if a parser error occurred
# show error message and try to jump to the error
curl --data-urlencode "rqst_input_sql=$SQL" 'http://www.gudusoft.com/format.php' 2> /dev/null  | php -r 'echo json_decode(file_get_contents("php://stdin"))->rspn_formatted_sql;'

20 remaining items

naillizard

naillizard commented on Aug 19, 2018

@naillizard
naillizard
on Aug 19, 2018 via email
naillizard

naillizard commented on Aug 21, 2018

@naillizard
naillizard
on Aug 21, 2018

@peh great man, have't used python in a while... could not remember the api... was a shot in the dark!

Cheers for fixing it.

akaramires

akaramires commented on Jan 9, 2019

@akaramires
akaramires
on Jan 9, 2019

Tnx, @minaevd ! replace the value of the input to "dpriver-9094-8133-2031", so the new line is:

<input type="hidden" name="clientid" value="dpriver-9094-8133-2031" />

That did it for me

Thank you!

vodatis
mentioned this on Feb 21, 2019
muffinmad

muffinmad commented on Apr 5, 2019

@muffinmad
muffinmad
on Apr 5, 2019

Modified solution from @peh to avoid new line at the end

#!/usr/local/bin/python

import sys
import sqlparse

sql = sys.stdin.readlines()

sys.stdout.write(sqlparse.format("".join(sql), reindent=True, keyword_case='upper'))
MISSDAURIS

MISSDAURIS commented on Nov 6, 2019

@MISSDAURIS
MISSDAURIS
on Nov 6, 2019

Hello!

I´ve tried both options that I have seen:

#!/usr/bin/php
<?php
$sql = @file_get_contents(“php://stdin”);
$request = [‘reindent’=>1,‘sql’=>$sql];
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, ’https://sqlformat.org/api/v1/format');
curl_setopt($ch, CURLOPT_POST, count($request));
curl_setopt($ch, CURLOPT_POSTFIELDS, http_build_query($request));
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
$result = curl_exec($ch);
curl_close($ch);
echo json_decode($result,true)[‘result’];

resulted in a BASH error.

The other code changing line 149 from the original to
<input type="hidden" name="clientid" value="dpriver-9094-8133-2031" />
resulted in a pop up showing me please wait and nothing happens.

Screenshot 2019-11-06 at 10 45 31

Am using Sequel Pro Nightly Build 5446 (97c1b85)

Please help!!

naillizard

naillizard commented on Nov 7, 2019

@naillizard
naillizard
on Nov 7, 2019 via email
naillizard

naillizard commented on Nov 20, 2019

@naillizard
naillizard
on Nov 20, 2019

@MISSDAURIS try solution above from @muffinmad

masciugo

masciugo commented on Dec 6, 2019

@masciugo
masciugo
on Dec 6, 2019

I installed python with home brew and I can successfully use sqlparse from console:

Python 3.7.3 (default, Mar 27 2019, 09:23:39)
[Clang 10.0.0 (clang-1000.11.45.5)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlparse
>>> sqlparse.split('select * from foo; select * from bar;')
['select * from foo;', 'select * from bar;']
>>>

but in my bundle script:

#!/usr/bin/python

import sys
import sqlparse

sql = sys.stdin.readlines()

sys.stdout.write(sqlparse.format("".join(sql), reindent=True, keyword_case='upper'))

I got:

image

any idea why?

muffinmad

muffinmad commented on Dec 6, 2019

@muffinmad
muffinmad
on Dec 6, 2019

@masciugo You run python3 from console but likely python2 with:

#!/usr/bin/python

Try

#!/usr/bin/python3

in your bundle script

lcsqlpete

lcsqlpete commented on Mar 19, 2020

@lcsqlpete
lcsqlpete
on Mar 19, 2020

The sqlformat/php solution doesn't work for me. All I get is another window with the original unformatted code in it

Jason-Morcos
mentioned this on Jun 17, 2020
stale

stale commented on Jul 2, 2020

@stale
stalebot
on Jul 2, 2020

This project is not maintained anymore and was replaced. Please see #3705.

stale
added
stale
on Jul 2, 2020
abhibeckert
removed
stale
on Jul 2, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    Bug

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

      Development

      No branches or pull requests

        Participants

        @Bibiko@xurizaemon@designosis@Greenek@abhibeckert

        Issue actions
          Update SQL formatter bundle for new API · Issue #1988 · sequelpro/sequelpro