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
Command Line, connect, Interactive SQL, ISQL, SYBASE, T-SQL, Transact-SQL
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
BCP, Bulk Copy Utility, Command Line, SYBASE, T-SQL, Transact-SQL
Remembering the correct command prompts across different platforms can be confusing. To make things easier on myself I have compiled a short list of the commands and their options that are commonly used to create and delete files and folder in DOS. Including how to delete a folder and all its contents.
How to Delete a file
Deleting files from the DOS prompt is done by the del command. To use it you simply specify the single file you want deleted or the wild card pattern to match files against.
Deleting a Single file
Deleting all .jpg files
In addition to deleting files in a single directory you can delete files from subfolders as well by using the /s switch. This option says to delete files matching the pattern in a ll subfolders. I highly suggest using this switch only if you know what you are doing.
Deleting all files within a folder and its subfolders
The most important thing to remember is that the del command will delete files but will leave the folder structure intact.
How to delete Folders
There are two commands available to delete folders rd and rmdir. Both commands perform the same functionality so you can use which ever comes more natural to you. To delete a folder you will first have to delete its contents.
Deleting a Single Folder
rd myFolder
rmdir myFolder
To delete a folder as well as its contents including files and sub folders you will use the /s switch. If you don’t want to have the command line prompt you with are you sure you want to delete each file you can also use the /q switch. This causes the command to run in quiet mode meaning it will assume y for all the prompts.
Deleting a Folder and all its contents
rd /s /q myFolder
rmdir /s /q myFolder
Creating a new Directory or Folder
To create a folder you use the md or mkdir commands. When running the command simply type the name of the folder you want to create. If you want to create a sub folder as well simply pass in the full path and it will create the parent folders if they don’t exist.
Creating a single Folder
md myFolder
mkdir myFolder
Creating a sub Folder and its parents
md myFolder/Folder2/Folder3
mkdir myFolder/Folder2/Folder3
To be sure creating and deleting files and folders is a command that by far is done mostly through the Operation Systems User Interface. However there are times that working from the dos prompt (command line) is a necessity, in those case simply remember del, rd, and md commands.
del, delete folder, delete subfiles, md, mkdir, rd, rmdir, switch
XCOPY is a dos command used to copy files or directories from one location to another. Most Windows user may never need to use the dos command prompt to migrate files between network drives but those of us that utilize both mac and pcs may find the need. Recently I obtained a new Windows 7 notebook to use at home and have since been in the process of syncing up my files from my apple macbook. While trying to transfer files off of my network drive I noticed that the transferred would fail silently with no files being copied to my notebook.
As it turned out windows didn’t like some of the file formats and so would quit on the copy without specifying an errors to the user… way to go Microsoft… Luckily for me I have experience with the command line and fired up the ms-dos prompt to see what was happening. After running a quick copy of the folder in question I found out immediately that a folder was unrecognizable by windows and was causing the copy to fail. To transfer the files I ended up using the XCOPY command as it allows you to continue the copy even if an error occurs. For others that may experience similar behavior below is a short How To on the XCOPY command.
Basic Command Format
The basic XCOPY requires 2 parameters the file / folder to be copied and the location the file / folder is to be copied to. If you want to copy a folders content and sub folders then be sure to use the * wild card.
XCOPY -SOURCE FILE- -DESTINATION- -OPTIONS-
Available Options
The XCOPY command has many options the ones we are intereseted in are listed below.
- /C – copy continues even if an error occurs
- /S – copy sub folders and files
- /I – default destination to a folder if command is unsure of destination
Example
XCOPY M:\users\user1\* C:\users\user1 /c /s /i
Resources
Apple, MAC, MS-DOS, Windows 7, XCOPY