List:MySQL++« Previous MessageNext Message »
From:Doug Morrow Date:January 4 2011 3:14am
Subject:Re: Problem with Connection Pointers and Stored Procedures
View as plain text  
Alright got this problem pegged.  It's a matter of setting the option
before connection.  I realized now that this was the scenario that
caused it to work in the past, and when attempting to use the
connection pool I had to better understand the purpose of the create
function and what it was doing.  I ended up setting up the connection
object without connecting, setting the multistatement option, which
indeed seems to be necessary for some reason, and then connecting and
returning the pointer to the object.

Just for completeness I am posting the script to create the stored
procedure, I'd like to know what about the statement requires
multistatements to be set to true.

DELIMITER $$

DROP PROCEDURE IF EXISTS `Somnium`.`LookupAccounts`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `LookupAccounts`()
BEGIN
SELECT * FROM Accounts;
END$$

DELIMITER ;

I'm using mysql query browser to generate the initial scripts, and
modifying them to suit my needs.  Are these scripts flawed in some
manner?


On Mon, Jan 3, 2011 at 4:17 PM, Doug Morrow <xsubterfugex@stripped> wrote:
> I rewrote this code a few times in debugging it. I will definitely take your
> suggestions on more effectively using the stringstream object. The memory
> clean up is below the rest of this snippet though. The contents of the
> stored procedure is "SELECT * FROM Accounts;". If I replace the parsed
> statement with this it will execute properly and return the desired results.
> If I remove the pointer and just make the connection object itself
> (mysqlpp::Connection conn(false);) the stored procedure will execute
> flawlessly as well. I added the try catch block originally when I decided to
> pass true to the constructor and see if the exception is different, however,
> it is identical.
>
> On Jan 3, 2011 4:11 AM, "Warren Young" <mysqlpp@stripped> wrote:
>> On Jan 3, 2011, at 1:55 AM, Doug Morrow wrote:
>>
>>> std::stringstream ss (std::stringstream::in | std::stringstream::out);
>>
>> It's probably not relevant to your immediate problem, but I can't help but
>> wonder, why don't you just use ostringstream here?
>>
>>> mysqlpp::Connection* conn = new mysqlpp::Connection(false);
>>
>> [snip]
>>
>>> catch(mysqlpp::Exception e)
>>> {
>>> std::cerr << "mysql++ exception thrown: " << e.what() <<
> std::endl;
>>> }
>>
>>
>> You've disabled exceptions in the Connection creation, so your try block
>> does nothing. You're checking return values as well, which is good since
>> exceptions won't be thrown, but you should pick one style or the other.
>>
>>> char* qstring = new char[256];
>>> ss.getline( qstring, 256 );
>>>
>>> std::cout << qstring << std::endl;
>>> if (conn->connect("Somnium", "localhost", "Somnium", "password"))
>>> {
>>> mysqlpp::Query query(conn->query((const char*)qstring));
>>
>> Like with stringstream, you're making things hard on yourself here, too.
>> You can get rid of the first two lines (and thus the need to manage that bit
>> of memory) and replace the last with:
>>
>> mysqlpp::Query query(conn->query(ss.str()));
>>
>>> if(conn->set_option(new mysqlpp::MultiStatementsOption(true)))
>>
>> Your query string doesn't contain multiple SQL queries, so you don't need
>> this option.
>>
>>> if(conn->set_option(new mysqlpp::MultiResultsOption(true)))
>>> std::cout << "MultiResults Set" << std::endl;
>>> else
>>> std::cout << "MultiResults NOT Set" << std::endl;
>>
>> You report that the 'else' doesn't happen, but you should restructure your
>> code to make the set_option() call mandatory. If you can't set the
>> multi-results option, the stored procedure call will fail.
>>
>>> StoredProcedure Call Failed(LookupAccounts): PROCEDURE
>>> Somnium.LookupAccounts can't return a result set in the given context
>>
>> I don't really know what that means, but Googling for that error string
>> brings me to the MySQL manual:
>>
>> http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html
>>
>> Apparently you're trying to do something in your stored procedure that
>> MySQL doesn't support.
>>
>>> This works fine if I revert the connection object to a static one.
>>
>> I doubt it. I modified examples/multiquery.cpp like so, and it works just
>> fine:
>>
>>> Index: examples/multiquery.cpp
>>> ===================================================================
>>> --- examples/multiquery.cpp (revision 2683)
>>> +++ examples/multiquery.cpp (working copy)
>>> @@ -145,17 +145,17 @@
>>> // interface. If you're familiar with the underlying C API,
>>> // you know that there is poor consistency on these matters;
>>> // MySQL++ abstracts these differences away.
>>> - Connection con;
>>> - con.set_option(new MultiStatementsOption(true));
>>> + Connection* pcon = new Connection();
>>> + pcon->set_option(new MultiStatementsOption(true));
>>>
>>> // Connect to the database
>>> - if (!con.connect(mysqlpp::examples::db_name, cmdline.server(),
>>> + if (!pcon->connect(mysqlpp::examples::db_name, cmdline.server(),
>>> cmdline.user(), cmdline.pass())) {
>>> return 1;
>>> }
>>>
>>> // Set up query with multiple queries.
>>> - Query query = con.query();
>>> + Query query = pcon->query();
>>> query << "DROP TABLE IF EXISTS test_table; " <<
>>> "CREATE TABLE test_table(id INT); " <<
>>> "INSERT INTO test_table VALUES(10); " <<
>>> @@ -187,6 +187,7 @@
>>> cout << "Query: " << query << endl;
>>> print_multiple_results(query);
>>> #endif
>>> + delete pcon;
>>>
>>> return 0;
>>> }
>>
>>
>> By the way, I don't see any delete calls for the objects you've created.
>>
>> It may be that you have a memory bug which is making you believe you have
>> this problem with MySQL++. If you're testing on Linux, try running your
>> program under valgrind. See this FAQ item before reporting errors:
>>
>> http://tangentsoft.net/mysql++/#memleak
>>
>> It may be that changing to ostringstream like I suggested will fix your
>> problems, if the issue is that you're overrunning that 256 byte buffer.
>> Letting C++ manage your strings for you solves a lot of problems.
>> --
>> MySQL++ Mailing List
>> For list archives: http://lists.mysql.com/plusplus
>> To unsubscribe:
>> http://lists.mysql.com/plusplus?unsub=1
>>
>
Thread
Problem with Connection Pointers and Stored ProceduresDoug Morrow3 Jan
  • Re: Problem with Connection Pointers and Stored ProceduresWarren Young3 Jan
    • Re: Problem with Connection Pointers and Stored ProceduresTomalak Geret'kal3 Jan
    • Re: Problem with Connection Pointers and Stored ProceduresDoug Morrow4 Jan
      • Re: Problem with Connection Pointers and Stored ProceduresWarren Young4 Jan
      • Re: Problem with Connection Pointers and Stored ProceduresDoug Morrow4 Jan
        • Re: Problem with Connection Pointers and Stored ProceduresChris Morgan4 Jan
          • Re: Problem with Connection Pointers and Stored ProceduresDoug Morrow4 Jan