List:MySQL++« Previous MessageNext Message »
From:onlyreply-sql Date:June 2 2008 4:49am
Subject:Re: mysqlpp::Query Issue
View as plain text  
Thanks Warren for detailed reply ! 
Sorry for garbled code and after reading your reply & user manual , finally I got
everything working.  I think the problem was with default constructor on query object not
properly being initialized.   Once I  initialized sqlquery object like this ...
mysqlpp::Query sqlquery = commonDBCon.query(); , the problem went away and I'm able to
use this object with multiple queries/tables.  I also removed 'reset' & 'clear'
function calls.   Here is the final code and please take a look when you get chance and
let me know if it's not right way of doing it.  I can store result directly in my
container also but  issue is I use some  enum types & also want to restrict some
values.

    EnterCriticalSection(&m_csDBAccess);
    try 
    {
        // Load the app
        mysqlpp::Query sqlquery = commonDBCon.query();
        mysqlpp::StoreQueryResult sqlresults;
        
        // Form query first        
        sqlquery << "select * from Applications where AppDN=" << AppDN; 
        sqlresults = sqlquery.store();
        if(sqlresults) {
            //for (size_t i = 0; i < res.num_rows(); ++i) {  // one app only
                m_nAppDN   = sqlresults[0]["AppDN"];
                m_sAppName = sqlresults[0]["AppName"]; 
                m_sAppDesc = sqlresults[0]["AppDescription"];
            //}
        }
        else {
            LOG_ERROR(logger,"Failed to get AppInfo");
            LeaveCriticalSection(&m_csDBAccess);
            return -1;
        } 
        // Now, Load Application Steps
        sqlquery << "select * from ApplicationSteps where AppDN=" << AppDN; 
        sqlresults = sqlquery.store();

        AppSteps_Module temp2;
        if (sqlresults) {
            for (size_t i = 0; i < sqlresults.num_rows(); ++i) {  
                temp2.StepNumber = sqlresults[i]["StepNumber"];
                int StepType = sqlresults[i]["StepType"];
                temp2.StepType = (Step_Type)StepType;
                temp2.PromptID= sqlresults[i]["PromptID"];
                temp2.PromptFileName  = sqlresults[i]["PromptFileName"];
                temp2.PromptText        = sqlresults[i]["PromptText"];
                temp2.MaxDigits        = sqlresults[i]["MaxDigits"];
                temp2.MaxSeconds        = sqlresults[i]["MaxSeconds"];
                temp2.TermTones        = sqlresults[i]["TermTones"];
                temp2.ValidDigits        = sqlresults[i]["ValidDigits"];
                AppSteps.push_back(temp2);
            }
        }
        else {
            LOG_ERROR(logger,"Failed to get AppSteps");
            LeaveCriticalSection(&m_csDBAccess);
            return -1;
        }    

        // Now, Load Application Step Items 
        sqlquery << "select * from ApplicationStepItems where AppDN=" <<
AppDN; 
        sqlresults = sqlquery.store();
        AppStepItems_Module temp;
        if (sqlresults) {
            for (size_t i = 0; i < sqlresults.num_rows(); ++i) {  
                //AppStepItems.push_back(sqlresults[i]);
                temp.StepNumber = sqlresults[i]["StepNumber"];
                temp.AppKeypadId = sqlresults[i]["AppKeypadId"];
                temp.NextStepNumber = sqlresults[i]["NextStepNumber"];
                AppStepItems.push_back(temp);
            }
        }
        else {
            LOG_ERROR(logger,"Failed to get AppStepItems");
            LeaveCriticalSection(&m_csDBAccess);
            return -1;
        }    
    } // try
    catch (const mysqlpp::BadQuery& er) {
        // Handle any query errors
        LOG_ERROR(logger,"TelApp:Query error: " << er.what() );
        LeaveCriticalSection(&m_csDBAccess);
        return -1;
    }
    catch (const mysqlpp::BadConversion& er) {
        // Handle bad conversions; e.g. type mismatch populating 'stock'
        LOG_ERROR(logger,"TelApp:Conversion error: " << er.what() << "
tretrieved data size: " << er.retrieved <<", actual size: " <<
er.actual_size);
        LeaveCriticalSection(&m_csDBAccess);
        return -1;
    }
    catch (const mysqlpp::Exception& er) {
        // Catch-all for any other MySQL++ exceptions
        LOG_ERROR(logger,"TelApp:Exception: " << er.what() );
        LeaveCriticalSection(&m_csDBAccess);
        return -1;
    }
    catch(...){
        LOG_ERROR(logger,"TelApp:Unknown Exception");
        LeaveCriticalSection(&m_csDBAccess);
        return -1;
    }

    LeaveCriticalSection(&m_csDBAccess);


----- Original Message ----
From: Warren Young <mysqlpp@stripped>
To: plusplus@stripped
Sent: Sunday, June 1, 2008 5:27:57 PM
Subject: Re: mysqlpp::Query  Issue

(Something in the way you posted this message is messed up: all these  
&nbsp;s make reading it a lot harder than it ought to be.  Please find  
and fix this problem.)

On Jun 1, 2008, at 3:47 AM, onlyreply-sql@stripped wrote:

> sqlresults.clear();

It's not necessary to clear() the query result object.

> sqlquery.reset();

This reset() call is either misplaced, or unnecessary.

MySQL++ v3 auto-resets the query object after a successful execution  
unless you're using template queries.

It doesn't auto-reset with template queries because that would throw  
away the template.  This doesn't apply here because you're not using  
that feature.

It also doesn't auto-reset after an error executing the query because  
some people like to send the failed SQL out to the debug log when  
reporting query errors.  Thus, if I'm wrong and reset() is somehow  
needed in your program, the bug may be that you have the reset() call  
inside the if { } statement: if the query fails, you need to reset()  
the query object before reusing it.

> sprintf(strquery,"select * from ApplicationStepItems where AppDN= 
> %d",nAppDN);

It's not relevant to your problem, but I wanted to point out that  
there's no need to build the query string separately here.  MySQL++  
has two built-in mechanisms for building query strings like this  
already: template queries, and a C++ stream interface.

Template queries are very much like sprintf(), but I think  
inappropriate in this particular instance.  They make the most sense  
when you re-use the same basic query format multiple times.  In your  
case, you're giving a different type of query each time.

I'd use the stream interface:

    Query q = conn.query("select * from ApplicationStepItems where  
AppDN=");
    q << nAppDN;

This gives you several advantages.  You avoid the need for a temporary  
buffer, you avoid the possibility of a buffer overrun, you can use  
data types that sprintf() doesn't know how to cope with, and you get  
features like automatic quoting and escaping.

> mysqlpp::Query sqlquery2 = pSystem-&gt;m_pCommon- 
> &gt;commonDBCon.query(strquery);

Here we see that you're not reusing the Query object, so reset()  
cannot help.  Resetting the first Query object can't affect this one,  
even though they use the same Connection object.

> Can anyone please suggest me the solution ?

You haven't given enough information.  You just say it doesn't work,  
but don't give the error message. I may have accidentally hit on the  
solution above, but if you make all these changes and you still get  
the same problem, I wouldn't be surprised.  Post the error message  
you're getting.

-- 
MySQL++ Mailing List
For list archives: http://lists.mysql.com/plusplus
To unsubscribe:    http://lists.mysql.com/plusplus?unsub=1
Thread
MySQL example codejupiter.hce29 May
  • Re: MySQL example codeWarren Young29 May
  • mysqlpp::Query Issueonlyreply-sql1 Jun
    • Re: mysqlpp::Query IssueWarren Young2 Jun
Re: mysqlpp::Query Issueonlyreply-sql2 Jun
  • Re: mysqlpp::Query IssueWarren Young2 Jun
  • Re: mysqlpp::Query IssueAlexis Rodriguez Castedo2 Jun
Re: mysqlpp::Query Issuebsingh3 Jun