MSSOAPTookKit30 忘了他 請改用 MSOSop30.dll

MS SOAP Toolkit on Excel 64 bit 沒這種東西 BUT MS office 64 bit 內有附一個 msosoap30.dll 請注意多了一個O.
路徑在:
c:\program File\Common Files\microsoft shared\office14\MSSOAP30.DLL

所以也不太用需要再去裝 SOAP ToolKit 了.

原本 mssop30.dll amyo 是x86 拿去X64 的EXCEL 上會無法使用..他會錯在 set myobj = new MSSOSOAPLib30.SoapClient30 這行. 所以在 excel vba 編輯IDE 中. 要去工具/設定引用項目/瀏覽  去找你自己OFFICE 的路徑.

TIPTOP BOM on ORACLE store procedure

1.
2.
3.
4.
01:建立object TYPE
CREATE OR REPLACE TYPE TP_BOM_01 AS OBJECT
--這是展BOM要用的TYPE
--
       ( TOP_PART       VARCHAR2(50),
         LAST_PART    VARCHAR2(50),
         UNIT          varchar2(20),  --單位
         Type_          varchar(20),  --形態 P, M
         BOM_PATH       VARCHAR2(4000),
         UsagePerSignal decimal(18,4),
         BOM_LEVEL      int,
         Locate         varchar2(4096)
        );
GO
02:將01的object 建一個table type 好回傳
/*return table type*/
CREATE OR REPLACE TYPE TP_BOM_WHERE_USE_01_TABLE AS TABLE OF GWT.TP_BOM_WHERE_USE_01
GO

03: 單一主建展bom 到最底階.
/* BOM 單一料號*/
CREATE OR REPLACE FUNCTION GWT.FN_BOM_001(
    p_date       in date,
    p_start_with in varchar2,
    p_OrgPartno in varchar2 default null,       --
    p_level in  number default 0,
    p_usage in number default 1,
    p_path in  varchar2 default '',
    p_bmb09 in varchar2 default '' )
    return TP_BOM_01_Table PIPELINED is
out_rec TP_BOM_01 := TP_BOM_01(null,null,null,null,null,null,null, null);
rowCount int;
p_OrgPartno2 varchar2(40);
begin

    p_OrgPartno2 := p_OrgPartno;
    for c in ( select * from bmb_file, ima_file where ima01=bmb03 and bmb01 like p_start_with and
                (  bmb04 <= p_date OR bmb04 IS NULL) AND ( bmb05 > p_date OR bmb05 IS NULL)  )
    loop
        --if (( p_level >1 and c.bmb19=3 ) or ( p_level<=1)) and (( p_level >1 and c.ima08='M' ) or ( p_level<=1)) then
                if p_level <=0 then
                    p_OrgPartno2 := c.bmb01;
                end if;
            select count(*) into rowCount from bmb_file where bmb01=c.bmb03 and
               ( (  bmb04 <= p_date OR bmb04 IS NULL) AND ( bmb05 > p_date OR bmb05 IS NULL)  );
            if rowCount =0  then -- and c.bmb19=3 then
                out_rec.TOP_PART := p_OrgPartno2;
                out_rec.LAST_PART := c.bmb03;
                out_rec.UNIT := c.bmb10;
                out_rec.Type_ := c.ima08;
                out_rec.BOM_PATH := p_path || '>>' ||  c.bmb03 || '(' || p_bmb09 || ')' ;
                out_rec.UsagePerSignal := c.BMB06 * p_usage ;
                out_rec.BOM_LEVEL := p_level;
                out_rec.Locate := '';
                PIPE ROW(out_rec);
            end if;
          
            if 1=1 then --c.bmb19=3 then
                for c2 in ( SELECT * from TABLE(FN_BOM_001( p_date, c.bmb03, p_OrgPartno2, p_level+1, c.BMB06 * p_usage, p_path || '>>' || p_start_with || '(' || p_bmb09 || ')' ,c.bmb09 )) )
                loop
                    out_rec.TOP_PART := c2.TOP_PART;
                    out_rec.LAST_PART := c2.LAST_PART;
                    out_rec.BOM_PATH :=  c2.BOM_PATH  ;
                    out_rec.UNIT := c2.UNIT;
                    out_rec.Type_ := c2.Type_;
                    out_rec.UsagePerSignal := c2.UsagePerSignal;
                    out_rec.BOM_LEVEL := c2.BOM_LEVEL;
                    out_rec.Locate := c2.Locate;
                    PIPE ROW(out_rec);
                end loop;
            end if;
        --end if;
    end loop;
end;


GO

04:一個一個展太
/*全展*/
CREATE OR REPLACE FUNCTION GWT.FN_BOM_ALL
    return TP_BOM_01_Table PIPELINED is
out_rec TP_BOM_01 := TP_BOM_01(null,null,null,null,null,null,null, null);
rowCount int;
p_OrgPartno2 varchar2(40);
begin


    for c in ( select * from  ima_file where ( ima01 like '01%' or ima01 like '0T%' or ima01 like '13%' or ima01 like '1013%' )  and ((ima01 like '01%' and   substr(ima01,12,1)<>'0' ) or (ima01 not like '01%' ))  )
    loop
        -- SELECT *  from TABLE(FN_BOM_001( to_date('16/05/18','yy/MM/dd'), '01DS102200GT', null, 0, 1, '' ))
        for c2 in ( SELECT * from TABLE(FN_BOM_001( sysdate , c.ima01,null, 0,1,'', '')) )
        loop
                    out_rec.TOP_PART := c2.TOP_PART;
                    out_rec.LAST_PART := c2.LAST_PART;
                    out_rec.BOM_PATH :=  c2.BOM_PATH  ;
                    out_rec.UNIT := c2.UNIT;
                    out_rec.Type_ := c2.Type_;
                    out_rec.UsagePerSignal := c2.UsagePerSignal;
                    out_rec.BOM_LEVEL := c2.BOM_LEVEL;
                    out_rec.Locate := c2.Locate;
                    PIPE ROW(out_rec);
        end loop;
    end loop;
end;


GO

使用方式
01: 單一BOM 查詢
SELECT * from TABLE(FN_BOM_001( sysdate , 'MastPart',null, 0,1,'', ''))
02: 全部料號BOM 查詢
SELECT * FROM TABLE(FN_BOM_ALL())
03: 將查詣的BOM 轉成TABLE 存放
CREATE TABLE bom_20160606 AS
SELECT * FROM TABLE(FN_BOM_ALL())

Oracle Grant All Permission to User

BEGIN
    FOR t IN (select * from dba_tables where owner!='GGSTEST'
            AND owner not like '%SYS%'
            AND owner not like '%ORDDATA%'
            AND owner not like '%ADMIN%'
            AND owner not like '%APEX%'
            AND owner not like '%XDB%'
            AND owner not like '%FLOW_FILES%'
            AND owner not like '%OE%' AND iot_type IS NULL)
    LOOP
    EXECUTE IMMEDIATE 'GRANT ALL PRIVILEGES ON '|| t.owner ||'.' || t.table_name ||' TO TIPTOPGP';
    END LOOP;
    END;

SQL Server sp_addlinkedserver 之 insert,update, delete 方法

SQL Server 的 sp_addlinkedserver 很好用. 可是在連上去後要更新遠端主機上的資料可就不能用傳統的方式來update.
以下是 MSDN 上找的指定
Update :
  update openquery(linked1, 'select ssn from testlinked where ssn=2')
  set ssn=ssn + 1
Insert :
  insert openquery(linked1, 'select ssn from testlinked where 1=0') values (1000)
Insert From..
  insert openquery(linked1, 'select ssn from testlinked where 1=0')
  select myssn from mytable

delete openquery(linked1, 'select ssn from testlinked where ssn>100')

Install Plex Media Server on FreeNas 9.3.0

請先參考他的作法.
https://forums.freenas.org/index.php?threads/tutorial-how-to-install-plex-in-a-freenas-9-3-jail-updated.19412/

安裝完後
進 Shell
指令
jls -v
jexec 2 tcsh
echo plexmediaserver_enable="YES">>/etc/rc.conf  # 我是用VI 去編輯他為plexmediaserver_enable="YES"
設好參數 起動SERVER.
service plexmediaserver start

好了..管理時因為plex server 是用jails 方式來起動PLEX 他是無法存取外部的目錄所以要用以下指令把外在的目錄MOUNT 到jail 中.
先在jail 對映的目錄下建一個Picture 目錄
mkdir /mnt/MIRROR01/JailRoot/plexmediaserver_1/media/Picture
再將路徑用MOUNT 的方式放上去.
mount_nullfs /mnt/MIRROR01/Picture /mnt/MIRROR01/JailRoot/plexmediaserver_1/media/Picture



注意.不可使用ln 的方式來處理..因為進到jail 系統後ln 的目錄是無法被正確存取.

之後可在外部的OS上/etc/fstab 中加入以下MOUNT 資料..他開機就自動MOUNT 上去了.
/mnt/MIRROR01/Picture/ /mnt/MIRROR01/JailRoot/plexmediaserver_1/media/Picture/   nullfs   ro      0       0

Informix CSK netset 設定

informix netset32
client_locale=zh_tw.big5
db_locale=zh_tw.57352

ibm informix server = on_shm1
hostname = 172.16.1.9x
protocolname = onsoctcp
service name = 8001

4GL 日期的運算

日期1-日期2 = 幾天
在 IBM Genero 4GL 程式語言中. 日期的運算可真是.不好用
以下程式代碼看看. 看不懂我也無法救你了.
注意. 在資料中通常我們會用   l_d1 like  abc_table.date01 方式來宣告變數. 可是在作運算時他很白痴的不會自動轉方Datetime 再作運算 所以我會先把資料庫欄位先存到我自己的變數 如下 l_d1 datetime year to day 這樣在作運算時比較不會出錯. 一出錯 你的 l_i1 就一定是 NULL 值


 
  define l_d1 datetime year to day
  define l_d2 datetime year to day
  define l_i1 interval day(5) to day
  define l_l2 interval month to month
  let l_d1 = '2002-01-01'
  let l_d2 = '2015-09-01'
  let l_i1=  l_d2-l_d1
  display  l_i1
  let l_l2 = interval(1) month to month
  let l_d1 = l_d1 +  interval(1) month to month    #加一個月的日期
  let l_d1 = l_d1 -  interval(1) month to month    #減一個月的日期
  let l_d1 = l_d1 +  interval(1) year to year    #加一個年的日期
 
 
  if l_i1 > interval(365) day(5) to day then    #判斷式的用法
    display "over 1 year"
  else
    display "Yes 1 year"   
  end if