We have a com component (written in C++ , Visual Studio.net 2003, ATL 7).
This extracts data from an Oracle 9.2 database.
It is successful when running on the above live Oracle system via ADO and a DSN using the Oracle service accessed via Oracle Client software.
We installed a test Oracle 10 system (W2k3 Enterprise Server)and experienced problems.
All the data is returned successfully bar one field.
This field (fieldname : BrandName (Number) a primary and foreign key as outlined below) will always return 0.
BUT if we alter the order in which the fields on our test system are returned , , by merely changing the SQL ,the query is successful.
We then installed an Oracle 9.2 test system (W2k pro)and experienced the same problems. We used a variety of Oracle clients and MDAC versions.
The problems only occur when accessing the data programmtically using ADO. SQLPlus is fine.
Other non ADO means of programmatic access (e.g. ODBC in c++ , c# etc) are successful in all cases at returning the data correctly.
The oracle test system was installed and the tables created using SQL statements by Oracle newbies . Are there any post installation tasks or configuration that needs to be done on windows which we may have missed out on which might cause this inconsistent behaviour ? We have configured the service etc obviously.
The second question : We find that our original code used the following :
By changing the CursorLocation to Server Side and the others to adOpenStatic, adLockOptimistic we can get things working better.
I have heard Oracle client side cursors are buggy. Is this true?
Are they best avoided?
But this brings me back to the question of why the code should have worked on the live system and not the test systems. And why should it be ADO as opposed to ODBC etc which falters.
Finally here is the SQL statement , does it seem ok at first view ?
Company.ID = Brand.ID and
Brand.BrandName = Model.BrandName and
Brand.ID = Model.ID and
Model.Row = ModelDetails.Row and
Model.ID = ModelDetails.ID and
Model.BrandName = ModelDetails.BrandName and
Company.Nationality = 'USA' and
Brand.IntroDate > add_months(sysdate,-36)