//RUN PSCorruption.sql from cl client
//built using:
//       gcc bug22297.c -o bug22297 -L/home/Tonci/bkwork/copyto/mysql-5-0/lib/mysql -I/home/Tonci/bkwork/copyto/mysql-5-0/include/mysql  -lmysqlclient_r -lz
//run using:
//       ./bug22297

//LD_LIBRARY_PATH=/home/Tonci/bkwork/copyto/mysql-5-0/lib/mysql ./bug22297

//SQL script
/*
DROP PROCEDURE IF EXISTS testBug22297;
drop table if exists tblTestBug2297_1;
drop table if exists tblTestBug2297_2;
create table tblTestBug2297_1(
id varchar(20) NOT NULL default '',
Income double(19,2) default NULL);	
create table tblTestBug2297_2(
id varchar(20) NOT NULL default '', 
CreatedOn datetime default NULL);
DELIMITER $$
CREATE PROCEDURE testBug22297(pcaseid INT)
BEGIN
  SET @sql = "DROP TEMPORARY TABLE IF EXISTS tmpOrders";
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
  SET @sql = "CREATE TEMPORARY TABLE tmpOrders SELECT id, 100 AS Income FROM tblTestBug2297_1 GROUP BY id";
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
  SELECT id, Income FROM (SELECT e.id AS id ,COALESCE(prof.Income,0) AS Income FROM tblTestBug2297_2 e LEFT JOIN tmpOrders prof ON e.id = prof.id WHERE e.CreatedOn > '2006-08-01') AS Final; 
END $$
DELIMITER ;
INSERT INTO tblTestBug2297_1 (`id`,`Income`) VALUES ('a',4094.00),('b',500.00),('c',3462.17),('d',500.00),('e',600.00);
INSERT INTO tblTestBug2297_2 (`id`,`CreatedOn`) VALUES ('d','2006-08-31 00:00:00'),('e','2006-08-31 00:00:00'),('b','2006-08-31 00:00:00'),('c','2006-08-31 00:00:00'),('a','2006-08-31 00:00:00');
*/

//RESULT
//mysql> call testBug22297(100);
//Field   1:  `id`
//Catalog:    `def`
//Database:   `test`
//Table:      `e`
//Org_table:  `tblTestBug2297_2`
//Type:       VAR_STRING
//Collation:  latin1_swedish_ci (8)
//Length:     20
//Max_length: 1
//Decimals:   0
//Flags:      NOT_NULL
//
//Field   2:  `Income`
//Catalog:    `def`
//Database:   ``
//Table:      `Final`
//Org_table:  ``
//Type:       LONG
//Collation:  binary (63)
//Length:     11
//Max_length: 3
//Decimals:   0
//Flags:      NUM
//
//+----+--------+
//| id | Income |
//+----+--------+
//| d  |    100 |
//| e  |    100 |
//| b  |    100 |
//| c  |    100 |
//| a  |    100 |
//| d  |    100 |
//| e  |    100 |
//| b  |    100 |
//| c  |    100 |
//| a  |    100 |
//| d  |    100 |
//| e  |    100 |
//| b  |    100 |
//| c  |    100 |
//| a  |    100 |
//+----+--------+
//15 rows in set (0.00 sec)
//
//From the test case
//Connected!
//
//Running {call sp_test(?)}
//
// total parameters in SELECT: 1
//
//Exec prep stmt
// total columns in statement: 2
//
//Init result buffers before fetch
//
//Bind result to buffer before fetch
//
//Buffer result
//Fetching results ...
//  row 1
//   column1 (varchar)  :  NULL(0)
//   column2 (long)   :  8.88742e-314(0)
//
//  row 2
//   column1 (varchar)  :  NULL(0)
//   column2 (long)   :  8.88742e-314(0)
//
// total rows fetched: 2
// MySQL failed to return all rows


#include <stdio.h>
#include <stdlib.h>
#include <stdarg.h>
#include <sys/resource.h>
#include <pthread.h>
#include <unistd.h>
#include <semaphore.h>
//#include "mysql.h"
#include <my_global.h>
#include <my_sys.h>
#include <mysql.h>
#include <errmsg.h>
#include <my_getopt.h>
#include <m_string.h>

#define check_execute(stmt, r) \
{ \
if (r) \
  mysterror(stmt, NULL); \
DIE_UNLESS(r == 0);\
}

/*
  This is to be what mysql_query() is for mysql_real_query(), for
  mysql_simple_prepare(): a variant without the 'length' parameter.
*/

MYSQL_STMT *STDCALL
mysql_simple_prepare(MYSQL  *mysql, const char *query)
{
  MYSQL_STMT *stmt= mysql_stmt_init(mysql);
  if (stmt && mysql_stmt_prepare(stmt, query, strlen(query)))
  {
    mysql_stmt_close(stmt);
    return 0;
  }
  return stmt;
}

int main(int argc, const char *argv[])
{
  MYSQL *mysql;
  MYSQL_RES *r=NULL;

  MYSQL_STMT *stmt;
  int        rc, i;
  int        int_data, param_count, column_count;
  char	     vc_data1[20], vc_data2[20]; //FOR FETCHING AS String
  char query[50];
  long      flt_data2;//double resclo2;
  MYSQL_BIND bindin[1], bindout[2];
  MYSQL_ROW w;
  my_bool       errorin[1], error[2];	
  unsigned long lengthin[1], length[2];
  my_bool       is_nullin[1], is_null[2];	
  MYSQL_RES     *prepare_meta_result;	


	mysql = mysql_init(NULL);
	if (!mysql_real_connect(mysql,"localhost","root","","test",3307, NULL, CLIENT_FOUND_ROWS|CLIENT_MULTI_STATEMENTS))
	{
		printf("mysql_real_connect failed: %s\n",mysql_error(mysql));
		exit(-1);
	}
	
	printf("Connected!\n");
		
    stmt= mysql_stmt_init(mysql);
	if (!stmt)
	{
		printf("\nmysql_stmt_init(), out of memory\n");
  		exit(-1);
	}

	printf("\nRunning %s\n\n","{call sp_test(?)}");

	strmov(query, "call testBug22297(?)");
	stmt= mysql_simple_prepare(mysql, query);
	//mysql_stmt_prepare(stmt, query, strlen(query)); //DOESN'T WORK EITHER


	param_count= mysql_stmt_param_count(stmt);
	fprintf(stdout, " total parameters in SELECT: %d\n", param_count);

	if (param_count != 1) /* validate parameter count */
	{
	  fprintf(stderr, " invalid parameter count returned by MySQL\n");
	  exit(0);
	}

	bzero((char*) bindin, sizeof(bindin));
	memset(bindin, 0, sizeof(bindin));
	
	bzero((char*) bindout, sizeof(bindout));
	memset(bindout, 0, sizeof(bindout));

	int_data= 320;

	/* integer */
	bindin[0].buffer_type= MYSQL_TYPE_LONG;
	bindin[0].buffer= (void *)&int_data;//char

	bindin[0].length= &lengthin[0];
	bindin[0].is_null= &is_nullin[0];
	is_null[0]= 0;

	rc= mysql_stmt_bind_param(stmt, bindin);

	printf("\nExec prep stmt\n");
	if (mysql_stmt_execute(stmt))
	{
  		printf("mysql_stmt_execute(), failed\n");
  		printf(" %s\n", mysql_stmt_error(stmt));
  		exit(-1);
	}

	
	prepare_meta_result = mysql_stmt_result_metadata(stmt);
	if (!prepare_meta_result)
	{
  		printf("\nmysql_stmt_result_metadata(), returned no meta information\n");
  		fprintf("%s\n", mysql_stmt_error(stmt));
  		exit(-1);
	}

	/* Get total columns in the query */
	column_count= mysql_num_fields(prepare_meta_result);
	fprintf(stdout, " total columns in statement: %d\n", column_count);

	if (column_count != 2) /* validate column count */
	{
	  fprintf(stderr, " invalid column count returned by MySQL\n");
	  exit(0);
	}

	printf("\nInit result buffers before fetch\n");

	//BIND COLS!!!!
	bindout[0].buffer_type= MYSQL_TYPE_VAR_STRING;
	bindout[0].buffer= (char *)&vc_data1;
	bindout[0].is_null= &is_null[0];
	bindout[0].length= &length[0];
	bindout[0].error= &error[0];

	bindout[1].buffer_type= MYSQL_TYPE_LONG;
	bindout[1].buffer= (char *)&flt_data2;
	bindout[1].is_null= &is_null[1];
	bindout[1].length= &length[1];
	bindout[1].error= &error[1];

	printf("\nBind result to buffer before fetch\n");
	if (mysql_stmt_bind_result(stmt, bindout))
	{
  		printf(" mysql_stmt_bind_result() failed\n");
  		printf(" %s\n", mysql_stmt_error(stmt));
  		exit(-1);
	}

	printf("\nBuffer result\n");
	if (mysql_stmt_store_result(stmt))
	{
  		printf(" mysql_stmt_store_result() failed\n");
  		printf(" %s\n", mysql_stmt_error(stmt));
  		exit(-1);
	}


	/* Fetch all rows */
	i= 0;
	fprintf(stdout, "Fetching results ...\n");
	while (!mysql_stmt_fetch(stmt))
	{
	  i++;
	  fprintf(stdout, "  row %d\n", i);

	  /* column 1 */
	  fprintf(stdout, "   column1 (varchar)  : ");
	  if (is_null[0])
		fprintf(stdout, " NULL(%ld)\n", length[0]);
	  else
		fprintf(stdout, " %s(%ld)\n", vc_data1, length[0]);

	  /* column 2 */
	  fprintf(stdout, "   column2 (long)   : ");
	  if (is_null[1])
		fprintf(stdout, " NULL\n");
	  else
		fprintf(stdout, " %g(%ld)\n", flt_data2, length[1]);//%s(%ld)

	  fprintf(stdout, "\n");
	}

	/* Validate rows fetched */
	fprintf(stdout, " total rows fetched: %d\n", i);
	if (i != 15)
	{
	  fprintf(stderr, " MySQL failed to return all rows\n");
	  exit(0);
	}

	/* Free the prepared result metadata */
	mysql_free_result(prepare_meta_result);


	printf("\nCLOSE\n");
	/* Close the statement */
	if (mysql_stmt_close(stmt))
	{
	  fprintf(stderr, " failed while closing the statement\n");
	  fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
	  exit(0);
	}


	exit(EXIT_SUCCESS);

}