MDBitz Development Resources

The official 1.0 release of the PHP Wrapper Library of the Harvest API is finally here. Over the last few weeks I have been performing bug fixes as well as making some functional enhancements. In this release I have added in a new HarvestReports object that allows users to query for only active/inactive clients, projects, and users. In addition there are quick methods for obtaining the active time entry of a user or for all users in your account, if you need to quickly see what Users are working on. The second major enhancement is the addition of preset time period methods to the Harvest_Range object allowing you to quickly get reports on this week, last week, and this/last month.

You can download the latest version of the library on it’s official page over in the Project Laboratory. In addition you will find many examples showing you how to configure and use the library.

Due to the increased usage and amount of projects and utilities that can be found here on my developer blog, I have decided it is time to create a separate site for them allowing for users to find relevant examples and information easier. Therefore the MDBitz – Project Laboratory is know available. Currently I have ported over all the examples and information on the Harvest API and Yahoo Finance API Wrapper Libraries.

Slowly over the next few weeks/months I will be updating all the pages and content found in this blog to reflect the new site. In the mean time please be patient as the site maintenance is in progress.

In most instances you will be using a User Interface of one sort or another to execute Transact-SQL commands against your Sybase Database Servers. In rare occasions however I find that I execute commands through the dos prompt via the Interactive SQL Utility or iSQL as it is more commonly referred.

Opening a Connection

BCP and iSQL both use the same parameter flags when specifying the database server and user credentials for connecting to a server.

• Server Flag -S You can specify the Database Server you are accessing by use of the -S flag followed by the Server. (e.g. -S SYBABC )
• User Flag -U If you are not using credentials outlined in your .ini file then you can specify the User you would like to use for authentication using the -U flag followed by the user name. (e.g. -U guest)
• Password Flag -P If no password is supplied then a prompt will occur to input it. You can use the -P flag to specify the password to use to bypass the prompt. (e.g. -P test123)

Using these variables you would connect to the Sybase Database Server with the following command.

isql -S server -U userName -P password

Issuing Transact-SQL Commands

A successful connection will present you with the > carat letting you know you can now enter your T-SQL commands. To do so you simply type the command line by line and when finished type the GO command to issue the SQL.

> USE db2
> GO
> SELECT *
> FROM books b
> WHERE b.pub_date >= '09/01/2009'
> GO

Understanding the output from iSQL

iSQL will output the results of the command to standard output. However you have the option of formatting the output to your own preferences.

• Headers Flag -h to modify the number of lines between the column headers and the data rows use the -h flag followed by the number of desired lines.
• Column Seperator -s by default a space is used to separate the columns in a row you can use the -s flag to change this to your desired delimiter.
• Column Width -w by default the output is limited to 80 characters per line use the -w flag to modify this to the length you want.

Interactive SQL is a useful tool when you need to quickly execute commands against your database. For that reason alone it is worth knowing about the utility, but in most cases you will find yourself using other UI based applications to issue commands against your Sybase Server.

References

• Sybase Online Manual – Using the iSQL Utility

When performing database maintenance you will occasionally find the need to export data out of your database tables to an operating system for storage, or conversely import data to a table from a file. You may find yourself needing to do these tasks for data backup or for inserting data that comes from a 3rd party export. Sybase makes this process simple by the Bulk Copy Utility (BCP).

Basic BCP Usage

BCP as it’s name suggest is used for 1 purpose the bulk copy of data to and from Sybase Tables. To utilize the utility the basic command format is:

bcp DATABASE..TABLE out C:\DB\temp.txt

If you wanted to perform an import you simply switch the out keyword for in.

BCP Option Flags

In addition to the command there are a few options you may also find yourself using.

• Server Flag -S You can specify the Database Server you are accessing by use of the -S flag followed by the Server. (e.g. -S SYBABC )
• User Flag -U If you are not using credentials outlined in your .ini file then you can specify the User you would like to use for authentication using the -U flag followed by the user name. (e.g. -U guest)
• Password Flag -P If no password is supplied then a prompt will occur to input it. You can use the -P flag to specify the password to use to bypass the prompt. (e.g. -P test123)
• Native Format -n To export data in native format which is not readable by looking directly at the file you can use the -n flag. This mode will make it so you don’t have to specify the column format during export.
• Char Format -c To export all data in basic char format then you can use the -c flag. This mode will also make it so you don’t have to specify the column format during export.
• Tab Format -t The default format for delimiting data columns per row is the tab character to specify a different delimiter -t can be used followed by the new delimiter.
• Row Format -r If you want to have a different delimiter then the new line character in your export you can modify it by using the -r flag followed by the new delimiter.

Basic BCP Output Example

bcp DATABASE..TABLE out C:\DB\temp.txt -c -S server -U userName -P password

Basic BCP Input Example

bcp DATABASE..TABLE inC:\DB\temp.txt -c -S server -U userName -P password

The Bulk Copy Utility (BCP) is a very simple and versatile tool. If you find yourself doing a lot of database management you may want to familiarize yourself with it as it can save you a lot of time, effort, and headaches.

Resources

• Sybase Online Manual – BCP Utility Guide

Release 0.4.2 is now available of the Harvest API PHP Wrapper Library hot on the heals of the 0.4.1 release. In this Bug Fix we resolved an unrelated issue with parsing of User and Project entries obtained by the getUserEntries and getProjectEntries methods. The underlying error was caused from the time tracking api using _ as their name delimiter while the extended api uses -. As a quick fix the parser method has been added with the day-entry case to match both instances.

A full fix for this inconsistency is in the works as part of the 1.0 release, where a converter will be introduced to properly manage the _ and – characters so that the end user (you) will not have to worry about these little quirks in the Harvest API.

To stay up-to-date with all the recent changes please visit the main HarvestAPI release page.