integrating ms office and wordpress with vba and xml-rpc

juust | 20/10/2009

Okay.

Yesterday I made some basic stuff to grab my Wordpress blog data with visual basic for applicationsn cos I don’t feel like programming php admin pages for tables.

So let’s make a basic xml-rpc crud plugin and put my agenda on vba remote control.

The basic xml-rpc plugin is simple, plug extra methods into the xml-rpc method array, and write a function per crud-method.

  1.  
  2. add_filter( 'xmlrpc_methods', 'add_agenda_xmlrpc_methods' );
  3.  
  4. function add_agenda_xmlrpc_methods( $methods ) {
  5.     $methods['agenda.addAgendaItem'] = 'addAgendaItem';
  6.     $methods['agenda.updateAgendaItem'] = 'updateAgendaItem';
  7.     $methods['agenda.deleteAgendaItem'] = 'deleteAgendaItem';
  8.     $methods['agenda.reportAgendaItem'] = 'reportAgendaItem';
  9.     return $methods;
  10. }
  11.  
  12. function addAgendaItem($args) { }
  13.  
  14. function updateAgendaItem($args) { }
  15.  
  16. function deleteAgendaItem($args) { }
  17.  
  18. function reportAgendaItem($args) { }
  19.  
  20. //basic login helper function
  21. function CheckLogin($user, $pwd) {}

There now, if I call on the xmlrpc.php file, the extra methods are added to the callback array and I can use the table CRUD functions from my vba desktop.

  1. function addAgendaItem($args) {
  2.  
  3.         $blog_id = (int) $args[0];
  4.         $username = $args[1];
  5.         $password = $args[2];
  6.         $AgendaItem     = $args[3];
  7.  
  8. //remember : add a login check
  9. //(for the example it is irrelevant)
  10.  
  11.         global $wpdb;
  12.         $sql = "INSERT INTO ".$wpdb->prefix."Agenda (
  13.                `userid`, `tags`, `description`, `firstdate`, `enddate`, `link`, `price`, `location`
  14.                ) VALUES (
  15.                '".$AgendaItem[0]['userid']."',
  16.                '".$AgendaItem[0]['tags']."',
  17.                '".$AgendaItem[0]['description']."',
  18.                '".$AgendaItem[0]['firstdate']."',
  19.                '".$AgendaItem[0]['enddate']."',
  20.                '".$AgendaItem[0]['link']."',
  21.                '".$AgendaItem[0]['price']."',
  22.                '".$AgendaItem[0]['location']."'                
  23.                )";
  24.         $wpdb->query($wpdb->prepare($sql));
  25.  
  26.         return $wpdb->insert_id;
  27. }

note : the agendaitem is a struct in an array (see below), I use [0] to get the first struct (which is the actual array with field-value pairs, my record with agenda info).

Activate the plugin, and write a simple test

  1.  
  2. 'Type to hold an agenda info record
  3. Type AgendaItem
  4.     userid As String
  5.     tags As String
  6.     Description As String
  7.     firstdate As String
  8.     enddate As String
  9.     link As String
  10.     price As String
  11.     location As String
  12. End Type
  13.  
  14.  
  15. Function AddAgendaItem()
  16.  
  17. txtURL = "http://wwwblog.com/xmlrpc.php"
  18. txtUserName = "MyUsername"
  19. txtPassword = "MyPassword"
  20.  
  21.   Dim objSvrHTTP As ServerXMLHTTP
  22.   Dim strT As String
  23.   Set objSvrHTTP = New ServerXMLHTTP
  24.  
  25.   objSvrHTTP.Open "POST", txtURL, False, CStr(txtUserName), _
  26.    CStr(txtPassword)
  27.  
  28.   objSvrHTTP.setRequestHeader "Accept", "application/xml"
  29.   objSvrHTTP.setRequestHeader "Content-Type", "application/xml"
  30.  
  31.     strT = ""
  32.     strT = strT & "<methodcall>"
  33.     strT = strT & "<methodname>agenda.addAgendaItem</methodname>"
  34.    
  35.     strT = strT & "<params>"
  36.     strT = strT & "<param><value><string>" & txtBlogId & "</string></value></param>"
  37.     strT = strT & "<param><value><string>" & txtUserName & "</string></value></param>"
  38.     strT = strT & "<param><value><string>" & txtPassword & "</string></value></param>"
  39.  
  40. 'now we go make the struct, I use a Type (stdobject), normally
  41. 'you'd use a recordset
  42.  
  43. Dim a As AgendaItem
  44.  
  45. With a
  46.     .userid = 1
  47.     .Description = "rpc testing"
  48.     .firstdate = "2009/10/14"
  49.     .enddate = "2009/10/14"
  50.     .link = "http://www.juust.org/"
  51.     .price = "rpc testing"
  52.     .location = "limmen"
  53.     .tags = "php, xml-rpc"
  54. End With
  55.  
  56.     strT = strT & "<param><value><array>"
  57.     strT = strT & "<data>"
  58.    
  59.     strT = strT & "<value><struct>"
  60.  
  61.     strT = strT & "<member><name>userid</name><value><string>" & a.userid & "</string></value></member>"
  62.     strT = strT & "<member><name>tags</name><value><string>" & a.tags & "</string></value></member>"
  63.     strT = strT & "<member><name>description</name><value><string>" & a.Description & "</string></value></member>"
  64.     strT = strT & "<member><name>firstdate</name><value><string>" & a.firstdate & "</string></value></member>"
  65.     strT = strT & "<member><name>enddate</name><value><string>" & a.enddate & "</string></value></member>"
  66.     strT = strT & "<member><name>link</name><value><string>" & a.link & "</string></value></member>"
  67.     strT = strT & "<member><name>price</name><value><string>" & a.price & "</string></value></member>"
  68.     strT = strT & "<member><name>location</name><value><string>" & a.location & "</string></value></member>"
  69.  
  70. 'close the struct    
  71.     strT = strT & "</struct></value>"
  72.     strT = strT & "</data>"
  73.  
  74. 'close the struct array
  75.     strT = strT & "</array></value></param>"
  76.  
  77. 'end parameters
  78.     strT = strT & "</params>"
  79.  
  80. 'end method
  81.     strT = strT & "</methodcall>"
  82.  
  83. 'send the lot to the blog  
  84.   objSvrHTTP.send strT
  85.  
  86. 'print the response to debug
  87.   Debug.Print  objSvrHTTP.responseText
  88.  
  89. End function

Et voila :

agenda rpc

That’s yer basic Office-Wordpress XML-RPC integration.

Comments
No Comments »
Categories
juust, wordpress, xml-rpc
Tags
vba, xml-rpc
Comments rss Comments rss
Trackback Trackback

more vba and wordpress xml rpc

juust | 19/10/2009

Basic Example II, vba wordpress remote control, demo.ayHello is fun but quite useless, so let’s proceed to wp.getUsersBlogs.

I make a table in access (id, isAdmin, blogid, url, blogName, xmlrpc) and then query wordpress, parse the xml response in store it in the database.

So here we go query Wordpress :

  1.     methodname = "wp.getUsersBlogs"
  2.  
  3. 'xmlrpc url
  4.     txtURL = "http://www.blog.com/xmlrpc.php"
  5.  
  6. 'authorization
  7.     txtUserName = "user"
  8.     txtPassword = "pass"
  9.  
  10. 'XML http request  
  11.     Dim objSvrHTTP As ServerXMLHTTP
  12.     Dim strT As String
  13.     Set objSvrHTTP = New ServerXMLHTTP
  14.    
  15.     objSvrHTTP.Open "POST", txtURL, False, CStr(txtUserName), _
  16.      CStr(txtPassword)
  17.    
  18.     objSvrHTTP.setRequestHeader "Accept", "application/xml"
  19.     objSvrHTTP.setRequestHeader "Content-Type", "application/xml"
  20.  
  21. 'here is the bit with the wp.getUserBlogs methodname,
  22. 'and the username and password    
  23.     strT = ""
  24.     strT = strT & "<methodcall>"
  25.     strT = strT & "<methodname>" & methodname & "</methodname>"
  26.     strT = strT & "<params>"
  27.     strT = strT & "<param><value><string>" & txtUserName & "</string></value></param>"
  28.     strT = strT & "<param><value><string>" & txtPassword & "</string></value></param>"
  29.     strT = strT & "</params>"
  30.     strT = strT & "</methodcall>"
  31.    
  32.     objSvrHTTP.send strT

that’s pretty straight forward.

I get an xml message back with the data stored in an array of STRUCT types.

  1. 'Create the DomDocument Object
  2. Dim oDoc As MSXML2.DOMDocument
  3. Set oDoc = CreateObject("MSXML2.DOMDocument")
  4. oDoc.async = False
  5. oDoc.validateOnParse = False
  6.  
  7. 'Load the response in the DomDocument Object
  8. Dim fSuccess As Boolean
  9. fSuccess = oDoc.loadXML(objSvrHTTP.responseText)
  10. If Not fSuccess Then
  11.     MsgBox "failed"
  12.     Exit Function
  13. End If
  14.        
  15. 'counters
  16. ct = 1
  17. cs = 1
  18.  
  19. 'array to story the field values
  20. Dim SArray(1 To 5) As String
  21.  
  22. 'open the table as recordset
  23. Dim d As DAO.Recordset
  24. Set d = CurrentDb.OpenRecordset("blogs", dbOpenTable)
  25.  
  26. 'lets get the structs
  27. Set oChildren = oDoc.selectNodes("//struct")
  28. For Each oStruct In oChildren
  29.  
  30. 'the struct has members,
  31.  
  32.    Set Members = oStruct.childNodes
  33.    For Each Member In Members
  34.  
  35. 'each member has a param pair (field, value)
  36. 'skip the first param, and store the field value
  37. 'in an array
  38.  
  39.        Set Params = Member.childNodes
  40.        For Each Param In Params
  41.             ct = ct + 1
  42.             If ct / 2 <> Int(ct / 2) Then
  43.                 SArray(ct / 2) = Param.Text
  44.                 cs = cs + 1
  45.             End If
  46.        Next
  47.  
  48.     Next
  49.  
  50.     'we got the struct data,
  51.     'now add the array to the database
  52.    
  53.      With d
  54.         .AddNew
  55.         !isAdmin = SArray(1)
  56.         !url = SArray(2)
  57.         !blogid = SArray(3)
  58.         !blogName = SArray(4)
  59.         !xmlrpc = SArray(5)
  60.         .Update
  61.     End With
  62.  
  63. Next
  64.  
  65. 'close up shop
  66. d.Close
  67. Set d = Nothing
  68.  
  69. End Function

The other worpdress api calls work quite the same.

Comments
No Comments »
Categories
wordpress, xml-rpc
Comments rss Comments rss
Trackback Trackback

wordpress xmlrpc and microsoft access vba

juust |

I was building a database in Wordpress but their table interface lacks completely and I dont want to spend 200 hours developing a backend for a once-off project.

I always used MsAccess for RAD blueprinting and I reckon you can build a better more flexible interface in 20 hours. With XHR MsAccess has some better internet capabilities these days, a nice simple XMLHttp library.

You can use it for any http rest api in the cloud.

Ajax for Access :) here’s the most basic wordpress xml-rpc call, sayHello.

  1. Sub PutXML()
  2.  
  3. txtURL = "http://www.blog.com/xmlrpc.php"
  4. txtUserName = "user"
  5. txtPassword = "pwd"
  6.  
  7.   Dim objSvrHTTP As ServerXMLHTTP
  8.   Dim strT As String
  9.   Set objSvrHTTP = New ServerXMLHTTP
  10.  
  11.   objSvrHTTP.Open "POST", txtURL, False, CStr(txtUserName), _
  12.    CStr(txtPassword)
  13.  
  14.   objSvrHTTP.setRequestHeader "Accept", "application/xml"
  15.   objSvrHTTP.setRequestHeader "Content-Type", "application/xml"
  16.  
  17.   strT = ""
  18.   strT = strT & "<methodcall>"
  19.   strT = strT & "<methodname>demo.sayHello</methodname>"
  20.   strT = strT & "</methodcall>"
  21.  
  22.   objSvrHTTP.send strT
  23.  
  24.   MsgBox objSvrHTTP.responseText
  25.  
  26. End Sub

For pre-Vista you need the MSXML 6.0 library from microsoft, in Vista I already had it installed so you can add a reference to the library and off you go.

Comments
5 Comments »
Categories
juust, xml-rpc
Tags
xhr
Comments rss Comments rss
Trackback Trackback

« Previous Entries Next Entries »