HTTP Post to Azure SQL DB


#1

I recently found DreamFactory and thought it might work for me. I’m using an Arduino Mega with WiFi capability
and I want to be able to make a POST to insert records into an Azure database.

POST EXAMPLE using Azure Mobile Service with Application Key:

POST http://todolist.azure-mobile.net/tables/TodoItem
Accept: application/json
X-ZUMO-APPLICATION: UzMAOXRlJdZyqibeUqCMoZZMrUXIRs92
Content-Type: application/json
Host: todolist.azure-mobile.net
Content-Length: 49
{“text”:“Complete the tutorial”,“complete”:false}

I can’t use SSL (https:) because that’s not possible on the Arduino, so I want to store the credentials on a proxy such as DreamFactory and have that connect to the database, so that I can use HTTP for my POST and not worry about credentials being exposed.

I created a Service on DF called NimSQL with service type SQL DB.

In the Service Config I added the connection string:
dblib:host=zg1u6f5t0l.database.windows.net:1433;dbname=nimthai

I entered user name and password and also added DF’s IP address to the Azure firewall as IP: 54.164.112.165

When I go to the API Docs I can see the Service listed but no Operations.

I’m not sure what else to do, or how to get it working. Can you help me with this?

Regards
Ross.


#2

I managed to create a service that connects to an SQL server/db and I can now see a list of all the operations.

The problem I have is that when I click POST (Add a record) I can’t select the table name - I updated the permissions for the db user on the server, but hose changes are not reflected in the operations.

I tried deleting the service and recreating it (because I could not find any way to “refresh” the service or operations). But despite re-creating the service, it still does not show the table name(s). It shows just one table name.


#3

Okay, I fixed the problem by recreating a service with a different name. The operations list now shows the correct table(s).


#4

I don’t seem to be able to add a record to the database. I have tried both POST operations, but each fails. The second POST gives me an error saying the table does not exist, yet the table is selectable in the drop-down list and it exists in the database.

POST - createRecord() - Create one record with given identifier
Response Code: 400 “Create record by identifier not currently supported”

POST - createRecords() - Create one or more records
Response Code: 404 “Table ‘booking_import’ does not exist in the database.” But the table does exist.


#5

@b4ip I am looking into this issue. Can you tell me what OS your DF install is on, and which driver you’re using to connect to your database?


#6

Hi Drew

I haven’t installed anything, and I’m not using any drivers. I just signed in to DF, created an instance, then created a service (SQL DB) with connection string dblib:host=xxx.xx.x.xx:1433;dbname=Samadhi. On my SQL server I have port 1433 open to any IP address.

I then went to the API Docs page where I see a list of operations.

I expanded the POST section (createRecord()) which shows the “booking_import” table from my db.

I entered the body and then when I click the “Try It Out” button I get response code: 404 (create record by identifier not currently supported). Actually there is no “id” but it says it’s a required field. I tried adding an “id” field to the db table and entering a value, but got the same error.

So then I tried POST (createRecords()) but that also failed saying that the “booking_import” table does not exist. But the table is there and I can even see it listed in the table_name parameter drop-down.

I am working from an Arduino platform and what I want to do is to send an http post that will create a record in an SQL DB. I’m not too worried about security because the POST should only be accepted if it comes from a specific IP addresses.

THIS IS AN EXAMPLE OF MY POST FROM ARDUINO CLIENT

Here I was trying to post to Azure but that also fails because it has to be HTTPS which I can’t do on the Arduino.

client.println(“POST http://nimthai.azure-mobile.net/tables/booking_import”);
client.println(“X-ZUMO-APPLICATION: CnhUSgmEmUqMGrZJYpXhPoeAJrAeSy75”);
client.println(“Host: nimthai.azure-mobile.net”);
client.println(“Content-Type: application/json”);
client.print("Content-Length: ");
client.println(myData.length());
client.println();
client.println(myData);

The myData string looks like this: {“staffid”:21,“cost”:100,“duration”:60}


#7

Hi ,

I am having similar issue - where you able to sort this out ?
IF so , can you let me know what was done to handle this error .


#8

Hi abhijitmehta

No one got back to me on this from DreamFactory so I could not get it working - finally gave up. I ended up ordering an Arduino Yun (“cloud” in Chinese apparently), so then I should be able to use htpps with Azure.

What would be ideal is a Web Service that can accept a complete SQL query and execute it securely against the server. I was not worried about security to the Web Service because I only wanted it to allow connections from my WAN IP address. I am now looking into trying to build my own Web Service to do this, as I could not find anything on the Internet that was simple and worked well.

Best wishes and good luck.
b4ip


#9

create record by id is not supported. you should be using simply create record.


#10

On the webpage there is only CreateRecord() and CreateRecords() and as explained in my post I tried both.
When using CreateRecord() it says the ID field is required, hence there was no way to simply create a record based on the field/value pairs I put in the body.

I waited nearly 3 weeks to get a response from DF and now my trial period has already expired, so no more thank you.


#11

@b4ip I’m sorry for the delay in getting this resolved and the misunderstanding over the issue.
If you wish to continue troubleshooting directly with support you may – i have sent you a PM about this.
Or if you would like to continue speaking here that is fine too.
OR if you really don’t wish to continue using DreamFactory we are disappointed, but we understand.

If it helps you or anyone else reading this post, there are two potential issues I see as possible causes of your problem.

  1. There was a bug in some of the earlier releases that set the X-HTTP-METHOD to GET on all of the CreateRecords Swagger definitions. This has been resolved. If you look at your CreateRecords call and see X-HTTP-METHOD set to GET and you cannot change it, you need to update your instance to the latest code.

  2. There was a bug in some of the earlier releases that would not let you access tables that weren’t all lowercase in name. This has also been resolved. If you are able to list tables, but get an error stating the table does not exist when trying to use it, and the table has uppercase letters in it, you need to update your instance to the latest code.