REFERENCE LIBRARY
LEARNING CENTER

Working with Databases

To continue with this content, please log in with your Data Access ID or create a new account.
Cancel Data Access ID
You may not be authorized to see this content. Please contact Data Access Europe for more information.
Cancel Data Access Europe
You are not authorized to see this content.
Cancel Data Access Europe

DataFlex courses

Information

Working with Databases
Introduction (0m 38s)
Use embedded SQL (7m 48s)
Next lesson:
Cancel

Working with databases

Lesson 10: Use embedded SQL

Note: This lesson was recorded in DataFlex 19.0 and does not utilize the SQLExecutor introduced in DataFlex 23.0. For detailed information on using SQLExecutor in DataFlex 23.0 and later versions, please refer to this lesson about Embedded SQL in DataFlex 2023.

This lesson will use embedded SQL to show an alternative method to access data. Embedded SQL is only available when working with SQL database. In this lesson Microsoft SQL will be used. This allows SQL statements to be used directly within DataFlex source to access the data.

Accessing the SQL database directly is advantageous. For example, when building a business process or when a list needs to be filled quickly.

  1. Create a basic Windows project…
  2. From within the DataFlex Studio select the ‘Create New’ icon from the top toolbar.
  3. Select the ‘Project’ tab.
  4. Select ‘Basic Project.’ Select OK.
  5. Enter “EmbeddedDemo” as the ‘File Name.’ Select OK.
  6. Select the ‘Create New’ icon from the top toolbar.
  7. Select the ‘Other’ tab.
  8. Select ‘Business Process.’ Select OK.
  9. Enter “oEmbeddedDemo” as the ‘Object Name.’ Select OK.
  10. Add packages to the business process (EmbeddedDemo.bp)…
    Use sql.pkgUse cConnection.pkg

  11. Enable the error state…
  12. From the ‘Properties’ tab in the ‘Properties’ panel on the left set ‘Display_Error_State’ to ‘True.’
  13. Add code to the inside of the OnProcess procedure to execute the embedded SQL statement…
    Procedure OnProcess      Handle hDbc hStmt
          String [][] aResults
          Integer i

          Get SQLConnectionId of ghoConnection “cookbook” to hDbc

    Get SQLOpen of hDbc to hStmt
         
          Send SQLExecDirect of hStmt “SELECT Name, Rating FROM Recipe WHERE Recipe.Rating > 5”

          Get SQLFetchResultsetValues of hStmt to aResults

          Send SQLClose of hSmt
          Send SQLDisconnect of hDbc

          For I from 0 to (SizeOfArray(aResults) – 1)
                Showln (SFormat (“Recipe: %1, Rating: %2”, aResults [i][0], aResults [i][1]))
          Loop
    End_Procedure

  14. Add code to include the business process in the application….
  15. Navigate to EmbeddedDemo.src.
     Use EmbeddedDemo.bpSend DoProcess of oEmbeddedDemo

  16. Add a break point to the code to confirm that it is properly working inside the debugger.
  17. Run the application by pressing F5.
  18. By stepping through the lines of code the information in the 'Locals' panel will change:
  19. On line 24 we see the connection handle in the 'Locals' panel.
  20. On line 26 we see the statement handle having it's value.
  21. On line 28 the results are fetched which we will see in the aResults array.
  22. The ‘Locals’ panel shows the two-dimensional arrays with the data.
  23. The form wil show the data.
©2024 Data Access Worldwide, all rights reserved
Privacy policy Cookies About dataaccess.com Online Documentation