Click here to Skip to main content
12,698,688 members (28,782 online)
Click here to Skip to main content
Add your own
alternative version

Stats

107.2K views
1.5K downloads
33 bookmarked
Posted

SQL WHERE - the easy way

, 18 Feb 2002 Ms-PL
Rate this:
Please Sign up or sign in to vote.
Creating SQL WHERE clauses can be a pain. This class grants relief.
<!-- Download Links -->

Creating SQL WHERE clauses

When developing C++ code to query SQL databases, one ends up with code which contains lots of ugly and cluttering statements like this:

char buf[256]; 
sprintf(buf, "WHERE (article=%i) AND (subject='%s%')", nArticle, pstrSubject); 
strcat(sql, buf);
// or even worse
strcat(sql, " WHERE (article=");
strcat(sql, _itoa(nArticle, buf, 10));
strcat(sql, ") AND (subject='");
strcat(sql, pstrSubject);
strcat(sql, ")");
// and so on and so forth...

Most of you have seen that sort of thing. I created the CWhere class to simplify creation of WHERE clauses and cleanup code. The resulting code, using the CWhere class, looks like this:

CWhere w(_T("article"), 1234); 
w.Add(_T("subject"), _T("CodeProject"); 
mySQLstring += w.GetWhereClause(); 
// should read "WHERE (article = 1234) AND (subject = 'CodeProject') ";

The class implements various methods to create the final string. Basically it all ends in providing several conversions of various datatypes to strings.
Currently the class contains all functions needed to create the following kind of clauses:

  1. "fieldXY IS NULL"
  2. "fieldXY exp value" where exp is any valid SQL operator (<, >, =,...)
  3. "fieldXY BETWEEN value1 AND value2"

Also, these clauses can be cascaded with AND and OR statements. The class takes care of nice SQL formatting, paranthesis, brackets and spaces. You can immediately use the resulting string for concatenation or other string operations. It contains leading and trailing spaces and all expressions are enclosed in brackets.

The class will provide correct strings for Unicode SQL statements too when the application is compiled using the UNICODE define.

Using the class

Lets start with the most common usage: creating a instance of CWhere with the first expression supplied in the constructor:

CWhere w(_T("article"), 1234);

This will create the variable w already containing "WHERE (article = 1234)". Now lets add another field:

w.Add(_T("subject"), _T("CodeProject");

This will add a " AND (subject = 'CodeProject')" to the variable. Finally, use the WHERE clause and add it to an existing SQL statement created somewhere else:

mySQLstring += w.GetWhereClause(); 
// should contain " WHERE (article = 1234) AND (subject = 'CodeProject') "

If the presence of the keyword WHERE is not needed or not wanted, just use GetWhereClausePlain().

Logical operations AND, OR

To simplify the creation of complex clauses containing AND or OR combinations, the operators &, |, &= and |= are overloaded. You can use them in a very intuitive way to create logical operations with the CWhere class.

CWhere w, article1, article2("article", -1L), subject("subject", pstrSubject), priority("priority", 2);
// logical operation OR
w = article1 | article2;		
// logical operation AND
w &= subject & priority;		

The resulting WHERE clause is: " WHERE ((article IS NULL) OR (article = -1)) AND ((subject = 'CodeProject') AND (priority = 2))"

Compatibility

Tested under Windows XP Pro, with VC6 and MFC. Supports fully UNICODE or MBCS. STL version is up to the reader :-)

License

This article, along with any associated source code and files, is licensed under The Microsoft Public License (Ms-PL)

Share

About the Author

No Biography provided

You may also be interested in...

Comments and Discussions

 
Questionstd::stringstream ?? Pin
Brett Gmoser19-Apr-07 13:34
memberBrett Gmoser19-Apr-07 13:34 
QuestionLatest Code ??? Pin
bachi19-Feb-04 5:08
memberbachi19-Feb-04 5:08 
AnswerRe: Latest Code ??? Pin
SaurweinAndreas19-Feb-04 5:17
memberSaurweinAndreas19-Feb-04 5:17 
Generalsmall help Pin
bachi19-Feb-04 7:13
memberbachi19-Feb-04 7:13 
Generalhandling differences in sql Pin
Vachooho18-Aug-03 15:12
sussVachooho18-Aug-03 15:12 
GeneralRe: handling differences in sql Pin
SaurweinAndreas21-Aug-03 4:17
memberSaurweinAndreas21-Aug-03 4:17 
QuestionCwhere 2.0 ? Pin
Jonathan de Halleux23-Jul-03 13:24
memberJonathan de Halleux23-Jul-03 13:24 
GeneralGoing further Pin
Jonathan de Halleux12-Jul-03 8:13
memberJonathan de Halleux12-Jul-03 8:13 
GeneralRe: Going further Pin
SaurweinAndreas16-Jul-03 4:45
memberSaurweinAndreas16-Jul-03 4:45 
Generalsource coming Pin
Jonathan de Halleux16-Jul-03 5:37
memberJonathan de Halleux16-Jul-03 5:37 
GeneralRe: source coming Pin
SaurweinAndreas17-Jul-03 6:10
memberSaurweinAndreas17-Jul-03 6:10 
Generalpreview Pin
Jonathan de Halleux18-Jul-03 8:59
memberJonathan de Halleux18-Jul-03 8:59 
SaurweinAndreas wrote:
The only idea that I may add - have a look at Spirit (http://www.codeproject.com/cpp/spiritintro.asp[^]).
Smile | :) look who's the author.


I've been working on this week but I must admit I went to fast for coding and now I'm to rewritting a part of the code to support some complicated stuff. Anyway, here a preview of piece of working code (it can be a little long Smile | :) )

Don't hesitate to comment Smile | :)

Creating the database


In order to do intelligent stuff, I need to build the database. Here I built it "by hand" but in the future, someone can built it directly from SQL or better by plugin in to the database.

Create Tables

Here I go with a 3 table db: clients, products and orders.

// I create a database
database_shared_ptr db( new database );

// creating a client table
table_shared_ptr clients = db->add_table( "Clients", "Client" );
// adding primary key
field_shared_ptr client_id = clients->add_primary_key( "ID");
// adding files
field_shared_ptr client_first_name = clients->add_field( "FirstName", "varchar(50)" );
field_shared_ptr client_last_name = clients->add_field( "LastName", "varchar(80)" );

// creating an order table
table_shared_ptr products = db->add_table( "Products", "Product" );
// adding primary key
field_shared_ptr product_id = products->add_primary_key("ID");
// adding files
field_shared_ptr product_name = products->add_field("Name", "varchar(50)");
field_shared_ptr product_price = products->add_field("Price", "int");

// creating an order table
table_shared_ptr orders = db->add_table( "Orders", "Order" );
field_shared_ptr order_id = orders->add_primary_key("ID");
field_shared_ptr order_date = orders->add_field("Date", "datetime");

Ok this sounds really long but normally you don't need to write such code. Field and tables can be accessed by their names:
db->get_table("clients");
clients->get_field("ClientID");


Note that "Client" has automatically been added.

Create relations

It is time to link the tables: we add two foreign keys to orders to have a many-to-many relation between clients and produtcs:
foreign_key_shared_ptr order_client_id = orders->add_foreign_key( clients );
foreign_key_shared_ptr order_product_id = orders->add_foreign_key( products, "NotTheSameNameID" );


A "ClientID" and "NotTheSameNameID" will be added to orders. That's it. We have all we need to create queries.

From here I can easily get the SQL to reacte the database... maybe XSD schema later.

Creating a query


I plan to able to create *complex* queries with multiple table joins, subqueries, etc... Here's the usual steps to do:

Create query and add tables

// creating a query
select_query_shared_ptr qr(new select_query(db));

// adding order table with alias
query_table_table_shared_ptr qo = qr->add_table( orders, "O" );
// making an inner join with clients (field automiatically deducted)
query_table_table_shared_ptr qc = qr->join( qo, clients, "C" );
// making an inner join with clients (here we specify the foreign key)
query_table_table_shared_ptr qp = qr->join( qo, products, "P" , InnerJoin, order_product_id);

Note that it supports multiple instance of table in case of self relation.

In the background I'm building a graph (vertex = table, edge=relation) using BGL. This enables to craete the join string by applying a depth_first_search to the graph Smile | :)

Adding field


We can now add the fields to the query (you can specify the alias):
// adding result fields to query
qr->add_field( qc, clients->get_field("ClientFirstName"), "First Name" );
qr->add_field( qc, clients->get_field("ClientLastName") );
qr->add_field( qo, orders->get_field("OrderDate"), "Date" );
qr->add_field( qp, orders->get_field("ProductName"), "Product Name");


Adding predicate


Let's create a number of predicate to be used later:
// creating a vector of string to use with IN
std::vector< string_type > names;
names.push_back("John");
names.push_back("Bill");

query_predicate_shared_ptr 
  // C.ClientLastName IS NOT NULL
  p1(is_not_null( qc + client_last_name)),
  // C.ClientFirstName = 'Peli', strings are escaped
  p2( qc + client_first_name == "Peli"),
  // C.ClientLastName NOT IN( 'John', 'Bill' )
  p3(not_in( qc + client_last_name, names.begin(), names.end() ) ),
  // C.ClientFirstName LIKE 'pi%'
  p4(like( qc + client_first_name, "pi%")),
  // O.OrderDate BETWEEN ... AND ...
  p5( between( qo + order_date,"10-09-1977", "21-09-77") );


We can now combine these predicates using the classical and, or etc..:
// (p1 && p2 && p3) || (p4&&p5)
qr->set_where(
    or(
        and(p1,
            and(p2,p3)
            ),
        and(p4,p5)
    )
		);


What do you think ?




Jonathan de Halleux.
GeneralRe: preview Pin
SaurweinAndreas18-Jul-03 11:19
memberSaurweinAndreas18-Jul-03 11:19 
GeneralRe: preview Pin
Jonathan de Halleux18-Jul-03 18:26
memberJonathan de Halleux18-Jul-03 18:26 
Generaluseful class Pin
Anthony_Yio2-Jan-03 16:10
memberAnthony_Yio2-Jan-03 16:10 
GeneralA suggestion Pin
Jim Johnson19-Feb-02 23:20
memberJim Johnson19-Feb-02 23:20 
GeneralDuplicate... Pin
Jim Johnson20-Feb-02 0:56
memberJim Johnson20-Feb-02 0:56 
GeneralRe: Duplicate... Pin
Andreas Saurwein20-Feb-02 7:18
memberAndreas Saurwein20-Feb-02 7:18 
GeneralThanks, Pin
Brian V Shifrin19-Feb-02 21:22
memberBrian V Shifrin19-Feb-02 21:22 
GeneralRe: Thanks, Pin
Andreas Saurwein20-Feb-02 7:17
memberAndreas Saurwein20-Feb-02 7:17 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.170118.1 | Last Updated 19 Feb 2002
Article Copyright 2002 by Andreas S. Franci Gonçalves
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid