Work with Informix Dynamic Server V11.10 and IBM Database Add-ins for Visual Studio 2005 V9.5

Rapidly develop Windows and Web applications for Visual Studio 2005, create database objects, and much more while connecting to IDS V11.10

The IBM® Database Add-ins for Visual Studio 2005 allow users working in the Visual Studio 2005 environment to connect to several IBM databases and perform rapid application development, as well as several database operations. The latest release of IBM Database Add-ins for Visual Studio 2005 V9.5 now includes support to connect and work with IBM Informix® Dynamic Server V11.10. Get an overview of all the features that have been added to IBM Database Add-ins for Visual Studio 2005 V9.5 to work with Informix Dynamic Server V11.10.

Share:

Vasantha Jayakumar (vasantha@us.ibm.com), Advisory Software Engineer, IBM, Software Group

Vasantha Jayakumar photoVasantha Jayakumar is an advisory software engineer at the IBM Silicon Valley Lab in San Jose, CA. Her expertise and focus in the last few years have been on data management and application development tools for various IBM servers including DB2 for Linux, UNIX, and Windows, z/OS, iSeries, and IDS, with special focus on the Microsoft .NET platform. More recently, she leads the partner integration efforts for IBM WebSphere DataStage.



18 October 2007

Also available in Chinese

Introduction and key feature highlights

While the IBM Database Add-ins for Visual Studio 2005 supported connecting and working with different IBM database server, it has been limited to DB2® for Linux®, UNIX®, and Windows®, DB2 for iSeries™, and DB2 for z/OS®. Now the latest version, IBM Database Add-ins for Visual Studio V9.5, includes support to connect and work with Informix Dynamic Servers (IDS), Version 11.10. Therefore, IDS users working on the Visual Studio 2005 environment can now experience a truly integrated environment and work with a rich set of IDS-specific features.

Some of the key features now supported for IDS V11.10 allow you to:

  • Integrate with Microsoft Server Explorer: Add a data connection to an IDS server using the Microsoft Server Explorer. The data connection lets you browse the database objects and its properties.
  • Develop Windows applications: Build Windows applications for IDS using standard drag-and-drop mechanisms, as supported by the Visual Studio 2005 environment.
  • Develop Web sites: Build Web sites for IDS using standard Visual Studio 2005 functionalities.
  • Create and delete of database objects: Non-modal designers are provided for creating several types of database objects, as listed below. Non-modal designers allow you the flexibility to simultaneously work on designing several database objects. Deletion of these objects is also supported.
    • Create and delete tables
    • Create and delete views
    • Create and delete stored procedures
    • Create and delete user defined functions
    • Create and delete triggers
    • Create and delete indexes
    • Create and delete user privileges for the database objects
  • View and update data for tables and views: Show as well as update data for tables and views.
  • Execute procedures and functions: Execute stored procedures and functions, and view the returned result sets.
  • Create and delete of IDS databases: Create and delete IDS databases directly from the Visual Studio 2005 IDE.
  • View and execute scripts: View the create scripts/DDL for database objects, and execute arbitrary scripts on the database.

Installation and requirements

This article assumes that you are accessing the Informix Dynamic Server, Version 11.10. You need the IBM Database Add-Ins for Visual Studio 2005 V9.5, which you can get when you download the IBM Data Server Client V9.5 or the DB2 Client V9.5. Get more information on IDS for .NET and download the IBM Database Add-ins for Visual Studio 2005 V9.5.


Adding and customizing data connections

In Visual Studio 2005 IDE, the Server Explorer lists the data connections that have been added. You can also add data connections to Informix Dynamic Server V11.10 using the Add Connection… context menu. Invoking this menu brings up the Add Connection dialog, as shown in Figure 1. The Add Connection dialog is specific to the IBM Database Add-ins and allows the following customizations:

  • An option to specify a schema filter that uses the LIKE search criteria. The schema filter applies to all the folders in the tree, and hence only objects belonging to this schema would be displayed in the tree.
  • An option to exclude system schemas.
  • Apart from the schema filter, a second level of filtration can be achieved by specifying folder filters for each of the object folders.
  • An option to includue/exclude the different folders in the tree.
  • An option to automatically refresh the connection when the Visual Studio 2005 IDE is loaded.
  • An option to always run procedures to discover result sets.
  • An option to persist the password across Visual Studio 2005 sessions.
  • An option to test the connection using the Test Connection button.
Figure 1. Add Connection dialog
Add Connection dialog

The connection string is automatically generated. To see a read-only view of the connection string in the Advanced Properties dialog, select the Advanced button in the Add Connection Dialog. Once a connection has been created, a new node for the connection is created and displayed in the Server Explorer. Expand the connection node to display the various nodes for the database objects (tables, views, functions, procedures) depending on the folder filters that were specified. Each of the folders can be further expanded to view an enumeration of the contained database objects. The properties window in the IDE displays the properties for the object that is in focus.

You can optionally modify an existing data connection using the Modify Connection... context menu, which invokes the same Add Connection dialog pre-filled with the connection information.


Developing Windows applications

Application developers using the Visual Studio 2005 IDE can easily build their Windows applications and connect to IDS data sources. Visual Studio 2005 uses the concept of configuring and creating a Data Source to be used by the applications. You can create a simple Windows application for IDS V11.10 using the following steps:

  1. Create a connection to the IDS database and then choose to create a Windows application -- select File > New > Project. You can choose to create a C# application.
  2. Create a data source for the application using the Data > Add New Data Source… top-level menu. Invoking this menu launches the data source configuration wizard provided by the Visual Studio IDE. This wizard (as shown in Figure 2) allows you to select the connection to work with and the data objects to be included in the data source. Once the wizard is completed, a data source node is created for each of the selected objects in the Data Sources window. To invoke the data sources window, select the top-level menu Data > Show Data Sources.
    Figure 2. Data source configuration wizard
    Data source configuration wizard
  3. To design the Windows application, drag and drop the data source node from the Data Sources Windows onto the Windows form. This causes a default user interface, which has a tabular format to be automatically created and also generates the necessary code to interact with the user interface.
    Figure 3. Designing the Windows application
    Designing the Windows application
  4. Run the Windows application. Notice that no coding is needed to create and run this application. Running the application displays the data from the data source in the form, and allows you to browse through the records and modify the data as needed.
    Figure 4. Running the Windows application
    Running the Windows application

Developing Web sites

Similar to building Windows applications, you can also build Web sites connecting to IDS V11.10 data sources using a few simple steps:

  1. To create a new ASP.NET Web site, select File > New Web Site.
  2. Open the Web site in design mode using the View Designer context menu for the default.aspx file that has been created for the Web site.
  3. To add the SqlDataSource to the designer, open the Data section in the toolbox window, as shown in Figure 5, and drag the SqlDataSource onto the open designer.
    Figure 5. Add data source to the Web site designer
    Add data source to the Web site designer
  4. To customize the data source, right-click on SqlDataSource in the designer, and choose Customize Data Source. In the wizard that is displayed, select the connection, the data objects, and the columns to be used for the data source. To specify an option to generate insert, update, and delete statements, click on the Advanced... button.
    Figure 6. Customize the data source
    Customize the data source
  5. To bind a user interface to the data source, drag and drop a user-interface control, such as a DataView control, from the toolbox onto the designer. Right-click on the DataView control and, from the drop-down list for Choose Data Source, select the data source that you created and configured in the previous step. In the context menus for the DataView, check the boxes for Enable Editing and Enable Deleting, as shown in Figure 7 below.
    Figure 7. Bind the user interface, and enable editing and deleting
    Bind the user interface, and enable editing and deleting

    From the toolbox, you can also drag and drop the DetailsView control on the dataset designer. For the DetailsView, select SqlDataSource1 from the list of data sources. Also check the boxes for Enable inserting to be able to insert data using the details view.

  6. To run the Web site, select the top-level menu Debug > Start without Debugging. Running the Web site allows you to browse the data as well as insert, update, or delete data from the data source through the Web site.
    Figure 8. Running the Web site
    Running the Web site

Database objects for Informix Dynamic Server

The IBM Database Add-ins for Visual Studio 2005 V9.5 provides several IBM Designers that have been customized to create and work with the various IDS database objects such as tables, views, stored procedures, functions, triggers, and indexes. The designers, unlike wizards, are non-modal and hence allow you the flexibility to simultaneously work on designing different database objects. The designers provide an aggregated view of an object and all its related objects. Each designer has one or more views that expose a part of the functionality available on the designer. A toolbar is associated with each designer, and you can use either the buttons in the toolbar or the top-level menus o switch between the various views of the designer. Common views, such as Show Script and Privileges View, are available on all the designers to provide some of the common functionality, and to maintain a uniform look and feel across all the designers. When you have completed designing the database object and are ready to save your changes, you can simply select File > Save, which triggers the propagation of the changes to the database.

In general, the UI is designed to adapt dynamically based on the type of data connection. Therefore, IDS V11.10 connections, certain fields and properties that are specific to IDS databases are displayed, while irrelevant ones are either hidden or disabled.

Once you have completed entering all the necessary information for defining the object, click on the Save icon or menu to save the changes, which causes the script to be compiled against the respective database and the object to be created on the database. The Output View is used to display status messages indicating successful execution of the script or errors.

The IBM Database Add-ins for Visual Studio 2005 V9.5 allows you to create the following database objects for IDS V11.10:

  • Tables
  • Views
  • Stored procedures
  • Functions
  • Indexes
  • Triggers

You can also set privileges for each of these objects. Each designer is discussed in detail in the sections that follow.


Creating and deleting tables in IDS

The table designer is a non-modal, graphical interface used to specify information to define a table. To invoke it, right-click the Tables node in the Server Explorer, and select the menu Add New Table with Designer. The table designer provides several views that allow you to specify the definitions for the table and its related objects. Defining a table object for IDS is mostly similar to defining a table object for some of the other IBM database flavors supported by the IBM Database Add-ins. Fields and properties specific to IDS databases are included in this designer to fully support the creation of tables for IDS.

  • The columns view allows you to define the table attributes and columns in a table. Figure 9 shows a screenshot of the columns view for an IDS connection. Notice that several fields displayed in this designer are specific to IDS connections, and the data types listed in this view are Informix-specific data types. The properties section in this view displays the properties for the selected column in the columns view, and certain properties will be enabled or disabled based on the data type of the selected column. Columns may also be imported from existing tables in the database using the Import button.
    Figure 9. Table designer – Columns view
    Table designer - Columns view
  • The keys view allows you to define primary, unique, and foreign keys for the table. For primary and unique keys, only the "not null" columns are listed and can be selected to define these keys. To ease the process of defining foreign keys, the following are provided:
    • A lookup on tables, based on the schema
    • A grid that allows the mapping of the columns in the current table to the primary key columns in the mapped table
    Figure 10. Table designer – Keys view
    Table designer - Keys view
  • The indexes view allow you to create indexes for the table by selecting from the list of columns defined for the table.
    Figure 11. Table designer – Indexes view
    Table designer - Indexes view
  • The check constraints view allows you to create table check constraints for the table. The SQL editor control that contains intellisense capabilities is provided to edit the check constraint expression.
    Figure 12. Table designer – Check constraints view
    Table designer - Check constraints view
  • The triggers view allows you to define triggers on the table. The trigger view allows you to specify the type of trigger, the trigger text, and several other trigger properties, as shown in Figure 13 below. The SQL editor is provided to edit the trigger text and, for ease of use, it is pre-filled with a skeleton SQL to create a trigger. For an IDS connection, the skeleton SQL pertains to the IDS syntax.
    Figure 13. Table designer – Triggers view
    Table designer - Triggers view
  • The privileges view in this designer allow you to assign privileges on a table to a user or a role. This view is one of the common views across the table, view, stored procedure, and function designers.
    • The privileges section contains two grids: one to specify the list of users, and one to specify the user privileges of the selected user. The type of user can either be a user or a role. The user privileges that can be assigned are specific to the type of object. Each of the privileges can be assigned a value such as yes, no, or grant. When yes is selected, a grant statement is generated; when no is selected, a revoke statement is generated; and when grant is selected, a grant statement with grant option is generated in the script. The grant all, allow all, and revoke all buttons are used to set all the values in the grid to grant, yes, or no, respectively.
    • The roles section is specific to IDS and consists of a grid where a role can be defined. Once a role is added, the script generated will contain a create role statement.
    Figure 14 provides a screenshot of this view in the table designer:
    Figure 14. Table designer – Privileges view
    Table designer – Privileges view
  • The scripts view provides a read-only view of the script that is generated for the object based on the information entered in all the other views. In addition, it provides options to enter a script header and a script trailer, which would be run before and after the script execution respectively. The scripts view is also a common view across all the designers for the database objects. A screenshot of this view is shown in Figure 15:
    Figure 15. Table designer – Scripts view
    Table designer - Scripts view

Once you have completed entering all the necessary information for defining the object, you can save the changes using the Save icon or menu, which executes the script and creates the table object on the database.

To delete tables from the Server Explorer tree, right-click on the appropriate table node in the tree, and choose the Delete option. Once the deletion has completed successfully, the node is removed from the tree and a status message is displayed in the output view in the IDE.


Creating and deleting views in IDS

The view designer is a non-modal, graphical interface used to specify information to define a view. To invoke it, right-click on the Views node in Server Explorer, and select the Add New View with Designer option. Defining a view object for IDS is very similar to defining a view object for some of the other IBM database flavors support by the IBM Database Add-ins. The view designer contains the following views to define a view object:

  • The 'view' view provides the SQL editor control to enter the SQL statement defining the view. Options are provided to check the syntax of the SQL defining the view as well as to test the execution of this statement. All test executions are rolled back. If the SQL execution returns results, the following, illustrated in Figure 16, is displayed in a results grid.
    Figure 16. View designer – Main view
    View designer - Main view
  • The triggers view allows you to define triggers on the view. This view is the same view that is also included in the Table designer (see Figure 13). The trigger view allows you to specify the type of trigger, the trigger text, and several other trigger properties. The SQL editor is provided to edit the trigger text and, for ease of use, it is pre-filled with a skeleton SQL to create a trigger. For an IDS connection, the skeleton SQL pertains to the IDS syntax.
  • The privileges view in this designer allows you to assign privileges on a table to a user or a role. This view is one of the common views across the table, view, stored procedure, and function designers.
    • The privileges section contains two grids: one to specify the list of users, and one to specify the user privileges of the selected user. The type of user can either be a user or a role. The user privileges that can be assigned are specific to the type of object. Each of the privileges can be assigned a value such as yes, no, or grant. When yes is selected, a grant statement is generated, when no is selected, a revoke statement is generated; and when grant is selected, a grant statement with grant option is generated in the script. The grant all, allow all, and revoke all buttons are used to set all the values in the grid to grant, yes, or no, respectively.
    • The roles section is specific to IDS and consists of a grid where a role can be defined. Once a role is added, the script generated will contain a create role statement. This view is similar to the view shown in Figure 14.
  • The scripts view provides a read-only view of the script that is generated for the object based on the information entered in all the other views. In addition, it provides options to enter a script header and a script trailer, which would be run before and after the script execution, respectively. The scripts view is also a common view across all the designers for the database objects. Figure 17 provides a screenshot containing the script for the view object:
    Figure 17. View designer – Script view
    View designer - Script view

Once you have completed entering all the necessary information for defining the object, you can select the Save icon or menu to save your changes, which executes the script and creates the view object on the database.

To delete the views from the Server Explorer tree, right-click on the appropriate view node in the tree, and select the Delete option. Once the deletion has completed successfully, the node is removed from the tree and a status message is displayed in the output view in the IDE.


Creating and deleting stored procedures in IDS

The stored procedure designer is a non-modal, graphical interface used to specify information to define a stored procedure. To invoke the stored procedure designer, right-click on the Procedures node in Server Explorer and select the menu option Add New Procedure with Designer. Defining a stored procedure object for IDS is slightly different from defining a stored procedure object for some of the other IBM database flavors supported by the IBM Database Add-ins. Fields and properties specific to IDS databases are included in this designer to fully support the creation of stored procedures for IDS. The stored procedure designer contains the following views to define the stored procedure:

  • The procedure view allows you to define procedure attribute, parameters, and its properties, the actual SQL body of the stored procedure, as well as the return types for the procedure.
    Figure 18. Procedure designer – Procedure definition
    Procedure designer – Procedure definition
    • Procedure definition section: The procedure definition section allows you to define the following procedure attributes for IDS:
      • Procedure name: Name of the stored procedure.
      • Owner name: Owner of the stored procedure.
      • Specific name: A specific name that uniquely identifies the stored procedure.
      • Comment: A string that typically contains a description of the stored procedure.
      • Routine modifier: Can be specified as VARIANT or NOT VARIANT. It is used to generate the WITH VARIANT or WITH NOT VARIANT clause. An SPL function is variant by default. If the WITH NOT VARIANT clause is specified, then the procedure cannot contain any SQL statements.
      • REFERENCING clause: This clause can be used to define a trigger routine for a table object whose FOR EACH ROW actions can invoke the trigger routine. The ellipsis (…) button invokes the referencing clause dialog. The values entered in this dialog are used to construct the REFERENCING clause. For example, for the values entered in the screenshot in Figure 19, the following clause would be generated – "REFERENCING OLD AS O NEW AS N FOR INFORMIX.EMPLOYEE."
        Figure 19. Declare referencing clause dialog definition
        Declare referencing clause dialog
      • Listing file name: The listing file name is used to generate the WITH LISTING IN clause for the stored procedure. The WITH LISTING IN option allows you to direct any compile-time warnings that might occur on a file on the database server. You can use this field to specify the file location that will record the compile-time warnings.
    • Procedure parameters section: The procedure parameters section can be used to define one or more parameters for the stored procedure and specify the data type, mode, and other parameter properties. Parameters can also be imported from existing tables in the database using the import button. On importing a parameter, the data type of the parameter is created to be the same as the data type of the column that the parameter is associated with. Apart from the various attributes that can be specified for a parameter, for IDS, a default value for the parameters can also be specified.
      Figure 20. Procedure designer – Procedure parameters
      Procedure designer – Procedure parameters
      The drop-down lists for data types for parameters are pre-filled with an IDS-specific list of data types. The data type of a parameter can be set using the data type style property, which can be set to SQL data type, LIKE or REFERENCES. Based on the selection, certain properties will be enabled or disabled in the parameter properties section. The default is set to SQL data type. If the data type style is set to:
      1. SQL data type, that indicates that the data type for the parameter will be specified using the SQL data type and selected using the type field in the parameter properties list.
      2. LIKE, that indicates that the data type for the parameter will be specified using the LIKE clause and specified using the parameter properties in the LIKE clause section, as shown below.
      3. REFERENCES, that indicates that the data type for the parameter will be specified using the REFERENCES clause and specified using the parameter properties in the REFERENCES clause section.
    • SQL body section: The SQL body section provides the SQL editor control to enter the SQL body for the stored procedure. For ease of use, a default SQL script for creating a procedure is displayed initially in the procedure body. You have the option to edit the SQL body and can optionally embed any SPL statements such as DEFINE, ON EXCEPTIONS, and so on to be SQL body.
      Figure 21. Procedure designer – Procedure SQL body
      Procedure designer – Procedure SQL body
  • The privileges view in this designer allows you to assign privileges on a stored procedure to a user or a role. This view is one of the common views across the table, view, stored procedure, and function designers.
    • The privileges section contains two grids: one to specify the list of users, and one to specify the user privileges of the selected user. The type of user can either be a user or a role. The user privileges that can be assigned are specific to the type of object. Each of the privileges can be assigned a value such as yes, no, or grant. When yes is selected, a grant statement is generated; when no is selected, a revoke statement is generated; and when grant is selected, a grant statement with grant option is generated in the script. The grant all, allow all, and revoke all buttons are used to set all the values in the grid to grant, yes, or no, respectively.
    • The roles section is specific to IDS and consists of a grid where a role can be defined. Once a role is added, the script generated will contain a create role statement. Figure 22 provides a screenshot of this view in this designer:
      Figure 22. Procedure designer – Privileges view
      Procedure designer – Privileges view
  • The scripts view provides a read-only view of the script that is generated for the object based on the information entered in all the other views. In addition, it provides options to enter a script header and a script trailer, which would be run before and after the script execution, respectively. The scripts view is also a common view across all the designers for the database objects. Figure 23 provides a screenshot of this view:
    Figure 23. Procedure designer – Scripts view
    Procedure designer – Scripts view

Once you have completed entering all the necessary information for defining the object, you can use the Save icon or menu to save the changes, which executes the script and creates the stored procedure object on the database.

To delete stored procedures from the server explorer tree, right-click on the appropriate stored procedure node in the tree, and select the Delete menu option. Once the deletion has completed successfully, the node is removed from the tree and a status message is displayed in the output view in the IDE.


Creating and deleting functions in IDS

The function designer is a non-modal, graphical interface used to specify information to define a function. To invoke it, right-click on the Functions node in Server Explorer, and select the menu option Add New Function with Designer. Defining a function object for IDS is slightly different from defining a function object for some of the other IBM database flavors supported by the IBM Database Add-ins. Fields and properties specific to IDS databases are included in this designer to fully support the creation of functions for IDS. The function designer contains the following views to define the function:

  • The function view allows you to define function attributes, function parameters, and its properties, the actual SQL body of the function as well as the return types for the function.
    • Function definition section: The function definition section allows you to define the following function attributes for IDS:
      • Function name: Name of the function.
      • Owner name: Owner of the function.
      • Specific name: A specific name that uniquely identifies the function.
      • Comment: A string that typically contains a description of the function.
      • Routine Modifier: Can be specified as VARIANT or NOT VARIANT. It is used to generate the WITH VARIANT or WITH NOT VARIANT clause. An SPL function is variant by default. If WITH NOT VARIANT clause is specified, then the function cannot contain any SQL statements.
      • REFERENCING clause: This clause can be used to define a trigger routine for a table object whose FOR EACH ROW actions can invoke the trigger routine. The ellipsis (…) button invokes the referencing clause dialog. The values entered in this dialog will be used to construct the REFERENCING clause. For example, for the values entered in the screenshot in Figure 24, the following clause would be generated – "REFERENCING OLD AS O NEW AS N FOR INFORMIX.EMPLOYEE." A screenshot of this dialog is as shown in Figure 19.
      • Listing file name: The listing file name is used to generate the WITH LISTING IN clause for the function. The WITH LISTING IN option allows you to direct any compile-time warnings that might occur to a file on the database server. You can use this field to specify the file location that will record the compile-time warnings.
        Figure 24. Function designer – Function definition
        Function designer – Function definition
    • Function parameters section: The function parameters section may be used to define one or more parameters for the function and specify the data type, mode, and other parameter properties. Parameters may also be imported from existing tables in the database using the Import button. On importing a parameter, the data type of the parameter is created to be the same as the data type of the column that the parameter is associated with. Apart from the various attributes that can be specified for a parameter, for IDS, a default value for the parameters can also be specified.
      Figure 25. Function designer – Function parameters section
      Function designer – Function parameters section
      The drop-down lists for data types for parameters are pre-filled with an IDS-specific list of data types. The data type of a parameter can be set using the data type style property, which can be set to SQL data type, LIKE or REFERENCES. Based on the selection, certain properties will be enabled or disabled in the parameter properties section. The default is set to SQL data type. If the data type style is set to:
      1. SQL data type, that indicates that the data type for the parameter will be specified using the SQL data type and selected using the type field in the parameter properties list.
      2. LIKE, that indicates that the data type for the parameter will be specified using the LIKE clause and specified using the parameter properties in the LIKE clause section, as shown below.
      3. REFERENCES, that indicates that the data type for the parameter will be specified using the REFERENCES clause and specified using the parameter properties in the REFERENCES clause section, as shown below.
    • Function SQL body section: The SQL body section provides the SQL editor control to enter the SQL body for the function. For ease of use, a default SQL script for creating a function is displayed initially in the function body. You have the option to edit the SQL body and can optionally embed any SPL statements such as DEFINE, ON EXCEPTIONS, and so on to the SQL body.
      Figure 26. Function designer – Function SQL body section
      Function designer – Function SQL body section
    • Function return type section: The function return type section allows you to define one or more data types, columns, or both that the function returns, depending on the SQL type specified in the body of the function. The following properties can be specified for the returned columns:
      • Name: Specifies the name of the returned column
      • Type: Specifies the data type of the returned column
      • Length: Specifies the length of the data type
      • Precision: Specified in the case of DECIMAL or FLOAT data types
      • Scale: Specified for DECIMAL data type
      • Units: Specified for BLOB, CLOB, and DBCLOB data types
      Figure 27. Function designer – Return type section
      Function designer – Return type section

      The data type for the function return type can be specified directly or using the REFERENCES clause. Also, it is optional to label returned parameters. However, if any returned value of the function has a name, then all must have names.

  • The privileges view in this designer allows you to assign privileges on a function to a user or a role. This view is one of the common views across the table, view, stored procedure, and function designers.
    • The privileges section contains two grids: one to specify the list of users, and one to specify the user privileges of the selected user. The type of user can either be a user or a role. The user privileges that can be assigned are specific to the type of object. Each of the privileges can be assigned a value such as yes, no, or grant. When yes is selected, a grant statement is generated; when no is selected, a revoke statement is generated; and when grant is selected, a grant statement with grant option is generated in the script. The grant all, allow all, and revoke all buttons are used to set all the values in the grid to grant, yes, or no, respectively.
    • The roles section is specific to IDS and consists of a grid where a role can be defined. Once a role is added, the script generated will contain a create role statement.
    A screenshot of this view in this designer is similar to the screen shot shown in Figure 22.
  • The scripts view provides a read-only view of the script that is generated for the object based on the information entered in all the other views. In addition, it provides options to enter a script header and a script trailer, which would be run before and after the script execution, respectively. The scripts view is also a common view across all the designers for the database objects. Figure 28, below, provides a screen shot of this view:
    Figure 28. Procedure designer – Scripts view
    Procedure designer – Scripts view

Once you have completed entering all the necessary information for defining the object, you can use the Save icon or menu to save the changes, which executes the script and creates the stored procedure object on the database.

To delete stored procedures from the server explorer tree, right-click on the appropriate stored procedure node in the tree, and select the Delete menu option. Once the deletion has completed successfully, the node is removed from the tree and a status message is displayed in the output view in the IDE.


Cloning database objects

In cases where you would like to create objects that are mostly similar to each other but differ slightly in functionality, it would be helpful to be able to start with a copy of the definition of an object and then modify it to define the new object. In other cases, you might want to make a copy of the object definition to store and use as a backup while you continue to modify the definition of the object. Hence, the IBM Database Add-ins supports the ability to clone an object definition. You can clone objects such as tables, views, stored procedures, and functions using the Clone Definition context menu that is available on these nodes in the Server Explorer tree. Cloning an object displays the designer for that object, which is pre-filled with the object details from the original object. The object names are renamed for uniqueness, as necessary, by appending a number to the original object name. You can choose to modify the definition as needed and then save the new definition to create the new object in the database.

Figure 29. Clone definition
Clone definition

Viewing data for tables and views

You can view, insert, update, or delete data for tables and views using the Show Data menu option that is available on the table, and view nodes in the Microsoft Server Explorer tree. Invoking this menu displays the Data Designer, as shown Figure 30. The data designer, by default, shows data for the selected table or view and limits the number of rows to 100. You can customize the data displayed by setting several options that are available in the designer.

Figure 30. Data designer
Data designer

The Data designer allows the following customizations:

  • The option to include or exclude certain columns from the data displayed for the table or view. The default filter option is to make all columns visible.
  • The option to specify the default number of rows of data that you would like to show. The default is set to display 100 rows of data. Being able to restrict the number of rows of data retrieved would be particularly helpful in the case of large tables that contain large amounts of data.
  • The option of a column alias and the sort order of the columns to be specified.
  • The option of column filtration based on SQL conditions that can be entered using the SQL editor control.
  • The option to import data from files as well as export data to files. The file formats supported for import and export of data are XML, comma-delimited, and pipe-delimited formats.

To provide ease of use, you can select the File > Save menu option to save the changes to the database.


Executing stored procedures and functions

To execute a stored procedure or a function, right-click on the appropriate node in the Server Explorer tree, and select the Execute menu option. Choosing this menu launches the run options dialog, as shown in Figure 31, below.

The run options dialogs allows you to set the following options:

  • Commit or rollback changes after executing the procedure or function.
  • Specify pre-run and post-run scripts that will be run before and after the procedure or function is executed, respectively. The SQL editor control that provides syntax colorization and intellisense is provided to enter these scripts. The SQL editor control also provides right-click menu options to load the script from a file or to save the script to a file.
  • Persist parameter information provides the list of parameters for which the values can be entered for each procedure or function, and these values are persisted. Therefore, the next time you choose to run the same procedure or function, the parameter values are pre-filled with values from the previous run of that particular procedure or function. This provides ease of use and avoids the need for you to re-enter the values during each run of the procedure or function.
Figure 31. Run options dialog
Run options dialog

After the procedure or function is executed, any result sets returned are displayed in a new instance of the data designer.


Viewing and executing scripts for database objects

For objects such as tables, views, procedures, and functions that already exist in the database, you can view the create script or DDL that was used to create the object. The script designer is used to display the scripts. To invoke it, use the Show Script context menu option that is available on the table, view, stored procedure, and function nodes in the Server Explorer tree. When invoked from a particular object node, it displays the DDL used to create that particular object. Note that the script displayed is editable. Hence, in addition to viewing the script, you can also choose to save, modify, and execute it. In the case that the script returns results, the results are displayed in the result data section of the designer

Figure 32. Script designer
Script designer

Executing scripts against the database from within the Visual Studio 2005 IDE

You can also invoke the script designer as a standalone instance without any relation to a specific database object using the New Script menu option that is available on the context menu for the data connection node in the Server Explorer tree. In the standalone instance, the script designer is displayed with a blank script, hence allowing you to specify your own script that you would like to execute on the database. This further allows you to enter any arbitrary script and execute it against the respective database from within the Visual Studio 2005 IDE.

Figure 33. New script menu option
New script menu option

You can also use he Visual Studio Query Builder to generate the script. To invoke it, use the Query Builder context menu option on the editor control. On closing the Query Builder, the SQL code in the Query Builder is automatically pasted in the editor at the insertion point.


Creating and dropping an IDS database

You can create or drop an IDS database directly from the Microsoft Server Explorer tree using context menus available on the Data Connections node or from the specific data connection nodes. To create a database, choose the Create New IBM Database… menu option, which displays a dialog to enter the name of the database to be created. To drop a database, choose the Drop IBM Database… menu option, and specify the name of the database to be dropped. Figure 34 illustrates these menu options:

Figure 34. Create and Drop Databases
Create and Drop Databases

Summary

Developers working on the Visual Studio 2005 environment can now use the IBM Database Add-ins for Visual Studio 2005 V9.5 to connect to Informix Dynamic Server V11.10 and take advantage of the rich set of features offered by the IBM Database Add-ins as well as use the standard Visual Studio 2005 features. This article provides a good overview of all the features that are available when connecting to IDS V11.10. While connecting to an IDS V11.10 data source, you can now build Windows application and Web sites using the Visual Studio 2005 IDE. You can create and delete database objects such as tables, view, stored procedures, and functions using non-modal designers. You can also view and update the data contained in tables and views, and execute stored procedures and functions. Additionally, you can create and drop an IDS V11.10 database directly from the Visual Studio 2005 IDE.


Acknowledgements

This release of the IBM Database Add-ins for Visual Studio V9.5 supporting IDS V11.10 is the result of a tremendous effort from a very dedicated and talented team based in the Silicon Valley Labs at IBM, with help from both the IBM .NET provider team and the Informix Dynamic Server team. Thanks to everyone for their invaluable efforts towards this release.

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=262961
ArticleTitle=Work with Informix Dynamic Server V11.10 and IBM Database Add-ins for Visual Studio 2005 V9.5
publish-date=10182007