Creating Database Projects with SQL Anywhere Studio, Part 1

SQL Anywhere Studio 9.0 is a product of iAnyware, a Sybase company. It is a packaged product with database management and database synchronization capabilites. It has RAD capabilities, and is primarily targeted for three categories of datacentric applications: mobile, small business, and embedded. In this first part of a two-part article, SQL Anywhere Studio 9.0 installation and capabilities will be discussed and in Part 2, user management, creating database applications with Microsoft Excel, VB 6.0, and Visual Studio will be discussed.

 

SQL Anywhere is supported on a large number of operating systems, including Microsoft. Its small footprint, broad platform support, and data access support make it attractive and ideal for small businesses. Combining it with the built-in synchronization architecture makes it  attractive for mobile applications, as evident from its being chosen for the Siebel Sales Enterprise software (http://www.ianywhere.com/success_stories/siebel.html).

The following topics will be discussed in this tutorial:

  1. Download and installation of SQL Anywhere Studio 9.0

  2. Sybase Central – Graphic User Interface for Object Management

  3. Description of Database Objects

  4. Database queries using Interactive SQL

  5. Conclusions

{mospagebreak title=Installation}

SQL Anywhere Studio may be downloaded from this location (http://www.ianywhere.com/downloads/sqlany.html). This executable will install the software with the chosen options on the machine. Once the install wizard starts, it is a relatively painless process, as seen in the next three pictures. You may download software documentation in HTML and PDF formats from the same locatlion.

Some of the important components installed include the databases(servers), synchronization and messaging for mobile link and SQL Remote client connectivity, and the tools, of which the Sybase Central is a key component. Sybase Central plays a role similar to that of the Enterprise Manager in SQL 2000 Server. Its smallfoot print is evident in the size it occupies on the drive. Ultralite’s even smaller fingerprint makes it optimal for handheld devices.

{mospagebreak title=Installed Elements}

The installation adds shortcuts to the Programs folder as shown above, makes entries in the windows registry, adds hooks to provide support for the .NET Framework, and installs required drivers for the ODBC Connections as shown. The discussions in this tutorial will be limited to Adaptive Server Anywhere and Sybase Central.

From Adaptive Server Anywhere one could access an interactive SQL Utility, a Network server, a Personal Server(local), and an ODBC manager. The network server(dbsrv.exe) is for client/server architecture, whereas the personal server(dbeng9.exe) is for desktop, single-machine use. Clicking on either Network Server Example or Personal Server Example will start the database server in the selected mode. Only one instance of a selected server can be running at any time. When the server is running the shortcut will be minimized. From here it is possible to shut down the server.

{mospagebreak title=Sybase Central}

Sybase Central is a dashboard for any activity that one may wish to perform on the database. Its functionality is very similar to the SQL 2000 Server’s Enterprise Manager. One could use this canvas to carry out many different kinds of activities, such as connecting to and disconnecting from the server, creating databases, creating tables and other objects, etc. If one is familiar with other database products it is quite intuitive.

Both Adaptive Server Anywhere 9.0 and Mobilelink Synchronization are plug-in type JAR files, as seen in the property page of this management panel.

Sybase Central can be activated by clicking its shorcut, shown earlier, which will display a splash screen as seen here. Sybase Central can be activated before or after starting the server.

Connection to the server from Sybase Central can be established in a couple of ways as shown below.

When you click on connect to establish a connection you will encounter this dialog box which requires authentication information[Identifcation tab]. For UserID type in dba and for password type in sql and accept the rest of the defaults and click OK. Proof positive for connection can be acertained by clicking the “Test” button in the ensuing dialogue.

In the Database tab click on the drop-down menu by the side of the label Server Name and you should see the name of the installed server [asademo9]. Now click on Find… and the program starts looking for the server and persents a FindServer dialogue box. If the server has not started then no servers will be found. If you start the server and then click find, you will see the following screen. Using the Browse… button by the side of Database File label, the database files must be located. This can found in the installed location, which in this case was C:Program FilesSybaseSQL Anywhere 9asademo.db. One could also find this location information in the running server, which is minimized in the taskbar.

{mospagebreak title=Database Objects}

Once the database files are found, clicking OK will populate the Sybase Central with all the objects for the database. Other defaults may be accepted for this initial exploration. In the folders frame you find the databases and other objects, and in the list view a set of tabbed pages showing various details of the object under query. For the database asademo9, Databases, All Connected Users, Statistics, Performance can be seen.

The Tools menu option can access all the activities that can be performed on the database as shown. The following pictures also show the expanded view of tables and the columns for the table Contact.

{mospagebreak title=Interactive SQL}

In this section, another important tool, the Interactive SQL will be explored. SQL Queries, stored procedures, etc. can be interactively run using this tool. Examples of creating and running queries and stored procedures will be shown. Clicking the shortcut, Interactive SQL brings up the earlier shown “Connect” dialogue, wherein you enter the authentication information.

Once the authentication is verified, the SQL designer window opens up with its own menu bar, a tool bar, a SQL pane, and a Results pane. The SQL, Data,Tools, and Help menu are used for various database connected activties. These can be seen in this next picture.

The important menu items are shown in the next picture. Some of them may be available from the tool bar as well. From the tools menu it is possible to look at the tables, stored procedures, indices, etc. In the SQL Statements pane, the queries/procedures will be coded and they can be executed by hitting the “run” button. The results of executing the queries are shown in the Results pane.

The Lookup Table functions very much like an object browser, and the column names can be obtained before coding the queries. The Lookup Table dialogue shows all the tables. This can be drilled down further for the columns and other items.

Here is an example that illustrates running a simple query. The query is typed into the SQL Statements pane and, after executing the query, the results are shown in the results pane.

Further filtering of the query yields Customers from “California” as shown here.

Query errors are shown with the error information and line at which error occured.

Error is removed and the query is executed again.

Stored procedures are are also coded and executed similarly. The next screen shows a stored procedure ‘test’ being coded and executed.

This creates a stored procedure object with the same name in the Sybase Central administrative pane as shown here and in the following the result of executing this stored procedure.


Concluding Remarks

Adaptive Server Anywhere download and installation is easy and fast. Database management is easily accomplished in the Sybase Central management console. This server appears to be superior to MSDE –which has approximately the same database size capacity, does not have a visual interface for database management, and is limited to Windows only platform.

[gp-comments width="770" linklove="off" ]