.DoQuery length

[Former Member]

SAP B1, DI API. MSSQL_2008

What is the maximum length of the expression in the .DoQuery?

For T-SQL, there are limits, but those are not based on string length, but on number of clauses (see https://msdn.microsoft.com/en-us/library/dd776382.aspx for the limit of 1000 Values rows in a given Insert Into statement).

For .Net, the limit on string length is over 1,000,000,000 characters (http://stackoverflow.com/questions/140468/).

Are there any DI specific limits on the expression length, in a .DoQuery expression?

[pedro_magueija]

Hi David,

I think any underlying limitations (of the database system) are "inherited" by .DoQuery.

There are some "remarks" regarding the use of Recordset object, but none related to the length of the expression. You can find this information in the SDK Help Center.

Below are the "remarks" I mentioned:

Browsing Mechanisms

The Recordset object includes two browsing mechanisms: the first mechanism applies to result sets that contain only one row (by the use of Select Top), which will retrieve only one record each time. Otherwise, the browsing will be performed on existing result set only.

DML Operations on Database Tables

The Recordset object allows the following Data Manipulation Language (DML) operations: UPDATE, INSERT, and DELETE.

DML operations are acceptable with the Recordset object for user tables only. For other business objects use only the relevant DI objects and not the Recordset object. Any DML operations on system tables pose a high risk for data corruption, and will not be supported. Use at your own risk.

Before the Recordset object executes an SQL query, it validates the user permission (same user permission as in SAP Business One). Otherwise, the SQL query is blocked.

Blocking DDL Actions

The following Data Definition Language (DDL) actions are blocked: CREATE, DROP, ALTER, and TRUNCATE. The reason for that is, when upgrading the SAP Business One application, it ignores user tables that are not created using the DI meta data objects (UserTablesMD and UserFieldsMD).

Limitation

Only one expression can be specified in the select list when the sub-query is not introduced with EXISTS.