Simple SQL Formatter

Databases
1

Does anyone have a XOJO method that will take a string of SQL and return a somewhat “pretty” formatted version?
Looking for something that can handle standard statements, and it needs to be non-declare oriented Xojo code.

If someone has something that I can drop it to my project, and it works for my needs, I’ll give you a lifetime license to the project it would go in.
(what that project is, will be under wraps for another week or so :slight_smile: )

preferably without using a webservice…

2

I just completed my SQLBuilder project that will let you write SQL like this:

dim rs as RecordSet = _
  SQLBuilder_MTC.SQLSelect ( "" ) _
  .From( "table" ) _
  .Where( "col1", 3 ) _
  .OrWhere( "col2", "<>", 4 ) _
  .CondOrWhere( firstName <> "", "first_name", firstName ) _
  .Prepare( db ).SQLSelect

You can also just convert that to a string instead of using Prepare, if you prefer, and it will be formatted with the indents and placeholders for your database.

Does that help?

3

I am looking for some that (super simple example) can take this

s="SELECT a.id,count(*) as cnt FROM table1 a, table2 b WHERE a.id=b.id and a.thing>b.thing GROUP BY a.id ORDER by a.id"
t=formatSQL(s)

where “T” would be something like this

of course I’d hope it could handle subquery and bracketed expressions too

I “could” just insert EOL before/after certain keywords… but in reality it would be more complicated than that.

4

I’m not aware of anything like that, and even the web service isn’t perfect. My code would require you to rewrite this as:

s = SQLBuilder_MTC.SQLSelect ( "a.id, count(*)" ).From( "table1 a" ).From( "table2 b" ).WhereRaw( "a.id = b.id" ).WhereRaw( "a.thing>b.thing" ).GroupBy( "a.id" ).OrderBy( "a.id").ToString

The output would be:

SELECT
  a.id, count(*)
FROM
  table1 a,
  table2 b
WHERE
  a.id = b.id
  AND a.thing>b.thing
GROUP BY
  a.id
ORDER BY
  a.id
5

yeah… using your tool would not be an option since the SQL would always pre-exist in a string, either typed by the user or extracted from a database.

6

The next option would be to tokenize it yourself, not a trivial task I think. Since I can see its uses, I will think about including that in my project.

But that doesn’t help you today, sorry.

7

online : http://poorsql.com/
source code : https://github.com/TaoK/PoorMansTSqlFormatter

it’s not xojo code, so there is some work before using it…

8

Thanks… the online one could not even handle by test case (it hung up forever)

I think I have an idea … if it works out decent, I’ll post more about it

9

and looking quite at the source C# code … well … it’s complicated !

10

well I tried it and it works immediately ?

SELECT a.id ,count(*) AS cnt FROM table1 a ,table2 b WHERE a.id = b.id AND a.thing > b.thing GROUP BY a.id ORDER BY a.id

11

I have had a similar issue in my SQLite Control database manager. I created a specific class for the formatting of SQL text.

You can download a copy here:
sqlFormatter

12

Why not hide all the ugly SQL away in a view or a function?

s = SQLBuilder_MTC.SQLSelect ( "a.id, count(*)" ).From( "table1 a" ).From( "table2 b" ).WhereRaw( "a.id = b.id" ).WhereRaw( "a.thing>b.thing" ).GroupBy( "a.id" ).OrderBy( "a.id").ToString

Hmm, you’re introducing the complexity of KQL (Kem’s Query Language) on top of SQL.

13

A few reasons:

  • If you separate the SQL from the method, it makes it harder to debug and modify when the database changes.
  • The utility is smart with Nils so if you do .Where( "i", myVariantValue ) where the value is nil, it will write it as WHERE i IS NULL.
  • It helps you with the syntax, especially the various Where options.
  • It has conditionals. For example:
s = SQLBuilder_MTC.SQLSelect( "" ) _
  .Where( "ssn", ssn ) _
  .CondWhere( firstName <> "", "first_name", firstName ) _
  .CondWhere( lastName <> "", "last_name", lastName ) _
  .ToString

Here ssn is required but both first name and last name are optional. Think about writing this as a prepared statement for Postgres where you must use the $-digit form of placeholders.

14

[quote=324671:@Dave S]

s="SELECT a.id,count(*) as cnt FROM table1 a, table2 b WHERE a.id=b.id and a.thing>b.thing GROUP BY a.id ORDER by a.id" t=formatSQL(s) [/quote]
My sqlFormatter class converts this to:

SELECT a.id,count(*) as cnt FROM table1 a, table2 b WHERE a.id=b.id AND a.thing>b.thing GROUP BY a.id ORDER BY a.id

15

@Simon Could you please give example of how to use the SQLFormatter?

16

the one I started working on yesterday takes the sample above and generates this output

SELECT a.id, COUNT(*) AS cnt
FROM table1 a, table2 b
WHERE a.id = b.id
AND a.thing > b.thing
GROUP BY a.id
ORDER BY a.id

which is the “style” of SQL I prefer… the next phase is indenting… (Hey I’ve only got 3 hours invested so far :slight_smile: )

17

Use the following:

' Where SQLText is a TextArea on the form dim fm As new sqlFormatter fm.InputText = SQLText.Text SQLText.Text = fm.OutputText
Simple as that!

18

Thanks Simon

19

Simon, your formatter does not take into account strings so it can actually change the SQL while it formats it. As a test:

dim s as string = _
  "SELECT 'select     (    ) from   a' FROM ""      where     "" WHERE 'where' = 'select' and 'and a = 2' <> 3"

gives us:

SELECT
 'select ( ) from a'
FROM
 ' where '
WHERE
 'where' = 'select' and 'and a = 2' <> 3

Note the spacing differences in the first string, the table identifier, and how the double quotes have been changed to single quotes.

20

mine produces this

SELECT 'select     (    ) from   a'
FROM ""
WHERE ""
WHERE 'where' = 'select'
AND 'and a = 2' <> 3

however I can’t see how your example is valid SQL…