ArcObjects ISQLSyntax with Oracle DB esriSQL_DelimitedIdentifierPrefix issue

Question

New to stack exchange, heading over from the esri forums, it is great!

I am trying to produce a query that I can use across all workspaces, naturally using ISQLSyntax. The problem I'm having is that when using esriSQL_DelimitedIdentifierPrefix on a Oracle SDE instance it is returning a speech mark, whereas I'd expect it to return a blank string. When I use this code on a FileGDB or an MDB it works as expected, but on SDE I get the error:

Underlying DBMS error[ORA-00904: "table.field_name": invalid identifier][OWNER.TABLE]

The error goes away if I remove the prefix and suffix, so there are no typos in the query. I don't want to do a select case SDE because it seems against the point of having ISQLSyntax.

Any Ideas?

Dim Prefix As string, Suffix As string
Dim upper As String, sQualColName As String
Prefix = pSQLSyntax.GetSpecialCharacter(esriSQLSpecialCharacters.esriSQL_DelimitedIdentifierPrefix)
Suffix = pSQLSyntax.GetSpecialCharacter(esriSQLSpecialCharacters.esriSQL_DelimitedIdentifierSuffix)

sQualColName = pSQLSyntax.QualifyColumnName("table", "field_name")

sVal = Prefix & sQualColName & Suffix & " = " & Chr(39) & Trim(strFindString).ToUpper & Chr(39)

Answer 1

When I tried the same thing in Oracle 11g, it worked fine. The Prefix and Suffix are double quotes just like FGDBs, but I believe they are optional in Oracle.

The difference seems to be that when you use double-quotes around your column identifiers that it becomes case-sensitive, so ensure all column names you write in code are the correct case.

QualifyColumnName doesn't correct the case for you.

Answer 2

Don't use it like that.

When you get a table name from the db and you want its parts, use ParseTableName.

When doing the opposite (i.e you want a fully qualified table name from its parts) use QualifyTableName

The same applies for columns (ParseColumnName, QualifyColumnName)

No need to append anything before or after.