Hi, The problem here is that you have two input bind variables, but you declaring MysQL BIND array as parm_bind[1], which can hold only one input bind variable. Make it parm_bind[2]. That should work.
sujay -----Original Message----- From: Darrell Cormier [mailto:[EMAIL PROTECTED] Sent: Thursday, August 18, 2005 1:23 AM To: mysql_list Subject: prepared statement problems 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
