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)不可不可

2013年5月29日 星期三

Operating the HTML table like Powerbuilder's DataWindow chapter 0

發想來自我接觸最久的程式語言 Powerbuilder,說真的跳通到其他語言時,會非常懷念它的DataWindow 物件。原因不外乎,它讓資料操作變得很簡單,甚至讓程式設計師變懶了。

但如果開始使用其他語言時,例如現在幾乎都要用網頁來工作,就發現網頁要操作資料真的不是那麼容易,尤其使用AJAX技術時,針對頁面的操作,每個工程師寫出來的方式大相逕庭,閱讀理解就變得重重。
有鑑於此,只好將自己理解datawidnow的部份,將其方法(method)實現到javascript裡面來。
盡力讓畫面表單操作變得簡單一些。
這樣的好處是,使用相同套件,程式碼閱讀起來清爽也易懂,比較容易除錯。

下面簡單介紹一下操作原理,與表格架構,DataWindow會用DW簡稱:
1、原則上一個類DW的架構如下:
<div id="objid">
 <table width="100">
  <tr>
   <th width="48" cn="field1">欄1標題</th>
   <th width="18" cn="field2">欄2標題</th>
   <th width="28" cn="field3">欄3標題</th>
  </tr>
 <table>
</div>
這是一個標準table外面包著一個div層,而這個div層的id就是這個datawindow的名稱(datawindow name),一個DW結構(div)裡面只能有一個table存在。
這個結構很重要,因為接下來所有的操作都會參考這個結構的定義。

2、TH屬性的定義:
cn:欄位名稱,必要的,英數字,其值就是代表了這個欄位的column name。
edit:編輯模式,選擇性,此屬性表示生成的資料可以編輯,其值(編輯模式)有:text / password / date / ddw / lbox。
key:關鍵欄,選擇性,其值為yes / no,yes=開啟編輯模式時,如果是修改的情況下,此欄位資料不可以被編輯。
fmt:顯示格式化,選擇性,其值為int / float ,不帶此屬性表示生成的資料為插入資料原始樣式。
ddw:選擇性,當edit="ddw"時,需指定此ddw屬性,畫面輸入方式呈現下拉式選單,而此下拉式選單的資料來源會以其指定的值向 隱藏資料區 取得。而隱藏資料區則是這個資料表單初始化的時候必須先完成,初始化請參考 retrieveDWC 指令。
lbox:選擇性,當edit="lbox"時,需指定此lbox屬性,畫面輸入方式呈現下拉式選單,而此下拉式選單的資料來源會參考其值="'key1':'value1','key2','value2'" 生成。,lbox值內容,以JSON內容列舉,key 或 value前後都必須加上單引號'。
xls:當畫面表格轉換excel檔案時,指定欄位輸出強制excel格式,選擇性,目前僅支援xls="nf@",此格式禁止excel把類數字自動轉數字(一般轉換數字後有0開頭會消失)
pis:轉出資料使用個資遮罩法,選擇性,值為一字串如 "i,5";這個字串由逗號分隔成兩個參數,參數1= i(間格遮)、r(右方遮)、l(左方遮),參數2=遮前保留字元。
※關於個資遮罩的疑惑:公司於內部網路因為內部需要所以有時後會顯示地址、電話等資訊,但是輸出檔案時就必須小心遮蔽重要識別資訊;但若於公司外部網路時,就必須在資料讀取時就預先遮蔽,亦即畫面也無法看到完整的地址、電話等供識別資訊

3、表/裡結構:
這個跟DW行為相同,當資料直接新增時,遇到有參考列舉選項時,雖然畫面呈現的是列舉選項的文字,但是實際上的值卻是選項的 value。

其次,編輯資料時,畫面上雖然是編輯狀態,但是使用者可以取消讓原本的資料復原。


2013年5月28日 星期二

使用coldfusion將HTML表格輸出成EXCEL檔案(使用者下載)

Coldfusion 的檔案輸出控制,主要有下面兩個指令
<cfcontent><cfheader>
cfcontent 用來告訴瀏覽器等一下要輸出的內容是甚麼格式
cfheader 用來宣告傳輸資料的相關資訊

簡單的輸出測試  saveFileTest.cfm:
<cfsetting enablecfoutputonly="Yes">
<cfcontent type=
"application/vnd.ms-excel; charset=big5">
<cfheader name=
"Content-Disposition" value="attachment;filename=ODR10001234567.xls">
<!--- office Excel Format Structure--->
<cfoutput><html xmlns:x="urn:schemas-microsoft-com:office:excel">
<head>
<meta http-equiv=
"Content-Type" content="text/html;charset=big5">
<!--[if gte mso 9]>
<xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>

<!--- this line names the worksheet --->
<x:Name>訂單狀態</x:Name>
<x:WorksheetOptions>

<!--- these 2 lines are what works the magic --->
<x:Panes>
</x:Panes>
</x:WorksheetOptions>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
</x:ExcelWorkbook>
</xml>
<![endif]-->

</head>
<body>

<table border="0" cellpadding="1" cellspacing="2">
<tr>
<th>
客戶編號</th><th>業務員</th><th>出貨員</th><th>訂單日</th><th>訂單號</th><th>訂購人</th><th>批號</th><th>交付日</th><th>交付時間</th><th>應收款</th><th>實收款</th><th>交易狀況</th></tr>
<tr>
<td style=
'vnd.ms-excel.numberformat:@'>00987</td>
<td>
苗小平</td>
<td>
江企財</td>
<td>
2013/05/30</td>
<td style=
'vnd.ms-excel.numberformat:@'>BCDF9876534</td>
<td>
黃疆普</td>
<td style=
'vnd.ms-excel.numberformat:@'>0098765432</td>
<td>
2013/06/25</td>
<td>
11:30</td>
<td>
1200</td>
<td>
1200</td>
<td>
100-success</td>
</tr>
<tr>
<td style=
'vnd.ms-excel.numberformat:@'>00147</td>
<td>
林英蠻</td>
<td>
江企財</td>
<td>
2013/05/30</td>
<td style=
'vnd.ms-excel.numberformat:@'>BCDF98788471</td>
<td>
劉力行</td>
<td style=
'vnd.ms-excel.numberformat:@'>0098765431</td>
<td>
2013/07/01</td>
<td>
13:00</td>
<td>
8477.5</td>
<td>
8477</td>
<td>
50-discount</td>
</tr>
</table>

</body>
</html>

</cfoutput>


首先,使用 cfsetting enablecfoutputonly="Yes" 來壓制不必要的空白輸出,
然後,cfcontent type="application/vnd.ms-excel; charset=big5"
宣告文件為excel類型,且為big5編碼,
接著,cfheader name="Content-Disposition" value="attachment;filename=ODR10001234567.xls"
告訴瀏覽器,這是附件下載的檔案 ODR10001234567.xls
接著用 cfoutput 輸出內容,簡易的excel內容編制可以使用HTML格式,但是需要經過宣告

最開始的 html 到 body 這一段就是讓excel認識下面的html table屬於excel內容編排
中間的<table></table>就是內容編排,你會發現它就是很標準的HTML table 表

注意的是有個 style='vnd.ms-excel.numberformat:@' 宣告,這是讓excel不要過度雞婆把『疑似數字』的內容自動轉成數字,因為會很捆擾的,比竟某些場合的數字是當成字串使用,因為excel轉數字的話會把前面的 0 自動取消掉。

執行這個網頁會直接讓瀏覽器輸出存檔,檔案名稱就是 ODR10001234567.xls(當然儲存時可以改)

如何?簡單吧...