Step 3: Select a Database

To display and edit data in your application, Iron Speed Designer needs to connect to your databas.  The user name and password information is used by:

• Iron Speed Designer to retrieve the database schema to help you create user interface pages containing database information.  No changes to the database are made during this retrieval process.

• Iron Speed Designer to create a set of stored procedures.

• Your application to display and edit data.

Database Connectivity

Iron Speed Designer uses ADO.NET specific drivers for each database such as Microsoft Access, Microsoft SQL Server and Oracle.  By contrast, Iron Speed Designer does not use ODBC drivers to connect to databases.  ODBC drivers do not support additional database-specific features, such as database-specific field types, which Iron Speed Designer supports.

Related topics

Microsoft Access Options for Database Server Selection

Microsoft SQL Server Options for Database Server Selection

MySQL Options for Database Server Selection

Oracle Options for Database Server Selection

Working with Complex Databases

Database, Table, and Field Name Restrictions

Microsoft Access Options for Database Server Selection

Microsoft SQL Server Options for Database Server Selection

Microsoft SQL Server

In some cases, Microsoft SQL Server servers may not be displayed.  When you install SQL Server, it disables TCP/IP for your default instance or your named instance.  Without the TCP/IP being enabled, Iron Speed Designer cannot see your database server.  Iron Speed Designer uses a Windows API function which requests the set of available database servers via TCP/IP.  To enable TCP/IP for your SQL Server default instance or named instance, follow the instructions in this Microsoft Knowledge Base article:

http://msdn2.microsoft.com/en-us/library/ms177440.aspx

You may also want to use “(local)” as the database server name for your Microsoft SQL Server installation if it’s on your local machine, in keeping with common practice in earlier versions of Microsoft SQL Server.

To do this, run the 'Microsoft SQL Server Management Studio' and connect 'Database Engine' using '(local)' in Server name.

MySQL Options for Database Server Selection

Oracle Options for Database Server Selection

Working with Complex Databases

Some users have more complex database structures such as:

• Databases owners other than dbo in Microsoft SQL Server.

• Multiple databases on the same server requiring foreign key relationships between them.

• Multiple schemas in Oracle containing related tables.

• Linked databases in Microsoft Access or Microsoft SQL Server.

Iron Speed Designer has a number of features that, when combined with features of the database product, allow you to take advantage of the complex database structures in your application.

The easiest way to take advantage of these features is to:

Step 1:  Create a database view within your primary database or schema to retrieves all records from the other database, schema or from another owner’s schema.  This can be done by specifying a query such as:

SELECT * FROM dbXYZ.Customers

This query retrieves all rows from the Customers table belonging to dbXYZ.  This query can be saved as a database view in the primary database being used by the Iron Speed Designer application.

Step 2:  Use the newly created database view in Iron Speed Designer via:

• The Application Wizard, when creating a set of pages.

• Selecting the “Include this table/view in the application” option in Databases.

Step 3:  After selecting the database view, create a Virtual Primary Key for the view in Iron Speed Designer.

Step 4:  Create any necessary Virtual Foreign Key relationships between this view and other tables or views.

After performing the above steps, you can create applications using these complex database structures.  When the schema changes, update the database view in your database, and then re-synchronize the database with in Iron Speed Designer.

Database, Table, and Field Name Restrictions

Database name restrictions

Iron Speed Designer is not currently able to connect to databases whose database names have these characteristics:

• The # character is not supported in database names.

Database table name restrictions

Due to limitations in Microsoft Windows, Iron Speed Designer is not able to use or reference database tables with these names:

AUX

CLOCK$

COM1-COM9

CON

LPT1-LPT9

NUL

PRN

Other table name restrictions include:

• Table names may not exceed 50 characters in length.

• Table names may not contain leading or trailing blanks.

• Many, but not all, special characters are supported.  Note, however, that table name containing some special characters may cause problems.  Iron Speed Designer replaces special characters such as +, -, *, and % by the underscore character (“_) when creating table names, class names based on table names, and stored procedure names based on table names.  This can lead to situations where two different table names have the same class name or stored procedure name because of this underscore mapping.  Moreover, MSI Deployment Installers created by Iron Speed Designer may not function properly because the MSI Deployment Installer will not install stored procedures containing underscores in their names.

• Two or more table names differing only in special characters (i.e., “A+B” and “A-B”) will cause a conflict because both map to the same modified name (e.g., “A_B”).  Iron Speed Designer does not guarantee uniqueness when mapping special characters.

• Table names with multiple brackets (i.e., “A[B])]C”) are not supported because the multiple brackets cause problems with SQL statements.

The same table name may not be included from two different databases in a multi-database application.  Iron Speed Designer creates class names based on the underlying table name or database view name, which conflicts when two tables have the same name.

Database Field Name Restrictions

Iron Speed Designer must create SQL statements that conform to the underlying database, such as Microsoft SQL Server.  Accordingly, there are several restrictions as to table names and field names used in the underlying database.

Note: Iron Speed Designer replaces spaces and dash characters with underscores.