Skip to content

Formatting SQL With A Web Service

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

Download Sample Project

Looking for something to try next? Watch the video below and learn how to use HTTPSocket in the Xojo Framework.

Watch the HTTPSocket Video