jquery - 从 AJAX 调用返回 CFC 查询数据

Question

我正在对 CFC 进行 AJAX 调用，以从数据库返回一条记录。记录始终以字符串形式返回。然后我用该值更新输入字段。

问题是我的字段正在填充以下内容:

<wddxPacket version='1.0'><header/><data><string>0</string></data</wddxPacket>

相反，我只想要字符串的值。在此示例中:0

AJAX 调用:

<script>
function populateSalesTax(){
    // Populate the start and stop odometer text boxes
    // when the selectlist ajax completes successfully

        $.ajax({
            url:'cfcs/taxdata.cfc?method=getSalesTax',
            data: { company_name: $("##company_name>option:selected").attr("Value") },
            success: function(response) {
                console.log('Sales Tax Fubction Ran Successfully');
                $("##taxRate").val( response );

                },
            error: function(response) {
                console.log('Error' + response)}
          });
    }
</script>

CFC功能:

<cffunction name="getSalesTax" access="remote" returnType="string">
   <cfargument name="company_name" type="any" required="true">

   <!--- localize function variables --->
   <cfset var taxDetail = "">
   <cfoutput>
   <cfquery name="taxDetail" datasource="#datasource#">
       SELECT tax_rate
       FROM   customer_table
       <!--- adjust cfsqltype if needed --->
       WHERE company_name = <cfqueryparam value="#ARGUMENTS.company_name#"
                                cfsqltype="cf_sql_varchar">
   </cfquery>
   </cfoutput>

   <cfreturn taxDetail.tax_rate>
</cffunction>

输入字段:

<cfinput name="taxRate" id="taxRate" type="text" size="2" readonly="yes">

Answer 1

我这里有很多代码，因为我做的事情与大多数人有很大不同，但这是一个演变。以下是我创建典型表格的方法:

if exists (select * from sysobjects where id = object_id(N'myTable') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE myTable
GO
CREATE TABLE myTable(
myTableID Int Identity(1000,1) PRIMARY KEY NONCLUSTERED
,myTableName varchar(128) default ''
,myTableDesc varchar(max) default ''
,myTableSort Int default 0
)
GO
INSERT INTO myTable(myTableName,myTableSort) VALUES('One',1)
INSERT INTO myTable(myTableName,myTableSort) VALUES('Two',2)
INSERT INTO myTable(myTableName,myTableSort) VALUES('Three',3)

我使用存储过程。 SQL 代码如下:

create schema myTable authorization dbo
-- It might seem odd to create a schema called with the name of the table, but that's how I organize the stored procedures by table name.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'myTable.[get]') AND type in (N'P', N'PC'))
DROP PROC myTable.[get]
GO
CREATE PROC myTable.[get]
(@UsrID Int
,@RemoteAddr Varchar(15)
,@myTableID Int
) AS
SELECT myTableID,myTableName,myTableDesc,myTableSort
FROM myTable
WHERE myTableID = @myTableID
GO
exec myTable.[get] 0,'',1001

@UsrID 是每个存储过程中的第一个参数。谁想知道。

@RemoteAddr 是每个存储过程中的第二个参数。这是用户的 IP 地址。现在，我知道这并不是万无一失的，但我认为这里的安全性是这样的1) @UsrID 是他们知道的东西(他们必须知道密码才能以用户身份登录)。

2) @RemoteAddr 是他们自己的东西(尽管 IP 地址可以被欺骗)。

@myTableID 是@myTable 的主键。我总是将主键命名为表名，后跟 ID。

这是一个典型的 .cfm 页面:

<cfscript>

</cfscript>

<cfoutput>
<cfinclude template="/Inc/html.cfm">
<link rel="stylesheet" type="text/css" href="Index.css">
<cfinclude template="/Inc/body.cfm">
<ul>
    <li><a class="myClass" data-mytableid="1000" href="JavaScript:;">Click One!</a></li>
    <li><a class="myClass" data-mytableid="1001" href="JavaScript:;">Click Two!</a></li>
    <li><a class="myClass" data-mytableid="1002" href="JavaScript:;">Click Three!</a></li>
</ul>
<cfinclude template="/Inc/foot.cfm">
<script src="Index.js"></script>
<cfinclude template="/Inc/End.cfm">
</cfoutput>

我在所有页面中包含的脚本:

window.dom = {}
dom.msg = $('#msg')
dom.main = $('main')
dom.fail = function(xhr, status, response) {
    dom.msg.text(status + ': ' + response).addClass('label-warning')
    dom.main.html(xhr.responseText)
    debugger
}

以及页面本身的脚本:

(function() {
    $(document).on('click','.myClass',clicked)

    function clicked() {
        var local = {}
        local.url = 'myTable.cfc'
        local.type = 'POST' // or GET
        local.dataType = 'json'
        local.data = {}
        local.data.method = 'get';
        local.data.myTableID = $(this).data('mytableid')
        local.context = this;
        result = $.ajax(local)
        result.fail(dom.fail)
        result.done(done)
    }
    function done(response, status, xhr) {
//      console.log(response.COLUMNS)
//      console.log(response.DATA)
//      console.log(response.DATA[0])
//      console.log(response.DATA[0][0]) // myTableID
//      console.log(response.DATA[0][1]) // myTableName
//      console.log(response.DATA[0][2]) // myTableDesc
//      console.log(response.DATA[0][3]) // myTableSort
//      console.log(status)
//      console.log(xhr)
    }
})()

最后，myTable.cfc:

component {

remote function get(myTableID) returnformat='json' {
    storedproc dataSource=session.dataSource
        procedure='myTable.[get]' {
        procparam cfSqlType='cf_sql_integer' value=session.Usr.UsrID;
        procparam cfSqlType='cf_sql_varchar' value=session.RemoteAddr;
        procparam cfSqlType='cf_sql_integer' value=arguments.myTableID;
        procresult resultset=1 name='result';
    }
    return result
}
}

我应该粘贴一个缩写的 Application.cfc:

component {
this.Name = 'myAJAX'
this.dataSource = 'LR2015Summer1'
this.SessionManagement = true

function onSessionStart() {
    session.dataSource = this.dataSource
    session.RemoteAddr = getPageContext().getRequest().getRemoteAddr()
    session.Usr = {}
    session.Usr.UsrID = 0
}

function onRequestStart(LogCFCName) {
    setting showDebugOutput=false;
    if (isDefined('url.onSessionStart')) {
        onSessionStart()
    }
    request.msg = ''
    request.mod = 'label-info'

    request.title='How I AJAX and return a query'
    request.home = '/HowIWrite/AJAX/cfc'
    request.css       = url.css       ?: true
    request.js        = url.js        ?: true
    request.bootstrap = url.bootstrap ?: true
    request.bootstrap_theme = 'bootstrap-theme'
    request.tryCatch  = url.tryCatch  ?: false
    request.navbar    = url.navbar    ?: true
    request.container = url.container ?: true
    request.GoogleMaterialdesign = url.GoogleMaterialdesign ?: true
    request.jQueryUI  = url.jQueryUI  ?: false
    request.jQueryUI_theme = 'ui-lightness' // black-tie,blitzer,cupertino,dark-hive,dot-luv,eggplant,excite-bike,flick,hot-sneaks,humanity,le-frog,mint-choc,overcast,pepper-grinder,redmond,smoothness,south-street,start,sunny,swanky-purse,trontastic,ui-darkness,ui-lightness,vader

    request.cgiName = getPageContext().getRequest().getServletPath()
    request.cgiName = Left(request.cgiName,Len(request.cgiName)-4)
    request.cgiName = ListLast(request.cgiName,'/')
    request.cssName = request.cgiName & '.css'
    request.jsName  = request.cgiName & '.js'
}

}