SELECT TMySQLStatement with parameters?

Hi,

I try to use prepared statements with parameters. It works perfectly for the insertions but I am unable to use them to select data.

Here is an example on how I try to do it :

// prepare statement with parameters string sql = "SELECT moname, updatetime FROM table1 WHERE moname = ?"; statement = (TMySQLStatement*) fMySQLServer->Statement(sql.c_str()); statement->NextIteration(); statement->SetString(0, "test1"); statement->NextIteration(); statement->SetString(0, "test2"); // process statement and go through the results statement->Process(); statement->StoreResult(); while (statement->NextResultRow()) { t = statement->GetString(1); cout << "retrieved time : " << t << endl; }

It seems that it executes all the ‘SELECT’ but keep result only of the last one. Is it correct ?
Is it possible to achieve what I attempt to do ?
Should I simply have a loop and execute the SELECT one after the others ?

Thanks in advance,
Barth

Hi, Barth

I am not sure that such usage of statements is possible with MySQL.

Normally NextIteration used in INSERT queries and adds new row in the statement. Number of NextIteration calls defines number of rows added to the table.

You try to define statement, where several SELECT queries are combined. Most probably, MYSQL ignore all such queries in the beginning and just executes last one.

In your case you should create two statements with different arguments and execute them one after another. Anyhow, you need single NextIteration call if you want specify parameters for your select statement.

You probably can reformulate your select statement and specify both values together:

string sql = "SELECT moname, updatetime FROM table1 WHERE moname = ? OR moname = ?";
statement = fMySQLServer->Statement(sql.c_str());
statement->NextIteration();
statement->SetString(0, "test1");
statement->SetString(1, "test2"); 
statement->Process();
statement->StoreResult(); 
...

Regards,
Sergey

Hi Sergey,

Thanks for your reply. You confirm what I thought. I will simply do several SELECT. I used to have a single SELECT with OR’s conditions, but I have to joint now with other tables and it will be better to have several SELECT’s.

By the way, here is a link to an old MySQL blog entry where they give an example of prepared SELECT statement: http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html
However, I am not sure how this should be implemented into ROOT and if it should. For me it is fine as it is now.

Cheers,
Barth