석수코딩교육

mysql> select @r:=@r+1 As rowNum, T2.t_regtime,T2.t_cnt from DUMMY T1, (select substr(regtime,1,17) as t_regtime, count(1) as t_cnt from RB_OCUS_POS_TEST2 group by substr(regtime,1,17)) T2, (Select @r:=0)r;
+--------+-------------------+-------+
| rowNum | t_regtime         | t_cnt |
+--------+-------------------+-------+
|      1 | 20190829 12:42:03 |   669 |
|      2 | 20190829 12:42:04 |  3021 |
|      3 | 20190829 12:42:05 |  5429 |
|      4 | 20190829 12:42:06 |  5005 |
|      5 | 20190829 12:42:07 |  3661 |
|      6 | 20190829 12:42:08 |  3310 |
|      7 | 20190829 12:42:09 |  2807 |
|      8 | 20190829 12:42:10 |  3950 |
|      9 | 20190829 12:42:11 |  4715 |
|     10 | 20190829 12:42:12 |  5130 |
|     11 | 20190829 12:42:13 |  4318 |
|     12 | 20190829 12:42:14 |  2918 |
|     13 | 20190829 12:42:15 |  2980 |
|     14 | 20190829 12:42:16 |  4156 |
|     15 | 20190829 12:42:17 |  5870 |
|     16 | 20190829 12:42:18 |  5236 |
|     17 | 20190829 12:42:19 |  5546 |
|     18 | 20190829 12:42:20 |  5367 |
|     19 | 20190829 12:42:21 |  3181 |
|     20 | 20190829 12:42:22 |  2807 |
|     21 | 20190829 12:42:23 |  2554 |
|     22 | 20190829 12:42:24 |  1379 |
|     23 | 20190829 12:42:25 |  4924 |
|     24 | 20190829 12:42:26 |  4724 |
|     25 | 20190829 12:42:27 |  5641 |
|     26 | 20190829 12:42:28 |  5661 |
|     27 | 20190829 12:42:29 |  3936 |
|     28 | 20190829 12:42:30 |  3557 |
|     29 | 20190829 12:42:31 |  4186 |
|     30 | 20190829 12:42:32 |  4821 |
|     31 | 20190829 12:42:33 |  4621 |
|     32 | 20190829 12:42:34 |  4697 |
|     33 | 20190829 12:42:35 |  3264 |
|     34 | 20190829 12:42:36 |  3706 |
|     35 | 20190829 12:42:37 |  2423 |
|     36 | 20190829 12:42:38 |  2043 |
|     37 | 20190829 12:42:39 |  4207 |
|     38 | 20190829 12:42:40 |  3524 |
|     39 | 20190829 12:42:41 |  4656 |
|     40 | 20190829 12:42:42 |  3582 |
|     41 | 20190829 12:42:43 |  2995 |
|     42 | 20190829 12:42:44 |   120 |
+--------+-------------------+-------+
42 rows in set (0.25 sec)

mysql>

다음과 같은 테이블이 존재한다. (테이블명 : ISS_HH)

위의 SEQ_NO를 1씩 증가시키기 위해서는 보통 아래와 같은 SQL을 생각하기 쉽다.

SQL>UPDATE ISS_HH SET SEQ_NO = SEQ_NO + 1 WHERE USER_ID > 10000

그러나 위를 실행하면, DUPLICATE ERROR가 발생한다. 이유는 멀까? 

PK인 SEQ_NO에 UPDATE시에 데이타가 중복으로 발생하게 된다. 즉 이런 경우는 가장큰 SEQ_NO를 증가시키는것이 일반적이다.

>>>>>>>>>OK QUERY

SQL>UPDATE ISS_HH SET SEQ_NO = SEQ_NO + 1 WHERE USER_ID > 10000 ORDER BY SEQ_NO DESC

-1 을 할경우는 반대로 생각하면 된다.

SQL>UPDATE ISS_HH SET SEQ_NO = SEQ_NO -1 WHERE USER_ID > 10000 ORDER BY SEQ_NO ASC

비고) 한번에 처리하지 않고, 하나씩 처리하는 프로그램으로 작성해보라,ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ

비고) 한번에 처리하지 않고, 하나씩 처리하는 프로그램으로 작성해보라,ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ

비고) 한번에 처리하지 않고, 하나씩 처리하는 프로그램으로 작성해보라,ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ

비고) 한번에 처리하지 않고, 하나씩 처리하는 프로그램으로 작성해보라,ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ

1. RB_OCUS_POS_TEST2테이블에 200,00개의 데이타를 넣는다.

2. 1초에 3,000개 이상의 데이타가 들어가는 횟수를 알고 싶다면?

3. 아래는 위의 문제를 구하기 위해서 구성한 쿼리이다.

mysql> show create table RB_OCUS_POS_TEST2;
+-------------------+-----------------------------------
| Table             | Create

​+-------------------+-----------------------------------
| RB_OCUS_POS_TEST2 | CREATE TABLE `RB_OCUS_POS_TEST2` (
  `seqno` int(11) NOT NULL,
  `account_no` varchar(10) NOT NULL,
  `stock_symbol` varchar(20) NOT NULL,
  `start_volume` varchar(10) DEFAULT NULL,
  `start_amount` varchar(13) DEFAULT NULL,
  `start_price` varchar(13) DEFAULT NULL,
  `stock_type` varchar(2) DEFAULT NULL,
  `trustee_id` varchar(2) DEFAULT NULL,
  `regtime` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`seqno`,`account_no`,`stock_symbol`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------------------+-----------------------------------
1 row in set (0.00 sec)

mysql>

mysql> desc RB_OCUS_POS_TEST2;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| seqno        | int(11)     | NO   | PRI | NULL    |       |
| account_no   | varchar(10) | NO   | PRI | NULL    |       |
| stock_symbol | varchar(20) | NO   | PRI | NULL    |       |
| start_volume | varchar(10) | YES  |     | NULL    |       |
| start_amount | varchar(13) | YES  |     | NULL    |       |
| start_price  | varchar(13) | YES  |     | NULL    |       |
| stock_type   | varchar(2)  | YES  |     | NULL    |       |
| trustee_id   | varchar(2)  | YES  |     | NULL    |       |
| regtime      | varchar(30) | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

mysql>

mysql> select * from RB_OCUS_POS_TEST2 limit 10;
+-------+------------+----------------------+--------------+---------------+---------------+------------+------------+------------------------+
| seqno | account_no | stock_symbol         | start_volume | start_amount  | start_price   | stock_type | trustee_id | regtime                |
+-------+------------+----------------------+--------------+---------------+---------------+------------+------------+------------------------+
|     1 | 0016100    | BBL                  | 0000001500   | 0000266962.22 | 000177.974813 | 02         |            | 20190828 11:19:25(140) |
|     2 | 0016100    | BPP                  | 0000002000   | 0000042000.00 | 000021.000000 | 02         |            | 20190828 11:19:25(141) |
|     3 | 0016100    | CK                   | 0000015375   | 0000296816.51 | 000019.305139 | 02         |            | 20190828 11:19:25(142) |
|     4 | 0016100    | IEC                  | 0012286603   | 0000308909.54 | 000000.025142 | 02         |            | 20190828 11:19:25(142) |
|     5 | 0016100    | IVL                  | 0000011769   | 0000309729.03 | 000026.317362 | 02         |            | 20190828 11:19:25(142) |
|     6 | 0016100    | KTB                  | 0000005000   | 0000103785.51 | 000020.757102 | 02         |            | 20190828 11:19:25(142) |
|     7 | 0055920    | ASAP                 | 0000001000   | 0000003106.87 | 000003.106870 | 02         |            | 20190828 11:19:25(142) |
|     8 | 0055920    | BCP                  | 0000001000   | 0000033574.22 | 000033.574220 | 02         |            | 20190828 11:19:25(142) |
|     9 | 0055920    | CBG                  | 0000000800   | 0000042844.70 | 000053.555875 | 02         |            | 20190828 11:19:25(143) |
|    10 | 0055920    | CHAYO                | 0000002000   | 0000008739.30 | 000004.369650 | 02         |            | 20190828 11:19:25(143) |
+-------+------------+----------------------+--------------+---------------+---------------+------------+------------+------------------------+
10 rows in set (0.00 sec)

mysql>

mysql> select T2.t_cnt from DUMMY T1, (select substr(regtime,1,17) as t_regtime, count(1) as t_cnt from RB_OCUS_POS_TEST2 group by substr(regtime,1,17)) T2 limit 10;
+-------+
| t_cnt |
+-------+
|  2149 |
|  2556 |
|  3462 |
|  2635 |
|  2780 |
|  3051 |
|  2450 |
|  2310 |
|  2695 |
|  2716 |
+-------+
10 rows in set (0.22 sec)

mysql> 
mysql> select T2.t_cnt from DUMMY T1, (select substr(regtime,1,17) as t_regtime, count(1) as t_cnt from RB_OCUS_POS_TEST2 group by substr(regtime,1,17)) T2 where T2.t_cnt > 3000;
+-------+
| t_cnt |
+-------+
|  3462 |
|  3051 |
|  3192 |
|  3761 |
|  3085 |
|  3418 |
|  3341 |
|  3066 |
|  3353 |
|  3287 |
|  3037 |
+-------+
11 rows in set (0.23 sec)

mysql>

mysql> select * from DUMMY;
+-------+------------+----------------------+--------------+---------------+---------------+------------+------------+
| seqno | account_no | stock_symbol         | start_volume | start_amount  | start_price   | stock_type | trustee_id |
+-------+------------+----------------------+--------------+---------------+---------------+------------+------------+
|     1 | 0016100    | BANPU                | 0000045000   | 0000645569.57 | 000014.345990 | 02         |            |
+-------+------------+----------------------+--------------+---------------+---------------+------------+------------+
1 row in set (0.00 sec)

mysql>

mysql> select T2.t_regtime,T2.t_cnt from DUMMY T1, (select substr(regtime,1,17) as t_regtime, count(1) as t_cnt from RB_OCUS_POS_TEST2 group by substr(regtime,1,17)) T2 where T2.t_cnt > 3000;
+-------------------+-------+
| t_regtime         | t_cnt |
+-------------------+-------+
| 20190828 11:19:27 |  3462 |
| 20190828 11:19:30 |  3051 |
| 20190828 11:19:38 |  3192 |
| 20190828 11:19:46 |  3761 |
| 20190828 11:19:47 |  3085 |
| 20190828 11:19:55 |  3418 |
| 20190828 11:20:00 |  3341 |
| 20190828 11:20:03 |  3066 |
| 20190828 11:20:12 |  3353 |
| 20190828 11:20:15 |  3287 |
| 20190828 11:20:20 |  3037 |
+-------------------+-------+
11 rows in set (0.19 sec)

mysql>  

mysql> select T2.t_regtime,T2.t_cnt from DUMMY T1, (select substr(regtime,1,17) as t_regtime, count(1) as t_cnt from RB_OCUS_POS_TEST2 group by substr(regtime,1,17)) T2 where T2.t_cnt > 3000 order by T2.t_cnt desc;
+-------------------+-------+
| t_regtime         | t_cnt |
+-------------------+-------+
| 20190828 11:19:46 |  3761 |
| 20190828 11:19:27 |  3462 |
| 20190828 11:19:55 |  3418 |
| 20190828 11:20:12 |  3353 |
| 20190828 11:20:00 |  3341 |
| 20190828 11:20:15 |  3287 |
| 20190828 11:19:38 |  3192 |
| 20190828 11:19:47 |  3085 |
| 20190828 11:20:03 |  3066 |
| 20190828 11:19:30 |  3051 |
| 20190828 11:20:20 |  3037 |
+-------------------+-------+
11 rows in set (0.00 sec)

mysql>

#####FUNCTION 생성

DROP FUNCTION IF EXISTS GETMASTER_SYMB;

DELIMITER $$
CREATE FUNCTION GETMASTER_SYMB(intput1 TEXT, intput2 TEXT) RETURNS TEXT
BEGIN

    DECLARE p_result    TEXT;
    DECLARE p_input1    TEXT;
    DECLARE p_input2    TEXT;

    SET p_input1 = intput1;
    SET p_input2 = intput2;

    SET p_result = (SELECT  SYMB FROM fssmaster WHERE ( COD2 = p_input1 OR COD1 = p_input2 ))
    ;

    RETURN p_result;
END $$
DELIMITER ;

#####FUNCTION 활용

C:\Users\kiwoom\Downloads>
C:\Users\kiwoom\Downloads>mysql -u root -p
Enter password: ***********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.1.35-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use fss;
Database changed
mysql> SELECT fss.GETMASTER_SYMB('4262', '4263');
+------------------------------------+
| fss.GETMASTER_SYMB('4262', '4263') |
+------------------------------------+
| AAV                                |
+------------------------------------+
1 row in set (0.02 sec)

mysql>
mysql>
mysql>

/*

 * gcc -c sample.c

 * gcc -o sample sample.o libmysql.lib

 */

#include "C:\Program Files\MySQL\MySQL Connector C 6.1\include\mysql.h"
#include <string.h>
#include <stdio.h>

#define DB_HOST "127.0.0.1"
#define DB_USER "root"
#define DB_PASS "eogksalsrnr"
#define DB_NAME "fss"
    
int main(void)
{
    MYSQL *connection=NULL, conn;
    MYSQL_RES *sql_result;
    MYSQL_ROW sql_row;
    int query_stat; 
    
    mysql_init(&conn);

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

    if (connection == NULL)
    {
        fprintf(stderr, "Mysql connection error : %s", mysql_error(&conn));
        return 1;
    }

    query_stat = mysql_query(connection, "SELECT fss.GETMASTER_SYMB('4262', '4263')");
    if (query_stat != 0)
    {
        fprintf(stderr, "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\n", sql_row[0]);
    }

    mysql_free_result(sql_result);

    mysql_close(connection);

​

    return 0;​
}
 

/*

 * 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]
----------------------------------------------------------------------------------------*/ 

#include <stdio.h>

#include <string.h>

#include <stdlib.h>

#include <time.h>

#include <errno.h>

#include <fcntl.h>

#include <signal.h>

#include <setjmp.h>

#include <unistd.h>

#include <ctype.h>

#include <sys/ipc.h>

#include <sys/msg.h>

#include <sys/stat.h>

#include <sys/types.h>

#include <sys/socket.h>

#include <netinet/in.h>

#include <arpa/inet.h>

#include <netdb.h>

#include <sys/shm.h>

#include <mysql.h>

extern MYSQL *STDCALL mysql_init(MYSQL *mysql);

extern MYSQL *STDCALL mysql_real_connect(MYSQL *mysql, const char *host,

   const char *user,

   const char *passwd,

   const char *db,

   unsigned int port,

   const char *unix_socket,

   unsigned long clientflag);

extern const char * STDCALL mysql_error(MYSQL *mysql);

extern int STDCALL mysql_select_db(MYSQL *mysql, const char *db);

extern unsigned int STDCALL mysql_errno(MYSQL *mysql);

extern void STDCALL mysql_close(MYSQL *sock);

extern int STDCALL mysql_query(MYSQL *mysql, const char *q);

#ifdef MYSQL_ERROR

#endif

MYSQL mysql;

char mysql_log_msg[SZ_MAX];

/*- ------------------------------------------------ -*

 *- ConnectDB

 *- ------------------------------------------------ -*/

int ConnectDB(char *database)

{

FILE *fp;

char  conn[20], user[20], passwd[20];

char buff[100], mysql_file[200];

if((fp= fopen("MYSQLDATABASE.dat", "r")) == NULL)

    {

         return(-1);

    }

while(fgets(buff,sizeof(buff),fp))

{

     if(strncmp(buff, "#", 1) == 0 || strncmp(buff, "\n", 1) == 0) continue;

sscanf(buff, "%s %s %s", &conn, &user, &passwd);

}

fclose(fp);

mysql_init(&mysql);

if(!mysql_real_connect(&mysql, conn, user, passwd, database, 0, NULL, 0))

{

        return(-2);

}

    printf("CONNECT DATABASE\n");

if(mysql_select_db(&mysql, database) != 0)

{

     return(-3);

}

return(0);

}

/*- ------------------------------------------------ -*

 *- DATABASE 연결해제 함수

 *- ------------------------------------------------ -*/

DisconnectDB()

{

mysql_close(&mysql);

printf("DISCONNECT DATABASE\n");

}

/*- ------------------------------------------------ -*

 *- MYSQL Table Query function

 *- ------------------------------------------------ -*/

int MysqlQuery(char *query)

{

FILE *fp=NULL;

char status_file[SZ_MAX], server_status[20];

mysql_query(&mysql, "Set names euckr;");

if((fp= fopen("SERVER.dat", "r")) == NULL)

    {

        return(-1);

    }

    while(fgets(server_status,sizeof(server_status),fp))

    {

if(strncmp(server_status, "#", 1) == 0 || strncmp(server_status, "\n", 1) == 0) continue;

}

fclose(fp);

    //[LIVE] is all ok(INSERT,TRUNCATE,UPDATE,REPLACE,DELETE,SELECT etc),

//[STANDBY] is SELECT ok

if( (strncmp(server_status, "LIVE", 4) == 0) || ((strncmp(server_status, "STANDBY", 7) == 0) 

        && ((strncmp(query, "SELECT", 6) == 0) || (strncmp(query, "(SELECT", 7) == 0))))

{

if(mysql_query(&mysql, query) != 0)

        {

            sprintf(mysql_log_msg, "MYSQL ERROR[%s-%d]\n", mysql_error(&mysql), mysql_errno(&mysql));

if(mysql_errno(&mysql) != 1062)

{

#ifdef MYSQL_ERROR

             printf("MYSQL ERROR[%s-%s]\n", query, mysql_log_msg);

#endif

            printf("%s\n", mysql_log_msg);

}

        }

return(mysql_errno(&mysql));

}

return(0);

}

#include <stdio.h>
#include <sys/timeb.h>
#include <stdlib.h>
#include <stdio.h>
#include <sys/types.h>
#include <sys/stat.h>
#include <unistd.h>
#include <time.h>
#include <stdlib.h>
#include <string.h>
#include <signal.h>
#include <sys/socket.h>
#include <netinet/in.h>
#include <netdb.h>
#include <sys/wait.h>
#include <sys/poll.h>
#include <stdbool.h>
#include <fcntl.h>
#include <sys/msg.h>
#include <stdlib.h>
#include <errno.h>

#include "isam.h"
#include "mysql.h"

#define SZ_QUEUETEXT 4096
#define ____MAX_QUEUE_MAX_NUMER 10
#define ____QUEUE_MAX_NUMER 10

int isfd=0, isrc=0, ismd=0;
static char *____get_semiconlon_date_time(void);
static int read_memory_from_file(void);
static int insert_into_rb_order_test(void);

char rb_memory_buf[200000][999];
int rb_memory_cnt=0;

int g_msg_id[____MAX_QUEUE_MAX_NUMER];

int main(void)
{
    int kk, rc, qkey;
    char begin_time[100],end_time[100];
    char queue_number[____MAX_QUEUE_MAX_NUMER][10] =    {"46560","46561","46562","46563","46564",

                        "46565","46566","46567","46568","46569"};

    for(kk=0; kk<____MAX_QUEUE_MAX_NUMER; kk++)
    {
        sscanf(queue_number[kk], "%x", &qkey);
        g_msg_id[kk] = msgget(qkey, IPC_CREAT | 0666 );

        if(g_msg_id[kk] == -1)
        {
            printf("%s queue ipc_creat fail\n", queue_number[kk]);
            return(-1);
        }
    }

    memset(begin_time,0x00,sizeof(begin_time));
    memset(end_time,0x00,sizeof(end_time));

    rc = read_memory_from_file();

    printf("send_msg_to_queue Insert>>[%s]\n", ____get_semiconlon_date_time());
    sprintf(begin_time, "%s", ____get_semiconlon_date_time());

    rc = insert_into_rb_order_test();
    if(rc)
    {
        printf("insert_into_rb_order_test>>error(%d)\n", rc);
        return(-1);
    }
    printf("send_msg_to_queue Insert>>[%s]\n", ____get_semiconlon_date_time());
    printf("send_msg_to_queue Insert>>CNT[%d]\n",rb_memory_cnt);

    sprintf(end_time, "%s", ____get_semiconlon_date_time());

    printf("send_msg_to_queue>>begin[%s],end[%s]\n", begin_time,end_time);

​
    return(0);
}

int read_memory_from_file(void)
{
    char jfile[40];
    char    command[7024];
    char buff[1024], tmpb[32];
    FILE  *jfp=NULL;

    memset(rb_memory_buf,0x00,sizeof(rb_memory_buf));

    sprintf(jfile, "%s/%s", "/sdat/xdat/iFIS/dat", "ocus_pos.dat");

    jfp = fopen(jfile, "r");
    if (jfp == NULL)
    {
        return(-1);
    }

​

    for(;;)
    {
        memset(buff, 0x00, sizeof(buff));
        if(fgets(buff, sizeof(buff), jfp)==NULL) break;

        strcpy(rb_memory_buf[rb_memory_cnt], buff);
        rb_memory_cnt++;
    }
    if(jfp != NULL) fclose(jfp);
    return(0);
}

int insert_into_rb_order_test(void)
{
    int     divide_queue_number;
    int     kk, rc;
    struct ocus_pos_o ocus_pos_o;
    char    command[7024];
    char buff[1024], tmpb[32];

    struct queue_message_buffer {
        long mtype;
        char mtext[SZ_QUEUETEXT];
    };
    struct queue_message_buffer msg_buf;

    kk=0;
    for(;;)
    {
        memcpy(&ocus_pos_o, rb_memory_buf[kk], sizeof(struct ocus_pos_o));
        memset(command,0x00,sizeof(command));

​

       sprintf(command, "%d|"
                        "%.10s|"
                        "%.20s|"
                        "%.10s|"
                        "%.13s|"
                        "%.13s|"
                        "%.2s|"
                        "%.2s|",
                        kk + 1,
                        ocus_pos_o.account_no,
                        ocus_pos_o.stock_symbol,
                        ocus_pos_o.start_volume,
                        ocus_pos_o.start_amount,
                        ocus_pos_o.start_price,
                        ocus_pos_o.stock_type,
                        ocus_pos_o.trustee_id); 

        msg_buf.mtype = 1;
        memcpy(&msg_buf.mtext[0], command, strlen(command));

        divide_queue_number = kk % ____QUEUE_MAX_NUMER;

        if(msgsnd(g_msg_id[divide_queue_number], (struct queue_message_buffer *)&msg_buf, strlen(command), 0) < 0)
        {
            if(errno==EAGAIN) 
            {
                printf(">>msg_push_thread>>[QueueDebug]queue is full, and IPC_NOWAIT was asserted\n");
            }
            else if(errno==EACCES) 
            {
                printf(">>msg_push_thread>>[QueueDebug]permission denied, no write permission\n");
            }
            else if(errno==EFAULT) 
            {
                printf(">>msg_push_thread>>[QueueDebug]msgp address isn't accessable - invalid\n");
            }
            else if(errno==EIDRM) 
            {
                printf(">>msg_push_thread>>[QueueDebug]The message queue has been removed\n");
            }
            else if(errno==EINTR) 
            {
                printf(">>msg_push_thread>>[QueueDebug]Received a signal while waiting to write\n");
            }
            else if(errno==EINVAL) 
            {
                printf(">>msg_push_thread>>[QueueDebug]Invalid message queue identifier, nonpositive message type,

                        or invalid message size\n");
            }
            else if(errno==ENOMEM) 
            {
                printf(">>msg_push_thread>>[QueueDebug]Not enough memory to copy message buffer\n");
            }
        }

        kk++;
        if(kk == rb_memory_cnt) break;

        if(kk/100*100==kk) printf(">>QUEUE[%d]>>TOT[%.7d],NOW[%.7d],DATA[%s]\n", divide_queue_number, rb_memory_cnt,

                           kk, command); 

        usleep(50);
    } 

    printf(">>QUEUE[%d]>>TOT[%.7d],NOW[%.7d],DATA[%s]\n", divide_queue_number, rb_memory_cnt, kk, command); 

    return(0);
}

char *____get_semiconlon_date_time(void)
{
    struct timeb itb;
    struct tm *lt;
    static char dt[100];

    ftime(&itb);

    lt = localtime(&itb.time);

    memset(dt , 0x00 , sizeof(dt));

    sprintf(dt, "%04d%02d%02d %02d:%02d:%02d(%03d)"
             , lt->tm_year + 1900, lt->tm_mon + 1, lt->tm_mday
             , lt->tm_hour, lt->tm_min, lt->tm_sec
             , itb.millitm);
    return dt;
}