0

Given an ODBC connection string, I'm trying to determine whether this connection supports cross joining.

Currently I use the following code to determine the supported join operators:

(SupportedJoinOperators)connection.GetSchema("DataSourceInformation").Rows[0]["SupportedJoinOperators"]

But this only gives me Inner, Left, Right and Full joins.

For example, if a connection string uses the microsoft text driver (.txt .csv), the supported join operators are left and right, and the driver actually doesn't support the cross join operator (fails with a message of incorrect syntax).

Using workarounds doesn't guarantee a correct answer, as I gather from this link.

I also thought of trying a lower level, like using Pinvoke with the ODBC API like SqlGetInfo or SqlGetFunctions but it seems like a dead end.

Any help will be greatly appreciated!

Jony Adamit
  • 3,178
  • 35
  • 44
  • Are you sure that a cross join is not the same as a full join? – Dan Bracuk Aug 06 '15 at 16:09
  • Not exactly the same, see http://stackoverflow.com/questions/3228871/sql-server-what-is-the-difference-between-cross-join-and-full-outer-join. But I'll nevertheless see if that's enough for my requirements – Jony Adamit Aug 06 '15 at 16:32

1 Answers1

1

If I get it right, then using the ODBC API you should be able to query that using the attribute SQL_SQL92_RELATIONAL_JOIN_OPERATORS, testing for SQL_SRJO_CROSS_JOIN. From the description at https://msdn.microsoft.com/en-us/library/ms711681%28v=vs.85%29.aspx:

SQL_SQL92_RELATIONAL_JOIN_OPERATORS(ODBC 3.0)

An SQLUINTEGER bitmask enumerating the relational join operators supported in a SELECT statement, as defined in SQL-92.

The SQL-92 or FIPS conformance level at which this feature must be supported is shown in parentheses next to each bitmask.

The following bitmasks are used to determine which options are supported by the data source:

SQL_SRJO_CORRESPONDING_CLAUSE (Intermediate level)SQL_SRJO_CROSS_JOIN (Full level)SQL_SRJO_EXCEPT_JOIN (Intermediate level)SQL_SRJO_FULL_OUTER_JOIN (Intermediate level) SQL_SRJO_INNER_JOIN (FIPS Transitional level)SQL_SRJO_INTERSECT_JOIN (Intermediate level)SQL_SRJO_LEFT_OUTER_JOIN (FIPS Transitional level)SQL_SRJO_NATURAL_JOIN (FIPS Transitional level)SQL_SRJO_RIGHT_OUTER_JOIN (FIPS Transitional level)SQL_SRJO_UNION_JOIN (Full level)

SQL_SRJO_INNER_JOIN indicates support for the INNER JOIN syntax, not for the inner join capability. Support for the INNER JOIN syntax is FIPS TRANSITIONAL, whereas support for the inner join capability is ENTRY.

erg
  • 1,632
  • 1
  • 11
  • 23
  • 1
    Hi @erg, Thank you for your answer. Actually, hidden in your answer is the easier solution that escaped me: the SQL Level is written in parentheses next to each join: `Entry, Intermediate, FIPS Transitional and Full`. Judging by this, cross join belongs to the full sql level (I have verified it by reading this pdf: http://www.databaser.net/moniwiki/pds/DatabaseSystem/SQL99.pdf. Since I already have this parameter by using `SqlDrivers` I'm relieved from meddling with `SqlGetInfo`, and my problem is solved! In any way, your answer is correct :-) – Jony Adamit Aug 10 '15 at 09:24
  • Yes, that makes sense. I'll try to remember that for the next time :) – erg Aug 12 '15 at 11:50