MySQL APIのuse()にハマる
C++からMySQLデータベースにアクセスする際，主にMySQL++を使っています．
MySQL++: MySQL++
MySQL++ is a C++ wrapper for MySQL’s C API. It is built around the same principles as the Standard C++ Library, to make dealing with the database as easy as dealing with STL containers. In addition, MySQL++ provides facilities that let you avoid the most repetitive sorts of SQL within your own code, providing native C++ interfaces for these common tasks.
データベースからデータをSELECT文等で取り出す際，以前はなんでもstore()を使っていたのですが，
MySQL++: MySQL++
StoreQueryResult set type for "store" queries.
This is the obvious C++ implementation of a class to hold results from a SQL query that returns rows: a specialization of std::vector holding Row objects in memory so you get random-access semantics. MySQL++ also supports UseQueryResult which is less friendly, but has better memory performance. See the user manual for more details on the distinction and the usage patterns required.
と書いてあるので，use()の方を使ってみることにしました．
MySQL++: MySQL++
StoreQueryResult set type for "use" queries.
See the user manual for the reason you might want to use this even though its interface is less friendly than StoreQueryResult's.
あらかじめMAX_ROWの数だけ行のある(key, value)ペアのテーブルを作っておき，
UseQueryResult results;
Row row;
for(int key=0;key<MAX_ROW;++key)
{
query<<"SELECT value FROM bench WHERE mykey="<<rand()%MAX_ROW;
results = query.use();
if(results)
{
row = results.fetch_row();
cout<<"result of key["<<key<<"] = "<<row["value"]<<endl;
}else
{
cout<<"cannot fetch results for key["<<key<<"]"<<endl;
cout<<"reason:"<<query.error()<<endl;
}
} というプログラムをコンパイルして実行しました．
# icc bench.cc -I/usr/local/include/mysql++/ -I /usr/local/mysql/include/ -lmysqlpp # ./a.out result of key[0] = val:3960886519 terminate called after throwing an instance of 'mysqlpp::BadQuery' what(): Commands out of sync; you can't run this command now アボートしました
．．．．（泣）
store()を使うと，普通に動くんですけどねぇ．
StoreQueryResult results;
int success = 0;
for(int key=0;key<MAX_ROW;++key)
{
Row row;
query<<"SELECT value FROM bench WHERE mykey="<<rand()%MAX_ROW;
results = query.store();
if(results)
{
row = results.at(0);
{
cout<<"result of key["<<key<<"] = "<<row["value"]<<endl;
++success;
}
}else
{
cout<<"cannot fetch results for key["<<key<<"]"<<endl;
cout<<"reason:"<<query.error()<<endl;
}
}これをコンパイルして実行すると，
# ./a.out |head -10 result of key[0] = val:8841 result of key[1] = val:9924930093 result of key[2] = val:4928046 result of key[3] = val:080686 result of key[4] = val:91069 result of key[5] = val:273 result of key[6] = val:5618715 result of key[7] = val:5880 result of key[8] = val:3223180299386 result of key[9] = val:17513
答えは，MySQL C APIのドキュメントにありました．
MySQL :: MySQL 5.6 リファレンスマニュアル :: 23.8.7.72 mysql_use_result()
mysql_use_result()を使うとき、NULL値が戻されるまで、mysql_fetch_row()を実行しなければなりません。C APIは、これを行うのを忘れた場合、エラーCommands out of sync; you can't run this command nowを附与します。
あ．．．まさに，「Commands out of sync」エラーが．．．
よって，例えば次のように，「NULL値が戻されるまでfetch_rowする」ようにプログラムを書き換えますと，無事に動きました．
UseQueryResult results;
Row row;
for(int key=0;key<MAX_ROW;++key)
{
query<<"SELECT value FROM bench WHERE mykey="<<rand()%MAX_ROW;
results = query.use();
if(results)
{
while(row = results.fetch_row())
{
cout<<"result of key["<<key<<"] = "<<row["value"]<<endl;
}
}else
{
cout<<"cannot fetch results for key["<<key<<"]"<<endl;
cout<<"reason:"<<query.error()<<endl;
}
} store()とuse()で，利用方法によってパフォーマンスが変わるのか，とかベンチマークをする予定だったのですが，今日はここまでで力尽きました．．．続きは明日です．
