SQL Queries in QCubed

Although the QCubed can generate the SQL query code for most of your application, you will undoubtedly need to write your own custom queries, to either perform more refined Load methods, execute searches, generate reports, etc.

The framework offers multiple ways for performing your own custom SQL queries, from completely free form queries using just the database adapter, itself, to completely structured object-oriented queries using QCubed Query, or QQ for short. In general, there are three main ways to perform queries, with pros and cons for each:

• Ad Hoc Queries: Completely custom, ad hoc queries can be executed by accessing the database adapter, itself. The advantage of this is that you have complete, total free form control over how you want the query to run. Moreover, you can also run "NonQuery" commands like UPDATE and DELETE. The disadvantage is that because the queries are completely free form, there is no structure, and the Qcodo generated ORM cannot take advantage or use your query results at all.

• Custom Load Queries: These custom SQL SELECT statements do require a bit more structure, but by adhering to a structure/form that QCubed expects, you can utilize code-generated InstantiateDbRow and InstantiateDbResult methods to convert your query results into instantiated data objects. You still get the benefit of writing, more or less, completely custom SQL SELECT statements, but you now have the added benefit of taking advantage of your code generated ORM. The drawback is that if/when you make changes to your data model, you may need to go back and revisit your custom-written SQL code to ensure that the appropriate fields are being selected to match what the QCubed ORM is expecting.

• QCubed Query: This is a fully structure, object-oriented approach to performing SQL SELECT queries, without needing to write a single line of SQL code. Utilizing code generated code and per-table-specific QCubed Query nodes, the QQ API offers almost the full set of functionality that free form Custom Load Queries provide, but with the added advantage that whenever you make changes to your data model and re-code generate, you do not have to worry about updating any hard-coded SQL statements in your code. Of course, the drawback is that QQ is a new methodology for performing queries, so there will be a learning curve.

The examples below provide a quick sample of each of these three query types. And then the following examples will illustrate QCubed Query in much greater detail.

As a final note, all the examples here are coded below on the page, itself. However, it is always a good practice to have query code like this written within the classes, themselves. Especially for any Load-related methods, QCubed tries to be consistent in following the Singleton design pattern with static "LoadBy" and "LoadArrayBy" methods, so the SELECT queries for any table can reside in that table's ORM class, itself. For more on this, be sure to view the code generated commented out sample code in your custom ORM subclasses in /includes/data_classes. See Customized Load Methods in Section 2 for more information.