SQLConvert

Download links below. If you're interested in seeing parts of the source code, take the .zip file. Otherwise the standalone .exe will do.

The 'Readme.txt' which is also in the .zip file, follows:

the main (and only) window; the big button converts text in the top area and displays it in the bottom area, with changes according to the parameters on the right.

SQLConvert 1.0

21-dec-2007, Roderik Muit (www.wyz.biz)

Purpose

This little tool can interpret SQL statements, and:

• (re)format them if needed
• output them again in a different SQL dialects (e.g. MS Access, SQL Server, Oracle)
• add/remove prefixes and 'quoting' to/from tablenames and fieldnames

As well as a handy utility for some, this is a means to show off a part of the code to interested parties.

Pressing the 'Convert SQL' button will interpret the SQL statement in the top textbox, and then output the interpreted statement again in the bottom textbox.

There's a very small 'example' button on the middle right of the application, which shows some statements that can be interpreted. The rest of the options should be self-explanatory - or you can try them out.

Code base

The VB(A) code base works in Visual Basic 6 as well as in MS Access (97 and up), with just a change to a compile-time constant.

It is part of a larger code base owned by Koster Engineering (who I used to work for), which forms a database independent framework in Visual Basic 6 for creating database (entry/reporting) applications. That framework has been rebranded 'ProblemSolution' (more information and screenshots on www.kosterengineering.com -> 'ProblemSolution' -> PDF document.)
The code base for SQLConvert does not include ProblemSolution's database connection components (which work in VB6 and MS Access VBA) and form/control drawing components (which work in VB6 only) - because it does not need them.

The 'SQL interpreter' is not based on any kind of official grammar. The 'rules' are coded in pure VB - which means that any exceptions and strange syntax (like the 'OVER()' clause for Oracle, and the Datepart() function for MS SQL Server whose first argument is not a proper string/value) need to be coded explicitly.

Features

It is not always necessary to set the 'SQL Type' for the input statement;
syntax is not strictly checked against one specific SQL dialect. One apparent difference is that double quotes are seen as string delimiters for MS Access, and as field delimiters for Oracle and MS SQL Server.

The 'SQL interpreter' can handle the following things which are not SQL syntax:

• Functions named 'Eval()' will usually be passed to a custom VB routine, Which can then be recoded to return anything based on the arguments passed.
  
  (See the source code for details.)
  
  Currently, nothing is done with the arguments inside. Anything inside an Eval() function will be literally preserved.
  
  
• SQL elements starting with a backslash are not interpreted and stored literally. (Which means they are not subject to things like stripping field prefixes.) This might be necessary for 'strange elements' which have not been explicitly coded yet, like DATEPART() for SQL Server.

Known bugs

• Not all forms of UPDATE statements work using the SQLBuilder.SetSQL(<string>) method, which this tool uses. Only MS Access is known to work. That is, 'UPDATE <table> SET <field>=<expr> FROM <table-group-expr>' does not necessarily work; 'UPDATE <table-group-expr> SET <field>=<expr>' does.
• Field aliases that follow a field expression (in the SELECT part) MUST be precededed by 'AS ', even though the SQL syntax for e.g. Oracle does not require this. (This can only be solved by recoding a basic assumption in the code.
  
  (Note to self: 'field aliases should be functionally tied to a SQLBuilder.mclsFieldList entry, and moved out of the SQLB_Expr class')
  
• functions which have 'strange' arguments (i.e. things that do not resolve to numeric or string values) usually do not work. DATEPART() for MS SQL Server is one example that has not been implemented yet, and there may be more.
• 'table()' for Oracle syntax (and possibly other 'virtual table expressions') are not supported yet.
• the separation between 'field expressions' and 'comparison expressions' is too strict. This means e.g. things like 'SELECT FLD=1 FROM TBL' generates an error. (For MS Access, this could be recoded to 'SELECT Iif(FLD=1,True,False) FROM TBL')
  
  (Note to self: integrate SQLB_Where into SQLB_Expr)
  

Other Issues

• field prefixes are stripped from table names. They are not stripped from aliases of subqueries (as example 1 shows). This can be done only after a non-trivial rewrite of parts of the code.
  
  (Note to self: a subquery should also get its own SQLB_Table instance. Like a 'Table()' statement, by the way. Makes the code cleaner too, in some places.)
  
• The special 'DUAL' table is supported OK when interpreting/reformatting Oracle statements, but not added/removed when converting to/from other SQL types.
• speed. The first example statement (which is about 16kB unformatted and has 11 nested subqueries) takes several seconds to be interpreted. This can probably be improved significantly, but only with a major rewrite.
  
  (Internal note: expressions should be stored in recursing collections of collections inside one SQLBuilder instance. They are now stored in collections inside recursing SQLB_Expr instances. SQLB_Expr should be ditched completely and all code moved inside SQLBuilder, which removes major overhead. First, however, SQLB_Where should be integrated into SQLB_Expr so that it also uses the InSQL(...,colPos) speed optimisation.)
  

License

The SQLConvert 1.0 package (binary and part of the source code) is distributed under the 3-clause BSD license. (This translates to "do whatever you want with it - just don't remove our name from the copyright, and don't use our name in publicity for any weird products you derive from it without asking us".)

Future releases (if any) may be relicensed under a different license,
depending on the then-current issues with the source code.

Source code & Copyright issues

The source code provided in this package is incomplete and does not compile. As such, it only gives an overview of the code quality and setup.

The source code was developed by various employees of Koster Engineering when I still worked there, and later refined by me to work with a.o. MS Access VBA. The basics were written by someone else. All of the 'SQL interpreter' (i.e. SetSQL() and related code) is mine; about half of the other code/documentation is mine or has been revised by me.

As such, the copyright of the source code is not clear. Koster Engineering
does not mind me distributing this tool, but would mind me distributing the fully functional source code.

If you are interested in this code, feel free to suggest things. My suggestion is you pay me for fixing above bugs and improvements, so Koster Engineering can benefit from those. I can probably get them to release the code in return under a license to be jointly discussed, and everyone wins :)