Forum Discussion

CDurkin's avatar
CDurkin
Contributor
14 years ago

Sharing a Connection across ADO objects

I have to call numerous StoredProcs from an Oracle package, and all of these calls have to be from the same connection. 

However whenever I try and assign the connection property of the “ADOStoredProc” I keep on getting a error “Object doesn't support this property or method”



What I’m doing is:

 


var connection = null;  

function getConnection(){

  if (connection == null) {

    connection = ADO.CreateADOConnection();     

    connection.ConnectionString = getConnectionString();

    connection.LoginPrompt = false;

    connection.Open();

  }

  return connection;

}



function closeConnection(){

  if (connection != null) {

    connection.Close();

    connection = null;

  }  

}



var xSProcFlush = null;

function flush() {

  if (xSProcFlush == null) {

    xSProcFlush = ADO.CreateADOStoredProc();

    xSProcFlush.Connection = getConnection();

    xSProcFlush.ProcedureName = "OBJ_CACHE.FLUSH";

  }

  xSProcFlush.ExecProc();

}



var xSProcPrintNode = null, xParamPrintNode;

function print_Node_Cache(value) {

  if (xSProcPrintNode == null) {

    xSProcPrintNode = ADO.CreateADOStoredProc();

    xSProcPrintNode.Connection = getConnection();

    xSProcPrintNode.ProcedureName = "OBJ_CACHE.PRINT_NODE_CACHE";

    xParamPrintNode = xSProcPrintNode.Parameters.CreateParameter("p_MAX_ROWS ", DB.ftFloat, DB.adParamInput, 0, null);

  }

  xParamPrintNode.Value = value;

  xSProcPrintNode.ExecProc();

}



function runSimpleSql(sql){

  var connection = getConnection();

  try  {

    var rs = connection.Execute_(sql);

    if (!rs.EOF)   {

      return rs.GetString();

    }

  }  catch (e)  {

    Log.Warning(e.description, sql);

  }

}

4 Replies

  • tristaanogre's avatar
    tristaanogre
    Esteemed Contributor
    14 years ago
    The connection object of the object returned by "CreateADOStoredProc" is read only so you cannot assign another connection object to it.



    You can, however, access the connection string and pass that along.



    For myself, actually, I've started using the ADO.CreateCommand().  I do something very similar to you in a script extension.






    var lConnectionObject = ADO.CreateCommand()

    function ExecuteStoredProcedure(ProcedureName)

    {

        try

        {

            var RowCount

            RefreshSettings();

            lConnectionObject.ConnectionString = GetConnectionString(); //Another routine that just populates the connection string

            lConnectionObject.Open()        

            lConnectionObject.Execute("EXEC " + aqConvert.VarToStr(ProcedureName), RowCount);

            lConnectionObject.Close()

            return RowCount

        }

        catch (e)

        {

            Log.Error("Error running Stored procedure " + aqConvert.VarToStr(ProcedureName) + ": " + e.description)

            return -1

        }

    }




  • CDurkin's avatar
    CDurkin
    Contributor
    14 years ago
    Sharing a ConnectionString does not mean we are sharing a oracle session.



    The SQL package I am testing has to be logged into and then it caches data, as well as its numerous functions.  So I need to be able to run a number of sperate command against a single session.  I cannot run this as a single SQL file because I need to check interim states.
  • CDurkin's avatar
    CDurkin
    Contributor
    14 years ago
    Sorry, I see what you mean now.  I will try that.

    Thanks
  • CDurkin's avatar
    CDurkin
    Contributor
    14 years ago
    If anyone is interested the eventual solution I used is below.  It caches the StoredProc connection for faster calling when I am using that function a lot.



    Thanks for the Help.




     



    var connection = null;  

    function getConnection(){

      if (connection == null) {

        connection = ADO.CreateConnection();    

        connection.ConnectionString = getConnectionString();

        connection.Open();

      }

      return connection;

    }


    var gnCommand = null, gnReturn, gmParam;

    function get_Name(ID_OBJECT) {

      //FUNCTION GET_NAME(ID_OBJECT IN VARCHAR2) RETURN VARCHAR2;

      if (gnCommand == null) {

        gnCommand = ADO.CreateCommand();

        gnCommand.ActiveConnection = getConnection();

        gnCommand.CommandText = "{?=call GET_NAME(?)}";

        gnReturn = gnCommand.CreateParameter("RETURN", adVarChar, adParamReturnValue, 1024, null);

        gmParam = gnCommand.CreateParameter("ID_OBJECT", adVarChar, adParamInput, 1024, null);

        gnCommand.Parameters.Append(gnReturn);

        gnCommand.Parameters.Append(gmParam);

      }

      gmParam.Value = ID_OBJECT;

      gnCommand.Execute();

      return gnReturn.Value;

    }