testaorasql.pas

delphi通过OCI访问ORACLE

unit testAOraSQL;

{$INCLUDE dOCI.inc}

interface
uses
 SysUtils, Dialogs, TestFrameWork, DB, ADataSet, OraDB, AOraSQL;

type
 TTestAOraSQL = class(TTestCase)
 private
   DB:TOraDB;
   SQL:TAOraSQL;
   procedure fillTestTable;
 protected
   procedure SetUp; override;
   procedure TearDown; override;
 published
   procedure testAOraSQLFetch1;
   procedure testAOraSQLFetch2;
   procedure testAOraSQLFetch3;
   procedure testAOraSQLFetch4;
   procedure testAOraSQLFetch5;
   procedure testAOraSQLFetch6;

   procedure testAOraSQLOpen11;
   procedure testAOraSQLOpen12;
   procedure testAOraSQLOpen13;
   procedure testAOraSQLOpen14;
   procedure testAOraSQLOpen15;

   procedure testAOraSQLUpdateWithNull;
   procedure testAOraSQLInsertBLOB;
   procedure testAOraSQLSQLType;
   procedure testAOraSQLPrepare;

   procedure testAOraSQLGianpaoloGasparini;
   procedure testAOraSQLManyDataTypes;
   procedure testAOraSQLPrevNext;
   procedure testAOraSQLUniDirectonal1;
   procedure testAOraSQLUniDirectonal2;
   procedure testManyInsertsInOneQuery;

   procedure testAOraSQLReadBlobAsParamEmpty;
   procedure testAOraSQLReadBlobAsParam;

   procedure testAOraSQLRawDataType;
   procedure testAOraSQLUpdateRawData;
   procedure testAOraSQLSelectNULL;
   procedure testAOraSQLInsertLargeCLOB;
end;


implementation

uses OraError;

procedure TTestAOraSQL.SetUp;
begin
 DB  := TOraDB.Create(nil);
 DB.DBLogin := 'test';
 DB.DBPassword := 'a';
 DB.DBServer := 'RRR';
 DB.LoginPrompt := False;

 SQL := TAOraSQL.Create(nil);
 SQL.Database := DB;
 SQL.Name := 'SQL';
end;

procedure TTestAOraSQL.TearDown;
begin
// DB.Free;
// SQL.Free;
end;

procedure TTestAOraSQL.testAOraSQLFetch1;
begin
 try
  SQL.Fetch;
 except
  on E:Exception do
   CheckEquals('TAOraSQL ''SQL'' is not active !', E.Message);
 end;
end;

procedure TTestAOraSQL.testAOraSQLFetch2;
begin
 try
  SQL.SetQuery('select * from test');
  SQL.Fetch;
 except
  on E:Exception do
   CheckEquals('TAOraSQL ''SQL'' is not active !', E.Message);
 end;
end;

procedure TTestAOraSQL.testAOraSQLFetch3;
begin
 try
  SQL.SetQuery('select * from test');
  SQL.LoadFields;
  SQL.Fetch;
 except
  on E:Exception do
   CheckEquals('TAOraSQL ''SQL'' is not active !', E.Message);
 end;
end;

procedure TTestAOraSQL.testAOraSQLFetch4;
begin
 try
  SQL.SetQuery('select * from test');
  SQL.Open;
  SQL.Fetch;
 except
  on E:EDatabaseError do
   CheckEquals('Oracle Error ORA-24374: define not done before fetch or execute and fetch'#$A, E.Message);
 end;

 FreeAndNil(SQL);
end;

procedure TTestAOraSQL.testAOraSQLFetch5;
begin
 SQL.SetQuery('select * from test');
 SQL.LoadFields;
 SQL.Open;
 SQL.ReadAll;

 FreeAndNil(SQL);
end;

procedure TTestAOraSQL.testAOraSQLFetch6;
begin
 SQL.SetQuery('select * from test');
 SQL.LoadFields;
 SQL.OpenAll;

 FreeAndNil(SQL);
end;


procedure TTestAOraSQL.testAOraSQLOpen11;
begin
 try
  SQL.Open;
 except
  on E:EDatabaseError do
   CheckEquals('SQL: No SQL statement available.', E.Message);
 end;
end;

procedure TTestAOraSQL.testAOraSQLOpen12;
begin
 SQL.SetQuery('select * from test');
 SQL.Open;

 FreeAndNil(SQL);
end;

procedure TTestAOraSQL.testAOraSQLOpen13;
begin
 SQL.SetQuery('select * from test');
 SQL.LoadFields;
 SQL.Open;

 FreeAndNil(SQL);
end;

procedure TTestAOraSQL.testAOraSQLOpen14;
begin
 try
  SQL.SetQuery('select * from test');
  SQL.ReadAll;
 except
  on E:Exception do
   CheckEquals('TAOraSQL ''SQL'' is not active !', E.Message);
 end;

 FreeAndNil(SQL);
end;

procedure TTestAOraSQL.testAOraSQLOpen15;
begin
 try
  SQL.SetQuery('select * from test');
  SQL.LoadFields;
  SQL.ReadAll;
 except
  on E:Exception do
   CheckEquals('TAOraSQL ''SQL'' is not active !', E.Message);
 end;

 FreeAndNil(SQL);
end;


procedure TTestAOraSQL.testAOraSQLUpdateWithNull;
begin
  SQL.SetQuery('update test set F3 = :F3 where F3 is null ');
  SQL.AddParam('F3', ftoString, ptoInput);
//  SQL.AddParam('oF3', ftoString, ptoInput);
  SQL.ParamByName['F3'].AsString := '0000000';
  //SQL.ParamByName['oF3'].AsString := '';
  SQL.ExecSQL;

 FreeAndNil(SQL);
end;

procedure TTestAOraSQL.testAOraSQLInsertBLOB;
var i:Integer;
begin
//  SQL.SetQuery('insert into test3 (F1, F3) values(3, EMPTY_BLOB())');
//  SQL.ExecSQL;

  SQL.SetQuery('select F1, F3 from test3 ');
//  SQL.AddField('F1', ftoString, 100, False);
//  SQL.AddField('F3', ftoBLOB, 0, False);
  SQL.LoadFields;
  SQL.OpenAll;

  i := SQL.RecordCount;

 FreeAndNil(SQL);
end;

procedure TTestAOraSQL.testAOraSQLSQLType;
begin
 SQL.SetQuery('select F1, F3 from test ');
 Check(stUnknown = SQL.SQLType);
 SQL.Prepare;
 Check(stSelect = SQL.SQLType);

 SQL.SetQuery('update test set F1=''aa'', F3=5');
 Check(stUnknown = SQL.SQLType);
 SQL.Prepare;
 Check(stUpdate = SQL.SQLType);

 SQL.SetQuery('delete from test');
 Check(stUnknown = SQL.SQLType);
 SQL.Prepare;
 Check(stDelete = SQL.SQLType);

 SQL.SetQuery('insert into test values() ');
 Check(stUnknown = SQL.SQLType);
 SQL.Prepare;
 Check(stInsert = SQL.SQLType);

 SQL.SetQuery('CREATE TABLE CUSTOMER(CUSTNO NUMBER NOT NULL,COMPANY VARCHAR2(30) NOT NULL)');
 Check(stUnknown = SQL.SQLType);
 SQL.Prepare;
 Check(stCreate = SQL.SQLType);

 SQL.SetQuery('drop table test');
 Check(stUnknown = SQL.SQLType);
 SQL.Prepare;
 Check(stDrop = SQL.SQLType);

 SQL.SetQuery('ALTER TABLE AA ADD("C" VARCHAR2(10) NOT NULL, "G" VARCHAR2(10) NOT NULL)');
 Check(stUnknown = SQL.SQLType);
 SQL.Prepare;
 Check(stAlter = SQL.SQLType);

{ SQL.SetQuery('');
 Check(stUnknown = SQL.SQLType);
 SQL.Prepare;
 Check(stBegin = SQL.SQLType);

 SQL.SetQuery('');
 Check(stUnknown = SQL.SQLType);
 SQL.Prepare;
 Check(stDeclare = SQL.SQLType);
}

 FreeAndNil(SQL);
end;

procedure TTestAOraSQL.testAOraSQLPrepare;
begin
  SQL.SetQuery('select * from test ');

  Check(not SQL.Prepared);
  SQL.Prepare;
  Check(SQL.Prepared);
  SQL.Prepare;
  Check(SQL.Prepared);
  SQL.UnPrepare;
  Check(not SQL.Prepared);
  SQL.UnPrepare;
  Check(not SQL.Prepared);
  SQL.Prepare;
  Check(SQL.Prepared);
  SQL.UnPrepare;
  Check(not SQL.Prepared);

  FreeAndNil(SQL);
end;

//Gianpaolo Gasparini <[e-mail]>
procedure TTestAOraSQL.testAOraSQLGianpaoloGasparini;
begin
  SQL.SetQuery('select test.*,rowID from test');
  SQL.LoadFields();
  SQL.OpenAll;

  CheckEquals(5, SQL.RecordCount);

  FreeAndNil(SQL);
end;

procedure TTestAOraSQL.testAOraSQLManyDataTypes;
begin
  SQL.SetQuery('delete from test3');
  SQL.ExecSQL;
 // SQL.Database.CommitTransaction;


  SQL.SetQuery('select * from test3');
  SQL.LoadFields();

  CheckEquals(15, SQL.FieldCount);

  SQL.OpenAll;

  CheckEquals(0, SQL.RecordCount);

  FreeAndNil(SQL);
end;

procedure TTestAOraSQL.testManyInsertsInOneQuery;
begin
//  SQL.SetQuery('DELETE FROM TEST');
//  SQL.ExecSQL;

  SQL.SetQuery(
   'BEGIN '+
   'INSERT INTO TEST4(F1, F2) VALUES(1234.7878, 0.0000000000000000006); '+
   'INSERT INTO TEST4(F1, F2) VALUES(1111.7878, 0.0000000000000000006); '+
   'END;'
    );
  SQL.ExecSQL;
  DB.CommitTransaction;
end;


procedure TTestAOraSQL.fillTestTable;
begin
  SQL.SetQuery('DELETE FROM TEST');
  SQL.ExecSQL;

  SQL.SetQuery(
   'BEGIN '+
   'INSERT INTO TEST(F1, F2, F3, F4, F5, F6) VALUES(''kkkkkkkkkk'', 77777.7878, ''one'', 12121212121212121212,0,0.0000000000000000006);'+
   'INSERT INTO TEST(F1, F2, F3, F4, F5, F6) VALUES(''qqqqqqqqqq'', 77777.7878, ''two'', 12121212121212121212,1,0.0000000000000000006);'+
   'INSERT INTO TEST(F1, F2, F3, F4, F5, F6) VALUES(''cccccccccc'', 77777.7878, ''three'', 12121212121212121212,2,0.0000000000000000006);'+
   'INSERT INTO TEST(F1, F2, F3, F4, F5, F6) VALUES(''gggggggggg'', 77777.7878, ''four'', 12121212121212121212,3,0.0000000000000000006);'+
   'INSERT INTO TEST(F1, F2, F3, F4, F5, F6) VALUES(''iiiiiiiiii'', 77777.7878, ''five'', 12121212121212121212,4,0.0000000000000000006);'+
   'END;'
  );
  SQL.ExecSQL;
  DB.CommitTransaction;
end;

procedure TTestAOraSQL.testAOraSQLPrevNext;
begin
  // filling table with test data
  fillTestTable;

  SQL.SetQuery('select * from test order by f5');
  SQL.FetchCount := 1;
  SQL.LoadFields;
  CheckEquals(6, SQL.FieldCount);

  SQL.Open;

  CheckEquals(-1, SQL.CurrentRecord);
  CheckEquals(0, SQL.RecordCount);
  CheckEquals(False, SQL.EOF);

  SQL.Next;
  CheckEquals(False, SQL.EOF);
  CheckEquals(1, SQL.RecordCount);