ClearDBを使ってみた

元記事はこちらです。

■Application.cfm

<cfprocessingdirective pageEncoding = "UTF-8">
<cfsetting enableCFoutputOnly="yes">

<cfapplication name="#hash( getCurrentTemplatePath() )#"
    sessionmanagement="yes"
    sessiontimeout=#CreateTimeSpan(1, 0, 0, 0)# >

<cfset request.proxyServer  = "">
<cfset request.proxyPort    = "80">

<cfset request.username = "xxx">
<cfset request.password = "yyy">
<cfset request.CLR_URL = "http://www.cleardb.com/v1/api.php?action=">

<cfparam name="application.s_id" default="">
<cfif (application.s_id eq "")>
    <cflock scope="application" timeout="60">
        <cfset application.s_id = cmn_login()>
    </cflock>
</cfif>
<!---===============================
cmn_login
    sessionIDを返す
================================--->
<cffunction name="cmn_login" access="public" returnType="string" output="no">
    <cfset var ret = "">

    <cfhttp 
        timeout="60"
        url="#request.CLR_URL#login"
        proxyServer="#request.proxyServer#"
        proxyPort="#request.proxyPort#"
        method = "post">

        <cfhttpparam type="formField" name="username" value="#request.username#">
        <cfhttpparam type="formField" name="password" value="#request.password#">
    </cfhttp>

    <cfif (cfhttp.filecontent eq "")>
        <cfreturn "">
    </cfif>

    <cfset ret = DeserializeJSON(cfhttp.filecontent)>
    <cfreturn ret.response>
</cffunction>

<!---===============================
cmn_sqlQuery
================================--->
<cffunction name="cmn_sqlQuery" access="public" returnType="struct" output="no">
    <cfargument name="s_id" type="string" required="yes">
    <cfargument name="qry"  type="string" required="yes">

    <cfhttp 
        timeout="60"
        url="#request.CLR_URL#sqlQuery"
        proxyServer="#request.proxyServer#"
        proxyPort="#request.proxyPort#"
        method = "post">
        <cfhttpparam type="formField" name="session_id" value="#arguments.s_id#">
        <cfhttpparam type="formField" name="query" value="#arguments.qry#">
    </cfhttp>

    <cfreturn DeserializeJSON(cfhttp.filecontent)>
</cffunction>

■insert.cfm

<cfset local = structNew()>

<cfloop index="local.line" file="#expandpath("data.txt")#">
    <cfif listLen(local.line, chr(9)) eq 3>
        <cfset local.asin       = ListGetAt(local.line, 1, chr(9))>
        <cfset local.title      = ListGetAt(local.line, 2, chr(9))>
        <cfset local.kin        = ListGetAt(local.line, 3, chr(9))>

        <cfsavecontent VARIABLE ="local.tmp">
        <cfoutput>
        insert into TEST_001 (
            ASIN,TITLE,KIN
        )values(
            '#local.asin#',
            '#local.title#',
             #local.kin#
        )
        </cfoutput>
        </cfsavecontent>

        <cfset local.data = cmn_sqlQuery(application.s_id, local.tmp)>
    </cfif>
</cfloop>

■view.cfm

<cfprocessingdirective pageEncoding = "UTF-8">
<cfparam name="url.order" default="asin">

<cfset local = structNew()>

<cfsavecontent VARIABLE ="local.tmp">
<cfoutput>
select *
from TEST_001
order by #url.order#
</cfoutput>
</cfsavecontent>

<cfset local.data = cmn_sqlQuery(application.s_id, local.tmp)>

<cfoutput>
<table border="1" cellspacing=0 cellpadding=0>
<cfloop index="local.idx" array="#local.data.response#">
    <tr>
        <td><a href="http://www.amazon.co.jp/exec/obidos/ASIN/#local.idx.asin#/utalab-22/ref=nosim" target="_blank">#local.idx.title#</a></td>
        <td align="right">#numberformat(local.idx.kin)#円</td>
    </tr>
</cfloop>
</table>

</cfoutput>

■data.txt（タブくぎりでUTF-8のデータ）

4478015147	Facebookをビジネスに使う本	1500

4797361123	日本でいちばん社員満足度が高い会社の非常識な働き方	1470

4844329685	できるポケット au IS03をスマートに使いこなす基本＆活用ワザ150	1344

4820717901	エバーノートとグーグルを使えばすべてのノート・メモ・書類が一元化できる！ クラウド情報整理術	1575

4881668625	Twitter×アメブロでビジネスを加速する方法	1554