Devart Forums

Code: Select all

create table depts( oid number(8) not null, name varchar2(200), dept_desc varchar2(200),  notes varchar2(200) );

alter table depts  add constraint dept_pk primary key (oid);
alter table depts  add constraint dept_ck  check (oid < 100);

Code: Select all

  vCount  := conn.ExecSQL('begin select count(*) into :result from A; end;', [0]);

Code: Select all

function TOraSQL.FindResultParam: TDAParam;
begin
  Result := Params.FindParam('Result');
end;

Code: Select all

function TUniSQL.FindResultParam: TDAParam;
var
  i: integer;
begin
  Result := nil;

  for i := 0 to Params.Count - 1 do
    if Params[i].ParamType = ptResult then
      Result := Params[i];

  if Result <> nil then
    if Result.DataType = ftUnknown then
      Result.DataType := ftInteger;    
end;

Code: Select all

  snMain := TUniConnection.Create(self);
  with snMain do begin
    SpecificOptions.Add('Oracle.Direct=True');
    SpecificOptions.Add('Oracle.ClientIdentifier=Admin');
    Username       := 'dblock';
    Password       := 'dblock';
    Server         := 'serv::db';
    AutoCommit     := False;
    ProviderName   := 'Oracle';
    Connect;

    ExecSQL('create table A (ID INTEGER not null, NAME VARCHAR2(250) not null, CONSTRAINT A_PK PRIMARY KEY (ID))', []);
    ExecSQL('create table B (ID INTEGER not null, NAME VARCHAR2(250) not null, CONSTRAINT B_PK PRIMARY KEY (ID))', []);
    ExecSQL('insert into A values(1, ''Atest 1'')', []);
    ExecSQL('insert into B values(1, ''Btest 1'')', []);

    Commit;
  end;

Code: Select all

    var
      snMain  : TUniConnection;
      qryTest : TUniQuery;
      tblTest : TUniTable;

    begin
      snMain := TUniConnection.Create(self);

      with snMain do begin
        SpecificOptions.Add('Oracle.Direct=True');
        Username    := 'dblock';
        Password    := 'dblock';
        Server      := 'ORA11g64bit::dbx';
        AutoCommit  := False;
        Connect;
        try
          ExecSQL('drop table A', []);
          ExecSQL('drop table B', []);
        except
        end;
       
        ExecSQL('create table A (ID INTEGER not null, NAME VARCHAR2(250) not null, CONSTRAINT A_PK PRIMARY KEY (ID))', []);
        ExecSQL('create table B (ID INTEGER not null, NAME VARCHAR2(250) not null, CONSTRAINT B_PK PRIMARY KEY (ID))', []);

        ExecSQL('insert into A values(1, ''test 1'')', []);
        ExecSQL('insert into A values(2, ''test 2'')', []);
        ExecSQL('insert into A values(3, ''test 3'')', []);

        ExecSQL('insert into B values(1, ''test 1'')', []);
        ExecSQL('insert into B values(2, ''test 2'')', []);
        ExecSQL('insert into B values(3, ''test 3'')', []);

        snMain.ExecSQL('commit', []); // used instead of commit because it raise an exception of inactive transaction
      end;


      tblTest := TUniTable.Create(self);
      with tblTest do begin
        Connection:= snMain;       
        TableName   := 'A';
        KeyFields   := 'ID';
        Open;

        Insert; FieldByName('ID').AsInteger := 4;   FieldByName('Name').AsString := 'test 4';  Post;

        Locate('ID', 1, []);
        Edit;  FieldByName('Name').AsString := 'new value 1';  Post;

        Locate('ID', 2, []); Delete;
        Next;
      end;

      snMain.ExecSQL('Rollback', []); // used instead of snMain.Rollback because it raise an exception of inactive transaction


      qryTest := TUniQuery.Create(self);
      with qryTest do begin
        Connection:= snMain;        
        SQL.Text := 'select * from B';
        KeyFields   := 'ID';
        Open;

        Insert; FieldByName('ID').AsInteger := 4;   FieldByName('Name').AsString := 'test 4'; Post;

        Locate('ID', 1, []);
        Edit;  FieldByName('Name').AsString := 'new value 1';    Post;

        Locate('ID', 2, []);
        Delete;
        Next;
      end;

       snMain.ExecSQL('Rollback', []); // used instead of snMain.Rollback because it raise an exception of inactive transaction
    end;