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
Often when working with Bash scripts we find that we want to add some options to the script by way of arguments or variables. Easily enough Bash command line arguments can be accessed by $1, $2, etc within the script where $1 is the first argument or variable and #2 is the second etc.
Example:
#!/usr/bin/env bash
echo the first argument is $1
echo second argument is $2
echo eleventh argument is $11
echo total number of arguments is $#
Resources:
Arguments, bash, Command Line, parameters
MDBitz - Matthew Denton
Java
What is Spring
Spring is an open source framework created with the purpose of enabling developers to build and deliver applications faster. The Framework is available for both the JAVA and .NET platforms. Spring provides a set of core features that can be used for most applications as well as modules that provide additional features.
Sampling of Modules
- Data Access -
The Data Access Module allows for streamline configuration of Relational Database Management Systems (RDBMS)using JDBC and ORM‘s such as Hibernate and ibatis
- Security | Authentication -
Spring Security Module provides advanced authorization and authentication features to your project/application. Some Key Authentication features being:
LDAP, JAAS, Basic Access Authentication, and Digest Access Authentication
- Model View Controller -
The Model View Controller module provides a MVC framework for use in developing Web Applications.
Initializing Spring from within an Application
To initialize Spring from within an application means to instantiate the ApplicationContext by use of a configuration file. I will not go in to the details as to how to set up the configuration file but will assume that you have created such an xml file called applicationContext.xml.
First Within your application import ApplicationContext and FileSystemXMLApplicationContext classes which will be used to instantiate the Application Context.
import org.springframework.context.ApplicationContext;
import org.springframework.context.support
.FileSystemXmlApplicationContext;Then we Instantiate the Application Context.
ApplicationContext appContext =
new FileSystemXmlApplicationContext( contextFileName );
Last we utilize the beans defined in our xml file.
MyObject obj= appContext.getBean( "ObjectName" );
Resources
Command Line, Spring, Spring Framework, SpringFramework