Establish a Connection to Another Database Only in a Block

Establish a connection to another database only in a block?

It would be nice if you keep all database connections in database.yml

development:
  adapter: mysql2
  other stuff...
  
db_2:
  adapter: mysql2
  other stuff..

other_envs:
.....

Then create a class

class OtherDB < ActiveRecord::Base
  establish_connection(:db_2)
end

From your controller you can access just like

OtherDB.table_name = "table_name"
OtherDB.first

Check my blog here http://imnithin.github.io/multiple-database.html

The reason we use using statement in the following code?

1) The reason for the using statement is to ensure that the myEntities.Dispose() method is called as soon as the code inside the using statement completes. This is necessary since the PlanetWroxEntities class' base class is a DbContext which holds a connection to the database.

Database connections are a precious resource that are obtained from the database connection pool and there are only a finite number of connections available that your whole application must share. If you don't call Dispose on the myEntities object as soon as you are done with it via the using statement (or some other manner) then the myEntities object will continue to tie up that database connection for some undetermined amount of time until the garbage collection code from the .net memory manager gets around to reclaiming the memory that is held by the myEntities object that is no longer in use.

The memory manager will at that time calls the dispose method. As soon as the Dispose method is called the database connection is released from the object and returned to the database connection pool where it can be obtained by other parts of your application when you make a call to get a database connection (for example when you create other instances of the PlanetWroxEntities class.)

The purpose of the using statement is to have the Dispose method called as soon as you are done with the object so that in this case the database connection is returned to the pool right away.

2) It means the database connection is released by your application and is returned to the database connection pool where it is now available to be obtained by other parts of your application. Here is one resource that talks more about connection pooling, it will give you more background on what is going on under the hood: https://msdn.microsoft.com/en-us/library/8xx3tyca.aspx

Under the hood the using statement is implemented as a finally statement. So this code:

using (PlanetWroxEntities myEntities = new PlanetWroxEntities())    
{
    var authorizedReviews = from review in myEntities.Reviews    
                            where review.Authorized == true    
                            orderby review.CreateDateTime descending    
                            select review;

    GridView1.DataSource = authorizedReviews.ToList();

    GridView1.DataBind();    
} 

becomes

PlanetWroxEntities myEntities; 
try{
    myEntities = new PlanetWroxEntities();
    var authorizedReviews = from review in myEntities.Reviews    
                            where review.Authorized == true    
                            orderby review.CreateDateTime descending    
                            select review;

    GridView1.DataSource = authorizedReviews.ToList();

    GridView1.DataBind();   

} finally{
    myEntities.Dispose();
}

In the old days of C# we always had to write the finally statement ourselves to dispose of disposable objects so that their unmanaged resources were released. But later the using statement was introduced in C# to make it easier to accomplish this. Even today, you can write the finally statement yourself if you prefer, but most people love the using statement.

For more information see https://msdn.microsoft.com/en-us/library/yh598w02.aspx

Efficient way to pull data from second database?

For simple scenarios, Rails can support this without any extra gems; simply define the database in database.yml:

other_db:
  adapter: mysql2
  encoding: utf8
  database: other_db
  username: user
  password: passwd
  host: 1.2.3.4
  port: 3306

Then in the model you want to use the other database add:

class Article < ActiveRecord::Base
  establish_connection(:other_db)
  self.table_name = 'other_db.articles'
end

And then you can perform your query:

Article.where("id > 1000")

=)

How to reconnect to database within try-catch block?

Update:

If you are wishing to simply reconnect the database, you could create a class specifically for this, make a getConnection method for it, and then simply return the re-established connection.
e.g:

public class ConnectionManager {

private Connection connection;
public String driverURL = "[Your driver URL]";

    public Connection getConnection(String user, String password) {
    try {
        //SQLServerDriver or whichever you are using
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        connection = DriverManager.getConnection(driverURL, username, password);
    }catch(ClassNotFoundException e) {
        e.printStackTrace();
    }
    catch(SQLException e) {
        e.printStackTrace();
    }
    return connection;
}

And then:

con = new ConnectionManager().getConnection(user, pass);

From there you can call the method again and retry the insert or go about it however you please.

How can I use one database connection object in whole application?

I suppose you need singleton pattern, here is quick example:

public class Connect_db {        
    static Connection con=null;
    public static Connection getConnection()
    {
        if (con != null) return con;
        // get db, user, pass from settings file
        return getConnection(db, user, pass);
    }

    private static Connection getConnection(String db_name,String user_name,String password)
    {
        try
        {
            Class.forName("com.mysql.jdbc.Driver");
            con=DriverManager.getConnection("jdbc:mysql://localhost/"+db_name+"?user="+user_name+"&password="+password);
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }

        return con;        
    }
} 

then you will be able to use connection like this:

Connect_db.getConnection().somemethods();

but, you should think - how this will work in multi-threaded environment, when several threads are trying to make requests to database.

In Python, how to make sure database connection will always close before leaving a code block?

The traditional approach is the try/finally statement:

def do_something_that_needs_database ():
    dbConnection = MySQLdb.connect(host=args['database_host'], user=args['database_user'], passwd=args['database_pass'], db=args['database_tabl'], cursorclass=MySQLdb.cursors.DictCursor)
    try:
       # as much work as you want, including return, raising exceptions, _whatever_
    finally:
       closeDb(dbConnection)

Since Python 2.6 (and 2.5 with a from __future__ import with_statement), there is an alternative (although try/finally still works perfectly well!): the with statement.

with somecontext as whatever:
   # the work goes here

A context has an __enter__ method, executed on entry (to return the whatever above, if you want) and an __exit__ method, executed on exit. Despite the elegance, since there is no existing context that works the way you want, the work needed to build one (although reduced in 2.6 with contextlib) should probably suggest that good old try/finally is best.

If you have 2.6 and want to try contextlib, this is one way you could do it to "hide" the try/finally...:

import contextlib

@contextlib.contextmanager
def dbconnect(**kwds):
  dbConnection = MySQLdb.connect(**kwds)
  try:
    yield dbConnection
  finally:
    closeDb(dbConnection)

to be used as:

def do_something_that_needs_database ():
    with dbconnect(host=args['database_host'], user=args['database_user'], 
                   passwd=args['database_pass'], db=args['database_tabl'], 
                   cursorclass=MySQLdb.cursors.DictCursor) as dbConnection:
       # as much work as you want, including return, raising exceptions, _whatever_

It may be worth it if you are going to use this many, many times, just to avoid repeating the try/finally over and over for each of those many uses.

To create new DB connection or not?

It's rarely advantageous to drop connections and re-establish them. Making a connection to a DB is normally a fairly heavy process. Lots of apps create connection pools just to avoid this: make some reasonable number of connections and then keep them for long periods of time, maybe even forever, letting each thread or user take connections from the pool when you need them and then giving them back.

If you're having a problem with connections getting orphaned -- the query fails and you never manage to free the connection -- the real solution is to implement proper exception handling so that doesn't happen.

If you have one thread sitting on an inactive connection while other threads are failing because the database has hit its connection maximum, then, yes, you need to look at freeing up connections.

Side note: MySQL claims that it can make connections very quickly, so that this is less of an issue than for other database engines. I have never benchmarked this.

---

Related Topics