This question is locked. New answers and comments are not allowed.
Is it possible to link objects to eachother by using a unique field from an object instead of the primary key of the object?
For example:
Table "Location"
id as integer (PRIMARY KEY)
name as string (UNIQUE!)
Table "container"
id as integer
locationName as string (and not locationId as integer)
Reasons:
The reason why i can't use the the id number is because the locationname might not always refer to a record in the table "location", but it might also refer to a record of (for example) a table "machine". All "location" names start with an "L" , so "L10023" would be a location which could have id "3", but will have the name "L10023". If the locationName would be "MWrapper1", it would mean the container is currently on a machine with the name of "MWrapper1", which could also have an id of "3".
I was able to create the database diagrams for this in SQL server, but when mapping it with ORM, OpenAccess keeps binding the "locationname" field from the container table, to the "id" field of the location table.
For example:
Table "Location"
id as integer (PRIMARY KEY)
name as string (UNIQUE!)
Table "container"
id as integer
locationName as string (and not locationId as integer)
Reasons:
The reason why i can't use the the id number is because the locationname might not always refer to a record in the table "location", but it might also refer to a record of (for example) a table "machine". All "location" names start with an "L" , so "L10023" would be a location which could have id "3", but will have the name "L10023". If the locationName would be "MWrapper1", it would mean the container is currently on a machine with the name of "MWrapper1", which could also have an id of "3".
I was able to create the database diagrams for this in SQL server, but when mapping it with ORM, OpenAccess keeps binding the "locationname" field from the container table, to the "id" field of the location table.
6 Answers, 1 is accepted
0
Myth
Top achievements
Rank 1
Rank 1
answered on 22 Feb 2010, 03:19 PM
More info:
1)
It does appear the reversemapping file is configured correctly, as it has this info:
2)
While my "container" has a field "locationName", i also had it create a one-to-many list, so i can access my containers from inside my storagelocation object
3)
Openaccess fails when trying to access the StorageLocation.Containers" property, which is a list of container objects.
4)
Error that is generated:
Telerik.OpenAccess.Exceptions.DataStoreException was unhandled
CanRetry=False
Message="Telerik.OpenAccess.RT.sql.SQLException: Conversion failed when converting the nvarchar value 'L111_07_4' to data type int. at Telerik.OpenAccess.RT.Adonet2Generic.Impl.ResultSetImp.next() at OpenAccessRuntime.Relational.fetch.FetchResultImp.rsNext() at OpenAccessRuntime.Relational.fetch.FetchResultImp.hasNext() "
Source="Telerik.OpenAccess"
StackTrace:
at Telerik.OpenAccess.SPI.Backends.ThrowException(Exception e)
at Telerik.OpenAccess.RT.ExceptionWrapper.Throw()
at OpenAccessRuntime.DataObjects.PCStateMan.handleException(Exception x)
at OpenAccessRuntime.DataObjects.PCStateMan.getObjectFieldImp(PersistenceCapable pc, FieldMetaData fmd, Object currentValue)
at OpenAccessRuntime.DataObjects.PCStateMan.getObjectField(PersistenceCapable pc, Int32 field, Object currentValue)
at ....StorageLocation.OpenAccessEnhancedGet_unitLoads(StorageLocation )
at ....StorageLocation.get_UnitLoads() in C:\...\StorageLocation.vb:line 136
.........
.........
1)
It does appear the reversemapping file is configured correctly, as it has this info:
<field name="_storageLocation">
<extension key="db-column" value="mssql">
<extension key="db-type" value="VARCHAR" />
<extension key="db-column-name" value="LocationName" />
<extension key="db-sql-type" value="nvarchar" />
<extension key="db-length" value="50" />
</extension>
</field>
2)
While my "container" has a field "locationName", i also had it create a one-to-many list, so i can access my containers from inside my storagelocation object
3)
Openaccess fails when trying to access the StorageLocation.Containers" property, which is a list of container objects.
4)
Error that is generated:
Telerik.OpenAccess.Exceptions.DataStoreException was unhandled
CanRetry=False
Message="Telerik.OpenAccess.RT.sql.SQLException: Conversion failed when converting the nvarchar value 'L111_07_4' to data type int. at Telerik.OpenAccess.RT.Adonet2Generic.Impl.ResultSetImp.next() at OpenAccessRuntime.Relational.fetch.FetchResultImp.rsNext() at OpenAccessRuntime.Relational.fetch.FetchResultImp.hasNext() "
Source="Telerik.OpenAccess"
StackTrace:
at Telerik.OpenAccess.SPI.Backends.ThrowException(Exception e)
at Telerik.OpenAccess.RT.ExceptionWrapper.Throw()
at OpenAccessRuntime.DataObjects.PCStateMan.handleException(Exception x)
at OpenAccessRuntime.DataObjects.PCStateMan.getObjectFieldImp(PersistenceCapable pc, FieldMetaData fmd, Object currentValue)
at OpenAccessRuntime.DataObjects.PCStateMan.getObjectField(PersistenceCapable pc, Int32 field, Object currentValue)
at ....StorageLocation.OpenAccessEnhancedGet_unitLoads(StorageLocation )
at ....StorageLocation.get_UnitLoads() in C:\...\StorageLocation.vb:line 136
.........
.........
0
Myth
Top achievements
Rank 1
Rank 1
answered on 25 Feb 2010, 08:58 AM
Loading the unitload object itself doesn't seem to work either (unitload = my "container" class in the previous explenations.
The following code generates an error at the unitloadResult.single line:
| _scope.Transaction.Begin() |
| Dim unitloadResult As Linq.IQueryable(Of UnitLoad) = _ |
| From u In _scope.Extent(Of UnitLoad)() _ |
| Select u Where u.LocationName = locationName |
| _scope.Transaction.Commit() |
| Return unitloadResult.Single |
This generates the following error:
| Telerik.OpenAccess.Exceptions.DataStoreException was unhandled |
| CanRetry=False |
| Message="Error reading field ....UnitLoad._storageLocation from ResultSet: System.FormatException: Input string was not in a correct format. at Telerik.OpenAccess.RT.Adonet2Generic.Impl.ResultSetImp.getInt(Int32 columnIndex) at OpenAccessRuntime.Relational.ResultSetParams.ReadIntNullable(ResultSetParams& p) at OpenAccessRuntime.Relational.RelationalGenericOID.copyKeyFields(ResultSetParams& p) at OpenAccessRuntime.Relational.RelationalGenericOID.copyKeyFields(ResultSet rs, Int32 firstCol) at OpenAccessRuntime.Relational.RelationalGenericState.getFieldData(ResultSetParams& para) at OpenAccessRuntime.Relational.RelationalGenericState.copyPass1Fields(ResultSet rs, FetchGroupField[] fetchGroups, Int32 firstCol) System.FormatException: Input string was not in a correct format. at Telerik.OpenAccess.RT.Adonet2Generic.Impl.ResultSetImp.getInt(Int32 columnIndex) at OpenAccessRuntime.Relational.ResultSetParams.ReadIntNullable(ResultSetParams& p) at OpenAccessRuntime.Relational.RelationalGenericOID.copyKeyFields(ResultSetParams& p) at OpenAccessRuntime.Relational.RelationalGenericOID.copyKeyFields(ResultSet rs, Int32 firstCol) at OpenAccessRuntime.Relational.RelationalGenericState.getFieldData(ResultSetParams& para) at OpenAccessRuntime.Relational.RelationalGenericState.copyPass1Fields(ResultSet rs, FetchGroupField[] fetchGroups, Int32 firstCol)" |
| Source="Telerik.OpenAccess" |
| StackTrace: |
| at Telerik.OpenAccess.SPI.Backends.ThrowException(Exception e) |
| at Telerik.OpenAccess.RT.ExceptionWrapper.Throw() |
| at OpenAccessRuntime.storagemanager.logging.LoggingStorageManager.executeQueryAll(ApplicationContext context, ImmutableQueryDetails query, CompiledQuery compiledQuery, Object[] para, Int32 skip, Int32 take) |
| at OpenAccessRuntime.DataObjects.UnsynchronizedPMProxy.getAllQueryResults(CompiledQuery cq, Object[] para, Int32 skip, Int32 take) |
| at OpenAccessRuntime.DataObjects.ForwardQueryResult.resolve() |
| at OpenAccessRuntime.DataObjects.ForwardQueryResult.get_Count() |
| at Telerik.OpenAccess.RT.ListEnumerator.get_Count() |
| at Telerik.OpenAccess.Query.QueryContext.PerformDatabaseQuerySingle[T](Expression expression, Boolean& wasNull, String aggregateMethod, Int32 elemAt) |
| at Telerik.OpenAccess.Query.QueryContext.ExecuteDynamic[TResult,T](Expression expressionParam, IQueryable`1 query) |
| at Telerik.OpenAccess.Query.ObjectScopeQuery`2.Execute[TResult](Expression expressionParam) |
| at System.Linq.Queryable.Single[TSource](IQueryable`1 source) |
| at ....DatabaseManager.getUnitLoadAtLocation(String locationName) in C:\Users\...\DatabaseManager.vb:line 64 |
| at ....SetStorageLocation(StorageLocation location) in C:\Users\...\frmLocationDetails.vb:line 57 |
| at ....stockPanel_DoubleClick(Object sender, EventArgs e) in C:\Users\...\frmStockview.vb:line 211 |
| at System.Windows.Forms.Control.OnDoubleClick(EventArgs e) |
| at Telerik.WinControls.RadControl.OnDoubleClick(EventArgs e) |
| at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks) |
| at System.Windows.Forms.Control.WndProc(Message& m) |
| at System.Windows.Forms.ScrollableControl.WndProc(Message& m) |
| at Telerik.WinControls.RadControl.WndProc(Message& m) |
| at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) |
| at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) |
| at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam) |
| at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg) |
| at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData) |
| at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context) |
| at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context) |
| at System.Windows.Forms.Application.Run(ApplicationContext context) |
| at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun() |
| at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel() |
| at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine) |
| at ....My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81 |
| at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args) |
| at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args) |
| at System.Runtime.Hosting.ManifestRunner.Run(Boolean checkAptModel) |
| at System.Runtime.Hosting.ManifestRunner.ExecuteAsAssembly() |
| at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext, String[] activationCustomData) |
| at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext) |
| at System.Activator.CreateInstance(ActivationContext activationContext) |
| at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssemblyDebugInZone() |
| at System.Threading.ThreadHelper.ThreadStart_Context(Object state) |
| at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) |
| at System.Threading.ThreadHelper.ThreadStart() |
| InnerException: System.FormatException |
| Message="Input string was not in a correct format." |
| Source="Telerik.OpenAccess.Adonet2" |
| StackTrace: |
| at Telerik.OpenAccess.RT.Adonet2Generic.Impl.ResultSetImp.getInt(Int32 columnIndex) |
| at OpenAccessRuntime.Relational.ResultSetParams.ReadIntNullable(ResultSetParams& p) |
| at OpenAccessRuntime.Relational.RelationalGenericOID.copyKeyFields(ResultSetParams& p) |
| at OpenAccessRuntime.Relational.RelationalGenericOID.copyKeyFields(ResultSet rs, Int32 firstCol) |
| at OpenAccessRuntime.Relational.RelationalGenericState.getFieldData(ResultSetParams& para) |
| at OpenAccessRuntime.Relational.RelationalGenericState.copyPass1Fields(ResultSet rs, FetchGroupField[] fetchGroups, Int32 firstCol) |
| InnerException: |
0
Hi Myth,
The reasons behind the reported behavior is that OpenAccess links the objects by their internal Object Id's. The ObjectId used internally by OpenAccess to identify an object is composed of the PK fields of the table it is mapped to. So one of the workarounds for you would be to make the unique name field part of the primary key of the Location table and work with composite id in that table.
Kind regards,
Zoran
the Telerik team
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.
The reasons behind the reported behavior is that OpenAccess links the objects by their internal Object Id's. The ObjectId used internally by OpenAccess to identify an object is composed of the PK fields of the table it is mapped to. So one of the workarounds for you would be to make the unique name field part of the primary key of the Location table and work with composite id in that table.
Kind regards,
Zoran
the Telerik team
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.
0
Myth
Top achievements
Rank 1
Rank 1
answered on 26 Feb 2010, 08:08 AM
Hello Zoran,
You mentioned this is just one of the posssible work arounds. Would you mind me asking what the alternatives are? I'm not really fond of the idea of changing the primary keys on the table, as the table is also linked to other tables.
Thanks in advance.
ps: I was recommended by the Telerik e-mail support to create a support ticket of this case. Perhaps it would be easier to close either this thread or the ticket (ticket id# 285094) to prevent duplicate efforts by multiple Telerik staff members.
You mentioned this is just one of the posssible work arounds. Would you mind me asking what the alternatives are? I'm not really fond of the idea of changing the primary keys on the table, as the table is also linked to other tables.
Thanks in advance.
ps: I was recommended by the Telerik e-mail support to create a support ticket of this case. Perhaps it would be easier to close either this thread or the ticket (ticket id# 285094) to prevent duplicate efforts by multiple Telerik staff members.
0
Roger
Top achievements
Rank 1
Rank 1
answered on 14 Dec 2010, 06:20 PM
Myth, did you get the answer(s) you were looking for? I can't seem to get to the PITS ticket number you mentioned in your last post.
0
Myth
Top achievements
Rank 1
Rank 1
answered on 15 Dec 2010, 08:06 AM
Hi Roger.
The number in the post was a support ticket, not a PITS number. As for the solution, I did link to an id field of a the Location table. To be able to connect my "unitloads" to a machine, I linked the unitload to a location, and the location to a machine.
In the end i'm happy with this solution. Like it more than the original idea.
The number in the post was a support ticket, not a PITS number. As for the solution, I did link to an id field of a the Location table. To be able to connect my "unitloads" to a machine, I linked the unitload to a location, and the location to a machine.
In the end i'm happy with this solution. Like it more than the original idea.