2013年6月7日 星期五

Coldfusion 多個 query 合併

簡單的用法:
<cfquery name="q1" datasource="store1" blockFactor="100">
 select * from sales_lists where sale_date = '2013/05/02'
</cfquery>

<cfquery name="q2" datasource="store2" blockFactor="100">
 select * from sales_lists where sale_date = '2013/05/02'
</cfquery>

<cfquery name="datesales" dbtype="query">
 select * from q1
 union
 select * from q2
 order by sale_employee
</cfquery>

把多個 DB 查詢用 in memory 查詢 union 起來。

那如果是 LOOP 迴圈連續查詢的 query 呢?
那就讓 query name 不一樣就可以了,然後再用LOOP把query union 起來
<cfloop from="1" to="#ListLen(employeelist)#" index="i">
 <cfquery name="qresult#i#" datasource="db1">
  select * from employee where employee_no = '#ListGetAt(i)#'
 </cfquery>
</cfloop>

<cfquery name="combine" dbtype="query">
  <cfloop from="1" to="#ListLen(employeelist)#" index="i">
    select * from qresult#i#
    <cfif i lt ListLen(employeelist)>union</cfif>
  </cfloop>
</cfquery>

2013年6月6日 星期四

Coldfusion Query 的 Commit

最近由coldfusion 操作資料庫查詢時發生的一件事情
在sybase資料庫 做 Replication 的兩部資料庫: A-DB 與 B-DB,
本來,Web-Base的作業都連線在 B-DB 上,App-Base都連在 A-DB上
後來因為B-DB資料區有異常,就把 coldfusion 連線全部連到 A-DB ,
接下來,慘事發生了......
A-DB 三不五時發生嚴重 lock ,導致所有作業都停擺,細查原因,發現:
1、全部 lock 都是來自 coldfusion 的 connection ,而且都是 jdbc 驅動連線
2、如果 coldfusion 使用 Sybase 驅動連線,不會發生此現象
3、ColdFusion 不管何種連線到 B-DB 都不太會發生 lock 過久問題
4、Web-Base 的查詢幾乎都是大範圍資料查詢

所以,查了一下設定發現,B-DB 的 lock 範圍強制設定在 row lock等級,
Read isolation 屬 uncommit ,這樣的設定應該是為了提高網頁端大量查詢的效率
而 A-DB 屬於內部作業使用有需要極高的正確性,因此屬於page lock等級,
Read isolation 屬 commit,因此 App-Base 開發時只要使用到 insert/update
一律要 commit/rollback才能繼續。

以上設定應該是因應公司營業型態調整的

所以,如果在不知道細節的情況下, Coldfusion 的 <cfquery> 指令下完之後
,無論是Select / Insert / Update 都記得要下一行:
<cftransaction action="commit" />
或是
<cftransaction action="rollback" />
才不至於出現過度lock狀況,當然這也是比較正統的撰寫習慣
至於 sybase driver connector 與 jdbc driver connector為什麼有不一樣的行為,
應該是差在 select 這個指令,sybase driver 對 select 指令會自動 commit 掉,
但是jdbc driver connector 則是全部自己來(該下的指令就要下不要偷懶)。

即便,不是sybase資料庫也建議SQL語句之後最好commit掉,以免有lock過度的情況

coldfusion query buildin functions

Method Signiture
Return TypeMethod SignitureDescription
void queryName.first() jump to the first row of a query
void queryName.last() jump to the last row of a query
boolean queryName.isFirst() true if we are positioned at the first row
boolean queryName.isLast() true if we are positioned at the first row
boolean queryName.next() go to the next row, return true if there is a next row
boolean queryName.previous() go to the previous row, return true if there is a previous row
int queryName.findColumn(String name) get the internal zero based Column ID by name
void sort(int columnID, boolean ascending) sort by a column, use findColumn to get the column id
String getColumnTypeName(int columnID) gets the data type of a column, this one didn't work properly when I tested it, it would return NUMERIC for a field that was a varchar. There is a method called guessColumnType that was probably used to determine it, it guessed wrong.

sort query in coldfusion

當我們需要用 coldfusion 查詢資料庫時會使用 cfquery 執行SQL指令
<cfquery name="allEmployee" datasource="myDB">
 Select employee_no,employee_name,employee.duty
 From employee
 Where employee_retired = 'N'
</cfquery>

當然,資料排序可以直接下 SQL 的 ORDER BY 來完成
但是,如果有特殊需求需要在 coldfusion 內(in memory)才排序的話
有兩種方式可以完成:
1、使用 cfquery 對已經存在的 query 再處理
<cfquery name="sortedEmployee" dbtype="query">
 Select *
 From allEmployee
 Order By employee_name ASC
</cfquery>


2、使用query專用指令
<cfset allEmployee.sort(allEmployee.findColumn("employee_name"),true)>
對於第2種用法,可能要非常小心
因為,adobe的 coldfusion 服務器與 Railo 的服務器有不同的使用法
(注意,我測試的adobe coldfusion 是 MX7,可能新的版本有變)
指令Adobe Coldfusion MX7Railo
QueryName.sort(QueryName.findColumn("ColumnName"),true)不可
QueryName.sort(2,true)不可
QueryName.sort("ColumnName",true)不可
QueryName.sort("2",true)不可不可