2020年11月19日 星期四

希望放置 SAVE 按鈕在表單的任何地方,而不是上面下面

 經常會有需要將按鈕放置在資料欄位旁邊。

怎麼辦?


增加一個欄位 型態為: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

 透過 JavaScript 來控制時間或是實作一個計時器,一定都會想到 setTimeout() / setInterval()

2020年11月9日 星期一

CodeCharge 裡面的 CheckBox Control 不能直接用來更新資料庫,Radio也是

 


<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();

 

2020年11月8日 星期日

關於商業規則 Business Rules

每個企業,都有他的企業商業規則,Business Rules。

這些商業規則,應該要獨立的存放在 MySQL資料庫中。

設計師應該要記住:商業規則不應該放在應用程式中。

商業規則,可以被應用程式使用,但不是把他寫死在營用城市裡面。

原因:

1- 商業規則是不斷發展中的

2- 情況是會改變的,例如有了新的規則

3-這個商業規則可能會使用在不同的應用程式裡面。


MySQL 和 商業規則

負責商業規則的程式設計師,要建立一個兩層的應用程式:

1 - 商業規則層

2 - 應用程式層

使用 MySQL 資料庫系統就是一個建立商業規則層的明顯選擇。

就是因為他可以使用大部分的程式語言,而且程式設計師可以:

1 - 將商業規則資訊存放在 資料表 Table中

2 - 使用 stored function/procedure 來執行商業規則。

使用 MySQL 建立好的商業規則,可以很便利的為使用各種不同的程式語言寫得贏用程式來呼叫使用。包括在網路上的應用程式。

建立簡單的 MySQL Stored Function 商業規則

商業規則本身,可以使用 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



CodeCharge 的 SQL Builder 裡面的 SQL Parameter 使用方法

 Code Charge 裡面的 SQL Builder,一般就使用 Table 就很夠用了。

但是,有時就會使用 SQL/Stored Procedure,這時,可能就會用到 SQL Parameters


使用方法是 {}+AddParameter,然後,定義裡面的內容。

令人混淆的關鍵是,要在寫得 SQL Statement 裡面,加上這個 Parameter,需要用上面指令列的"Add Parameter To SQL"那個按鍵,加到  SQL Statement裡面。

然後,令人氣結的是:如果是  Text 型態,竟然要自己加上單引號在外面?!

因為他有指定 ccsText 型態,會令人以為 跟 CCSTOSQL() 函數一樣,會自動判斷加上單引號!

試了才知道,這部分竟然沒有自動加單引號。恩~~~~

MySQL 判斷是否存在的兩種寫法

這兩種寫法,都可以判斷是否存在:

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;


2020年11月3日 星期二

MySQL 的 Transaction Commit/Rollback

 

  • 不能在  Function/Trigger 裡面指定使用 Commit/Rollback
  • 使用 stored procedure 裡面要處理 error handling, rollback等問題


  • SET autocommit=1; 
    • 這是一個 SESSION Variable
    • 如果 有 明顯的:BEGIN...END/ START TRANSACTION  COMMIT/ROLLBACK,則在這個區間則是 會自動暫時設 SET AUTOCOMMIT=0,執行完畢 COMMIT / ROLLBACK 後就自動回復 on!
    • 配合 ERROR HANDLER 做 TRANSACTION 控制。

  • SAMPLE PROCEDURE:
proc_label: BEGIN

-- 輸入參數
-- 輸出參數: OUT out_ret_code VARCHAR(200);

DECLARE v_day_amount_limit INT(11);
DECLARE v_today_in_amount_accu INT(11);

DECLARE EXIT handler for sqlexception
BEGIN
GET DIAGNOSTICS CONDITION 1
@p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;

ROLLBACK;
-- SET out_ret_code = "ERROR:sqlexception";
SET out_ret_code = (SELECT concat("RETURNED_SQLSTATE:",@p1 ," MESSAGE_TEXT:" ,@p2 ));
CALL system_log(CONCAT("sqlexception:",out_ret_code));
END;

DECLARE EXIT handler for sqlwarning
BEGIN
GET DIAGNOSTICS CONDITION 1
@p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
ROLLBACK;
SET out_ret_code = (SELECT concat("RETURNED_SQLSTATE:",@p1 ," MESSAGE_TEXT:" ,@p2 ));
CALL system_log(CONCAT("sqlwarning:",out_ret_code));
END;

-- 檢查輸入參數的程式碼。所有可能傳進來錯誤的參數情形,先做檢查、排除、紀錄,產生錯誤代碼、記錄到LOG裡面、停止程式、傳回錯誤。
-- 最後,才做 Transaction 寫入資料庫。
-- 也就是Transaction 裡面,只要有純粹的資料庫寫入更新的動作就好。
-- 如果萬一有資料庫寫入更新本身的錯誤,才產生 Rollback,沒有錯誤就直接 Commit


START TRANSACTION;

-- Program.....

COMMIT;

SET out_ret_code="SUCCESS";

END

MySQL 的資料型態的一些基本問題

 

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 數值型態的大小、相關問題

Integer Types (Exact Value)

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

TypeStorage (Bytes)Minimum Value SignedMinimum Value UnsignedMaximum Value SignedMaximum Value Unsigned
TINYINT1-1280127255
SMALLINT2-3276803276765535
MEDIUMINT3-83886080838860716777215
INT4-2147483648021474836474294967295
BIGINT8-2630263-1264-1

MySql: Tinyint (2) vs tinyint(1) - what is the difference?

https://stackoverflow.com/questions/12839927/mysql-tinyint-2-vs-tinyint1-what-is-the-difference

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

Numeric Data Type Syntax

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 FLOATDOUBLE, 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.

Warning

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”.

  • BIT[(M)]

    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.

  • BOOLBOOLEAN

    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(col_name) or MAX(col_name), where 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-380, 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(M,D) 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.

    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(p) syntax is provided for ODBC compatibility.

  • DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

    A normal-size (double-precision) floating-point number. Permissible values are -1.7976931348623157E+308 to -2.2250738585072014E-3080, 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(M,D) 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.

    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

如何判斷現在FORM是在 insert mode? 還是 update mode?

只要用  if (empty({primary_key})) 就可以知道是否為新增模式了。 如果 {promary_key} 是空白的,那麼就是在新增模式;反之,就是更新模式。 以上。