VBA中的ODBCConnection.CommandText字符串的最大长度是否有解决方法? [英] Is there a workaround for the maximum length of an ODBCConnection.CommandText string in VBA?

问题描述

我有一个VBA脚本，可为Excel中的SAP HANA ODBC连接生成查询字符串.该查询由用户输入确定，并且长度可能会大不相同.该查询本身使用通过UNION ALL语法彼此附加的相似查询的许多版本.

I have a VBA script that generates a query string for a SAP HANA ODBC Connection in Excel. The query is determined by user inputs and can vary greatly in length. The query itself uses many versions of a similar query appended to one another using UNION ALL syntax.

该脚本有时在尝试刷新时会引发运行时错误.根据我的研究，很清楚，其原因是CommandText字符串超过了最大允许长度32,767(

The script sometimes throws a runtime error when trying to refresh. From my research, it has become clear that the reason for this is that the CommandText string exceeds a maximum allowed length of 32,767 (https://ask.sqlservercentral.com/questions/50819/too-long-sql-in-excel-vba.html).

我想知道是否有一种解决方法，而不是使用存储过程(如果有一种方法可以在运行时创建存储过程然后执行它，我不反对这样做，但是我无法使用预定义的存储过程，因为我的查询始终是不同的，因此需要VBA创建它)

I wondered whether there is a workaround for this, other than using a stored procedure (I am not against this if there is a way to create a stored procedure at runtime then execute it, but I cannot use a predefined stored procedure as my query is always different hence the need for VBA to create it)

有关VBA中动态查询的更多信息:

Some more info about the dynamic query in VBA:

• 列名以及参数是动态创建的，每次都可以不同

• Column names, as well as parameters, are created dynamically and can be different every time

该查询使用产品编号列表的组为每个产品组生成一个IN语句，然后将这些产品的销售额加到该组的名称下.然后将所有这些都合并在一起，以创建一个具有分组记录的表

The query uses groups of lists of product numbers to generate an IN statement for each product group, then sums the sales for those products under the name of the group. These are then all UNION'd together to create one table with grouped records

用户输入示例:

查询结果示例:

WITH SOME_CTE (SOME_FIELDS) AS 
(SELECT SOME_STUFF
FROM SOME_TABLE
WHERE SOME_STUFF_IS_GOING_ON)

SELECT GEND "Gender", 'Attribute 1'  "Attribute", SUM(UNITS) "Units", SUM(VAL) "Value", SUM(MARGIN) "Margin"
FROM KPI_SUM
WHERE PRODUCT IN ('12345', '23456', '34567', '45678')
GROUP BY GEND

UNION ALL

SELECT GEND, 'Attribute 2'  ATTR_NAME, SUM(UNITS), SUM(VAL), SUM(MARGIN)
FROM KPI_SUM
WHERE PRODUCT IN ('01234', '02345', '03456', '03567')
GROUP BY GEND
ORDER BY "Gender", "Attribute"

...等等.

如您所见，有2个属性组，每个属性组包含4个产品，这没问题，但是当我们达到30个，每个都有数百个产品时，可能就太长了.

As you can see, with 2 attribute groups containing 4 products each there is no problem, but when we get to about 30 with several hundred each, it could be too long.

注意:我已经尝试过将查询字符串的重复部分中的字段引用缩短为1个字符等操作，这有帮助但不能解决问题.
任何帮助将不胜感激.

Note: I have tried things like shortening field references in the repeated parts of the query string to 1 character etc. which helps but does not solve the problem.
Any help would be greatly appreciated.

推荐答案

一种解决方法是发送多个查询.由于您使用的是union all，因此您可以在每次select语句(即

One workaround is to send multiple queries. Since you are using union all, you could execute every time single select statement, i.e.

在(例如)主数据库中创建表(不要创建临时表！因为在每次查询后它们都会被删除)-但在此之前，请确保创建新表，因此如果存在则删除旧表(也删除完成后的表格).现在，您将每个select语句更改为insert语句，这会将记录插入到所谓的临时表中.

create table in (for example) master database (don't create temporary tables! as they will be dropped after every query) - but before that, make sure you create new table, so delete old one if exists (also drop the table after you are done with it). Now every single select statement you'll change to insert statement, which will insert records to your so-called temporary table.

这样，您将避免冗长的查询，而只发送单个insert .. into.. select语句.

This way, you'll avoid lengthy queries, you'll just send single insert .. into.. select statements.

最后，要获取所有结果，您只需要简单的select查询.获取此数据后，您应该删除该表，因为它不再需要.

At the end, to get all results, you just need simple select query. After getting this data, you should drop that table, as it's no longer needed.

这篇关于VBA中的ODBCConnection.CommandText字符串的最大长度是否有解决方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！