menu
  Home  ==>  papers  ==>  db  ==>  sql_to_html   

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_symbole_WHERE_symbole_ORDER_symbol]
  THEN // -- start a new line and indent

or even better, using set of symbols:

CONST k_indented_symbol_set= [e_FROM_symbole_WHERE_symbole_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_htmlClass(c_basic_object)
                  m_c_sql_scanner_refc_sql_scanner;

                  constructor create_sql_to_html(p_nameString);

                    function f_symbol_typet_sql_symbol_type;
                    function f_symbol_stringString;
                    procedure read_symbol;
                  procedure pretty_print(p_full_file_nameString);
                  procedure generate_html(p_full_file_nameString);
                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_nameString);
  var l_resultString;
      l_symbol_stringString;
  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_resultk_new_linek_new_line;

      Case f_symbol_type of
        e_FROM_symbole_WHERE_symbol,
          e_ORDER_symbole_HAVING_symbol : l_result:= l_resultk_new_line'  ';

        e_AND_symbole_OR_symbol :
            l_result:= l_resultk_new_line'      ';

        e_point_symbole_closing_parenthesis_symbol,
          e_comma_symbol : Delete(l_resultLength(l_result), 1);
      end// case

      l_result:= l_resultl_symbol_string;

      if not (f_symbol_type in [e_point_symbole_opening_parenthesis_symbol,
          e_colon_symbol])
        then l_result:= l_result' ';
    until f_symbol_typee_end_of_parse_symbol;

    display(l_result);
    save_string(l_resultp_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_nameString);
  var l_resultString;
      l_indentationInteger;
      l_html_indentationInteger;

  function f_start_fatString;
    begin
      Result:= '<B>';
    end// f_start_fat

  // ...

  var l_symbol_stringString;

  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_fatf_end_color
        else
          if f_symbol_type in k_sql_litteral_symbol_set
        then l_symbol_string:= f_start_color(clBlue)+ l_symbol_stringf_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_symbole_WHERE_symbol,
              e_ORDER_symbole_HAVING_symbol :
            begin
              l_html_indentation:= 2;
              add_line('');
            end;

        e_AND_symbole_OR_symbol :
            begin
              l_html_indentation:= 6;
              add_line('');
            end;

        e_point_symbole_closing_parenthesis_symbol,
          e_comma_symbol : Delete(l_resultLength(l_result), 1);
      end// case

      l_result:= l_resultl_symbol_string;

      if not (f_symbol_type in [e_point_symbole_opening_parenthesis_symbol,
          e_colon_symbol])
        then l_result:= l_result' ';
    until f_symbol_typee_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_resultp_full_file_name);
  end// generate_html



2.3 - Snapshot of the Converter

Here is a snapshot of the converter:

sql to html tool



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
    Name :
    E-mail :
    Comments * :
     

  • 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
Debug and Test
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
Helper units
windows environment read and write Windows Environment strings
stdin stdout send and receive strings from a GUI application to a CONSOLE application




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.
Created: nov-04. Last updated: feb-2018 - 98 articles, 232 .ZIP sources, 1232 figures
Copyright © Felix J. Colibri   http://www.felix-colibri.com 2004 - 2018. All rigths reserved
Back:    Home  Papers  Training  Delphi developments  Links  Download
the Pascal Institute

Felix J COLIBRI

+ Home
  + articles_with_sources
    + database
      + interbase
      – firebird_trans_simulator
      + sql_server
      + bdp
      – db_refactoring
      – sql_parser
      – sql_to_html
      – sniffing_interbase
      – eco_tutorial
      – dbx4_programming
      – blackfishsql
      – rave_pdf_intraweb
      – rave_reports_tutorial
      – rave_reports_video
      – embarcadero_er/studio
      + firedac
      – bde_unidac_migration
      + oracle
    + web_internet_sockets
    + oop_components
    + uml_design_patterns
    + debug_and_test
    + graphic
    + controls
    + colibri_utilities
    + colibri_helpers
    + delphi
    + firemonkey
    + compilers
    + vcl
  + delphi_training
  + delphi_developments
  + sweet_home
  – download_zip_sources
  + links
Contacts
Site Map
– search :

RSS feed  
Blog