使用SCRIPTCASE的一些注意事項。
** GRID 如果自訂 SQL需用 SCRIPTCASE內設的 SQL BUILDER建立，比較沒問題。
如果自己用編輯器，建立的 SQL stmt，很容易產生錯誤。不知道為什麼？！可能SC本身有針對SQL語法做一些處理的關係。
** 不能偷懶
剛設計好，上傳出版時，在執行主機環境設定時，時間很趕，所有漏掉了 Client Char Set 為 "UTF-8"。結果忘記了！
今天在上傳正式程式時，才發現怎麼中文都出現亂碼，成為 "?"。這是典型的中文亂碼的問題。
一時忘了之前漏掉設定資料庫連線為 Client CHAR UTF-8，結果搞了半天。逐一檢查個別應用程式的 Application CHAR SET/ Language/ 專案的 Language 等！浪費了很多時間！都白做了！
其實就是 Server 上的 Database Client 連線設定忘記設！
一重新設定，就OK了！
唉~~真的每一個步驟，不要馬虎，以免多做了許多虛功！
紀錄一下，提醒自己。以後不要犯一樣的錯誤。
{lang_othrs_title} Report of - 報表
{lang_othrs_titl} List - 列表
記得要更改一下Grid Layout HEADER 上的顯示。
ScriptCase 提供很多PDF匯出功能，使用了一些 Third Party 做的 pdf Class
而，Grid Export to PDF，則是使用 wkhtmltopdf 這個程式。wkhtmltopdf 不是一個 php class，而是一個可執行的 cmd 檔案，必須直接用 cmd 來下指令 將 html 產生 pdf
類似這樣：
> wkhtmltopdf source.html output.pdf
而 wkhtmltopdf 需要安裝在 產品主機上，才可以執行。
------------------------------------------------------------
Scriptcase 在自己Local開發環境下，Grip Application 可以順利的匯出 pdf 檔案。
可是，出版到產品環境時，卻無法匯出。
經過Google大神查詢結果，發現：
http://www.toughdev.com/content/2015/11/fixing-pdf-export-issues-when-using-a-scriptcase-grid-application/
1. 產品主機要安裝 JRE
2. SC使用 wkhtmltopdf ，所以
放在：/_lib/prod/third/wkhtmltopdf/os_.../
產生的檔案在
/_lib/tmp/
裡面有 grid 的 html 檔案。
還有一個執行 wkhtmltopdf 的 log 檔案
./wkhtmltopdf-amd64 --page-size A4 --orientation Portrait --header-right "[page]" https://malladmin.hongfumall88.com/_lib/tmp/sc_gamer_grid_link2_qrcode_pdf_html_hmsaloggbfeb16ekrisu8lbjb9.html /home/ddnews/domains/malladmin.hongfumall88.com/public_html/_lib/tmp/sc_pdf_20201210065017_905_gamer_grid_link2_qrcode_pdf.pdf
3. 需要打開 /_lib/prod/third/wkhtmltopdf/os_.../ 那個執行檔的權限為 "777"，這樣就解決了權限的問題。
(直接用 SSH 在 Linxs cmd 下去執行 ./wkhtmltopdf-amd64 --page-size A4 --orientation Portrait --header-right "[page]" https://malladmin.hongfumall88.com/_lib/tmp/sc_gamer_grid_link2_qrcode_pdf_html_hmsaloggbfeb16ekrisu8lbjb9.html /home/ddnews/domains/malladmin.hongfumall88.com/public_html/_lib/tmp/sc_pdf_20201210065017_905_gamer_grid_link2_qrcode_pdf.pdf，結果出現 權限錯誤的問題，還有幾個 SC 產生的 html 裡面所指引的 .css 路徑錯誤 "/_lib/" 變成 "/_lib/_lib/....，這應該是 SC產生的 html碼 路徑錯誤！)
修改這幾個後，可以順利產生 pdf, 了，但是出來的中文卻是亂碼！
這個問題還要繼續解決！
======================
https://my.oschina.net/huqiji/blog/804899
終於在這個網頁找到答案了
中文亂碼或者空白解決方法
如果wkhtmltopdf中文顯示空白或者亂碼方框
打開windows c:\Windows\fonts\simsun.ttc拷貝到linux伺服器/usr/share/fonts/目錄下,再次生成pdf中文顯示正常
解決了！
=======================
2021/04/01今天愚人節！
SC的PDF又來搞怪了！
後來查一查，跟上面紀錄的一樣，就是那個執行檔案的權限，改為"777"就可以了！
可能是我前兩天有更新 deploy _lib檔案，而SC的預設權限為"666"，
以後記得，如果重新 deploy lib檔案，這裡要來設定權限為"777"
/_lib/prod/third/wkhtmltopdf/linux-amd64/wkhtmltopdf-amd64.exe
一、資料庫部分
二、程式部分
使用英文名稱
Comment 盡量不要使用
先處理參數正確性檢查
先處理不用寫資料庫的運算
最後要寫資料庫時，再
START TRANSACTION
COMMIT;
ROLLBACK;*/
START TRANSACTION;
/*
資料庫更新動作
*/
SET out_ret_code = "SUCCESS";
COMMIT;
END;
IF NOT EXISTS(SELECT * FROM People) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'OMG PANIC';
END IF;
DECLARE v_exit INT DEFAULT FALSE;
DECLARE cur_table CURSOR FOR
SELECT field1, field2 FROM table;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_exit = TRUE;
OPEN cur_table;
my_fetch: LOOP
FETCH cur_table
INTO v_field1, v_field2;
IF v_exit THEN
LEAVE my_fetch;
END IF;
<process code here...>
END LOOP my_fetch;
CLOSE cur_table;
從Apache log中發現這一條，很奇怪，我主機中，並沒有example.com這個網址，為何會產生這個？
奇怪哩！
紀錄一下，以後再來查
<?php
$mysqli = new mysqli("localhost","my_user","my_password","my_db");
if ($mysqli -> connect_errno) {
echo "Failed to connect to MySQL: " . $mysqli -> connect_error;
exit();
}
// Escape special characters, if any
$firstname = $mysqli -> real_escape_string($_POST['firstname']);
$lastname = $mysqli -> real_escape_string($_POST['lastname']);
$age = $mysqli -> real_escape_string($_POST['age']);
$sql="INSERT INTO Persons (FirstName, LastName, Age) VALUES ('$firstname', '$lastname', '$age')";
if (!$mysqli -> query($sql)) {
printf("%d Row inserted.\n", $mysqli->affected_rows);
}
$mysqli -> close();
?>
像下面這個例子： $lastname 裡面的值有單引號。這樣進入 SQL 就會發生錯誤，應該要避開。
就使用這個函數：mysqli_real_escape_string()，就可以把單引號等避開了！
<?php
$lastname = "D'Ore";
$sql="INSERT INTO Persons (LastName) VALUES ('$lastname')";
// This query will fail, cause we didn't escape $lastname
if (!$mysqli -> query($sql)) {
printf("%d Row inserted.\n", $mysqli->affected_rows);
}
?>
https://www.w3schools.com/php/func_mysqli_real_escape_string.asp
https://www.php.net/manual/en/mysqli.real-escape-string.php
之前做了一個 AJAX動態去讀取伺服器程式 getname.php，要獲取姓名的程式。
結果程式呼叫 JavaScript 讀取 getname.php 之後，版面大變！
後來發現，原來是我是以CodeChargeStudio來製作getname.php，沒有把 html 模板清空、清乾淨，還留有CCS原本內定的一些 html模板的文字，這些<html><head>......</head><body>.....</body></html>整個一起都傳到網頁裡面去了！所以，CSS就大亂了！
我將 getname.php 在 CCS上留存的 html模板都清空，只剩下產生{name}，就乾淨了！
犯了這個錯誤！紀錄一下。
經常會有需要將按鈕放置在資料欄位旁邊。
怎麼辦？
增加一個欄位 型態為：LABEL，把他放在你想要放的位置。
使用 "OnLoad" EVENT 來將這段 JavaScript 放到這個欄位：
{save_it} = '<a href="javascript: nm_atualiza (\'alterar\');" id="sc_b_upd_t" onclick="nm_atualiza (\'alterar\');; return false;" class="scButton_default" title="Update record" style="vertical-align: middle; display:inline-block;">Click to save</a>';
透過 JavaScript 來控制時間或是實作一個計時器，一定都會想到 setTimeout() / setInterval()
<div class="checkbox">
<label>
<input
type="checkbox"
id="Contentusdtcapply_usdt_confirmed"
value="1"
name="apple" ckecked>
{res:apply_usdt_confirmed}
</label>
</div>
HTML 前端的 Checkbox ，如果有選取，則會往端傳 Value，沒有選取的，不會往後端傳 Value
所以，在後端 php 程式，必須使用 isset($_POST["checkbox_control_name"]) 是否有傳值，來決定有沒有被選取(Check)(選取打勾)，而其值為 value
因此，在 codecharge 裡面，資料庫要新增、或修改，不可以直接要使用 checkbox control 的值來更新，而必須自己另外寫成 SQL 指令，來執行，這樣才可以。
例如：
if(isset($_POST['apply_usdt_confirmed'])) {
$t_usdtc = "1";
} else {
$t_usdtc = "0";
}
$db = new clsDBconn_mysql();
$sql = "UPDATE admin_rp_apply SET apply_usdt_confirmed = ".$t_usdtc .
" WHERE apply_id = " . $usdtc->apply_id->GetValue();
$db->query($sql);
$db->close();
每個企業，都有他的企業商業規則，Business Rules。
這些商業規則，應該要獨立的存放在 MySQL資料庫中。
設計師應該要記住：商業規則不應該放在應用程式中。
商業規則，可以被應用程式使用，但不是把他寫死在營用城市裡面。
原因：
1- 商業規則是不斷發展中的
2- 情況是會改變的，例如有了新的規則
3-這個商業規則可能會使用在不同的應用程式裡面。
負責商業規則的程式設計師，要建立一個兩層的應用程式：
1 - 商業規則層
2 - 應用程式層
使用 MySQL 資料庫系統就是一個建立商業規則層的明顯選擇。
就是因為他可以使用大部分的程式語言，而且程式設計師可以：
1 - 將商業規則資訊存放在 資料表 Table中
2 - 使用 stored function/procedure 來執行商業規則。
使用 MySQL 建立好的商業規則，可以很便利的為使用各種不同的程式語言寫得贏用程式來呼叫使用。包括在網路上的應用程式。
商業規則本身，可以使用 MySQL的函數來建立。
很明顯的，這些規則可以使用 if.. then..else 指令來完成。
例如：根據輸入的項目，而採取不同的行動，程式：
delimiter //
drop function if exists business_rule;
create function business_rule (status varchar(50)) returns varchar(50)
deterministic
begin
declare new_status varchar(50);
if status = "new" then
set new_status = "case opened - send to engineer";
elseif status = "fixed" then
set new_status = "problem solved - inform customer";
elseif status = "nofix" then
set new_status = "problem unresolved - sent to engineer";
elseif status = "raise" then
set new_status = "problem escalated - inform manager";
elseif status = "close" then
set new_status = "case closed";
else set status = "undefined";
end if;
return new_status;
end
//
delimiter ;
這個函數，可以被應用程式呼叫使用：
echo "select business_rule('new')" | mysql -uuser -ppassword business_rules_db
這樣一來，
1. 這些商業規則，可以被不同語言的應用程式使用，或任何可以使用MySQL的應用程式
2. 商業規則可以被獨立更新或修改、增加而不需要更動應用程式。
最後，程式設計師可以有一個有力且可適應修改商業規則，而與使用他們的應用程式獨立運作。
簡單翻譯自：
https://steemit.com/utopian-io/@haig/how-to-use-business-rules-in-a-mysql-database-using-mysql-functions-to-create-two-tier-business-savvy-applications
Code Charge 裡面的 SQL Builder，一般就使用 Table 就很夠用了。
但是，有時就會使用 SQL/Stored Procedure，這時，可能就會用到 SQL Parameters
使用方法是 {}+AddParameter，然後，定義裡面的內容。
令人混淆的關鍵是，要在寫得 SQL Statement 裡面，加上這個 Parameter，需要用上面指令列的"Add Parameter To SQL"那個按鍵，加到 SQL Statement裡面。
然後，令人氣結的是：如果是 Text 型態，竟然要自己加上單引號在外面？！
因為他有指定 ccsText 型態，會令人以為 跟 CCSTOSQL() 函數一樣，會自動判斷加上單引號！
試了才知道，這部分竟然沒有自動加單引號。恩~~~~
這兩種寫法，都可以判斷是否存在：
IF ((select count(*) from members where mem_id = in_mem_id ) = 0) THEN
.....
END IF;
IF not exists (SELECT * FROM members WHERE mem_id = in_mem_id) THEN
.....
END IF;
1. NULL / Empty String "" ：不一樣
mysql> INSERT INTO my_table (phone) VALUES (NULL); mysql> INSERT INTO my_table (phone) VALUES ('');
NULL 就是"甚麼都沒有"。
NULL 不論與甚麼運算，都是 NULL！
SELECT NULL, 1+NULL, CONCAT('nothing', NULL);
出來的就是 NULL；
SELECT * FROM table WHERE phone = NULL;
出來的結果是空的！
判斷是否NULL，只能使用 IS NULL , IS NOT NULL 運算元, IFNULL() 函數。
MySQL supports the SQL standard integer types INTEGER (or INT) and SMALLINT. As an extension to the standard, MySQL also supports the integer types TINYINT, MEDIUMINT, and BIGINT. The following table shows the required storage and range for each integer type.
Table 11.1 Required Storage and Range for Integer Types Supported by MySQL
| Type | Storage (Bytes) | Minimum Value Signed | Minimum Value Unsigned | Maximum Value Signed | Maximum Value Unsigned |
|---|---|---|---|---|---|
TINYINT | 1 | -128 | 0 | 127 | 255 |
SMALLINT | 2 | -32768 | 0 | 32767 | 65535 |
MEDIUMINT | 3 | -8388608 | 0 | 8388607 | 16777215 |
INT | 4 | -2147483648 | 0 | 2147483647 | 4294967295 |
BIGINT | 8 | -263 | 0 | 263-1 | 264-1 |
It means display width
Whether you use tinyint(1) or tinyint(2), it does not make any difference.
I always use tinyint(1) and int(11), I used several mysql clients (navicat, sequel pro).
It does not mean anything AT ALL! I ran a test, all above clients or even the command-line client seems to ignore this.
But, display width is most important if you are using ZEROFILL option, for example your table has following 2 columns:
A tinyint(2) zerofill
B tinyint(4) zerofill
For integer data types, M indicates the maximum display width. The maximum display width is 255. Display width is unrelated to the range of values a type can store, as described in Section 11.1.6, “Numeric Type Attributes”.
For floating-point and fixed-point data types, M is the total number of digits that can be stored.
As of MySQL 8.0.17, the display width attribute is deprecated for integer data types; you should expect support for it to be removed in a future version of MySQL.
If you specify ZEROFILL for a numeric column, MySQL automatically adds the UNSIGNED attribute to the column.
As of MySQL 8.0.17, the ZEROFILL attribute is deprecated for numeric data types; you should expect support for it to be removed in a future version of MySQL. Consider using an alternative means of producing the effect of this attribute. For example, applications could use the LPAD() function to zero-pad numbers up to the desired width, or they could store the formatted numbers in CHAR columns.
Numeric data types that permit the UNSIGNED attribute also permit SIGNED. However, these data types are signed by default, so the SIGNED attribute has no effect.
As of MySQL 8.0.17, the UNSIGNED attribute is deprecated for columns of type FLOAT, DOUBLE, and DECIMAL (and any synonyms); you should expect support for it to be removed in a future version of MySQL. Consider using a simple CHECK constraint instead for such columns.
SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.
SERIAL DEFAULT VALUE in the definition of an integer column is an alias for NOT NULL AUTO_INCREMENT UNIQUE.
When you use subtraction between integer values where one is of type UNSIGNED, the result is unsigned unless the NO_UNSIGNED_SUBTRACTION SQL mode is enabled. See Section 12.11, “Cast Functions and Operators”.
A bit-value type. M indicates the number of bits per value, from 1 to 64. The default is 1 if M is omitted.
TINYINT[(M)] [UNSIGNED] [ZEROFILL]
A very small integer. The signed range is -128 to 127. The unsigned range is 0 to 255.
These types are synonyms for TINYINT(1). A value of zero is considered false. Nonzero values are considered true:
mysql> SELECT IF(0, 'true', 'false');
+------------------------+
| IF(0, 'true', 'false') |
+------------------------+
| false |
+------------------------+
mysql> SELECT IF(1, 'true', 'false');
+------------------------+
| IF(1, 'true', 'false') |
+------------------------+
| true |
+------------------------+
mysql> SELECT IF(2, 'true', 'false');
+------------------------+
| IF(2, 'true', 'false') |
+------------------------+
| true |
+------------------------+However, the values TRUE and FALSE are merely aliases for 1 and 0, respectively, as shown here:
mysql> SELECT IF(0 = FALSE, 'true', 'false');
+--------------------------------+
| IF(0 = FALSE, 'true', 'false') |
+--------------------------------+
| true |
+--------------------------------+
mysql> SELECT IF(1 = TRUE, 'true', 'false');
+-------------------------------+
| IF(1 = TRUE, 'true', 'false') |
+-------------------------------+
| true |
+-------------------------------+
mysql> SELECT IF(2 = TRUE, 'true', 'false');
+-------------------------------+
| IF(2 = TRUE, 'true', 'false') |
+-------------------------------+
| false |
+-------------------------------+
mysql> SELECT IF(2 = FALSE, 'true', 'false');
+--------------------------------+
| IF(2 = FALSE, 'true', 'false') |
+--------------------------------+
| false |
+--------------------------------+The last two statements display the results shown because 2 is equal to neither 1 nor 0.
SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
A small integer. The signed range is -32768 to 32767. The unsigned range is 0 to 65535.
MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]
A medium-sized integer. The signed range is -8388608 to 8388607. The unsigned range is 0 to 16777215.
INT[(M)] [UNSIGNED] [ZEROFILL]
A normal-size integer. The signed range is -2147483648 to 2147483647. The unsigned range is 0 to 4294967295.
INTEGER[(M)] [UNSIGNED] [ZEROFILL]
This type is a synonym for INT.
BIGINT[(M)] [UNSIGNED] [ZEROFILL]
A large integer. The signed range is -9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615.
SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.
Some things you should be aware of with respect to BIGINT columns:
All arithmetic is done using signed BIGINT or DOUBLE values, so you should not use unsigned big integers larger than 9223372036854775807 (63 bits) except with bit functions! If you do that, some of the last digits in the result may be wrong because of rounding errors when converting a BIGINT value to a DOUBLE.
MySQL can handle BIGINT in the following cases:
When using integers to store large unsigned values in a BIGINT column.
In MIN( or col_name)MAX(, where col_name)col_name refers to a BIGINT column.
When using operators (+, -, *, and so on) where both operands are integers.
You can always store an exact integer value in a BIGINT column by storing it using a string. In this case, MySQL performs a string-to-number conversion that involves no intermediate double-precision representation.
The -, +, and * operators use BIGINT arithmetic when both operands are integer values. This means that if you multiply two big integers (or results from functions that return integers), you may get unexpected results when the result is larger than 9223372036854775807.
DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]
A packed “exact” fixed-point number. M is the total number of digits (the precision) and D is the number of digits after the decimal point (the scale). The decimal point and (for negative numbers) the - sign are not counted in M. If D is 0, values have no decimal point or fractional part. The maximum number of digits (M) for DECIMAL is 65. The maximum number of supported decimals (D) is 30. If D is omitted, the default is 0. If M is omitted, the default is 10. (There is also a limit on how long the text of DECIMAL literals can be; see Section 12.25.3, “Expression Handling”.)
UNSIGNED, if specified, disallows negative values. As of MySQL 8.0.17, the UNSIGNED attribute is deprecated for columns of type DECIMAL (and any synonyms); you should expect support for it to be removed in a future version of MySQL. Consider using a simple CHECK constraint instead for such columns.
All basic calculations (+, -, *, /) with DECIMAL columns are done with a precision of 65 digits.
DEC[(, M[,D])] [UNSIGNED] [ZEROFILL]NUMERIC[(, M[,D])] [UNSIGNED] [ZEROFILL]FIXED[(M[,D])] [UNSIGNED] [ZEROFILL]
These types are synonyms for DECIMAL. The FIXED synonym is available for compatibility with other database systems.
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
A small (single-precision) floating-point number. Permissible values are -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38. These are the theoretical limits, based on the IEEE standard. The actual range might be slightly smaller depending on your hardware or operating system.
M is the total number of digits and D is the number of digits following the decimal point. If M and D are omitted, values are stored to the limits permitted by the hardware. A single-precision floating-point number is accurate to approximately 7 decimal places.
FLOAT( is a nonstandard MySQL extension. As of MySQL 8.0.17, this syntax is deprecated, and you should expect support for it to be removed in a future version of MySQL.M,D)
UNSIGNED, if specified, disallows negative values. As of MySQL 8.0.17, the UNSIGNED attribute is deprecated for columns of type FLOAT (and any synonyms) and you should expect support for it to be removed in a future version of MySQL. Consider using a simple CHECK constraint instead for such columns.
Using FLOAT might give you some unexpected problems because all calculations in MySQL are done with double precision. See Section B.3.4.7, “Solving Problems with No Matching Rows”.
FLOAT(p) [UNSIGNED] [ZEROFILL]
A floating-point number. p represents the precision in bits, but MySQL uses this value only to determine whether to use FLOAT or DOUBLE for the resulting data type. If p is from 0 to 24, the data type becomes FLOAT with no M or D values. If p is from 25 to 53, the data type becomes DOUBLE with no M or D values. The range of the resulting column is the same as for the single-precision FLOAT or double-precision DOUBLE data types described earlier in this section.
UNSIGNED, if specified, disallows negative values. As of MySQL 8.0.17, the UNSIGNED attribute is deprecated for columns of type FLOAT (and any synonyms) and you should expect support for it to be removed in a future version of MySQL. Consider using a simple CHECK constraint instead for such columns.
FLOAT( syntax is provided for ODBC compatibility.p)
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
A normal-size (double-precision) floating-point number. Permissible values are -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308. These are the theoretical limits, based on the IEEE standard. The actual range might be slightly smaller depending on your hardware or operating system.
M is the total number of digits and D is the number of digits following the decimal point. If M and D are omitted, values are stored to the limits permitted by the hardware. A double-precision floating-point number is accurate to approximately 15 decimal places.
DOUBLE( is a nonstandard MySQL extension. As of MySQL 8.0.17, this syntax is deprecated and you should expect support for it to be removed in a future version of MySQL.M,D)
UNSIGNED, if specified, disallows negative values. As of MySQL 8.0.17, the UNSIGNED attribute is deprecated for columns of type DOUBLE (and any synonyms) and you should expect support for it to be removed in a future version of MySQL. Consider using a simple CHECK constraint instead for such columns.
DOUBLE PRECISION[(, M,D)] [UNSIGNED] [ZEROFILL]REAL[(M,D)] [UNSIGNED] [ZEROFILL]
These types are synonyms for DOUBLE. Exception: If the REAL_AS_FLOAT SQL mode is enabled, REAL is a synonym for FLOAT rather than DOUBLE.
hhhe