Each server contains multiple databases. Each database contains multiple tables.
A user has one logon (login id/password) for the whole server. This is called the server user name.
A user must be added to a database within the server before the user can access that database. The user's name within the database is just called a "user name".
A user has one server user name for the whole server and one database user name for each database he's been in.
Each database has one user designated as the database owner or dbo. The dbo is privileged within the database.
A server user name may not be the same as the corresponding database username. It's generally a good idea to keep them the same.
When a user logins to the database server, the user is put into his default database (different for different users).
By default an SQL query will look for a table name in the current database.
A server will often contain multiple databases. There is a "pubs" database supplied by Sybase as an example application database. It even has consistent data in it.
This section provides some sample Transact-SQL relating to multiple databases in an SQL Server. Note that "isql" (from unix) requires a "go" after each of the SQL statements.
To look at your current database:
Looking at the list of available databases:
Changing your default database to the pubs database:
Doing a select against a table in the current database (assuming the current database is pubs):
Doing a select against a table in another database:
In the above query, tempdb refers to a special database within each server. More on tempdb later. The optional "dbo" in the above query refers to the user owning the table in the tempdb database. We'll talk more about this in the security section later. For now just use the ".." notation.
The above examples use select statements, but they could just have easily used insert, update, or delete statements.
figure - pubs database block diagram
From a Unix terminal login use isql to submit sql to a Sybase server. For example:
You can send more than one SQL statement at a time. For example, you can type in eight select statements and then send them all to the server at once. These eight select statements are said to be part of a batch.
In Unix, use the "go" statement in isql to send the SQL statements.
In most PC client SQL utilities, use the "send sql" button. You will need to click on the "more results" button for the results from each separate select statement.
Note that a "go" or "send" corresponds to a dbsqlexec in db-library (Open Client) C code.
Certain commands cannot be batched together (eg. use pubs).
Sybase keywords are NOT case sensitive.
Sybase object names (tables, views, etc.) ARE case sensitive.
We'll talk about security later, but for now you'll need to know that everyone has read-only access to the pubs database.
The Commands Reference Manual is the bible for Transact-SQL.
Structured Query Language is non-procedural. Data manipulation occurs in a single step (query).
Sybase has extended SQL with procedural control statements. The extended SQL is called Transact-SQL.
Transact-SQL includes the following procedural extensions:
Before we discuss the control structures like "if" and "while", we need to briefly discuss local variables.
Local variables exist within the current procedure batch. That is they exist until the next "go" (isql) or "send sql" . As we'll see later, local variables are also used by stored procedures. A local variable declaration has the following syntax:
For example:
Note that there is no end of line terminator (eg. semicolon).
To assign values to a local variable, use the "select" statement:
Note, when using select statements to assign values to variables only one value must be assigned, otherwise the last value returned is used.
For example:
There are two ways to display local variables which are slightly different. The first way is to use the "select" statement:
The second way to display local variables use the "print" statement. The print statement only works with character type variables:
With the select statement, the value of myvar is returned as part of the mainstream data. To the client it looks like the results of a select on a one row table. Also, the select statement can have expressions in it's select list.
The print statement actually sends a text message to the client error message handler (eventhough it's not an error). The client handle the output of the print statement in the same way it handles error messages from the server, usually with a pop up box.
The "print" statement is usually used for sending warning or status messages to the end user.
The select statement output must be explicitly processed by the client to be made use of. In other words, the client must be expecting the results. This is not the case with the print statement.
Note you cannot assign to and display a local variable in the same select statement.
The print statement can only take a single string parameter. In order to format a print message with parameters you must do the following:
A boolean expression uses standard comparison operators such as:
Boolean expressions can contain local variables.
The following are examples of several types of if statements:
Note that in the last example any select statement can be used as long as it returns only one row of the proper type. The select statement must be enclosed in parenthesis.
A special version of the "if" statement works as follows:
Also:
The "if exists" statement only tests to see if any rows were returned by the query. It doesn't make any difference what column is selected in the "if exists" test.
The begin..end structure permits a series of Transact-SQL statements to be treated as one from the point of view of "if" and "while" constructs.
For example, in all the previous "if" examples, only one Transact- SQL statement could be executed if the boolean condition was true. The "if" construct treats all the lines between the begin and end as if they were one statement:
Begin..end has no significance for transaction processing. It in no way affects commits or rollbacks.
The syntax of the "while" statement is as follows:
For example:
A "break" statement in the begin..end will exit the while loop.
A "continue" statement in the begin..end will jump to the "end" and start the next iteration of the loop.
The "while" statement isn't used that much because Transact- SQL doesn't support arrays.
One common Transact-SQL coding problem involves performing processing for each row in a table. Here's an example:
System 10 will use cursors control to implement this sort of processing.
The following is a table of Sybase supported datatypes:
The Sybase server uses ASCII as does the Unix operating system. IBM's DB2 database uses EBCDIC.
As we'll see later, the Sybase Net Gateway transparently takes care of the ASCII to EBCDIC conversion for us.
ASCII and EBCDIC have different sort orders. Some applications are affected by this.
Pre-System 10 Sybase does not support fixed decimal numbers (System 10 has the numeric datatype). Instead, you would use the money datatype (which actually is a fixed decimal type) or the float datatype. In some cases, it may be necessary to carry the numbers as integers in Sybase (int) and manually keep track of the decimal point.
Sybase's char and varchar datatypes support a maximum string length of 255 characters. DB2's is max char string length is much longer. Sybase does support arbitrary length "text" and "image" datatypes, but there are restrictions on these (as we'll see later).
Sybase has special global variables which, which should really be called system variables.
These are predefined variables which hold system related information. All global variables start with a "@@"
The most interesting global (system) variables are:
Very similar to the "print" statement with one added feature. The @@error global variable is also set by this.
The raiserror statement does not abort the current batch of command. Execution continues at the next statement after the raiserror.
Format is:
Error number must be greater than 20000. The @@error variable is set to {error number}
Hint, Sybase defined error codes have values less than 20000. These error codes and associated descriptions are kept in a database table called sysmessages in the master database. Here's a sample query that look for error codes:
A transaction represents a set of database operations which either complete successfully or rollback so that no modifications at all are made.
Sybase transactions differ slightly from DB2 style transactions.
Sybase transactions start with an explicit "begin transaction" statement and end with the standard "commit transaction". These statements have to occur in pairs.
In DB2, there is no explicit "begin transaction". Instead, there is an implicit "begin transaction" whenever a "select", "insert", "update", or "delete" is executed. This style of transaction management is called chained transaction management.
In Sybase, if a single "select", "insert", "update", or "delete" is executed, the single statement is treated as if there is a "begin transacton" before the statement and a "commit transaction" after the statement.
The following is an example of transaction management in Sybase (you must have insert access to authors to run this):
Sybase locks pages (2048 bytes) not rows. This is a trade off between the speed of managing locking and level of contention that occurs at runtime.
Any writes (insert, update, delete) that occur during a transaction cause that page to be locked until the commit or rollback.
Remember that even with no "begin transaction" statement, indivdual insert, update, delete statements have implicit begin/end transaction statements around them. While these statements are running they will have pages locked.
By default, reads (select) are not locked. Sometimes, this is not appropriate. If you need to guarantee that a selected row doesn't change once read during a transaction, use the select with holdlock. For example: