Devart Forums

LordFjord

Hello,

I have the case to filter data depending on the value of a flagged enum, the Devart.Data.Linq.Provider throws a System.NotSupportedException: Operation is not supported: BitAnd at Devart.Data.Oracle.Linq.Provider.Query.a.a(SqlNodeType A_0)

Code Example:

Code: Select all

var result = context.MyObject.Where( x => (x.MyColumn & (int)MyFlaggedEnum.SomeValue) == 0);

Are there any plans to support BitAnd operations via LINQ? This works with the .NET System.Data.Linq library on a MS SQL database.
Also, Oracle has a builtin bitand function if i remember right.

LordFjord

Hi,
you have probably similar problems as I have (see my post on it here).

Does the Name column of your entity allow nulls?

LordFjord

Hi,
I found this thread after some searching, but i havent found any good solution or workaround for the oracle - null - string.Empty issue.

In the oracle world, an empty string is the same as null, and it cannot be inserted into not nullable columns. (for whatever reasons)

It might be easy to tackle this issue if you only use oracle databases, however with a mixed environment of ms sql server and oracle things get messy. I am porting a ms sql database to oracle, so i cant simply change colums to be nullable on the oracle side.

In addition to this, at some points it is indeed important to distuingish between an empty and a not-existing value.

What I am testing right now is to replace empty strings with a " " (blank).
I have edited the template for my entity developer model to add methods to all non-nullable string properties that handle the conversion of string empty to " " back and forth.

This looked promising in the beginning, but it failed on more complex LINQ queries. I guess the properties are not used while processing those?

Small example:
context is my datacontext to access the DB. MyProperty1 of the object MyObject has a method added to its get/set that handles the string.empty->" " conversion.

Code: Select all

MyObject obj = context.MyObjects.SingleOrDefault(x => x.MyProperty1 == myProp1);

The Property is not accessed on this call, the value is not converted and the query is returning null even if the data exists in the database.

Similar to this, when inserting data, I'm getting the ORA-01400 Cannot insert NULL... errors.

So far the only working (and very very tedious and error-prone) workaround I found is to manually check before each DB access if the values are ok. As you can imagine with a lot of table this is nearly not maintainable.

Has anyone found a generic approach to handle this issue? Any help would be appretiated.

LordFjord

Im not sure why but it seems to work now. Probably some oversight on my end.

LordFjord

I have managed to reproduce the problem with a small test project.
I couldnt update my system to the new version yet as my project is in the final phases.

I have sent the test project to the devart support email address.

LordFjord

Hello,
i have made a custom template to my Entity Developer project and with the tool it is working file. However, in Visual Studio 2010, the DevartLinqToSqlGenerator still uses the default template file.

I have stored the template file in the same directory as the lqml file.

What did I do wrong? I would like to use the custom template together with Visual Studio.

LordFjord

A few additions:

If I rewrite the code not to use ExecuteCommand but generate the context objects and use InsertOnSubmit with SubmitChanges, things work fine.

If I rewrite the code to use ExecuteCommand and build the SQL string including parameters manually, things work fine. However in this case I would need to implement something to catch all the reserved characters and the like to not mess up the SQL.

If I used ExecuteCommand and replace the empty strings with a " ", then it works, BUT only if this happens on a single parameter. Once I replace two or more, the query is messed up in a similar way as I described it in my previous post.

LordFjord

Hello,
I am using dotConnect SQLite professional (version 2.90.146.0).
The problem occurs when i am using ExecuteCommand on the sqlite context where a parameter contains an emtpy string.
In this case, the parameter value is simply gone, and the value of the next parameter takes its place, shifting all paramters after it to the left.
The error does not occur if i replace the empty strings with a blank.

A small example, maybe this helps to reproduce it on your side:

The table, nothing fancy

Code: Select all

CREATE TABLE MYTABLE (
UID NVARCHAR(36) NOT NULL PRIMARY KEY,
DATA1 INT NULL,
DATA2 INT NULL,
DATA3 NVARCHAR(128) NULL,
DATA4 DATETIME NULL,
DATA5 NVARCHAR(128) NULL
);

Some sample code how i fill the table (sqliteContext is the datacontext for the sqlite db file):

Code: Select all

string sqlCommand = "INSERT INTO MYTABLE (UID, DATA1, DATA2, DATA3, DATA4, DATA5) VALUES ({0}, {1}, {2}, {3}, {4}, {5});";
sqliteContext.ExecuteCommand( sqlCommand,
Guid.NewGuid().ToString(),
0,
1,
string.Empty, // if I use some replacement string like " " then it works
DateTime.UtcNow,
"SomeText");

The date ends up in the DATA3 string instead of in DATA4. The DATA4 and DATA5 values are empty as well.

LordFjord

I have fixed my problems after many retries, reinstalling devart, rolling back to older versions and so on...

Previously, my businesslogic called the begintransaction, committransaction and rollbacktransaction methods of my database wrapper manually.

Now, i am returning a transaction object to my businesslogic and use it with a using block around the operations, which call my database component.

Initially, i tried to avoid making my businesslogic independant from DbTransaction (or any directly database related objects), seems that didnt work out.

I am still not sure what the cause of my initial problem was, probably multithreading or other race-condition related things.

Anyway, I found a workaround that is acceptable, and it is recommended to use transactions in "using"-blocks anyway.

Maybe this helps others who run into similar issues...

Greetings,
LordFjord

LordFjord

Hello,
I have trouble with the ORA-01453 error message (SET TRANSACTION must be first statement of transaction).
I am using dotConnect Oracle professional version 5.70.146.0. Oracle server is Oracle 10g Express 10.2.0.1.

I have built a wrapper around the dotconnect drivers, so i can use oracle, ms sql, mysql ...

The wrapper starts the connection, handles the transactions and executes command and queries.

In the businesslogic i have a timer that periodically takes objects from a queue and processes the designated queries for these.

During this time, the database connection is kept open, only the transaction is started and committed (or rolled back in case of errors).
The oracle commands are created (with the sql, connection and transaction) for each sql i need to process and disposed after execution.

I am running into the ORA-01453 error at the point where i start the new transaction for the 2nd object, the error occurs. The 1st object is processed correctly (this includes a number of sql queries and commands, all within the transaction).

The transaction is null before i call begintransaction.

I have experimented with closing and reopening the connection for each object processing, without success. I have tried to explicitely call commit (or rollback) on the connection before starting the new transaction. This worked if i trigger the object processing one by one, but runs into the same error if i let it proceed automatically.
My guess is that some race-condition could be involved.
The timer created a new thread each time it runs, so my databasewrapper is called from a different thread each time, while maintaining the same connection and setting up new transactions for each processed object.

I have no trouble at all with the other database server types (mssql, mysql).

The weird thing is, that some time ago this worked, though I cannot tell if its because i updated the dotconnect components to the latest version or i messed something up in my code

I am looking for a hint or idea to solve this problem.

stacktrace:

Code: Select all

Error during BeginTransaction: Devart.Data.Oracle.OracleException: ORA-01453: SET TRANSACTION muss erste Anweisung der Transaktion sein   at Devart.Data.Oracle.bo.b(Int32 A_0)
   at Devart.Data.Oracle.an.a(Int32 A_0, a3 A_1)
   at Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
   at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
   at Devart.Data.Oracle.OracleCommand.ExecuteNonQuery()
   at Devart.Data.Oracle.OracleTransaction..ctor(OracleConnection A_0, IsolationLevel A_1)
   at Devart.Data.Oracle.OracleConnection.BeginTransaction(IsolationLevel il)

Thanks ahead.

LordFjord

Hello,
i have run into the same issues just today. I have tried to deploy my application on a 64 bit OS machine.
Could you send me the 64 bit version of sqlite3.dll as well or point me to a location where it is available for download?

I have installed dotConnect for SQLite pro on my 32 bit developer system, so i cant access the 64 bit version of the file, if i understood the previous posts correctly.

A suggestion:
If seperate sqlite3.dll-s are needed for 32 and 64 deployments, then they should be both available somewhere within the installed dotconnect sqlite files.

thanks ahead

LordFjord

Hello,
I am using dotConnect for Oracle 5.70.140.0

I get a NotSupportedException for the following query:

Code: Select all

List myObject2s = context.MyObjects.Where( n => n.ObjectID == objectID).Select( o => o.MyObject2s ).ToList();

MyObject2 and MyObject are connected via a 1-many assotiation.

I want to retrieve all MyObject2s into a list that belong to a MyObject with the given ID.

Stacktrace

Code: Select all

System.NotSupportedException: Specified method is not supported.   at Devart.Data.Linq.Provider.h.a(ax A_0)
   at Devart.Data.Linq.Provider.h.a(SqlExpression A_0)
   at Devart.Data.Linq.Provider.h.a(bi A_0)
   at Devart.Data.Linq.Provider.h.a(SqlExpression A_0)
   at Devart.Data.Linq.Provider.h.a(SqlExpression A_0, Boolean A_1)
   at Devart.Data.Linq.Provider.m.a(u A_0, SqlExpression A_1, Boolean A_2)
   at Devart.Data.Linq.Provider.m.a(u A_0, SqlExpression A_1)
   at Devart.Data.Linq.Provider.m.a(Type A_0, SqlExpression A_1, u A_2)
   at Devart.Data.Linq.Provider.DataProvider.CompiledQuery.GetReaderFactory(List`1 elementInstanceTypes, u services, SqlNode query)
   at Devart.Data.Linq.Provider.DataProvider.CompiledQuery..ctor(QueryInfo queryInfo, u services, Boolean isQueryObjectByKey, Object queryObjectKey)
   at Devart.Data.Linq.Provider.DataProvider.BuildQuery(Expression query)
   at Devart.Data.Linq.Provider.DataProvider.Devart.Data.Linq.Provider.IProvider.Compile(Expression query)
   at Devart.Data.Linq.DataQuery`1.i()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)

The same code works on MS SQL Server 2008 with the Microsoft LINQtoSQL libs.

If i divide the query into 2 subqueries (one to retrieve MyObject and one to get the MyObject2 in a seperate query) then it works. Could be an issue with ToList or associations.

Any help would be appreciated.

LordFjord

A few more things:
The exception has definately something to do with the trigger. If i disable the trigger, the commands run without errors (but, well, my lastmodifieddate isnt updated).

LordFjord

Hello,
I have a table with a TIMESTAMP(3) column that represents the last modified date of the object as UTC time without offsets. The defaut value is SYS_EXTRACT_UTC(SYSTIMESTAMP).
I have set up a trigger to automate this, on the sql level it works fine.

When I read the object via LINQ, modify it and commit the changes, I get a "System.ArgumentOutOfRangeException: Year, Month, and Day parameters describe an un-representable DateTime." exception.

Some additional info:
I am using 5.70.140.0 dotConnect for oracle (professonal edition).

The column definition:

Code: Select all

CREATE TABLE "MYUSER"."MYTABLE"
  (
...
LASTMODIFIEDDATE" TIMESTAMP (3) DEFAULT SYS_EXTRACT_UTC(SYSTIMESTAMP) NOT NULL ENABLE,
...

The trigger definition:

Code: Select all

create or replace
TRIGGER "MYUSER"."TRIGGERNAME" BEFORE
  UPDATE OF  ON MYUSER.MYTABLE
FOR EACH ROW
BEGIN
	:NEW.LASTMODIFIEDDATE := SYS_EXTRACT_UTC(SYSTIMESTAMP);
END;

The C# code to fetch the data, modify it and write it back:

Code: Select all

...
MyObject myObject = context.MyTables.SingleOrDefault(x => x.ID == id);
if ( myObject != null )
{
  myObject.SomeValue = value;
}
context.SubmitChanges();

The LastModifiedDate column's mapping:

Code: Select all

[Column(Name = @"LASTMODIFIEDDATE", Storage = "_LastModifiedDate", CanBeNull = false, DbType = "TIMESTAMP(3) NOT NULL", IsDbGenerated = true, UpdateCheck = UpdateCheck.Never)]

The stacktrace:

Code: Select all

Devart.Data.Linq.LinqCommandExecutionException: Error on executing DbCommand. ---> System.ArgumentOutOfRangeException: Year, Month, and Day parameters describe an un-representable DateTime.   at System.DateTime.DateToTicks(Int32 year, Int32 month, Int32 day)
   at Devart.Data.Oracle.OracleTimeStamp.get_Value()
   at Devart.Data.Oracle.OracleParameter.a(OracleDbType A_0, Object A_1, Type& A_2, Object& A_3, Byte[] A_4, Hashtable A_5, Int32 A_6, Int32 A_7, Int32 A_8, Int32 A_9, Boolean A_10, Boolean A_11, OracleCommand A_12, ParameterDirection A_13, av A_14, String A_15)
   at Devart.Data.Oracle.OracleParameter.a(aq& A_0, Boolean A_1, Boolean A_2, OracleCommand A_3, Byte[] A_4, Hashtable A_5, av A_6)
   at Devart.Data.Oracle.OracleCommand.a(OracleParameterCollection A_0, aq[] A_1, bt A_2, av A_3)
   at Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
   at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
   at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
   at Devart.Data.Linq.Provider.DataProvider.ExecuteQuery(String commandText, List`1 resultsetParameters, IList`1 parameters, Boolean isBatch, IDbCommand& dbCommand)
   --- End of inner exception stack trace ---
   at Devart.Data.Linq.LinqCommandExecutionException.a(String A_0, Exception A_1)
   at Devart.Data.Linq.Provider.DataProvider.ExecuteQuery(String commandText, List`1 resultsetParameters, IList`1 parameters, Boolean isBatch, IDbCommand& dbCommand)
   at Devart.Data.Linq.h.a(SubmitCommand A_0, SubmitCommandBuilder A_1)
   at Devart.Data.Linq.h.a(SubmitedObject A_0, SubmitCommand A_1, SubmitCommandBuilder A_2)
   at Devart.Data.Linq.r.a(SubmitedObject A_0, SubmitCommand A_1, SubmitCommandBuilder A_2)
   at Devart.Data.Linq.s.a(m A_0, af A_1, Object A_2, Boolean A_3)
   at Devart.Data.Linq.aa.a(s A_0, af A_1, Boolean A_2)
   at Devart.Data.Linq.aa.a(DataContext A_0, ConflictMode A_1)
   at Devart.Data.Linq.aa.b(DataContext A_0, ConflictMode A_1)
   at Devart.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode)
   at Devart.Data.Linq.DataContext.SubmitChanges()

Is there a known issue with timestamps/triggers/LINQ?

LordFjord

Thank you very much for the hint, disabling collection pooling helped

Devart Forums

Search found 18 matches

Oracle BitAnd support

Using custom templates with VS

ExecuteCommand empty parameter error

ORA-01453 SET TRANSACTION... problem

System.NotSupportedException with ToList()

Trigger on Timestamp column issues (with LINQ)