What are connections used for?
Define the connection and add the company
Add multiple Named Connections
Connections define the database connections needed to access company documents. Most users need only one connection because they are integrating with one financial system or business entity.
Multiple connections may need to be set up for certain users. These are some common cases :
§ the user's financial system consists of multiple databases;
§ the user wants to update shipping information in a related system, such as EDI or CRM;
§ If the user will be shipping against documents in two different financial systems (for example, sample orders in CRM and warehouse orders in the financial system).
What are connections used for?
§ Each SQL statement that makes up the interface for browsing, selecting, or writing back to documents will have an associated Named Connection.
§ When users add a company, either in the SQL Interface Builder or the StarShip Client, they will need to establish a connection to the database for each Named Connection.
§ ID : The connection identifier
§ Description : Displayed for the user when adding a company.
§ Database Validation SQL : This field can be used to validate the connection to the correct database. This field can also be left blank ... this is just an optional way to add connection validation. If specified, StarShip will require the Expected Table to be in the database that the user associates with this connection when adding a company. For developers using Pervasive ODBC and other proprietary ODBC database engines, you can define your own SQL statement with whatever syntax is supported by your database engine.
§ SQL Query : An optional Select statement identifying the field and table where the company information is located. If you do not specify a SQL and 'Column that returns company name', a user must type in the company name and can input any company name, whether it exists in the database or not.
§ Column that returns company name : If any SQL is specified to return company names, you should define the field that will return the company name chosen by the user. In this example, it is CompanyName . To provide the user with company name(s) you need to specify the field in your SQL that contains the company name(s). If you specify a SQL for company names but do not populate this field, you cannot add a company.
§ Design Time Company : Currently active company for this interface. When entering SQL statements, they are dynamically validated with SQL Server against the database selected in Design Time Company.
1. Select Add from below the All Named Connections List. Enter the Connection ID, Description, and the (optional) Expected Table.
2. (Optional) A SQL statement can be added to retrieve the company names, if your database contains that information. Click the button next to the SQL Query field to add the query. See Notes below if you are not entering a query.
3. Then, click Add below the Design Time Company field. The Companies dialog displays.
This dialog shows you the Connection ID so you know which connection you are using, in case of multiple connections. Note that the description you entered in Step 1 displays in the Description field.
Click Create Connection.
4. Define the connection parameters. This includes selecting the Server name, authentication, and the database.
5. Click OK. The Company Name field will be populated with the company names returned from the SQL statement, if you entered one. In the case of multiple companies, one can be selected from a drop-down list.
If you did not specify a SQL, type the company name into the Company Name field.
The connection is automatically tested when you click OK and exit the dialog.
If a SQL that returns company names is not specified on the Connections tab, but the internal field {Company.Company Name} is used in a Where clause elsewhere, this will leave an opening for user error when typing in the Company Name (during Add Company). The user may enter a company name that does not exist in the database; this is pertinent if any of your queries depend upon the company name being supplied correctly.
If you are using multiple databases, you will need to create multiple connections and specify connection properties for each one.
First, create the connections by adding them to All Named Connections. Then, click Add below the Design Time Company field. Click Next to page through the wizard to add connection properties for each connection.
Note that only one connection should have a Company SQL (if you provided one); if you specify a company SQL for multiple connections they would overwrite each other when adding a company, and only one would be used.
Click Finish.
When two or more companies exist, you need to repeat the "Add" step. After adding the first company and clicking Finish, click Add again on the same connection.
Choose the second company name and click Finish.
On the Connections tab, you can now select from multiple companies in the Design Time Company field.
In the case that the database's structure is different for the same connection but has different companies, you must select the correct Design Time Company when entering a SQL statement in order for it to be validated.
Next : Define Company Fields