[SP]up_Desc

Posted In: MSSQL , Stored Procedure . By 卡西卡

類似Oracle的desc指令

CREATE PROCEDURE up_desc (   
    @TABLE_NAME NVARCHAR(128)  -- 資料表名稱    
)AS    
SELECT COLUMN_NAME,    
CASE DATA_TYPE    
        WHEN 'char' THEN DATA_TYPE+'('+CONVERT(VARCHAR(10),CHARACTER_MAXIMUM_LENGTH)+')'    
        WHEN 'varchar' THEN DATA_TYPE+'('+CONVERT(VARCHAR(10),CHARACTER_MAXIMUM_LENGTH)+')'    
        WHEN 'nvarchar' THEN DATA_TYPE+'('+CONVERT(VARCHAR(10),CHARACTER_MAXIMUM_LENGTH)+')'    
        WHEN 'numeric' THEN DATA_TYPE+'('+CONVERT(VARCHAR(10),NUMERIC_PRECISION)+','+CONVERT(VARCHAR(10),NUMERIC_SCALE)+')'    
        ELSE DATA_TYPE END AS OBJECT_DATA_TYPE,    
    B.value as [OBJECT_DESCRIPTION]    
FROM [INFORMATION_SCHEMA].[COLUMNS] A    
  LEFT JOIN ::fn_listextendedproperty ('MS_Description', 'user', 'dbo', 'TABLE', @TABLE_NAME, 'COLUMN', default) B ON A.COLUMN_NAME=B.objname    
WHERE TABLE_NAME=@TABLE_NAME    
ORDER BY A.ORDINAL_POSITION

Up?

[SP]up_Obj

Posted In: MSSQL , Stored Procedure . By 卡西卡

尋找view,proc,func中有某個字串

CREATE PROC up_obj (   
  @STR VARCHAR(100)    
) AS



SELECT object_name(id) AS OBJNAME   
FROM [syscomments]    
WHERE [text] like '%'+@STR+'%'    
ORDER BY object_name(id)

Up?

[SP]up_Select

Posted In: MSSQL , Stored Procedure . By 卡西卡

將資料表所有欄位組成SELECT句

CREATE PROCEDURE up_Select (    
    @TABLE_NAME NVARCHAR(128) -- 資料表名稱     
)AS     
DECLARE @BUF NVARCHAR(1000)     
DECLARE @COLUMN_NAME NVARCHAR(128)




SET @BUF='' 



DECLARE curView CURSOR  FOR      
    SELECT COLUMN_NAME       
    FROM [INFORMATION_SCHEMA].[COLUMNS]       
    WHERE TABLE_NAME=@TABLE_NAME




OPEN curView      
FETCH curView       
INTO @COLUMN_NAME 



WHILE @@FETCH_STATUS = 0      
    BEGIN -- WHILE       
        SELECT @BUF=@BUF + @COLUMN_NAME + ','       
        FETCH curView INTO @COLUMN_NAME       
    END -- WHILE 




CLOSE curView      
DEALLOCATE curView



IF Len(@BUF)>0      
BEGIN       
    SELECT @BUF = LEFT(@BUF,LEN(@BUF)-1)       
    PRINT 'SELECT'       
    PRINT @BUF       
    PRINT 'FROM ['+@TABLE_NAME+']'       
END       
ELSE       
    PRINT '(none)'

Up?

[SP]up_RefreshView

Posted In: MSSQL , Stored Procedure . By 卡西卡

重整所有視表。
sp_refreshview [msdn]

CREATE PROCEDURE [up_RefreshView] AS
DECLARE @ViewName sysname
    
DECLARE curView CURSOR  FOR
    SELECT TABLE_NAME
    FROM [INFORMATION_SCHEMA].[TABLES]     
    WHERE TABLE_TYPE='VIEW' AND [TABLE_SCHEMA]<>'INFORMATION_SCHEMA' AND SUBSTRING(TABLE_NAME,1,3)!='sys'



OPEN curView FETCH curView INTO @ViewName



WHILE @@FETCH_STATUS = 0
    BEGIN -- WHILE
        print @ViewName
        exec sp_refreshview @ViewName
        FETCH curView INTO @ViewName
    END -- WHILE



CLOSE curView
DEALLOCATE curView

Up?

Ping 1.1

Posted In: C# 1.1 , Network . By 卡西卡

using System; using System.Net; using System.Net.Sockets; using System.Text; public class NetworkHelper { public static string GetLastError() { return _LastError; } private static string _LastError = string.Empty; public static bool Ping(string hostnameOrIp) { Socket host = null; try { // 對hostname的處理 IPHostEntry hostInfo; try { hostInfo = Dns.GetHostByName(hostnameOrIp); } // try catch(Exception) { _LastError = string.Format("Unknown host {0}", hostnameOrIp); return false; } // catch IPEndPoint iep= new IPEndPoint(hostInfo.AddressList[0], 0); EndPoint ep = (EndPoint)iep; byte[] data = new byte[1024]; int recv; host = new Socket(AddressFamily.InterNetwork, SocketType.Raw,ProtocolType.Icmp); ICMP packet = new ICMP(); packet.Type = 0x08; packet.Code = 0x00; packet.Checksum = 0; Buffer.BlockCopy(BitConverter.GetBytes((short)1), 0, packet.Message, 0, 2); Buffer.BlockCopy(BitConverter.GetBytes((short)1), 0, packet.Message, 2, 2); data = Encoding.ASCII.GetBytes("test packet"); Buffer.BlockCopy(data, 0, packet.Message, 4, data.Length); packet.MessageSize = data.Length + 4; int packetsize = packet.MessageSize + 4; UInt16 chcksum = packet.getChecksum(); packet.Checksum = chcksum; host.SetSocketOption(SocketOptionLevel.Socket, SocketOptionName.ReceiveTimeout, 3000); host.SendTo(packet.getBytes(), packetsize, SocketFlags.None, iep); try { data = new byte[1024]; recv = host.ReceiveFrom(data, ref ep); return true; } // try catch (SocketException) { _LastError = "No response from remote host"; return false; } // catch // // 結果 // ICMP response = new ICMP(data, recv); // Console.WriteLine("response from: {0}", ep.ToString()); // Console.WriteLine(" Type {0}", response.Type); // Console.WriteLine(" Code: {0}", response.Code); // int Identifier = BitConverter.ToInt16(response.Message, 0); // int Sequence = BitConverter.ToInt16(response.Message, 2); // Console.WriteLine(" Identifier: {0}", Identifier); // Console.WriteLine(" Sequence: {0}", Sequence); // string stringData = Encoding.ASCII.GetString(response.Message, 4, response.MessageSize - 4); // Console.WriteLine(" data: {0}", stringData); } // try catch { _LastError = string.Format("Unknown host {0}", hostnameOrIp); return false; } // catch finally { if ( host!=null ) host.Close(); } // finally } class ICMP { public byte Type; public byte Code; public UInt16 Checksum; public int MessageSize; public byte[] Message = new byte[1024]; public ICMP() {} public ICMP(byte[] data, int size) { Type = data[20]; Code = data[21]; Checksum = BitConverter.ToUInt16(data, 22); MessageSize = size - 24; Buffer.BlockCopy(data, 24, Message, 0, MessageSize); } public byte[] getBytes() { byte[] data = new byte[MessageSize + 9]; Buffer.BlockCopy(BitConverter.GetBytes(Type), 0, data, 0, 1); Buffer.BlockCopy(BitConverter.GetBytes(Code), 0, data, 1, 1); Buffer.BlockCopy(BitConverter.GetBytes(Checksum), 0, data, 2, 2); Buffer.BlockCopy(Message, 0, data, 4, MessageSize); return data; } public UInt16 getChecksum() { UInt32 chcksm = 0; byte[] data = getBytes(); int packetsize = MessageSize + 8; int index = 0; while ( index < packetsize) { chcksm += Convert.ToUInt32(BitConverter.ToUInt16(data, index)); index += 2; } chcksm = (chcksm >> 16) + (chcksm & 0xffff); chcksm += (chcksm >> 16); return (UInt16)(~chcksm); } } // class ICMP } // class NetworkHelper

使用範例：
public class MainProc { public static void Main(string[] argv) { // 測試 bool ret = NetworkHelper.Ping(argv[0]); Console.WriteLine("Ping [{0}] = {1}", argv[0], ret); } }　

注意事項：

.NET 2.0 以上有Ping Class可以直接使用。[msdn]

參考資料：

http://www.java2s.com/Code/CSharp/Network/SimplePing.htm

Up?

ArcIMS - GET_FEATURES

Posted In: ArcIMS , GIS . By 卡西卡

GET_FEATURES 只能對Query, Feature, ArcMap 這3種Server送出。
送出的Request編碼要設為big5才不會有中文亂碼。不能用UTF-8。

送出

LAYER：指定要查那一層
SPATIALQUERY：查詢條件
- subfields：回傳的欄位，用空格分隔（如：ST_CODE ST_NAME）
  #ALL#, #SHAPE#
- where：條件。<>符號要先編碼

回傳

<?xml version="1.0" encoding="big5"?>
<ARCXML version="1.1">
<RESPONSE>
<FEATURES>
<FEATURE>
<FIELDS>
<FIELD name="ID" value="2" />
<FIELD name="STCODE" value="466900" />
<FIELD name="STNAME" value="淡水" />
<FIELD name="LONG" value="121.44" />
<FIELD name="LAT" value="25.1655555555556" />
<FIELD name="STX" value="293512.984729649" />
<FIELD name="STY" value="2784394.69179442" />
<FIELD name="#SHAPE#" value="[Geometry]" />
<FIELD name="#ID#" value="2" />
</FIELDS>
<MULTIPOINT>
<POINT x="293512.984729649" y="2784394.69179442"/>
</MULTIPOINT>
</FEATURE>
<FEATURE>
<FIELDS>
<FIELD name="ID" value="3" />
<FIELD name="STCODE" value="466910" />
<FIELD name="STNAME" value="鞍部" />
<FIELD name="LONG" value="121.520183333333" />
<FIELD name="LAT" value="25.1865138888889" />
<FIELD name="STX" value="301587.056077921" />
<FIELD name="STY" value="2786745.00316004" />
<FIELD name="#SHAPE#" value="[Geometry]" />
<FIELD name="#ID#" value="3" />
</FIELDS>
<MULTIPOINT>
<POINT x="301587.056077921" y="2786745.00316004"/>
</MULTIPOINT>
</FEATURE>
<FEATURE>
<FIELDS>
<FIELD name="ID" value="4" />
<FIELD name="STCODE" value="466920" />
<FIELD name="STNAME" value="臺北" />
<FIELD name="LONG" value="121.506708333333" />
<FIELD name="LAT" value="25.0396166666667" />
<FIELD name="STX" value="300289.987461941" />
<FIELD name="STY" value="2770468.50369946" />
<FIELD name="#SHAPE#" value="[Geometry]" />
<FIELD name="#ID#" value="4" />
</FIELDS>
<MULTIPOINT>
<POINT x="300289.987461941" y="2770468.50369946"/>
</MULTIPOINT>
</FEATURE>
<FEATURE>
<FIELDS>
<FIELD name="ID" value="5" />
<FIELD name="STCODE" value="466930" />
<FIELD name="STNAME" value="竹子湖" />
<FIELD name="LONG" value="121.536272222222" />
<FIELD name="LAT" value="25.1649861111111" />
<FIELD name="STX" value="303218.153693239" />
<FIELD name="STY" value="2784366.77813037" />
<FIELD name="#SHAPE#" value="[Geometry]" />
<FIELD name="#ID#" value="5" />
</FIELDS>
<MULTIPOINT>
<POINT x="303218.153693239" y="2784366.77813037"/>
</MULTIPOINT>
</FEATURE>
<FEATURECOUNT count="4" hasmore="false" />
</FEATURES>
</RESPONSE>
</ARCXML>

GET_FEATURES

outputmode (binary, xml, newxml)
binary表示傳及收都是compressed binary stream。
newxml回傳型式如上面的例子。
xml會傳回簡短型式的資料
<FEATURE><FIELDS STCODE="466880" STNAME="板橋" /></FEATURE>
加上featurelimit可以限制回傳的資料筆數
加上beginrecord可以指定從第幾筆資料開始回傳。
加上skipfeatures，true表示不傳細項，只回傳有幾筆資料。

subfields

用#ALL#會回傳所有欄位。
加上 #SHAPE# ，如："#SHAPE# STCODE STNAME，會回傳：
<FEATURE> <FIELDS> <FIELD name="STCODE" value="466900" /> <FIELD name="STNAME" value="淡水" /> <FIELD name="#SHAPE#" value="[Geometry]" /> </FIELDS> <MULTIPOINT> <POINT x="293512.984729649" y="2784394.69179442"/> </MULTIPOINT> </FEATURE>

參考資料：

ArcXML Programmer's Reference Guide

Up?

ArcIMS - GET_IMAGE

Posted In: ArcIMS , GIS . By 卡西卡

.NET Link, ArcXML

using ESRI.ArcIMS.Server;

ServerConnection oConn = new ServerConnection();
oConn.Host = "localhost";
oConn.ServiceName = "edbims";
oConn.AppServerPort = 5300;
oConn.Scheme = Scheme.TCP;

System.Text.StringBuilder sAXLText = new System.Text.StringBuilder();
sAXLText.Append("<?xml version=\"1.0\" encoding=\"UTF-8\"?><ARCXML version=\"1.1\">");
sAXLText.Append("<REQUEST><GET_IMAGE><PROPERTIES>");
// 指定輸出圖檔的寬與高
sAXLText.AppendFormat("<IMAGESIZE width='{0}' height='{1}' />", imgMap.Width.Value , imgMap.Height.Value );
// 指定輸出地圖的範圍
sAXLText.AppendFormat("<ENVELOPE minx='{0}' miny='{1}' maxx='{2}' maxy='{3}' />", minX,minY,maxX,maxY);
sAXLText.Append("</PROPERTIES></GET_IMAGE></REQUEST></ARCXML>");

string response = oConn.Send(sAXLText.ToString());

System.Xml.XmlDocument axlResponse = new System.Xml.XmlDocument();
axlResponse.LoadXml(response); 
if (axlResponse.GetElementsByTagName("OUTPUT").Count == 1)
{ 
 System.Xml.XmlNodeList nodeOutput = axlResponse.GetElementsByTagName("OUTPUT");
 // 指定最後的圖檔URL
 imgMap.ImageUrl = nodeOutput[0].Attributes["url"].Value;
} // if

相關DLL

ESRI.ArcIMS.Server.dll
ESRI.ArcIMS.Server.TCP.dll

重點在於將圖檔大小和地圖範圍填入XML中傳給ArcIMS Server，並解析回傳的結果。

送出
<?xml version="1.0" encoding="UTF-8"?> <ARCXML version="1.1"> <REQUEST> <GET_IMAGE> <PROPERTIES> <IMAGESIZE width='600' height='500' /> <ENVELOPE minx='281113.624997922' miny='2794476.96154711' maxx='319513.624997922' maxy='2765676.96154711' /> </PROPERTIES> </GET_IMAGE> </REQUEST> </ARCXML>

接收
<?xml version="1.0" encoding="UTF-8"?> <ARCXML version="1.1"> <RESPONSE> <IMAGE> <ENVELOPE minx="281113.624997922" miny="2764076.96154711" maxx="319513.624997922" maxy="2796076.96154711" /> <OUTPUT file="F:\ArcIMS\Output\edbims_EMC-AP226763524199.png" url="http://emc-ap2/output/edbims_EMC-AP226763524199.png" /> </IMAGE> </RESPONSE> </ARCXML>

參考資料：

http://gis.ascc.net/ISTIS/tools.html

Up?

[SP]up_Desc

[SP]up_Obj

[SP]up_Select

[SP]up_RefreshView

Ping 1.1

ArcIMS - GET_FEATURES

ArcIMS - GET_IMAGE

Search This Blog

Blog Archive

參考資料

Categories

工具

好物

Javascript Framework

funP