Hi there,

I have an idea for being able to use Delphi TStrings objects within a
stored proc, but it all depends on when UDF Libraries are loaded and
unloaded which I need clarification on.

Background:
I've got some delphi code that I'd like to turn into a stored proc but
to keep it quick I really need some sort of TStrings ability within my
stored proc. I can't use temporary tables as we're stuck on FB1.5
dialect 1. So I was thinking this could be achieved by writing a UDF
library like so in Delphi:

unit DBLists;

uses Classes, SysUtils, IniFiles;

implementation

// This master list will contain all the lists
// created by the NewList function
var ListOfLists: TList;

// Private function which returns a TStrings given the ListNo
function GetList(ListNo: Integer): TStrings;
begin
Result := TStrings(ListOfLists[ListNo]);
end;

// Exported function which will create a new TStrings and add
// it to the master list of TStrings objects and return back
// the index of this list within the master list
function NewList(Hashed: integer): integer; cdecl;
var List: TStrings;
begin
if Hashed = 0 then
List := TStringList.Create
else
List := THashedStringList.Create;
ListOfLists.Add(List);
Result := ListOfLists.Count - 1;
end;

// Exported function to add a varchar to a list
procedure AddToList(ListNo: Integer; ListItem: PChar); cdecl;
begin
GetList(ListNo).Add(string(ListItem));
end;

// Exported function to return the index of a
// varchar within a list
function IndexInList(ListNo: Integer;
ListItem: PChar): Integer; cdecl;
begin
Result := GetList(ListNo).IndexOf(string(ListItem));
end;

// Exported function to delete an item from a list
procedure DeleteFromList(ListNo: Integer;
ListItemIndex: integer); cdecl;
begin
GetList(ListNo).Delete(ListItemIndex);
end;

// Exported function to return the count of items in a list
function ListSize(ListNo: integer): Integer; cdecl;
begin
Result := GetList(ListNo).Count;
end;

// Exported function to free a list once we're done with it
procedure FreeList(ListNo: Integer); cdecl;
var List: TStrings;
begin
// Free it but do not delete it from the list of
// lists otherwise all other list numbers will change!
List := GetList(ListNo)
if List <> nil then List.Free;
ListOfLists[ListNo] := nil;
end;

// Create List of lists when UDF library is loaded
initialization
ListOfLists := TList.Create;

// Destroy all lists when UDF library is unloaded
finalization
while ListOfLists.Count > 0 do
begin
FreeList(0);
ListOfLists.Delete(0);
end;
FreeAndNil(ListOfLists);

exports
NewList, AddToList, IndexInList, DeleteFromList,
ListSize, FreeList;

I only want to create and work with a list in the context of a stored
procedure and once execution gets to the end of the stored procedure
I'm finished with my lists and happy to destroy them. In theory, this
should all work fine SO LONG AS THE UDF LIBRARY IS NOT UNLOADED AND
RELOADED IN THE MIDDLE OF MY STORED PROCEDURE....and I have no idea if
there is potential for that to happen?

So can someone please tell me, could a UDF library be unloaded and
reloaded by the firebird server thread running my stored procedure in
the middle of my stored procedure's execution? Or could I be
guaranteed that the instance of the udf dll would stay in memory at
least until the stored procedure finished running?

Also, I would love to know if separate firebird threads load their own
instance of a UDF dll into memory or if multiple firebird server
threads may use the same instance of a loaded udf library.

Many thanks in advance!
Ben :)