Using v2.6 runtime date 01/16/2009, Adapter, SQL Server 2005

When I attempt to call UnitOfWork2.Commit, I get a ORMQueryConstructionException

"The insert query doesn't contain any fields."

What does this mean?

I am trying to save a new Contact recursively, which consists of 4 entities (all w identity pKeys, so the ID is never set on these entities): IndividualEntity (which is a subtype of ContactEntity) AddressEntity ContactAddressEntity PhoneEntity

The only thing added to the UnitOfWork2 is the IndividualEntity. Then I call Commit, with true to save recursively.

The UnitOfWork2 object properly determines and orders the entities to be saved, but throws that exception.

Any clues?

-Josh

I found this thread:

http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=10601

which talks about how a supertype with no fields set on it, doesn't save itself properly. Why is this? I do have an identity column (ContactID) on the abstract supertype (ContactEntity). I also have SQLServer2005 compatibility set.

Could you please post the relevant code where you are saving? Did you set/update any fields on the entities to save?

the save code looks like this:

        public void Save(UnitOfWork2 workToBeDone)
        {
            using (DataAccessAdapter adapter = SqlServerProxy.GetAdapter())
            {
                workToBeDone.Commit(adapter, true);
            }
        }

There are the following entities involved (these are listed in the insertQueue of the uow): 1) IndividualEntity (a SUBTYPE of abstract ContactEntity) I've set last name, first name, occupation, employer fields on this. 2) AddressEntity I've set Street, City, State, ZipCode on this 3) ContactAddressEntity References to AddressEntity and IndividualEntity have been made on properties Address, and Contact 4) PhoneEntity set Number property, and linked Contact property to the IndividualEntity

One more thing. Could you please post the exact exception message and stack trace?

The message is: "The insert query doesn't contain any fields."

Stack Trace: at SD.LLBLGen.Pro.ORMSupportClasses.UnitOfWork2.Commit(IDataAccessAdapter adapterToUse, Boolean autoCommit) at CL.Service.CLDataService.Save(UnitOfWork2 workToBeDone) in C:\Documents and Settings\Nick\My Documents\Visual Studio 2008\Projects\CL.Prism\CL.Service\CLDataService.cs:line 94 at CL.Infrastructure.EntityViewModel.Save() in C:\Documents and Settings\Nick\My Documents\Visual Studio 2008\Projects\CL.Prism\CL.Infrastructure\Views\EntityViewModel.cs:line 48 at CL.Controls.Contact.ContactModel.Save() in C:\Documents and Settings\Nick\My Documents\Visual Studio 2008\Projects\CL.Prism\CL.Controls\Contact\ContactModel.cs:line 49 at CL.Infrastructure.EntityViewModel.<.ctor>b__0() in C:\Documents and Settings\Nick\My Documents\Visual Studio 2008\Projects\CL.Prism\CL.Infrastructure\Views\EntityViewModel.cs:line 25 at CL.Infrastructure.RelayCommand.Execute(Object parameter) in C:\Documents and Settings\Nick\My Documents\Visual Studio 2008\Projects\CL.Prism\CL.Infrastructure\Commands\RelayCommand.cs:line 156 at MS.Internal.Commands.CommandHelpers.CriticalExecuteCommandSource(ICommandSource commandSource, Boolean userInitiated) at System.Windows.Controls.Primitives.ButtonBase.OnClick() at System.Windows.Controls.Button.OnClick() at System.Windows.Controls.Primitives.ButtonBase.OnKeyUp(KeyEventArgs e) at System.Windows.UIElement.OnKeyUpThunk(Object sender, KeyEventArgs e) at System.Windows.Input.KeyEventArgs.InvokeEventHandler(Delegate genericHandler, Object genericTarget) at System.Windows.RoutedEventArgs.InvokeHandler(Delegate handler, Object target) at System.Windows.RoutedEventHandlerInfo.InvokeHandler(Object target, RoutedEventArgs routedEventArgs) at System.Windows.EventRoute.InvokeHandlersImpl(Object source, RoutedEventArgs args, Boolean reRaised) at System.Windows.UIElement.RaiseEventImpl(DependencyObject sender, RoutedEventArgs args) at System.Windows.UIElement.RaiseEvent(RoutedEventArgs args, Boolean trusted) at System.Windows.Input.InputManager.ProcessStagingArea() at System.Windows.Input.InputManager.ProcessInput(InputEventArgs input) at System.Windows.Input.InputProviderSite.ReportInput(InputReport inputReport) at System.Windows.Interop.HwndKeyboardInputProvider.ReportInput(IntPtr hwnd, InputMode mode, Int32 timestamp, RawKeyboardActions actions, Int32 scanCode, Boolean isExtendedKey, Boolean isSystemKey, Int32 virtualKey) at System.Windows.Interop.HwndKeyboardInputProvider.ProcessKeyAction(MSG& msg, Boolean& handled) at System.Windows.Interop.HwndSource.CriticalTranslateAccelerator(MSG& msg, ModifierKeys modifiers) at System.Windows.Interop.HwndSource.OnPreprocessMessage(Object param) at System.Windows.Threading.ExceptionWrapper.InternalRealCall(Delegate callback, Object args, Boolean isSingleParameter) at System.Windows.Threading.ExceptionWrapper.TryCatchWhen(Object source, Delegate callback, Object args, Boolean isSingleParameter, Delegate catchHandler) at System.Windows.Threading.Dispatcher.WrappedInvoke(Delegate callback, Object args, Boolean isSingleParameter, Delegate catchHandler) at System.Windows.Threading.Dispatcher.InvokeImpl(DispatcherPriority priority, TimeSpan timeout, Delegate method, Object args, Boolean isSingleParameter) at System.Windows.Threading.Dispatcher.Invoke(DispatcherPriority priority, Delegate method, Object arg) at System.Windows.Interop.HwndSource.OnPreprocessMessageThunk(MSG& msg, Boolean& handled) at System.Windows.Interop.HwndSource.WeakEventPreprocessMessage.OnPreprocessMessage(MSG& msg, Boolean& handled) at System.Windows.Interop.ThreadMessageEventHandler.Invoke(MSG& msg, Boolean& handled) at System.Windows.Interop.ComponentDispatcherThread.RaiseThreadMessage(MSG& msg) at System.Windows.Threading.Dispatcher.PushFrameImpl(DispatcherFrame frame) at System.Windows.Threading.Dispatcher.PushFrame(DispatcherFrame frame) at System.Windows.Window.ShowHelper(Object booleanBox) at System.Windows.Window.Show() at System.Windows.Window.ShowDialog()

This thread confirms the issue on Access databases, but claims that with SQL Server databases, there should be no issue.

http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=15123

tomahawk wrote:

This thread confirms the issue on Access databases, but claims that with SQL Server databases, there should be no issue.

http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=15123

Yes, that's correct. when there are no fields in an entity but a PK which is an identity field, there's no insert query possible: INSERT INTO table () VALUES () would be the query to execute which fails. On sqlserver, you can specify DEFAULT instead of () to make the query work but on access you can't. In general it's not a good idea to have a table with just an identity field, as the entity instance (the data) is not there: there's no instance at all, as there's only an artificial number which isn't part of the entity instance itself (as it's an artificial pk)

My structure is as follows:

Contact ---------Individual | | | |-----Business | | | |-----Committee | |-----Address | |-----Phone | |-----Email

So, because each contact subtype has an address, phone, and email, I need the intermediate supertype.

So I ask, if SQL Server can use the DEFAULT keyword, does LLBLGen leverage this? If so, how and, if not, what do I need to do to get this functionality working?

Also a question about the polymorphic fetch support. LLBLGen documentation states:

In our previous example about BoardMember and CompanyCar, BoardMember is a derived type of Manager which is a derived type of Employee. While this might not be the best OO hierarchy thinkable, it's enough to illustrate the point: if FetchEntity is called by passing in an Employee instance, and the PK identifies a BoardMember, only the Employee's fields are loaded, however if the entity is in a hierarchy of type TargetPerEntity, LLBLGen Pro will perform joins with all subtypes from the supertype, to make sure a type is stored OK.

The last phrase

...if the entity is in a hierarchy of type TargetPerEntity, LLBLGen Pro will perform joins with all subtypes from the supertype, to make sure a type is stored OK.

leads me to believe that if I do the following:

ContactEntity c = new ContactEntity(1023); // passing the pKey
adapter.FetchEntity(c);

that LLBLGen would perform the joins with subtypes and return a populated Individual, Business, or Committee entity appropriately.

How can I get this behavior? (without resorting to a stored proc)

My structure is as follows:

Contact ---------Individual | | | |-----Business | | | |-----Committee | |-----Address | |-----Phone | |-----Email

So, because each contact subtype has an address, phone, and email, I need the intermediate supertype.

So I ask, if SQL Server can use the DEFAULT keyword, does LLBLGen leverage this? If so, how and, if not, what do I need to do to get this functionality working?

So Contact should have another fields other than the PK, which defines the Address, Phone and Email, right? In this case there should be no problem, implementing this design.

The last phrase ...if the entity is in a hierarchy of type TargetPerEntity, LLBLGen Pro will perform joins with all subtypes from the supertype, to make sure a type is stored OK.

leads me to believe that if I do the following:

ContactEntity c = new ContactEntity(1023); // passing the pKey adapter.FetchEntity(c);

that LLBLGen would perform the joins with subtypes and return a populated Individual, Business, or Committee entity appropriately.

How can I get this behavior? (without resorting to a stored proc)

FetchEntity is not polymorphic, FetchNewEntity is polymorphic you should be using it. Please go back to the same manual page and check, Polymorphic fetches

So Contact should have another fields other than the PK, which defines the Address, Phone and Email, right?

My ANSI diagram must not be clear enough. Each word in the diagram is a different table. Address, phone, and email, are not fields of Contact, they are separate tables with a many-to-one relationship to Contact (as a contact can have many of each).

FetchEntity is not polymorphic, FetchNewEntity is polymorphic you should be using it.

I will look at the FetchNewEntity function, thanks.

So you don't have any common fields between Individual Committee and Business, perhaps a "Name" field, "ModifiedBy", "ModifiedDate" or "IsActive".....etc?

Not presently, but I do plan on adding some system specific fields (TimeStamp, CreatedBy, etc) in the future. I guess correct queries will have to wait until then? I guess I expect LLBLGen to handle this edge case, as I can see a few other posts about this.

As Frans (Otis) has explained, it's not a good idea to have a table with only an artificial PK. That's why it's not on our to do list.

tomahawk wrote:

Not presently, but I do plan on adding some system specific fields (TimeStamp, CreatedBy, etc) in the future. I guess correct queries will have to wait until then? I guess I expect LLBLGen to handle this edge case, as I can see a few other posts about this.

What exactly IS the edge case here? You get this error because: 1) there's no identity PK AND 2) there are no fields to insert.

this means that there's nothing to insert. IF there would be an identity field, it could use DEFAULT and it will produce INSERT INTO table DEFAULT VALUES but because it detects no such field, it can't do anything and reports this as an error. As I explained above: no data == no entity instance == something is wrong with your model.

When I look at your model, I see 3 elements which can't possibly be an entity: address, email and phone. These are values of an entity, not entities by themselves, because they have no identity of their own: the data they are formed with (the single value) is the identity which means they're an attribute, associated with an entity, here contact.

Keep in mind that inheritance is something which is possible, though it has a cost: the more types you create, the more performance you'll pay for it. This because RDBMS's don't support inheritance at the relational model level, so it has to be interpreted from the data using joins, which can be expensive at runtime.

That was it, I didn't have identity turned on for Contact. Thanks for the help...will turn on identity and try again. My impression was that it was on, and LLBLGen wasn't able to use DEFAULT. My mistake!

Address, Email, and Phone are their own entities (tables), and they contain fields like Street, City, State; EmailAddress, EmailTypeID; PhoneNum, PhoneTypeID, with pkeys, and foreign keys to ContactID.