Problem with MSACCESS ODBC Driver

Problem with MSACCESS ODBC Driver

Post by bullshar » Wed, 18 Dec 2002 03:42:18



TableA: Idx...CLongBinaryColumn...CIdx...

TableB: Idx Text

TableC: Idx TableBIdx Text

Select TableA.*, B.Text, C.Text FROM
   TableB RIGHT JOIN( TableC RIGHT JOIN TableA ON TableC.Idx = TableA.CIdx )
     ON TableB.Idx = TableC.TableBIdx

When using ODBC and running debug, the driver consistently reports:

[Microsoft][ODBC Microsoft Access Driver] The specified field '`Idx`' could
refer to more than one table listed in the FROM clause of your SQL statement.

...ONLY while trying to access the CLongBinary column via CFieldExchange::fixup.
The actual execution of the query is unremarkable, and all data is returned as
expected, except the CLongBinary column, which happens to be empty on the
first record.

If the Select is reduced to 'SELECT * from TableA' (with changes to
DoFieldExchange() and the m_nFields attribute as appropriate), everything
works normally...

Either Select statement works fine in MSAccess or in an OleDB context.

This really begs two questions:

1) Why is Idx being considered ambiguous when it is not(why only CLongBinary columns)?
2) Am I bashing my head against a wall by attempting to use Appwizard
defined strategies to do anything meaningful with databases?

As far as question 2 is concerned, I am well experienced in C, C++, and Java,
but rather new to MFC. As such, I thought that letting MS steer me in their
use would be a good idea.

--
bullshark

 
 
 

Problem with MSACCESS ODBC Driver

Post by Georg Kreyerhof » Wed, 18 Dec 2002 05:11:02


bullshark schrieb:
Quote:

> TableA: Idx...CLongBinaryColumn...CIdx...

> TableB: Idx Text

> TableC: Idx TableBIdx Text

> Select TableA.*, B.Text, C.Text FROM
>    TableB RIGHT JOIN( TableC RIGHT JOIN TableA ON TableC.Idx = TableA.CIdx )
>      ON TableB.Idx = TableC.TableBIdx

> When using ODBC and running debug, the driver consistently reports:

> [Microsoft][ODBC Microsoft Access Driver] The specified field '`Idx`' could
> refer to more than one table listed in the FROM clause of your SQL statement.

> ...ONLY while trying to access the CLongBinary column via CFieldExchange::fixup.
> The actual execution of the query is unremarkable, and all data is returned as
> expected, except the CLongBinary column, which happens to be empty on the
> first record.

> If the Select is reduced to 'SELECT * from TableA' (with changes to
> DoFieldExchange() and the m_nFields attribute as appropriate), everything
> works normally...

> Either Select statement works fine in MSAccess or in an OleDB context.

> This really begs two questions:

> 1) Why is Idx being considered ambiguous when it is not(why only CLongBinary columns)?
> 2) Am I bashing my head against a wall by attempting to use Appwizard
> defined strategies to do anything meaningful with databases?

Yes, at least with Blob-fields.

I was once having similar problems, though with Blobs from a postgres-database.
The problem however lies in SQLGetData() called by CFieldExchange.
This function generates faulty SQL-statements for
uniquely identifying the current record for retrieval of the data in the
blob-fields: In joins, table names are not included in the generated
select-statement (which will fail, if the joined tables have identical
column names) and column-names  are not quoted (which doomed me,
since postgres converts column names to lowercase if they are not quoted).

The workaround was not to retrieve the Blob-fields from a Recordset containing
joins or from queries which return more than one record.
Create a special recordset for the Blobs, which retrieves only
the Blob-fields and always select only one recordset by an appropriate filter.

Georg

 
 
 

Problem with MSACCESS ODBC Driver

Post by bullshar » Wed, 18 Dec 2002 09:42:53



> bullshark schrieb:
> > 1) Why is Idx being considered ambiguous when it is not(why only CLongBinary columns)?
> > 2) Am I bashing my head against a wall by attempting to use Appwizard
> > defined strategies to do anything meaningful with databases?

> Yes, at least with Blob-fields.

> I was once having similar problems, though with Blobs from a postgres-database.
> The problem however lies in SQLGetData() called by CFieldExchange.
> This function generates faulty SQL-statements for
> uniquely identifying the current record for retrieval of the data in the
> blob-fields: In joins, table names are not included in the generated
> select-statement (which will fail, if the joined tables have identical
> column names) and column-names  are not quoted (which doomed me,
> since postgres converts column names to lowercase if they are not quoted).

> The workaround was not to retrieve the Blob-fields from a Recordset containing
> joins or from queries which return more than one record.
> Create a special recordset for the Blobs, which retrieves only
> the Blob-fields and always select only one recordset by an appropriate filter.

> Georg

LOL...I guess that answers number two.

I traced the exception as far as the SQLGetData call, but everything there
seemed opaque. I'm curious how you tracked it down to bad SQL statements.
I had already figured that I would have to re-arrange, but I was hoping
there was a better solution.

thanks for the help.
--

bullshark

 
 
 

Problem with MSACCESS ODBC Driver

Post by Georg Kreyerhof » Wed, 18 Dec 2002 18:24:24


bullshark schrieb:


> > bullshark schrieb:
> > > 1) Why is Idx being considered ambiguous when it is not(why only CLongBinary columns)?
> > > 2) Am I bashing my head against a wall by attempting to use Appwizard
> > > defined strategies to do anything meaningful with databases?

> > Yes, at least with Blob-fields.

> > I was once having similar problems, though with Blobs from a postgres-database.
> > The problem however lies in SQLGetData() called by CFieldExchange.
> > This function generates faulty SQL-statements for
> > uniquely identifying the current record for retrieval of the data in the
> > blob-fields: In joins, table names are not included in the generated
> > select-statement (which will fail, if the joined tables have identical
> > column names) and column-names  are not quoted (which doomed me,
> > since postgres converts column names to lowercase if they are not quoted).

> > The workaround was not to retrieve the Blob-fields from a Recordset containing
> > joins or from queries which return more than one record.
> > Create a special recordset for the Blobs, which retrieves only
> > the Blob-fields and always select only one recordset by an appropriate filter.

> > Georg

> LOL...I guess that answers number two.

> I traced the exception as far as the SQLGetData call, but everything there
> seemed opaque. I'm curious how you tracked it down to bad SQL statements.
> I had already figured that I would have to re-arrange, but I was hoping
> there was a better solution.

the SQL-driver of the database I used had a logging option and I also had
the source-code, so I could build a debug-version of the driver. The calls
from SQLGetData however where not created by the ODBC-driver, but from
some MS-ODBC-library which is between the application and the vendor-ODBC-driver.
So I guess the problem is independend of the ODBC-driver acually used.
After that I searched the documentation and found some restrictions
of SQLGetData in MSDN. E.g. this article from the MSDN-documentation
coming with VC6:

SQLGetData in the Cursor Library

The cursor library implements SQLGetData by first constructing a SELECT statement
with a WHERE clause that enumerates the values stored in its cache for each
bound column in the current row. It then executes the SELECT statement to
reselect the row and calls SQLGetData in the driver to retrieve the data
from the data source (as opposed to the cache).

Caution: The WHERE clause constructed by the cursor library to identify    (!)
 the current row can fail to identify any rows, identify a different row,  (!)
or identify more than one row. For more information, see                   (!)
Constructing Searched Statements earlier in this appendix.               (!)

If the SQL_ATTR_USE_BOOKMARKS statement attribute is set to SQL_UB_VARIABLE,
SQLGetData can be called on column 0 to return bookmark data.

Calls to SQLGetData are subject to the following restrictions:

SQLGetData cannot be called for forward-only cursors.

SQLGetData can only be called when a SELECT statement generated the result set, (!)
the SELECT statement did not contain a join, a UNION clause, or a GROUP BY      (!)
clause, and any columns that used an alias or expression in the select list     (!)
were not bound with SQLBindCol.                                                 (!)

...

Georg

 
 
 

Problem with MSACCESS ODBC Driver

Post by bullshar » Wed, 18 Dec 2002 21:27:24



> the SQL-driver of the database I used had a logging option and I also had
> the source-code, so I could build a debug-version of the driver.

I just realized I could have used the ODBC Driver manager to trace, if
I had been using a DSN instead of a connect string...

Quote:> After that I searched the documentation and found some restrictions
> of SQLGetData in MSDN. E.g. this article from the MSDN-documentation
> coming with VC6:

> SQLGetData in the Cursor Library

I found the article. I was looking in all the wrong places,
never realizing that the MFC was complaining about it's own work,
not mine. Doh!

thanks again,
--

bullshark

 
 
 

1. MSAccess ODBC driver & ODBC libraries

Recently I downloaded MDAC to write a database frontend in Borland
C++Builder, but it won't read the library (.lib) files that come with it!
TLIB says something about a bad header or something...
Also, is the MSAcces ODBC driver on the net for free download, or does one
need to have Access? Or is the driver that comes with Acess distributable?
If so, which files do I distribute?

Thanks in advance for your help.

Wilderness Child

2. SqlDumpExceptionHandler on MsSQL 7.0 SP4.0

3. Where to get MSAccess ODBC Driver

4. XML for Analysis Design Review

5. MSAccess ODBC driver slow-down on upgrade from VC++ 5.0 to 6.0

6. Using Explain

7. JDBC/ODBC MSAccess driver not capable

8. decimal point for avg( )

9. ODBC driver/MSAccess/Sybase

10. Sybase ODBC driver from MSACCESS

11. Which ODBC driver for MSAccess?

12. Problem w/ MSACCESS native driver in D2

13. Sequence and oracle ODBC driver 8.01.07 driver problem