The program uses PostgreSQL. However once a day, it pulls data from an Oracle server into a couple of Memory files, runs a process procedure, and writes the data to a PG table. This has been reliable for several years.
After completion of the above, if I attempt to open a related PG table or view, I get an Oracle table or view does not exist error (ORA-00942). This error does not occur if the program is closed, reopened, & the same PG browse called. It seems almost as if the ODBC driver will only connect with the Oracle server once that connection has been used.
I guess the first thing would be to make sure that you have actually closed the Oracle file. If you have, then you shouldn’t still have an open connection to the Oracle database, which you clearly do have.
You could create a little procedure to check. In the Individual overrides for the Oracle file, click the FileManager Options for button. Derive the class and create a new method called Stillopen or something, and get it to return Self.Opened. Call it at the point where you think the file should have been closed. If the number returned is not zero, you have a problem. You could also put a loop in there to call Access:<>.close until none are left open.
Maybe you have declared a relationship on your Oracle file with one of your postgres files, so opening the postgres file also opened the Oracle file?
Thanks for the quick reply. My thinking about what to check has been much the same.
The Oracle tables do not have any relationships defined. I have confirmed that Oracle table open/close calls are all matched. I also added an OraTable{ PROP:Disconnect} call based on the Clarion docs that should insure that the connection is closed.
I’ve also seen similar behaviour back in C6. Looked for all the world like clarion got confused and used the wrong connection, or rather continued to use the one connection.
I had thought that fixed
Tables declared in Dct - so using the FileManagerClass.
Two global connection variables, one for Oracle, one for PostgreSQL. The values are set at startup and never change.
No. The corresponding PostgreSQL table is slightly different. That is why the Oracle data is processed before written to PG.
And to be clear, after processing, the Oracle table or view does not exist error will be given when opening a completely different PG table or view that is not involved in the processing.
Every table is declared as either a PG or Oracle table, right? No changing prop:owner on a table.
What is your prop:BusyHandling setting?
Do you use /TurboSQL on any table in the system. I had to abandon TurboSQL in systems that connected to multiple databases because the system would attempt to use the wrong database. Not just with the TurboSQL tables, but regular tables, too. Using TURBOSQL driver string for a normal SQL table?
Yes, every table declared as either PG or Oracle. No PROP:Owner changes.
Have not set PROP:BusyHandling. I will take a look, but seems unlikely to be involved.
All Oracle tables have /TurboSQL. It is not set for any PG tables.
This is easy enough to remove and test since only 5 Oracle tables are involved. Thank you.
I guess you are using ODBC to access the Oracle tables? Since turbosql is not supported by the oracle driver. Any real reason why you are using it? It might save you a fraction of a second on opening the table.
From my morning test, I was just reminded why I am using it on Oracle tables.
The Oracle table definition in my Dct does NOT exactly match the Oracle table on the server.
Instead, it is essentially a view with one additional column from another Oracle table. The join between these two tables is a real pain in the backside (7 columns involved) that is determined by the anonymous administrators of the Oracle data. It therefore is much easier to UseSQL to define the query and not enter everything in the Dct. Without turbosql, the check to match the server definition fails.
I should note that this Oracle data has been processed since 2007. It is only with the recent need to include this process in an app with other procedures that the Oracle error was noticed when accessing PG tables.
If /turboSQL is the cause of your problem and you need to get rid of it, I guess the choices are:
Set up a view on the server that has all the columns you need.
Set up the table from which you grab the additional field as an additional table in your clarion dictionary, and have clarion construct the view (probably using a custom join condition that you provide)
While I haven’t yet absolutely confirmed that /turboSQL is the problem, I agree with your choices. Unfortunately, #1 is out as I have no control or input on the Oracle server.