본문 바로가기
C | C++ | VC++

How to Convert MYSQL to SQLite

by 두루물 2025. 6. 12.

SELECT shtcode,UNIX_TIMESTAMP() - UNIX_TIMESTAMP(update_ts) AS diffsecs,sdate,
    hname,upname,marketid, marketgb,capital, sigatotal, listing, abscnt,etf, 
    category, bu12gubun, spac_gubun, per, pbrx, t_per, epsrt, bfeps, 
    bfoperatingincome, bfoperatingincome2, high52w, low52w, 
    high52wdate, low52wdate, listdate, uplmtprice, dnlmtprice, openprice, high, low, closeprice, 
    dsign, dchange, drate, jnilvolume, volume, `locks`, manage, stops, warn, care, shterm_text, lend_text, investatt, 
    fprevolumerate, fprevolumelistrate, `insert_id`, update_id, 
    DATE_FORMAT(update_ts, '%Y%m%d') update_dt
FROM `homidb`.`t_stockinfo`
ORDER BY shtcode ASC;

 

SELECT shtcode,STRFTIME("%s", "now") - STRFTIME('%Y%m%d',update_ts) AS diffsecs,sdate,
    hname,upname,marketid, marketgb,capital, sigatotal, listing, abscnt,etf, 
    category, bu12gubun, spac_gubun, per, pbrx, t_per, epsrt, bfeps, 
    bfoperatingincome, bfoperatingincome2, high52w, low52w, 
    high52wdate, low52wdate, listdate, uplmtprice, dnlmtprice, openprice, high, low, closeprice, 
    dsign, dchange, drate, jnilvolume, volume, `locks`, manage, stops, warn, care, shterm_text, lend_text, investatt, 
    fprevolumerate, fprevolumelistrate, `insert_id`, update_id, 
    STRFTIME('%Y%m%d',update_ts) update_dt
FROM t_stockinfo
ORDER BY shtcode ASC;

 

int BizCommDB::SaveMasterInfo()
{
    int rc = 0;
    int rows = 0;
    int qlen = 0;
    const char* szerror = 0;
    char* szquery = NULL;
    char* szvalue = NULL;
    char* szvalue2 = NULL;
    LONG64 dwSaveBegTick = GetTickCount64();
    string strlog;

    CMySQLDB* pDB = CDBPool::GetLiveDB();
    if (pDB && pDB->IsConnected() && CDBPool::GetConnected()) {
        MYSTOCK_LIST* pStockList = &g_datacenter.m_stocklist;
        MYSTOCK_LIST::HASHMAP* pMap = pStockList->GetHashMap();
        MYSTOCK_LIST::HASHMAP::iterator it;
        HMSiseInfo* pSItem;
        HMStockInfo* psm;
        int doit = 0;
        char szhname[86] = { 0, };
        char szupname[86] = { 0, };
        char szmarketgb[20] = { 0, };
        char szlocks[40] = { 0, };
        char szmanage[40] = { 0, };
        char szstop[40] = { 0, };
        char szwarn[40] = { 0, };
        char szcare[40] = { 0, };
        char shterm_text[40] = { 0, };
        char lend_text[40] = { 0, };
        char szupdate_dt[12 + 1];//갱신시간 yyyymmddhhmm
        int vlen = 0, vlen1 = 0;
        int vlen2 = 0, vlen3 = 0;
        int count = pMap->size();

        sprintf(szupdate_dt, "%08d%02d", g_conf.m_bizday,16);
        szquery = (char*)malloc(10000);
        if (!szquery) goto exit_save;
        szquery[0] = NULL;
        szvalue = (char*)malloc(6096);
        if (!szvalue) goto exit_save;
        szvalue[0] = NULL;

        szvalue2 = (char*)malloc(6096);
        if (!szvalue2) goto exit_save;
        szvalue2[0] = NULL;

        qlen = sprintf(szquery, //당일시간보다 이전이면 삭제
            "DELETE FROM homidb.t_stockinfo "
            "WHERE update_ts < FROM_UNIXTIME(UNIX_TIMESTAMP(STR_TO_DATE(\'%s\', '%%Y%%m%%d%%H')));", szupdate_dt);
        rows = pDB->ExecuteQuery(szquery);
        pDB->Commit();
#if 1
        SQLiteCpp sqlite;
        bool rc2;
        int qlen;
        //char szquery[2048];
        //char szupdate_dt[12 + 1];//갱신시간 yyyymmddhhmm
        string strquery;
        CHAR szdatafile[MAX_PATH] = { 0, };
        sprintf(szdatafile, "%s\\data\\Homi.db", g_conf.m_commondir);
        strlog = (string)szdatafile;
        rc2 = sqlite.OpenDB(szdatafile);
        //sprintf(szupdate_dt, "%08d%02d", g_conf.m_bizday, 16);
        qlen = sprintf(szquery, //당일시간보다 이전이면 삭제
            "DELETE FROM t_stockinfo "
            "WHERE STRFTIME('%%Y%%m%%d',update_ts) < \'%s\';\0", szupdate_dt);
        rc2 = sqlite.queryexec(szquery);
#endif   

        pStockList->Lock();
        for (it = pMap->begin(); it != pMap->end(); it++)
        {
            pSItem = &it->second;
            psm = pSItem->psm;
            //모두저장 20231019
            doit = 1;
#if 0
            wstring strUni = CA2W(psm->hname);
            string strUTF8 = CW2A(strUni.c_str(), CP_UTF8).m_psz;
#endif
            //빠른디버깅을 위해 종목마스터 선별 통과한 것만(시세오류있어도)
            if ((psm->noerror == 0 || psm->noerror > (int)ENUM_SISEERROR::TRASHSTOCK)
                && psm->si.abscnt > 0) {
                doit = 1;
            }
            if (doit == 0)
               continue;
            //////////////////////////////////////////////////////////////////////////
            MBS2UTF8(psm->hname, szhname, sizeof(szhname) - 1);
            MBS2UTF8(psm->upname, szupname, sizeof(szupname) - 1);
            MBS2UTF8(psm->marketgb, szmarketgb, sizeof(szmarketgb) - 1);
            MBS2UTF8(psm->si.lock, szlocks, sizeof(szlocks) - 1);
            MBS2UTF8(psm->si.manage, szmanage, sizeof(szmanage) - 1);
            MBS2UTF8(psm->si.stop, szstop, sizeof(szstop) - 1);
            MBS2UTF8(psm->si.warn, szwarn, sizeof(szwarn) - 1);
            MBS2UTF8(psm->si.care, szcare, sizeof(szcare) - 1);
            MBS2UTF8(psm->si.shterm_text, shterm_text, sizeof(shterm_text) - 1);
            MBS2UTF8(psm->si.lend_text, lend_text, sizeof(lend_text) - 1);

            // When a duplicate value is found, then MySQL will perform an update instead of an insert.
            // ON DUPLICATE KEY UPDATE 데이터 추가되면 응답 값을 1로 리턴하고,업데이트되면 응답 값을 2로 리턴한다.
            vlen1 = sprintf(szvalue + vlen,
                "%s(\'%08d\',\'%s\' ,\'%s\' ,\'%s\',\'%s\' ,\'%d\' ,\'%s\' ,\'%d\',\'%s\',\r\n"
                "   \'%s\',\'%s\' ,\'%ld\' ,\'%ld\' ,\'%lld\' ,\'%lld\' ,\'%3.2f\' ,\'%3.2f\' ,\'%3.2f\' ,\'%3.2f\',\r\n"
                "   \'%3.2f\',\'%ld\' ,\'%ld\' ,\'%ld\' ,\'%ld\' ,\'%s\' ,\'%s\' ,\'%s\' ,\'%ld\',\r\n"
                "   \'%ld\' ,\'%ld\' ,\'%ld\' ,\'%ld\' ,\'%ld\' ,\'%ld\' ,\'%s\' ,\'%ld\' ,\'%3.2f\' ,\'%lu\' ,\'%lu\',\r\n"
                "   \'%s\', \'%s\', \'%s\',\'%s\' ,\'%s\' ,\'%s\' ,\'%s\' ,\'%s\' ,\'%3.2f\' ,\'%3.2f\' , \'%s\',NOW())\r\n",
                vlen > 0 ? ",\r\n" : "", g_conf.m_today, psm->shtcode, psm->excode, szhname, szupname,
                psm->marketid, szmarketgb, psm->si.etf, psm->category,
                psm->si.bu12gubun, psm->si.spac_gubun, psm->si.capital, psm->si.total,
                psm->si.listing, psm->si.abscnt, psm->si.per, psm->si.pbrx,
                psm->si.t_per, psm->si.epsrt, psm->si.bfeps,
                psm->si.bfoperatingincome, psm->si.bfoperatingincome2, psm->si.high52w, psm->si.low52w,
                psm->si.high52wdate, psm->si.low52wdate, psm->si.listdate, psm->si.preclose,
                psm->si.uplmtprice, psm->si.dnlmtprice,
                psm->si.open, psm->si.high, psm->si.low, psm->si.price,
                psm->si.dsign, psm->si.dchange, psm->si.drate, psm->si.jnilvolume, psm->si.todayvolume,
                szlocks, szmanage, szstop, szwarn, szcare, shterm_text, lend_text, psm->si.szinvestatt,
                psm->si.fabsvolrate, psm->si.flistonvolrate, g_conf.m_lastname);
            vlen += vlen1;

            vlen3 = sprintf(szvalue2 + vlen2,
                //"%s(?,? ,? ,?,?,?,?,?,?,\r\n"
                //"   ?,?,?,?,?,? ,?,?,?,?,\r\n"
                //"   ?,?,?,?,?,?,?,?,?,\r\n"
                //"   ? ,? ? ? ? ? ? ? ? ? ?,\r\n"
                //"   ?, ?, ?,? ,? ,? ,? ,? ,? ,? ,?,NOW())\r\n",
                "%s(\'%08d\',\'%s\' ,\'%s\' ,\'%s\',\'%s\' ,\'%d\' ,\'%s\' ,\'%d\',\'%s\',\r\n"
                "   \'%s\',\'%s\' ,\'%ld\' ,\'%ld\' ,\'%lld\' ,\'%lld\' ,\'%3.2f\' ,\'%3.2f\' ,\'%3.2f\' ,\'%3.2f\',\r\n"
                "   \'%3.2f\',\'%ld\' ,\'%ld\' ,\'%ld\' ,\'%ld\' ,\'%s\' ,\'%s\' ,\'%s\' ,\'%ld\',\r\n"
                "   \'%ld\' ,\'%ld\' ,\'%ld\' ,\'%ld\' ,\'%ld\' ,\'%ld\' ,\'%s\' ,\'%ld\' ,\'%3.2f\' ,\'%lu\' ,\'%lu\',\r\n"
                "   \'%s\', \'%s\', \'%s\',\'%s\' ,\'%s\' ,\'%s\' ,\'%s\' ,\'%s\' ,\'%3.2f\' ,\'%3.2f\' , \'%s\',datetime('now','localtime'))\r\n",
                vlen2 > 0 ? ",\r\n" : "", g_conf.m_today, psm->shtcode, psm->excode, szhname, szupname,
                psm->marketid, szmarketgb, psm->si.etf, psm->category,
                psm->si.bu12gubun, psm->si.spac_gubun, psm->si.capital, psm->si.total,
                psm->si.listing, psm->si.abscnt, psm->si.per, psm->si.pbrx,
                psm->si.t_per, psm->si.epsrt, psm->si.bfeps,
                psm->si.bfoperatingincome, psm->si.bfoperatingincome2, psm->si.high52w, psm->si.low52w,
                psm->si.high52wdate, psm->si.low52wdate, psm->si.listdate, psm->si.preclose,
                psm->si.uplmtprice, psm->si.dnlmtprice,
                psm->si.open, psm->si.high, psm->si.low, psm->si.price,
                psm->si.dsign, psm->si.dchange, psm->si.drate, psm->si.jnilvolume, psm->si.todayvolume,
                szlocks, szmanage, szstop, szwarn, szcare, shterm_text, lend_text, psm->si.szinvestatt,
                psm->si.fabsvolrate, psm->si.flistonvolrate, g_conf.m_lastname);
            vlen2 += vlen3;

            if ((rc + 1) % 10 == 0 || rc == count - 1 || vlen >= 5800) {
                qlen = sprintf(szquery,
                    "INSERT INTO `homidb`.`t_stockinfo` (sdate, shtcode, excode, hname, upname,marketid, marketgb, etf, category,\r\n"
                    "   bu12gubun, spac_gubun, capital, sigatotal, listing, abscnt, per, pbrx, t_per, epsrt,\r\n"
                    "   bfeps, bfoperatingincome, bfoperatingincome2, high52w, low52w, high52wdate, low52wdate, listdate, preclose,\r\n"
                    "   uplmtprice, dnlmtprice, openprice, high, low, closeprice, dsign, dchange, drate, jnilvolume, volume,\r\n"
                    "   `locks`, manage, stops, warn, care, shterm_text, lend_text,investatt, fprevolumerate, fprevolumelistrate,insert_id, update_ts) \r\n"
                    "   VALUES\r\n"
                    "%s\r\n"
                    "ON DUPLICATE KEY UPDATE\r\n"
                    "sdate = VALUES(sdate),shtcode = VALUES(shtcode),excode = VALUES(excode),hname = VALUES(hname),upname = VALUES(upname),\r\n"
                    "marketid =VALUES(marketid), etf=VALUES(etf), category=VALUES(category),\r\n"
                    "bfeps=VALUES(bfeps), bfoperatingincome=VALUES(bfoperatingincome), bfoperatingincome2=VALUES(bfoperatingincome2), high52w =VALUES(high52w),\r\n"
                    "low52w=VALUES(low52w), high52wdate=VALUES(high52wdate), low52wdate=VALUES(low52wdate),listdate = VALUES(listdate),preclose = VALUES(preclose),\r\n"
                    "uplmtprice=VALUES(uplmtprice),dnlmtprice=VALUES(dnlmtprice),openprice=VALUES(openprice),high=VALUES(high),low=VALUES(low),\r\n"
                    "closeprice=VALUES(closeprice),dsign=VALUES(dsign),dchange=VALUES(dchange),drate=VALUES(drate),jnilvolume=VALUES(jnilvolume),\r\n"
                    "volume=VALUES(volume),locks=VALUES(locks),manage=VALUES(manage),stops=VALUES(stops),warn=VALUES(warn),care=VALUES(care),shterm_text=VALUES(shterm_text),\r\n"
                    "lend_text=VALUES(lend_text),investatt=VALUES(investatt),fprevolumerate=VALUES(fprevolumerate),fprevolumelistrate=VALUES(fprevolumelistrate),\r\n"
                    "update_id=\'%s\',update_ts=VALUES(update_ts);\r\n", szvalue, g_conf.m_lastname);

                rows += pDB->ExecuteQuery(szquery);
                //memset(szvalue, 0, sizeof(szvalue));

#if 1 //simple SQL
                qlen = sprintf(szquery,
                    "INSERT INTO t_stockinfo (sdate, shtcode, excode, hname, upname,marketid, marketgb, etf, category,\r\n"
                    "   bu12gubun, spac_gubun, capital, sigatotal, listing, abscnt, per, pbrx, t_per, epsrt,\r\n"
                    "   bfeps, bfoperatingincome, bfoperatingincome2, high52w, low52w, high52wdate, low52wdate, listdate, preclose,\r\n"
                    "   uplmtprice, dnlmtprice, openprice, high, low, closeprice, dsign, dchange, drate, jnilvolume, volume,\r\n"
                    "   `locks`, manage, stops, warn, care, shterm_text, lend_text,investatt, fprevolumerate, fprevolumelistrate,insert_id, update_ts) \r\n"
                    "   VALUES\r\n"
                    "%s\r\n", szvalue2);
#else
                //https://stackoverflow.com/questions/2717590/sqlite-insert-on-duplicate-key-update-upsert
                qlen = sprintf(szquery,
                    "INSERT INTO `homidb`.`t_stockinfo` (sdate, shtcode, excode, hname, upname,marketid, marketgb, etf, category,\r\n"
                    "   bu12gubun, spac_gubun, capital, sigatotal, listing, abscnt, per, pbrx, t_per, epsrt,\r\n"
                    "   bfeps, bfoperatingincome, bfoperatingincome2, high52w, low52w, high52wdate, low52wdate, listdate, preclose,\r\n"
                    "   uplmtprice, dnlmtprice, openprice, high, low, closeprice, dsign, dchange, drate, jnilvolume, volume,\r\n"
                    "   `locks`, manage, stops, warn, care, shterm_text, lend_text,investatt, fprevolumerate, fprevolumelistrate,insert_id, update_ts) \r\n"
                    "   VALUES\r\n"
                    "%s\r\n"
                    "ON CONFLICT(shtcode) DO UPDATE SET \r\n"
                    "sdate = VALUES(sdate),excode = VALUES(excode),hname = VALUES(hname),upname = VALUES(upname),\r\n"
                    "marketid =VALUES(marketid), etf=VALUES(etf), category=VALUES(category),\r\n"
                    "bfeps=VALUES(bfeps), bfoperatingincome=VALUES(bfoperatingincome), bfoperatingincome2=VALUES(bfoperatingincome2), high52w =VALUES(high52w),\r\n"
                    "low52w=VALUES(low52w), high52wdate=VALUES(high52wdate), low52wdate=VALUES(low52wdate),listdate = VALUES(listdate),preclose = VALUES(preclose),\r\n"
                    "uplmtprice=VALUES(uplmtprice),dnlmtprice=VALUES(dnlmtprice),openprice=VALUES(openprice),high=VALUES(high),low=VALUES(low),\r\n"
                    "closeprice=VALUES(closeprice),dsign=VALUES(dsign),dchange=VALUES(dchange),drate=VALUES(drate),jnilvolume=VALUES(jnilvolume),\r\n"
                    "volume=VALUES(volume),locks=VALUES(locks),manage=VALUES(manage),stops=VALUES(stops),warn=VALUES(warn),care=VALUES(care),shterm_text=VALUES(shterm_text),\r\n"
                    "lend_text=VALUES(lend_text),investatt=VALUES(investatt),fprevolumerate=VALUES(fprevolumerate),fprevolumelistrate=VALUES(fprevolumelistrate),\r\n"
                    "update_id=\'%s\',update_ts=VALUES(update_ts);\r\n", szvalue, g_conf.m_lastname);
#endif
                int errcode = 0;
                rc2 = sqlite.queryexec(szquery);
                if (rc2 == 0)
                    errcode = sqlite.errcode();
                szvalue2[0] = NULL;
                vlen = 0;
                vlen2 = 0;
            }
            //////////////////////////////////////////////////////////////////////////
            rc++;
        }
        pStockList->Unlock();
        if(rc > 0)
            pDB->Commit();
#if 1
        rc = sqlite.affected_rows();
        rc2 = sqlite.CloseDB();
#endif
    }
exit_save:
    if (pDB)
        CDBPool::FreeLiveDB(pDB);
    if(szquery)
        free(szquery);
    if (szvalue)
        free(szvalue);

    if (rc > 0) {
        CString strmsg;
        strmsg.Format("종목정보|저장(%ld) SQLite>%s(%d초)",
            rc, strlog.c_str(), (GetTickCount64() - dwSaveBegTick) / 1000);
        AddToMainLog(strmsg, 0x03);
    }
    return rc;
}

DBTool

int BizCommDB::SaveMasterInfo()

{

int rc = 0;

int rows = 0;

int qlen = 0;

const char* szerror = 0;

char* szquery = NULL;

char* szvalue = NULL;

char* szvalue2 = NULL;

LONG64 dwSaveBegTick = GetTickCount64();

string strlog;

 

CMySQLDB* pDB = CDBPool::GetLiveDB();

if (pDB && pDB->IsConnected() && CDBPool::GetConnected()) {

MYSTOCK_LIST* pStockList = &g_datacenter.m_stocklist;

MYSTOCK_LIST::HASHMAP* pMap = pStockList->GetHashMap();

MYSTOCK_LIST::HASHMAP::iterator it;

HMSiseInfo* pSItem;

HMStockInfo* psm;

int doit = 0;

char szhname[86] = { 0, };

char szupname[86] = { 0, };

char szmarketgb[20] = { 0, };

char szlocks[40] = { 0, };

char szmanage[40] = { 0, };

char szstop[40] = { 0, };

char szwarn[40] = { 0, };

char szcare[40] = { 0, };

char shterm_text[40] = { 0, };

char lend_text[40] = { 0, };

char szupdate_dt[12 + 1];//갱신시간 yyyymmddhhmm

int vlen = 0, vlen1 = 0;

int vlen2 = 0, vlen3 = 0;

int count = pMap->size();

 

sprintf(szupdate_dt, "%08d%02d", g_conf.m_bizday,16);

szquery = (char*)malloc(10000);

if (!szquery) goto exit_save;

szquery[0] = NULL;

szvalue = (char*)malloc(6096);

if (!szvalue) goto exit_save;

szvalue[0] = NULL;

 

szvalue2 = (char*)malloc(6096);

if (!szvalue2) goto exit_save;

szvalue2[0] = NULL;

 

qlen = sprintf(szquery, //당일시간보다 이전이면 삭제

"DELETE FROM homidb.t_stockinfo "

"WHERE update_ts < FROM_UNIXTIME(UNIX_TIMESTAMP(STR_TO_DATE(\'%s\', '%%Y%%m%%d%%H')));", szupdate_dt);

rows = pDB->ExecuteQuery(szquery);

pDB->Commit();

#if 1

SQLiteCpp sqlite;

bool rc2;

int qlen;

//char szquery[2048];

//char szupdate_dt[12 + 1];//갱신시간 yyyymmddhhmm

string strquery;

CHAR szdatafile[MAX_PATH] = { 0, };

sprintf(szdatafile, "%s\\data\\Homi.db", g_conf.m_commondir);

strlog = (string)szdatafile;

rc2 = sqlite.OpenDB(szdatafile);

//sprintf(szupdate_dt, "%08d%02d", g_conf.m_bizday, 16);

qlen = sprintf(szquery, //당일시간보다 이전이면 삭제

"DELETE FROM t_stockinfo "

"WHERE STRFTIME('%%Y%%m%%d',update_ts) < \'%s\';\0", szupdate_dt);

rc2 = sqlite.queryexec(szquery);

#endif

 

pStockList->Lock();

for (it = pMap->begin(); it != pMap->end(); it++)

{

pSItem = &it->second;

psm = pSItem->psm;

//모두저장 20231019

doit = 1;

#if 0

wstring strUni = CA2W(psm->hname);

string strUTF8 = CW2A(strUni.c_str(), CP_UTF8).m_psz;

#endif

//빠른디버깅을 위해 종목마스터 선별 통과한 것만(시세오류있어도)

if ((psm->noerror == 0 || psm->noerror > (int)ENUM_SISEERROR::TRASHSTOCK)

&& psm->si.abscnt > 0) {

doit = 1;

}

if (doit == 0)

continue;

//////////////////////////////////////////////////////////////////////////

MBS2UTF8(psm->hname, szhname, sizeof(szhname) - 1);

MBS2UTF8(psm->upname, szupname, sizeof(szupname) - 1);

MBS2UTF8(psm->marketgb, szmarketgb, sizeof(szmarketgb) - 1);

MBS2UTF8(psm->si.lock, szlocks, sizeof(szlocks) - 1);

MBS2UTF8(psm->si.manage, szmanage, sizeof(szmanage) - 1);

MBS2UTF8(psm->si.stop, szstop, sizeof(szstop) - 1);

MBS2UTF8(psm->si.warn, szwarn, sizeof(szwarn) - 1);

MBS2UTF8(psm->si.care, szcare, sizeof(szcare) - 1);

MBS2UTF8(psm->si.shterm_text, shterm_text, sizeof(shterm_text) - 1);

MBS2UTF8(psm->si.lend_text, lend_text, sizeof(lend_text) - 1);

 

// When a duplicate value is found, then MySQL will perform an update instead of an insert.

// ON DUPLICATE KEY UPDATE 데이터 추가되면 응답 값을 1로 리턴하고,업데이트되면 응답 값을 2로 리턴한다.

vlen1 = sprintf(szvalue + vlen,

"%s(\'%08d\',\'%s\' ,\'%s\' ,\'%s\',\'%s\' ,\'%d\' ,\'%s\' ,\'%d\',\'%s\',\r\n"

"   \'%s\',\'%s\' ,\'%ld\' ,\'%ld\' ,\'%lld\' ,\'%lld\' ,\'%3.2f\' ,\'%3.2f\' ,\'%3.2f\' ,\'%3.2f\',\r\n"

"   \'%3.2f\',\'%ld\' ,\'%ld\' ,\'%ld\' ,\'%ld\' ,\'%s\' ,\'%s\' ,\'%s\' ,\'%ld\',\r\n"

"   \'%ld\' ,\'%ld\' ,\'%ld\' ,\'%ld\' ,\'%ld\' ,\'%ld\' ,\'%s\' ,\'%ld\' ,\'%3.2f\' ,\'%lu\' ,\'%lu\',\r\n"

"   \'%s\', \'%s\', \'%s\',\'%s\' ,\'%s\' ,\'%s\' ,\'%s\' ,\'%s\' ,\'%3.2f\' ,\'%3.2f\' , \'%s\',NOW())\r\n",

vlen > 0 ? ",\r\n" : "", g_conf.m_today, psm->shtcode, psm->excode, szhname, szupname,

psm->marketid, szmarketgb, psm->si.etf, psm->category,

psm->si.bu12gubun, psm->si.spac_gubun, psm->si.capital, psm->si.total,

psm->si.listing, psm->si.abscnt, psm->si.per, psm->si.pbrx,

psm->si.t_per, psm->si.epsrt, psm->si.bfeps,

psm->si.bfoperatingincome, psm->si.bfoperatingincome2, psm->si.high52w, psm->si.low52w,

psm->si.high52wdate, psm->si.low52wdate, psm->si.listdate, psm->si.preclose,

psm->si.uplmtprice, psm->si.dnlmtprice,

psm->si.open, psm->si.high, psm->si.low, psm->si.price,

psm->si.dsign, psm->si.dchange, psm->si.drate, psm->si.jnilvolume, psm->si.todayvolume,

szlocks, szmanage, szstop, szwarn, szcare, shterm_text, lend_text, psm->si.szinvestatt,

psm->si.fabsvolrate, psm->si.flistonvolrate, g_conf.m_lastname);

vlen += vlen1;

 

vlen3 = sprintf(szvalue2 + vlen2,

//"%s(?,? ,? ,?,?,?,?,?,?,\r\n"

//"   ?,?,?,?,?,? ,?,?,?,?,\r\n"

//"   ?,?,?,?,?,?,?,?,?,\r\n"

//"   ? ,? ? ? ? ? ? ? ? ? ?,\r\n"

//"   ?, ?, ?,? ,? ,? ,? ,? ,? ,? ,?,NOW())\r\n",

"%s(\'%08d\',\'%s\' ,\'%s\' ,\'%s\',\'%s\' ,\'%d\' ,\'%s\' ,\'%d\',\'%s\',\r\n"

"   \'%s\',\'%s\' ,\'%ld\' ,\'%ld\' ,\'%lld\' ,\'%lld\' ,\'%3.2f\' ,\'%3.2f\' ,\'%3.2f\' ,\'%3.2f\',\r\n"

"   \'%3.2f\',\'%ld\' ,\'%ld\' ,\'%ld\' ,\'%ld\' ,\'%s\' ,\'%s\' ,\'%s\' ,\'%ld\',\r\n"

"   \'%ld\' ,\'%ld\' ,\'%ld\' ,\'%ld\' ,\'%ld\' ,\'%ld\' ,\'%s\' ,\'%ld\' ,\'%3.2f\' ,\'%lu\' ,\'%lu\',\r\n"

"   \'%s\', \'%s\', \'%s\',\'%s\' ,\'%s\' ,\'%s\' ,\'%s\' ,\'%s\' ,\'%3.2f\' ,\'%3.2f\' , \'%s\',datetime('now','localtime'))\r\n",

vlen2 > 0 ? ",\r\n" : "", g_conf.m_today, psm->shtcode, psm->excode, szhname, szupname,

psm->marketid, szmarketgb, psm->si.etf, psm->category,

psm->si.bu12gubun, psm->si.spac_gubun, psm->si.capital, psm->si.total,

psm->si.listing, psm->si.abscnt, psm->si.per, psm->si.pbrx,

psm->si.t_per, psm->si.epsrt, psm->si.bfeps,

psm->si.bfoperatingincome, psm->si.bfoperatingincome2, psm->si.high52w, psm->si.low52w,

psm->si.high52wdate, psm->si.low52wdate, psm->si.listdate, psm->si.preclose,

psm->si.uplmtprice, psm->si.dnlmtprice,

psm->si.open, psm->si.high, psm->si.low, psm->si.price,

psm->si.dsign, psm->si.dchange, psm->si.drate, psm->si.jnilvolume, psm->si.todayvolume,

szlocks, szmanage, szstop, szwarn, szcare, shterm_text, lend_text, psm->si.szinvestatt,

psm->si.fabsvolrate, psm->si.flistonvolrate, g_conf.m_lastname);

vlen2 += vlen3;

 

if ((rc + 1) % 10 == 0 || rc == count - 1 || vlen >= 5800) {

qlen = sprintf(szquery,

"INSERT INTO `homidb`.`t_stockinfo` (sdate, shtcode, excode, hname, upname,marketid, marketgb, etf, category,\r\n"

"   bu12gubun, spac_gubun, capital, sigatotal, listing, abscnt, per, pbrx, t_per, epsrt,\r\n"

"   bfeps, bfoperatingincome, bfoperatingincome2, high52w, low52w, high52wdate, low52wdate, listdate, preclose,\r\n"

"   uplmtprice, dnlmtprice, openprice, high, low, closeprice, dsign, dchange, drate, jnilvolume, volume,\r\n"

"   `locks`, manage, stops, warn, care, shterm_text, lend_text,investatt, fprevolumerate, fprevolumelistrate,insert_id, update_ts) \r\n"

"   VALUES\r\n"

"%s\r\n"

"ON DUPLICATE KEY UPDATE\r\n"

"sdate = VALUES(sdate),shtcode = VALUES(shtcode),excode = VALUES(excode),hname = VALUES(hname),upname = VALUES(upname),\r\n"

"marketid =VALUES(marketid), etf=VALUES(etf), category=VALUES(category),\r\n"

"bfeps=VALUES(bfeps), bfoperatingincome=VALUES(bfoperatingincome), bfoperatingincome2=VALUES(bfoperatingincome2), high52w =VALUES(high52w),\r\n"

"low52w=VALUES(low52w), high52wdate=VALUES(high52wdate), low52wdate=VALUES(low52wdate),listdate = VALUES(listdate),preclose = VALUES(preclose),\r\n"

"uplmtprice=VALUES(uplmtprice),dnlmtprice=VALUES(dnlmtprice),openprice=VALUES(openprice),high=VALUES(high),low=VALUES(low),\r\n"

"closeprice=VALUES(closeprice),dsign=VALUES(dsign),dchange=VALUES(dchange),drate=VALUES(drate),jnilvolume=VALUES(jnilvolume),\r\n"

"volume=VALUES(volume),locks=VALUES(locks),manage=VALUES(manage),stops=VALUES(stops),warn=VALUES(warn),care=VALUES(care),shterm_text=VALUES(shterm_text),\r\n"

"lend_text=VALUES(lend_text),investatt=VALUES(investatt),fprevolumerate=VALUES(fprevolumerate),fprevolumelistrate=VALUES(fprevolumelistrate),\r\n"

"update_id=\'%s\',update_ts=VALUES(update_ts);\r\n", szvalue, g_conf.m_lastname);

 

rows += pDB->ExecuteQuery(szquery);

//memset(szvalue, 0, sizeof(szvalue));

 

#if 1 //simple SQL

qlen = sprintf(szquery,

"INSERT INTO t_stockinfo (sdate, shtcode, excode, hname, upname,marketid, marketgb, etf, category,\r\n"

"   bu12gubun, spac_gubun, capital, sigatotal, listing, abscnt, per, pbrx, t_per, epsrt,\r\n"

"   bfeps, bfoperatingincome, bfoperatingincome2, high52w, low52w, high52wdate, low52wdate, listdate, preclose,\r\n"

"   uplmtprice, dnlmtprice, openprice, high, low, closeprice, dsign, dchange, drate, jnilvolume, volume,\r\n"

"   `locks`, manage, stops, warn, care, shterm_text, lend_text,investatt, fprevolumerate, fprevolumelistrate,insert_id, update_ts) \r\n"

"   VALUES\r\n"

"%s\r\n", szvalue2);

#else

//https://stackoverflow.com/questions/2717590/sqlite-insert-on-duplicate-key-update-upsert

qlen = sprintf(szquery,

"INSERT INTO `homidb`.`t_stockinfo` (sdate, shtcode, excode, hname, upname,marketid, marketgb, etf, category,\r\n"

"   bu12gubun, spac_gubun, capital, sigatotal, listing, abscnt, per, pbrx, t_per, epsrt,\r\n"

"   bfeps, bfoperatingincome, bfoperatingincome2, high52w, low52w, high52wdate, low52wdate, listdate, preclose,\r\n"

"   uplmtprice, dnlmtprice, openprice, high, low, closeprice, dsign, dchange, drate, jnilvolume, volume,\r\n"

"   `locks`, manage, stops, warn, care, shterm_text, lend_text,investatt, fprevolumerate, fprevolumelistrate,insert_id, update_ts) \r\n"

"   VALUES\r\n"

"%s\r\n"

"ON CONFLICT(shtcode) DO UPDATE SET \r\n"

"sdate = VALUES(sdate),excode = VALUES(excode),hname = VALUES(hname),upname = VALUES(upname),\r\n"

"marketid =VALUES(marketid), etf=VALUES(etf), category=VALUES(category),\r\n"

"bfeps=VALUES(bfeps), bfoperatingincome=VALUES(bfoperatingincome), bfoperatingincome2=VALUES(bfoperatingincome2), high52w =VALUES(high52w),\r\n"

"low52w=VALUES(low52w), high52wdate=VALUES(high52wdate), low52wdate=VALUES(low52wdate),listdate = VALUES(listdate),preclose = VALUES(preclose),\r\n"

"uplmtprice=VALUES(uplmtprice),dnlmtprice=VALUES(dnlmtprice),openprice=VALUES(openprice),high=VALUES(high),low=VALUES(low),\r\n"

"closeprice=VALUES(closeprice),dsign=VALUES(dsign),dchange=VALUES(dchange),drate=VALUES(drate),jnilvolume=VALUES(jnilvolume),\r\n"

"volume=VALUES(volume),locks=VALUES(locks),manage=VALUES(manage),stops=VALUES(stops),warn=VALUES(warn),care=VALUES(care),shterm_text=VALUES(shterm_text),\r\n"

"lend_text=VALUES(lend_text),investatt=VALUES(investatt),fprevolumerate=VALUES(fprevolumerate),fprevolumelistrate=VALUES(fprevolumelistrate),\r\n"

"update_id=\'%s\',update_ts=VALUES(update_ts);\r\n", szvalue, g_conf.m_lastname);

#endif

int errcode = 0;

rc2 = sqlite.queryexec(szquery);

if (rc2 == 0)

errcode = sqlite.errcode();

szvalue2[0] = NULL;

vlen = 0;

vlen2 = 0;

}

//////////////////////////////////////////////////////////////////////////

rc++;

}

pStockList->Unlock();

if(rc > 0)

pDB->Commit();

#if 1

rc = sqlite.affected_rows();

rc2 = sqlite.CloseDB();

#endif

}

exit_save:

if (pDB)

CDBPool::FreeLiveDB(pDB);

if(szquery)

free(szquery);

if (szvalue)

free(szvalue);

 

if (rc > 0) {

CString strmsg;

strmsg.Format("종목정보|저장(%ld) SQLite>%s(%d초)",

rc, strlog.c_str(), (GetTickCount64() - dwSaveBegTick) / 1000);

AddToMainLog(strmsg, 0x03);

}

return rc;

}

'C | C++ | VC++' 카테고리의 다른 글

VC++ 정규식검색 여러문자열  (0) 2025.06.12
MYSQL to SQLite  (1) 2025.06.08
주식종목 리스트업(sort)  (5) 2025.02.22
.rc 에서 사용되고 있습니다.  (0) 2025.02.16
마소는 visual studio 버그안고치냐  (5) 2024.12.19

관련글

티스토리툴바