MySQL example code

[Warren Young-3]

jupiter.hce@... wrote:
>
> Are there any good reference / example code to show how to connect MySQL
> database, what is the whole procedule to get query response (data in
> buffer) using multithreads and cache?

Read the user manual:

        http://tangentsoft.net/mysql++/doc/html/userman/

At the very least, read chapters 1 through 3, plus 7.

Then, read README-examples.txt in the MySQL++ distribution, along with
the README for your platform and/or tool chain.  Then play with the
examples.

This should answer most of your questions.

--
MySQL++ Mailing List
For list archives: http://lists.mysql.com/plusplus
To unsubscribe:    http://lists.mysql.com/plusplus?unsub=lists@...

[jupiter.hce]

Hi,

Are there any good reference / example code to show how to connect MySQL
database, what is the whole procedule to get query response (data in
buffer) using multithreads and cache?

Thank you.

Kind Regards,

Jim

--
MySQL++ Mailing List
For list archives: http://lists.mysql.com/plusplus
To unsubscribe:    http://lists.mysql.com/plusplus?unsub=lists@...

[onlyreply-sql]

Hi All,

I'm not able to reuse mysqlpp::Query  object and program throws exception.  Although,I'm calling    reset() function on it.  Here si the code snippet...



    mysqlpp::Query sqlquery = commonDBCon.query(strquery);
    if (sqlresults = sqlquery.store()) {
            AppDN   = sqlresults[0]["AppDN"];
            AppName = sqlresults[0]["AppName"];
            AppDesc = sqlresults[0]["AppDescription"];
        sqlresults.clear();
        sqlquery.reset();
    }

    // Now, Load Application Step Items
    sprintf(strquery,"select * from ApplicationStepItems where AppDN=%d",nAppDN);
    mysqlpp::Query sqlquery2 = pSystem->m_pCommon->commonDBCon.query(strquery);
    //sqlquery.storein(AppStepItems);
    AppStepItems_Module temp;
    if (sqlresults = sqlquery2.store()) {
        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);
      }
 }


Can anyone please suggest me the solution ? 
My requirements is just to  access multiple tables and store records in appropriate structures/templates.


thanks

[Warren Young-3]

(Something in the way you posted this message is messed up: all these  
 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@... 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->m_pCommon-
> >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=lists@...

[onlyreply-sql]

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@...>
To: plusplus@...
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  
 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@... 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->m_pCommon-
> >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=onlyreply-sql@...

[Warren Young-3]

onlyreply-sql@... wrote:
> 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

Are you saying that it doesn't work when you pass the query string to
Connection::query()?  The examples do this in several places
successfully, and this is an intended feature, so if you can show an
instance where it doesn't work, I'd be interested in seeing compilable
code that shows it.

>             LeaveCriticalSection(&m_csDBAccess);

Not MySQL++ related, but this is highly error-prone code.  You should be
using an RAII pattern here to make sure that all code paths that exit
the function release the critical section mutex.  It appears fine now,
but future maintenance could add a code path that accidentally doesn't
release the mutex.  See the code for MySQL++'s Transaction class -- it's
short -- for the general idea.

--
MySQL++ Mailing List
For list archives: http://lists.mysql.com/plusplus
To unsubscribe:    http://lists.mysql.com/plusplus?unsub=lists@...

[Alexis Rodriguez Castedo]

Hi:

Try mutex instead critical section, mutexes have the advantage that can be
accessed by the threads of different processes. Critical section is often used
to protect a shared resource in order to synchronize the access order and (one
of the most important restriction for me) you cannot have a wait with a timeout
like mutex or semaphore,

Best regards,

Alex

----- Original Message -----
From: "Warren Young" <mysqlpp@...>
To: "MySQL++ Mailing List" <plusplus@...>
Sent: Monday, June 02, 2008 11:00 AM
Subject: Re: mysqlpp::Query Issue

> onlyreply-sql@... wrote:
>> 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
>
> Are you saying that it doesn't work when you pass the query string to
> Connection::query()?  The examples do this in several places successfully, and
> this is an intended feature, so if you can show an instance where it doesn't
> work, I'd be interested in seeing compilable code that shows it.
>
>>             LeaveCriticalSection(&m_csDBAccess);
>
> Not MySQL++ related, but this is highly error-prone code.  You should be using
> an RAII pattern here to make sure that all code paths that exit the function
> release the critical section mutex.  It appears fine now, but future
> maintenance could add a code path that accidentally doesn't release the mutex.
> See the code for MySQL++'s Transaction class -- it's short -- for the general
> idea.
>
> --
> MySQL++ Mailing List
> For list archives: http://lists.mysql.com/plusplus
> To unsubscribe:
> http://lists.mysql.com/plusplus?unsub=alexis.rodriguez@...
>
>

--
MySQL++ Mailing List
For list archives: http://lists.mysql.com/plusplus
To unsubscribe:    http://lists.mysql.com/plusplus?unsub=lists@...

[onlyreply-sql]

Hi Warren,

I was saying that if I declare  mysqlpp::Query sqlquery();   instead of      mysqlpp::Query sqlquery = m_pCommon->commonDBCon.query();   then I was not able to use 'sqlquery' object  for another transaction. I tried to do it  again to see what happens but it is not compiling because of  stream "<<" operation.
Anyway, It seems all is fine  now and I also removed CriticalSection calls and now using another Mutex object.  
SysMutex is predefined object which I have been using other places and destructor of this class takes care of releasing.

Here is  final code...

    SysMutex_var mutex = m_pCommon->m_mutexDBAccess;
    if (!mutex.waitForMutex(INFINITE))
    {                      
        LOG_WARN(logger,"Tel_app::Load:Error getting mutex");
        return -1;
    }
    try
    {
        // Load the app
        mysqlpp::Query sqlquery = m_pCommon->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");
            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");
            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");
            return -1;
        }    
    } // try
    catch (const mysqlpp::BadQuery& er) {
        // Handle any query errors
        LOG_ERROR(logger,"TelApp:Query error: " << er.what() );
        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);
        return -1;
    }
    catch (const mysqlpp::Exception& er) {
        // Catch-all for any other MySQL++ exceptions
        LOG_ERROR(logger,"TelApp:Exception: " << er.what() );
        return -1;
    }
    catch(...){
        LOG_ERROR(logger,"TelApp:Unknown Exception");
        return -1;
    }


----- Original Message ----
From: Warren Young <mysqlpp@...>
To: MySQL++ Mailing List <plusplus@...>
Sent: Monday, June 2, 2008 2:00:44 AM
Subject: Re: mysqlpp::Query  Issue

onlyreply-sql@... wrote:
> 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

Are you saying that it doesn't work when you pass the query string to
Connection::query()?  The examples do this in several places
successfully, and this is an intended feature, so if you can show an
instance where it doesn't work, I'd be interested in seeing compilable
code that shows it.

>             LeaveCriticalSection(&m_csDBAccess);

Not MySQL++ related, but this is highly error-prone code.  You should be
using an RAII pattern here to make sure that all code paths that exit
the function release the critical section mutex.  It appears fine now,
but future maintenance could add a code path that accidentally doesn't
release the mutex.  See the code for MySQL++'s Transaction class -- it's
short -- for the general idea.

--
MySQL++ Mailing List
For list archives: http://lists.mysql.com/plusplus
To unsubscribe:    http://lists.mysql.com/plusplus?unsub=onlyreply-sql@...