1. AuraPlayer
  2. FAQ/ How-To
  3. Creating Services

Articles in this section

See more

DB Services Follow

Yossi Nakash
  • Updated

The ‘DB Service’ executes database queries/operations, and returns the result set.
Your ServiceManager operates with DB Services in a very similar way to Oracle Form Services.

Creating DB Services

  1. On the Service Manager Toolbar, click on "Create" →“DB service”.

       mceclip0.png

  1. The “Service Editor” will be opened, with dedicated database configuration view.mceclip2.png
  1. Enter a service name and description (optional).
  2. Configure the database connection:
    - Fill in the hostname of the database server.
    - Fill in the database port.
    Default ports are: 1521 for Oracle databases, 3306 for MySQL, and 5432 for PostgreSQL.
      - Enter the database Name or SID.
      - Select the database type.

 

  1. Click on “Test connection” to ensure that your connection is properly configured.

 

  1. A username/password dialog will open, enter your credentials to the database server and confirm.
    mceclip3.png

    Your username and password will be automatically stored as the values of the corresponding input parameters. You may clear or hide them if you do not wish them to be exposed. 
          
  2. Success/failure status will be displayed in popup message.
    If the connection failed, do not continue any further - check your DB configuration and retry.


  3. Enter a “Query” as described in the following section.

DB queries / statements


The last field under “Service details” of a Database Service is the “Query” field.
Enter a valid SQL statement.
You should enter a single statement (without semicolon). In order to execute multiple commands, wrap them in function or procedure and call it from here.

Examples:

  • SELECT STATE FROM CITIES WHERE POPULATION <1000

 

  • INSERT INTO CITIES (STATE, CITY, POPULATION, WHITE, BLACK, HISPANIC, ASIAN, OTHER) VALUES ('DE', 'Muenchen', 1300000, 0, 0, 0, 0, 0)

 

Dynamic queries (injecting input parameters)


Your query may vary according to input provided to the service.

For example, assuming that you are managing a CITIES table, and your service updates the population of the city, then the city name and population are probably input provided by the user executing the service. Meaning, instead of writing:

 

  • UPDATE CITIES SET POPULATION=40000 WHERE CITY='Muenchen'

 

You may write:

 

  • UPDATE CITIES SET POPULATION=${population} WHERE CITY='${city}'

 

Now, the population and city values are expected to be found in the input parameters of the service.
You should add an input parameter named population and and input parameter named city to the input parameters table:

Click on the add button mceclip5.png  to append a new row at the bottom of the input parameters table.

Fill in the name (must match to the query; use population in our example), label and default value for parameter.

Finally, click on the save icon mceclip6.png.
You may now add more input parameters – add city for this example. 

mceclip7.png

 

Query examples

 

SELECT

SELECT * FROM ${table} WHERE POPULATION <${max_population}

UPDATE

UPDATE CITIES SETPOPULATION =0 WHERE CITY='${city}'

INSERT

INSERT INTO CITIES(STATE, CITY, POPULATION, WHITE, BLACK, HISPANIC, ASIAN, OTHER) VALUES ('${state}', '${city}', ${population}, 0, 0, 0, 0, 0)

DELETE

DELETE FROM CITIES WHERE state = '${state}'

Calling a function

SELECT GET_POPULATION('${city}') as result FROM dual
Since the name of the selected value is dynamic, we have to add "as result" (or any other name) so the output will be returned by that name and we would be able to capture the value with an output parameter named “result”.

Executing a procedure

EXECUTE INCREASE_POPULATION('${city}')

 

 

Testing queries and acquiring output parameters 

After configuring your database connection and query, click on the “Get output” button to execute your query for the first time.
In addition to executing the query, it also adds any table columns returned from the query to the “Output parameters” table.

mceclip1.png 

Your query may not return any values, and no output parameters will be added (except from the traditional Error, PopupMessages, andStatusBarMessages).

The number of new output parameters added to the table will be displayed in a popup message:

mceclip0.png

You may also manually add, hide, or delete any output parameters.

 

Finish by saving the service.

 

Viewing recorded DB services

 

All services are listed in the ‘Services’ tab from the left toolbar.

The ‘Type’ column in the services list, distinguishes DB Services from other services.

Executing, editing and using DB services

 

All actions are to be performed in the same manner as with the regular Oracle Form Services.
Consult this manual for further instructions.

 

 

DB Services Output

The DB Services maintain the same response structure as the Oracle Form Services:

 

 

 

The contents of the 3 special output parameters are:

  • Error – DB errors, if any.
  • StatusBarMessages – Number of affected rows if the executed statement has no output.

 

The ‘DB Service’ executes database queries/operations, and returns the result set.
Your ServiceManager operates with DB Services in a very similar way to Oracle Form Services.

 

 

Related articles

Comments

0 comments

Please sign in to leave a comment.