ExcelVBAでSQL
Option Explicit
Private Const adOpenDynamic As Long = 2
Private Const adLockOptimistic As Long = 3
Private Const adStateClosed As Long = 0
Sub ボタン1_Click()
Call DataGetSample
End Sub
'==========================================================
'コネクションを返す
'==========================================================
Public Function GetXLSConnection(DataSource As String) As Object
Dim objCN As Object
Dim strCNString As String
Set objCN = CreateObject("ADODB.Connection")
strCNString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & DataSource & ";" _
& "Extended Properties=""Excel 8.0;" _
& "HDR=Yes"";"
objCN.Open strCNString
Set GetXLSConnection = objCN
End Function
'==========================================================
'レコードセットを返す
'==========================================================
Public Function GetRecordSet(strSQL As String, objCN As Object) As Object
Dim objRS As Object
Set objRS = CreateObject("ADODB.Recordset")
objRS.Open strSQL, objCN, adOpenDynamic, adLockOptimistic
Set GetRecordSet = objRS
End Function
'==========================================================
'コネクション破棄
'==========================================================
Public Sub CloseConnection(objCN As Object)
If objCN.State <> adStateClosed Then
objCN.Close
End If
Set objCN = Nothing
End Sub
'==========================================================
'レコードセット破棄
'==========================================================
Public Sub CloseRecordSet(objRS As Object)
If objRS.State <> adStateClosed Then
objRS.Close
End If
Set objRS = Nothing
End Sub
'==========================================================
'データ取得サンプル
'==========================================================
Public Sub DataGetSample()
Dim objCN As Object
Dim objRS As Object
Dim strSQL As String
Dim lngF As Long
'コネクションを確立
Set objCN = GetXLSConnection(ThisWorkbook.FullName)
'抽出条件を作成
strSQL = "SELECT" '抽出フィールド(項目)を指定
strSQL = strSQL & " [header1]"
strSQL = strSQL & ", [header2]"
strSQL = strSQL & ", [header3]"
strSQL = strSQL & " FROM [Sheet1$]" 'データテーブルを指定
strSQL = strSQL & " WHERE 1 = 1" '抽出条件
'strSQL = strSQL & " AND [header2] >= 20"
strSQL = strSQL & " AND [header3] = '東海'"
'抽出実行
Set objRS = GetRecordSet(strSQL, objCN)
'抽出結果を出力
With wsXLSDataBase
With .Range("rngXDB_DataTop")
'出力エリアにある既存データを消去
.CurrentRegion.ClearContents
'フィールド(項目)名を出力
For lngF = 0 To objRS.Fields.Count - 1
.Offset(, lngF).Value = objRS.Fields(lngF).Name
Next lngF
'データを出力
.Offset(1).CopyFromRecordset objRS
End With
End With
END_PROC:
'レコードセットを閉じる
Call CloseRecordSet(objRS)
'コネクションを閉じる
Call CloseConnection(objCN)
End Sub
jQuery get再帰実行メモ
var dlUrlList = []; dlUrlList.push('https://www.amazon.co.jp'); dlUrlList.push('https://www.google.co.jp/?gws_rd=ssl'); dlUrlList.push('https://www.yahoo.co.jp'); var i = 0; function dl() { $.get(dlUrlList[i]).done(doneDL).fail(doneDL); console.log('num:' + i + ' , url:' + dlUrlList[i]); } function doneDL() { if(i === 3){ return; } i++; dl(); } dl();
SqlUtil.cs
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace SQLFormatter { class SqlUtil { /** * 文字列を置き換える. 同一文字列内に複数の変換対象があってもよい。 * * @param argTargetString * 処理対象となる文字列。 * @param argFrom * 変換前の文字列。(ex." <") * @param argTo * 変換後の文字列。(ex."<") * @return 置換された後の文字列。 */ public static String replace(String argTargetString, String argFrom, String argTo) { String newStr = ""; int lastpos = 0; for (;;) { int pos = argTargetString.IndexOf(argFrom, lastpos); if (pos == -1) { break; } newStr += argTargetString.Substring(lastpos, pos); newStr += argTo; lastpos = pos + argFrom.Length; } return newStr + argTargetString.Substring(lastpos); } } }
SqlTokenConstants.cs
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace SQLFormatter { class SqlTokenConstants { /** * 項目番号:1<br> * 空文字. TAB,CR等も1つの文字列として含む。 */ public static readonly int SPACE = 0; /** * 項目番号:2<br> * 記号. " <="のような2つで1つの記号もある。 */ public static readonly int SYMBOL = 1; /** * 項目番号:3<br> * キーワード. "SELECT", "ORDER"など. */ public static readonly int KEYWORD = 2; /** * 項目番号:4<br> * 名前. テーブル名、列名など。ダブルクォーテーションが付く場合がある。 */ public static readonly int NAME = 3; /** * 項目番号:5<br> * 値. 数値(整数、実数)、文字列など。 */ public static readonly int VALUE = 4; /** * 項目番号:6<br> * コメント. シングルラインコメントとマルチラインコメントがある。 */ public static readonly int COMMENT = 5; /** * 項目番号:7<br> * SQL文の終わり. */ public static readonly int END = 6; /** * 項目番号:8<br> * 解析不可能なトークン. 通常のSQLではありえない。 */ public static readonly int UNKNOWN = 7; } }
SqlToken .cs
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace SQLFormatter { class SqlToken : AbstractSqlToken { /** * SQLトークンのインスタンスを作成します。 * * @param argType * @param argString * @param argPos */ public SqlToken(int argType, String argString, int argPos) { setType(argType); setString(argString); setPos(argPos); } /** * SQLトークンのインスタンスを作成します。 * * @param argType * @param argString */ public SqlToken(int argType, String argString) :this(argType, argString, -1) { } } }
SqlRule.cs
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace SQLFormatter { class SqlRule { /** キーワードの変換規則. */ public int keyword = KEYWORD_UPPER_CASE; /** キーワードの変換規則:何もしない */ public const int KEYWORD_NONE = 0; /** キーワードの変換規則:大文字にする */ public const int KEYWORD_UPPER_CASE = 1; /** キーワードの変換規則:小文字にする */ public const int KEYWORD_LOWER_CASE = 2; /** * インデントの文字列. 設定は自由入力とする。通常は " ", " ", "\t" のいずれか。 */ public String indentString = " "; /** * 関数の名前。 */ private String[] fFunctionNames = null; public void setKeywordCase(int keyword) { this.keyword = keyword; } /** * 関数の名前か? * * @param name * 調べる名前 * @return 関数の名前のとき、<code>true</code> を返す。 */ public bool isFunction(String name) { if (fFunctionNames == null) return false; for (int i = 0; i < fFunctionNames.Length; i++) { if(fFunctionNames[i].Equals(name, StringComparison.CurrentCultureIgnoreCase)) return true; } return false; } /** * 関数の名前の配列を登録します。 * * @param names * 関数名の配列。null可。 */ public void setFunctionNames(String[] names) { fFunctionNames = names; } } }
SqlParser.cs
using System; using System.Collections; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace SQLFormatter { class SqlParser { /** * 解析前の文字列 */ private String fBefore; /** * 解析中の文字。 */ private char fChar; /** * 解析中の位置 */ private int fPos; /** * 2文字からなる記号。 * * なお、|| は文字列結合にあたります。 */ private static String[] twoCharacterSymbol = { "<>", "<=", ">=", "||" }; /** * パーサのインスタンスを作成します。 */ public SqlParser() { } /** * 与えられた文字が、ホワイトスペース文字かどうかを判定します。 * * @param argChar * @return */ public static bool isSpace(char argChar) { // 2005.07.26 Tosiki Iga \r も処理範囲に含める必要があります。 // 2005.08.12 Tosiki Iga 65535(もとは-1)はホワイトスペースとして扱うよう変更します。 return argChar == ' ' || argChar == '\t' || argChar == '\n' || argChar == '\r' || argChar == 65535; } /** * 文字として認識して妥当かどうかを判定します。 * * 全角文字なども文字として認識を許容するものと判断します<br> * ※このメソッドはBlancoSqlEditorPluginから参照されます。 * * @param argChar * @return */ public static bool isLetter(char argChar) { // SQLにおいて アンダースコアは英字の仲間です. // blanco において # は英字の仲間です. // ここに日本語も含めなくてはならない。 // return ('A' <= c && c <= 'Z') || ('a' <= c && c <= 'z') // || (c == '_' || c == '#'); if (isSpace(argChar)) { return false; } if (isDigit(argChar)) { return false; } if (isSymbol(argChar)) { return false; } return true; } /** * 数字かどうかを判定します。 * * @param argChar * @return */ public static bool isDigit(char argChar) { return '0' <= argChar && argChar <= '9'; } /** * 記号かどうかを判定します。 * * @param argChar * @return */ public static bool isSymbol(char argChar) { switch (argChar) { case '"': // double quote case '?': // question mark case '%': // percent case '&': // ampersand case '\'': // quote case '(': // left paren case ')': // right paren case '|': // vertical bar case '*': // asterisk case '+': // plus sign case ',': // comma case '-': // minus sign case '.': // period case '/': // solidus case ':': // colon case ';': // semicolon case '<': // less than operator case '=': // equals operator case '>': // greater than operator // blancoでは # は文字列の一部です case '#': // アンダースコアは記号とは扱いません case '_': //underscore // これ以降の文字の扱いは保留 // case '!': // case '$': // case '[': // case '\\': // case ']': // case '^': // case '{': // case '}': // case '~': return true; default: return false; } } /** * トークンを次に進めます。 * * posを進める。sに結果を返す。typeにその種類を設定する。 * * 不正なSQLの場合、例外が発生します。 ここでは、文法チェックは行っていない点に注目してください。 * * @return トークンを返す. */ SqlToken nextToken() { int start_pos = fPos; if (fPos >= fBefore.Length) { fPos++; return new SqlToken(SqlTokenConstants.END, "", start_pos); } fChar = fBefore[fPos]; if (isSpace(fChar)) { String workString = ""; for (;;) { workString += fChar; fChar = fBefore[fPos]; if (!isSpace(fChar)) { return new SqlToken(SqlTokenConstants.SPACE, workString, start_pos); } fPos++; if (fPos >= fBefore.Length) { return new SqlToken(SqlTokenConstants.SPACE, workString, start_pos); } } } else if (fChar == ';') { fPos++; // 2005.07.26 Tosiki Iga セミコロンは終了扱いではないようにする。 return new SqlToken(SqlTokenConstants.SYMBOL, ";", start_pos); } else if (isDigit(fChar)) { String s = ""; while (isDigit(fChar) || fChar == '.') { // if (ch == '.') type = Token.REAL; s += fChar; fPos++; if (fPos >= fBefore.Length) { // 長さを超えている場合には処理中断します。 break; } fChar = fBefore[fPos]; } return new SqlToken(SqlTokenConstants.VALUE, s, start_pos); } else if (isLetter(fChar)) { String s = ""; // 文字列中のドットについては、文字列と一体として考える。 while (isLetter(fChar) || isDigit(fChar) || fChar == '.') { s += fChar; fPos++; if (fPos >= fBefore.Length) { break; } fChar = fBefore[fPos]; } for (int i = 0; i < SqlConstants.SQL_RESERVED_WORDS.Length; i++) { if(string.Compare(s, SqlConstants.SQL_RESERVED_WORDS[i], true) == 0){ return new SqlToken(SqlTokenConstants.KEYWORD, s, start_pos); } } return new SqlToken(SqlTokenConstants.NAME, s, start_pos); } // single line comment else if (fChar == '-') { fPos++; char ch2 = fBefore[fPos]; // -- じゃなかったとき if (ch2 != '-') { return new SqlToken(SqlTokenConstants.SYMBOL, "-", start_pos); } fPos++; String s = "--"; for (;;) { fChar = fBefore[fPos]; s += fChar; fPos++; if (fChar == '\n' || fPos >= fBefore.Length) { return new SqlToken(SqlTokenConstants.COMMENT, s, start_pos); } } } // マルチラインコメント else if (fChar == '/') { fPos++; char ch2 = fBefore[fPos]; // /* じゃなかったとき if (ch2 != '*') { return new SqlToken(SqlTokenConstants.SYMBOL, "/", start_pos); } String s = "/*"; fPos++; int ch0 = -1; for (;;) { ch0 = fChar; fChar = fBefore[fPos]; s += fChar; fPos++; if (ch0 == '*' && fChar == '/') { return new SqlToken(SqlTokenConstants.COMMENT, s, start_pos); } } } else if (fChar == '\'') { fPos++; String s = "'"; for (;;) { fChar = fBefore[fPos]; s += fChar; fPos++; if (fChar == '\'') { return new SqlToken(SqlTokenConstants.VALUE, s, start_pos); } } } else if (fChar == '\"') { fPos++; String s = "\""; for (;;) { fChar = fBefore[fPos]; s += fChar; fPos++; if (fChar == '\"') { return new SqlToken(SqlTokenConstants.NAME, s, start_pos); } } } else if (isSymbol(fChar)) { // 記号 String s = "" + fChar; fPos++; if (fPos >= fBefore.Length) { return new SqlToken(SqlTokenConstants.SYMBOL, s, start_pos); } // 2文字の記号かどうか調べる char ch2 = fBefore[fPos]; for (int i = 0; i < twoCharacterSymbol.Length; i++) { if (twoCharacterSymbol[i][0] == fChar && twoCharacterSymbol[i][1] == ch2) { fPos++; s += ch2; break; } } return new SqlToken(SqlTokenConstants.SYMBOL, s, start_pos); } else { fPos++; return new SqlToken(SqlTokenConstants.UNKNOWN, "" + fChar, start_pos); } } /** * SQL文字列をトークンの配列に変換します。 * * @param argSql * 変換前のSQL文 * @return Tokenの配列 */ public List<SqlToken> parse(String argSql) { fPos = 0; fBefore = argSql; List<SqlToken> list = new List<SqlToken>(); for (; ; ) { SqlToken token = nextToken(); if (token.getType() == SqlTokenConstants.END) { break; } list.Add(token); } return list; } //public ArrayList parse(String argSql) { // fPos = 0; // fBefore = argSql; // ArrayList list = new ArrayList(); // for (;;) { // SqlToken token = nextToken(); // if (token.getType() == SqlTokenConstants.END) { // break; // } // list.Add(token); // } // return list; //} } }