- 1 Database servers
- 2 Controlling via the command line
- 3 Remote synchronization
- 4 Keys
A CyberTracker database is normally a Microsoft Access database or .MDB file. This is convenient because a single file can hold the entire database. However, only one user can connect at a time and the database size is limited to 2 GB.
CyberTracker supports connections to Server databases such as MySQL and Microsoft SQL Server. This is done using a connection file, which tells CyberTracker how to communicate with the server.
Installing and configuring MySQL
Download and install MySQL from http://dev.mysql.com/downloads/mysql/5.0.html. Version 5.1 or later is recommended.
Add the following line to \MySQL\MySQL Server 5.1\my.ini:
max_allowed_packet = 64M
Download and install MySQL Connector from http://dev.mysql.com/downloads/connector/odbc/5.1.html.
Creating a connection
CyberTracker supports connecting to a database via an EDB (External Database) file. An EDB file is an XML file that tells CyberTracker how to connect to the database. Here is an example EDB file:
<?xml version="1.0" encoding="UTF-8"?> <Database> <TypeRules>MySQL</TypeRules> <ConnectionString> Provider=MSDASQL.1; User ID=root; Password=root; Persist Security Info=True; Data Source=MySQL DataSource; Initial Catalog=MyDatabaseName </ConnectionString> </Database>
- TypeRules specifies how to treat data types. Acceptable values are "MySQL" and "SQL Server". This is to handle differences in SQL database types.
- User ID and Password should be set according to your credentials.
- Data Source is the name of a User Data Source as configured by the windows "ODBC Data Source Administrator". This tool binds a database driver to a data source so that applications can access it easily. The tool can be run by using Start->Run and typing "odbcad32.exe". Make sure that an ODBC driver is available for your database before setting this up. For MySQL, this is the "MySQL Connector".
- Initial Catalog is the name of the database. This database can be created using the "CREATE DATABASE MyDatabaseName" SQL command.
Converting an existing database
Once you can successfully connect to the server database by opening the .EDB file (using File->Open from the CyberTracker main menu), the next step is to import an existing database. The most common way to do this is:
- Load the old MDB database, using File->Open
- Export it to a CTX file, using File->Export
- Open the new EDB database, using File->Open
- Import the CTX file, using File->Import
- Save the new database
Multiple simultaneous connections
By distributing the EDB file within your organization, multiple users can connect to the database simultaneously. This has 2 implications to be aware of:
- As new sightings come in from remote sources, users will need to press "F5" (or use Edit->Refresh) to see new sightings
- Conflicts may arise if multiple users are making changes to the same database. To restrict this, end-users should be restricted in the operations they can perform. In all cases, the most recently saved data wins.
Using Export Data Tables
An Export Data Table is a table in a database that holds captured sighting data in an exported tabular form.
In order to view sighting data within CyberTracker, it is projected onto a table. This simply means that each sighting attribute is mapped to a column. This table is a very natural way to view and query the data.
CyberTracker has the ability to export the Query View table to a database table. However, doing this requires some setup: the table must already be created in the target database and must have certain pre-defined fields.
To get started, create a table in your database named "Export" with the following SQL command:
CREATE TABLE Export (ID CHAR(38) NOT NULL UNIQUE, Date DATE, Time TIME, Latitude DOUBLE, LONGITUDE DOUBLE, Note LONGTEXT)
The perform the following steps:
- Load CyberTracker and open a report with sightings
- With the Query Editor selected, click on the "Export View" button
- In the "Save As Type" drop down, select "External database" and select the database you want to export to
- Click Okay. Note you will get a message about overwriting this file, but it can be ignored (the .EDB file will not be overwritten, but duplicate data in the "Export" table will be)
Finally, use the following SQL command to see the data in the "Export" table in the database:
SELECT * FROM Export
Here is the list of field names (and their associated types) which CyberTracker recognizes specially:
|Field name||SQL Type||Description|
||The primary Id of the sighting. This must be indexed for merging reasons.|
||The Id of the device used to capture the observation. This can be used to group sightings created by a single observer.|
||Whether or not the sighting has been deleted. Using this field allows deleted sightings to be correctly merged.|
The UI component of this feature is designed mainly for experimentation. It can be more fully controlled via the command line interface.
CyberTracker matches fields by name, so the column name in CyberTracker must be the same as the field name in the table to work correctly. Column names can be changed using the "View Properties" dialog.
Controlling via the command line
Several CyberTracker features can be triggered via the command-line. This functionality is usually intended for server side scripts that operate on the database as a whole. To use the command line, use:
Start->Run "cmd.exe" cd <directory where CyberTracker is installed> ct3 /?
- The install directory is either: "\Program Files\CyberTracker Software\CyberTracker3" or "%userprofile%\AppData\Local\CyberTracker3"
- Do not use "ct3.exe /?", because this will trigger the windows application. Instead, just type "ct3".
"System" folders are mentioned below. These are sub-folders of "Documents\CyberTracker Databases\System". This folder can be opened in Explorer to see the way data moves through the system.
||Data that could not be inserted into a database is placed into this folder. Typically is caused by renaming of a database. The system does not process this folder again, so users should access this data using the "Data recovery console" feature on the "Tools" menu in CyberTracker.|
||Once data has been successfully added to a database, it is moved to this folder. If the database later becomes corrupt, it acts as a second line of defense.|
||When creating sightings on the local machine, these are backed up to this location if the application has the "Use SD card for backup" option checked. This directory is only used if no removable storage card is found.|
||If CyberTracker crashes, the changes that have been made since the last save are exported to a CTX file and placed here.|
||When a database update occurs and new history (past sightings) need to be uploaded to a client, then they are placed in this folder.|
||When files are downloaded from the internet, they are temporarily held in this location.|
||CyberTracker keeps a log of all sync operations. Check out the download.txt file in this location to understand errors.|
||Once files have been downloaded via a local sync, they may need to ultimately be sent to an internet server. Check out the "Send on Sync" option on the "Database Properties" dialog. This is a holding location for them before they are processed.|
||When data is recovered from a PDA or SD card, it is placed in this folder.|
||Local system settings, e.g. the current client screen selection and zoom level.|
|| Data that is waiting to be inserted into a database is placed into this folder. This folder is processed by CyberTracker routinely, e.g. on startup, on sync, etc. If the data cannot be uploaded into the target database for any reason, data will be moved to the |
|Command line option||Description|
||Disable console output.|
||Create a CTX file from the files in the specified directory.|
||Connect to a mobile device and download any data that is on it. As soon as data is downloaded from the PDA, it is placed in the Staging (see above) folder.|
|| Use the download settings of the associated |
||Upload application to the mobile device.|
||Process the Outgoing (see above) folder.|
||Specify a data file (.CTX) for use with data operations, for example as an input for the /ExportTable mechansim. If the full path is not present, the system will attempt to locate the file in "Documents\My CyberTracker Databases".|
||Specify the database to use for data operations, for example as an input for the /ExportTable mechanism.|
||The export rule file. The export rule file can be created by using the "Export View" toolbar button with the "Query Editor" active. Use the "Save as type" drop down on the File Save dialog to select "Export Rule File".|
||The target database for export operations. Must be accompanied by a /ExportRule and /ExportTable. The input for this operation comes from a /Datafile, /Database or /DownloadURL command. If the full path is not present, the system will attempt to locate the file in "Documents\My CyberTracker Databases".|
||The table name in the target database. This table must already exist and be properly configured. See "Using Export Data Tables" in CyberTracker Server Setup.|
||Export data in the specified data file to an XML file.|
ct3 /Database mydb.mdb /DownloadUrl
Open the database mydb.mdb, get the download settings and execute a download using the parameters in the "Database Properties" dialog. This is equivalent to clicking the "Download" button on the "Database Properties" dialog box.
Download all data from the currently connected device.
ct3 /Database myin.edb /DownloadUrl /ExportDatabase myout.edb /ExportRule myrule.exr /ExportTable mytable
Open the database myin.edb and get the download settings. Download all the files available using the parameters in the "Database Properties" dialog. Export this data to the myTable table in the myout.edb database. Note, myin.edb and myout.edb can be the same database.
Using this method is the preferred way of configuring a schedule task that keeps an export table up to date, because only the newly downloaded data is considered for export.
If your project involves multiple users sending data to a single database, then this feature may be of interest to you. Remote Synchronization involves CyberTracker desktops and PDAs sending data over the internet to a central server. The database administrator can then download this sighting data into a consolidated database.
Getting set up
- Get an FTP site. These are widely available and come with most web hosting solutions. Use a search engine for "Web hosting" and pick the one you are most comfortable with. They will provide you with an FTP site, username and password. If your organization has an IT department, contract them first. They may already have something set up.
- Use an FTP client like FileZilla and log in
- Create a directory
- Open CyberTracker, load the database and click "File->Database Properties"
- In the "Upload Settings", setup the Url to: ftp://ftp.yoursite.com/CTData and also set user name and password
- Leave "Download Settings" as "[Same as Upload]"
- Ensure "Send on Client" is checked
- Click the "Download" button. This will report connection errors if any.
- Close the dialog, save your database and re-sync your PDAs
Note: if the PDAs do not have an internet connection and your users sync their data to their own PCs, then check "Send on Sync". This will cause any data downloaded off the PDA to be sent to the same central server.
Note: the auto-send frequency will cause CyberTracker to send data in the background at timed intervals. This is useful if you would like a consistent incoming data stream and are less concerned with battery life.
Testing the system
- On the PDA, create a few sightings
- Open the "Options" dialog
- One of the tabs should be "Send Data". If not, make sure the "Send on Client" checkbox is enabled (see above)
- Tap the button where it says "Tap to send". This will kick start the sending process.
- If the send fails for any reason, check your connection settings. If any changes are made in the "Database Properties" section on the desktop, PDAs will need to be re-synced.
- Go to the "File->Database Properties" dialog and click the "Download" button
- Once downloading is complete, close the dialog
- The new sightings should appear the report
If the FTP server is also the database server, then it is possible to bypass a step. In this case, the Download Settings protocol should be "File share", with the url being of the form "\\server\directory", or possibly even "c:\ftpfiles".
If all the devices are on the same wireless network, it is possible to bypass FTP altogether. In this case, set the "Upload settings" protocol can be "File share".
HTTP and HTTPS are also provided for upload. However these don't work for download, because these protocols don't provide file-system like functionality.
Data can also be uploaded and downloaded directly via the CyberTracker Command-Line. This allows a database administrator to create a scheduled task on the server which routinely picks up data files and adds them to the database.
CyberTracker has a feature called Keys which allows progressive refinement of data on lists. As data is entered, you can filter a result set down to just those matching specific attributes. For example, a bird list can be keyed on the colors and characteristics of each species.
Normally this information would be entirely contained within CyberTracker, but it is often convenient to describe Keys in Excel and paste them into CyberTracker when complete. The Screen Element Filter dialog contains a "Paste From Excel" button that can be used for this purpose.
To configure a key outside of CyberTracker, follow these steps:
- Create a key in Excel in the above format (colors don't matter)
- Select all populated cells
- Copy to the clipboard
- Paste into CyberTracker
The above key and demo can be downloaded here.