How to set the DefaultDatabase property of an ADO Connection connected to Oracle

Greetings experts,

I'm trying to run the following code in VB 6.0 against an Oracle 9i database:

Set cnMyConn = New ADODB.Connection
cnMyConn.CursorLocation = adUseClient
cnMyConn.Open "Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=myadmin;Data Source=mysvcnm;Extended Properties="PLSQLRSet=1;ChunkSize=500;Fetchsize=1000;DistribTX=0";Password=mypwd"
cnMyConn.DefaultDatabase = "MyDefaultDB"

But I'm getting a "read-only property not set" error setting the default database.

What am I doing wrong?

Thanks
Joe
LVL 2
DalTXColtsFanAsked:
Who is Participating?
 
leclairmCommented:
I'm really not sure if this is possible.

A workaround would be to store the schema owner in a ini/config file that your app will load.  Whenever you build your string, you could do something like:

strSql = SetSchema("select * from SC_OWNER.ATable")

Throughout the app, SC_OWNER is would be the standard that you use for a generic schema owner.
 
function SetSchema(sql as string) as string
  if instr(1,sql,"SC_OWNER") <> 0 then
     SetSchema = replace(sql,"SC_OWNER", SchemaFromConfigFile)
  end if
end function

Just an example, but you get the idea.  At least this way the schema isn't hard coded and leaves more flexibility.
0
 
leclairmCommented:
I'm not sure if this property is available with the oracle provider as it is the provider that is supposed to initally set this property I believe.  I think your connection string should be enough?!?!?
0
 
DalTXColtsFanAuthor Commented:
To add a little more detail about what I'm trying to accomplish, in the Oracle database instance mysvcnm, the user MyDefaultDB is the schema owner, and coincidentally (or perhaps *not* coincidentally but as the result of good design) all of the tables, views, stored procs, functions etc in the database are under MyDefaultDB.

The client's DBA controls the password of the MyDefaultDB user, so I have to log on as other users, i.e. MyAdmin, MyUser, MyGuest etc.

What I'm trying to accomplish is being able to run SQL statements using the cnMyConn connection without having to append the schemaname to all of the objects, i.e.

select * from ATable

instead of

select * from MyDefaultDB.ATable


Is there a way to put MyDefaultDB somewhere in the connection string?
0
 
DalTXColtsFanAuthor Commented:
I was in a rush so I worked around the problem by executing the SQL "ALTER SESSION SET CURRENT_SCHEMA = 'MySchema' after making the connection.

But thanks for your answers.

Joe
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.