Author: wyoung
Date: Thu Mar 5 19:05:38 2009
New Revision: 2476
URL: http://svn.gna.org/viewcvs/mysqlpp?rev=2476&view=rev
Log:
Expanded the discussion of ways to run into the "Commands out of sync"
error to cover stored procedures and multi-queries.
Modified:
trunk/doc/userman/tutorial.dbx
Modified: trunk/doc/userman/tutorial.dbx
URL:
http://svn.gna.org/viewcvs/mysqlpp/trunk/doc/userman/tutorial.dbx?rev=2476&r1=2475&r2=2476&view=diff
==============================================================================
--- trunk/doc/userman/tutorial.dbx (original)
+++ trunk/doc/userman/tutorial.dbx Thu Mar 5 19:05:38 2009
@@ -126,7 +126,7 @@
database:</para>
<screen>
-CREATE USER mysqlpp_test@'%' IDENTIFIED BY 'nunyabinness';
+CREATE USER mysqlpp_test@'%' IDENTIFIED BY ’nunyabinness';
GRANT ALL PRIVILEGES ON mysql_cpp_data.* TO mysqlpp_test@'%';</screen>
<para>You could then create the sample database with the following
@@ -259,7 +259,7 @@
{
mysqlpp::NoExceptions ne(con);
if (!con.select_db("a_db_that_might_not_exist_yet")) {
- // Our DB doesn't exist yet, so create and select it here; no need
+ // Our DB doesn’t exist yet, so create and select it here; no need
// to push handling of this case way off in an exception handler.
}
}</programlisting>
@@ -354,12 +354,12 @@
Buns” instead. The resulting query would be:</para>
<programlisting>
-SELECT * FROM stock WHERE item = 'Frank's Brand Hotdog Buns' </programlisting>
+SELECT * FROM stock WHERE item = 'Frank’s Brand Hotdog Buns'
</programlisting>
<para>That’s not valid SQL syntax. The correct syntax is:</para>
<programlisting>
-SELECT * FROM stock WHERE item = 'Frank''s Brand Hotdog Buns' </programlisting>
+SELECT * FROM stock WHERE item = 'Frank'’s Brand Hotdog Buns'
</programlisting>
<para>As you might expect, MySQL++ provides that feature, too,
through its <type>escape</type> manipulator. But here, we want both
@@ -367,7 +367,7 @@
manipulator:</para>
<programlisting>
-string s = "Frank's Brand Hotdog Buns";
+string s = "Frank’s Brand Hotdog Buns";
query << "SELECT * FROM stock WHERE item = " << quote << s;
</programlisting>
<para>The <type>quote</type> manipulator both quotes strings and
@@ -1070,46 +1070,90 @@
<title>Concurrent Queries on a Connection</title>
<para>An important limitation of the MySQL C API library —
- which MySQL++ is built atop, so it shares this limitation — is
- that you can’t have two concurrent queries running on a single
- connection. If you try, you get an obscure error message about
+ which MySQL++ is built atop, so it shares this limitation —
+ is that you can only have one query in progress on each connection
+ to the database server. If you try to issue a second query while
+ one is still in progress, you get an obscure error message about
“Commands out of sync” from the underlying C API
- library. (You get it in a MySQL++ exception unless you have
- exceptions disabled, in which case you get a failure code and
- <methodname>Connection::error()</methodname> returns this
- message.)</para>
-
- <para>The easiest way to cause this error is in a multithreaded
- program where you have a single <ulink type="classref"
- url="Connection"/> object, but allow multiple threads to issue
- queries on it. Unless you put in a lot of work to synchronize
- access, this is almost guaranteed to fail.</para>
-
- <para>If you give each thread that issues queries has its own
- <classname>Connection</classname> object, you can still run into
- trouble if you pass the data you get from queries around to other
- threads. What can happen is that one of these child objects
- indirectly calls back to the <classname>Connection</classname> at a
- time where it’s involved with another query. (There are other
- ways to run into trouble when sharing MySQL++ data structures among
- threads, but the whole topic is complex enough to deserve its own
- chapter, <xref linkend="threads"/>.)</para>
-
- <para>It’s possible to run into this problem in a
- single-threaded program as well. As discussed above (<xref
- linkend="querytypes"/>), one of the options MySQL offers for
- executing a query lets you issue the query, then consume the rows
- one at a time, on demand: it’s the “use” query. If
- you don’t consume all rows from a query before you issue
- another on that connection, you are effectively trying to have
- multiple concurrent queries on a single connection, and you end up
- with the same problem. The simplest recipie for disaster is:</para>
-
- <programlisting>
+ library. (You normally get this message in a MySQL++ exception
+ unless you have exceptions disabled, in which case you get a
+ failure code and <methodname>Connection::error()</methodname>
+ returns this message.)</para>
+
+ <para>There are lots of ways to run into this limitation:</para>
+
+ <itemizedlist>
+ <listitem>
+ <para>The easiest way is to try to use a single <ulink
+ type="classref" url="Connection"/> object in a multithreaded
+ program, with more than one thread attempting to use it to
+ issue queries. Unless you put in a lot of work to synchronize
+ access, this is almost guaranteed to fail at some point, giving
+ the dread “Commands out of sync” error.</para>
+ </listitem>
+
+ <listitem>
+ <para>You might then think to give each thread that issues
+ queries its own <classname>Connection</classname> object.
+ You can still run into trouble if you pass the data you get
+ from queries around to other threads. What can happen is
+ that one of these child objects indirectly calls back to the
+ <classname>Connection</classname> at a time where it’s
+ involved with another query. This and other topics is properly
+ covered elsewhere, in <xref linkend="threads"/>.)</para>
+ </listitem>
+
+ <listitem>
+ <para>One way to run into this problem without using
+ threads is with “use” queries, discussed <link
+ linkend="querytypes">above</link>. If you don’t
+ consume all rows from a query before you issue another on
+ that connection, you are effectively trying to have multiple
+ concurrent queries on a single connection. Here’s a
+ recipie for this particular disaster:</para>
+
+ <programlisting>
UseQueryResult r1 = query.use("select garbage from plink where
foobie='tamagotchi'");
UseQueryResult r2 = query.use("select blah from bonk where
bletch='smurf'");</programlisting>
- <para>The second <methodname>use()</methodname> call fails because
- the first result set hasn’t been consumed yet.</para>
+ <para>The second <methodname>use()</methodname> call fails because
+ the first result set hasn’t been consumed yet.</para>
+ </listitem>
+
+ <listitem>
+ <para>Still another way to run into this limitation
+ is if you use MySQL’s multi-query feature. This
+ lets you give multiple queries in a single call,
+ separated by semicolons, and get back the results for
+ each query separately. If you issue three queries using
+ <methodname>Query::store()</methodname>, you only get
+ back the first query’s results with that call, and
+ then have to call <methodname>store_next()</methodname>
+ to get the subsequent query results. MySQL++ provides
+ <methodname>Query::more_results()</methodname> so
+ you know whether you’re done, or need to call
+ <methodname>store_next()</methodname> again. Until you reach
+ the last result set, you can’t issue another query on
+ that connection.</para>
+ </listitem>
+
+ <listitem>
+ <para>Finally, there’s a way to run into this
+ that surprises almost everyone sooner or later: stored
+ procedures. MySQL normally returns <emphasis>at least
+ two</emphasis> result sets for a stored procedure call. The
+ simple case is that the stored procedure contains a single
+ SQL query, and it succeeds: you get two results, first the
+ results of the embedded SQL query, and then the result
+ of the call itself. If there are multiple SQL queries
+ within the stored procedure, you get more than two result
+ sets. Until you consume them all, you can’t start a
+ new query on the connection. As above, you want to have
+ a loop calling <methodname>more_results()</methodname>
+ and <methodname>store_next()</methodname> to work your
+ way through all of the result sets produced by the stored
+ procedure call.</para>
+ </listitem>
+ </itemizedlist>
</sect2>
</sect1>
_______________________________________________
Mysqlpp-commits mailing list
[email protected]
https://mail.gna.org/listinfo/mysqlpp-commits
