Creating Database Projects with SQL Anywhere Studio, Part 2

In this second of two articles, you will learn about user management with Sybase Central, integrated logins, importing ASA data into an Excel worksheet, and more.

Introduction

In Part 1 of this two part article, SQL Anywhere Studio 9.0 installation, and interactive SQL tool were discussed. More importantly, starting and stopping the database server, creating sql statements and stored procedures were discussed.

In this tutorial, user managment will be discussed. Since databases have to be accessed, setting up user credentials, and giving permissions to ASPNET for Web applications will be discussed.

User Management with Sybase Central

Following procedures as outlined in the previous tutorial it should now be easy to start and stop the server, bring up Sybase Central and connect to the database, and also write and run queries. Now start the server and the Sybase Central management console. After this step, the server (network) should be running and Sybase Central should be showing the database as shown in the next picture.

Creating Database Projects with SQL Anywhere Studio, Part 2

In the left hand frame the Users and Groups node can be located. An expanded view of this is shown in the next picture. If you right click this node, you will be able to create a new user or new group.

Creating Database Projects with SQL Anywhere Studio, Part 2

If you agree to create a new user, you will open up a Create New User wizard. The next couple of pictures show the screens that you would encounter when creating a new user. Here a new user named “mysorian” is created. It will be necessary to create a password and, more importantly, to note it down as it will be used later in the program.

Creating Database Projects with SQL Anywhere Studio, Part 2

Creating Database Projects with SQL Anywhere Studio, Part 2

The next screen is important because it deals with the permitted role of the user and his access to resources. When in doubt, accept defaults.

Creating Database Projects with SQL Anywhere Studio, Part 2

When you click Finish, the user “mysorian” will be created, as you can see in the next picture. User “mysorian” does not yet belong to any group, but he has the same credentials as the DBA.

Creating Database Projects with SQL Anywhere Studio, Part 2

{mospagebreak title=Integrated Login}

Logging into a server, for example, an SQL 2000 Server, requires authentication. There are two kinds of authentication: Windows authentication (trusted connection) and SQL Server authentication(from non-trusted clients). Similarly in Adaptive Server Anywhere, there is what is called an Integrated login; that is, the login that is mapped to the user profile of an existing user (who may fullfil a specific role).

In order to create an Integrated login, right click on the “Integrated Login” node in the left frame of Sybase Central. If there are existing integrated logins, they will be seen on the list. A new one can be created by right clicking in an empty area in the list view, and picking up “new.” This wakes up the Integrated Login Wizard, as shown in the next picture.

Creating Database Projects with SQL Anywhere Studio, Part 2

In order for the ASPNET application to access the database, this application should have an integrated login. This is made possible by choosing for ASPNET to have an integrated login as shown in the next picture. Further, it has been allowed to assume the DBA role. In production environments, a lot more care is necessary when making these choices.

Creating Database Projects with SQL Anywhere Studio, Part 2

Clicking on the NEXT button in the above picture takes you to the final dialogue, which is, what kind of login do you wish to confer upon this user? In this case, mixed login mode has been chosen. When the FINISH button is clicked, one should see the ASPNET user in the Integrated login list view, as shown in the following picture. Integrated login will help with logging in once for both the application and the data base.

Creating Database Projects with SQL Anywhere Studio, Part 2

Creating Database Projects with SQL Anywhere Studio, Part 2

{mospagebreak title=Importing ASA data into an Excel worksheet}

Microsoft Excel is a widely used, extremely popular spreadsheet program. Financial experts love it because of its integration with other Office products, charting, underlying VBA, and number crunching power among others. Before you can connect to the database, it is necessary to prepare a File Data Source Name. In order to prepare this, open a Notepad application (albeit empty) and save it as, for example, Asa.udl. The application (Notepad) will warn you that it may be unusable if the extension is changed, but ignore this objection. Now right click on this icon,

Creating Database Projects with SQL Anywhere Studio, Part 2

to bring up the Data Link Dialogue. Make sure that under Providers, MS OLEDB PROVIDER FOR ODBC DRIVERS is chosen. For Use Data Source Name choice type in “ASA 9.0 Sample”, use dba for User Name and sql for Password. You may test this connection.

Creating Database Projects with SQL Anywhere Studio, Part 2

Click open MS Excel application (Excel 2002 SP1 in this example), and in the Data menu item, click to show the drop down items. Under Import External Data pick up Import Data…

Creating Database Projects with SQL Anywhere Studio, Part 2

This brings up a file open dialogue and you may browse to find the Asa.udl that was created earlier (in this example it was saved in a folder called Excel; it depends on where it is saved). Highlight this and click Open.

Creating Database Projects with SQL Anywhere Studio, Part 2

As was saw in Part 1, this database had a number of tables. The table list from the database now pops up, where you can select the table you want to bring in to Excel.

Creating Database Projects with SQL Anywhere Studio, Part 2

As soon as you pick a table, you will be asked the location where it may be displayed. You can choose a location. In this screen shot, it starts from first cell (default) in the worksheet.

Creating Database Projects with SQL Anywhere Studio, Part 2

If you click on the Properties button in the above-imaged screen, you can access the External Data Range properties. You can choose formatting options; refresh the background; save query definitions and/or passwords; and so on.

Creating Database Projects with SQL Anywhere Studio, Part 2

Now the table data is nicely positioned in the Excel Worksheet. From here it can be processed within Excel as shown in the following picture, where autosort is turned on.

Creating Database Projects with SQL Anywhere Studio, Part 2

Creating Database Projects with SQL Anywhere Studio, Part 2

{mospagebreak title=Creating a VB application}

There are several ways to create a data project within VB 6.0. The method demonstrated here is one of rapidly prototyping the application. Start Adaptive Server Anywhere as discussed earlier (double clicking the shortcut in the Start->Programs). Start a vb Standard Exe project. From main menu->Project, click and select AddDataEnvironment. This adds an icon DataEnvironment1 to the DataEnvironment objects pane and an item(Connection1) to the Project folder as shown in the next two pictures.

Creating Database Projects with SQL Anywhere Studio, Part 2

Creating Database Projects with SQL Anywhere Studio, Part 2

Right Click the Connection1 in the Designer’s pane and view its properties. It opens up the Data Link Properties with Microsoft OLEDB for ODBC as the provider.

Creating Database Projects with SQL Anywhere Studio, Part 2

In the Connection tab of the Data Link properties, you should be able to pick up Asa 9.0 Sample; for the authentication, use dba for User Name and sql for password. Test the connection.

Creating Database Projects with SQL Anywhere Studio, Part 2

Creating Database Projects with SQL Anywhere Studio, Part 2

The connection string can now be seen in the Connection1’s property window, reproduced here: Provider=MSDASQL.1;Persist Security Info=False;User ID=dba;Data Source=ASA 9.0 Sample

Now right click the Connection1 in the Designer’s plane and pick AddCommand. This adds a default Command1 (you may rename most of the defaults).

Creating Database Projects with SQL Anywhere Studio, Part 2

Right click the Command1 added, to view its properties. It opens up Command1’s properties page window with six tabs: General, Parameters, Relation, Grouping, Aggregates, and Advanced. The Connection text box will be showing Connection1. The Command Name text box will be showing the Command1 you are trying to set up.

For the Source of Data object’s drop down list, you have couple of choices: Stored Procedures, Table, View, and Synonym. For the chosen database object the next drop-down shows the object Name. Should your choice be Tables, the drop down shows all the tables, etc. If you want to write your own SQL statement, then you choose the SQL Statement Radio button. From here on, either you can type in the statement, or build the statement using the query builder, a builder that can be accessed from within VB. In this particular case, since this article is not a tutorial on how to build queries or create a complete application, a table database object is chosen for simplicity.

Creating Database Projects with SQL Anywhere Studio, Part 2

Finally, highlight the Command1 object in the designer’s plane and drag it on to form1. The whole table with all the columns will be placed on the form.

Creating Database Projects with SQL Anywhere Studio, Part 2

Now add a button to the bottom of this form with Caption as “Next”, and Name as cmdNext. To the click event of the cmdNext button add the following code:

Private Sub cmdNext_Click()
If Not DataEnvironment1.rsCommand1.EOF Then
DataEnvironment1.rsCommand1.MoveNext
End If
End Sub

Now save the project and run. With this, the rows in the table can be moved in the “Next” direction. You could add other buttons to create a navigation set, or you can add buttons and code to manipulate the data in the database. Since this is only an example of connecting to Adaptive Server Anywhere, only the steps necessary to create a single movement are shown.

Creating Database Projects with SQL Anywhere Studio, Part 2

{mospagebreak title=An example of ADO.NET application}

Again a rapid application path will be chosen to demonstrate working with Adapter Server Anywhere. The first thing is to start the Database server as discussed previously. Next start an ASP.NET Web application by adding a Web form (default WebForm1.aspx). If you do not see the data controls that were automatically added when iAnywhere software was installed as shown in the next picture,

Creating Database Projects with SQL Anywhere Studio, Part 2

you must then right click on the Data tab in the Toolbox and select Add/remove items from the pop-up menu. This will open a dialogue where you can add these items as shown here.

Creating Database Projects with SQL Anywhere Studio, Part 2

Now drag the AsaConnection object to the WebForm1’s design plane. This will add an AsaConnection1 control to the tray below the design plane as shown. Also observe the automatic entries in the properties window.

Creating Database Projects with SQL Anywhere Studio, Part 2

Now drag an AsaCommand control from the Toolbox to the design plane of WebForm1. aspx. This will add an instance AsaCommand1 of asaCommand object.

Creating Database Projects with SQL Anywhere Studio, Part 2

In the above dialog, right click on the CommandText, which opens the following Command Text builder tool. You can enter the SQL Statement here as shown. You may execute the statement and see the results. Click OK to this, and the command will execute when called against the database using this statement

Creating Database Projects with SQL Anywhere Studio, Part 2

Now add a command button which has the following text in this example, “Get Customers from MN.” Add also a DataGrid from Toolbox to the design plane to display the results. To the click event of this button, add the following code.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.
EventArgs) Handles Button1.Click
AsaConnection1.Open()
‘give full reference to object
Dim dr As iAnywhere.Data.AsaClient.AsaDataReader
dr = AsaCommand1.ExecuteReader
DataGrid1.DataSource = dr
DataGrid1.DataBind()
End Sub

Creating Database Projects with SQL Anywhere Studio, Part 2

Build the project, right click webForm1.aspx, and select View in Browser. If everything is working as it should, the following output may be seen in the browser.

Creating Database Projects with SQL Anywhere Studio, Part 2

Summary

Easy to use Adaptive Server Anywhere adapts itself (no pun intended) extremly well, and integrates well with Microsoft products Excel, VB 6.0, and VB.Net. The server software supports the creation of database objects by providing the necessary hooks in terms of design time controls, once the proper reference is established. Although extremely simple cases of database projects were considered, it should be relatively easy to create projects which manipulate data on the server.

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