Are Prepared statements supported in embedded MySQL

Question

I normally develop with a live server, but for the first time I figured I'd make the leap and see if I could get all my (C++) mysql code working as an embedded server. Particularly, I'm very fond of prepared statements as they are (IMHO) "generally" superior to the non-prepared variety.

I've tried using libmysqld from 5.5.22 and libmysqld from 5.6.4 and neither work.

Connection is made, simple mysql_query / mysql_real_query commands work fine, but as soon as my first prepared statement issues a mysql_stmt_fetch() I get the hated 'commands out of sync' error.

A very similar issue appeard on oracles forums ( http://forums.mysql.com/read.php?168,507863,507863#msg-507863 ) without resolution.

I do not see, nor believe that I'm missing any commands between mysql_real_connect() and the mysql_stmt_fetch().

All my searches have come up empty for any example of an embedded server that is using prepared statements. Nor have I found an actual sentence "you can't do this".

So...is it or is it not supported?

Thank you for your expertise.

//edit so as to demystify this further (and instruct if necessary) my full mysql cmd sequence is as follows:

mysql_library_init();  // as embedded
mysql_init();
mysql_options(MYSQL_SET_CHARSET_NAME);  //to utf8
mysql_options(MYSQL_OPT_USE_EMBEDDED_CONNECTION);
mysql_real_connect();
mysql_real_query("SET NAMES 'utf8'");
mysql_real_query("SET CHARACTER SET 'utf8'");
mysql_set_character_set("utf8");  // yes, you really do need to set utf8 four times
mysql_autocommit( mAutocommit ); 

at this point, mysql_real_query() calls DO work. I continue...

//all this would only happen once for each stmt
{
    mysql_stmt_init();
    mysql_stmt_prepare(theQuery);
    mysql_stmt_param_count(); // to assert input bind object (aka the predicates) has the same number of params as theQuery
    mysql_stmt_result_metadata()
    mysql_num_fields(); // to assert the output bind object has the same number of params as theQuery
    mysql_free_result(metadata);
    mysql_stmt_bind_param(); // called IF there are input params
    mysql_stmt_bind_result(); // pretty much always called for the output params
}
// and at last
mysql_stmt_execute();
//mysql_stmt_store_result();  //{OPTIONAL: use if you want to buffer the fetch - I dont}
mysql_stmt_fetch();   // ERROR! commands out of sync.

// and for completeness, 
mysql_stmt_free_result();
mysql_stmt_close();

// and the shutdown
mysql_close();
mysql_library_end();

Embranchment

Answer 1

I was afraid of this.. but after no small amount of work, I have an answer to my question, and a solution to the problem. (yes I'm a lazy coder... i'd hoped someone else would have told me all this was necessary..hehe)

Here is my own authoritative answer to embedded server + prepared statements not working question.

the question: are stmts supported in embedded? the answer... they should be but they are NOT.

Yes, there is a bug in embedded mysql with respect to stmts. See: http://bugs.mysql.com/bug.php?id=62136

Mr Qi Zhou has all my respect. He somehow determined that when running embedded, mysql_stmt_execute() was incorrectly setting the result status to "MYSQL_STATUS_GET_RESULT" instead of "MYSQL_STATUS_STATEMENT_GET_RESULT" (i.e. treating a stmt like a non-statment) This obviously WOULD naturally lead to a "commands out of sync" error. So it requires patching the source code itself.

How to do that.. The MySql "how to build on windows" page here: http://dev.mysql.com/doc/refman/5.5/en/source-installation.html

references this much easier-to-read, HOW-TO-BUILD: http://www.chriscalender.com/?p=689

Additional HOW-TO notes I determined in the process

Chris's how to is for VS2008 express. I use 2010 Pro and learned the cmake -G arg can be ommitted. For me, 2010 was auto-determiend to be the compiler to use.

I only installed cmake and bison. perl and bazaar are not required for this. and I got the standard 5.5.22 source distro instead of pulling from bazaar.

re: installing bison:

• make sure to install bison to a path without spaces
• DO NOT allow installer to add anything to the start menu (caused the "m4.exe not found" error)
• manually add bison's bin folder to the system PATH

re: signtool.exe

insure the path to signtool is added to PATH. Example (for me)

• c:\Program Files\Microsoft SDKs\Windows\v7.0A\bin

Download the source distro of MySql ( http://dev.mysql.com/downloads/mysql/#downloads ): Generic Linux (Architecture Independent), Compressed TAR Archive (mysql-5.5.22.tar.gz)

You need to edit {D:\your_path}\mysql-5.5.22\libmysqld\lib_sql.cc

On line 340 you will see:

if (res)
{
    NET *net= &stmt->mysql->net;
    set_stmt_errmsg(stmt, net);
    DBUG_RETURN(1);
}
//ADD CODE HERE
DBUG_RETURN(0);

Insert between the if codeblock and DBUG_RETURN(0) the following:

//kgk 2012/04/11 - see http://bugs.mysql.com/bug.php?id=62136
//  Qi Zhou's modification to allow prep'd stmts to work
else if (stmt->mysql->status == MYSQL_STATUS_GET_RESULT)
{
    stmt->mysql->status= MYSQL_STATUS_STATEMENT_GET_RESULT;
}

And build yourself a new release version of libmysqld.dll, libmysqld.lib, libmysqld.pdb

and the problem is fixed.

When you have built the dll, don't do like me and forget to move the new DLL to your binary's runtime folder and sit there wondering why the change didn't do anything. Sigh.

FYI: oracle's techie's bug report comment tagged with [20 Feb 18:34] Sveta Smirnova is complete nonesense. the serverARgs has nothing to do with anything.

Embranchment

Answer 2

http://dev.mysql.com/doc/refman/5.1/en/mysql-stmt-execute.html

sorry for my bad english, but problem is that mysql_stmt_fetch open cursor, but mysql_stmt_execute only execute mysql_stmt_store_result work with cursor...

Nahtanha

my code path is as above, mysql_stmt_execute() followed by mysql_stmt_fetch(). This is perfectly implemented "stmt" code when connecting to a remote mysql server. It however generates an 'out of sync' error when applied to an embedded server. so.. your answer is not relevant. – Embranchment

Answer 3

Embedded MySQL is the one which is not normally use and for this problem I have searched the web lots of times and I have prepared a separate project. While doing so, I have learnt many things and one of thing is that first execute the query and then store the results.

To get rid of different types of query kindly create a object for execution and return the result and in this case you don't have to write execution query again and again.

In the present case also the user didn't execute the query and trying to store the results and this only happens when you try to copy others commands. Thus the solution to the problem is to execute mysql_stmt_execute and then call mysql_stmt_store_result

Natishanative

Thank you for your help. My question is not on the value of prepared statements but on information relating to whether or not they work with the embedded mysql library (ie no external server). For me, they are NOT working. secondly, calling mysql_stmt_store_result isn't relevant. For your reference see: dev.mysql.com/doc/refman/5.5/en/mysql-stmt-store-result.html if you are not buffering the fetch (which I am NOT), then you do NOT need to call store (which I dont). None-the-less, just to see, I tried your suggestion and the out of sync occurs at the store command. – Embranchment