Hello.
I've modified your source code, and it works now. Use diff to see the
changes.
My test table is:
mysql> show create table lth\G;
*************************** 1. row ***************************
Table: lth
Create Table: CREATE TABLE `lth` (
`product` char(255) default NULL,
`facility` char(255) default NULL,
`lot` char(255) default NULL,
`trn` char(255) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
The test data is:
mysql> select * from lth;
+---------+----------+---------+------+
| product | facility | lot | trn |
+---------+----------+---------+------+
| gleb | XTEX6 | 5025267 | LOGI |
+---------+----------+---------+------+
1 row in set (0.00 sec)
The compilation string:
g++ -g -o p -I/usr/include/mysql -L/usr/lib/mysql p.cc -lmysqlclient
The results:
[EMAIL PROTECTED] egor]$ ./p
0x9407ed8
Connection to the LTS database was successful.
mysql_stmt_prepare() was successful
total parameters in SELECT: 2
Total number of columns in SELECT statement : 1
Fetching results...
Row 1
gleb(4)
Total rows SELECTED: 1
Total rows fetched: 1
The source code:
#ifdef HAVE_CONFIG_H
#include <config.h>
#endif
#include <mysql.h>
#include <iostream>
#include <cstdlib>
//#include "msql_conn_env.h"
#define STRING_SIZE 50
MYSQL_STMT *stmt;
MYSQL_BIND parm_bind[2], res_bind[2];
MYSQL_RES *ps_meta_result, *ps_results;
int parm_count, col_count, row_count, fetch_row_count;
unsigned long length, str_length[2];
my_bool is_null[1];
static char *sql_stmt = {
"select product "
"from lth "
"where facility = ? "
"and lot = ? "
"and trn = \"LOGI\" "
};
using namespace std;
void print_error(MYSQL *conn, char *message)
{
cerr << message << endl;
if (conn != NULL)
{
cerr << "ERROR " << mysql_errno(conn) << ": (" << mysql_error(conn) <<
")" << endl;
}//end if(conn != NULL)
}//end print_error
int main(int argc, char *argv[])
{
// MYSQL mysql;
MYSQL *conn;
//conn = &mysql;
char product[35], facility[50], lot[50];
string f_cility = "XTEX6";
conn=mysql_init(NULL);
cout << conn << endl;
if(conn==NULL)
{
print_error(conn,"F -- Database Login Failed!\n ");
exit(1);
} //end db init
/* if (!s)
{
s = getenv ("DB_LZS_CONNECT");
if (!s)
{
//error ("Umgebungs-Variable DB_LZS_CONNECT ist nicht definiert.");
//English translation needed:
cerr << "Environment Variable DB_LZS_CONNECT not defined!\n";
}
}
*/
//if (mysql_real_connect(conn,s) == NULL)
if (!mysql_real_connect(conn,"localhost","root","","test", 0, NULL,0) )
//MySQL connection --> NULL = Failure; a successful connection would return
first variable's value (i.e. MySQL connection handle).
{
printf("%d: %s \n",mysql_errno(conn),
mysql_error(conn));
exit(1);
}
else
{
cerr << "Connection to the LTS database was successful.\n";
}
stmt = mysql_stmt_init(conn);
if(!stmt)
{
cerr << "mysql_stmt_init() failure. Possibly out of memory\n";
exit(0);
}
if(mysql_stmt_prepare(stmt, sql_stmt, strlen(sql_stmt)))
{
cerr << "mysql_stmt_prepare(), SELECT Failed!!\n";
cerr << mysql_stmt_error(stmt) << "\n";
exit(0);
}
cout << "mysql_stmt_prepare() was successful\n";
parm_count = mysql_stmt_param_count(stmt);
cout << "total parameters in SELECT: " << parm_count << endl;
//NEED TO VALIDATE PARAMETERS HERE//
if (parm_count !=2)
{
cerr << " invalid parameter count returned by MySQL" << endl;
exit(0);
}
memset (parm_bind, 0, sizeof(parm_bind));
str_length[1]=strlen("5025267");//lot
str_length[0]=strlen("XTEX6");//facility
strncpy(lot, "5025267",sizeof("5025267"));
strncpy(facility, "XTEX6",sizeof("XTEX6"));
//bind facility
parm_bind[0].buffer_type= MYSQL_TYPE_STRING;
parm_bind[0].buffer= (char*)&facility;
parm_bind[0].buffer_length= 50;
parm_bind[0].is_null= 0;
parm_bind[0].length= &str_length[0];
//bind lot
parm_bind[1].buffer_type = MYSQL_TYPE_STRING;
parm_bind[1].buffer = (char*) &lot;
parm_bind[1].buffer_length = 50;
parm_bind[1].is_null=0;
parm_bind[1].length= &str_length[1]; // Specify the data values for
the parameters.
//strmov(szData, (char *)"venu");
//bind parameter buffers to prepared statement
if (mysql_stmt_bind_param (stmt, parm_bind))
{
cerr << " mysql_stmnt_bind_param() failed" << endl;
cerr << mysql_stmt_error(stmt) << endl;
exit(0);
}
/* Fetch result set meta information */
ps_meta_result = mysql_stmt_result_metadata(stmt);
if (!ps_meta_result)
{
cerr << "ERROR - mysql_stmt_result_metadat() failed!" << endl;
cerr << mysql_stmt_error(stmt) << endl;
exit(0);
}
col_count = mysql_num_fields(ps_meta_result);
cout << "Total number of columns in SELECT statement : " << col_count <<
endl;
if (col_count !=1) //validate column count
{
cerr << " Invalid number of columns returned by MySQL!!" << endl;
exit(0);
}
// Execute the SELECT query
if(mysql_stmt_execute(stmt))
{
cerr << " mysql_stmt_execute() failed!!" << endl;
cerr << mysql_stmt_error(stmt) << endl;
exit(0);
}
memset(res_bind, 0, sizeof(res_bind));
res_bind[0].buffer_type = MYSQL_TYPE_STRING;
res_bind[0].buffer = (char*) &product;
res_bind[0].buffer_length = STRING_SIZE;
res_bind[0].is_null = &is_null[0];
res_bind[0].length = &length;
// bind the results buffers
if (mysql_stmt_bind_result(stmt, res_bind))
{
cerr << "mysql_stmt_bind_result() failed" << endl;
cerr << mysql_stmt_error(stmt) << endl;
exit(0);
}
//buffer the results to the client
if (mysql_stmt_store_result(stmt))
{
cerr << "mysql_stmt_store_result() failed" << endl;
cerr << mysql_stmt_error(stmt) << endl;
exit(0);
}
row_count = mysql_stmt_num_rows(stmt);
//fetch all rows
fetch_row_count=0;
cout << "Fetching results..." << endl;
while(!mysql_stmt_fetch(stmt))
{
fetch_row_count++;
cout << "Row " << fetch_row_count << endl;
//product (aka column1)\
cout << "Product : ";
if (is_null[0])
{
cout << "NULL" << endl;
}
else
{
cout << product << "(" << length << ")" << endl;
}
} // end while fetch
cout << "Total rows SELECTED: " << row_count << endl;
cout << "Total rows fetched: " << fetch_row_count << endl;
if (row_count != fetch_row_count)
{
cerr << "MySQL failed to return all data rows. " << endl;
exit(0);
}
//Free the result metadata
mysql_free_result(ps_meta_result);
//Close prepared statement
if (mysql_stmt_close(stmt))
{
cerr << "ERROR - failed while closing the prepared statement. " << endl;
cerr << mysql_stmt_error(stmt) << endl;
exit(0);
}
return EXIT_SUCCESS;
}
Darrell Cormier <[EMAIL PROTECTED]> wrote:
> Greetings,
> I am trying to figure out prepared statements in the C-API. The problem
> I am having is passing parameters to a prepared statement.
>
> If I hard code everything in my SQL statement, like:
> static char *sql_stmt = {
> "select product "
> "from lth "
> "where facility = \"XTEX6\""
> "and lot = \"5025267\" "
> "and trn = \"LOGI\" "
> };
> everything works fine. However, if I attempt to pass facility and lot
> as parameters:
> static char *sql_stmt = {
> "select product "
> "from lth "
> "where facility = ? "
> "and lot = ? "
> "and trn = \"LOGI\" "
> };
>
> I am unable to get any data back. I have enclosed my source code
> below. Does anyone see where I have made a mistake that would prevent
> this from working properly.
>
> Sorry for the length. I will greatly appreciate any assistance I can get.
>
> Regards,
> Darrell
>
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
