Re: SUBTIME

[Rick Gutleber]

Demetris:

I came up with this method that I use for queries that return a single 
value.  I took out the app-specific stuff and exception handling to make 
it more readable. "log" is just a representation of the stream that gets 
written out to the log file.  The method returns a mysqlpp::String 
object which you can cast to whatever type it's supposed to be.

Rick


mysqlpp::String DBConnection::findSingleValue( const bamString & 
strQuery, bool bWarn ) {
    mysqlpp::String value;
    value.it_is_null( );  

    mysqlpp::StoreQueryResult result;

    if ( store( strQuery, result ) ) {
        if ( result.num_rows( ) < 1 ) {
            if ( bWarn ) {
                log << "query '" << strQuery << "' found zero results 
when one was expected" << std::endl;
            }

            return value;   // nothing to do but return an empty result
        } else if ( ( result.num_rows( ) > 1 ) && bWarn ) {
            log << "query '" << text << "' found multiple records when 
only one was expected";
        }

        if ( ( result[ 0 ].size( ) > 1 ) && bWarn ) {
            log << "query '" << strQuery <<
                    "' found one result with multiple columns when only 
one was expected" << std::endl;
        }

        // it seems pointless to check if the result has 0 columns...
        value = result[ 0 ][ 0 ];
    } else {
        ost << "query '" << strQuery << "' caused store( ) to fail"
<< 
std::endl;
    }

    return value;
}

bool DBConnection::store( const std::string & strQuery, 
mysqlpp::StoreQueryResult & result ) {
    if ( !con ) {
        return false;
    }

    try {
        result = con->query( ).store( strQuery.c_str( ) );
    }
    catch {
       // ... exception handling here...
    }

    return true;
}






Demetris Zavorotnichenko wrote:
> I need to make a query that would do time calculation
>
>  mysqlpp::String DBConnection::findSingleValue( const std::string & strQuery,
> bool bWarn ) {
>     mysqlpp::String value;
>     value.it_is_null( );   // who came up with _that_ name?!
>
>     mysqlpp::StoreQueryResult result;
>
>     if ( store( strQuery, result ) ) {
>         if ( ( result.num_rows( ) > 1 ) && bWarn ) {
>             log << "query '" << text << "' found multiple records
> when only one was expected" << std::endl;
>         }
>
>         if ( ( result[ 0 ].size( ) > 1 ) && bWarn ) {
>             TRUNCATE_QUERY( strQuery, text )
>
>             std::ostringstream ost;
>
>             ost << "query '" << text <<
>                     "' found one result with multiple columns when only one was
> expected" << std::endl;
>
>             logFWarning( ) << ost.str( ) << std::endl;;
>
>             createEvent( bam::EventBAMWarning, "warning", ost.str( ) );
>         }
>
>         if ( result.num_rows( ) < 1 ) {
>             if ( bWarn ) {
>                 TRUNCATE_QUERY( strQuery, text )
>
>                 std::ostringstream ost;
>
>                 ost << "query '" << text << "' found no result when
> one was expected" << std::endl;
>
>                 logFWarning( ) << ost.str( ) << std::endl;;
>
>                 createEvent( bam::EventBAMWarning, "warning", ost.str( ) );
>             }
>         } else {
>             // it seems pointless to check if the result has 0 columns...
>             value = result[ 0 ][ 0 ];
>         }
>     }
>
>     return value;
> }
>
>
>
>  
>
> SELECT SUBTIME('2007-12-31 23:59:59.999999','1 1:1:1.000002')
>
>  
>
> So how would I get a result from this query ?
>
>  
>
> With a simple result ?
>
>  
>
> mysqlpp::SimpleResult res = query.execute();
>
>  
>
> or how ?
>
>  
>
> PLS help out.
>
>
>