LINUX,프로그래밍을 통한 PROCEDURE 호출후 결과

/*

 * Mysql 프로그래밍(6) - 프로그래밍을 통한 PROCEDURE 호출후 결과

 * Porcedure의 경우는 보통 일괄처리에 사용되지만, 다중리턴값을 줄때도 사용되어진다.

 * Function과 차이점을 알아야 한다.

 */

​
#include "mysql.h"
#include <string.h>
#include <stdio.h>

 

#define DB_HOST "127.0.0.1"
#define DB_USER "root"
#define DB_PASS "root"
#define DB_NAME "root"
    
/*--------------------------------------------------------------------------------
DELIMITER $$
CREATE PROCEDURE `fss`.`GET_TRADEEVENT3`(IN input1 VARCHAR(20), IN input2 VARCHAR(20))
  READS SQL DATA
BEGIN
    SET @input1=input1;
    SET @input2=input2;

 

    prepare id from
    'SELECT timeofevent, timeoftrade, orderbook, orderqty, price, highprice, lowprice, totalturnover
    FROM fss.tradeevent 
    WHERE ( orderbook = ? OR orderbook = ? )
    ORDER BY orderbook asc, timeofevent asc';

​
    execute id USING @input1,@input2;
  
DEALLOCATE PREPARE id;
END$$
----------------------------------------------------------------------------------*/
int main(void)
{
    MYSQL *connection=NULL, conn;
    MYSQL_RES *sql_result;
    MYSQL_ROW sql_row;
    int query_stat; 
    char argument_rbuf[2048];

    mysql_init(&conn);

    connection = mysql_real_connect(&conn, DB_HOST, DB_USER, DB_PASS, DB_NAME, 3306, (char *)NULL, CLIENT_MULTI_RESULTS);

    if (connection == NULL)
    {
        fprintf(stderr, "Mysql connection error : %s", mysql_error(&conn));
        return 1;
    }
 
    memset(argument_rbuf, 0x00, sizeof(argument_rbuf));
    sprintf(argument_rbuf, "SET @orderbook1 = '%s', @orderbook2 = '%s'", "22658", "16568");

    query_stat = mysql_query(connection, argument_rbuf);
    if (query_stat != 0)
    {
        fprintf(stderr, "@@@@[1010]Mysql query error : %s", mysql_error(&conn));
        return 1;
    }
    query_stat = mysql_query(connection, "CALL GET_TRADEEVENT3(@orderbook1,@orderbook2)");
    if (query_stat != 0)
    {
        fprintf(stderr, "@@@@[2020]Mysql query error : %s", mysql_error(&conn));
        return 1;
    }
    
    sql_result = mysql_store_result(connection);
    
    while ( (sql_row = mysql_fetch_row(sql_result)) != NULL )
    {
        printf(">>>[%s],[%s],[%s],[%s],[%s],[%s],[%s]\n", sql_row[1],sql_row[2],sql_row[3],sql_row[4],sql_row[5],sql_row[6],sql_row[7]);
    }

    mysql_free_result(sql_result);

    mysql_close(connection);
 
    return 0;
}

/*RESULT----------------------------------------------------------------------------------------
>>>[2017-04-03T10:53:06.661],[16568],[50000],[1040],[1040],[1040],[52000000]
>>>[2017-04-03T15:52:31.685],[16568],[4900],[1040],[1040],[1040],[57096000]
>>>[2017-04-03T15:52:45.007],[16568],[100],[1110],[1110],[1040],[57207000]
>>>[2017-04-03T15:53:07.334],[16568],[500],[1110],[1110],[1040],[57762000]
>>>[2017-04-03T15:53:13.430],[16568],[700],[1110],[1110],[1040],[58539000]
>>>[2017-04-03T15:53:16.995],[16568],[800],[1110],[1110],[1040],[59427000]
>>>[2017-04-03T15:54:44.687],[16568],[6800],[1040],[1110],[1040],[66499000]
>>>[2017-04-03T15:54:44.687],[16568],[3700],[1040],[1110],[1040],[70347000]
>>>[2017-04-03T15:54:57.614],[16568],[500],[1040],[1110],[1040],[70867000]
>>>[2017-04-03T15:57:17.670],[16568],[900],[1110],[1110],[1040],[71866000]
>>>[2017-04-03T15:57:17.858],[16568],[400],[1110],[1110],[1040],[72310000]
>>>[2017-04-03T15:57:17.858],[16568],[500],[1130],[1130],[1040],[72875000]
>>>[2017-04-03T15:57:18.063],[16568],[800],[1130],[1130],[1040],[73779000]
>>>[2017-04-03T15:57:18.063],[16568],[100],[1130],[1130],[1040],[73892000]
>>>[2017-04-03T15:57:18.583],[16568],[900],[1130],[1130],[1040],[74909000]
>>>[2017-04-03T15:57:21.882],[16568],[900],[1130],[1130],[1040],[75926000]
>>>[2017-04-03T15:57:41.366],[16568],[200],[1130],[1130],[1040],[76152000]
>>>[2017-04-03T15:57:41.366],[16568],[300],[1130],[1130],[1040],[76491000]
>>>[2017-04-03T15:57:41.533],[16568],[500],[1130],[1130],[1040],[77056000]
>>>[2017-04-03T15:57:41.721],[16568],[500],[1130],[1130],[1040],[77621000]
>>>[2017-04-03T15:57:41.912],[16568],[500],[1130],[1130],[1040],[78186000]
>>>[2017-04-03T15:57:42.996],[16568],[300],[1130],[1130],[1040],[78525000]
>>>[2017-04-03T15:57:42.996],[16568],[200],[1130],[1130],[1040],[78751000]
>>>[2017-04-03T15:57:43.904],[16568],[500],[1130],[1130],[1040],[79316000]
>>>[2017-04-03T15:57:47.634],[16568],[500],[1130],[1130],[1040],[79881000]
>>>[2017-04-03T15:59:57.163],[16568],[300],[1140],[1140],[1040],[81466000]
>>>[2017-04-03T15:59:57.163],[16568],[900],[1130],[1130],[1040],[81124000]
>>>[2017-04-03T15:59:57.163],[16568],[200],[1130],[1130],[1040],[80107000]
>>>[2017-04-03T16:00:00.440],[16568],[1400],[1140],[1140],[1040],[83062000]
>>>[2017-04-03T16:00:02.880],[16568],[1200],[1150],[1150],[1040],[84670000]
>>>[2017-04-03T16:00:02.880],[16568],[200],[1140],[1140],[1040],[83290000]
>>>[2017-04-03T16:00:05.983],[16568],[1100],[1150],[1150],[1040],[85935000]
>>>[2017-04-03T16:00:05.983],[16568],[300],[1150],[1150],[1040],[86280000]
>>>[2017-04-03T16:00:08.448],[16568],[1400],[1150],[1150],[1040],[87890000]
>>>[2017-04-03T16:02:31.378],[16568],[4500],[1040],[1150],[1040],[92570000]
>>>[2017-04-03T16:02:31.378],[16568],[2000],[1040],[1150],[1040],[94650000]
>>>[2017-04-03T16:02:31.378],[16568],[500],[1040],[1150],[1040],[95170000]
>>>[2017-04-03T16:02:55.979],[16568],[1500],[1150],[1150],[1040],[96895000]
>>>[2017-04-03T16:02:56.161],[16568],[100],[1160],[1160],[1040],[98621000]
>>>[2017-04-03T16:02:56.161],[16568],[1400],[1150],[1150],[1040],[98505000]
>>>[2017-04-03T16:03:51.826],[16568],[200],[1160],[1160],[1040],[98853000]
>>>[2017-04-03T16:03:57.872],[16568],[800],[1160],[1160],[1040],[99781000]
>>>[2017-04-03T16:04:04.512],[16568],[1200],[1160],[1160],[1040],[101173000]
>>>[2017-04-03T16:05:24.370],[16568],[500],[1160],[1160],[1040],[101753000]
>>>[2017-04-03T16:07:47.740],[16568],[500],[1160],[1160],[1040],[102333000]
>>>[2017-04-03T16:16:20.053],[16568],[500],[1160],[1160],[1040],[102913000]
>>>[2017-04-03T09:59:14.791],[22658],[60000],[57],[57],[57],[3420000]
>>>[2017-04-03T09:59:15.161],[22658],[20000],[57],[57],[57],[4560000]
>>>[2017-04-03T09:59:25.398],[22658],[40000],[57],[57],[57],[6840000]
>>>[2017-04-03T09:59:25.512],[22658],[20000],[57],[57],[57],[7980000]
>>>[2017-04-03T09:59:35.820],[22658],[40000],[57],[57],[57],[10260000]
>>>[2017-04-03T10:17:29.628],[22658],[40000],[56],[57],[56],[12500000]
>>>[2017-04-03T10:17:29.628],[22658],[20000],[57],[57],[56],[13640000]
>>>[2017-04-03T10:17:29.922],[22658],[20000],[57],[57],[56],[14780000]
>>>[2017-04-03T10:17:40.268],[22658],[40000],[57],[57],[56],[17060000]
>>>[2017-04-03T10:17:40.381],[22658],[20000],[57],[57],[56],[18200000]
>>>[2017-04-03T10:17:51.893],[22658],[30000],[57],[57],[56],[19910000]
>>>[2017-04-03T10:18:08.321],[22658],[40000],[56],[57],[56],[22150000]
>>>[2017-04-03T10:18:18.549],[22658],[40000],[56],[57],[56],[24390000]
>>>[2017-04-03T10:18:37.490],[22658],[40000],[56],[57],[56],[26630000]
>>>[2017-04-03T10:18:41.700],[22658],[40000],[56],[57],[56],[28870000]
>>>[2017-04-03T10:18:45.898],[22658],[10000],[56],[57],[56],[29430000]
>>>[2017-04-03T10:19:10.753],[22658],[40000],[56],[57],[56],[31670000]
>>>[2017-04-03T10:22:22.731],[22658],[40000],[56],[57],[56],[33910000]
>>>[2017-04-03T10:23:03.844],[22658],[40000],[53],[57],[53],[36030000]
>>>[2017-04-03T10:23:03.844],[22658],[20000],[52],[57],[52],[37070000]
>>>[2017-04-03T14:26:23.652],[22658],[10000],[59],[59],[52],[37660000]
>>>[2017-04-03T14:28:03.314],[22658],[10000],[44],[59],[44],[38100000]
>>>[2017-04-03T14:28:42.773],[22658],[200000],[44],[59],[44],[46900000]
>>>[2017-04-03T14:33:33.473],[22658],[40000],[45],[59],[44],[48700000]
>>>[2017-04-03T14:33:38.545],[22658],[40000],[45],[59],[44],[50500000]
>>>[2017-04-03T14:33:42.768],[22658],[40000],[45],[59],[44],[52300000]
>>>[2017-04-03T14:33:46.983],[22658],[40000],[45],[59],[44],[54100000]
>>>[2017-04-03T14:33:51.194],[22658],[20000],[45],[59],[44],[55000000]
>>>[2017-04-03T14:38:35.640],[22658],[40000],[45],[59],[44],[56800000]
>>>[2017-04-03T14:38:50.929],[22658],[35000],[44],[59],[44],[58340000]
----------------------------------------------------------------------------------------*/