天軟科技-應用專題第三方交互04平臺與數據庫版

更新日更新說增加交互配置方式--API連接(API模式為2014923日新增功能，仍保留原來的若部分客戶沒有該功能請更新客戶端)為了與通用數據庫進行交互，天軟提供了ExcecSQLInsertSQLTable兩種方法。兩種方法各有應通過ExecSQL可以進行CreateTable、Insert、Select、Delete、Update、DropTable通過ExecSQL可以進行CreateProcedure、Exec、DropProcedure等操ExcecSQLInsertSQLTable進行Insert數據的時候，各有優(yōu)劣和應用場合如果數據庫字段是日期類型適用于客戶端的用戶，以下是3個用到交互的情況：需要調用本地數據庫的數據（這些數據的提供商可以是非天軟科技）運算常見的通用數據庫SQLServer、Oracle、MySQL、Access天軟都支持與本地交互Sql2008、Oracle、Mysql、Access與平臺實現交互前，需要進行一些配置，平臺上提供了與數據庫交互的兩種連接方式ADO連接和API連接。ADO的配置（3.2.1中的驅動選擇），即使本地沒有安裝Oracle客戶端也可以進行交互，因此建議使用Oracle的用戶與TS平臺交互時使用API連接的方式。在天軟安裝下，找到并打開“ConnectMan.exe”。(TS平臺上“系統(tǒng)”—>“系統(tǒng)設置”—>點擊“打開運行/存貯”找到)，如下圖：方法1-API連Oracle：Ora 下pubkrnl.dll文件會自動更新，同時API模式還需要Oracle下面的四個dll文件：oci.dll、oraocci11.dll、oraociei11.dll、orasql11.dll(名稱中的數字和版本有關)。為了能加載到這四個dll文件，有兩種方法：本機已經安裝Oracle客戶端，把這四個dll文件所在路徑放到環(huán)境變量中（oracle客戶端會自動生成路徑并添加，或者從其他機子拷貝4dll文件到某路徑，并添加到系統(tǒng)path中）。 Database：輸入“數據庫所在服務器:端口/數據庫名”或者”O(jiān)racle客戶端的服務命名”(服務命名的配置部分詳見下文)，例如”:1521/TS_FUNDIDS”或者”ka”,如下圖。Password：登錄數據庫的。AuthMode：數據庫登錄模式Characterset：字符集，只有UTF8，如果是Oracle用戶，得到字符串后，需自己把字符集ZHS16GBK點擊Test測試連用戶可在ConnectMan.exe中繼續(xù)測試是否能提取數據，打開“SQLScript”欄，在框中輸SQL語句，比如SelectDatabase：輸入“數據庫所在服務器:端口/數據庫名”MY_SQL(SQLserver只需輸入數據庫Password：登錄數據庫的。相同3-選擇TS平臺上“系統(tǒng)”—>“系統(tǒng)設置”—>SQL信任配置文件”，將獲取的連接串復第一部分是數據庫別名第二部分是連接串，注意開頭必須是以Oracle為例，從Connectman中的連接串為：在配置文件Execsql.ini中的標準格式如圖注：在ExecSQL.ini文件中配置不同的數據庫，需要使用不同的別名方法2-ADO連Oracle：OracleProviderforOLEDB或者OLEDBProviderforSqlserver：OLEDBProviderforSQLOLEDBProviderforODBCDriversAccess：Office12.0AccessDatabaseEngineOLEDBProvider在步驟2中若選擇OracleProviderforOLEDB。(OracleProviderforOLEDB,否則將無法正常使4.2insertsqltable方法)。在“1.輸入數據源和(或)數據位置”下的數據源處輸入OracleOracleNetManager中進行添加配置，如下圖的ka：(注：也可將已配好客戶端的配置文件(tnsnames.ora)到指 下即可，如果客戶端裝在盤，則路徑 在“2.輸入登錄服務器的信息”下輸入用戶名稱和勾選“允許保存單擊測試連接，如果成功，單擊“確定”，回到ConnectMan窗口，連接串，修改連接串為標準模Sql在“1.選擇或輸入服務器名稱”下選擇sqlserver數據庫所在服務器名稱(或計算機名)或輸入IP在“2.輸入登錄服務器的信息”下單擊“使用指定的用戶名稱和”，輸入用戶名稱和；(注：選兩種驗證方式都可以，區(qū)別在于WindowsNT驗證只能連接本機的數據庫，SQL驗證則可以連接任勾選“允許保存單擊.在服務器上選擇數據庫”的下拉菜單，選擇數據庫，如左下圖；(注：也可以選擇“附加一個數據庫文件作為數據庫名”，但是需要瀏覽找到數據庫文件(即mdf文件)，可以用查詢語句tme,pyc_meomdabas_e”查詢到數據庫文件的路徑，然后將該數據庫的名稱到上一空行即可，如右下圖。若提示該文件正在使用，只要停止er相關服務即可)單擊“測試連接”，如果成功，單擊“確定”，回到ConnectMan窗口，連接串，修改連接串為標準1)并配置MysqlODBC驅動(MysqlODBC的安裝可以上網查詢)，即可獲得對應的數據源名稱(圖中為mysql5.3)，選擇驅動OLEDBProviderforODBCDrivers，單擊下一步；在“1.指定數據源”下單擊“使用數據源名稱”下拉選擇1)在“2.輸入登錄服務器的信息”下輸入用戶名稱和勾選“允許保存單擊“測試連接”，如果成功，單擊“確定”，回到ConnectMan窗口，連接串，修改連接串為標準在天軟yse.NET 下創(chuàng)建一個mdb文件（圖中為myexample.mdb），然后將該文件路徑在“2.輸入登錄服務器的信息”下輸入用戶名稱和（access默認用戶名為Admin，無勾選“空白”和“允許保存單擊“測試連接”，如果成功，單擊“確定”，回到ConnectMan窗口，連接串，修改連接串為標準3-第一部分是數據庫別名第二部分是連接串，注意開頭必須是以SQL2008為例，從connectman中的連接串為：Provider=SQLOLEDB.1;IntegratedSecurity=SSPI;PersistSecurityInfo=False;UserID=sa;InitialCatalog=examonline;DataSource=LIANGLIANG-PC\MYSQL2008在配置文件Execsql.ini中的標準模式ID=sa;InitialCatalog=examonline;DataSource=liangliang-pc\MYSQL2008再如ID=sfjy_tr;DataSource=ka注：在ExecSQL.ini文件中配置不同的數據庫，需要使用不同的別名 在配置文件Execsql.ini Insertsqltable方法Execsql方String,][Password：Sting,]Param：調用過程需要輸入的參數(類型為二維表)。DBName：配置文件中數據庫別名，例如[SQLAlias]； ResultPassword：口令，完成配置成后可省略；空類型轉換為空字符串，某些類型轉換為數字0等等。是Password)6個(DataBaseName、SQL、Result、UserName、Password、DataKeepNull)。如果調用的是過程，需要將參數Param帶入，則插在括號的最前端，類型為二維數組，過程2個(ValueDirection)，Value對應的是參數值，Direction設為1。例如Param的值為Param:=array('sex':('Value':1,'Direction':1)，則表示過程需要的參數sex的值為1。Delete、Drop等ExecSQL創(chuàng)建表-CreateSQL、Oracle創(chuàng)建表類似，有圖有SQLStr:="ifexists(Select1fromsysobjectswhereid=object_id('Test'))droptableTestCreateTableTest([EndDate]DateTime,[Close]numeric(18,4),[Vol]numericReturnRet;SQLStr:="declareiNumber;Selectcount(1)intoifromuser_tableswheretable_name=ifi>0executeimmediate'droptableTest';endIf;executeimmediate'CreateTableTest(EndDatedate,StockIDVarchar(10),Closenumber(18,4),volnumber(18,0))';ReturnRet;ExecSQL插入數據-ExecSQL可以直接對數據庫進行InsertExecSQL中，需要首先拼接一個字 r:=SelectSelectopt(8*fromrend;//執(zhí)行SQL操作ReturnRet;SQLStr:='INSERTINTO'+table+'//執(zhí)行SQLreturnRet; //r:=SelectSelectopt(8)*fromr//執(zhí)行SQL操作ReturnRet;a:='insertall\r\n';c:='Select*fromdual';fori:=0tolength(r)-1dot:='into'+table+'values'+t+'\r\n';ReturnRet;ExecSQLSelect、Delete、Update、DropSQLStr:="Select*fromTESTorderbyEndDatedesc";Ret:=rdo2ExecSQL('SQLAlias',SQLStr,t);ifRetthenSQLStr:="DeletefromTestwhereEndDate>='2013-06-30'";Ret:=rdo2ExecSQL('SQLAlias',SQLStr,t);ReturnSQLStr:="UpdateTestSetStockId='000002'whereStockId='SZ000002'";Ret:=rdo2ExecSQL('SQLAlias',SQLStr,t);ReturnRet:=rdo2加上分號，否則會報錯，而sqlserver、mysql、access加不加分號都能執(zhí)行。使用ExecSQL調用過詳見：#調用過Insertsqltable方以上范例用到的InsertInsertsqltableTableofDbnameselect[字稱]fromR注：Insert語句的具體用法可參見 .cn/tsdn/helpdoc/display.tsl?id=216或者在平臺上F1打開語言幫助搜索“INSERT語句”查詢。測試表結構：用CreateTable或者在SQL/OracleCreate創(chuàng)建 //Insert//也可以把數據Insert到服務器上指定的數據庫，不過在做此前，先確認是否限ReturnRet;//DBAlias：在ExecSQL.ini中配置的數據庫別//TableReturnInsertsqltabletableofDBAlias使用說明請參見：http Insert插入數據的3種情況（適用于Oracle和需要插入的數據字和數據庫表中字相同。4.2.1范例中的子函數Insertsqltable代碼可簡寫為Insertsqltable表名of別名數據表;returnInsertsqltabletableofdbname需要插入的數據字和數據庫字不盡相同，需要使用類SQL語法中的Select語句進行修改字//假設數據r的字稱和數據庫名稱不同，使用類SQL語法的Select語句轉換t:=Select['d']as'EndDate',['c']as'Close',returnInsertsqltabletableofDBAliast;注：雖然可以使用類SQL語法中的Select進行字稱轉換，這樣做無形中增加了開發(fā)工作量。因此，我們一般建議在進行模型設計和數據庫設計的時候，字稱盡量相同。只需要插入部分字段，并且字不盡相同。例如4.2.1范例中，只插入前4個字段，除去[vol]，子//InsertVolt:=Select['d']as'EndDate',['c']as'Close'fromrreturnInsertsqltabletableofDBAlias特別說明：若源數據字段個數大于數據庫表中的字段個數，將會插入失InsertSQLTable表4-1：Sql2008中兩種方法耗時比較(單位：秒)。數據個InsertsqltableExecsql耗時InsertSQLTable表4-2：SQL2008中兩種方法用于批量插入的優(yōu)缺點插入數據方優(yōu)缺會提示“INSERT語句中行值表達式的數目超出了1000行值的最大允許值?！保ㄗⅲ涸撓拗剖莝ql自InsertSqltable方但是，使用ExecSQL也不是沒有缺點。當插入數據條數過多的時候可能報“INSERT語句中 r:=SelectSelectopt(8)*fromrend;returnRet;//對于Insert數據到SQLServer，如果數據個數<1000,Insert；否則，分批elsebegin//>1000，分批插入divV:=length(r)divmodV:=length(r)mod1000;fornI:=0tonCount-1doifnotRetthenSQLStr:='INSERTINTO'+table+'returnRet;表4-3：Oracle中兩種方法耗時比較(單位：秒)數據個InsertsqltableExecsql耗時InsertSQLTable說明：Oracle中兩種方法用于批量插入的優(yōu)缺點類似于sql2008的表4-數據庫類建議使用方DateTime/Date類型：419729.5201433112:00SQL2008DateTimeOracleNumber。一般而言，在通常數據庫設計中，日期類型一般都用DateTime類型。String/Varchar類型YYYY-MM-DDHH:MM:SSYYYY-MM-DD，例如“2013-12-31。如果從平臺獲取的是實數類型的數據，那么就要通過DateTimeToStr/DateToStr函數對數據進行類型轉化。對應的SQL2008或Oracle中的Varchar。Integer/Int類型YYYYMMDD，如表示2013DateToInt函數對數據進行轉化。SQL2008intOraclenumberTS平臺上不同的日期或日期時間類型插入到OracleSql2008的表中，可能會遇到插入失敗的情況。現針對幾種常見的日期時間類型進試，見表4-6和表4-7天軟平臺期數據的返回額類取數代碼（詳見r:=nday(n,'EndDate',datetimetostr(sptime()),'Close',close(),r:=nday(n,'EndDate',sp_time(),'Close',close(),'Vol',vol());r:=nday(n,'EndDate',datetoint(sp'Close',close(),表4-6：Oracle中插入日期時間天軟平臺期數據的類Oracle數據庫日期字段類XXX√√XXX√√XX√√XXX√ExecSQL法：如果數據類型是DateTime，數據庫類型也是Date，使用就會出現插入數據失??；如果數據類型是String類型(DateToStr/DateTimeToStr后)，數據庫類型是Date和Varchar類型均可表4-7：Sql2008中插入日期時間類型數據天軟平臺期數據的類SQL數據庫日期字段類√XX√√XXX√√XX√√XXX√類型(DateToStr/DateTimeToStr后)，數據庫類型是DateTime和Varchar類型均在通常數據庫設計中，日期類型一般都用DateTime數據庫側：最好用DateTime調用過Oracle的表TESTcreatePROCEDUREGet_Data@Dealer_StockIDVARCHAR(10))SELECT*FROMtestWHEREEndDate=@Dealer_EndDate該過程的作用是找出指定代碼和指定日期下的數據。現要查找出代碼為'SZ000002',并且日期'2014-04-29'的數據，兩種方s:="execGet_Data'2014-04-IfRetthenReturnt;ElseReturn獲取失敗Param:=array('Dealer_EndDate':('Value':'2014-04-29','Direction':1),Ret:=rdo2ExecSQL(Param,'SQL','GetData',t);IfRetthenReturnt;ElseReturn獲取失敗組為：存貯過程參數名為行標（不需要面加上特殊字符@），列標為value和Direction,value對應參數值，Direction為1,第三個參數即為過程名。createorreplaceprocedureinserttest(st_numinnumber,ed_numinnumber)foriinst_num..ed_numinsertintoTESTvalues('2014-05-05','SZ000002',' 該過程的作用是循環(huán)插入數據values('2014-05-05','SZ000002','萬科A',10, 100次，兩種方式如下：s:="callinserttest(1,100)";IfRetthenReturn'插入成功';ElseReturn'獲取失敗';Param:=Ret:=rdo2ExecSQL(param,'oracle','inserttest',t);IfRetthenReturn插入成功';ElseReturn'獲取失敗';為過程名，例子中表示按找1:100循環(huán)100次插入語句。表4-8：Sql2008和Oracle調用過程的兩種方式對拼接字符串形輸入參數形Ret:=rdo2ExecSQL('別名',s,t);Param:=array('參數1':('Value':參數值Ret:=rdo2ExecSQL('別名',s,t);Ret:=rdo2ExecSQL(Param,'別名','過程名表4-9：Sql2008和Oracle調用過程的建參數個建議使用方 不同數據庫的連接串到配置文件execsql.ini中都要遵循標準格式，即[別名](換行) 客戶端用戶，與本地數據庫交互時需要在函數前加上rdo2，表示本地運行。如果是落地服務器，做服務器層的配置而不用rdo2，效率更高。上分號，而sqlse