A while back (yesterday), I penned a blog post highlighting the ORDS REST-Enabled SQL Service. And in that blog, I displayed the output of a cURL command. A cURL command I issued to an ORDS REST-Enabled SQL Service endpoint. Unfortunately, it was very messy and very unreadable. I mentioned that I would fix it later. Well…it’s now…later (temporal paradox, anybody š¤Ø?).
Recap
If you recall, the output of my POST request looked like this:
Yikes, you kiss your mother with that mouth?!
JSON is not displaying correctly
Well, the reason why I didn’t originally pipe in the json_pp command is because this is what happened when I attempted it:
Jefe to the rescue
After reading my newly published article, Jefe suggested I try the jq command.
The Yoda to my Padawan
Which, of course, I did. Still no luck:
Different issue though
Andiamo a googliare!
Online search to the rescue
Search online using the keywords “parse error: Invalid numeric literal at,” and you’ll quickly discover that you’re not the only one with this problem.
Five minutes of research revealed a potential culprit. What I was experiencing seemed to be a known issue. For example, a long-standing jqbug on GitHub details this exact scenario. This doesn’t seem to be a jq or json_pp issue. Instead, the problem is somehow related to the -i cURL command option and JSON parsing.
After another few minutes, as luck would have it, I found a Stack Overflow thread discussing the same issue I encountered! After scrolling to the bottom of the thread, I found this golden nugget:
Thank you Mattias and nhs503 š„°
Testing without -i
So, I did just what Mattias and nhs503 suggested. I removed the -i option (-i, or –include) from my cURL command, and wouldn’t you know? The damn thing works as expected! I tested while piping jq and json_pp. I also concede that jq is the prettier of the two; I appreciate the colors (although, admittedly, this would NOT pass any accessibility testing).
jq part one of the responsejq part two of the responsejson_pp part one of the responsejson_pp part two of the response
Final thoughts
And for some final thoughts…
It turns out it’s NOT ORDS – it’s something to do with an underlying JSON parser not liking the header info that is coming through
json_pp and jq both work; they output the information in different order
The ORDS REST-Enabled SQL Service returns to you not only your results, but the SQL statement initially used (that is cool and I didn’t originally realize or mention this)
And that’s it for this one! I really hope you find this useful. I hope this saves you some time from having to troubleshoot and/or hunt for a fix for this tricky problem. That’s all for now!
Follow
And don’t forget to follow, like, subscribe, share, taunt, troll, or stalk me!
It was bugging me that I couldnāt perform a simple Python POST request to an ORDS REST-enabled table.
This one actually…
I donāt mean to convey that this isnāt possible. Up until very recently, I wasnāt able to do this. Luckily I had a few hours free, so I took to the docs to do some reading. And wouldnāt you know it, like most things in tech, reading the documentation was a practical and valuable use of my time.
Side note
Let me pause here for a minute or two. I should add this disclaimer that none of what I share here uses OAuth 2.0 authentication. Of course, I wish it did, but Iām just not there yet (technical proficiency). In the future, Iād like to update this with security in mind1:
The other thing Iāll mention is that Iām going to include the SQL for creating this table along with all the Python code in my GitHub repo (Iāll also add any code updates to my repo! will also add any updates I make to this code to the repo!).
Also, did you know that saving DDL with Database Actions is just a mouse click away?
Right-click an object in Database Actions > save DDL to a SQL Worksheet or File.DDL for creating this example table.
New User speed run
This section is a bit of an aside, but I also created a new Python Developer user in one of my Autonomous Databases. Itās straightforward to do as the admin. Here is a āspeed runā:
POST haste
After creating my new user, I created a āPython_Postā table. Super imaginative, right? And I kept things simple, naming the four columns (wait for it): "col1", "col2", "col3", and "col4".
Damn…he actually did that.
Cheat codes
I auto-REST enabled my table and reviewed a Bash cURL command so I could remind myself of the expected data (aka payload).
A Bash cURL command for POST requests; can be used as later reference in your editor.
Iāve noticed that if I donāt specify a ārowidā the Autonomous Database automatically does this. SQL-newbies (like me) might appreciate this since we still donāt know all the best practices for database design (or DDL, for that matter)!
My process might differ from yours, but Iāve used the cURL commands in Database Actions as a cheat. Iāve been copying/pasting this cURL command into my working Python file, so I donāt have to switch screens. Additionally, it helps to remind me what the {ākeyā:āvalueā} pairs are (even though I just created all this, I STILL canāt remember what I did).
In this case, Iām referencing a POST request, but you could do this for the other HTTPS methods too:
GET ALL
GET
POST
BATCH LOAD
PUT
DELETE
Moving on…
I could omit the ārowidā when making these POST requests. I donāt know if this is typical when working with databases, but this seems to work consistently (at least with the testing I did) with Python and the Requests library.
If you werenāt taken aback by my imaginative table name and column names, then get ready because Iām about to blow your mind with this next bit. I created a payload for each of these POST request variations:
And I’m just going to be as literal as possible to avoid confusion…is it lame? Yes. Does it work? Undecided, you tell me.
In case you didn’t catch it, they were: payload1, payload2, and payload3.
On feedback…
I also included some feedback for myself. And I feel like this is a good practice because if you successfully POST something, how would you know? Conversely, the same would be true if you unsuccessfully POSTed something. If I were smart, Iād design a REST API in Database Actions that automatically includes some feedback. But I am not.
If you want to read about this Implicit Parameter and others, click here.
Luckily, the Python Requests library includes various feedback methods. Iāve included the following in my Python file:
raise_for_status() – this will display the error message (if applicable); displays "None" if the request was successful
headers – returns the serverās response headers as a Python dictionary
Different payloads
My Python code is simple enough. It looks more than it is because I include three POST request variations. Iāve also tested payloads, and it seems like we can send a "payload" as:
Oracle REST Data Services has a ton of documentation on filtering with query parameters⦠Iām still learning about it, but they are powerful. Check them out here.
You can even send payloads consisting of nested items/objects, too (e.g., an array or list as one of your values). Iām sure this violates normalization rules, but itās possible. In my case, I didnāt do this; I just stuck to my four columns.
Executing the code
After I executed the code, I received the following feedback for these POST requests:
If you squint, you can see three separate responses. I’ve only boxed one, but you should be able to point out the other two.
You should see:
a 201 status code, which indicates “the request has been fulfilled and has resulted in one or more new resources being created2.
None – which if there were a 400 error, that would show me the error message
While in VS Code, the POSTrequests appeared successful. But since I had access, I logged into Database Actions and manually inspected my table. Success!
Reviewing the new updates in Database Actions.
And thatās itā¦zero to POST request in my Oracle Autonomous Database + ORDS in no time!
Was this helpful?
I have this code in my GitHub repository, so feel free to fork, download, or add comments. But Iāll include here too:
And if you are still hereāāāwas this helpful? Want to see more or something else that is Python + ORDS-related? Let me know here in the comments, on Twitter, or by email!