The database connector reads its definitions from a configuration file. By default this file is located in <frevvo-home>/tomcat/webapps/database/WEB-INF/etc/configuration.xml but you can [[#Configuration_File_Location | change the location]].

...

Note the string '''{customerId}'''. The database connector SQL statements are actually templates that are resolved at run time using values passed in the http request. In the example above, if there is a parameter in the http GET request that hits the connector with customerId=1234 than the statement would return the record for customer 1234.

===== Stored Procedure =====
You can also execute stored procedures via the database connector. Here is an example of a mySql stored procedure.

DELIMITER //
CREATE PROCEDURE GetNewOrderNum()
BEGIN
SELECT max(orderNumber) + 1 as onum FROM Orders;
END //
DELIMITER ;

To call this from the mySql command line you would use the command: call GetNewOrderNum(); To call this from the database connector you would add the following to configuration.xml:

<query name="getOrderNumber">
<retrieve>
<statement>
call GetNewOrderNum() 
</statement> 
</retrieve> 
</query>

To retrieve the next order number from your database and populate that value into a control in your form named 'onum', add the following business rule to your form:

eval ('x=' + http.get('http://localhost:8082/database/BIRT/getOrderNumber'));
onum.value = x.resultSet[0].onum;

Here is an example with the syntax required for a SQL server stored procedure:

<query name="getOrderNumber">
<retrieve>
<statement>
exec GetNewOrderNum() 
</statement> 
</retrieve> 
</query>

To pass form field values to your SQL server stored procedure, append the variables to the end of the exec line. For example if your form contains a field named customerId and department, and your sproc takes two arguments @cust and @dept:

<query name="getOrderNumber">
<retrieve>
<statement>
exec GetNewOrderNum() @cust = {customerId}, @dept = {department} 
</statement> 
</retrieve> 
</query>

Auto Create Rows

You can set the attribute '''autocreate''' in a '''query''' element.

...