Home » Open Source » Programming Interfaces » SQL query returning wrong values (Oracle Database 10.2.0.5.0 under Windows 7 x86)
| SQL query returning wrong values [message #669719] |
Wed, 09 May 2018 07:21  |
 |
rfog
Messages: 4
Registered: May 2018
|
Junior Member |
|
|
Sometimes a SQL query return previous values, but only in CRecordSet. Let me explain.
We produce a kiosk that has a local oracle database connected to the running program via ODBC under Windows 7 x86. The database is only for the running program and no other connection exists.
As a sample, we have a query that returns this combination of files and columns:
ANZAHL WERT
---------- ----------
37 500
11 1000
Program runs and unpdate the DB changing ANZAHL to 0. Subsequent calls returns the right values:
ANZAHL WERT
---------- ----------
0 500
0 1000
Until not. It starts returning the right values. However, suddenly, it starts to return the previous values, and once it starts doing that, always returns them. However, the strange things come here: both SQL Developer and SQLPlus retuns the right ones.
Program restart (or PC restart). Starts returning righ values and suddenly, starts returning the previous ones. Sometimes starts from first moment to return the previous ones.
And more strange things: we drop the entire database. Create a new one with all values set to 0. And yes, suddenly, the query starts returning the previous values. And both SQL Developer and SQLPlus always return the right ones.
Our code is a fixup of MFC C++, plain C++, some C# code. Read access to Oracle is done via CRecordSet, and are the object that sometimes fails to return the right values.
The worst thing is once it starts happening in one machine, we cannot resolve it. Same software, same oracle, same our program version. Two identical machines side by side. One starts to fail, the other not. If we import the database from the one is working well, it still fails. We have replaced the entire program. Reinstall Oracle... still fails. I've traced our code to the internals of the CRecordSet and all seems ok.
It seems some kind of cache in Oracle who is getting the trouble... but my knowing of Oracle is near to zero.
Can please someone help me on this?
|
|
|
|
|
|
|
|
|
|
| Re: SQL query returning wrong values [message #669728 is a reply to message #669720] |
Wed, 09 May 2018 09:11  |
|
rfog
Messages: 4
Registered: May 2018
|
Junior Member |
|
|
Thank you for your fast answer!
Yes, I'm convinced it is a problem in our code, of course, but I need some advice because it is making me berserk.
Firt query is
select anzahlfrom inhalte i, containers c, finanztitel f where i.container_id = c.id and i.inhalttyp = 'B' and container_id in (98,99,100,101) and f.titel_id=i.FINANZTITEL_ID and f.WAEHRUNG_ALFACODE=i.WAEHRUNG_ALFACODE"
Then some updates not related to that tables (without commit).
After that, this one:
select anzahl,maxanzahl,wert from inhalte i, containers c, finanztitel f where i.container_id = c.id and i.inhalttyp = 'B' and container_id in (98,99,100,101) and f.titel_id=i.FINANZTITEL_ID and f.WAEHRUNG_ALFACODE=i.WAEHRUNG_ALFACODE"
Difference is second one gets more fields from the same tables, but the interesting one, ANZAHL, is different in the second. Even running the first one the second time returns the wrong values.
Both queries are run on each own instance of CRecordSet, and the first one is still open while the second is run.
If I stop the debugger after run the second one, and execute it in SQL Developer or SQLPlus, the returned values are the right ones.
|
|
|
|
|
|
|
|
|
|
| Re: SQL query returning wrong values [message #669732 is a reply to message #669731] |
Wed, 09 May 2018 09:31 |
|
rfog
Messages: 4
Registered: May 2018
|
Junior Member |
|
|
BlackSwan wrote on Wed, 09 May 2018 09:28Forgive me, I am not very knowledgeable with Microsoft programming environments.
>Our program made with C++ using part of MFC, CRecordSet as reading from the tables, and the Oracle ODBC driver.
Are you say that an executable program resides on each client PC & directly issues SQL into Oracle database & obtain result set back from DB Server?
How many different Oracle schemas are utilized by this application?
Yes, that is the way it works. Killing flies with atomic bombs, but is how I received the project.
Mmmm... What's a "schema". (Sorry, I'm C++ developer, not database, and less Oracle)...
If it is what I call "database" (a set of tables, indexes and triggers), only one. The used one.
|
|
|
|
Goto Forum:
Current Time: Mon Dec 30 11:03:38 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
