5 Replies Latest reply: Oct 30, 2007 5:25 PM by 474459 RSS

    delphi with oo4o start information and example

    602333
      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
          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
            Hi
            Thanks, it work good. :)
            • 3. Re: delphi with oo4o start information and example
              602333
              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
                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
                  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.