Overview
Examples
Screenshots
Comparisons
Applications
Download
Documentation
Tutorials
Bazaar
Status & Roadmap
FAQ
Authors & License
Forums
Funding Ultimate++
Search on this site
Search in forums












SourceForge.net Logo
Home » U++ Library support » U++ SQL » Question about SQL....
Question about SQL.... [message #29222] Mon, 11 October 2010 21:08 Go to next message
mdelfede is currently offline  mdelfede
Messages: 1307
Registered: September 2007
Ultimate Contributor
I'm totally new to sql, so maybe it's a dumb question.
I've a database with email as a primary key, I need to find if there's an entry with this email; if is there, update records, otherwise append them.
Here my (ugly) function :

bool ProtectDB::Set(VectorMap<String, Value> const &data)
{
	String eMail = data.Get("EMAIL");
	
	SQL * Select(SqlAll()).From(USERS).Where(EMAIL == eMail);
	if(SQL.Fetch())
	{
		SQL * Update(USERS)
			(NAME			, data.Get("NAME"))
			(ADDRESS		, data.Get("ADDRESS"))
			(COUNTRY		, data.Get("COUNTRY"))
			(ZIP			, data.Get("ZIP"))
			(PHONE			, data.Get("PHONE"))
			(FAX			, data.Get("FAX"))
			(CELL			, data.Get("CELL"))
			(LICENSES		, data.Get("LICENSES"))
			(EXPIRATION		, data.Get("EXPIRATION"))
			(ACTIVATIONKEY	, data.Get("ACTIVATIONKEY"))
			(ACTIVATIONSENT	, data.Get("ACTIVATIONSENT"))
			(ACTIVATED		, data.Get("ACTIVATED"))
			.Where(EMAIL == eMail);
	}
	else
	{
		SQL * Insert(USERS)
			(EMAIL			, eMail)
			(NAME			, data.Get("NAME"))
			(ADDRESS		, data.Get("ADDRESS"))
			(COUNTRY		, data.Get("COUNTRY"))
			(ZIP			, data.Get("ZIP"))
			(PHONE			, data.Get("PHONE"))
			(FAX			, data.Get("FAX"))
			(CELL			, data.Get("CELL"))
			(LICENSES		, data.Get("LICENSES"))
			(EXPIRATION		, data.Get("EXPIRATION"))
			(ACTIVATIONKEY	, data.Get("ACTIVATIONKEY"))
			(ACTIVATIONSENT	, data.Get("ACTIVATIONSENT"))
			(ACTIVATED		, data.Get("ACTIVATED"))
		;
	}
}


Purpose is clear enough... data is passed in with a VectorMap.

Is there a "cleaner" way to do it ? maybe without repeating so many times the record names ?

Ciao

Max
Re: Question about SQL.... [message #29224 is a reply to message #29222] Mon, 11 October 2010 23:32 Go to previous messageGo to next message
unodgs is currently offline  unodgs
Messages: 1366
Registered: November 2005
Location: Poland
Ultimate Contributor

Try this:

struct MyFields
{
	VectorMap<String, Value> data;
	SqlId t;
	
	MyFields(const SqlId& table, const VectorMap<String, Value>& map)
	{
		data <<= map;
		t = table;
	}
	
	void FieldLayout(FieldOperator& f)
	{
		f.Table(~t);
		f
			(NAME			, data.Get("NAME"))
			(ADDRESS		, data.Get("ADDRESS"))
			(COUNTRY		, data.Get("COUNTRY"))
			(ZIP			, data.Get("ZIP"))
			(PHONE			, data.Get("PHONE"))
			(FAX			, data.Get("FAX"))
			(CELL			, data.Get("CELL"))
			(LICENSES		, data.Get("LICENSES"))
			(EXPIRATION		, data.Get("EXPIRATION"))
			(ACTIVATIONKEY	, data.Get("ACTIVATIONKEY"))
			(ACTIVATIONSENT	, data.Get("ACTIVATIONSENT"))
			(ACTIVATED		, data.Get("ACTIVATED"));
	}
	
	operator Fields() { return callback(this, &MyFields::FieldLayout); }
};

String eMail = data.Get("EMAIL");

SQL * Select(SqlAll()).From(USERS).Where(EMAIL == eMail);

MyFields fields(USERS, data);
	
if(SQL.Fetch())
{
	SQL * Update(fields).Where(EMAIL == eMail);
}
else
{
	SQL * Insert(fields)
		(EMAIL, eMail);
}
Re: Question about SQL.... [message #29232 is a reply to message #29224] Tue, 12 October 2010 12:58 Go to previous message
mdelfede is currently offline  mdelfede
Messages: 1307
Registered: September 2007
Ultimate Contributor
Thank you Uno, I'll try it asap Smile

Max
Previous Topic: Always MySQL Link error
Next Topic: Sqlite schema fixes
Goto Forum:
  


Current Time: Thu Mar 28 17:09:02 CET 2024

Total time taken to generate the page: 0.01385 seconds