vba SQL 对于字符串太长
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9764234/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL too long for String
提问by Kyle
I have the following SQL to be queried. It is a valid SQL. Unfortunately, it is too long for a string in VBA. Anyone knows of a workaround to run this query?
我有以下 SQL 需要查询。这是一个有效的 SQL。不幸的是，VBA 中的字符串太长了。任何人都知道运行此查询的解决方法？
SQL = "SELECT A.cust_ky, A.incid_id, A.OPEN_TS, A.CLOSE_TS, A.REC_UPD_TS, B.wrkgp_id, A.CURR_AGNT_KY, A.incid_ttl_dn " _
& "FROM (MAINTBLS.INCID_FAB A INNER JOIN MAINTBLS.DEPTMNT B ON A.curr_wrkgp_ky=B.wrkgp_ky) " _
& "WHERE B.wrkgp_id='" & wrkgp & "' And (A.open_fg = 1 OR A.pend_fg = 1)" _
& "ORDER BY A.cust_ky, A.curr_agnt_ky ASC"
rs.Open SQL, con, adOpenKeyset
回答by user158017
Since you use Oracle, you should use a bind variable instead of dynamic SQL and then set the value in the parameter collection of the command object. Not only will it prevent SQL Injection, but it will better optimize your query.
由于您使用Oracle，您应该使用绑定变量而不是动态SQL，然后在命令对象的参数集合中设置值。它不仅可以防止 SQL 注入，而且可以更好地优化您的查询。
Also, it looks like your SQL Statement is missing a space before the order by clause. That could easily cause your error. See below - untested, but should give you the idea.
此外，看起来您的 SQL 语句在 order by 子句之前缺少一个空格。这很容易导致您的错误。见下文 - 未经测试，但应该给你的想法。
SQL = "SELECT A.cust_ky, A.incid_id, A.OPEN_TS, A.CLOSE_TS, A.REC_UPD_TS, B.wrkgp_id, A.CURR_AGNT_KY, A.incid_ttl_dn " _
& "FROM (MAINTBLS.INCID_FAB A INNER JOIN MAINTBLS.DEPTMNT B ON A.curr_wrkgp_ky=B.wrkgp_ky) " _
& "WHERE B.wrkgp_id= :wrkgp And (A.open_fg = 1 OR A.pend_fg = 1) " _
& "ORDER BY A.cust_ky, A.curr_agnt_ky ASC"
With cmd
.ActiveConnection = conn
.CommandText = SQL
.CommandType = adCmdText
.Parameters.Append .CreateParameter(, adVarChar, adParamInput, wrkgp)
End With
回答by steve
Create a view for the query, something like this
为查询创建一个视图，像这样
create view fix_for_broken_vba as
SELECT A.cust_ky, A.incid_id, A.OPEN_TS, A.CLOSE_TS, A.REC_UPD_TS, B.wrkgp_id,
A.CURR_AGNT_KY, A.incid_ttl_dn FROM (MAINTBLS.INCID_FAB A INNER JOIN MAINTBLS.DEPTMNT B ON A.curr_wrkgp_ky=B.wrkgp_ky)
WHERE (A.open_fg = 1 OR A.pend_fg = 1)
and then rewrite the query accordingly.
然后相应地重写查询。
回答by alberic
If you are using VBA in an Excel sheet, why don't you consider putting the SQL query in a protected cell of your document? You could even put it in an hidden sheet of your document.
如果您在 Excel 工作表中使用 VBA，为什么不考虑将 SQL 查询放在文档的受保护单元格中？您甚至可以将其放在文档的隐藏表中。
Something like:
就像是：
Cells(6, 2).Select
sqlString = Cells(6, 2).Value
I did it and it works like a charm in my case.
我做到了，在我的情况下它就像一个魅力。
回答by Skidemon
I've used the following steps for a 100+ line query
我对 100+ 行查询使用了以下步骤
Use a long Query with Excel VBA
在 Excel VBA 中使用长查询
- Initially create the query in SSMS
- Create a sheet called SQL Query
- Copy and paste the query in to the SQL Query sheet and delete all rows that are blank or commented row
- Incorporate any variables in to the declare statement
Use the following macro to loop through the SQL Query sheet
LastRowQ = ThisWorkbook.Sheets("SQL Query").Cells(ThisWorkbook.Sheets("SQL Query").Rows.Count, "B").End(xlUp).Row Count = 2 Do Until Count > LastRowQ SQLQTemp = ThisWorkbook.Sheets("SQL Query").Range("B" & Count) SQLQuery = SQLQuery & " " & SQLQTemp Count = Count + 1 Loop SQLDatabaseRS.Open SQLQuery
- 最初在 SSMS 中创建查询
- 创建一个名为 SQL Query 的工作表
- 将查询复制并粘贴到 SQL 查询表中并删除所有空白行或注释行
- 将任何变量合并到声明语句中
使用以下宏循环遍历 SQL 查询表
LastRowQ = ThisWorkbook.Sheets("SQL Query").Cells(ThisWorkbook.Sheets("SQL Query").Rows.Count, "B").End(xlUp).Row Count = 2 Do Until Count > LastRowQ SQLQTemp = ThisWorkbook.Sheets("SQL Query").Range("B" & Count) SQLQuery = SQLQuery & " " & SQLQTemp Count = Count + 1 Loop SQLDatabaseRS.Open SQLQuery
