Results 1 to 7 of 7
  1. #1
    mitch_pearce79 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2012
    Location
    Perth
    Posts
    15

    Updating records over an API using PUT

    Hi Guys and Gals,

    I have developed some VBA code using the MSXML2.ServerXMLHTTP object to request data over a API. Using "GET" the code returns a JSON string which I then break down and save in a table.

    Now I want to be able to use "PUT" to update a field in a particular record over the API. I have provided some concatenated code below...

    Using GET:

    'lsRequest = "http://localhost:8080/xxxxxxxxx/xxxxxxxxxxx/Contact/Customer/?api-version=v2"



    With objXMLRequest
    .Open "GET", lsRequest, False
    ' need to do basic authentication
    ' acknowledgement to http://pastie.org/1192157
    .setRequestHeader "Content-Type", "application/json"
    .setRequestHeader "Accept", "application/json"
    .setRequestHeader "Authorization", "Basic " + Base64Encode(lsCFUsername + ":" + lsCFPassword)
    .Send
    byteData = .responsebody
    End with

    Now I need to use "PUT" to update a record...

    Using PUT:

    'lsRequest = "http://localhost:8080/xxxxxxxxx/xxxxxxxxxxx/Contact/Customer/?api-version=v2"
    'lsData = "{""....""}" ?

    With objXMLRequest
    .Open "PUT", lsRequest, False
    ' need to do basic authentication
    ' acknowledgement to http://pastie.org/1192157
    .setRequestHeader "Content-Type", "application/json"
    .setRequestHeader "Accept", "application/json"
    .setRequestHeader "Authorization", "Basic " + Base64Encode(lsCFUsername + ":" + lsCFPassword)
    .Send (lsData)
    byteData = .responsebody
    End with


    So, my questions are:

    1. Do I need to specify the particular record I want to update in the lsRequest string (i.e. "http://localhost:8080/xxxxxxxxx/xxxxxxxxxxx/Contact/Customer/?&Filter="UID%20eq%20xxxxxxxx&api-version=v2") and then lsData = "{""IsActive"":"True"}" to update the IsActive field?... or

    2. Should lsRequest remain "http://localhost:8080/xxxxxxxxx/xxxxxxxxxxx/Contact/Customer/?api-version=v2" and I put the ID and the updated field in the lsData string (i.e. "{""UID"":""xxxxxxx"",""IsActive"":"True"}"?

    Thanks in advance!

    Mitch

  2. #2
    mitch_pearce79 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2012
    Location
    Perth
    Posts
    15
    Ok, so I found a link with some non-vba examples (https://parse.com/docs/rest/guide#ob...dating-objects)

    curl -X PUT \
    -H "X-Parse-Application-Id: ${APPLICATION_ID}" \
    -H "X-Parse-REST-API-Key: ${REST_API_KEY}" \
    -H "Content-Type: application/json" \
    -d '{"score":73453}' \
    https://api.parse.com/1/classes/GameScore/Ed1nuqPvcm

    So it looks to me as though i need to specify the unique id of the record in the URL (lsRequest) , and only the field and value in lsData

    'lsRequest = "http://localhost:8080/xxxxxxxxx/xxxxxxxxxxx/Contact/Customer/{unique ID of record}/?api-version=v2"
    'lsData = "{""IsActive"":"True"}"

    Can anyone confirm before I test this out and stuff up the data?

    Regards,

    Mitch

  3. #3
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Can't you use a backup of the data instead of the actual data?

  4. #4
    redbull's Avatar
    redbull is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Mar 2012
    Location
    Missouri
    Posts
    480
    I do a lot of IE automation, but I avoided the GETs and SENDs.

    From what I've been told is you have to be able to pass parameters to the server, and one way (don't know if its the only way) is to use the URL address. As long as its what the server is expecting, you should get something in return.

    'lsRequest = http://localhost:8080/xxxxxxxxx/xxxx...api-version=v2

    would be correct. If you want to be sure of this, just use the sites front end. It will be much slower because of page load times, but at least you can visually see what is happening. The reason I avoided this is because I just have nightmares of admins screaming my ear off for trying to push 20000 records lol.

    One thing I've always wondered, is this method really an API? or is it more of a backdoor (inject) to the webserver?

  5. #5
    mitch_pearce79 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2012
    Location
    Perth
    Posts
    15

    Success!!! Nearly

    So, I backed up the Company File and went ahead with the tests...

    Everything went right with 'lsRequest = "http://localhost:8080/xxxxxxxxx/xxxxxxxxxxx/Contact/Customer/{unique ID of record}/?api-version=v2"

    ...but I got a 'serialization' error trying to send ("{""IsActive"":True}")

    It appears the XMLHTTP object didn't expect the string 'True' and would prefer a number(?)... I did a bit of digging and am going to try 1 = True and 0 = false. Will update post if it works.

    Fingers crossed.

  6. #6
    hapm is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    197
    Be sure to not mess things up here. The XMLHTTP only does the http protocol side. It puts your request together to be conform to http, but doesn't validate it against the service you are using, afaik it doesn't even validate if you have clean json submitted as the payload of your PUT request. This is done by the service you use (parse.com). If it comes to json, from wikipedia it should be true and false in lower case.

  7. #7
    mcpearce is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Apr 2014
    Location
    Perth
    Posts
    20
    Ok. So after much messing around and seeking confirmation from MYOB, in order to update a record you must first GET the record by specifying its unique ID, then edit the JSON text as required before using PUT to replace the entire record. MYOB API does not support PATCH or allow you to expose/update a field within the record. Thanks to all for your input! Regards,
    Mitch

Please reply to this thread with any new information or opinions.

Similar Threads

  1. calling records and updating
    By vicsaccess in forum Programming
    Replies: 3
    Last Post: 12-20-2015, 01:02 PM
  2. Updating Old Records Based on New Records
    By lzook88 in forum Programming
    Replies: 24
    Last Post: 09-18-2015, 09:17 AM
  3. Using a Priority and updating other records
    By Perceptus in forum Modules
    Replies: 6
    Last Post: 07-08-2014, 11:18 AM
  4. updating records to another table.
    By sankar519 in forum Access
    Replies: 1
    Last Post: 06-30-2014, 08:31 AM
  5. HELP for updating selected records
    By explorer19 in forum Programming
    Replies: 7
    Last Post: 06-01-2011, 01:29 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums