In Firebird I have a simple SP with one output parameter being "char(16) character set octets". That's what's basically used for Guids in Firebird.

create or alter procedure AAA_TEST
returns (
    R char(16) character set octets)
as
begin
    suspend;
end

If I try to import this stored procedure, the result set is not properly inferred. Attached debugger shows ArgumentNullException in SD.Tools.Algorithmia.dll with message:

Value cannot be null.
Parameter name: typeDefinition

Also check stacks below.

Any idea what's wrong? Maybe something in provider.

Stack (in exception):

   at SD.Tools.Algorithmia.UtilityClasses.ArgumentVerifier.CantBeNull(Object argument, String name) in C:\Myprojects\VS.NET Projects\Algorithmia_hg\SD.Tools.Algorithmia\UtilityClasses\ArgumentVerifier.cs:line 58
   at SD.LLBLGen.Pro.DBDriverCore.DBField..ctor(String fieldName, DBTypeDefinition typeDefinition, String description, Boolean enableChangeTracking, Boolean isNullable, Int32 ordinalPosition)
   at SD.LLBLGen.Pro.DBDriverCore.DBSchemaRetriever.CreateResultsetFromSchemaTable(DBStoredProcedure owningProcedure, DataTable resultsetMetaData, Int32 resultsetNumber)
   at SD.LLBLGen.Pro.DBDriverCore.DBSchemaRetriever.ExecuteResultsetRetrievalQuery(DbConnection openConnection, DbCommand command, DBStoredProcedure storedProcedure, CommandBehavior behavior)

Stack (complete, from debugger):

SD.Tools.Algorithmia.dll!SD.Tools.Algorithmia.UtilityClasses.ArgumentVerifier.CantBeNull(object argument, string name) Line 60  C#
    SD.LLBLGen.Pro.DBDriverCore.dll!SD.LLBLGen.Pro.DBDriverCore.DBField.DBField(string fieldName, SD.LLBLGen.Pro.DBDriverCore.DBTypeDefinition typeDefinition, string description, bool enableChangeTracking, bool isNullable, int ordinalPosition) + 0x1f bytes    
    SD.LLBLGen.Pro.DBDriverCore.dll!SD.LLBLGen.Pro.DBDriverCore.DBSchemaRetriever. CreateResultsetFromSchemaTable(SD.LLBLGen.Pro.DBDriverCore.DBStoredProcedure owningProcedure, System.Data.DataTable resultsetMetaData, int resultsetNumber) + 0x1ef bytes    
    SD.LLBLGen.Pro.DBDriverCore.dll!SD.LLBLGen.Pro.DBDriverCore.DBSchemaRetriever. ExecuteResultsetRetrievalQuery(System.Data.Common.DbConnection openConnection, System.Data.Common.DbCommand command, SD.LLBLGen.Pro.DBDriverCore.DBStoredProcedure storedProcedure, System.Data.CommandBehavior behavior) + 0x70 bytes   
    SD.LLBLGen.Pro.DBDriverCore.dll!SD.LLBLGen.Pro.DBDriverCore.DBSchemaRetriever. RetrieveResultsetsForStoredProcedure(System.Data.Common.DbConnection openConnection, SD.LLBLGen.Pro.DBDriverCore.DBStoredProcedure storedProcedure) + 0x1f6 bytes    
    SD.LLBLGen.Pro.DBDriverCore.dll!SD.LLBLGen.Pro.DBDriverCore.DBSchemaRetriever. RetrieveStoredProcedureResultsetMetaData(SD.LLBLGen.Pro.DBDriverCore.DBSchema schemaToFill, System.Collections.Generic.IEnumerable<SD.LLBLGen.Pro.DBDriverCore.DBElementName> elementNames) + 0x17a bytes    
    SD.LLBLGen.Pro.DBDriverCore.dll!SD.LLBLGen.Pro.DBDriverCore.DBSchemaRetriever. BatchRunner<System.__Canon>(SD.LLBLGen.Pro.DBDriverCore.DBSchema schemaToFill, SD.LLBLGen.Pro.DBDriverCore.DBSchemaRetriever.MetaDataType typeOfMetaData, System.Collections.Generic.IEnumerable<System.__Canon> elementNames) + 0x97 bytes  
    SD.LLBLGen.Pro.DBDriverCore.dll!SD.LLBLGen.Pro.DBDriverCore.DBSchemaRetriever. Batcher< SD.LLBLGen.Pro.DBDriverCore.DBElementName>(SD.LLBLGen.Pro.DBDriverCore.DBSchema schemaToFill, System.Collections.Generic.ICollection<SD.LLBLGen.Pro.DBDriverCore.DBElementName> names, SD.LLBLGen.Pro.DBDriverCore.DBSchemaRetriever.MetaDataType typeOfMetaData, string formattedSubTaskDescription) + 0x1d9 bytes 
    SD.LLBLGen.Pro.DBDriverCore.dll!SD.LLBLGen.Pro.DBDriverCore.DBSchemaRetriever. RetrieveSchema(SD.LLBLGen.Pro.DBDriverCore.DBCatalog catalog, SD.LLBLGen.Pro.DBDriverCore.DBSchemaElementNameContainer nameContainer) + 0x1aa bytes  
    SD.LLBLGen.Pro.DBDriverCore.dll!SD.LLBLGen.Pro.DBDriverCore.DBCatalogRetriever. RetrieveCatalog(string catalogName, System.Collections.Generic.List<SD.LLBLGen.Pro.DBDriverCore.DBSchemaElementNameContainer> elementsToRetrieve) + 0x207 bytes 
    SD.LLBLGen.Pro.DBDriverCore.dll!SD.LLBLGen.Pro.DBDriverCore.DBDriverBase. PopulateCatalogs( System.Collections.Generic.Dictionary<SD.LLBLGen.Pro.Core.ProgressCallBack,object> callBacks, System.Collections.Generic.Dictionary<string, System.Collections.Generic.List<SD.LLBLGen.Pro.DBDriverCore.DBSchemaElementNameContainer>> elementsToRetrieve) + 0x139 bytes    
    LLBLGenPro.exe!SD.LLBLGen.Pro.Gui.Classes.GuiController. ObtainMetaDataFromDatabase(SD.LLBLGen.Pro.DBDriverCore.DBDriverBase driverUsed, System.Collections.Generic.Dictionary< string,System.Collections.Generic.List<SD.LLBLGen.Pro.DBDriverCore.DBSchemaElementNameContainer>> selectedElements) + 0xb1 bytes    
    LLBLGenPro.exe!SD.LLBLGen.Pro.Gui.Classes.GuiController.PerformRefreshMetaDataAction(string driverID) + 0x3f5 bytes 
    LLBLGenPro.exe!SD.LLBLGen.Pro.Gui.Controls.CatalogExplorer.HandleRefreshMetaDataCmd() + 0x68 bytes  
    LLBLGenPro.exe!SD.LLBLGen.Pro.Gui.Controls.CatalogExplorer._commandManager_CommandClick(object sender, Janus.Windows.UI.CommandBars.CommandEventArgs e) + 0x251 bytes   
    Janus.Windows.UI.v3.dll!Janus.Windows.UI.CommandBars.UICommandManager.OnCommandClick( Janus.Windows.UI.CommandBars.CommandEventArgs e) + 0x5a bytes 
    Janus.Windows.UI.v3.dll!Janus.Windows.UI.CommandBars.UICommandManager. Janus.Windows.UI.CommandBars.ICommandOwner.OnCommandClicked(Janus.Windows.UI.CommandBars.CommandEventArgs e) + 0x33 bytes    
    Janus.Windows.UI.v3.dll!Janus.Windows.UI.CommandBars.UICommandCollection.c(object , Janus.Windows.UI.CommandBars.CommandEventArgs ) + 0x13 bytes    
    Janus.Windows.UI.v3.dll!Janus.Windows.UI.CommandBars.UICommand.OnClick( Janus.Windows.UI.CommandBars.CommandEventArgs e) + 0x5a bytes   
    Janus.Windows.UI.v3.dll!Janus.Windows.UI.CommandBars.UICommand.ar() + 0x16a bytes   
    Janus.Windows.UI.v3.dll!Janus.Windows.UI.Internal.JNSCZ.OnMouseUp(System.Windows.Forms.MouseEventArgs e) + 0xe3 bytes   
    System.Windows.Forms.dll!System.Windows.Forms.Control.WmMouseUp(ref System.Windows.Forms.Message m, System.Windows.Forms.MouseButtons button, int clicks) + 0x274 bytes 
    System.Windows.Forms.dll!System.Windows.Forms.Control.WndProc(ref System.Windows.Forms.Message m) + 0x894 bytes 
    Janus.Windows.UI.v3.dll!Janus.Windows.UI.Internal.JNSCZ.WndProc(ref System.Windows.Forms.Message m) + 0x5a bytes    
    System.Windows.Forms.dll!System.Windows.Forms.Control.ControlNativeWindow.OnMessage(ref System.Windows.Forms.Message m) + 0x11 bytes    
    System.Windows.Forms.dll!System.Windows.Forms.Control.ControlNativeWindow.WndProc(ref System.Windows.Forms.Message m) + 0x39 bytes  
    System.Windows.Forms.dll!System.Windows.Forms.NativeWindow.Callback(System.IntPtr hWnd, int msg, System.IntPtr wparam, System.IntPtr lparam) + 0x80 bytes   
    [Native to Managed Transition]  
    [Managed to Native Transition]  
    Janus.Windows.UI.v3.dll!Janus.Windows.UI.Internal.JNSCZ.k() + 0x14e7 bytes  
    Janus.Windows.UI.v3.dll!Janus.Windows.UI.Internal.JNSCZ.b(System.Drawing.Point ) + 0xc6f bytes  
    Janus.Windows.UI.v3.dll!Janus.Windows.UI.CommandBars.UIContextMenu.a(System.Windows.Forms.Control , System.Drawing.Point , int ) + 0x1e2 bytes  
    Janus.Windows.UI.v3.dll!Janus.Windows.UI.CommandBars.UIContextMenu.Show(System.Windows.Forms.Control control) + 0x2e bytes  
    Janus.Windows.UI.v3.dll!Janus.Windows.UI.CommandBars.UICommandManager.b(object , System.Windows.Forms.MouseEventArgs ) + 0x102 bytes    
    System.Windows.Forms.dll!System.Windows.Forms.Control.OnMouseUp(System.Windows.Forms.MouseEventArgs e) + 0x5d bytes 
    LLBLGenPro.exe!SD.LLBLGen.Pro.Gui.Controls.MultiSelectTreeView.OnMouseUp(System.Windows.Forms.MouseEventArgs e) + 0x5f bytes    
    System.Windows.Forms.dll!System.Windows.Forms.TreeView.WmNotify(ref System.Windows.Forms.Message m) + 0x423 bytes   
    System.Windows.Forms.dll!System.Windows.Forms.TreeView.WndProc(ref System.Windows.Forms.Message m) + 0xf4 bytes 
    System.Windows.Forms.dll!System.Windows.Forms.Control.ControlNativeWindow.OnMessage(ref System.Windows.Forms.Message m) + 0x11 bytes    
    System.Windows.Forms.dll!System.Windows.Forms.Control.ControlNativeWindow.WndProc(ref System.Windows.Forms.Message m) + 0x39 bytes  
    System.Windows.Forms.dll!System.Windows.Forms.NativeWindow.Callback(System.IntPtr hWnd, int msg, System.IntPtr wparam, System.IntPtr lparam) + 0x80 bytes   
    [Native to Managed Transition]  
    [Managed to Native Transition]  
    System.Windows.Forms.dll!System.Windows.Forms.Control.SendMessage(int msg, System.IntPtr wparam, System.IntPtr lparam) + 0x21 bytes 
    System.Windows.Forms.dll!System.Windows.Forms.Control.ReflectMessageInternal(System.IntPtr hWnd, ref System.Windows.Forms.Message m) + 0x3b bytes   
    System.Windows.Forms.dll!System.Windows.Forms.Control.WmNotify(ref System.Windows.Forms.Message m) + 0x17 bytes 
    System.Windows.Forms.dll!System.Windows.Forms.Control.WndProc(ref System.Windows.Forms.Message m) + 0x3ef bytes 
    System.Windows.Forms.dll!System.Windows.Forms.ScrollableControl.WndProc(ref System.Windows.Forms.Message m) + 0x2a bytes    
    System.Windows.Forms.dll!System.Windows.Forms.UserControl.WndProc(ref System.Windows.Forms.Message m) + 0x23 bytes  
    System.Windows.Forms.dll!System.Windows.Forms.Control.ControlNativeWindow.OnMessage(ref System.Windows.Forms.Message m) + 0x11 bytes    
    System.Windows.Forms.dll!System.Windows.Forms.Control.ControlNativeWindow.WndProc(ref System.Windows.Forms.Message m) + 0x39 bytes  
    System.Windows.Forms.dll!System.Windows.Forms.NativeWindow.Callback(System.IntPtr hWnd, int msg, System.IntPtr wparam, System.IntPtr lparam) + 0x80 bytes   
    [Native to Managed Transition]  
    [Managed to Native Transition]  
    System.Windows.Forms.dll!System.Windows.Forms.NativeWindow. DefWndProc(ref System.Windows.Forms.Message m) + 0x56 bytes 
    System.Windows.Forms.dll!System.Windows.Forms.Control.DefWndProc(ref System.Windows.Forms.Message m) + 0xa bytes    
    System.Windows.Forms.dll!System.Windows.Forms.TreeView.WmMouseDown(ref System.Windows.Forms.Message m, System.Windows.Forms.MouseButtons button, int clicks) + 0x73 bytes   
    System.Windows.Forms.dll!System.Windows.Forms.TreeView.WndProc(ref System.Windows.Forms.Message m) + 0xb5a bytes    
    System.Windows.Forms.dll!System.Windows.Forms.Control.ControlNativeWindow.OnMessage(ref System.Windows.Forms.Message m) + 0x11 bytes    
    System.Windows.Forms.dll!System.Windows.Forms.Control.ControlNativeWindow.WndProc(ref System.Windows.Forms.Message m) + 0x39 bytes  
    System.Windows.Forms.dll!System.Windows.Forms.NativeWindow.Callback(System.IntPtr hWnd, int msg, System.IntPtr wparam, System.IntPtr lparam) + 0x80 bytes   
    [Native to Managed Transition]  
    [Managed to Native Transition]  
    System.Windows.Forms.dll!System.Windows.Forms.Application. ComponentManager.System.Windows.Forms.UnsafeNativeMethods. IMsoComponentManager.FPushMessageLoop( System.IntPtr dwComponentID, int reason, int pvLoopData) + 0x24d bytes 
    System.Windows.Forms.dll!System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(int reason, System.Windows.Forms.ApplicationContext context) + 0x155 bytes  
    System.Windows.Forms.dll!System.Windows.Forms.Application.ThreadContext.RunMessageLoop(int reason, System.Windows.Forms.ApplicationContext context) + 0x4a bytes    
    System.Windows.Forms.dll!System.Windows.Forms.Application.Run(System.Windows.Forms.Form mainForm) + 0x31 bytes  
    LLBLGenPro.exe!SD.LLBLGen.Pro.Gui.Program.DoApplicationStart(string fileToLoad) + 0x153 bytes   
    LLBLGenPro.exe!SD.LLBLGen.Pro.Gui.Program.Main(string[] args) + 0xf9 bytes  
    [Native to Managed Transition]  
    LLBLGenPro_NET40.exe!Net40Runner.Program.Main(string[] args) + 0xb4 bytes       

Apparently it can't find a proper .NET type for the parameter's type. I'll see if I can reproduce it locally, with the current firebird provider.

The .NET type reported in the datatable (which is the 'resultset' of the proc, when called with null-ed parameters) is 'Guid'. The firebird driver doesn't have a mapping for guid as it's not a known type to be supported, and therefore the type definition is null, causing the exception.

I won't add that type for that specific char construct, as it would mean everyone using that char type will now get a guid .net type, which might not be what the user wants. I don't know why the char type is reported as the guid .net type. It might be a 'nice' feature for some, (no type converters needed) but alternatively: if the .net type changes for a given DB type, we can't support it any longer as it's not stable: people who now have char(16) character set octets as a table field type, will get a guid with your latest firebird provider (2.7.7), which is IMHO not what should be done: maybe they don't want to store a guid in there but something else (e.g. 16 bytes, or a 128 bit integer... who knows?)

I had a feeling it's something about metadata. I'll fix that. Could you provide a piece of code you're using, so I'm sure the fix is 100% correct?

cincura.net wrote:

I had a feeling it's something about metadata. I'll fix that. Could you provide a piece of code you're using, so I'm sure the fix is 100% correct?

What I do is this:

private bool ExecuteResultsetRetrievalQuery(DbConnection openConnection, DbCommand command, DBStoredProcedure storedProcedure, CommandBehavior behavior )
{
    bool succeeded = false;
    using(DbTransaction transaction = openConnection.BeginTransaction())
    {
        try
        {
            command.Transaction = transaction;
            using(DbDataReader reader = command.ExecuteReader(behavior))
            {
                int resultsetCounter = 1;
                reader.Read();
                DataTable metaData = reader.GetSchemaTable();
                DBStoredProcedureResultset toAdd = CreateResultsetFromSchemaTable(storedProcedure, metaData, resultsetCounter);
//.......
                reader.Close();
            }
            succeeded = true;
        }
//...........

        }
        finally
        {
            transaction.Rollback();
        }
    }
    return succeeded;
}

So I call a GetSchemaTable() on the normal command to execute the proc AFTER I've executed the proc. The datatable returned from GetSchemaTable contains the Guid type definition for the column.

Can you post exact code what are you checking? I added the Guid, but LLBLGen Pro still crashes.

Otis wrote:

So I call a GetSchemaTable() on the normal command to execute the proc AFTER I've executed the proc. The datatable returned from GetSchemaTable contains the Guid type definition for the column.

Please check the code in the last post.

Walaa wrote:

Please check the code in the last post.

And how it's going to help me in this particular case? I don't know what's happening in CreateResultsetFromSchemaTable. The Guid is as parameter in reader.GetSchemaTable with proper DbType (also FbDbType) referece. The conn.GetSchema("DataTypes") contains Guid as well.

cincura.net wrote:

Walaa wrote:

Please check the code in the last post.

And how it's going to help me in this particular case? I don't know what's happening in CreateResultsetFromSchemaTable. The Guid is as parameter in reader.GetSchemaTable with proper DbType (also FbDbType) referece. The conn.GetSchema("DataTypes") contains Guid as well.

The problem is: the GetSchemaTable returns 1 row, and reports that the .net type is Guid. But our Firebird driver doesn't have a Guid type mapped as anything, so it won't be able to create a typedescription object. Now, you can add the Guid to the list of types the firebird provider supports, but as it simply converts a char type to a Guid, we won't add the mapping ourselves,simply because it would break every firebird application which has that same char type and which isn't used as a guid. (i.o.w.: you use a type converter inside the provider, which you shouldn't do.)

I'm little bit lost. So what can I do to import the result set properly, without exception?

Your provider shouldn't report a 'Guid' as type for char(16) octets as it's not a guid.

Ahh, that's not possible. This default conversion is there since beginning, as char(16) octets is considered guid. Not only by provider, even by engine i.e. when it returns the result for functions like GEN_UUID it's char(16) octets.

How we're going to solve it?

we have to add the guid <-> char(16) octets mapping to the tables in the driver. I have no idea how this type is reported by firebird in the various versions though. So if I have a firebird 1.5 server with a table with char(16) octets typed fields, will I get the same type as when I have a firebird 2.5 server with char(16) octets typed fields?

If so: we'll add it, if not, we won't.

Do you know whether this is the case: does firebird 1.5 with char(16) octets typed fields return guids for those fields or char strings?

Yes, you'll get the same type in 1.5, 2.0, 2.1, 2.5 - char(16) octets. The functions and client libraries are in charge of handling it as guid (i.e. http://www.firebirdsql.org/refdocs/langrefupd21-intfunc-gen_uuid.html).

Ok, we'll see how we can fix this by adding the type mapping.

There are a couple of things: - The FbDbType.Guid value, when was it introduced? If it was introduced very recently, it will give us a breaking change, and I don't want that. - A datareader, what will GetValue(ordinal) return for the char(16) octets field? I guess a guid ? - the schemas for view fields, table fields and parameters returned by connection.GetSchema(), they do report CHARACTER_SET_NAME, which is needed for this feature. I assume they're doing that since a long time?

Otis wrote:

• The FbDbType.Guid value, when was it introduced? If it was introduced very recently, it will give us a breaking change, and I don't want that.

It's there at least since 2006, where the SVN history stops. I was not checking the original CVS history.

Otis wrote:

• A datareader, what will GetValue(ordinal) return for the char(16) octets field? I guess a guid ?

Yes.

Otis wrote:

• the schemas for view fields, table fields and parameters returned by connection.GetSchema(), they do report CHARACTER_SET_NAME, which is needed for this feature. I assume they're doing that since a long time?

Yes.

Ok, driver side is complete. I now have to add the guid -> char(16) octets conversion to the model first DDL templates and we're set.

Will have an updated package for you within an hour or less.

Good work! Appreciate it.

Done . test works OK (saving and loading)

Attached you'll find a zip file. In it there are two files. Copy both as administrator and to the folders below.

The file: FieldCreationInclude.lpt to : <llblgen pro installation folder>\Frameworks\DDL SQL\Templates\FirebirdSpecific This file is needed for model-first work. I have to admit, model first on firebird isn't 100% solid at the moment as I couldn't find any documentation on DDL SQL statements, so some might fail. If they do, please provide me with a working alternative and I'll fix the templates.

The file: SD.LLBLGen.Pro.DBDrivers.FirebirdDBDriver.dll to: <llblgen pro installation folder>\Drivers\Firebird

Simply refresh your existing project should make the appropriate changes.

When I replace these files I get:

The XML contains a reference to a driver with ID: 'BCF16BE3-7E29-4E07-AC27-9FBF79D3CA7A' which isn't loaded, likely due to a missing ADO.NET provider.

LLBLGen Pro version 3.5. Build May 1st, 2012
-----[Core exception]--------------------
   at SD.LLBLGen.Pro.ApplicationCore.MetaData.MetaDataStore.DeserializeTargetDatabase(XmlReader reader)
   at SD.LLBLGen.Pro.ApplicationCore.MetaData.MetaDataStore.DeserializeFromReader(XmlReader reader)
   at SD.LLBLGen.Pro.ApplicationCore.ProjectClasses.Project.DeserializeFromReader(XmlReader reader, String additionalTypeConverterFolder)
   at SD.LLBLGen.Pro.ApplicationCore.ProjectClasses.Project.Load(String filename, String additionalTypeConverterFolder)
   at SD.LLBLGen.Pro.Gui.Classes.GuiController.PerformOpenProjectAction(String filenameToOpen)

Ahh, loooks like the latest LLBLGen Pro has this fix included.

yes, but the one attached should also have worked. But, I think you ran into a thing I ran into a lot as well: you have the .NET 4 version in the GAC and you ran the designer on .net 3.5. Or you didn't change the definition in the llblgenpro.exe.config file.