Winapi.ShellAPI.ShellExecute

uses
  ShellAPI;

function ShellExecute(hWnd: HWND; Operation, FileName, Parameters,  Directory: LPWSTR; ShowCmd: Integer): HINST; stdcall;


以下轉貼至:
https://msdn.microsoft.com/en-us/library/windows/desktop/bb762153(v=vs.85).aspx

Performs an operation on a specified file.

Syntax

C++

HINSTANCE ShellExecute(

  _In_opt_ HWND    hwnd,

  _In_opt_ LPCTSTR lpOperation,

  _In_     LPCTSTR lpFile,

  _In_opt_ LPCTSTR lpParameters,

  _In_opt_ LPCTSTR lpDirectory,

  _In_     INT     nShowCmd

);

Parameters

hwnd [in, optional]

Type: HWND

A handle to the parent window used for displaying a UI or error messages. This value can be NULL if the operation is not associated with a window.

lpOperation [in, optional]

Type: LPCTSTR

A pointer to a null-terminated string, referred to in this case as a verb, that specifies the action to be performed. The set of available verbs depends on the particular file or folder. Generally, the actions available from an object's shortcut menu are available verbs. The following verbs are commonly used:

edit

Launches an editor and opens the document for editing. If lpFile is not a document file, the function will fail.

explore

Explores a folder specified by lpFile.

find

Initiates a search beginning in the directory specified by lpDirectory.

open

Opens the item specified by the lpFile parameter. The item can be a file or folder.

print

Prints the file specified by lpFile. If lpFile is not a document file, the function fails.

NULL

The default verb is used, if available. If not, the "open" verb is used. If neither verb is available, the system uses the first verb listed in the registry.

lpFile [in]

Type: LPCTSTR

A pointer to a null-terminated string that specifies the file or object on which to execute the specified verb. To specify a Shell namespace object, pass the fully qualified parse name. Note that not all verbs are supported on all objects. For example, not all document types support the "print" verb. If a relative path is used for the lpDirectory parameter do not use a relative path for lpFile.

lpParameters [in, optional]

Type: LPCTSTR

If lpFile specifies an executable file, this parameter is a pointer to a null-terminated string that specifies the parameters to be passed to the application. The format of this string is determined by the verb that is to be invoked. If lpFile specifies a document file, lpParameters should be NULL.

lpDirectory [in, optional]

Type: LPCTSTR

A pointer to a null-terminated string that specifies the default (working) directory for the action. If this value is NULL, the current working directory is used. If a relative path is provided at lpFile, do not use a relative path for lpDirectory.

nShowCmd [in]

Type: INT

The flags that specify how an application is to be displayed when it is opened. If lpFile specifies a document file, the flag is simply passed to the associated application. It is up to the application to decide how to handle it. These values are defined in Winuser.h.

SW_HIDE (0)

Hides the window and activates another window.

SW_MAXIMIZE (3)

Maximizes the specified window.

SW_MINIMIZE (6)

Minimizes the specified window and activates the next top-level window in the z-order.

SW_RESTORE (9)

Activates and displays the window. If the window is minimized or maximized, Windows restores it to its original size and position. An application should specify this flag when restoring a minimized window.

SW_SHOW (5)

Activates the window and displays it in its current size and position.

SW_SHOWDEFAULT (10)

Sets the show state based on the SW_ flag specified in the STARTUPINFO structure passed to the CreateProcess function by the program that started the application. An application should call ShowWindow with this flag to set the initial show state of its main window.

SW_SHOWMAXIMIZED (3)

Activates the window and displays it as a maximized window.

SW_SHOWMINIMIZED (2)

Activates the window and displays it as a minimized window.

SW_SHOWMINNOACTIVE (7)

Displays the window as a minimized window. The active window remains active.

SW_SHOWNA (8)

Displays the window in its current state. The active window remains active.

SW_SHOWNOACTIVATE (4)

Displays a window in its most recent size and position. The active window remains active.

SW_SHOWNORMAL (1)

Activates and displays a window. If the window is minimized or maximized, Windows restores it to its original size and position. An application should specify this flag when displaying the window for the first time.

Return value

Type: HINSTANCE

If the function succeeds, it returns a value greater than 32. If the function fails, it returns an error value that indicates the cause of the failure. The return value is cast as an HINSTANCE for backward compatibility with 16-bit Windows applications. It is not a true HINSTANCE, however. It can be cast only to an int and compared to either 32 or the following error codes below.

Return code	Description
0	The operating system is out of memory or resources.
ERROR_FILE_NOT_FOUND	The specified file was not found.
ERROR_PATH_NOT_FOUND	The specified path was not found.
ERROR_BAD_FORMAT	The .exe file is invalid (non-Win32 .exe or error in .exe image).
SE_ERR_ACCESSDENIED	The operating system denied access to the specified file.
SE_ERR_ASSOCINCOMPLETE	The file name association is incomplete or invalid.
SE_ERR_DDEBUSY	The DDE transaction could not be completed because other DDE transactions were being processed.
SE_ERR_DDEFAIL	The DDE transaction failed.
SE_ERR_DDETIMEOUT	The DDE transaction could not be completed because the request timed out.
SE_ERR_DLLNOTFOUND	The specified DLL was not found.
SE_ERR_FNF	The specified file was not found.
SE_ERR_NOASSOC	There is no application associated with the given file name extension. This error will also be returned if you attempt to print a file that is not printable.
SE_ERR_OOM	There was not enough memory to complete the operation.
SE_ERR_PNF	The specified path was not found.
SE_ERR_SHARE	A sharing violation occurred.

SysUtils.StringReplace

System.SysUtils.StringReplace

function StringReplace(const Source, OldPattern, NewPattern: string; Flags: TReplaceFlags): string;

TReplaceFlags

  rfReplaceAll  //取代全部符合的字串

  rfIgnoreCase //不區分大小寫

Ex:

  StringReplace(String, 'OldValue', 'NewValue', [rfReplaceAll, rfIgnoreCase]);

TDataset.Locate

Data.DB.TDataSet.Locate

function Locate(const KeyFields: string;  const KeyValues: Variant;  Options: TLocateOptions): Boolean; virtual;

TLocateOptions 

  loCaseInsensitive  //不區分大小寫

  loPartialKey          //模糊比對

Ex:

  Locate('Field1;Field2', VarArrayOf(['Value1', 'Value2']), [loCaseInsensitive, loPartialKey]);

路徑自動加分隔符號/倒斜線

System.SysUtils

//
function IncludeTrailingBackslash(const S: string): string;
begin
  Result := IncludeTrailingPathDelimiter(S);
end;

//
function IncludeTrailingPathDelimiter(const S: string): string;
begin
  Result := S;
  if not IsPathDelimiter(Result, High(Result)) then
    Result := Result + PathDelim;
end;

取得系統語系 & 使用者自訂語系

type
  TUserDefaultLang = (langEnglish, langChinese_Simp, langChinese_Trad);

function fn_GetUserDefaultLangID:TUserDefaultLang;
begin
  //取得使用者自訂的系統語系 GetUserDefaultLangID
 //取得系統語系 GetSystemDefaultLangID
  case GetUserDefaultLangID of
    1028: Result := langChinese_Trad;    //1028 繁體中文
    1033: Result := langEnglish;                //1033 英文
    2052: Result := langChinese_Simp;  //2052 簡體中文
  end;
end;

QuickReport 得知Prepare 或 Print狀態

//Preview

QuickRep1.QRPrinter.ShowingPreview

//Printing
QuickRep1.QRPrinter.aPrinterSettings.Printer.Printing

清空 TDateTimePicker 日期值的處理方式

procedure TForm1.DateTimePicker1KeyDown(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
  if Key=VK_DELETE then
    DateTimePicker1.Format:=' ';
end;

procedure TForm1.DateTimePicker1Change(Sender: TObject);
begin
  DateTimePicker1.Format:=ShortDateFormat;
end;


在取用TDateTimePicker時，需檢查Format決定是空值還是日期值

关于Delphi2007 Remote Data Module 模块无法注册的问题的解决

第一次用 Delphi 2007　编写远程数据模块，发现程序运行没有自动注册。后来找了一些资料，解决了。

在 TRemoteDataModule 单元中添加以下代码即可。

initialization

TComponentFactory.Create(ComServer, TXOtecDbRemoteServer,

      Class_XOtecDbRemoteServer, ciMultiInstance, tmApartment);

    ComServer.UpdateRegistry(True); //添加这一句，可实现注册 

轉貼:http://www.voidcn.com/article/p-oualhgog-sm.html

SQL Server - 取英文月份名稱

set language  'us_english'

select  convert(char, getdate(), 107)

列出所有 Table 的資料結構.

/* 列出所有 Table 的資料結構.sql */
select C.name as TableName,
case A.status
   when '16' then 'PK'
   else ''
end as [Key], A.name as FieldName, '' as [Desc], D.name as type, A.length,
case A.isnullable
   when '1' then 'V'
   else ''
end as isnullable, isnull(B.text, '') as [Default],
case C.type
  when 'U' then 'Table'
  when 'V' then 'View'
  else ''
end as TableOrView
from syscolumns as A, syscomments as B, systypes as D, sysobjects as C
 where A.cdefault = B.id and A.xtype = D.xtype and D.status <> '1'  -- 排除sysname 和 nvarchar 同 xtype = 231
 and A.id = C.id
 and (C.type = 'U' or C.type = 'V')
order by C.name, A.colid

轉貼至 http://tw.myblog.yahoo.com/jw!nowr4YGVQE4pAgs1INa7JnTZmg--/article?mid=509

將多筆資料的特定欄位依分隔符號組成字串

select STUFF(
  (select  ',' +field1
   from table1
   where ..................
   FOR XML PATH('')
  ), 1, 1, '') aa

參考至 http://www.dotblogs.com.tw/terrychuang/archive/2011/04/16/22867.aspx

TSQL 解析JSON-fn_ParseJSON (轉貼)

Create FUNCTION [dbo].[fn_ParseJSON]( @json nvarchar(max) ) 
RETURNS @hierarchy table 
( 
  element_id int IDENTITY(1, 1) NOT NULL, /* internal surrogate primary key gives the order of parsing and the list order */ 
  parent_id int NOT NULL, /* [0 -- Root] if the element has a parent then it is in this column. The document is the ultimate parent, so you can get the structure from recursing from the document */ 
  object_id int NOT NULL, /* [0 -- Not an object] each list or object has an object id. This ties all elements to a parent. Lists are treated as objects here */ 
  name nvarchar(2000), /* the name of the object */ 
  stringvalue nvarchar(4000) NOT NULL, /*the string representation of the value of the element. */ 
  valuetype nvarchar(100) NOT NULL, /* the declared type of the value represented as a string in stringvalue*/ 
  bigintvalue bigint 
) 
 
AS 
 
BEGIN 
    --20140703 Peter Copy
    --select * from dbo.fn_ParseJSON('{"a":"1", "b":"2"}')

    DECLARE 
        @firstobject int, --the index of the first open bracket found in the JSON string 
        @opendelimiter int, --the index of the next open bracket found in the JSON string 
        @nextopendelimiter int,--the index of subsequent open bracket found in the JSON string 
        @nextclosedelimiter int,--the index of subsequent close bracket found in the JSON string 
        @type nvarchar(10),--whether it denotes an object or an array 
        @nextclosedelimiterChar CHAR(1),--either a '}' or a ']' 
        @contents nvarchar(MAX), --the unparsed contents of the bracketed expression 
        @start int, --index of the start of the token that you are parsing 
        @end int,--index of the end of the token that you are parsing 
        @param int,--the parameter at the end of the next Object/Array token 
        @endofname int,--the index of the start of the parameter at end of Object/Array token 
        @token nvarchar(4000),--either a string or object 
        @value nvarchar(MAX), -- the value as a string 
        @name nvarchar(200), --the name as a string 
        @parent_id int,--the next parent ID to allocate 
        @lenjson int,--the current length of the JSON String 
        @characters NCHAR(62),--used to convert hex to decimal 
        @result BIGINT,--the value of the hex symbol being parsed 
        @index SMALLINT,--used for parsing the hex value 
        @escape int; --the index of the next escape character 
     
    /* in this temporary table we keep all strings, even the names of the elements, since they are 'escaped' 
     * in a different way, and may contain, unescaped, brackets denoting objects or lists. These are replaced in 
     * the JSON string by tokens representing the string 
     */ 
    DECLARE @strings table 
    ( 
        string_id int IDENTITY(1, 1), 
        stringvalue nvarchar(MAX) 
    ) 
     
    /* initialise the characters to convert hex to ascii */ 
    SET @characters = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'; 
    SET @parent_id = 0; 
     
    /* firstly we process all strings. This is done because [{} and ] aren't escaped in strings, which complicates an iterative parse. */ 
    WHILE 1 = 1 /* forever until there is nothing more to do */ 
    BEGIN 
        SET @start = PATINDEX('%[^a-zA-Z]["]%', @json collate SQL_Latin1_General_CP850_Bin); /* next delimited string */ 
         
        IF @start = 0 BREAK; /*no more so drop through the WHILE loop */ 
         
        IF SUBSTRING(@json, @start+1, 1) = '"' 
        BEGIN  /* Delimited name */ 
            SET @start = @start+1; 
            SET @end = PATINDEX('%[^\]["]%', RIGHT(@json, LEN(@json+'|')-@start) collate SQL_Latin1_General_CP850_Bin); 
        END 
         
        IF @end = 0 /*no end delimiter to last string*/ 
            BREAK; /* no more */ 
         
        SELECT @token = SUBSTRING(@json, @start+1, @end-1) 
         
        /* now put in the escaped control characters */ 
        SELECT @token = REPLACE(@token, from_string, to_string) 
        FROM 
        ( 
            SELECT '\"' AS from_string, '"' AS to_string 
            UNION ALL 
            SELECT '\\', '\' 
            UNION ALL 
            SELECT '\/', '/' 
            UNION ALL 
            SELECT '\b', CHAR(08) 
            UNION ALL 
            SELECT '\f', CHAR(12) 
            UNION ALL 
            SELECT '\n', CHAR(10) 
            UNION ALL 
            SELECT '\r', CHAR(13) 
            UNION ALL 
            SELECT '\t', CHAR(09) 
        ) substitutions; 
         
        SET @result = 0; 
        SET @escape = 1; 
         
        /*Begin to take out any hex escape codes*/ 
        WHILE @escape > 0 
        BEGIN 
         
            /* find the next hex escape sequence */ 
            SET @index = 0; 
            SET @escape = PATINDEX('%\x[0-9a-f][0-9a-f][0-9a-f][0-9a-f]%', @token collate SQL_Latin1_General_CP850_Bin); 
             
            IF @escape > 0 /* if there is one */ 
            BEGIN 
                 
                WHILE @index < 4 /* there are always four digits to a \x sequence  */ 
                BEGIN 
                    /* determine its value */ 
                    SET @result = @result + POWER(16, @index) * (CHARINDEX(SUBSTRING(@token, @escape + 2 + 3 - @index, 1), @characters) - 1); 
                    SET @index = @index + 1; 
                END 
                 
                /* and replace the hex sequence by its unicode value */ 
                SET @token = STUFF(@token, @escape, 6, NCHAR(@result)); 
            END 
             
        END 
         
        /* now store the string away */ 
        INSERT INTO @strings (stringvalue) SELECT @token; 
         
        /* and replace the string with a token */ 
        SET @json = STUFF(@json, @start, @end + 1, '@string' + CONVERT(nvarchar(5), @@identity)); 
         
    END 
     
    /* all strings are now removed. Now we find the first leaf. */ 
    WHILE 1 = 1  /* forever until there is nothing more to do */ 
    BEGIN 
     
        SET @parent_id = @parent_id + 1; 
         
        /* find the first object or list by looking for the open bracket */ 
        SET @firstobject = PATINDEX('%[{[[]%', @json collate SQL_Latin1_General_CP850_Bin);  /*object or array*/ 
         
        IF @firstobject = 0 BREAK; 
         
        IF (SUBSTRING(@json, @firstobject, 1) = '{') 
            SELECT @nextclosedelimiterChar = '}', @type = 'object'; 
        ELSE 
            SELECT @nextclosedelimiterChar = ']', @type = 'array'; 
         
         
        SET @opendelimiter = @firstobject; 
         
        WHILE 1 = 1 --find the innermost object or list... 
        BEGIN 
            SET @lenjson = LEN(@json+'|') - 1; 
             
            /* find the matching close-delimiter proceeding after the open-delimiter */ 
            SET @nextclosedelimiter = CHARINDEX(@nextclosedelimiterChar, @json, @opendelimiter + 1); 
             
            /* is there an intervening open-delimiter of either type */ 
            SET @nextopendelimiter = PATINDEX('%[{[[]%',RIGHT(@json, @lenjson-@opendelimiter) collate SQL_Latin1_General_CP850_Bin); /*object*/ 
             
            IF @nextopendelimiter = 0 BREAK; 
             
            SET @nextopendelimiter = @nextopendelimiter + @opendelimiter; 
             
            IF @nextclosedelimiter < @nextopendelimiter BREAK; 
             
            IF SUBSTRING(@json, @nextopendelimiter, 1) = '{' 
                SELECT @nextclosedelimiterChar = '}', @type = 'object'; 
            ELSE 
                SELECT @nextclosedelimiterChar = ']', @type = 'array'; 
             
            SET @opendelimiter = @nextopendelimiter; 
        END 
         
        /* and parse out the list or name/value pairs */ 
        SET @contents = SUBSTRING(@json, @opendelimiter+1, @nextclosedelimiter-@opendelimiter - 1); 
         
        SET @json = STUFF(@json, @opendelimiter, @nextclosedelimiter - @opendelimiter + 1, '@' + @type + CONVERT(nvarchar(5), @parent_id)); 
         
        WHILE (PATINDEX('%[A-Za-z0-9@+.e]%', @contents collate SQL_Latin1_General_CP850_Bin)) <  > 0 
        BEGIN /* WHILE PATINDEX */ 
         
            IF @type = 'object' /*it will be a 0-n list containing a string followed by a string, number,boolean, or null*/ 
            BEGIN 
                 
                SET @end = CHARINDEX(':', ' '+@contents); /*if there is anything, it will be a string-based name.*/ 
                SET @start = PATINDEX('%[^A-Za-z@][@]%', ' ' + @contents collate SQL_Latin1_General_CP850_Bin); /*AAAAAAAA*/ 
                 
                SET @token = SUBSTRING(' '+@contents, @start + 1, @end - @start - 1); 
                SET @endofname = PATINDEX('%[0-9]%', @token collate SQL_Latin1_General_CP850_Bin); 
                SET @param = RIGHT(@token, LEN(@token)-@endofname + 1); 
                 
                SET @token = LEFT(@token, @endofname - 1); 
                SET @contents = RIGHT(' ' + @contents, LEN(' ' + @contents + '|') - @end - 1); 
                 
                SELECT @name = stringvalue FROM @strings WHERE string_id = @param; /*fetch the name*/ 
                 
            END 
            ELSE 
            BEGIN 
                SET @name = null; 
            END 
             
            SET @end = CHARINDEX(',', @contents);  /*a string-token, object-token, list-token, number,boolean, or null*/ 
             
            IF @end = 0 
                SET @end = PATINDEX('%[A-Za-z0-9@+.e][^A-Za-z0-9@+.e]%', @contents+' ' collate SQL_Latin1_General_CP850_Bin) + 1; 
             
            SET @start = PATINDEX('%[^A-Za-z0-9@+.e][A-Za-z0-9@+.e]%', ' ' + @contents collate SQL_Latin1_General_CP850_Bin); 
             
            /*select @start,@end, LEN(@contents+'|'), @contents */ 
             
            SET @value = RTRIM(SUBSTRING(@contents, @start, @end-@start)); 
            SET @contents = RIGHT(@contents + ' ', LEN(@contents+'|') - @end); 
             
            IF SUBSTRING(@value, 1, 7) = '@object' 
                INSERT INTO @hierarchy (name, parent_id, stringvalue, object_id, valuetype) 
                SELECT @name, @parent_id, SUBSTRING(@value, 8, 5), SUBSTRING(@value, 8, 5), 'object'; 
             
            ELSE 
                IF SUBSTRING(@value, 1, 6) = '@array' 
                    INSERT INTO @hierarchy (name, parent_id, stringvalue, object_id, valuetype) 
                    SELECT @name, @parent_id, SUBSTRING(@value, 7, 5), SUBSTRING(@value, 7, 5), 'array'; 
                ELSE 
                    IF SUBSTRING(@value, 1, 7) = '@string' 
                        INSERT INTO @hierarchy (name, parent_id, stringvalue, valuetype, object_id) 
                        SELECT @name, @parent_id, stringvalue, 'string', 0 
                        FROM @strings 
                        WHERE string_id = SUBSTRING(@value, 8, 5); 
                    ELSE 
                        IF @value IN ('true', 'false') 
                            INSERT INTO @hierarchy (name, parent_id, stringvalue, valuetype, object_id) 
                            SELECT @name, @parent_id, @value, 'boolean', 0; 
                        ELSE 
                            IF @value = 'null' 
                                INSERT INTO @hierarchy (name, parent_id, stringvalue, valuetype, object_id) 
                                SELECT @name, @parent_id, @value, 'null', 0; 
                            ELSE 
                                IF PATINDEX('%[^0-9]%', @value collate SQL_Latin1_General_CP850_Bin) > 0 
                                    INSERT INTO @hierarchy (name, parent_id, stringvalue, valuetype, object_id) 
                                    SELECT @name, @parent_id, @value, 'real', 0; 
                                ELSE 
                                    INSERT INTO @hierarchy (name, parent_id, stringvalue, valuetype, object_id, bigintvalue) 
                                    SELECT @name, @parent_id, @value, 'bigint', 0, dbo.TryConvertBigInt(@value); 
                                 
        END /* WHILE PATINDEX */ 
     
    END /* WHILE 1=1 forever until there is nothing more to do */ 
     
    INSERT INTO @hierarchy (name, parent_id, stringvalue, object_id, valuetype) 
    SELECT '', 0, '', @parent_id - 1, @type; 
     
    RETURN; 
 
END 

TSQL 小數轉分數-fn_DftOf

Create [dbo].[fn_DftOf](@JSON nvarchar(2000))
RETURNS varchar(100)
as 
begin
  -----------------
  --小數轉分數-----
  -----------------
  declare
    @type varchar(50)='',
    @value varchar(50)='',
    @num1 varchar(50)='',
    @num2 varchar(50)=''
 
  declare
    @sStr    varchar(50),
    @iValue  decimal(12,6),
    @sResult varchar(50),
    @sFh     varchar(20)='', --負數符號
    @sZs     varchar(20),    --整數 
    @sXs     varchar(20),    --小數 
    @iPos    int,            --小數點位置 
    @iFm     int,            --分母 
    @iFz     int,            --分子 
    @iZdgys  int             --最大公約數
   
  declare
    @iYs int, --餘數
    @iNum1 int,
    @iNum2 int 
   
  select @type=stringvalue from dbo.fn_parsejson(@JSON) where name='type' 
  select @value=stringvalue from dbo.fn_parsejson(@JSON) where name='value'
  select @num1=stringvalue from dbo.fn_parsejson(@JSON) where name='num1'
  select @num2=stringvalue from dbo.fn_parsejson(@JSON) where name='num2'
 
  ------------------------------------------------------------------------------------------------------------------------
  ------------------------------------------------------------------------------------------------------------------------
  --小數轉分數
  ------------------------------------------------------------------------------------------------------------------------
  ------------------------------------------------------------------------------------------------------------------------
  if @type=''
  begin
    --select dbo.fn_DftOf('{"value":"0.5"}')
    --select dbo.fn_DftOf('{"value":"-3.5"}')
    --select dbo.fn_DftOf('{"value":"90.000000"}')
    --select dbo.fn_DftOf('{"value":"90"}')
    --select dbo.fn_DftOf('{"value":"26.156250"}')
   
    set @iValue = Convert(Decimal(12,6), @value)
    if @iValue<0
      set @sFh = '-'
    set @sZs = Convert(varchar(50), Abs(Cast(@iValue as int))) --整數 
    set @iValue = @iValue - cast(@iValue  as int)         --Ex: -1.2345 -> -0.2345
    set @iValue = Abs(@iValue)                            --Ex: -0.2345 -> 0.2345
    set @sXs = Convert(varchar(50), @iValue)
    set @sXs = convert(varchar(50), convert(int, right(@sXs, len(@sXs)-2))) --小數
 
    if @sXs = '0'
    begin
      return @sZs --純整數
    end
    else
    begin

      set @iFz = convert(int, @sXs)   --分子
      set @iFm = power(10, len(@sXs)) --分母 
      set @iZdgys = (select dbo.fn_DftOf('{"type":"getZDGYS", "num1":"'+convert(varchar(10), @iFm)+'", "num2":"'+convert(varchar(10), @iFz)+'"}')) --求最大公約數 
      set @sResult = convert(varchar(50),(@iFz/@iZdgys))+'/'+convert(varchar(50),(@iFm/@iZdgys))


      if @sZs='0'
        set @sZs = ''
      else
        set @sZs = @sZs +'-' --整數與分數的連接符號

      if @sFh='-'
        set @sResult = '-(' + @sZs + @sResult + ')' --Ex: -(3-1/2)
      else
        set @sResult = @sZs + @sResult              --Ex:   3-1/2
    end
   
    return @sResult
  end
  ------------------------------------------------------------------------------------------------------------------------
  ------------------------------------------------------------------------------------------------------------------------
  --取最大公約數
  ------------------------------------------------------------------------------------------------------------------------
  ------------------------------------------------------------------------------------------------------------------------
  else if @type='getZDGYS'
  begin
    --select dbo.fn_DftOf('{"type":"getZDGYS", "num1":"10", "num2":"5"}') --求最大公約數 
    --select dbo.fn_DftOf('{"type":"getZDGYS", "num1":"100", "num2":"75"}') --求最大公約數 

    --輾轉相除法
    set @iNum1=convert(int, @num1)
    set @iNum2=convert(int, @num2)
    set @iYs=@iNum1 - @iNum2 * convert(int, @iNum1/@iNum2)  --MOD

    while @iYs <> 0
    begin
      set @iNum1 = @iNum2
      set @iNum2 = @iYs 
      set @iYs   = @iNum1 - @iNum2 * convert(int, @iNum1/@iNum2) --MOD
    end

    set @sResult = convert(varchar(50), @iNum2)

    return @sResult
  end
 
  return '' 
end
 



改編至 http://blog.csdn.net/yzsind/article/details/2604798

SQL 常用數值函式

-------------------------------------------------------------------------------------------------------
--數字
-------------------------------------------------------------------------------------------------------
--取整數 select 
select cast(12.345  as int)
--取小數
select   12.345 - cast(12.345  as int)
-------------------------------------------------------------------------------------------------------
--字串
-------------------------------------------------------------------------------------------------------
--字串前面補零   
select REPLICATE('0',10-LEN(Field)) + RTRIM(CAST(Field AS CHAR)) FROM Table
-------------------------------------------------------------------------------------------------------
--日期
-------------------------------------------------------------------------------------------------------
--年初
select DATEADD(yy, datediff(yy, 0,  getdate()), 0) as 年初
--年底
select DATEADD(yy, datediff(yy, 0,  getdate())+1, -1) as 年底
--月初
select DATEADD(mm, DATEDIFF(mm, '', getdate()), '') as 月初
--月底
select DATEADD(day, -1, DATEADD(mm, DATEDIFF(mm, '', getdate())+1, '')) as 月底
--上月初
select DATEADD(mm, -1, DATEADD(mm, DATEDIFF(mm, '', getdate()), '')) as 上月初
--上月底
select DATEADD(day, -1, DATEADD(mm, DATEDIFF(mm, '', getdate()), '')) as 上月底
--前二個月初
select DATEADD(mm, DATEDIFF(mm, '', getdate())-2, '') as 前二個月初   
--前二個月底           
select DATEADD(day, -1, DATEADD(mm, DATEDIFF(mm, '', getdate())-1, '')) as 前二個月底 
--下個月初
select DATEADD(mm, DATEDIFF(mm, '', getdate())+1, '') as 下個月初 
--下個月底           
select DATEADD(day, -1, DATEADD(mm, DATEDIFF(mm, '', getdate())+2, '')) as 下個月底
--本週起始日_週日起算
select Getdate()-DATEPART(dw, GETDATE()-7)+1 as 本週起始日_週日起算
--本週末_週六
select Getdate()-DATEPART(dw, GETDATE()-7)+7 as 本週末_週六
--本週起始日_週一起算
select Getdate()-DATEPART(dw, GETDATE())+2 as 本週起始日_週一起算
--本週末_週日
select Getdate()-DATEPART(dw, GETDATE())+8 as 本週末_週日
--上週起始日_週日起算
select Getdate()-7-DATEPART(dw, GETDATE()-7)+1 as 上週起始日_週日起算
--上週末(週六)
select Getdate()-7-DATEPART(dw, GETDATE()-7)+7 as 上週末_週六
--上週起始日_週一起算
select Getdate()-7-DATEPART(dw, GETDATE()-7)+2 as 上週起始日_週一起算
--上週末_週日
select Getdate()-7-DATEPART(dw, GETDATE()-7)+8 as 上週末_週日

MSSQL RowID By Group

Select ROW_NUMBER() OVER(PARTITION BY [Field1] ORDER BY [Field1] DESC) AS RowID, *
from [Table]

SQL 找不到要更新的資料列。最後讀取的值已被變更

Ex:

  SQL Server

    Close;
    SQL.Text := 'SET NOCOUNT OFF';
    ExecSQL;

    UpdateBatch();

    Close;
    SQL.Text := 'SET NOCOUNT ON';
    ExecSQL;

SQL 常用日期函式

本週星期一
SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)

第一個星期一
select DATEADD(wk, DATEDIFF(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())), 0)

月初
SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)

月底
SELECT DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))   

上個月底
SELECT DATEADD(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))

年初
SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)

年底
SELECT DATEADD(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))

去年底
SELECT DATEADD(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))

由滑鼠位置取得Form上物件的名稱

procedure TForm1.ApplicationEvents1Message(var Msg: tagMSG;
  var Handled: Boolean);
var pd:TPoint;
  WinCon : TWinControl;
  WND : HWND;
  cmpComponent:TComponent;
begin
  if Msg.message=WM_MOUSEMOVE then
  begin
    GetCursorPos(pd);
    WND := Handle;
    repeat
      WinCon := FindControl(WND);
      WND := ChildWindowFromPoint(WinCon.Handle,WinCon.ScreenToClient(pd));
      if (WND = 0) or (not WinCon.Showing) or (not WinCon.CanFocus) then //增加判斷 showing focus
        exit;
    until (WND = WinCon.Handle) or (WinCon.ControlCount <= 0);
    cmpComponent := FindComponent(wincon.Name);
    if Assigned(cmpComponent) then
    begin
      Label1.Caption := cmpComponent.Name;
    end;
  end;
end;

參考:  http://delphi.ktop.com.tw/board.php?cid=168&fid=913&tid=101905

將應用程式建立在Form上

Ex:將小算盤移到Form上

procedure TForm1.Button1Click(Sender: TObject);
var hWndNewParent : THandle;
begin
  hWndNewParent := Findwindow(nil, '小算盤');
  Windows.SetParent(hWndNewParent, Form1.Handle);
end;

tscap32 Delphi Video Capture Component

網路視訊 for Delphi 元件

http://tscap32.sourceforge.net/screenshots.html