examples/SelectInto/TestSelectInto.dpr

program TestSelectInto;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils,
  Classes, FBUDRController, FBUdrPlugin, IB,
  UdrSelectInto in 'UdrSelectInto.pas';

procedure ReportResult(aValue: IResults);
var i: integer;
begin
  for i := 0 to aValue.getCount - 1 do
    writeln(aValue[i].Name,' = ',aValue[i].AsString);
end;

procedure ReportResults(cursor: IResultset);
begin
  while not cursor.IsEof do
  begin
    ReportResult(cursor);
    cursor.FetchNext;
  end;
end;

procedure RunTest(UDRPlugin: TFBUdrPluginEmulator);
var SelectInto: TExternalProcedureWrapper;
    Transaction: ITransaction;
    Results: IProcedureResults;
begin
  {Get the emulator wrapper for the row_count function, declared as MyRowCount}
  SelectInto := UDRPlugin.makeProcedure('SELECT_INTO',  {Name of procedure in database - case sensitive}
                                       '',            {package name is empty}
                                       'selectinto!select_into' {entry point}
                                       );
  try
    SelectInto.InputParams.ByName('select_statement').AsString := 'Select * From EMPLOYEE Where Salary < 50000';
    SelectInto.InputParams.ByName('table_name').AsString := 'LOWER_PAID';
    SelectInto.InputParams.ByName('table_type').AsString := '';
    Transaction := UDRPlugin.Attachment.StartTransaction([isc_tpb_write,isc_tpb_nowait,isc_tpb_read_committed],taCommit);
    Results := SelectInto.Execute(Transaction);
    if Results.FetchNext then
      writeln('Procedure Returns ',Results.ByName('Status').AsString)
    else
       writeln('No results');

   writeln('Show Table');
   ReportResults(UDRPlugin.Attachment.OpenCursorAtStart(Transaction,'Select * From LOWER_PAID'));
  finally
    SelectInto.Free;
  end;
end;

const
  DDL: array [0..0] of Ansistring = ('create or alter procedure select_into (' +
                                     'select_statement blob sub_type 1 not null,'+
                                     'table_name varchar(63) not null,'+
                                     'table_type varchar(25) not null' +
                                     ') returns ('+
                                     'status varchar(100)) as begin end'
                                     );

  CleanUpDDL: array [0..0] of Ansistring = ('Drop procedure select_into'
                                            );


procedure DoRunTest;
var Attachment: IAttachment;
    DPB: IDPB;
    UDRPlugin: TFBUdrPluginEmulator;
    i: integer;
begin
  {Open a connection with the example employee database. Amend database parameters
   as needed.}
  DPB := FirebirdAPI.AllocateDPB;
  DPB.Add(isc_dpb_user_name).setAsString('SYSDBA');
  DPB.Add(isc_dpb_password).setAsString('masterkey');
  DPB.Add(isc_dpb_lc_ctype).setAsString('UTF8');
  DPB.Add(isc_dpb_set_db_SQL_dialect).setAsByte(3);
  Attachment := FirebirdAPI.OpenDatabase('localhost:employee',DPB);
  for i := 0 to Length(DDL) -1 do
    Attachment.ExecImmediate([isc_tpb_write,isc_tpb_nowait,isc_tpb_concurrency],DDL[i]);
  try
    UDRPlugin := TFBUdrPluginEmulator.Create(FBUDRControllerOptions.ModuleName);
    try
      {initialize the emulator with the database connection}
      UDRPlugin.Attachment := Attachment;
      RunTest(UDRPlugin);
    finally
      UDRPlugin.Free;
    end;
  finally
    for i := 0 to Length(CleanUpDDL) -1 do
      Attachment.ExecImmediate([isc_tpb_write,isc_tpb_nowait,isc_tpb_concurrency],CleanUpDDL[i]);
    Attachment.Disconnect(true);
  end;
end;

begin
  with FBUDRControllerOptions do
  begin
    ModuleName := 'selectinto';
    AllowConfigFileOverrides := true;
    LogFileNameTemplate := 'SelectInto.log';
    LogOptions := [loLogFunctions, loLogProcedures, loLogTriggers, loDetails];
  end;
  DoRunTest;
  readln; {force console window to stay open}
end.

Revision:	402
Committed:	Mon Aug 1 10:07:24 2022 UTC (2 years, 4 months ago) by tony
File size:	3795 byte(s)
Log Message:	IBX Release 2.5.0
#	User	Rev	Content
1	tony	402	program TestSelectInto;
2
3			{$APPTYPE CONSOLE}
4
5			{$R *.res}
6
7			uses
8			System.SysUtils,
9			Classes, FBUDRController, FBUdrPlugin, IB,
10			UdrSelectInto in 'UdrSelectInto.pas';
11
12			procedure ReportResult(aValue: IResults);
13			var i: integer;
14			begin
15			for i := 0 to aValue.getCount - 1 do
16			writeln(aValue[i].Name,' = ',aValue[i].AsString);
17			end;
18
19			procedure ReportResults(cursor: IResultset);
20			begin
21			while not cursor.IsEof do
22			begin
23			ReportResult(cursor);
24			cursor.FetchNext;
25			end;
26			end;
27
28			procedure RunTest(UDRPlugin: TFBUdrPluginEmulator);
29			var SelectInto: TExternalProcedureWrapper;
30			Transaction: ITransaction;
31			Results: IProcedureResults;
32			begin
33			{Get the emulator wrapper for the row_count function, declared as MyRowCount}
34			SelectInto := UDRPlugin.makeProcedure('SELECT_INTO', {Name of procedure in database - case sensitive}
35			'', {package name is empty}
36			'selectinto!select_into' {entry point}
37			);
38			try
39			SelectInto.InputParams.ByName('select_statement').AsString := 'Select * From EMPLOYEE Where Salary < 50000';
40			SelectInto.InputParams.ByName('table_name').AsString := 'LOWER_PAID';
41			SelectInto.InputParams.ByName('table_type').AsString := '';
42			Transaction := UDRPlugin.Attachment.StartTransaction([isc_tpb_write,isc_tpb_nowait,isc_tpb_read_committed],taCommit);
43			Results := SelectInto.Execute(Transaction);
44			if Results.FetchNext then
45			writeln('Procedure Returns ',Results.ByName('Status').AsString)
46			else
47			writeln('No results');
48
49			writeln('Show Table');
50			ReportResults(UDRPlugin.Attachment.OpenCursorAtStart(Transaction,'Select * From LOWER_PAID'));
51			finally
52			SelectInto.Free;
53			end;
54			end;
55
56			const
57			DDL: array [0..0] of Ansistring = ('create or alter procedure select_into (' +
58			'select_statement blob sub_type 1 not null,'+
59			'table_name varchar(63) not null,'+
60			'table_type varchar(25) not null' +
61			') returns ('+
62			'status varchar(100)) as begin end'
63			);
64
65			CleanUpDDL: array [0..0] of Ansistring = ('Drop procedure select_into'
66			);
67
68
69			procedure DoRunTest;
70			var Attachment: IAttachment;
71			DPB: IDPB;
72			UDRPlugin: TFBUdrPluginEmulator;
73			i: integer;
74			begin
75			{Open a connection with the example employee database. Amend database parameters
76			as needed.}
77			DPB := FirebirdAPI.AllocateDPB;
78			DPB.Add(isc_dpb_user_name).setAsString('SYSDBA');
79			DPB.Add(isc_dpb_password).setAsString('masterkey');
80			DPB.Add(isc_dpb_lc_ctype).setAsString('UTF8');
81			DPB.Add(isc_dpb_set_db_SQL_dialect).setAsByte(3);
82			Attachment := FirebirdAPI.OpenDatabase('localhost:employee',DPB);
83			for i := 0 to Length(DDL) -1 do
84			Attachment.ExecImmediate([isc_tpb_write,isc_tpb_nowait,isc_tpb_concurrency],DDL[i]);
85			try
86			UDRPlugin := TFBUdrPluginEmulator.Create(FBUDRControllerOptions.ModuleName);
87			try
88			{initialize the emulator with the database connection}
89			UDRPlugin.Attachment := Attachment;
90			RunTest(UDRPlugin);
91			finally
92			UDRPlugin.Free;
93			end;
94			finally
95			for i := 0 to Length(CleanUpDDL) -1 do
96			Attachment.ExecImmediate([isc_tpb_write,isc_tpb_nowait,isc_tpb_concurrency],CleanUpDDL[i]);
97			Attachment.Disconnect(true);
98			end;
99			end;
100
101			begin
102			with FBUDRControllerOptions do
103			begin
104			ModuleName := 'selectinto';
105			AllowConfigFileOverrides := true;
106			LogFileNameTemplate := 'SelectInto.log';
107			LogOptions := [loLogFunctions, loLogProcedures, loLogTriggers, loDetails];
108			end;
109			DoRunTest;
110			readln; {force console window to stay open}
111			end.
112