DevOps, CI/CD and Automation

Hi,

First, I'm not a DB configuration expert, but I was tasked with a job to connect via ODBC to an Oracle DB, make a query, pull some data out, make some mods, reinsert the data. I'm running Oracle XE on my personal machine, and the whole app works great.

Now I need to try it on a test copy of our "real" database, and if it works on that, we'll run it against the real thing. This is Oracle 10g, and the connection is not local, but on another machine.

I made a DNS entry, "DBFix", from the Data Sources (ODBC) control panel, and it does the test connection just fine. I can also connect to the DB via SQLDeveloper. But my very same code that worked just fine on my local XE is failing. It opens the database successfully (this is written in VB) but attempts to get records fail.

Now, the interesting thing is whereas on my local machine I just had the one DB, on this other machine, for space (and server) considerations they made a second tablespace (because they use the server for other stuff, and couldn't just dedicate the whole box). They made a separate user that has that second tablespace as its default database.

So, the machine has two tablespaces. Let's call them OFF_LIMITS and MY_TEST. There is a user called MY_TEST that has MY_TEST as its default tablespace (likewise there is an off_limits user for the off_limits tablespace).

My connection string is along the lines of:
"ODBC;DSN=DBFix;USR=MY_TEST;PWD=MY_TEST"
(I've also tried UID instead of USR, specifying DATABASE, HST (host IP), PRT (port) - no diff).

The SQL statement is along the lines of:
"select * from W_DOCUMENT where this condition and that condition..."
The statement has not changed since being on my local machine.

It connects fine, but when it goes to execute the SQL statement - and here's the weird part - it'll kick back an error:

(3078) The Microsoft Jet database engine could not find the input table or query 'OFF_LIMITS.W_DOCUMENT'

I shouldn't be logged in as OFF_LIMITS, I should be logged in as MY_TEST, but it's looking for the other one (and obviously knows about it).

So I tried explicitly setting the tablespace like so:
"select * from MY_TEST.W_DOCUMENT where this condition and that condition"

and the result was equally bizarre:

(3024) Couldn't find file c:\myfolder\MY_TEST.mdb

The obvious two things wrong here is it is looking for a .mdb (Access DB) and looking on my local drive, not the remote server.

The ODBC DSN is set up as a System DSN to use the Oracle in OraClient10g_home2 driver, and I note they have two clients on here for some reason.

Regardless, I had one of our DB people (when I could grab one) look at it, and he agrees it seems to be properly set up.

I'm at a loss. If I explicitly specify the tablespace, it looks on my local drive for a DB, and if I don't it properly goes out to the server but is asking for the tablespace of the other user.

What the heck?