CODEWITHSUNDEEP
mysqlsqlweb-services
Jesse Figueroa
Jesse Figueroa

Reputation: 83

Connect to web-service/API in MySQL?

I'm creating a sql based procedure which can

  1. Accept a table
  2. load the values one at a time
  3. send the variables to a remote API
  4. Record the response of the API
  5. Write the response to a table for viewing later

I have successfully implemented 1,2, and 5. I am hoping there may be some way of choosing an address to contact and for SQL to listen too for a response. Please let me know if you have any suggestions!

Upvotes: 4

Views: 20510

Answers (2)

EkoostikMartin
EkoostikMartin

Reputation: 6911

The only way MySQL can call a webservice is through a custom UDF.

See the below link, but basically what you would need to do is build a C program that would call the remote webservice API with the variables as parameters, return the reponse via the UDF to your actual SQL which could insert the results to a table. This is quite a lot of work though, especially if you are not at all familiar with C.

http://www.codeguru.com/cpp/data/mfc_database/misc/article.php/c12615/MySQL-UDFs.htm

Upvotes: 2

O. Jones
O. Jones

Reputation: 108641

MySQL cannot do this using SQL code that executes on the server, without extending the MySQL server with user defined functions.

Some other SQL table servers (e.g. PostgreSQL, Microsoft SQL Server, Oracle) can do this with stored procedures. But, accessing network resources like APIs from table-server-resident code is a tricky business, because those resources can time out or fail in other ways.

This kind of thing is a total hairball to debug when you run it in the table server.

You'd be much better off writing a hunk of client code (by which I mean code that is a client of the MySQL server) to do this. There are plenty of languages that can support this very easily, including python, php, PERL, C#, Java, etc etc.

Upvotes: 4

Related Questions