Hello:
> 1. i install firebird/fyracle 0.8.10
Have in mind that will work only with * .NET 2.0 *
The CLRexternalEngine.dll should be installed in firebird\fyracle X.X.X\plugins
You will need to create a bin\assembly inside firebird\fyracle X.X.X\ and place
there
your assemblies.
You will need some ddl for the external engine configuration ( if it's
not created yet ),
as a sample i were using this:
/*
* Create External procedure engines
*/
CREATE TABLE RDB$EXTERNAL_ENGINE (
RDB$LANGUAGE VARCHAR(10) NOT NULL PRIMARY KEY,
RDB$MODULE_NAME VARCHAR(250)
);
CREATE TABLE RDB$EXTERNAL_PROCEDURES(
RDB$PROCEDURE_NAME RDB$PROCEDURE_NAME NOT NULL PRIMARY KEY,
RDB$EXTERNAL_NAME VARCHAR(4096),
RDB$LANGUAGE VARCHAR(10)
);
CREATE TABLE RDB$EXTERNAL_FUNCTIONS(
RDB$FUNCTION_NAME RDB$FUNCTION_NAME NOT NULL PRIMARY KEY,
RDB$EXTERNAL_NAME VARCHAR(4096),
RDB$LANGUAGE VARCHAR(10)
);
INSERT INTO RDB$EXTERNAL_ENGINE(RDB$LANGUAGE, RDB$MODULE_NAME)
VALUES('DOTNET','clrexternalengine');
A sample procedure ( ported from the Firebird employee sample database )
could look like this:
/*
* Select one row.
*
* Compute total, average, smallest, and largest department budget.
*
* Parameters:
* department id
* Returns:
* total budget
* average budget
* min budget
* max budget
*/
CREATE PROCEDURE sub_tot_budget
(
head_dept CHAR(3)
)
RETURNS
(
tot_budget DECIMAL(12, 2),
avg_budget DECIMAL(12, 2),
min_budget DECIMAL(12, 2),
max_budget DECIMAL(12, 2)
)
LANGUAGE DOTNET
EXTERNAL NAME
'FirebirdSql.ExternalEngine.Samples.Employee,FirebirdSql.ExternalEngine.Samples::SubTotalBudget'!!
The implementation in C# will look like this:
private string GetConnectionString()
{
FbConnectionStringBuilder csb = new FbConnectionStringBuilder();
csb.ContextConnection = true;
csb.Charset = "UNICODE_FSS";
return csb.ToString();
}
public FbResultSet SubTotalBudget(string headDepartament)
{
FbConnection connection = new FbConnection(this.GetConnectionString());
connection.Open();
string sql = "SELECT SUM(budget), AVG(budget), MIN(budget),
MAX(budget) FROM " +
"department WHERE head_dept = @HeadDepartament";
FbCommand select = new FbCommand(sql, connection);
select.Parameters.Add("@HeadDepartament", FbDbType.Char).Value =
headDepartament.Trim();
return new FbResultSet(select.ExecuteReader());
}
Finally it should be possible to use it as:
select * from sub_tot_budget('4')
--
Carlos Guzmán Álvarez
Vigo-Spain
http://carlosga.blogspot.com/
No hay un solo rey que no descienda de un esclavo,
ni un esclavo que no haya tenido reyes en su familia.
_______________________________________________
Firebird-net-provider mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
