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

Posted on 2006-05-17
Medium Priority
Last Modified: 2013-12-25
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?

Question by:DalTXColtsFan
  • 2
  • 2
LVL 11

Expert Comment

ID: 16698984
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?!?!?

Author Comment

ID: 16699128
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?
LVL 11

Accepted Solution

leclairm earned 400 total points
ID: 16718446
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.

Author Comment

ID: 16736518
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.


Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month14 days, 1 hour left to enroll

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question