Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Already on GitHub? Sign in to your account

Connection does not recover if a query had failed due to temporary network problem #79

Closed
yozik04 opened this Issue Oct 4, 2012 · 6 comments

Comments

Projects
None yet
4 participants

yozik04 commented Oct 4, 2012

I am on development build

Problem

Connection does not recover if you had an internet outage. Driver keeps using old connection...

Reproduction steps:

  1. Create DB connection to a remote database
  2. Make first query
  3. Disconnect from internet
  4. Make second query. This one will fail with:
    { [Error: [Microsoft][SQL Server Native Client 10.0]Communication link failure] sqlstate: '08S01', code: 121 }
  5. Connect internet back. Make sure you are connected.
  6. Make next queries. All queries will fail with:
    { [Error: [Microsoft][SQL Server Native Client 10.0]Communication link failure] sqlstate: '08S01', code: 0 }
Contributor

jkint commented Oct 4, 2012

Yes, this is the case. There is no connection resiliency in the driver.

If connection dropping is a problem, please use the module level query API. It will establish a new connection for every query, which should solve this problem.

Using the module level query API will give you the best of both worlds. Since a new connection is used, you won't be a susceptible from disconnections. However, for performance, when a connection is used (and it doesn't disconnect from error), it is not closed but instead returned to a connection pool. The next module query API will use this pooled connection, offering almost the same performance as an explicitly open connection.

@jguerin jguerin closed this Oct 4, 2012

CnApTaK commented Oct 22, 2012

what you mean by "module level query API". any example of this? i just need to know how i can use connection pool. does it working?

Contributor

jkint commented Oct 22, 2012

The "module level query API" is the one exposed directly by the module:

var mssql = require('msnodesql');

mssql.query( "Driver={SQL Server Native Client 11.0};Server=(local);...", "SELECT * FROM ...", function( err, results ) {
    assert.ifError( err ),
    console.log( results );
});

This is the module level query API, as opposed to the connection level API you can use after calling mssql.open:

var mssql = require('msnodesql');

mssql.open( "Driver=...", function( err, conn ) {

    conn.query( "SELECT * FROM ...", function ( err, results ) {
        assert.ifError( err );
        console.log( results );
    });
});

The connection pooling is handled by the ODBC driver manager, so you receive it for 'free'.

CnApTaK commented Oct 22, 2012

but if i need to use transactions logic in my app, i cant use "module level query API"? i need to figure out how to use driver to solve my problems... i mean.. i have a task... i have many tcp connections.. and after each connection i receive data witch must be written in two databases... ms sql and postgresql. so.. i need start transactions in both databases... make insert queries and if they was without errors - commit transactions... any ideas how ms sql part can be done with this driver?

Contributor

jkint commented Oct 22, 2012

Yes, this is a scenario we don't currently support. However, your need for such an option strongly encourages us to implement such an option. Thanks for your feedback.

For now the only transaction support we have is within the connection level API.

Contributor

jguerin commented Oct 22, 2012

You will need to implement your own connection-retry logic in the v0.2 driver if you wish to do Transactions on a connection which may break. If this is the case, you will also need to restart your Transaction, as a Transaction is tied to a connection, and the server will drop it if you disconnect. This driver is a developer preview for now, so we are evaluating feedback.

@CnApTaK, do you find that you cannot use a connection right now...? I'm not understanding how you are stuck?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment