Formatting SQL for display can sometimes be a pain, particularly for the many different flavors of SQL. One easy way to do the formatting is to use a web service.
I did a little Google searching and found this one which seems to work well: https://github.com/sqlparser/sql-pretty-printer/wiki/SQL-FaaS-API-manual
To use it with Xojo, create a subclass of Xojo.Net.HttpSocket and drag it onto a window
This code in a button takes unformatted SQL from a TextArea and sends it to the web service, asking that it be formatted for Oracle (rqst_db_vendor=1):
Dim sql As Text = SQLArea.Text.ToText
Dim postText As Text = "rqst_input_sql=" + _
EncodeURLComponent(sql).ToText + _
"&rqst_db_vendor=1"
Dim postData As Xojo.Core.MemoryBlock
postData = Xojo.Core.TextEncoding.UTF8.ConvertTextToData(postText)
SQLFormatter1.SetRequestContent(postData, _
"application/x-www-form-urlencoded")
SQLFormatter1.Send("POST", _
"http://www.gudusoft.com/format.php")
This code in the PageReceived event handler of your HttpSocket subclass parses the resulting JSON and displays the formatted SQL:
Dim jsonText As Text
jsontText = Xojo.Core.TextEncoding.UTF8.ConvertDataToText(Content)
Dim json As Xojo.Core.Dictionary = Xojo.Data.ParseJSON(jsonText)
FormattedSQLArea.Text = json.Value("rspn_formatted_sql")
Looking for something to try next? Watch the video below and learn how to use HTTPSocket in the Xojo Framework.
