MS SQL Server ODBC - Is there a way to obtain SQL table name syntax?

Author

Is there a way to obtain SQL table name syntax?

Pradeep

2005-08-01, 8:23 pm

Hello,

The application that I am working on uses a database to store
application-specific data. The choice of database server is defined by
the customer via ODBC settings.

The problem that we are running into is that the table name syntax is
not the same among database servers. For example, the following SQL
statement works fine with SQL Server, Excel, etc.

select [c1], [c2] from [t1]

However, for FoxPro, the syntax must be:

select `c1`, `c2` from `t1`

Obviously, the qualifier is needed only if we have spaces in the field
name. However, this is most often the case for us.

Is there a way to obtain the table/column syntax information
dynamically through ODBC?

Thank you in advance for your help.

Pradeep

Scot T Brennecke

2005-08-01, 8:23 pm

This is directly from the ODBC Programmer's Reference in the MSDN Library:
"Quoted Identifiers
In an SQL statement, identifiers containing special characters or match keywords must be enclosed in
identifier quote characters; identifiers enclosed in such characters are known as quoted identifiers
(also known as delimited identifiers in SQL-92). For example, the Accounts Payable identifier is
quoted in the following SELECT statement:

SELECT * FROM "Accounts Payable"The reason for quoting identifiers is to make the statement
parseable. For example, if Accounts Payable was not quoted in the previous statement, the parser
would assume there were two tables, Accounts and Payable, and return a syntax error that they were
not separated by a comma. The identifier quote character is driver-specific and is retrieved with
the SQL_IDENTIFIER_QUOTE
_CHAR option in SQLGetInfo. The lists of special characters and of keywords
are retrieved with the SQL_SPECIAL_CHARACTE
RS and SQL_KEYWORDS options in SQLGetInfo.

To be safe, interoperable applications often quote all identifiers except those for pseudo-columns,
such as the ROWID column in Oracle. SQLSpecialColumns returns a list of pseudo-columns. Also, if
there are application-specific restrictions on where special characters can appear in an object
name, it is best for interoperable applications not to use special characters in those positions."

"Pradeep" <pradeep@tapadiya.net> wrote in message
news:1122923111.675767.234850@g43g2000cwa.googlegroups.com...
>
> Hello,
>
> The application that I am working on uses a database to store
> application-specific data. The choice of database server is defined by
> the customer via ODBC settings.
>
> The problem that we are running into is that the table name syntax is
> not the same among database servers. For example, the following SQL
> statement works fine with SQL Server, Excel, etc.
>
> select [c1], [c2] from [t1]
>
> However, for FoxPro, the syntax must be:
>
> select `c1`, `c2` from `t1`
>
> Obviously, the qualifier is needed only if we have spaces in the field
> name. However, this is most often the case for us.
>
> Is there a way to obtain the table/column syntax information
> dynamically through ODBC?
>
> Thank you in advance for your help.
>
> Pradeep
>

Pradeep

2005-08-02, 3:23 am

Thank you very much. SQL_IDENTIFIER_QUOTE
_CHAR did the trick.

Is there any equivalent way in OLEDB to get this information?

Pradeep

Sophie Guo [MSFT]

2005-08-02, 3:23 am

Hello,

After you open connection you may need to send

SET QUOTED_IDENTIFIER OFF

as your first batch.

I hope the information is helpful.

Sophie Guo
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security

====================
====================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
====================
====================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Sponsored Links