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

Posted on 2006-05-17
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
    LVL 11

    Expert Comment

    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?!?!?
    LVL 2

    Author Comment

    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

    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.
    LVL 2

    Author Comment

    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.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Article by: Martin
    Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    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…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now