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
--
Darrell Cormier <[EMAIL PROTECTED]>
Registered Linux user #370711 http://counter.li.org
// The following is my code//
///////////////////////////////////////
#ifdef HAVE_CONFIG_H
#include <config.h>
#endif
#include <iostream>
#include <cstdlib>
#include "msql_conn_env.h"
#define STRING_SIZE 50
MYSQL_STMT *stmt;
MYSQL_BIND parm_bind[1], res_bind[1];
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[])
{
char product[35], facility[6], lot[12];
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,my_host_name,my_user_name,my_pwd,my_db_name,
my_port_number, my_socket_name, my_flags) == NULL)
//MySQL connection --> NULL = Failure; a successful connection would
return first variable's value (i.e. MySQL connection handle).
{
cerr << "No connection to the data base server (LTS
DB).\n"; // no connection to database server
}
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[0]=6;
str_length[1]=12;
strncpy(lot, "5028368",12);
strncpy(facility, "XTEX6",6);
//bind facility
parm_bind[0].buffer_type= MYSQL_TYPE_STRING;
parm_bind[0].buffer= (char*)&facility;
parm_bind[0].buffer_length= 6;
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 = 12;
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;
}
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
