SQL_IDENTIFIER_QUOTE_CHAR equivalent in OleDbLiteral

11 May 2006 7:17 PM

sudha

NETters,

If you have a sql query that has spaces in the table name or the column
name, you need to qualify them using identifier quotes. For example,
this quote prefix and suffix for SQLServer are "[" and "]"
respectively.

OLEDB supports a mechanism to get this metadata using
SQL_IDENTIFIER_QUOTE_CHAR. The facility to get metdata was not
available in .NET 1.1 but now is available in .NET 2.0. However, I
don't see a way to get the identifier quotes using OleDBLiteral
enumeration.

When queried, I see that we can get "Quote_Prefix" and "Quote_Suffix."
Both SQLServer and Oracle are returning double quotes for these values,
which obviously is not right. I think this is used for literal quotes
and not identifier quotes.

I would appreciate it if someone can enlighten me on what I missed.

Thank you in advance for your help.

Sudha

11 May 2006 11:52 PM

Mark Ashton

Actually, double quotes do work as the quote prefix and suffix for MS Sql
Server.

You can get the QuotePrefix and QuoteSuffix in both V1.1 and V2.0 from an
OleDbConnection.
        DataTable table =
connection.GetOleDbSchemaTable(OleDbSchemaGuid.DbInfoLiterals, null);
        string quotePrefix =
(table.Select("LiteralName='Quote_Prefix'")[0])["LiteralValue"] as string;
        string quoteSuffix =
(table.Select("LiteralName='Quote_Suffix'")[0])["LiteralValue"] as string;

Show quote

"sudha" wrote:

> NETters,
>
> If you have a sql query that has spaces in the table name or the column
> name, you need to qualify them using identifier quotes. For example,
> this quote prefix and suffix for SQLServer are "[" and "]"
> respectively.
>
> OLEDB supports a mechanism to get this metadata using
> SQL_IDENTIFIER_QUOTE_CHAR. The facility to get metdata was not
> available in .NET 1.1 but now is available in .NET 2.0. However, I
> don't see a way to get the identifier quotes using OleDBLiteral
> enumeration.
>
> When queried, I see that we can get "Quote_Prefix" and "Quote_Suffix."
> Both SQLServer and Oracle are returning double quotes for these values,
> which obviously is not right. I think this is used for literal quotes
> and not identifier quotes.
>
> I would appreciate it if someone can enlighten me on what I missed.
>
> Thank you in advance for your help.
>
> Sudha
>
>