VMWare.Data.SQLFire.SQLFClientConnection

VMWare.Data.SQLFire.SQLFClientConnection establishes connections to SQLFire locators or servers using the Network client driver.

It provides the same functionality as the JDBC URL scheme 'jdbc:sqlfire://'. This class implements ADO.NET's System.Data.IDbConnection interface and extends the abstract System.Data.Common.DbConnection class. It has SQLFConnection class as its abstract base that will be used for other SQLFire connection types that may be provided in future.

Constructors

Method Description
SQLFClientConnection() Returns a new default connection object with no associated connection string. A connection string must be specified for this connection object to be useful before invocation of Open method.
SQLFClientConnection(string connectionString) Returns a new connection object with the specified connection string. The connection string must follow the same rules as described for the SQLFConnectionString property.

SQLFire-Specific Extensions

The sections that follow describe SQLFire specific extensions. The MSDN documentation of IDbConnection and DbConnection describe the other API methods.

ConnectionString

The ConnectionString is required to be of the form: Server=<host>:<port>[;<key>=<value>]. The semi-colon separated key, value pairs are optional and can be one of those as described below in section Open(Dictionary<string, object>). This can be provided in the constructor of SQLFClientConnection or set in the ConnectionString property.

AutoCommit

This property can be used to get or set auto-commit property for all subsequent transactional operations executed on this connection. If set to true then it causes an implicit commit to be invoked at the end of every statement execution in a transaction.

BeginSQLFTransaction

Overloaded methods to start a new SQLFire transaction without creating a SQLFTransaction object. The transaction can be controlled using the Commit or Rollback methods directly on the connection. The zero argument method will start a new transaction with isolation-level System.Data.IsolationLevel.Unspecified that maps to System.Data.IsolationLevel.ReadCommitted.

Commit

Commit the active transaction in progress on this connection. There is no support for nested transactions in SQLFire so at most one transaction can be in progress at a given time on a connection.

IsolationLevel

Property to get the isolation level for the current transaction, if any. If no transaction is in progress then this returns System.Data.IsolationLevel.Unspecified.

Open(Dictionary<string, object>)

Open the connection providing an optional set of properties. This set of properties can also be provided as part of the connection string as semi-colon separated <key>=<value> pairs..The available properties are:
  • load-balance: (Boolean) Indicates whether to attempt load-balancing for this connection by selecting a less loaded server for actual connection. Load balancing for clients works only if there is a locator in the SQLFire cluster, and clients should preferably use the locator for creating the connection. Default is true.
  • disable-streaming: (Boolean) If set to false then queries executed on the SQLFire servers will stream results as soon as some members have data. If set to true then streaming will be disabled and the query result will not be sent back till all servers in the cluster have completely sent back their results. If set to false then this has implications in the way failover works when servers fail – if a server happens to fail while some result has already been consumed by the client using a SQLFDataReader then the driver cannot transparently failover to the new server rather the client will receive a SQLFException with State “X0Z01�?. Default is false.
  • user: (String) The user name to be used for this connection if authentication is enabled on the servers.
  • password: (String) The password to be used for this connection if authentication is enabled on the servers.

Rollback

Rollback the active transaction in progress on this connection. There is no support for nested transactions in SQLFire so at most one transaction can be in progress at a given time on a connection.

Example: Connect to a single server with defaults and disable-streaming property

// Start a SQLFire server that starts a network server on localhost:1527
$ sqlf server start
Starting SQLFire Server using multicast for peer discovery: 239.192.81.1[10334]
Starting network server for SQLFire Server at address localhost/127.0.0.1[1527]
...

// Open a new connection to the network server running on localhost:1527
string host = "localhost";
int port = 1527;
string connectionStr = string.Format("server={0}:{1}", host, port);
using (SQLFClientConnection conn = new SQLFClientConnection(connectionStr)) {
  conn.Open();
  // perform operations
  conn.Close();
}

// Open a new connection to the server with streaming disabled
using (SQLFClientConnection conn = new SQLFClientConnection(connectionStr)) {
  Dictionary<string, string> props = new Dictionary<string, string>();
  props.Add("disable-streaming", "true");
  conn.Open(props);
  // perform operations
  conn.Close();
}

Example: Connect to a locator with defaults and load-balance property

// Start a SQLFire locator that starts a network server on localhost:1527
$ sqlf locator start -peer-discovery-port=3000
Starting SQLFire Locator using peer discovery on: 0.0.0.0[3000]
Starting network server for SQLFire Locator at address localhost/127.0.0.1[1527]
...

// Start a couple of SQLFire servers with network servers on different ports
$ sqlf server start -locators=localhost[3000] -client-port=1528 -dir=server1
Starting SQLFire Server using locators for peer discovery: localhost[3000]
Starting network server for SQLFire Server at address localhost/127.0.0.1[1528]
...

$ sqlf server start -locators=localhost[3000] -client-port=1529 -dir=server2
Starting SQLFire Server using locators for peer discovery: localhost[3000]
Starting network server for SQLFire Server at address localhost/127.0.0.1[1529]
...

// Open a new connection to the locator having network server on localhost:1527.
// In default mode the driver will transparently query the locator and connect
// to one of the servers as per load balancing for the real data connection.
string host = "localhost";
int port = 1527;
string connectionStr = string.Format("server={0}:{1}", host, port);
using (SQLFClientConnection conn = new SQLFClientConnection(connectionStr)) {
  conn.Open();
  // perform operations
  conn.Close();
}

// Open a new connection to a server with load balancing disabled explicitly
int port2 = 1528;
string connectionStr2 = string.Format("server={0}:{1}", host, port2);
using (SQLFClientConnection conn = new SQLFClientConnection(connectionStr2)) {
  Dictionary<string, string> props = new Dictionary<string, string>();
  props.Add("load-balance", "false");
  conn.Open(props);
  // perform operations
  conn.Close();
}

Example: Connect to a cluster that has BUILTIN authentication enabled

// Start a SQLFire locator with BUILTIN authentication.
// The system users should preferably be specified in sqlfire.properties rather
// than on command-line as in the example below (see section ). System user
// in example below is “gem1�? with password “gem1�?
$ sqlf locator start -peer-discovery-port=3000 -auth-provider=BUILTIN -sqlfire.user.gem1=gem1 -user=gem1 -password=gem1
Starting SQLFire Locator using peer discovery on: 0.0.0.0[3000]
Starting network server for SQLFire Locator at address localhost/127.0.0.1[1527]
...

// Start a couple of SQLFire servers with network servers on different ports
$ sqlf server start -locators=localhost[3000] -auth-provider=BUILTIN -sqlfire.user.gem1=gem1 -user=gem1 -password=gem1 -dir=server1 -client-port=1528
Starting SQLFire Server using locators for peer discovery: localhost[3000]
Starting network server for SQLFire Server at address localhost/127.0.0.1[1528]
...

$ sqlf server start -locators=localhost[3000] -auth-provider=BUILTIN -sqlfire.user.gem1=gem1 -user=gem1 -password=gem1 -dir=server2 -client-port=1529
Starting SQLFire Server using locators for peer discovery: localhost[3000]
Starting network server for SQLFire Server at address localhost/127.0.0.1[1529]
...

// Create a new user (username/password as gem2/gem2) from SQLF command-shell.
$ sqlf
sqlf version 10.4 
sqlf> connect client 'localhost:1527;user=gem1;password=gem1';
sqlf> call sys.create_user('sqlfire.user.gem2', 'gem2'); 
Statement executed. 
sqlf> quit;

// Open a new connection to the locator having network server on localhost:1527
// with username and password in the connection string.
string host = "localhost";
int port = 1527;
string user = "gem2";
string passwd = "gem2";
string connectionStr = string.Format("server={0}:{1};user={2};password={3}",
                                     host, port, user, passwd);
using (SQLFClientConnection conn = new SQLFClientConnection(connectionStr)) {
  conn.Open();
  // perform operations
  conn.Close();
}

// Open a new connection to the locator having network server on localhost:1527
// with username and password passed as properties.
string connectionStr2 = string.Format("server={0}:{1}", host, port);
using (SQLFClientConnection conn = new SQLFClientConnection(connectionStr2)) {
  Dictionary<string, string> props = new Dictionary<string, string>();
  props.Add("user", user);
  props.Add("password", passwd);
  conn.Open(props);
  // perform operations
  conn.Close();
}