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)

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.

|improve this answer|||||
  • Many thanks blah, you are right about the case. One further thing, when using the prefix I found that using the fully qualified name no longer worked (i.e TABLE.COLUMN). Cheers for all your help – David Wilton Nov 7 '11 at 8:58

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.

|improve this answer|||||
  • Does QualifyColumnName add prefix/suffix itself? E.g. brackets for personal GDB, double-quotes for SHP and FGDB, etc. I can't remember but I don't think it does. Sounds like the goal is to be able to write a where clause. – blah238 Nov 3 '11 at 10:56
  • blah is correct, I'm not after the parts of the field name or the combined and QualifyColumnName does not bring back the delimitors. What I need is for the esriSQL_DelimitedIdentifierSuffix to return [ for Personal Geodatabase, " for File and then a blank string for SDE. The goal is to write a where clause with the correct delimiters, which I think this should do. It just doesn't return the correct value for SDE. – David Wilton Nov 3 '11 at 11:07
  • It occurred to me that it might be because I was returning the value to a char and chars can't be blank. I tested with a string and it still doesn't work – David Wilton Nov 3 '11 at 14:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.