Send the code if it is still not working. sujay
-----Original Message----- From: Darrell Cormier [mailto:[EMAIL PROTECTED] Sent: Thursday, August 18, 2005 7:16 PM To: mysql_list Subject: Re: prepared statement problems Sujay Koduri said the following on 08/18/2005 12:31 AM: >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 > > Thank you for your response. Unfortunately this is not the problem. I'm sorry I forgot to change that back when I pasted the code into this email. I originally had it as parm_bind[2] but then was trying to narrow my problem and only work with one parameter in hopes of solving one at a time. I then forgot to change it back to 2 before sending my request for help. There must be something else here that I am missing. Regards, Darrell p.s. I corrected the code below for the mistake you pointed out. <snip> >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[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]=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; >} > > > -- Darrell Cormier <[EMAIL PROTECTED]> Registered Linux user #370711 http://counter.li.org -- 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]
