|
SQL to HTML - John Felix COLIBRI. |
- abstract : This tool converts ASCII SQL requests into HTML files.
- key words : SQL - Scanner - SQL grammar - SQL to HTML - Sql pretty printer
- software used : Windows XP, Delphi 6
- hardware used : Pentium 1.400Mhz, 256 M memory, 140 G hard disc
- scope : Delphi 1 to 2005 for Windows, Kylix
- level : Delphi developer
- plan :
1 - Introduction
Having built an SQL parser, one of the first by product was an HTML converter which allows the inclusion of the request in our papers. Here is the converter.
2 - The SQL to HTML conversion 2.1 - The converter Let us take the following SQL request (from IbMastApp):
select * from parts where (parts.OnOrder > :OnHand) and (reference= 'bolt') |
We want to produce the following presentation:
SELECT *
FROM parts
WHERE (parts.OnOrder > :OnHand)
AND (reference = 'bolt')
|
To obtain this result, we must: - scan the request, to isolate the SQL symbols ("select", "*", "from" etc)
- change the casing according to some rule. We chose to present the SQL
keywords in uppercase, and leave the other symbols untouched
- pretty print the request. We chose a Pascal like indentation scheme, with the following rule:
- the main command symbol is at the margin (SELECT, INSERT etc)
- for the SELECT clause
- FROM, WHERE, ORDER BY etc are indented by 2
- the other command have not been indented, but we will include the rules
as they are needed by our applications
- add the HTML tags:
- "<FONT ...>" for the coloring
- "<B>" for the bold face
- " " and "<BR>" for the indentation and the line breaks
So all we need is to recognize the keywords, and change the casing, indent or otherwise color and hilite the symbols, as they come by. Since we had a scanner available, it was easier to use it rather then analyze
the string from scratch. In addition, our scanner uses enumerated symbol definitions (e_SELECT_symbol, e_FROM_symbol etc), which simplifies the grouping of tests:
IF f_symbol_type IN [e_FROM_symbol, e_WHERE_symbol, e_ORDER_symbol]
THEN // -- start a new line and indent
| or even better, using set of symbols:
CONST k_indented_symbol_set= [e_FROM_symbol, e_WHERE_symbol, e_ORDER_symbol];
...
IF f_symbol_type IN k_indented_symbol_set
THEN // -- start a new line and indent
|
2.2 - The conversion unit
Here it the CLASS definition:
c_sql_to_html= Class(c_basic_object)
m_c_sql_scanner_ref: c_sql_scanner;
constructor create_sql_to_html(p_name: String);
function f_symbol_type: t_sql_symbol_type;
function f_symbol_string: String;
procedure read_symbol;
procedure pretty_print(p_full_file_name: String);
procedure generate_html(p_full_file_name: String);
end; // c_sql_to_html
|
The pretty printer only can be done with this procedure:
procedure c_sql_to_html.pretty_print(p_full_file_name: String);
var l_result: String;
l_symbol_string: String;
begin
l_result:= '';
if not m_c_sql_scanner_ref.f_initialized
then display_bug_halt('could_not_initialize_scanner');
repeat
read_symbol;
l_symbol_string:= f_symbol_string;
if f_symbol_type in [Succ(e_identifier_start)..Pred(e_identifier_symbol)]
then l_symbol_string:= UpperCase(l_symbol_string);
if Pos(chr(13), m_c_sql_scanner_ref.m_blank_string)> 0
then l_result:= l_result+ k_new_line+ k_new_line;
Case f_symbol_type of
e_FROM_symbol, e_WHERE_symbol,
e_ORDER_symbol, e_HAVING_symbol : l_result:= l_result+ k_new_line+ ' ';
e_AND_symbol, e_OR_symbol :
l_result:= l_result+ k_new_line+ ' ';
e_point_symbol, e_closing_parenthesis_symbol,
e_comma_symbol : Delete(l_result, Length(l_result), 1);
end; // case
l_result:= l_result+ l_symbol_string;
if not (f_symbol_type in [e_point_symbol, e_opening_parenthesis_symbol,
e_colon_symbol])
then l_result:= l_result+ ' ';
until f_symbol_type= e_end_of_parse_symbol;
display(l_result);
save_string(l_result, p_full_file_name);
end; // pretty_print
| And to generate .HTML files, we simple add the HTML tags (partial):
procedure c_sql_to_html.generate_html(p_full_file_name: String);
var l_result: String;
l_indentation: Integer;
l_html_indentation: Integer;
function f_start_fat: String;
begin
Result:= '<B>';
end; // f_start_fat
// ...
var l_symbol_string: String;
begin // pretty_print
if not m_c_sql_scanner_ref.f_initialized
then display_bug_halt('could_not_initialize_scanner');
l_result:= '';
l_indentation:= 0; l_html_indentation:= 0;
l_has_color:= False;
add_text('<HTML>'+ k_new_line);
add_text(' <BODY>'+ k_new_line+ ' ');
Inc(l_indentation, 4);
repeat
read_symbol;
l_symbol_string:= f_symbol_string;
if f_symbol_type in [Succ(e_identifier_start)..Pred(e_identifier_symbol),
e_colon_symbol]
then l_symbol_string:= f_start_color(clRed)+ f_start_fat
+ UpperCase(l_symbol_string)+ f_end_fat+ f_end_color
else
if f_symbol_type in k_sql_litteral_symbol_set
then l_symbol_string:= f_start_color(clBlue)+ l_symbol_string+ f_end_color;
if Pos(chr(13), m_c_sql_scanner_ref.m_blank_string)> 0
then begin
l_html_indentation:= 0;
add_line(''); add_line('');
end;
Case f_symbol_type of
e_FROM_symbol, e_WHERE_symbol,
e_ORDER_symbol, e_HAVING_symbol :
begin
l_html_indentation:= 2;
add_line('');
end;
e_AND_symbol, e_OR_symbol :
begin
l_html_indentation:= 6;
add_line('');
end;
e_point_symbol, e_closing_parenthesis_symbol,
e_comma_symbol : Delete(l_result, Length(l_result), 1);
end; // case
l_result:= l_result+ l_symbol_string;
if not (f_symbol_type in [e_point_symbol, e_opening_parenthesis_symbol,
e_colon_symbol])
then l_result:= l_result+ ' ';
until f_symbol_type= e_end_of_parse_symbol;
Dec(l_indentation, 2);
add_text(' </BODY>'+ k_new_line);
add_text('</HTML>'+ k_new_line);
Inc(l_indentation, 2);
display(l_result);
save_string(l_result, p_full_file_name);
end; // generate_html
|
2.3 - Snapshot of the Converter Here is a snapshot of the converter:
2.4 - Generalize the Converter This tool was coded in about half an hour for our own needs. It is quite easy to generalize it:
- first add the SQL key words which are missing (ALTER etc) in the enumeration in the u_sql_symbol_definition UNIT
- then add the line break / indentation symbols in the CASE
Similarily, changing the coloring or hilighting rules should not be difficult.
3 - Download the Sources Here are the source code files:
Those .ZIP files contain: - the main program (.DPR, .DOF, .RES), the main form (.PAS, .DFM), and any other auxiliary form
- any .TXT for parameters
- all units (.PAS) for units
Those .ZIP - are self-contained: you will not need any other product (unless expressly mentioned).
- can be used from any folder (the pathes are RELATIVE)
- will not modify your PC in any way beyond the path where you placed the .ZIP (no registry changes, no path creation etc).
To use the .ZIP: - create or select any folder of your choice
- unzip the downloaded file
- using Delphi, compile and execute
To remove the .ZIP simply delete the folder.
As usual:
- please tell us at fcolibri@felix-colibri.com if you found some errors, mistakes, bugs, broken links or had some problem downloading the file. Resulting corrections will
be helpful for other readers
- we welcome any comment, criticism, enhancement, other sources or reference suggestion. Just send an e-mail to fcolibri@felix-colibri.com.
- or more simply, enter your (anonymous or with your e-mail if you want an answer) comments below and clic the "send" button
- and if you liked this article, talk about this site to your fellow developpers, add a link to your links page ou mention our articles in your blog or newsgroup posts when relevant. That's the way we operate:
the more traffic and Google references we get, the more articles we will write.
4 - Conclusion We presented here a converter from SQL to HTML
5 - Other Papers with Source and Links |
Database | |
database reverse engineering | Extraction of the Database Schema by analyzing the content of the application's .DFMs
| | sql parser | Parsing SQL requests in Delphi, starting from an EBNF grammar for SELECT, INSERT and UPDATE |
| ado net tutorial |
a complete Ado Net architectural presentation, and projects for creating the Database, creating Tables, adding, deleting and updating rows, displaying the data in controls and DataGrids, using in memory DataSets, handling Views, updating the Tables with a DataGrid
| | turbo delphi interbase tutorial |
develop database applications with Turbo Delphi and Interbase. Complete ADO Net architecture, and full projects to create the database, the Tables, fill the rows, display and update the values with DataGrids. Uses the BDP |
| bdp ado net blobs | BDP and Blobs : reading and writing Blob fields using the BDP with Turbo Delphi |
| interbase stored procedure grammar |
Interbase Stored Procedure Grammar : The BNF Grammar of the Interbase Stored Procedure. This grammar can be used to build stored procedure utilities, like pretty printers, renaming tools, Sql Engine conversion or ports | |
using interbase system tables |
Using InterBase System Tables : The Interbase / FireBird System Tables: description of the main Tables, with their relationship and presents examples of how to extract information from the schema | |
eco tutorial |
Writing a simple ECO application: the UML model, the in memory objects and the GUI presentation. We also will show how to evaluate OCL expressions using the EcoHandles, and persist the data on disc | |
delphi dbx4 programming |
the new dbExpress 4 framework for RAD Studio 2007 : the configuration files, how to connect, read and write data, using tracing and pooling delegates and metadata handling | |
blackfishsql |
using the new BlackfishSql standalone database engine of RAD Studio 2007 (Win32 and .Net) : create the database, create / fill / read Tables, use Pascal User Defined Functions and Stored Procedures | |
rave pdf intraweb |
how to produce PDF reports using Rave, and have an Intraweb site generate and display .PDF pages, with multi-user access | |
embarcadero er studio |
Embarcadero ER Studio tutorial: how to use the Entity Relationship tool to create a new model, reverse engineer a database, create sub-models, generate reports, import metadata, switch to Dimensional Model | | |
| Web |
| sql to html | converting SQL ascii request to HTML format
| | simple web server |
a simple HTTP web Server and the corresponding HTTP web Browser, using our Client Server Socket library | |
simple cgi web server |
a simple CGI Web Server which handles HTML <FORM> requests, mainly for debugging CGI Server extension purposes | |
cgi database browser | a CGI extension in order to display and modify a Table using a Web Browser | |
whois | a Whois Client who requests information about owners of IP adresses. Works in batch mode. | |
web downloader |
an HTTP tool enabling to save on a local folder an HTML page with its associated images (.GIF, .JPEG, .PNG or other) for archieving or later off-line reading | |
web spider | a Web Spider allowing to download all pages from a site, with custom or GUI filtering and selection. | |
asp net log file |
a logging CLASS allowing to monitor the Asp.Net events, mainly used for undesrtanding, debugging and journaling Asp.Net Web applications | |
asp net viewstate viewer |
an ASP.NET utility displaying the content of the viewtate field which carries the request state between Internet Explorer and the IIS / CASSINI Servers | |
rss reader |
the RSS Reader lets you download and view the content of an .RSS feed (the entry point into somebody's blog) in a tMemo or a tTreeView. Comes complete with an .HTML downloader and an .XML parser | |
news message tree |
how to build a tree of the NNTP News Messages. The downloaded messages are displayed in tListBox by message thread (topic), and for each thread the messages are presented in a tTreeVi"ew | |
threaded indy news reader |
a NewsReader which presents the articles sorted by thread and in a logical hierarchical way. This is the basic Indy newsreader demo plus the tree organization of messages | |
delphi asp net portal programming |
presentation, architecture and programming of the Delphi Asp Net Portal. This is a Delphi version of the Microsoft ASP.NET Starter Kit Web Portal showcase. With detailed schemas and step by step presentation, the Sql scripts and binaries of the Database
| | delphi web designer |
a tiny Delphi "RAD Web Designer", which explains how the Delphi IDE can be used to generate .HTML pages using the Palette / Object Inspector / Form metaphor to layout the page content | |
intraweb architecture |
the architecture of the Intraweb web site building tool. Explains how Delphi "rad html generator" work, and presents the CLASS organization (UML Class diagrams) | |
ajax tutorial |
AJAX Tutorial : writing an AJAX web application. How AJAX works, using a JavaScript DOM parser, the Indy Web Server, requesting .XML data packets - Integrated development project | |
asp net master pages |
Asp.Net 2.0 Master Pages : the new Asp.Net 2.0 allow us to define the page structure in a hierarchical way using Master Pages and Content Pages, in a way similar to tForm inheritance |
| delphi asp net 20 databases |
Asp.Net 2.0 and Ado.Net 2.0 : displaying and writing InterBase and Blackfish Sql data using Dbx4, Ado.Net Db and AdoDbxClient. Handling of ListBox and GridView with DataSource components
| | asp net 20 users roles profiles |
Asp.Net 2.0 Security: Users, Roles and Profiles : Asp.Net 2.0 offers a vaslty improved support for handling security: new Login Controls, and services for managing Users, grouping Users in Roles, and storing User preferences in Profiles
| | bayesian spam filter |
Bayesian Spam Filter : presentation and implementation of a spam elimination tool which uses Bayesian Filtering techniques | | |
| TCP/IP | |
tcp ip sniffer | project to capture and display the packets travelling on the Ethernet network of your PC. | |
sniffing interbase traffic |
capture and analysis of Interbase packets. Creation of a database and test table, and comparison of the BDE vs Interbase Express Delphi components | |
socket programming | the simplest Client Server example of TCP / IP communication using Windows Sockets with Delphi |
| delphi socket architecture |
the organization of the ScktComp unit, with UML diagrams and a simple Client Server file transfer example using tClientSocket and tServerSocket | | |
| Object Oriented Programming Components |
| delphi virtual constructor |
VIRTUAL CONSTRUCTORS together with CLASS references and dynamic Packages allow the separation between a main project and modules compiled and linked in later. The starting point for Application Frameworks and Plugins
| | delphi generics tutorial |
Delphi Generics Tutorial : using Generics (parameterized types) in Delphi : the type parameter and the type argument, application of generics, constraints on INTERFACEs or CONSTRUCTORs | |
| | UML Patterns |
| the lexi editor |
delphi source code of the Gof Editor: Composite, Decorator, Iterator, Strategy, Visitor, Command, with UML diagrams | |
factory and bridge patterns |
presentation and Delphi sources for the Abstract Factory and Bridge patterns, used in the Lexi Document Editor case study from the GOF book | |
gof design patterns |
delphi source code of the 23 Gof (GAMMA and other) patterns: Composite, Decorator, Iterator, Strategy, Visitor, Command | | | |
| | Graphic |
| delphi 3d designer |
build a 3d volume list, display it in perspective and move the camera, the screen or the volumes with the mouse. | |
writing a flash player |
build your own ShockWave Flash movie Player, with pause, custom back and forward steps, snapshots, resizing. Designed for analyzing .SWF demos. | | |
| Utilities | |
the coliget search engine |
a Full Text Search unit allowing to find the files in a directory satisfying a complex string request (UML AND Delphi OR Patters) | |
treeview html help viewer |
Treeview .HTML Help Viewer : the use of a Treeview along with a WebBrowser to display .HTML files alows both structuring and ordering of the help topics. This tool was used to browse the Delphi PRISM Wiki help. | |
| | Delphi utilities |
| delphi net bdsproj |
structure and analysis of the .BDSPROJ file with the help of a small Delphi .XML parser | | dccil bat generator
| generation of the .BAT for the Delphi DCCIL command line compiler using the .BDSPROJ | | dfm parser |
a Delphi Project analyzing the .DFM file and building a memory representation. This can be used for transformations of the form components | |
dfm binary to text | a Delphi Project converting all .DFM file from a path from binary to ascii format |
| component to code |
generate the component creation and initialization code by analyzing the .DFM. Handy to avoid installing components on the Palette when examining new libraries | |
exe dll pe explorer |
presents and analyzes the content of .EXE and .DLL files. The starting point for extracting resources, spying .DLL function calls or injecting additional functionalities | |
dll and process viewer |
analyze and display the list of running processes, with their associated DLLs and Memory mapped files (Process Walker) | | |
| Controls | |
find memo | a tMemo with "find first", "find next", "sort", "save" capabilities | | |
| |
6 - The author
Felix John COLIBRI works at the Pascal Institute. Starting with Pascal in 1979, he then became involved with Object Oriented Programming, Delphi, Sql, Tcp/Ip, Html, UML. Currently, he is mainly
active in the area of custom software development (new projects, maintenance, audits, BDE migration, Delphi
Xe_n migrations, refactoring), Delphi Consulting and Delph
training. His web site features tutorials, technical papers about programming with full downloadable source code, and the description and calendar of forthcoming Delphi, FireBird, Tcp/IP, Web Services, OOP / UML, Design Patterns, Unit Testing training sessions.
|
