SQL Connector Limits

Suite 7.0/6.0/5.0 and Optimus Suite 5.0
#1

Is there a limitation to the length of the SQL Statement that can be passed? if so what is the length and how do you work around it?

#2

There is no limitations for the length ( it limited only by max-string-length of used script language , which is quite big )

But scheduler may fail to process SQL correctly, if it has complex sub-queries

#3

riddle me this batman…(by they way thanks for your help/assistance)

here is the connector code that works:
Public Overloads Overrides Function CreateConnector(ByVal context As HttpContext) As IdhtmlxConnector
Dim connector As New dhtmlxGridConnector(“SELECT tblSites.SiteName, tblSites.SiteAlias, tblCity.City, tblStates.StateXX, tblSites.SitePhone, tblTimeZones.TimeZoneAbbr FROM tblCity INNER JOIN tblSites ON tblCity.CityID = tblSites.CityID INNER JOIN tblStates ON tblCity.StateID = tblStates.StateID INNER JOIN tblTimeZones ON tblCity.TimeZoneID = tblTimeZones.TimeZoneID”, _
“SiteID”, _
dhtmlxDatabaseAdapterType.SqlServer2005, ConfigurationManager.ConnectionStrings(“NOCdb”).ConnectionString, “SiteID”)
If context.Request.QueryString(“dynamic”) IsNot Nothing Then
connector.SetDynamicLoading(Convert.ToInt32(context.Request.QueryString(“dynamic”)))
End If
Return connector
End Function

Here is the code that doesn’t:
Public Overloads Overrides Function CreateConnector(ByVal context As HttpContext) As IdhtmlxConnector
Dim connector As New dhtmlxGridConnector(“SELECT tblSites.SiteName, tblSites.SiteAlias, tblCity.City, tblStates.StateXX, tblSites.SitePhone, tblTimeZones.TimeZoneAbbr, tblSites.SiteMMS, tblCADVersion.CADVersion, tblPCRVersion.ePCRVersion, tblSites.SiteID FROM tblCity INNER JOIN tblSites ON tblCity.CityID = tblSites.CityID INNER JOIN tblContacts ON tblSites.ContactID = tblContacts.ContactID INNER JOIN tblStates ON tblCity.StateID = tblStates.StateID INNER JOIN tblTimeZones ON tblCity.TimeZoneID = tblTimeZones.TimeZoneID INNER JOIN tblCADVersion ON tblSites.CADID = tblCADVersion.CADID INNER JOIN tblPCRVersion ON tblSites.ePCRID = tblPCRVersion.ePCRID”, _
“SiteID”, _
dhtmlxDatabaseAdapterType.SqlServer2005, ConfigurationManager.ConnectionStrings(“NOCdb”).ConnectionString, “SiteID”)
If context.Request.QueryString(“dynamic”) IsNot Nothing Then
connector.SetDynamicLoading(Convert.ToInt32(context.Request.QueryString(“dynamic”)))
End If
Return connector
End Function

the only difference is the number of INNER JOINS

#4

Hi Stalling

I suggesting you to do the following:

  1. Enable logs from connector. It will show you the exact SQL’s are played.
  2. Enable verbose javascript errors in browser. You’ll see alerts indicating the problems.

I’m using long SQL clauses here and I seen similar problems, but in fact all of them were inspired by my mistakes :wink: But these two points helped me to find them and fix.