This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
Dear, We have been evaluating the Lightspeed ORM product and have recently acquired a professional license. Our product communicates with 2 types of databases, Microsoft SQL Server and Oracle, so using Lightspeed as a uniform data access layer was an obvious choice. However, we have been experiencing problems with certain queries launched against an Oracle database server 10.2.0.0.0. Many primary/foreign key fields in our database have a fixed length such as char(38). When a value of such a field is shorter than 38, it will be padded with spaces by the database. When launching queries containing these fields in the WHERE-clause, we have experienced different behavior between SQL Server and Oracle. For example:
Returns 1 record on both SQL Server an Oracle, when launched in Query Analyzer/SQL Plus. When we do the same from Lightspeed:
This returns 1 record on SQL Server and 0 records on Oracle. When looking deeper into this, LS seems to build a parametrized query and we suspect the problem has to lie there. Testing with a pure ADO .NET implementation with a parametrized query results in success:
Without setting DbType and Size, we can mimic LS’s behavior. However, when we set the type and size we get a record back. This leads us to believe that the DbType or Size is not correctly set inside Ligthspeed (possibly due to us not specifying it in the model). Due to the data type not being set, LS adds the parameter as a varchar (DbType.AnsiString), so Oracle performs a Nonpadded Comparison, which compares the table’s padded value with the parameter’s non-padded value, and this of course doesn’t match. We have tried to override the PrepareCommand()-method, and set the DbType and Size on the needed parameters but this yielded no success. What can we do so the data type of the parameters is set correctly ? Thanks in advance. |
|
|
Hmm, from what you show in your ADO.NET fragment, modifying the DbType and Size in PrepareCommand should work. PrepareCommand is about the last thing we call before executing the command, so all parameters should be present and nothing should be overriding any changes you make in PrepareCommand. I assume you have set a breakpoint and verified that your override is being called? We do precompile FindById queries in current nightly builds. This shouldn't affect PrepareCommand behaviour, but could you let us know if you are using 4.0 RTM or a recent nightly? (You can get the build number from the main Mindscape.LightSpeed.dll.) Do you get the same behaviour if you write an explicit ID query ( |
|
|
Also, for diagnostics, if you set |
|
|
Hi Ivan, Thanks for the quick suggestions. It seems like we figured it out: Instead of calling the So basically what we do is the following:
I think another option could have been to change the data types of the entity fields from string to char[], but this would affect the existing codebase too much. Thanks for the pointers. |
|