Creating a Database in Sybase
Implementing Database in Sybase – Part 2
Division 4
Forward: In this part of the series, we see how to create a database in Sybase.
By: Chrysanthus Date Published: 26 Aug 2012
Introduction
Note: If you cannot see the code or if you think anything is missing (broken link, image absent), just contact me at forchatrans@yahoo.com. That is, contact me for the slightest problem you have about what you are reading.
Working with a Database
To work with a database you have to follow the following steps:
- Create the database file.
- Start the database server (in memory) that will run the database.
- Start the database (put it in memory).
- Connect user to the database (in memory).
- Use (create tables, save data, retrieve data, etc) the database.
- Disconnect from the database.
- Stop the database (remove from memory).
- Stop the database server.
Note: when you complete anything you do with the database such as create table or send data, the result is automatically saved to the file; there is no command or statement to save changes to file. However, in some cases you can have the changes in memory and then flush the changes to the database file in disk.
In this division, steps 2 and 3 will be combined and steps 6, 7 and 8 will also be combined.
The dbinit Utility
SQL Anywhere 12 comes with a tool called dbinit. This tool works directly with the operating system to create a database. In simple terms the syntax is:
dbinit Path\filename.db
You type the tool name, which is a command, at the command prompt; then a space and then a path and new file name ending with the extension, db. The directories in the path should already exist. The file name should be new and should not already exist in the last directory in the path. The database and its tables will be kept in this file. When this command is executed at the command prompt, if everything goes well, some lines will be displayed at the command prompt window and one of them will say that the creation of the database has been successful. Then you will have a new command prompt displayed again.
You can start the server and database at the same time with the dbeng12 utility we saw in the previous part of the series. In simple terms the syntax is:
dbeng12 -n serverName Path\filename.db
You type a servername of your choice, as you type the command, and then you type the path and file name of the database file in disk. When stopping the server, you have to use this very name.
Interactive SQL
SQL Anywhere comes with a tool called Interactive SQL. This is a window-based tool that allows us to use SQL statements. SQL statement are statements you use to create your database tables; send data to the tables and retrieve the data. While starting Interactive SQL the user can be connected to the database. In simple terms, the command to start and connect the user to the database at the command prompt window is:
dbisql –c "server=serverName;DBF=Path\filename;UID=userid;PWD=password-string"
You can start Interactive SQL from the command prompt, using the tool (utility), dbisql, whose name means Database Interactive SQL. This command opens the Interactive SQL window and connects to the database. You type the name, followed by the –c connection switch. Then you have the parameters for the connection switch in a string. The parameters are separated by semicolons. Each parameter consists of a parameter name and a parameter value.
The value of the server parameter is the server name of the server you started. The value of the database filename name (DBF) parameter is the filename of the database file, preceded by the path. The database and the database file have the same name by default but the database file has the extension, db; you can change this so that they have different names, but I will not go into that.
It is not everybody that has the right to use a database. So the user ID and his password have to be typed in the connection string. The default user ID is DBA for Database Administrator. The default password is “sql”. If you choose the default ID and default password, it means anybody can use the database. In this series, we shall use this default ID and default password most of the time.
Consider the database of a wholesale company that others products from manufacturers and sells to shops in the cities. Let the database name be, wholesale. The following commands will start a server called, wholesalesrv, and create the database wholesale (with filename, wholesale.db) in the path and directory, C:wholesale. The Interactive SQL will also be started for you to type in your SQL statements to do things like create the tables (see later). The Interactive SQL window may take a while to appear. First create the directory, wholesale, in the root directory of your computer. Start the command prompt window and type, cd c:, to go to the root directory; then type the statements pressing the Enter key at the end of each statement; type each statement when you see the command prompt after you have seen the effect of the previous statement.
dbinit c:\wholesale\wholesale.db
dbeng12 -n wholesalesrv c:\wholesale\wholesale.db
dbisql -c "server=wholesalesrv;DBF=c:\sholesale\wholesale.db;UID=DBA;PWD=sql"
Note that the DOS backslash in the directory path is doubled. With this, the utilities will know that you mean backslash and not some escape sequence.
After that you should be seeing the Interactive SQL window. There are two panes in the window arranged vertically. In the top pane you type in the SQL statements. In the bottom one you see the results of the SQL statements. We shall talk a lot about those panes later.
You can disconnect the current user from the database, using a SQL statement in the Interactive SQL. The syntax is
DISCONNECT CURRENT;
Any SQL statement end with a semicolon.
Stopping the Database
You can stop the database (remove from memory) using a SQL statement. The syntax is:
STOP DATABASE database-name
You can use the Interactive SQL window for this.
Note: When closing the Interactive SQL window, you may be asked if you want to save the SQL statements (into a file), just click No.
Stopping the Server
We shall be stopping the server using the dbstop command at the command prompt. We saw this in the previous part of the series. For simplicity, we shall be doing the last three steps above in one step. Type the following command at the command prompt to stop the server, and then press the Enter key.
dbstop wholesalesrv
The command prompt may say there are still active connections and it will ask you if you want to stop anyway. If that is the case, type, y, for Yes and press the Enter key.
The log file
As you are manipulating your database, a file can be keeping crucial information about the database. When something goes wrong with the database, the file can be used to correct the problem. When you use the dbeng12 command to start the server and database, this file is created automatically. The file has the name of your database but with the extension, log. It is saved in the same directory as your database file.
That is it for this part of the series. We stop here and continue in the next part.
Chrys
Related Courses
C++ CourseRelational Database and Sybase
Windows User Interface
Computer Programmer – A Jack of all Trade – Poem
NEXT