Creating a Database Service

We would like to display a combo box using values in a table inside a database, I saw in the help section that I can do it using a database service by using ODBC driver.  Do you currently have any examples of this?

Thanks.

------------------------------
Peter Cheng
Software Engineer
Amber Road Inc
Parsippany NJ US
------------------------------

Hi Peter,

A project example for ODBC may be a bit challenging as the database services rely on the database being available and defined precisely as for the example was used.

The configuration is not complex but does require a number of steps, I can give explanations with screenshots of the steps to take to make this work.

As example I have created a database in PostgreSQL called 'example'. 
Next to installing PostgreSQL I've also installed the 64-bit ODBC driver to access the database through ODBC.
I also created an ODBC entry to connect to my example database (using 64-bit ODBC data sources in windows).
I will not add instructions for this, as these will be available in the documentation of these products.

Important to note is that the ODBC driver will also need to be available on the server where your engine will run later.

My example database contains a table called 'menus' with two columns: menuid (type char, length 10)  and menudesc (type text)
I've added a number of rows, and my intention is to use this contents for the 'MENU'  field on the logon page - showing descriptions (display values) and selecting the menuid as value for the host (send value).

The process in the web builder starts by creating a database service in your web project.

Open repositories in your resource explorer and right-click on the Service definitions to create a new database service:

Give this a name that describes the service for you. I've called mine "GetMenus":
Next, you will need to connect to the database.
I had called my ODCB connection data source "example" so I can connect to this data source. Select the data source and enter your credentials in the bottom fields of this dialog.
Clicking the button "Generate connection string" will create the connection string in the top field.

From there you can test the connection.
When the test is successful you can finish this dialog.
After finishing this dialog the definition will open in the editor, ready to create your SQL query.
In my example I am using plain SQL. For production systems it would be better to use a stored procedure for security reasons.
The concept is the same though.

1. Create the database query
2. Run the query to see if the results is as expected
3. Generate the definitions
4. Check the definitions and when satisfied, click OK

Save this, this is the ODBC definition that can later be used to get the information from the database.

Next we will need to make an OIS object in order to call the service from either an event or from script.
To do so, right-click on the OIS Objects in your resource explorer, and select "New" then "Service Call".
Give this a useful name. I've used "GetMenus" here:
Next the editor will open.
You can add a description.
The default selection of events (Command) will be correct.
Click on the browse button to select a Service Name.
For production usage I would recommend also adding an Error handling function to script what to do when the service fails. For instance when the database is not available. For the sake of this example I will skip that step, details on this can be found in the documentation.
Save this configuration and your OIS object is also ready to go.

Now we can connect the database service output to the combo box field definition.
To do so:

1. Open the properties of the combo box and select the ellipsis on the Values for the combo box
2. Select the ellipsis for display and/or send values and select the response field to use
   By creating the service in previous steps, these variables have been created by the wizard
3. Select the proper field from the response

The next steps it to make sure the database service is called at the correct time.
Before the values of the database will be available, the OIS object need to be executed.
In my example I will connect the OIS event to the OnLoad of my page.
The service has no input variables, so I can execute it as soon as the page opens.
This event can also take place before loading the page (e.g. on a previous page) or through script when input is required.

To execute the OIS event on the page load:

1. Open the page and go to the properties of the page itself, select the Events tab and click the ellipsis on OnLoad to see the handlers
2. Add a handler
3. Select OIS script call

Next, select the new created OIS object and click OK:
Click finish to add the OIS object to the OnLoad handler:
This completes the definition:

1. Created database service
2. Created OIS object to call the database service
3. Linked the database service fields to the combobox definitions
4. Call the OIS object to execute the database service on load of the logon page

The result of a live test is a pre-populated combo box with the contents of the database table. When selecting a value of the list of menudesc entries, the value of menuid will be actually send to the host system.
Hope this helps,
Regards,

------------------------------
Roger van Valen
Senior manager, software engineering
Rocket Software
Dordrecht, The Netherlands
------------------------------

Hi Peter,

A project example for ODBC may be a bit challenging as the database services rely on the database being available and defined precisely as for the example was used.

The configuration is not complex but does require a number of steps, I can give explanations with screenshots of the steps to take to make this work.

As example I have created a database in PostgreSQL called 'example'. 
Next to installing PostgreSQL I've also installed the 64-bit ODBC driver to access the database through ODBC.
I also created an ODBC entry to connect to my example database (using 64-bit ODBC data sources in windows).
I will not add instructions for this, as these will be available in the documentation of these products.

Important to note is that the ODBC driver will also need to be available on the server where your engine will run later.

My example database contains a table called 'menus' with two columns: menuid (type char, length 10)  and menudesc (type text)
I've added a number of rows, and my intention is to use this contents for the 'MENU'  field on the logon page - showing descriptions (display values) and selecting the menuid as value for the host (send value).

The process in the web builder starts by creating a database service in your web project.

Open repositories in your resource explorer and right-click on the Service definitions to create a new database service:

Give this a name that describes the service for you. I've called mine "GetMenus":

Next, you will need to connect to the database.
I had called my ODCB connection data source "example" so I can connect to this data source. Select the data source and enter your credentials in the bottom fields of this dialog.

Clicking the button "Generate connection string" will create the connection string in the top field.

From there you can test the connection.
When the test is successful you can finish this dialog.

After finishing this dialog the definition will open in the editor, ready to create your SQL query.
In my example I am using plain SQL. For production systems it would be better to use a stored procedure for security reasons.
The concept is the same though.

1. Create the database query
2. Run the query to see if the results is as expected
3. Generate the definitions
4. Check the definitions and when satisfied, click OK

Save this, this is the ODBC definition that can later be used to get the information from the database.

Next we will need to make an OIS object in order to call the service from either an event or from script.
To do so, right-click on the OIS Objects in your resource explorer, and select "New" then "Service Call".

Give this a useful name. I've used "GetMenus" here:

Next the editor will open.
You can add a description.
The default selection of events (Command) will be correct.
Click on the browse button to select a Service Name.
For production usage I would recommend also adding an Error handling function to script what to do when the service fails. For instance when the database is not available. For the sake of this example I will skip that step, details on this can be found in the documentation.

Save this configuration and your OIS object is also ready to go.

Now we can connect the database service output to the combo box field definition.
To do so:

1. Open the properties of the combo box and select the ellipsis on the Values for the combo box
2. Select the ellipsis for display and/or send values and select the response field to use
   By creating the service in previous steps, these variables have been created by the wizard
3. Select the proper field from the response

The next steps it to make sure the database service is called at the correct time.
Before the values of the database will be available, the OIS object need to be executed.
In my example I will connect the OIS event to the OnLoad of my page.
The service has no input variables, so I can execute it as soon as the page opens.
This event can also take place before loading the page (e.g. on a previous page) or through script when input is required.

To execute the OIS event on the page load:

1. Open the page and go to the properties of the page itself, select the Events tab and click the ellipsis on OnLoad to see the handlers
2. Add a handler
3. Select OIS script call

Next, select the new created OIS object and click OK:

Click finish to add the OIS object to the OnLoad handler:

This completes the definition:

1. Created database service
2. Created OIS object to call the database service
3. Linked the database service fields to the combobox definitions
4. Call the OIS object to execute the database service on load of the logon page

The result of a live test is a pre-populated combo box with the contents of the database table. When selecting a value of the list of menudesc entries, the value of menuid will be actually send to the host system.

Hope this helps,
Regards,

------------------------------
Roger van Valen
Senior manager, software engineering
Rocket Software
Dordrecht, The Netherlands
------------------------------

Thanks Roger, the instructions are very good, I got it working at my first try.

------------------------------
Peter Cheng
Software Engineer
Amber Road Inc
Parsippany NJ US
------------------------------

Thanks Roger, the instructions are very good, I got it working at my first try.

------------------------------
Peter Cheng
Software Engineer
Amber Road Inc
Parsippany NJ US
------------------------------

Hi Roger, does reverse imaging work on these fields?



------------------------------
Peter Cheng
Software Engineering
Amber Road Inc
Parsippany NJ US
------------------------------

Hi Roger, does reverse imaging work on these fields?



------------------------------
Peter Cheng
Software Engineering
Amber Road Inc
Parsippany NJ US
------------------------------

Hi Peter,

From the screen shots I see that the top two fields are colored red, but the combo is not.
My assumption is that you want the same behavior for the combo box.

When the combo is configured as "Normal, no edit" the color conversion for the Combo box will work the same way as it does for the text fields.
For me it currently looks like an editable combo box does not always set color conversion. I will have a look to see if this is behavior for a reason, or if we should change and correct this behavior in future versions.

Hope this helps,
Regards,
Roger.

------------------------------
Roger van Valen
Senior manager, software engineering
Rocket Software
Dordrecht, The Netherlands
------------------------------

Hi Peter,

From the screen shots I see that the top two fields are colored red, but the combo is not.
My assumption is that you want the same behavior for the combo box.

When the combo is configured as "Normal, no edit" the color conversion for the Combo box will work the same way as it does for the text fields.
For me it currently looks like an editable combo box does not always set color conversion. I will have a look to see if this is behavior for a reason, or if we should change and correct this behavior in future versions.

Hope this helps,
Regards,
Roger.

------------------------------
Roger van Valen
Senior manager, software engineering
Rocket Software
Dordrecht, The Netherlands
------------------------------

It is currently on "Normal, no edit", it doesn't seem to work.



------------------------------
Peter Cheng
Software Engineering
Amber Road Inc
Parsippany NJ US
------------------------------

It is currently on "Normal, no edit", it doesn't seem to work.



------------------------------
Peter Cheng
Software Engineering
Amber Road Inc
Parsippany NJ US
------------------------------

Hi Peter,

For combo boxes color conversion should work.
Can you please create a customer case with your product and reproducible steps attached so we can look into your specific situation to see why it does not work well for you?

Regards,
Roger.

------------------------------
Roger van Valen
Senior manager, software engineering
Rocket Software
Dordrecht, The Netherlands
------------------------------

Hi Peter,

For combo boxes color conversion should work.
Can you please create a customer case with your product and reproducible steps attached so we can look into your specific situation to see why it does not work well for you?

Regards,
Roger.

------------------------------
Roger van Valen
Senior manager, software engineering
Rocket Software
Dordrecht, The Netherlands
------------------------------

Hi Roger,

Does it have anything to do because it's a database service combo box?  here's the case #.

CASE 00815891

Thanks

Peter

------------------------------
Peter Cheng
Software Engineering
Amber Road Inc
Parsippany NJ US
------------------------------

Hi Roger,

Does it have anything to do because it's a database service combo box?  here's the case #.

CASE 00815891

Thanks

Peter

------------------------------
Peter Cheng
Software Engineering
Amber Road Inc
Parsippany NJ US
------------------------------

Hi Peter,

Color conversion should work as long as the host field defined is the host field for color conversion (or an alternate host field is set). 

It will not matter where the available values for the combo box come from. Either fixed, a host reference, a database service, a REST or SOAP service. 

Thanks for reporting the case, we will have a look.
As you know the product has many options so it could be a combination of options in the theme and/or project that makes this behave a little different for you. Looking at your project I am confident we can find out how this can be best addressed.

Regards,
Roger.

------------------------------
Roger van Valen
Senior manager, software engineering
Rocket Software
Dordrecht, The Netherlands
------------------------------