Fetching results from a DB - how to handle a textcursor

I am following along in the “Julia Programming Projects” book (by Adrain Salceanu). I have Julie 1.4.0 and have had to use some package versions that are obviously more recent than his (I tried using the packages/manifests available with the book but there were too many conflicts especially with IJulia). In Chapter four we connect to a MySQL database. I have had to change the code for it to work with the most recent version and use “DBInterface” to connect and execute. So far so good. I have a data-table and some data stored in it. My problem comes when I query the table. I can’t do it as per the book and all examples I can find and must use our new friend “DBInterface” as follows:

articles = Article # Article is a struct matching the columns in the db table
sql = “SELECT * FROM articles WHERE url = ‘$url’”
result = DBInterface.execute(CONN, sql)

This works but the next line in the book’s code does not:

isempty(result.url) && return articles

The line above results in the error: “ERROR: LoadError: type TextCursor has no field url”. I guess the old MySQL.query method was a convenience method that auto-converted the rows retrieved by a cursor into an array or something. What I can’t find (or don’t understand) is how to handle the TextCursor object because it has no methods according to the documentation in Julia, so I can’t figure out how to iterate with it, or get all the results - it might well be empty too (no rows in DB).

So, I’m feeling a bit stupid because I can do this sort of thing in Python with Postgresql but am completely failing with Julia and MySQL. Can somebody walk me through this please?

I assume you are using MySQL.jl, the documentation seems to go from 1.0.0 to 1.0.1 so it is not very helpful, and it is very simple. Sincerely, reading the documentation seems like result will always be an iterable. And you seem to be checking that a field url (on that iterable) is empty. But together with the query this does not make much sense. The iterable has no field url, as it is a list of rows each with its field url, more than that, url can only be empty in the result of such query if the searched url is empty. I think you either want:

isempty(url) && return articles

or, far more probable in my opinion,

isempty(result) && return articles

but not your isempty(result.url) && return articles.

1 Like

Perfect! It is so obvious now I see it. It’s one of those time when endlessly staring at your code doesn’t let you see anything - that and working between an example from undocumented old version and the documentation of the new version. The answer as to why result.url doesn’t work must be because the returned object is a cursor and not an actual row (therefore no fields). Later in the code I iterate the cursor and it works like a charm.

1 Like

I know the feeling.

Just so you know, accessing a field of an object is equivalent to: getfield(obj, symbol_of_field_name), so if you just need a vector with the value in the url field for all objects in an iterator, you can just do: vector_of_urls = getfield.(iterator, :url).