hi, you should use strlen() instead of sizeof() to pass the length of input bind parameter, otherwise it will read past the actual parameter and tries to compare with the whole thing which is not intended. str_length[0]= sizeof(facility); str_length[1]=sizeof(lot); is_null[0] is not initialized to either '0' or '1', which actually should be. STRING_SIZE should be max length which product can hold. should be 35. i guess these changes would help you. sujay
_____ From: Darrell Cormier [mailto:[EMAIL PROTECTED] Sent: Thursday, August 18, 2005 7:33 PM To: mysql_list Cc: Sujay Koduri Subject: Re: prepared statement problems Sujay Koduri said the following on 08/18/2005 08:48 AM: Send the code if it is still not working. sujay Not sure what you mean by send the code since it is included at the end of this email. However, I have also attached the file. Since I am not sure if the list will accept files, I have also copied you on this message. Please forgive me if this is not what you intended. Kindest regards, Darrell Cormier #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[2], 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]= sizeof(facility); str_length[1]=sizeof(lot); strncpy(lot, "5028368",12); strncpy(facility, "XTEX6",6); //bind facility parm_bind[0].buffer_type= MYSQL_TYPE_STRING; parm_bind[0].buffer= (void*)&facility; parm_bind[0].buffer_length= sizeof(facility); parm_bind[0].is_null = &is_null[0]; parm_bind[0].length= &str_length[0]; //bind lot parm_bind[1].buffer_type = MYSQL_TYPE_STRING; parm_bind[1].buffer = (void*) &lot; parm_bind[1].buffer_length = sizeof(lot); parm_bind[1].is_null=&is_null[1]; 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)!=0) { 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; }
