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 Go to next message
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 #669720 is a reply to message #669719] Wed, 09 May 2018 07:33 Go to previous messageGo to next message
BlackSwan
Messages: 26606
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

You keep saying "same, same, same".
My reality tells me that when you observe different results, then you can be 100% certain that something is different.
I could easily be wrong here, but my gut reaction is that there is an application bug; since sqlplus & SQL developer produce expected results.

If you can show us what is different, then it should be easy to fix.
Re: SQL query returning wrong values [message #669723 is a reply to message #669719] Wed, 09 May 2018 07:54 Go to previous messageGo to next message
JPBoileau
Messages: 75
Registered: September 2017
Member
Since SQLPlus + SQL-Developer return the correct results, we can safely rule out the Oracle Database itself.

That being said, I would surmise that the problem is either in the application (possible software bug) or the layers between the application and the database.

If it's not the application, my hunch would be that there is caching at the layer(s) between the application and the database. Since you mentioned ODBC, I would look in that direction.

JP
Re: SQL query returning wrong values [message #669725 is a reply to message #669723] Wed, 09 May 2018 08:18 Go to previous messageGo to next message
Michel Cadot
Messages: 66644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I concur and move the topic to "Programming Interfaces" forum.

Re: SQL query returning wrong values [message #669728 is a reply to message #669720] Wed, 09 May 2018 09:11 Go to previous messageGo to next message
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 #669729 is a reply to message #669728] Wed, 09 May 2018 09:19 Go to previous messageGo to next message
BlackSwan
Messages: 26606
Registered: January 2009
Location: SoCal
Senior Member
does some or all of the SQL reside within PL/SQL procedures or packages.

What exactly exists between the end users & the Oracle database?
Re: SQL query returning wrong values [message #669730 is a reply to message #669729] Wed, 09 May 2018 09:22 Go to previous messageGo to next message
rfog
Messages: 4
Registered: May 2018
Junior Member
BlackSwan wrote on Wed, 09 May 2018 09:19
does some or all of the SQL reside within PL/SQL procedures or packages.
No.

BlackSwan wrote on Wed, 09 May 2018 09:19
What exactly exists between the end users & the Oracle database?
Our program made with C++ using part of MFC, CRecordSet as reading from the tables, and the Oracle ODBC driver.
Re: SQL query returning wrong values [message #669731 is a reply to message #669730] Wed, 09 May 2018 09:28 Go to previous messageGo to next message
BlackSwan
Messages: 26606
Registered: January 2009
Location: SoCal
Senior Member
Forgive 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?
Re: SQL query returning wrong values [message #669732 is a reply to message #669731] Wed, 09 May 2018 09:31 Go to previous message
rfog
Messages: 4
Registered: May 2018
Junior Member
BlackSwan wrote on Wed, 09 May 2018 09:28
Forgive 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.
Previous Topic: IST sysdate default is not working with Entity Framwork
Next Topic: plsql output to STDOUT
Goto Forum:
  


Current Time: Sun Oct 20 16:21:51 CDT 2019