English

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