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
#	Content
1	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