Jump to content









Photo

How to post SQL Server Data to Carriots?

sql

  • Please log in to reply
6 replies to this topic

#1 erpnedir

erpnedir

    Newbie

  • Members
  • Pip
  • 3 posts

Posted 31 July 2013 - 03:24 PM

Hi,

I started working on an ITS (Intelligent Transport Solution) project. I would like to test drive carriots on this.

The legacy system has a log database, which stores all the data coming from devices to a table. I tried to find ways to post each inserted record data to carriots but I couldn't come to a solution.

Simply, a trigger which can post the data in JSON format with curl (or whatever) would do the work. But as far as I see, I can not use curl that way.

What is the best way to push the data to carriots?

Regards



#2 amendoza

amendoza

    Master of Puppets

  • Members
  • PipPip
  • 10 posts

Posted 01 August 2013 - 10:58 AM

I think the best you can do is to create a trigger that calls to cURL.
 
It should be something like this:
 
 
CREATE TRIGGER carriotslog
ON dbo.test_table
AFTER INSERT
AS
BEGIN
DECLARE @CMD VARCHAR(255)
DECLARE @field1 VARCHAR(25)
DECLARE @field2 VARCHAR(25)
DECLARE @field3 INT
DECLARE @data VARCHAR(255)


SELECT @field1 = field1,
@field2 = field2,
@field3 = field3
FROM inserted

SET @data = '{"protocol": "v1", "at":"now", "device":"defaultDevice@CUSTOMER", "checksum":"","data":{ "key1":"'+@field1+'", "key2":"'+@field2+'", "key3":'+CONVERT(VARCHAR(20),@field3)+'} }'

-- Create a temp file
SET @CMD = 'echo > c:\tempfile.txt '+@data
EXEC xp_cmdshell @CMD
	
-- Create curl command
SET @CMD = 'curl --request POST --data-binary @c:\tempfile.txt --header "carriots.apiKey:YOURAPIKEY" --verbose http://api.carriots.com/streams ' 
print @CMD
EXEC xp_cmdshell @CMD
END


Remember to put your device id_developer at "SET @data" command
Also to put your APIKEY at the second "SET @CMD" command

#3 erpnedir

erpnedir

    Newbie

  • Members
  • Pip
  • 3 posts

Posted 06 August 2013 - 10:09 PM

Hi amendoza,

Thank you very much for your help. 

Two things to note:

  1. xp_cmdshell has to be enabled
  2. command curl has to be written by its path like SET @CMD = 'c:\program files\curl\curl --request......'

The trigger you proposed worked very well for my testing. But when I bind it to a real log table that inserting device data every second, the trigger locked the table.

 

So at second step I will try creating an asyncronious model based on queue tables or sql service broker.



#4 mcastillo

mcastillo

    Carriots CEO

  • Administrators
  • 34 posts
  • LocationMadrid

Posted 08 August 2013 - 08:53 AM

Hi erpnedir,

 

I am glad to know you are testing Carriots as part of your ITS systems. I hope you managed to integrate your legacy SQL server without problems and that is working nicely.

 

It would be great if you make a little post on this forum describing your ITS project and how you plan to use Carriots. Once we know a little bit more about it, perhaps we can give you some hints on how to use some of the powerful features of Carriots. Any feedback you give us will be also very appreciated  :) .

 

Regards,

Miguel

CEO of Carriots



#5 erpnedir

erpnedir

    Newbie

  • Members
  • Pip
  • 3 posts

Posted 16 August 2013 - 02:47 PM

Hi Miguel,

Thank you very much for your interest. Sory for the late reply, this was a public holiday time in Turkey. Your help and advices are highly appreciated.

 

We are trying to set up an ITS (Intelligent Transport Solution). Basically there are traffic devices set up on the field like computers that control the traffic lights at junctions, Traffic Cameras, VMS (variable message signs), meteo sensors and that sort of things. There is a legacy system that collects data from devices every second and logs to a RDBMS for audit purposes. Each device is controlled by its own software supplied by the manufacturer.

Our goals are:

- Create a real time traffic monitoring app that receives signals from devices and visualize traffic on a map

- Show the status of devices on the map.

- Manage the devices remotely (for most used functions)

- Create a Urban Traffic Management Enterprise Software! (ultimate goal)

 

From the first playing around carriots, I understand that carriots is efficient for device monitoring and some specific management. Let's say that "if the listener detects temprature change of 1 degrees for the last 3 data stream coming from meteo sensor nr1,  send show new temprature message to VMS nr 5"

I am not sure if carriots can handle high volume data input like: data stream from 200 devices every second (or every 100 miliseconds)

 

Anyway, you are the expert in ITS (internet of things this time) I would be glad to hear your thoughts on this project.

 

Regards,

Fatih.



#6 alvaro

alvaro

    Carriots CTO

  • Administrators
  • 72 posts

Posted 17 August 2013 - 11:35 AM

Hi Fatih.

 

The system you want to build is a common use case at this time in IOT projects. As the technology is not really new there are lots of independent projects already in use that must to be connected if you want them to act as a "smart" system. Carriots will bring you the tools for that solution.

 

Starting with your GIS (Geographic Information System), you can roll your own system based on available map services combined with your data. Those services can be Google Maps, ESRI's ArcGIS, etc. Your data can be extracted querying Carriots DB using our REST API (Pull method) or by storing it in another external DB where Carriots pushes the information (triggers or listeners).

You can build a complete external frontend (dashboard and control panel) in an independent server using Carriots as your backend. 

 

Publish data with Carriots: https://www.carriots...ls/publish_data

 

Bidirectional communication can be done using Carriots and let you manage your device. There are different approaches depending on which side starts the communication (the device or Carriots).You can find an example here: https://www.carriots...ts/electrovalve

And there is device configuration management that can also be done: https://www.carriots...d_stream/status

 

The logic you mention is an example of what can be done using Carriots' business logic, but there is a lot of potential here. From custom complex scripts to external services queries, access historic data, access data from other devices, etc. You can not only work with a data stream information when you execute a listener code, you can access the whole DB (https://www.carriots.../core_libraries)

 

Finally, for high volume data input there is no problem, Carriots is designed for that.

 

Enjoy Carriots!


Alvaro Everlet

Carriots CTO


#7 bkonnet

bkonnet

    Newbie

  • Members
  • Pip
  • 3 posts

Posted 02 October 2017 - 10:55 PM

I think the best you can do is to create a trigger that calls to cURL.
 
It should be something like this:
 
 

CREATE TRIGGER carriotslog
ON dbo.test_table
AFTER INSERT
AS
BEGIN
DECLARE @CMD VARCHAR(255)
DECLARE @field1 VARCHAR(25)
DECLARE @field2 VARCHAR(25)
DECLARE @field3 INT
DECLARE @data VARCHAR(255)


SELECT @field1 = field1,
@field2 = field2,
@field3 = field3
FROM inserted

SET @data = '{"protocol": "v1", "at":"now", "device":"defaultDevice@CUSTOMER", "checksum":"","data":{ "key1":"'+@field1+'", "key2":"'+@field2+'", "key3":'+CONVERT(VARCHAR(20),@field3)+'} }'

-- Create a temp file
SET @CMD = 'echo > c:\tempfile.txt '+@data
EXEC xp_cmdshell @CMD
	
-- Create curl command
SET @CMD = 'curl --request POST --data-binary @c:\tempfile.txt --header "carriots.apiKey:YOURAPIKEY" --verbose http://api.carriots.com/streams ' 
print @CMD
EXEC xp_cmdshell @CMD
END


Remember to put your device id_developer at "SET @data" command
Also to put your APIKEY at the second "SET @CMD" command

Hi amendoza

 

 

I have the same question as erpnedir 5 years ago. I need to get the data from an external SQL database and use it as a device. Can it be done in a more simple way this time?

 

Thank you.






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users