This discussion is archived
5 Replies Latest reply: Oct 30, 2007 3:25 PM by 474459 RSS

delphi with oo4o start information and example

602333 Newbie
Currently Being Moderated
Hi
I'm new user. I'm find any information and examples for:
- Connect to Oracle database with oo4o from Delphi 5 applications;
- Insert new records to tables, update it and ;
  • 1. Re: delphi with oo4o start information and example
    474459 Newbie
    Currently Being Moderated
    Hi,

    The link below is a piece of code demonstrating some basic oo4o Delphi programming:

    http://www.delphi3000.com/articles/article_3898.asp?SK=

    Hope this helps,
    M
  • 2. Re: delphi with oo4o start information and example
    602333 Newbie
    Currently Being Moderated
    Hi
    Thanks, it work good. :)
  • 3. Re: delphi with oo4o start information and example
    602333 Newbie
    Currently Being Moderated
    This example work good, bat to us this in really applications we need more information about properties, methods of OraSession, OraDatabase and OraDynaSet. Where we find it.
  • 4. Re: delphi with oo4o start information and example
    474459 Newbie
    Currently Being Moderated
    Sorry,

    but I looked for the same kind of information and couldn't find it...
    Everything I developed was pure trial and error work.

    If you send me an email address (it doesn't have to be yours) I can send you some sample code.

    Cheers,
    Marcos
  • 5. Re: delphi with oo4o start information and example
    474459 Newbie
    Currently Being Moderated
    The code below is what I have. I started building this component just to run procedures and get their results: Don't really have time now to keep working on it. Have fun.

    Marcos

    ---------------------------------------------
    unit OraObject4Ole;

    interface

    uses
    SysUtils, Classes, ComObj, Variants;

    type TOraParameterType = (ORAPARM_INPUT = 1, ORAPARM_OUTPUT = 2, ORAPARM_BOTH = 3);
    type TOraDataTypeCode = (ORATYPE_VARCHAR2 = 1);
    type TOraOpenDbMethodOpt = (ORADB_DEFAULT = $0, ORADB_ORAMODE = $1, ORADB_NOWAIT = $2, ORADB_DBDEFAULT = $4, ORADB_DEFERRED = $8,ORADB_ENLIST_IN_MTS = $10);

    //'OpenDatabase Method Options
    //Global Const ORADB_DEFAULT = &H0&
    //Global Const ORADB_ORAMODE = &H1&
    //Global Const ORADB_NOWAIT = &H2&
    //Global Const ORADB_DBDEFAULT = &H4&
    //Global Const ORADB_DEFERRED = &H8&
    //Global Const ORADB_ENLIST_IN_MTS = &H10&

    type
    TOraObject4Ole = class(TComponent)
    private
    { private declarations here }
    pDatabaseName: string;
    pUserName: string;
    pPassword: string;
    pOraSession: Variant;
    pOraDatabase: Variant;
    function pGetDatabaseName: string;
    procedure pSetDatabaseName(NewDatabaseName: string);
    function pGetUserName: string;
    procedure pSetUserName(NewUserName: string);
    function pGetPassword: string;
    procedure pSetPassword(NewPassword: string);
    procedure pRemoveParameters;
    procedure pCreateSession;
    protected
    { protected declarations here }
    function pGetParametersList : string;
    function pOraSessionUp: boolean;
    public
    { public declarations here }
    constructor Create(AOwner: TComponent); override;
    destructor Destroy(); override;
    procedure ConnectToDatabase(sDatabase, sUser, sPass: WideString);
    procedure Connect;
    procedure Disconnect;
    procedure CreateParameter(sName: OleVariant; ovValue: OleVariant; opType: TOraParameterType; oDataType: TOraDataTypeCode);
    procedure CreateVarchar2InputParam(sName: OleVariant; ovValue: OleVariant);
    procedure CreateVarchar2OutputParam(sName: OleVariant; iSize: integer);
    function GetParameterValue(sName:OleVariant): string;
    procedure Execute(sProcName: WideString);
    function GetOracleVersion : string;
    function GetOO4OVersion: string;
    procedure BeginTransaction;
    procedure CommitTransaction;
    procedure RollbackTransaction;
    published
    property DatabaseName: string read pGetDatabaseName write pSetDatabaseName;
    property Username: string read pGetUserName write pSetUserName;
    property Password: string read pGetPassword write pSetPassword;
    { published declarations here }
    end;

    procedure Register;

    implementation

    //uses unOracleConst;

    { TOO40Procedure }

    procedure TOraObject4Ole.BeginTransaction;
    begin
    pOraDatabase.BeginTrans;
    end;

    procedure TOraObject4Ole.CommitTransaction;
    begin
    pOraDatabase.CommitTrans;
    end;

    procedure TOraObject4Ole.Connect;
    var sUserPass : WideString;
    begin
    // Connects to the database
    if pOraSessionUp
    then begin
    sUserPass := pGetUserName + '/' + pGetPassword;
    // pOraSession.CreateDatabasePool(4,10,20,pDatabaseName,sUserPass,0);
    // pOraDatabase := pOraSession.GetDatabaseFromPool[5];
    pOraDatabase := pOraSession.OpenDatabase[pDatabaseName,sUserPass,$0];
    end;
    end;

    procedure TOraObject4Ole.ConnectToDatabase(sDatabase, sUser, sPass: WideString);
    var sUserPass : WideString;
    begin
    // Connects to the database
    if pOraSessionUp
    then begin
    sUserPass := sUser + '/' + sPass;
    // pOraSession.CreateDatabasePool(4,10,20,sDatabase,sUserPass,0);
    // pOraDatabase := pOraSession.GetDatabaseFromPool[5];
    pOraDatabase := pOraSession.OpenDatabase[sDatabase,sUserPass,$0];
    end;
    end;

    procedure TOraObject4Ole.pCreateSession;
    var sUserPass : WideString;
    begin
    // Create object
    pOraSession := CreateOleObject('OracleInProcServer.XOraSession');
    sUserPass := pGetUserName + '/' + pGetPassword;
    end;

    constructor TOraObject4Ole.Create(AOwner: TComponent);
    begin
    inherited;
    pCreateSession;
    end;

    procedure TOraObject4Ole.CreateParameter(sName: OleVariant; ovValue: OleVariant; opType: TOraParameterType; oDataType: TOraDataTypeCode);
    begin
    if not VarIsEmpty(pOraDatabase)
    then begin
    pOraDatabase.Parameters.Add(sName,ovValue,opType,oDataType,sName);
    end;
    end;

    procedure TOraObject4Ole.CreateVarchar2InputParam(sName, ovValue: OleVariant);
    begin
    if not VarIsEmpty(pOraDatabase)
    then begin
    pOraDatabase.Parameters.Add(sName,ovValue,ORAPARM_INPUT,ORATYPE_VARCHAR2,sName);
    end;
    end;

    procedure TOraObject4Ole.CreateVarchar2OutputParam(sName: OleVariant; iSize: integer);
    begin
    if not VarIsEmpty(pOraDatabase)
    then begin
    pOraDatabase.Parameters.Add(sName,Unassigned,ORAPARM_OUTPUT,ORATYPE_VARCHAR2,sName);
    pOraDatabase.Parameters[sName].MinimumSize := iSize;
    end;
    end;

    destructor TOraObject4Ole.Destroy;
    begin
    Disconnect;
    inherited;
    end;

    procedure TOraObject4Ole.Disconnect;
    begin
    pRemoveParameters;
    // pOraDatabase.Close;
    pOraDatabase := Unassigned;
    pOraSession := Unassigned;
    end;

    procedure TOraObject4Ole.Execute(sProcName: WideString);
    var SQLText : WideString;
    begin
    if not VarIsEmpty(pOraDatabase)
    then begin
    SQLText := ' BEGIN ' + sProcName + '(' + pGetParametersList + '); END; ';
    // pOraDatabase.CreateSQL[SQLText,0]; do not use this!!!!
    pOraDatabase.ExecuteSQL(SQLText);

    end;
    end;

    function TOraObject4Ole.pGetDatabaseName: string;
    begin
    Result := pDatabaseName;
    end;

    function TOraObject4Ole.pGetParametersList: string;
    var i: integer;
    tmpList : string;
    begin
    if not VarIsEmpty(pOraDatabase)
    then begin
    tmpList := '';
    for I := 0 to pOraDatabase.Parameters.Count - 1
    do if I = (pOraDatabase.Parameters.Count - 1)
    then tmpList := tmpList + ':' + VarToStr(pOraDatabase.Parameters.Name)
    else tmpList := tmpList + ':' + VarToStr(pOraDatabase.Parameters[I].Name)+', ';
    Result := tmpList;
    end;
    end;

    procedure TOraObject4Ole.pRemoveParameters;
    begin
    if not VarIsEmpty(pOraDatabase)
    then while pOraDatabase.Parameters.Count > 0
    do pOraDatabase.Parameters.Remove(pOraDatabase.Parameters[0].Name);
    end;

    function TOraObject4Ole.pGetPassword: string;
    begin
    Result := pPassword;
    end;

    function TOraObject4Ole.pGetUserName: string;
    begin
    Result := pUserName;
    end;

    function TOraObject4Ole.pOraSessionUp: boolean;
    begin
    try
    try
    if (VarIsEmpty(pOraSession))
    then pCreateSession;
    result := true;
    except on E: Exception do
    result := false;
    end;
    finally
    end;
    end;

    procedure TOraObject4Ole.pSetDatabaseName(NewDatabaseName: string);
    begin
    pDatabaseName := NewDatabaseName;
    end;

    procedure TOraObject4Ole.pSetPassword(NewPassword: string);
    begin
    pPassword := NewPassword;
    end;

    procedure TOraObject4Ole.pSetUserName(NewUserName: string);
    begin
    pUserName := NewUserName;
    end;

    procedure TOraObject4Ole.RollbackTransaction;
    begin
    pOraDatabase.Rollback;
    end;

    function TOraObject4Ole.GetParameterValue(sName: OleVariant): string;
    begin
    if not VarIsEmpty(pOraDatabase)
    then Result := VarToStr(pOraDatabase.Parameters[sName].Value)
    else Result := 'Not Connected.';
    end;

    function TOraObject4Ole.GetOracleVersion: string;
    begin
    if not VarIsEmpty(pOraDatabase)
    then Result := VarToStr(pOraDatabase.RDBMSVersion)
    else Result := 'Not Connected.';
    end;

    function TOraObject4Ole.GetOO4OVersion: string;
    begin
    if not VarIsEmpty(pOraSession)
    then Result := VarToStr(pOraSession.OIPVersionNumber)
    else Result := 'Not Connected.';
    end;

    procedure Register;
    begin
    RegisterComponents('Oracle Objects 4 OLE', [TOraObject4Ole]);
    end;

    end.