Page 1 of 1

How to find one value with DatabaseLayer

Posted: Sun Jan 28, 2007 10:58 pm
by manianis
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;
}

Posted: Sun Jan 28, 2007 11:47 pm
by jb_coder
If the field is a date, you might be able to get the value as a wxDateTime with GetResultDate().

Posted: Mon Jan 29, 2007 8:24 am
by manianis
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/...

Posted: Mon Jan 29, 2007 8:47 am
by manianis
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.

Posted: Mon Jan 29, 2007 10:53 pm
by gururamnath
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

Posted: Tue Jan 30, 2007 11:55 am
by manianis
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...

Posted: Thu Feb 01, 2007 1:34 am
by jb_coder
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!

Posted: Thu Feb 01, 2007 4:27 pm
by manianis
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 ?

Posted: Thu Feb 01, 2007 6:10 pm
by jb_coder
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.

Posted: Thu Feb 01, 2007 8:00 pm
by gururamnath
I'm glad that the snippets are helpful. I'm really looking forward to check the code in the CVS.

-regards,
Guru Kathiresan