Configure a JDBC Data Service

To configure a JDBC data service so that Designers can retrieve data from a database, a Data Service Administrator A local administrator who creates and configures data services for use by Designers who configure Database data sources and Alternate data sources. should use this procedure.

Before You Begin: Instead of specifying a connection URL and credentials in each JDBC data service, you can configure a Link data service to centrally manage those parameters. This is particularly useful if multiple JDBC data services will use the same connection URL and credentials, or if label templates are frequently moved from one Loftware Application Server to another that requires different credentials to access a database. As a best practice, it is recommended that you configure the Link data service first. For more information, see Configure a Link Data Service.

Important! Java Database Connectivity (JDBC) API drivers for Oracle 19c, PostgreSQL 14.4 to 15 for Linux, and PostgreSQL 14.4 to 15 for Windows Server, are installed with Loftware Enterprise SP. To enable Loftware Enterprise SP to connect to another type of database, you must install a third-party JDBC API driver on the computer where Loftware Enterprise SP is installed. For more information, see Add a JDBC Driver.

Note: You do not have to stop a data service to make changes to its configuration or for those changes to take effect, but you must save your changes.

1. In Data Services , click > JDBC Data Service.
2. In the Connection panel enter the URL and credentials for connecting to a database. If the connection URL and credentials will be provided via a Link data service, in the General panel select Is Data Link and select the Link data service in the Data Link field.
3. For each parameter, decide whether you will manage the parameter or whether you will allow the Designer to manage the parameter for each Database data source. For more about each parameter, see Configuration Parameters for JDBC Data Services.
   • To allow Designers to configure a parameter, select the Is Parameter check box. You can either specify an initial value or data ref for the parameter or leave it blank. Changes made to the parameter in the data service are not propagated to existing Database data sources.
   • To configure a parameter as part of the data service, clear the Is Parameter check box and configure the parameter, entering a value or a data ref. Changes made to the parameter in the data service are propagated to Database data sources. The parameter is not displayed to Designers in Database data sources.

     Important! In most cases, you should clear the Is Parameter check boxes for the JDBC Connection URL, the database credentials, the URL credentials, and Read Only Session so that they cannot be changed by a Designer. For JDBC data services, it is recommended that you select the Read Only Session check box to prevent misuse of the database connection.

4. To add a custom parameter, in the Advanced panel click Add Parameter.

   More about adding a parameter

   1. In the Add Parameter dialog box, enter a Variable Name for the parameter. When the parameter is displayed to Data Service Administrators or Designers, this name identifies the parameter.
   2. Select a Data Type for the parameter.

      Note: The maximum length for a string is 4,000 characters.

   3. Click OK. The parameter is displayed in the data service.
   4. If you want to allow Designers to enter a value for this parameter or connect it to a data source:

      Select the Is Parameter check box.

      Leave the text field blank or specify an initial value.

   5. If you want to prevent Designers from viewing or configuring this parameter:

      Clear the Is Parameter check box.

      Enter a value or a data ref to associate with the parameter. For a data ref, use the format ${<DataRef>}. If you enter a data ref, label templates that contain a Database data source associated with this data service must include a data source with that data ref.

   Note: To remove the parameter, click the Delete button next to it.

   Important! If you delete a parameter from a data service, the parameter is automatically removed from any Database data sources associated with that data service.

   For more information about custom parameters, see the Advanced section of Configuration Parameters for JDBC Data Services.

5. In the SQL panel, you can include a query of the database. Alternatively, you can allow Designers to configure the query when they create Database data sources that use the data service.
   
6. Click Run Query to populate the Result Map with details about each column returned by the SQL query.

   Note: The Result Map is not dynamic. If the columns available in the database have changed, you must run the query again to see those changes reflected in the data service.

   More about the Result Map

   For the Result Map, if you have cleared Is Parameter and want to customize the column information returned by the query, do the following. For more information, see Configuration Parameters for JDBC Data Services.

   1. Click Run Query to populate the Result Map.
   2. Review the Result Map and make changes as needed.
   3. If you have extensively customized the Result Map, it is recommended that you note the details of your configuration so that you can reproduce it if it should ever become necessary to reset the Result Map due to changes in the database or other data source.
7. In the Properties panel, enter a Description of the data service.
8. In the ribbon, click Save .
   
   Characters permitted in names

   The following characters are permitted in the name: letters, numbers, parentheses, square brackets, ampersands, plus signs, commas, semi-colons, and tildes. Additionally, the following characters are permitted but cannot begin or end the name: spaces, double quotation marks, single quotation marks, hyphens, underscores, periods, and grave accents. For letters, the case that you specify is displayed, but case is ignored when Loftware Enterprise SP interprets a name. If you will be installing the Loftware Enterprise SP application on a computer running Windows Server, and transferring data between Loftware Enterprise SP instances, see the "Transferring Data in a Compressed File" section of the Loftware Enterprise SP Data Transfer Guide.

   Important! Provide a Display Name that is easily recognizable to Designers. When a Designer Person who creates and designs label templates, forms, and applications for use by Data Providers. creates a Database data source A connection to a database that acts as a data source and can serve as the data ref for a document field. A Database data source is associated with a Database data service., the Display Name (but not the Description) is displayed. Data service names are case sensitive.

9. If the data service has not been started, click Start Data Service.
10. In the ribbon, click Save .

The data service is updated with the connection information. You can view the status of the data service in the Properties pane. If the data service is stopped, you must start it to make the results available when printing labels.

Important! Whenever you add a new device, data service, integration, or facility, you must ensure that the service is activated on all Loftware Application Servers on which it should be available to run and started on at least one. In an environment with only one Loftware Application Server, these types of services are automatically activated and started when they are created. For more information, see High Availability with Distributed Services.

Note: If you want to delete a data service, you must stop it before you can delete it.