Code up retrieving the colname name definitions from zwq_FieldDefinitions

[eeholmes]

Thanks, Mike and Greg!

On Wed, Dec 7, 2022 at 1:08 PM Mike Banach [mike_banach@psmfc.org](mailto:mike_banach@psmfc.org) wrote: Hi Mari.

Eli should now be able to directly access field definitions via the StreamNet API.

The GUID for the table is BD0C3244-B688-4C3E-BE18-21AF0E9E5FCB. Table name is zwq_FieldDefinitions. The table has a few fields that are not useful but she should be able to easily find and use what she's after.

Mike

[eeholmes]

@mari-williams Unfortunately no luck accessing the table for the definitions. The GUID that Mike sent. It is returning a server side error (meaning its on CAX's side). The error is the same as before: It is looking for a "publish" column.

"[Macromedia][SQLServer JDBC Driver][SQLServer]Invalid column name 'publish'."

I am guessing there might be a check to make sure they only return data that have 'publish=Yes' but it causes a problem if the table has no 'publish' column.

httr::GET("https://api.streamnet.org/api/v1/ca?table_id=BD0C3244-B688-4C3E-BE18-21AF0E9E5FCB&XApiKey=7A2F1EA9-4882-49E8-B23D-7DC202C2ACA5")

[mari-williams]

They've added a publish column vs coding an exception to the rule. If it works, close this out.

[eeholmes]

It works! I'll keep it open because now I need to code up getting the column definitions from the downloaded table.

[eeholmes]

@mari-williams They said the table name is zwq_FieldDefinitions but it is not listed when I do a query from https://api.streamnet.org/api/v1/ca/tables. I can just use the table_id that they provided but I'd rather look up the table_id in case they had to change the table_id but kept the table name the same. Can you check?

[mari-williams]

Now all 'list' tables should have an exception to the 'publish' requirement!

[eeholmes]

I tested with this code

tab = httr::GET("https://api.streamnet.org/api/v1/ca/tables?XApiKey=7A2F1EA9-4882-49E8-B23D-7DC202C2ACA5")
data = jsonlite::fromJSON(rawToChar((tab$content)))
data$tables$name

and zwq_FieldDefinitions now appears in the list.

Tested the table with

#zwq_FieldDefinitions
tab = httr::GET("https://api.streamnet.org/api/v1/ca?table_id=BD0C3244-B688-4C3E-BE18-21AF0E9E5FCB&XApiKey=7A2F1EA9-4882-49E8-B23D-7DC202C2ACA5&$select=id")
data = jsonlite::fromJSON(rawToChar((tab$content)))
data$records

and this also works.

Closing.