RE: stored procedure

[Jim Wallace]

This is a sample of how I do it to pass in 6 parameters and get out 3.


Query query = m_conn->query();

query << "CALL apply_transaction_and_details_to_user_balance( %0, %1,
%2, %3, %4, %5, @rc, @newBalance, @tranId ); SELECT @rc, @newBalance,
@tranId;";

// set input parameter values
query.def[0U] = serverId;
query.def[1U] = player1AmtChanging;
query.def[2U] = transactionType;
query.def[3U] = currencyType;
query.def[4U] = glid;
query.def[5U] = qty;

query.parse();

Result res = query.store();



-----Original Message-----
From: kunal kabra [mailto:kunalkkabra@stripped] 
Sent: Friday, November 21, 2008 1:12 AM
To: plusplus@stripped
Subject: stored procedure

Hi

I have used following 2 ways to call the stored procedure in c++

1]
           mysqlpp::Query query=conn->query();

           query<<"CALL
GetVoucherDetails1(@pi_voucher_pin,@pi_user_id,@pi_recharge_interface_na
me)";


           //query<<"CALL
GetVoucherDetails1('2541983','1000','VCall')";

           mysqlpp::StoreQueryResult res = query.store();

          if(res)
          {
                     mysqlpp::Row row;
                     for (size_t j = 0; j < res.num_rows(); ++j)
                      {
                                 row=res[j];
                                 cout<<"batch_no = "<<row[0]<<endl;
 
cout<<"voucher_serial_no="<<row[1]<<endl;
                                   cout<<"denomination= "<<row[7]<<endl;
                                   cout<<"reason code= "<<row[9]<<endl;
                       }
          }

           In this, when I am using constant  parameters then procedure
is
executing properly.
           But when passing the values through variables then it is
showing
that parameter is null.

2]

           #define STRING_SIZE 50;

MYSQL_STMT    *stmt= mysql_stmt_init(conn);

           const char *pi_voucher_pin="2541983";

           const char *pi_user_id="1000";

           const char *pi_recharge_interface_name="VCall";

           int po_batch_no;

           int po_voucher_serial_no;

           int po_denomination;

           int po_reason_code;

           /* send SQL query */

           const char *auth_proc= "call
GetVoucherDetails(@pi_voucher_pin,@
pi_user_id,@
pi_recharge_interface_name,@po_batch_no,@po_voucher_serial_no,@po_denomi
nation,@po_reason_code)";

                       mysql_stmt_prepare(stmt,
auth_proc,strlen(auth_proc));

                      MYSQL_BIND bindings[3];
                       memset(bindings, 0, sizeof(MYSQL_BIND));

                       bindings[0].buffer_type = MYSQL_TYPE_STRING;
                       bindings[0].buffer_length = STRING_SIZE;
                       bindings[0].buffer = (char *)pi_voucher_pin;
                       bindings[0].is_null= 0;

                       bindings[1].buffer_type = MYSQL_TYPE_STRING;
                       bindings[1].buffer_length = STRING_SIZE;
                       bindings[1].buffer = (void *)pi_user_id;
                       bindings[1].is_null= 0;

                       bindings[2].buffer_type = MYSQL_TYPE_STRING;
                       bindings[2].buffer_length = STRING_SIZE;
                       bindings[2].buffer =
(void*)pi_recharge_interface_name;
                       bindings[2].is_null= 0;

                       bindings[3].buffer_type = MYSQL_TYPE_LONG;
                       bindings[3].buffer_length = sizeof(int);
                        bindings[3].buffer = (char*)&po_batch_no;
                       bindings[3].is_null=0;
                       bindings[3].length=0;

                       bindings[4].buffer_type = MYSQL_TYPE_LONG;
                       bindings[4].buffer_length = sizeof(int);
                        bindings[4].buffer =
(char*)&po_voucher_serial_no;
                       bindings[4].is_null=0;
                       bindings[4].length=0;

                       bindings[5].buffer_type = MYSQL_TYPE_LONG;
                        bindings[5].buffer_length = sizeof(int);
                       bindings[5.buffer = (char*)&po_denomination;
                       bindings[5].is_null=0;
                       bindings[5].length=0;

                       bindings[6].buffer_type = MYSQL_TYPE_LONG;
                       bindings[6].buffer_length = sizeof(int);
                       bindings[6].buffer = (char*)&po_reason_code;
                       bindings[6].is_null=0;
                       bindings[6].length=0;

                       int param_count= mysql_stmt_param_count(stmt);
                       mysql_stmt_bind_param(stmt, bindings);
                       pi_voucher_pin="2541983";
                       pi_user_id="1000";
                       pi_recharge_interface_name="VCall";

                      if(mysql_stmt_execute(stmt) == 0)
///////core  dumped
                            printf("Statement Executed successfully\n");
                       else
 
cout<<"error"<<mysql_stmt_error(stmt);

                      int affected_rows= mysql_stmt_affected_rows(stmt);

                       char *state = "SELECT
@po_batch_no,@po_voucher_serial_no,@po_voucher_status,@po_denomination,@
po_reason_code";
                       int sta
=mysql_real_query(conn,state,strlen(state));
                       res = mysql_store_result(conn);
                       if (res)
                       {
                                 printf("Process Result\n");
                                int num=mysql_num_rows(res);
                                  if((row = mysql_fetch_row(res)) !=
NULL)
                                   {
                                             printf("Result out
parameter%d
\n", row[0]);
                                               mysql_free_result(res);
                                   }
                       }
                      mysql_close(conn);

this is showing a core dump

please help me about this.

Thanking You
Kunal