ODBC Connection problem

Feb 2, 2011 at 1:53 PM
Edited Feb 2, 2011 at 1:55 PM

Hi, i'm trying to create a new module as a MVC 3 project.Everything is ok with the new module setup.

The problem is when i try to open a OdbcConnection in my HomeController to a Sybase Adaptive Server Anywhere version 11 server.

 string connectionString = string.Format("DSN={0};UID={1};PWD={2};", "TestOrchard", "dba", "sql");

var c = new OdbcConnection(connectionString);

c.Open();

c.Close();

After that the folowing eror occures in Repository.cs at:

 public virtual T Get(int id) {  return Session.Get<T>(id);        }

Error message:

While preparing SELECT contentite0_.Id as Id43_0_, contentite0_.Data as Data43_0_, contentite0_.ContentType_id as ContentT3_43_0_ FROM Orchard_Framework_ContentItemRecord contentite0_ WHERE contentite0_.Id=@p0 an error occurred

Inner Exception: The connection object can not be enlisted in transaction scope.

Thanks!

Feb 4, 2011 at 9:03 AM

Hello again, thx for your attention.By the way is the same thing when trying to open OleDbConnection.Give it a try.

I think its a Nhibernate opening session problem.

Thanks.

Coordinator
Feb 4, 2011 at 5:48 PM

Now that sounds like a bug. Would you mind opening it on the issue tracker?

Feb 5, 2011 at 12:22 PM

I have similar problem as well, with Mysql database. believe its not db related as such.

the code works if the db calls are part of the base Controller though.

logged a workitem here: http://orchard.codeplex.com/workitem/17308

thanks!

 

 

Feb 6, 2011 at 6:35 AM

Hi Bertrand, I've already posted it on the Issue Tracker. It's 

Item # 17291

Thanks.

Feb 6, 2011 at 6:47 AM

Hi, Juzfun, what do you mean by "the code works if the db calls are part of the base Controller though."?

Could you be a little more specific?

Thank you.

By the way, everything works fine when using SqlConnection class.

Feb 7, 2011 at 1:02 AM

@razvan: Mine is obviously is an MVC app, so if the db calls are made part of the base controller it seem to work. for eg:

lets say i have an AdminController which inherit from my app specific base controller (say MyAppBaseController, which in turn derives from System.Web.Mvc.Controller type).

Now if i have the db related initializatoin code inside the MyAppBaseController (in its constructor may be) it works just fine.

 

 

Feb 7, 2011 at 8:02 PM

Hello again,

Bertrand, i've read your comment on the IssueTracker.

To be more explicit: i need to write for my company small web apps for an existing big project. This small apps needs to expose data on the web from the existing databases (to be more specific Sybase databases). Now...our customers needs this small apps integrated in bigger projects, web portals, with CMS functions provided. I find Orchard perfect for this as we use MVC for those apps. Please tell me if this is a wrong aproach.

Thank you!

Coordinator
Feb 7, 2011 at 9:16 PM

Ah, OK, I'll link the bug to this thread. I should have made the connection but somehow didn't. Yes, this should work, this is definitely a bug.

Feb 8, 2011 at 8:08 AM

Hello again,

this code works fine:

[Themed]  

public class HomeController:Controller

 public OdbcConnection Connection { get; set; }   {

 public ActionResult Index()        {

 Connection = new OdbcConnection("DSN=aaTestOrchard_;UID=dba;PWD=sql;");  

         Connection.Open();

 //   Connection.Close();    

    //   Connection.Dispose();      

     return View();        }    }

 

However, if i uncomment Connection.Close();  or Connection.Dispose();      it fails again.

 

Thanks.

Feb 8, 2011 at 10:17 AM

You seem to be lucky there, I wouldnt have a problem if closing connection solves this. that should be the ideal thing to do well.  but in my case, closing/disposing connections doesn't solve this.

I am using MySqlConnection btw...

Coordinator
Feb 8, 2011 at 6:17 PM

Yes, it seems like the problem is when you *do* close the connection, which you should absolutely do.

Feb 9, 2011 at 6:31 AM

@juzfun: i said that closing the connection is the problem not viceversa:P

Coordinator
Feb 9, 2011 at 6:39 AM

Absolutely. That was in answer to juzfun's post, not yours.

Coordinator
Apr 3, 2011 at 11:52 PM

Fortunately there is a very simple workaround for this: suppress the ambient transaction scope at the moment in time when you are opening the connection to your database. This way the connection will open normally without enlisting in the ambient transaction. The following code sample illustrates this.

const string connectionString = @"Data Source=.\sqlexpress;Initial Catalog=tempdb;Integrated Security=True";

var connection = new SqlConnection(connectionString);

using (new TransactionScope(TransactionScopeOption.Suppress)) {

    connection.Open();

}

// the connection may now be used normally and closed at any time

connection.Close();


Apr 12, 2011 at 6:03 AM

I have a same problem when using another ORM for Data Layer... 

Is this issue resolved in 1.1?

Coordinator
Apr 12, 2011 at 6:11 AM

Same workaround that Louis explained right above your post.

Apr 12, 2011 at 6:19 AM

But what if I am using an ORM Framework that opens the connection internally....

For ex, my code looks like this :

IDayaLayer dal = FetchDataLayer(connectionString);

Session custSession = new Session(dal);

Customer cust = new Customer(custSession);

cust.FirstName = "Jon";

cust.LastName = "Koo";

cust.Save();

I don't deal with transaction scopes etc., The framework does those things for me internally...

Coordinator
Apr 12, 2011 at 6:22 AM

The code above does not create a transaction, it prevents the current one from affecting your code.

Jan 17, 2013 at 8:04 PM

we can make the connection with another bank, in our case it was an excel spreadsheet using the above code,

using (new TransactionScope (TransactionScopeOption.Suppress)) {Connection.Open ();}
and add using System.Transactions; reference and on the respective module project.