Skip to main content Skip to complementary content
Qlik Resources
Loading SearchUnify's search If you need assistance with your product, please contact Qlik Support.
Qlik Customer Portal
Loading SearchUnify's search If you need assistance with your product, please contact Qlik Support.
Qlik Customer Portal

Querying a user database using OpenAI

About this task

This scenario will show you how to use the cLangChainTools component to query an user database with OpenAI.
To execute this use case, you need an H2 database engine to be started and the following Products table available.

Dropping and linking the components

Procedure

  1. From the Palette, drag and drop a cLangChainConnection, a cSQLConnection, a cBeanRegister, a cTimer, a cSetBody, a cJavaDSLProcessor, a cProcessor, a cSQL, a cJsonWriter, two cLangChainTools, and two cLog components onto the design workspace.
  2. Link the components with the Row > Route connection as shown above.

Configuring the components

Procedure

  1. Double-click the cLangChainConnection component to display its Basic settings view in the Component tab.
  2. From the Language Model list, select OpenAI.
    In the Base URL field, keep the default address of the API server, http://127.0.0.1/default.
    In the API Key field, enter your API Key to access OpenAI.
    In the Model Name field, enter the name of the model to be used, "gpt-4o-mini" in this use case.
    In the Timeout(s) field, enter 3000 for the component to establish a connection to the language model.
  3. Double-click the cSQLConnection component to display its Basic settings view in the Component tab.
  4. Configure the properties as follows to connect to the H2 database:
    1. In the Driver Class field, enter the class name for the H2 database, "org.h2.Driver".
    2. Click the [+] button under the Driver JAR table to add a row.
    3. Select the cell and click the [...] button at the right side of the cell to open the Module dialog box.
    4. Click the Platform option and select the driver JAR h2-2.2.220.jar from the list.
    5. Click OK to close the Module dialog box.
    6. In the JDBC URL field, enter the JDBC URL of the database to be used, "jdbc:h2:tcp://localhost/~/test" in this example.
    7. Select the Authentication check box and enter the database user authentication data.
  5. Double-click the cBeanRegister component to open its Basic settings view in the Component tab.
  6. In the Id field, enter "jacksonJSONDataFormat".
    Select the Customized option and in the Code field enter the following code to define the Gson data format:
    GsonDataFormat jsonDataFormat = new GsonDataFormat();
jsonDataFormat.setPrettyPrint(true);
jsonDataFormat.setSerializeNulls(false);
beanInstance = jsonDataFormat;
  7. Double-click the cTimer component to open its Basic settings view in the Component tab.
  8. In the Repeat field, enter 1 to generate a message exchange. Keep the default settings of the other options.
  9. Double-click the cSetBody component to open its Basic settings view in the Component tab.
  10. Select Constant from the Language list box and type in "Hello, could you show me the information of all pants?" in the Expression field as the message body.
  11. Double-click the cLangChainTools component in the first subRoute to display its Basic settings view in the Component tab.
  12. Enter "db-tools" in the Name field and "store" in the Tags field.
    In the Connection list, select the cLangChainConnection_1 component to connect to OpenAI.
  13. In the Advanced settings view, enter the following message in the System Message box. 
    "You are an intelligent store assistant. In the store, threre are shirts, dresses, pants and shoes. Users will ask you questions about store products. Your task is to provide accurate and concise answers. 
If you are unable to access the tools to answer the user's query, tell the user that the requested information is not available at this time and that they can try again later."
  14. Double-click the cLog component in the first subRoute to display its Basic settings view in the Component tab.
  15. Select the Specify output log message option and enter "${body}" in the Message field to log the message body.
  16. Double-click the cLangChainTools component in the second subRoute to display its Basic settings view in the Component tab.
  17. Enter "productsByCategory" in the Name field and "store" in the Tags field.
    In the Description field, enter a description for the tool, "Query_database_products_by_category" in this example.
    In the Connection list, select the cLangChainConnection_1 component to connect to OpenAI.
  18. Double-click the cJavaDSLProcessor component to open its Basic settings view in the Component tab.
  19. In the Code box, enter .convertHeaderTo("category", String.class) to convert the header category to a string.
  20. Double-click the second cLog component to display its Basic settings view in the Component tab.
  21. Select the Specify output log message option and enter "${header.category}" in the Message field to log the category.
  22. Double-click the cProcessor component to open its Basic settings view in the Component tab.
  23. Enter the following code in the Code box: 
    String category = (String)exchange.getMessage().getHeader("category");
if (null != category) {
	category = category.replaceAll("^\"|\"$", "");
	exchange.getMessage().setHeader("category", category);
}
  24. Double-click the cSQL component to display its Basic settings view in the Component tab.
  25. In the Datasource list, select cSQLConnection_1 to use the database connection established in it. In Query box, enter the following code to select the data in the category, size, color, and stock columns of the requested category in the Products table: 
    "select category, size, color, stock from Products where Lower(category) = Lower(:#category)"
  26. Double-click the cJsonWriter component to display its Basic settings view in the Component tab.
  27. Select Gson in the JSON Library list and select the Pretty Print check box to format the output JSON message in a readable way.
  28. Press Ctrl+S to save your Route.

Viewing the code and executing the Route

Procedure

  1. Click the Code tab at the bottom of the design workspace to check the generated code.

    As shown above, one message flow is routed from cLangChainTools_2 to cJsonWriter_1, and the other is routed from cTimer_1 to cLog_1.

  2. Click the Run view to display it and click the Run button to launch the execution of your Route. You can also press F6 to execute it.
    The requested category and the information of products in this category in the database table is shown in the execution console.
Related reference
Related information

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – please let us know!

Leave your feedback here