#title="ADO"
#tooltip = "選択した範囲のSQLの実行結果を出力します"
#icon = "C:\Program Files\Common Files\system\ole db\msdasql.dll",0
/**
 * @fileOverview ADOを使用して選択した範囲のSQLの実行結果を出力します
 *
 * @author gogogo56
 * @version 0.0.1
 */

/*
 * プロバイダ
 * http://msdn.microsoft.com/ja-jp/library/cc426812.aspx
 */
var connString = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=C:\\test.mdb";
//var connString = "Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=c:\\;";
//var connString = "DRIVER={Microsoft ODBC for Oracle};UID=scott;PWD=tiger;SERVER=dept;";

/*
 * リミット
 */
var limit = 10000;
/*
 * 出力先（1:新規ファイル　1以外:OutputBar）
 */
var output = 1;
/*
 * 出力先が1:新規ファイルの場合でも、この件数以下の場合はOutputBarに出力する
 */
var outputlimit = 100;

/*
 * 定数
 */
var adCmdText = 1;
var adCmdTable = 2;

/*
 * DataType定数
 */
var DataTypeEnum = new ActiveXObject("Scripting.Dictionary");
DataTypeEnum.add (   0, "adEmpty");
DataTypeEnum.add (   2, "adSmallInt");
DataTypeEnum.add (   3, "adInteger");
DataTypeEnum.add (   4, "adSingle");
DataTypeEnum.add (   5, "adDouble");
DataTypeEnum.add (   6, "adCurrency");
DataTypeEnum.add (   7, "adDate");
DataTypeEnum.add (   8, "adBSTR");
DataTypeEnum.add (   9, "adIDispatch");
DataTypeEnum.add (  10, "adError");
DataTypeEnum.add (  11, "adBoolean");
DataTypeEnum.add (  12, "adVariant");
DataTypeEnum.add (  13, "adIUnknown");
DataTypeEnum.add (  14, "adDecimal");
DataTypeEnum.add (  16, "adTinyInt");
DataTypeEnum.add (  17, "adUnsignedTinyInt");
DataTypeEnum.add (  18, "adUnsignedSmallInt");
DataTypeEnum.add (  19, "adUnsignedInt");
DataTypeEnum.add (  20, "adBigInt");
DataTypeEnum.add (  21, "adUnsignedBigInt");
DataTypeEnum.add (  64, "adFileTime");
DataTypeEnum.add (  72, "adGUID");
DataTypeEnum.add ( 128, "adBinary");
DataTypeEnum.add ( 129, "adChar");
DataTypeEnum.add ( 130, "adWChar");
DataTypeEnum.add ( 131, "adNumeric");
DataTypeEnum.add ( 132, "adUserDefined");
DataTypeEnum.add ( 133, "adDBDate");
DataTypeEnum.add ( 134, "adDBTime");
DataTypeEnum.add ( 135, "adDBTimeStamp");
DataTypeEnum.add ( 136, "adChapter");
DataTypeEnum.add ( 138, "adPropVariant");
DataTypeEnum.add ( 139, "adVarNumeric");
DataTypeEnum.add ( 200, "adVarChar");
DataTypeEnum.add ( 201, "adLongVarChar");
DataTypeEnum.add ( 202, "adVarWChar");
DataTypeEnum.add ( 203, "adLongVarWChar");
DataTypeEnum.add ( 204, "adVarBinary");
DataTypeEnum.add ( 205, "adLongVarBinary");
DataTypeEnum.add (8192, "adArray");

(function(s) {

	try{

		if (s === ""){
			alert("SQL文を範囲選択してください");
			return;
		}
		var conn = new ActiveXObject("ADODB.Connection");
	    var cmd  = new ActiveXObject("ADODB.Command");
		conn.Open(connString);
		var RecordsAffected = 0;

		if((pm = s.match(/^[a-z0-9_]+$/i)) != null){
			s = "SELECT * FROM " + s;
		}

	    cmd.ActiveConnection = conn;
	    cmd.CommandText = s;
	    cmd.CommandType = adCmdText;
	    cmd.Prepared = true;
		cmd.Parameters.Refresh;
		for(var i = 0; i < cmd.Parameters.Count; i++){
			param = cmd.Parameters(i);
			var p = prompt(param.Name + " " + DataTypeEnum(param.Type) + "(" + param.Size + ")[" +param.Precision + "," +param.NumericScale + "]"  , "");
		    param.Value = p;
		}
		if(s.toUpperCase().indexOf("SELECT") == -1){
			if (cmd.Parameters.Count == 0){
				conn.Execute(s, RecordsAffected, adCmdText);
			}else{
				cmd.Execute(RecordsAffected);
			}
			//alert(RecordsAffected + "レコードを更新しました。");
			alert("実行しました。");
		}else{
		    var rs = cmd.Execute();
			var row = new Array();
			var col = new Array();
			var c = 0;
			if(rs.Fields.Count > 0){

				if (!rs.EOF){
					col = new Array();
					for(var i = 0; i < rs.Fields.Count; i++)
						col.push(rs.Fields(i).Name);

					row.push(col.join("\t"));
				}
				if (!rs.EOF){
					col = new Array();
					for(var i = 0; i < rs.Fields.Count; i++)
						col.push(DataTypeEnum(rs.Fields(i).type) + "(" + rs.Fields(i).DefinedSize + ")[" + rs.Fields(i).Precision + "," + rs.Fields(i).NumericScale + "]");

					row.push(col.join("\t"));
				}

				while(!rs.EOF){
					col = new Array();
					for(var i = 0; i < rs.Fields.Count; i++){
						if(rs.Fields(i).Value == null){
							col.push("<null>");
							continue;
						}
						switch(rs.Fields(i).type){
							case 7:
								var d = new Date(rs.Fields(i).Value);
								col.push(d.getYear() + "/" + (d.getMonth()+1) + "/" + d.getDate() + " "
										+ d.getHours() + ":" + d.getMinutes() + ":" + d.getSeconds() + "." + d.getMilliseconds() + "");
								break;
							default:
								col.push(rs.Fields(i).Value);
								break;
						}
					}

					row.push(col.join("\t"));
					rs.MoveNext();

					if(++c >= limit) {
						if (!rs.EOF)
							row.push(limit + "件を超えました");
						break;
					}
				}
			}

			if(row.length ==0){
				alert("結果がありません");
			}else{
				if((output == 1) && (c > outputlimit)){
					editor.newFile();
					document.writeln(row.join("\n"));
				}else{
					OutputBar.clear();
					OutputBar.writeln(row.join("\n"));
					OutputBar.Visible=true;
				}
			}
			rs.close();
		}

		conn.close();

	}catch(e){
		alert(e.description);
	}finally{
		rs = null;
		conn = null;
	}
})(document.selection.text);