Oracle SQL Formatter for Xojo?

Databases
1

Hi,

SQL statement is displayed in Textarea from Oracle database but as you might imagine, its format is not clean. Just one line and not indented properly.
I mean I’m trying to implement to make it look better.

Is there any class or something to incorporate in Xojo(Windows desktop application)?
Textarea or HTMLviewer can be the place to display the SQL statement.

I tried to run the command line tool for formatting SQL and get the result by Shell, but it is not for Oracle database.

2

You could call a web service to format the SQL. This one seems to work well: https://github.com/sqlparser/sql-pretty-printer/wiki/SQL-FaaS-API-manual

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:

  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 = Xojo.Core.TextEncoding.UTF8.ConvertDataToText(Content)
  
  Dim json As Xojo.Core.Dictionary = Xojo.Data.ParseJSON(jsonText)
  
  FormattedSQLArea.Text = json.Value("rspn_formatted_sql")

Sample Project

3

Really really good approach.

However, most customers are not allowed to access internet for now.
I need to find the way I can use in just local.

Thank you so much.

4

Thanks @Paul Lefebvre for this sample project. Besides SQL formatting it gives a good example how to use a web-service.

Actually I am looking for an algorithm to translate / optimize the typical SQLite SQL to for example PostgreSQL. For now I have different sets of queries for different database types, since most of my desktop applications give the freedom to the administrator to work with SQLite, MySQL or PostgreSQL as production database.

5

Paul:

I’m trying to use your code, it works well except one issue.
When I click the row in listbox, its change event calls SQLFormatAction method.

[code]Sub SQLFormatAction()
Dim sql As Text = SQLAreaTemp.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”) <---- Exception
End Sub
[/code]

When I click the row slowly one by one, it works but when I do it fast, I got ‘UnsupportedOperationException’.
The exception detail says that “A request is already in progress.”.
I think in case I click other row and HTTPsocket hasn’t finished its job, it seems that I got that exception.

If I handle this exception with something like status code, I can avoid it I guess.

Can you help me how to handle the status?

Thanks.

6

You can handle it however you want. Wrapping the Send method call in a Try…Catch is one way. Or you could set your own Boolean to indicate that a request is underway and not run the SQLFormatCode event again. Or you could create a new Socket each time (rather than using the same one). It really depends on your needs.

7

Okay. By the way, how can I check if the request is underway and not finished?
I’m new to HTTPSocket.

8

This doesn’t really have anything to do with HTTPSocket. You could just create a boolean property (Sending). After calling the Send method, then Sending = True.
In the PageReceived event handler, set Sending = False.
Back in your SQLFormatAction, method, simply return if Sending is True.

Or you can wrap the Send method in a Try Catch and ignore the exception:

Try SQLFormatter1.Send... Catch e As UnsupportedOperationException Return End Try

9

Ah…Boolean method really works well.
Now, I understand how it works.

Thanks a lot.