In this article, we will walk through how to define a new Table using the QuickBooks Online ODBC driver. You can run Stored Procedures as SQL queries using the EXECUTE or EXEC statements; however, our driver offers a more graphical, streamlined approach in the ODBC administrator tool for Stored Procedures that generates report schema files for relevant QuickBooks Online Reports.
We will be creating a report schema file when using this Stored Procedure, which creates a .RSD file structuring the elements according to our described inputs.
Before we create the schema file, let's make sure we have the Location set in our Connection.
You will need to go to your ODBC Data Source Administrator, select your relevant DSN, in my case I have 'CData QuickBooksOnline Source' selected, and click on 'Configure...'
Next, we will need to define the Location since we plan to extend the data model with a new View. There is a default value set for us, as seen in our documentation (https://cdn.cdata.com/help/RNJ/odbc/RSBQuickBooks_p_Location.htm). If you would like the schema files to be placed elsewhere then specify a path by going to the Schema Tab under Advanced Connection Properties.
Then, head over to the Data Model tab, make sure QuickBooks Online is selected, and click on 'Define New Table'. It is important to note that we are creating a View, and not Table, as this will be a read-only entity that cannot be modified.
Under Table Schema Type, you will be able to choose the View you want, in my case I selected 'Create Profit and Loss Summary Report'.
You'll be able to configure the parameters for your new View. See this documentation (https://cdn.cdata.com/help/RNJ/odbc/pg_allsps.htm) of report schema files and click on the relevant one for descriptions of each parameter.
After creating the View, you should see the .RSD file in the Location specified earlier, as well as the View grouped with our other Tables and Views - see screenshot below.
Now you will be able to query the report data, as if it were any other View in the list, using your desired ODBC querying tool!