4

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!

Jesse Figueroa
  • 83
  • 1
  • 2
  • 6
  • Not really (although you could use stored procedures and proprietary extensions, but it's likely to not be what you want). I think you should use a scripting language for this, which does the db operations for you - PHP, Ruby, Python, Java etc. – halfer Jun 20 '12 at 20:51
  • What do you mean by "accept a table" and "load the values"? – halfer Jun 20 '12 at 20:52
  • (Ah - you _are_ using a stored procedure. Hmm, maybe more detail required - I still wouldn't do the whole thing in SQL, even if it turns out to be possible). – halfer Jun 20 '12 at 21:02
  • Example would be if I were examining names. I get the table I wanted to examine in the query which runs the procedure.Then I go down the name column of the table and load names (via loop) into a variable, which would then be sent to a remote API for evaluation, and then get the response to be recorded. Then repeat the loop to do it for as many names as there are. I've done everything successfully except for the contacting the API and recording the result. So I've "loaded" the variables and written them to a new table with no issues. I'm hoping for a sql way to solve the api contact problem. – Jesse Figueroa Jun 20 '12 at 21:05
  • 1
    Right, I understand what you're doing now. But why are you "hoping for a SQL way" - is there a particular reason you want to avoid a programming language? – halfer Jun 20 '12 at 21:22

2 Answers2

4

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.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
2

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

EkoostikMartin
  • 6,831
  • 2
  • 33
  • 62
  • 1
    Thank you so much! I think this is what I was looking for (I'm somewhat proficient in C, and find PHP daunting). If you have any other websites that have helped you with UDF development, or any tips, please let me know! – Jesse Figueroa Jun 20 '12 at 21:48
  • 1
    old but is somewhat helpful if you use visual studio for development - http://rpbouman.blogspot.com/2007/09/creating-mysql-udfs-with-microsoft.html – EkoostikMartin Jun 20 '12 at 22:01