HOW TO
Determine supported DBMS SQL functions
Summary
Each DBMS supports its own set of functions. ISQLSyntax::GetFunctionName returns the name of the requested function call for a specific DBMS. In some cases, no name is returned because the requested function is not supported.The following VBA sample code returns a list of valid function calls in the VBA Immediate Window. Although the code is written to run against a Personal Geodatabase, it can readily be modified for Enterprise Geodatabase.
Procedure
Paste the code into the Visual Basic editor in either ArcMap or ArcCatalog, then run the macro SQLTest.Public Sub SQLTest()
' Declare variables
Dim pFact As IWorkspaceFactory
Dim strPath As String
Dim pWorkspace As IWorkspace
Dim pSQLSyntax As ISQLSyntax
Set pFact = New AccessWorkspaceFactory
strPath = "e:\ArcObjects\Data\us.mdb"
Set pWorkspace = pFact.OpenFromFile(strPath, Application.hWnd)
Set pSQLSyntax = pWorkspace ' QI for workspace
'** Each of these returns a Long value which denotes which of the esriSQLPredicates and
'** esriSQLClauses is supported. In either case, a value of -1 indicates all are
'** supported.
Debug.Print "Predicates: " & pSQLSyntax.GetSupportedPredicates
Debug.Print "Supported Clauses: " & pSQLSyntax.GetSupportedClauses
' The functions
Debug.Print "ABS : " & pSQLSyntax.GetFunctionName(esriSQL_ABS)
Debug.Print "ACOS : " & pSQLSyntax.GetFunctionName(esriSQL_ACOS)
Debug.Print "ASCII : " & pSQLSyntax.GetFunctionName(esriSQL_ASCII)
Debug.Print "ASIN : " & pSQLSyntax.GetFunctionName(esriSQL_ASIN)
Debug.Print "ATAN : " & pSQLSyntax.GetFunctionName(esriSQL_ATAN)
Debug.Print "AVG : " & pSQLSyntax.GetFunctionName(esriSQL_AVG)
Debug.Print "BITLENGTH: " & pSQLSyntax.GetFunctionName(esriSQL_BITLENGTH)
Debug.Print "CAST : " & pSQLSyntax.GetFunctionName(esriSQL_CAST)
Debug.Print "CEILING : " & pSQLSyntax.GetFunctionName(esriSQL_CEILING)
Debug.Print "CHAR : " & pSQLSyntax.GetFunctionName(esriSQL_CHAR)
Debug.Print "CHARLENGTH: " & pSQLSyntax.GetFunctionName(esriSQL_CHARLENGTH)
Debug.Print "COALESCE : " & pSQLSyntax.GetFunctionName(esriSQL_COALESCE)
Debug.Print "CONCAT : " & pSQLSyntax.GetFunctionName(esriSQL_CONCAT)
Debug.Print "CONVERT : " & pSQLSyntax.GetFunctionName(esriSQL_CONVERT)
Debug.Print "COS : " & pSQLSyntax.GetFunctionName(esriSQL_COS)
Debug.Print "COSH : " & pSQLSyntax.GetFunctionName(esriSQL_COSH)
Debug.Print "COUNT : " & pSQLSyntax.GetFunctionName(esriSQL_COUNT)
Debug.Print "CURRENTDATE: " & pSQLSyntax.GetFunctionName(esriSQL_CURRENTDATE)
Debug.Print "CURRENTTIME : " & pSQLSyntax.GetFunctionName(esriSQL_CURRENTTIME)
Debug.Print "CURRENTUSER: " & pSQLSyntax.GetFunctionName(esriSQL_CURRENTUSER)
Debug.Print "EXTRACT : " & pSQLSyntax.GetFunctionName(esriSQL_EXTRACT)
Debug.Print "FLOOR : " & pSQLSyntax.GetFunctionName(esriSQL_FLOOR)
Debug.Print "HEX : " & pSQLSyntax.GetFunctionName(esriSQL_HEX)
Debug.Print "LEADING : " & pSQLSyntax.GetFunctionName(esriSQL_LEADING)
Debug.Print "LN : " & pSQLSyntax.GetFunctionName(esriSQL_LN)
Debug.Print "LOG : " & pSQLSyntax.GetFunctionName(esriSQL_LOG)
Debug.Print "LOG10 : " & pSQLSyntax.GetFunctionName(esriSQL_LOG10)
Debug.Print "LOWER : " & pSQLSyntax.GetFunctionName(esriSQL_LOWER)
Debug.Print "MAX : " & pSQLSyntax.GetFunctionName(esriSQL_MAX)
Debug.Print "MIN : " & pSQLSyntax.GetFunctionName(esriSQL_MIN)
Debug.Print "MOD : " & pSQLSyntax.GetFunctionName(esriSQL_MOD)
Debug.Print "OCTETLENGTH : " & pSQLSyntax.GetFunctionName(esriSQL_OCTETLENGTH)
Debug.Print "POSITION : " & pSQLSyntax.GetFunctionName(esriSQL_POSITION)
Debug.Print "POWER : " & pSQLSyntax.GetFunctionName(esriSQL_POWER)
Debug.Print "ROUND : " & pSQLSyntax.GetFunctionName(esriSQL_ROUND)
Debug.Print "SIGN : " & pSQLSyntax.GetFunctionName(esriSQL_SIGN)
Debug.Print "SIN : " & pSQLSyntax.GetFunctionName(esriSQL_SIN)
Debug.Print "SINH : " & pSQLSyntax.GetFunctionName(esriSQL_SINH)
Debug.Print "SOUNDEX : " & pSQLSyntax.GetFunctionName(esriSQL_SOUNDEX)
Debug.Print " STDDEV : " & pSQLSyntax.GetFunctionName(esriSQL_STDDEV)
Debug.Print "SUBSTRING: " & pSQLSyntax.GetFunctionName(esriSQL_SUBSTRING)
Debug.Print "SUBSTRINGBYTES: " & pSQLSyntax.GetFunctionName(esriSQL_SUBSTRINGBYTES)
Debug.Print "SUM : " & pSQLSyntax.GetFunctionName(esriSQL_SUM)
Debug.Print "TAN : " & pSQLSyntax.GetFunctionName(esriSQL_TAN)
Debug.Print "TANH : " & pSQLSyntax.GetFunctionName(esriSQL_TANH)
Debug.Print "TRAILING : " & pSQLSyntax.GetFunctionName(esriSQL_TRAILING)
Debug.Print "TRANSLATE: " & pSQLSyntax.GetFunctionName(esriSQL_TRANSLATE)
Debug.Print "TRIM : " & pSQLSyntax.GetFunctionName(esriSQL_TRIM)
Debug.Print "TRUNCATE : " & pSQLSyntax.GetFunctionName(esriSQL_TRUNCATE)
Debug.Print "UPPER : " & pSQLSyntax.GetFunctionName(esriSQL_UPPER)
Debug.Print "VAR : " & pSQLSyntax.GetFunctionName(esriSQL_VAR)
End Sub
' Declare variables
Dim pFact As IWorkspaceFactory
Dim strPath As String
Dim pWorkspace As IWorkspace
Dim pSQLSyntax As ISQLSyntax
Set pFact = New AccessWorkspaceFactory
strPath = "e:\ArcObjects\Data\us.mdb"
Set pWorkspace = pFact.OpenFromFile(strPath, Application.hWnd)
Set pSQLSyntax = pWorkspace ' QI for workspace
'** Each of these returns a Long value which denotes which of the esriSQLPredicates and
'** esriSQLClauses is supported. In either case, a value of -1 indicates all are
'** supported.
Debug.Print "Predicates: " & pSQLSyntax.GetSupportedPredicates
Debug.Print "Supported Clauses: " & pSQLSyntax.GetSupportedClauses
' The functions
Debug.Print "ABS : " & pSQLSyntax.GetFunctionName(esriSQL_ABS)
Debug.Print "ACOS : " & pSQLSyntax.GetFunctionName(esriSQL_ACOS)
Debug.Print "ASCII : " & pSQLSyntax.GetFunctionName(esriSQL_ASCII)
Debug.Print "ASIN : " & pSQLSyntax.GetFunctionName(esriSQL_ASIN)
Debug.Print "ATAN : " & pSQLSyntax.GetFunctionName(esriSQL_ATAN)
Debug.Print "AVG : " & pSQLSyntax.GetFunctionName(esriSQL_AVG)
Debug.Print "BITLENGTH: " & pSQLSyntax.GetFunctionName(esriSQL_BITLENGTH)
Debug.Print "CAST : " & pSQLSyntax.GetFunctionName(esriSQL_CAST)
Debug.Print "CEILING : " & pSQLSyntax.GetFunctionName(esriSQL_CEILING)
Debug.Print "CHAR : " & pSQLSyntax.GetFunctionName(esriSQL_CHAR)
Debug.Print "CHARLENGTH: " & pSQLSyntax.GetFunctionName(esriSQL_CHARLENGTH)
Debug.Print "COALESCE : " & pSQLSyntax.GetFunctionName(esriSQL_COALESCE)
Debug.Print "CONCAT : " & pSQLSyntax.GetFunctionName(esriSQL_CONCAT)
Debug.Print "CONVERT : " & pSQLSyntax.GetFunctionName(esriSQL_CONVERT)
Debug.Print "COS : " & pSQLSyntax.GetFunctionName(esriSQL_COS)
Debug.Print "COSH : " & pSQLSyntax.GetFunctionName(esriSQL_COSH)
Debug.Print "COUNT : " & pSQLSyntax.GetFunctionName(esriSQL_COUNT)
Debug.Print "CURRENTDATE: " & pSQLSyntax.GetFunctionName(esriSQL_CURRENTDATE)
Debug.Print "CURRENTTIME : " & pSQLSyntax.GetFunctionName(esriSQL_CURRENTTIME)
Debug.Print "CURRENTUSER: " & pSQLSyntax.GetFunctionName(esriSQL_CURRENTUSER)
Debug.Print "EXTRACT : " & pSQLSyntax.GetFunctionName(esriSQL_EXTRACT)
Debug.Print "FLOOR : " & pSQLSyntax.GetFunctionName(esriSQL_FLOOR)
Debug.Print "HEX : " & pSQLSyntax.GetFunctionName(esriSQL_HEX)
Debug.Print "LEADING : " & pSQLSyntax.GetFunctionName(esriSQL_LEADING)
Debug.Print "LN : " & pSQLSyntax.GetFunctionName(esriSQL_LN)
Debug.Print "LOG : " & pSQLSyntax.GetFunctionName(esriSQL_LOG)
Debug.Print "LOG10 : " & pSQLSyntax.GetFunctionName(esriSQL_LOG10)
Debug.Print "LOWER : " & pSQLSyntax.GetFunctionName(esriSQL_LOWER)
Debug.Print "MAX : " & pSQLSyntax.GetFunctionName(esriSQL_MAX)
Debug.Print "MIN : " & pSQLSyntax.GetFunctionName(esriSQL_MIN)
Debug.Print "MOD : " & pSQLSyntax.GetFunctionName(esriSQL_MOD)
Debug.Print "OCTETLENGTH : " & pSQLSyntax.GetFunctionName(esriSQL_OCTETLENGTH)
Debug.Print "POSITION : " & pSQLSyntax.GetFunctionName(esriSQL_POSITION)
Debug.Print "POWER : " & pSQLSyntax.GetFunctionName(esriSQL_POWER)
Debug.Print "ROUND : " & pSQLSyntax.GetFunctionName(esriSQL_ROUND)
Debug.Print "SIGN : " & pSQLSyntax.GetFunctionName(esriSQL_SIGN)
Debug.Print "SIN : " & pSQLSyntax.GetFunctionName(esriSQL_SIN)
Debug.Print "SINH : " & pSQLSyntax.GetFunctionName(esriSQL_SINH)
Debug.Print "SOUNDEX : " & pSQLSyntax.GetFunctionName(esriSQL_SOUNDEX)
Debug.Print " STDDEV : " & pSQLSyntax.GetFunctionName(esriSQL_STDDEV)
Debug.Print "SUBSTRING: " & pSQLSyntax.GetFunctionName(esriSQL_SUBSTRING)
Debug.Print "SUBSTRINGBYTES: " & pSQLSyntax.GetFunctionName(esriSQL_SUBSTRINGBYTES)
Debug.Print "SUM : " & pSQLSyntax.GetFunctionName(esriSQL_SUM)
Debug.Print "TAN : " & pSQLSyntax.GetFunctionName(esriSQL_TAN)
Debug.Print "TANH : " & pSQLSyntax.GetFunctionName(esriSQL_TANH)
Debug.Print "TRAILING : " & pSQLSyntax.GetFunctionName(esriSQL_TRAILING)
Debug.Print "TRANSLATE: " & pSQLSyntax.GetFunctionName(esriSQL_TRANSLATE)
Debug.Print "TRIM : " & pSQLSyntax.GetFunctionName(esriSQL_TRIM)
Debug.Print "TRUNCATE : " & pSQLSyntax.GetFunctionName(esriSQL_TRUNCATE)
Debug.Print "UPPER : " & pSQLSyntax.GetFunctionName(esriSQL_UPPER)
Debug.Print "VAR : " & pSQLSyntax.GetFunctionName(esriSQL_VAR)
End Sub
Article ID:000003669
Software:
- ArcMap 8 x
Get help from ArcGIS experts
Download the Esri Support App
Discover more on this topic
Search for related information
Find training related to this topic
Explore ideas and give feedback