Hello again and welcome to the Part 3 - Using the SQL API!
If you have been wondering about how to use SQL along with Frontier, you came to the right place. That's because since Frontier wraps the common Caché SQL API within it's own, you need to use the API provided from it. But you don't need to worry about its learning curve, because the Frontier SQL API is really simple.
If you arrived here without checking the Part 1, I'd recommend you doing so, because Part 1 covers the essential for getting started with Frontier routers.
- Core concepts
- Getting started
- Creating a simple request
- Query parameters
- Aliasing query parameters
- Changing output format
- Rest query parameters
- Inferring object instances
- Using literal notation
- Seamlessly mixing instances with literals
- Returning streams
- Handling payloads
- How it works
- Making it useful
- Unmarshalling payloads into instances
- Using the unmarshaller to EDIT an existing object
- Using the SQL API
- Creating a simple dynamic query
- Overwriting the default container property
- Using cached queries
- Passing parameters to queries
- Sharing data across router methods
- Forcing API errors
- Managing errors with Reporters
This part covers the topic 6.
6. Using the SQL API
The SQL API is similar to what %SQL.Statement provides, with a single restriction: you can NOT use it to execute a query manually.
It's designed this way to make sure that each row is processed by Frontier's SQL serializer and also to prevent the developer from using different SQL APIs (%ResultSet) or restricting them to use queries only.
So keep in mind, that whenever you want to return a collection of data using SQL you MUST use the Frontier's SQL API.
However, if you only need to do some internal operation using SQL, you can use %ResultSet, %SQL.Statement or embedded SQL without issues.
Creating a simple dynamic query
In order to demonstrate what is possible to do with it, let's create a simple method that returns a TOP 3 list of Persons using a dynamic query.
Add the route:
And the method for the Call.
As we execute it, we can notice the result:
Very easy, right?
Overwriting the default container property
Notice that SQL results inserted are binded to that "results" property, by default this property serves as a container for SQL providers. If you want to override this behavior, simply put the result inside a %DynamicObject instance.
To see this feature in action, update the method to look like this:
This way you can use a custom JSON structure.
Using cached queries
If you want to use a cached query, Frontier provides a way similar to the format used with %ResultSet. That is:
Add a new route like this:
Then copy the method we created earlier and modify to use the syntax above.
Now create the query, remember to flag it with SqlProc or it won't be available to use with the SQL engine.
Passing parameters to queries
Until this point, queries are not that much useful because they don't accept parameters. So to demonstrate how we can pass query parameters we will simulate a pagination mechanism using %VID.
In order to do that, create a new route:
And a method that receives arguments. We're going to use query parameters to delimit how much entries we want to fetch.
Notice that Parameters method, it's signature is similar to what %Execute does, but without actually executing it.
Mode is what we would call SelectMode or DisplayMode. You'll notice that the DOB field is formated.
And finally, we define a query to support it:
And we have a paginated query:
Naturally, you can do the same using a dynamic query, just like before, but use ? instead of :.
This concludes the part 3, if you have any doubts please don't be afraid to post them below.
Next part we are going to learn how to share data between methods. Stay in touch!