SQLSelect.cc

Attached is a class for parsing and manipulating SQL strings.
For a description see the text below my signature.
Maybe someone will find it useful.
Comments and improvements welcome.
Roland
/*
     ------------------------------------------------------------------
     Programmer..: Roland Wingerter
     Last update.: December 5, 2006.
     Notes.......: Class for working with SQL-Select-Statements
           Parses a SQL-String passed as a parameter
                   and stores its parts in custom properties.
                   The class has been tested with simple sql strings.
                   JOINs should work too, but complex queries with
                   subqueries, union etc. are not supported.
                   getClause() extracts a part of the SQL string
                   setClause() can be used to change a part of the SQL 
string
     Written for.: dBase Plus
     Rev. History: 0.1 Dec 4, 2006
                   First version posted in dbase.deutsch
                   0.2 Dec 5, 2006
                   - Fixed bug found by Ivar B. Jessen:
                     Keywords are now recognized regardless of case.
                   - The case of the sql statement passed will be preserved.
                   - Fixed problem with fieldnames like "order1"
                     by adding space to Keywords
                   - Shortened code of getClause()
     Dependency..: :dUFLP:stringEx.cc
     Usage.......: oRef = new sqlSelect(<cSQL>)
     Example.....: set proc to sqlSelect.cc additive
                   o = new sqlSelect("SELECT * FROM fish WHERE id > 1
ORDER 
BY name")
                   ? o.Select
                   ? o.From
                   ? o.Where
                   ? o.OrderBy
                   o.setClause("SELECT","species, name")
                   ? o.Select
       o.setClause("ORDER BY","species, name")
                   ? o.OrderBy
        o.setClause("WHERE","name LIKE '%fish'")
                   ? o.Where
                   ? o.String
     Parameter...: cSQL: SQL-Statement
     ------------------------------------------------------------------
*/

Roland:

  I think this is a great idea.  I will not have a chance to play with it before
the new year, but already see it expanding to solve my need for dynamic sql
statement substitutions when using edit, insert and update.

JM



Roland Wingerter Wrote:

> Attached is a class for parsing and manipulating SQL strings.
> For a description see the text below my signature.
> 
> Maybe someone will find it useful.
> 
> Comments and improvements welcome.
> 
> Roland
> 
> 
> /*
>      ------------------------------------------------------------------
>      Programmer..: Roland Wingerter
> 
>      Last update.: December 5, 2006.
> 
>      Notes.......: Class for working with SQL-Select-Statements
>            Parses a SQL-String passed as a parameter
>                    and stores its parts in custom properties.
> 
>                    The class has been tested with simple sql strings.
>                    JOINs should work too, but complex queries with
>                    subqueries, union etc. are not supported.
> 
>                    getClause() extracts a part of the SQL string
>                    setClause() can be used to change a part of the SQL 
> string
> 
>      Written for.: dBase Plus
> 
>      Rev. History: 0.1 Dec 4, 2006
>                    First version posted in dbase.deutsch
> 
>                    0.2 Dec 5, 2006
>                    - Fixed bug found by Ivar B. Jessen:
>                      Keywords are now recognized regardless of case.
>                    - The case of the sql statement passed will be
preserved.
>                    - Fixed problem with fieldnames like "order1"
>                      by adding space to Keywords
>                    - Shortened code of getClause()
> 
>      Dependency..: :dUFLP:stringEx.cc
> 
>      Usage.......: oRef = new sqlSelect(<cSQL>)
> 
>      Example.....: set proc to sqlSelect.cc additive
>                    o = new sqlSelect("SELECT * FROM fish WHERE id >
1 ORDER 
> BY name")
>                    ? o.Select
>                    ? o.From
>                    ? o.Where
>                    ? o.OrderBy
> 
>                    o.setClause("SELECT","species,
name")
>                    ? o.Select
> 
>        o.setClause("ORDER BY","species, name")
>                    ? o.OrderBy
> 
>         o.setClause("WHERE","name LIKE '%fish'")
>                    ? o.Where
> 
>                    ? o.String
> 
>      Parameter...: cSQL: SQL-Statement
>      ------------------------------------------------------------------
> */
> 
> 

John Marshall wrote
>
> Roland:
>
>  I think this is a great idea.
------
Thanks.

I will not have a chance to play with it before the new year, but already 
see it expanding to solve my need for dynamic sql statement substitutions 
when using edit, insert and update.
------
I have no need for those, but I am sure it can be done.

Roland

On Sun, 17 Dec 2006 11:56:06 +0100
Roland Wingerter said :

> For more safety, I tried to protect the class properties using the keywordn

> "protect".
> 
> However, this does not work as expected. Can someone show me what I am
doing 
> wrong?

Hi Roland

You don't say what exactly is not working, or what the problem is, ... but ...

> protect aKeywords, string, select, from, where, groupby, orderby

> this.string = cString
> this.Select		= ''
> this.From		= ''
> this.Where		= ''
> this.GroupBy	= ''
> this.OrderBy	= ''

Two things occur to me from a quick glance at your code.

1) You are trying to protect a variable which does not yet exist.
   Try moving the "protect" line below the declaration block.

2) These property names are all in danger of conflicting with reserved names.
   Try a prefix, good practice would be to use a prefix which denotes the
   variable type.


   this.cString  = cString
   this.cSelect  = ''
   this.cFrom    = ''
   this.cWhere   = ''
   this.cGroupBy = ''
   this.cOrderBy = ''

   protect aKeywords, cString, cSelect, cFrom, cWhere, cGroupBy, cOrderBy


Hope this helps.

-- 
Ronnie MacGregor
Scotland

Ronnie at
dBASEdeveloper
dot co dot uk

www.dBASEdeveloper.co.uk

Roland W. wrote
> Attached is a class for parsing and manipulating SQL strings.
> For a description see the text below my signature.
-------
For more safety, I tried to protect the class properties using the keywordn 
"protect".
However, this does not work as expected. Can someone show me what I am doing 
wrong?
Roland