Accessing CyberTracker data directly from R: can't retreive data from the .mdb

Support - Classic
1

Hello everyone,
I am trying to write an R script to load sighting data directly from the database into R, i.e. directly from the .mdb file, without the need to export a .csv or .ctx file in between.

Using the RODBC Package, I have no issue accessing the database and can retrieve some data, for example Sightings2/Id, Sightings2/DateCreated…

My 2 issues are with the actual sighting data, stored as Long Binary in the .mdb column BinData. Here is my fetching command, then my issues:

df ← sqlQuery(channel,“SELECT TOP 3 Id, DateCreated, BinData FROM Sighting2 ORDER BY Id;”)

  1. The data I get back is encoded (I think in ASCII). When I open the data-frame in R-Studio, I get:

as.raw(c(0xe6, 0xf5, 0x64, 0x47, 0xa1, 0x15, 0xbf, […]

In R, I get

e6f56447a115bf48808af673ed7cdcda… (circa 700 characters)

Is there a way to get the data from the .mdb file in a human friendly way?
I guess it could be converted after fetching, but is it possible to fetch it directly readable, so R can fit it into different columns as it get the sightings.

  1. I added TOP 3 to my query because whenever I tried to fetch more than 3 lines, R and R-studio crash. The LongBin/blob is less than 700 character long for each line, I doubt R is running out of memory.

It would help my colleagues and I greatly if we could make this connection work.
Thank you per advance,
Mathieu

2

Hi Mathieu,

We do not want people to connect directly to the database. Instead the idea is to export the data to format you like (e.g. CSV) and then query that. You can create a command script which does this routinely: Advanced Topics | CyberTracker Wiki.

Cheers,
-Justin

3

Hey Justin,

Thank you for your answer, however, this is unfortunately what I am trying to avoid.
Our database is huge, so exporting (via .csv, .ctx or even to an SQL database), then importing it back to R is too time consuming.

Is there a way to read the Long Binary Sightings in the .mdb file, or is this something that is locked on purpose by CyberTracker?

Thank you per advance,
Mathieu

4

Hi Mathieu,

Could you say more about the size? Even tens of thousands of observations should export in seconds.

We could also make the export incremental if using an external database (like SQL Server).

Decoding a custom binary format that may change is probably not going to be very durable.

Cheers,
-Justin