How to Post Data to SQL SERVER 2008R2

Agent Code
1

Hi,

Has any body post data to a SQL SERVER? Is it possible to logon with specific user and password? Any sample code to get me started much appreciated.

I all ready have the SQL SERVER DB hosted on a cloud.

2

Currently the only way to send data from the agent is via HTTP. So you’ll need an HTTP (REST) endpoint in front of your SQL Server instance.

Also, it’s probably a really bad idea to expose a SQL Server instance directly to the public internet.

3

Hi rogerlipscombe,

Thanks for the response and the heads up.

I got it working, writing to SQL SERVER, using Dream Factory.

`dfAPIKey <-"";
session_token <-"";
INSTANCE_URL <-""

function dfLogon(){

session_token = "";

INSTANCE_URL = "https://<<YOUR URL>>.enterprise.dreamfactory.com";
local email         = ; // your email address
local password      = ;// password in Dream Factory

local headers =  {"Content-Type": "application/json' --header 'Accept: application/json' --header 'X-DreamFactory-Api-Key: dfAPIKey ", X-DreamFactory-Session-Token": <<SESSION_TOKEN>>; // Replace the token with yours
local url = INSTANCE_URL + "/api/v2/user/session";
local string = { "email": email, "password": password } ;
local request = http.post(url, headers, http.jsonencode(string));
local response = request.sendsync();  


if (response.statuscode==200){
    local getToken = response.body;
    local TokenStart = getToken.find(":")+1;
    local TokenEnd = getToken.find(",")-1;
    session_token = getToken.slice(TokenStart, TokenEnd);
    server.log("Dream Factory LOGON Succesfull!")
    //server.log("display session_token: "+ session_token);
    return session_token;

    //local aftertoken = getToken.slice(TokenEnd);
    //server.log(aftertoken);
}
else{
        server.log("Dream Factory Error: "+response.body)
        session_token = null;
        return session_token;
};
}

function dfPost(){
local headers = {“Content-Type”: “application/json; charset=utf-8”, “X-DreamFactory-Api-Key”: dfAPIKey, “X-DreamFactory-Session-Token”: session_token }; // Replace the token with yours
local url =INSTANCE_URL+ “/api/v2/atemp/_table/SeonsorReadings”;
local pin1Value=5;
local ltime = time();
server.log(ltime);
local pin2Value=“05/19/2016”;
local data = { “id”: pin1Value, “fec”: pin2Value, “lat”: 18.5634, “lon”: -69.4578, “time”: ltime }
local string ={“resource”: [data] } ;

//server.log("display session_token before post "+ session_token);

local request = http.post(url, headers, http.jsonencode(string));
local response = request.sendsync();  
server.log("Code: " + response.statuscode + ". Message: " + response.body);

};

dfLogon();
dfPost();`
4

@jcmv007 I am trying to understand your Dream Factory solution. In order for Dream Factory to work with your SQL database did you have to install anything from Dream Factory onto your cloud instance.

5

Hi Gerrikoio,

It works in 2 possible ways, one is going in the Dream Factory web site and configuring a Services which is a connection to an sql server, this one can be used for testing purposes. The other is to down load and install Dream Factory and configure a PHP file in the server where sql server is running.

James