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.
- Create the database query
- Run the query to see if the results is as expected
- Generate the definitions
- 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:
- Open the properties of the combo box and select the ellipsis on the Values for the combo box
- 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
- 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:
- 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
- Add a handler
- 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:
- Created database service
- Created OIS object to call the database service
- Linked the database service fields to the combobox definitions
- 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
------------------------------
Original Message:
Sent: 03-01-2022 14:25
From: Peter Cheng
Subject: 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
------------------------------