How to find one value with DatabaseLayer Topic is solved

Talk here about issues with one of the components hosted at wxCode, or suggest features for it.
Post Reply
manianis
Experienced Solver
Experienced Solver
Posts: 72
Joined: Mon Jan 15, 2007 11:00 am

How to find one value with DatabaseLayer

Post by manianis » Sun Jan 28, 2007 10:58 pm

I'm trying to create a function that return only one value from a query like :

SELECT MAX(date_operation) AS dateStart FROM mytable

I'm using MySQL. I tried this function but it doesn't return the results I want.

Code: Select all

wxVariant MyApp::QueryValue(MysqlDatabaseLayer* myConn, wxString strQuery, wxString field)
{
	wxArrayString array;
	DatabaseResultSet* myRes = NULL;
	wxVariant value = wxEmptyString;

	myRes = myConn->RunQueryWithResults(strQuery);
	if (myRes)
	{
		if (myRes->Next())
		{
			value = myRes->GetResultString(field);
		}
		myConn->CloseResultSet(myRes);
		// delete myRes;
	}

	return value;
}

jb_coder
Super wx Problem Solver
Super wx Problem Solver
Posts: 267
Joined: Mon Oct 18, 2004 10:55 am

Post by jb_coder » Sun Jan 28, 2007 11:47 pm

If the field is a date, you might be able to get the value as a wxDateTime with GetResultDate().

manianis
Experienced Solver
Experienced Solver
Posts: 72
Joined: Mon Jan 15, 2007 11:00 am

Post by manianis » Mon Jan 29, 2007 8:24 am

I just need a generic function that returns only one value not a resultset. Because the result of the query can be of anytype : date/double/string/...

manianis
Experienced Solver
Experienced Solver
Posts: 72
Joined: Mon Jan 15, 2007 11:00 am

Post by manianis » Mon Jan 29, 2007 8:47 am

Why not add a function in DatabaseLayer class like this :

Code: Select all

/// Run a select query to find only one result
virtual wxString RunQueryOneResult(const wxString& strQuery, int nField) = 0;
It's useful if the resultset contains only one value as a result.

gururamnath
Moderator
Moderator
Posts: 466
Joined: Sat Sep 18, 2004 2:49 am
Location: California, USA

Post by gururamnath » Mon Jan 29, 2007 10:53 pm

manianis wrote:Why not add a function in DatabaseLayer class like this :

Code: Select all

/// Run a select query to find only one result
virtual wxString RunQueryOneResult(const wxString& strQuery, int nField) = 0;
It's useful if the resultset contains only one value as a result.
I have added some dblayer specific functions to http://wxSnippets.com. I pulled these snippets from a working project.
Have a look at :

http://wxsnippets.com/view_article.php?num=103

-Guru Kathiresan
wxWidgets Form Designer for Delphi and C++ Builder - http://www.twinforms.com

Code snippets for wxWidgets - http://wxsnippets.com

manianis
Experienced Solver
Experienced Solver
Posts: 72
Joined: Mon Jan 15, 2007 11:00 am

Post by manianis » Tue Jan 30, 2007 11:55 am

Thank you very much the snippet are very usefull. I'll use it to find one column result of the query.

I've added this lines to the MysqlDatabaseLayer class in order to retrieve only one wxString scalar value. Because I found that adding this functions to my program will be more complicated than adding it directly to the library.

Code: Select all

wxString MysqlDatabaseLayer::RunQueryOneResult(const wxString& strQuery, int nField)
{
	wxCharBuffer sQuery = ConvertToUnicodeStream(strQuery);
	MYSQL_RES* res;
	MYSQL_ROW row;

	if (mysql_query(m_pDatabase, sQuery))
	{
	  SetErrorCode(MysqlDatabaseLayer::TranslateErrorCode(mysql_errno(m_pDatabase)));
	  SetErrorMessage(ConvertFromUnicodeStream(mysql_error(m_pDatabase)));
      ThrowDatabaseException();
      return wxEmptyString;
	}
	res = mysql_store_result(m_pDatabase);
	if (!res)
	{
	  SetErrorCode(MysqlDatabaseLayer::TranslateErrorCode(mysql_errno(m_pDatabase)));
	  SetErrorMessage(ConvertFromUnicodeStream(mysql_error(m_pDatabase)));
    ThrowDatabaseException();
    return wxEmptyString;
	}
	
	if (!(row = mysql_fetch_row(res)))
	{
	  SetErrorCode(MysqlDatabaseLayer::TranslateErrorCode(mysql_errno(m_pDatabase)));
	  SetErrorMessage(ConvertFromUnicodeStream(mysql_error(m_pDatabase)));
	  mysql_free_result(res);
    ThrowDatabaseException();
    return wxEmptyString;
	}
	
	wxString value = ConvertFromUnicodeStream(row[nField]);
	mysql_free_result(res);
	
	return value;
}
If there's some bugs please tell me...

jb_coder
Super wx Problem Solver
Super wx Problem Solver
Posts: 267
Joined: Mon Oct 18, 2004 10:55 am

Post by jb_coder » Thu Feb 01, 2007 1:34 am

Guru, thanks for those snippets! I've tried adding those API into the DatabaseLayer class in CVS. I massaged the function signatures to be similar to the existing DatabaseResultSet API. Mainly the change is that the database value is returned from the function. If no records are found or more than one record is found, an exception is thrown from the function. Currently, only recalling the field by index rather than name is checked into CVS. I need to re-factor the code to handle retrieving by column name or index without a lot of duplicate code in the DatabaseLayer class.

I'd appreciate any feedback.
Thanks!

manianis
Experienced Solver
Experienced Solver
Posts: 72
Joined: Mon Jan 15, 2007 11:00 am

Post by manianis » Thu Feb 01, 2007 4:27 pm

Good Job jb_coder... :)

I've another question please... Why not adding this functionnalities directly into the appropriate files using the database C API. Is that difficult or impossible for all the supported DBs ?

jb_coder
Super wx Problem Solver
Super wx Problem Solver
Posts: 267
Joined: Mon Oct 18, 2004 10:55 am

Post by jb_coder » Thu Feb 01, 2007 6:10 pm

It would end up as unnecessarily duplicated code. It can be performed in the base class much easier without duplicating the code in each subclass. Also, the main functions (RunQuery/RunQueryWithResults) do most of the work and get most of the debugging. By leveraging this, the bugfixes can be more focused, and I have less worries about adding bugs (and memory leaks) because I'm using the functions that have been verified more thoroughly.

A third reason that I try to use the DatabaseLayer API is that it helps me to see the API as a user and potentially see how it can be more user-friendly.

gururamnath
Moderator
Moderator
Posts: 466
Joined: Sat Sep 18, 2004 2:49 am
Location: California, USA

Post by gururamnath » Thu Feb 01, 2007 8:00 pm

I'm glad that the snippets are helpful. I'm really looking forward to check the code in the CVS.

-regards,
Guru Kathiresan
wxWidgets Form Designer for Delphi and C++ Builder - http://www.twinforms.com

Code snippets for wxWidgets - http://wxsnippets.com

Post Reply