Federations support? #33

Closed
elarcent opened this Issue Jul 15, 2012 · 9 comments

Projects

None yet

4 participants

@elarcent

Are Federations supported with the driver? I tried to make prepared query with "GO", and to chain the queries - it doesnt work. If Federations are supported please give example how to use. If not, please advise if support is in roadmap. Thanks!

@jguerin
Contributor
jguerin commented Jul 16, 2012

I'm getting more info about this, I'll update this when I've got it. :)

Cheers,

Jonathan

@jguerin jguerin was assigned Jul 16, 2012
@jkint
Contributor
jkint commented Jul 16, 2012

This is probably a clown question, but when you say "make a prepared query with 'GO'", do you mean you appended the word "GO" to the query as you would in SQL Server Management Studio?

Also, prepared queries are (for now) emulated in our driver. Support for true parameterized queries is forthcoming.

@mattneerincx

Federations will work with our node.js driver. The key thing is the USE FEDERATION command MUST be executed in a single query by itself (this is a requirement of USE FEDERATION). Then you execute the next query on the same connection to fire off the query to the selected federated database.

Note that GO is a special keyword that is only recognized by our command line and query tools (for example sqlcmd tool or SQL Server Management Studio tool). The underlying driver does not recognize the word GO. The tools as a convenience added the GO keyword as a batch separator. The tool searches for GO and issues a separate query to the driver for each batch.

@elarcent

Well, pardon my negligence regarding the use of "GO" in queries, I considered it as logical separator for queries. I'm very new to T-SQL, coming from MySQL, trying to catch up quickly. Federations are actually the main reason I went for Azure SQL.

Back to the problem: I tried to chain the queries, as you suggest, and the following code returns "events.js:2076: Uncaught Error: 42S02: [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'dbo.owners'." error for me:

var sql = require('node-sqlserver');
var conn_str = "Driver={SQL Server Native Client 10.0};Server=tcp:xxx.database.windows.net,1433;Database=xxx;Uid=xxx;Pwd=xxx;Encrypt=yes;Connection Timeout=30;";

    sql.query(conn_str, 'USE FEDERATION MyFederation(own_id=1) WITH RESET, FILTERING=OFF',
                function(err, results) {
                if(err) {
                    throw err;
                } else {
                    console.log(results);
                    sql.query(conn_str, 'SELECT * FROM dbo.owners WHERE ownerid=1',
                                function(err, results) {
                                    if(err) {
                                        throw err;
                                    } else {
                                        console.log(results);
                                    }

                                });
                        }

                });

At the same time if I use the following query in SQL Server Management Portal, I receive my results successfully:

USE FEDERATION MyFederation(own_id=1) WITH RESET, FILTERING=OFF
GO
SELECT * from dbo.owners WHERE ownerid=1
@mattneerincx

I ran the following test that worked =>

Create federated database using TSQL =>

create database shardtest
go
create federation shardfed(fid bigint range)
go
use federation shardfed(fid = 0) with reset, filtering=off
go
create table fedshardtable(id bigint, data varchar(255), constraint pkidfedshardtable primary key clustered(id asc)) federated on (fid=id)
go
insert fedshardtable (id, data) values (1,'fed #1'),(2,'fed #2')
go
use federation root with reset
go
alter federation shardfed split at (fid=2)
go

Then ran following code to verify =>

var sql = require('C:/NodeJs/src/node-sqlserver/lib/sql');
var connectionString = "Driver={SQL Server Native Client 11.0};Server=zzz.database.windows.net;database=shardtest;uid=zzz;pwd=zzz;";

// State loops thru 1,2,3,4 to trigger switching federations and selecting results.
var state = 1;

var conn = sql.open(connectionString, function (err, conn) {
    if (err) {
        console.log(err.toString());
        return;
    }
    runFederationLoop();
});


function runFederationLoop()
{
   var querySQL;

   switch (state)
   {
      case 1:
         querySQL = "use federation shardfed(fid = 1) with reset, filtering=off";
      break;
      case 2:
         querySQL = "select * from fedshardtable";
      break;
      case 3:
         querySQL = "use federation shardfed(fid = 2) with reset, filtering=off";
      break;
      case 4:
         querySQL = "select * from fedshardtable";
      break;
   }

   var query = conn.queryRaw(querySQL, function (err, results) {
      console.log("querySQL=" + querySQL);
      if (err) {
         console.log(err.toString());
      }
      else {
         if (results.rows.length > 0) {
             console.log(JSON.stringify(results));
         }
      }
    }); // sql.query(...)
    query.on('done', runNextFederationLoop);
}

// Run next iteration of loop in 1 second
function runNextFederationLoop()
{
    state = state + 1;
    if (state > 4) state = 1;
    setTimeout (runFederationLoop, 1000)
}

Results are =>

C:\NodeJs\src>"C:\Program Files (x86)\nodejs\node.exe" "C:\NodeJs\src\testfed.js"
querySQL=use federation shardfed(fid = 1) with reset, filtering=off
querySQL=select * from fedshardtable
{"meta":[{"name":"id","size":19,"nullable":false,"type":"number"},{"name":"data","size":255,"nullable":true,"type":"text"}],"rows":[
[1,"fed #1"]]}
querySQL=use federation shardfed(fid = 2) with reset, filtering=off
querySQL=select * from fedshardtable
{"meta":[{"name":"id","size":19,"nullable":false,"type":"number"},{"name":"data","size":255,"nullable":true,"type":"text"}],"rows":[
[2,"fed #2"]]}

@jguerin
Contributor
jguerin commented Jul 17, 2012

@elarcent: could you please try the above example in a new SQL Database, just to rule out any problems with the driver etc.?

Thanks!

Jonathan

@mattneerincx

Note I see the problem with your example.

You are using sql.query(conn_str. The problem with this is it will open a new connection each time, which will be reset to root database. So you need to follow my example and use sql.open to get a connection variable, then use the connection variable to hold connection state between USE FED and select.

@elarcent

Bingo! Now it's clear, thank you very much. I believe it should get a better coverage in docs and samples.

@jguerin
Contributor
jguerin commented Jul 17, 2012

Glad you worked it out!

@jguerin jguerin closed this Jul 17, 2012
@jguerin jguerin was unassigned by elarcent Apr 1, 2014
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment