How to query Sql databases via smartobjects

Hi Moises,

You would firstly need to create a SQL Service Instance, this can be done by either registering a new SQL Service Instance using the SmartObject Tester Tool, once you have done this, you could either generate a SmartObject from the SQL Service Instance, or create a SmartBox SmartObject whereby you would link the methods of the Service Object to the SmartObject you are creating. This will allow you to access methods and data from any SQL table.

Hope this helps.

Regards,

You don't really "write code" with SmartObjects.  They are representations of defined data structures in other business systems.

If you need to join four different tables, you can either:

1) Create a SmartObject for each table being joined and join the SmartObjects, or

2) Create a view in your DB with joined tables and create a SmartObject for the view

If you are joining four tables, the view is probably easier, IMHO.

s0m3one actually makes a good point. While accessing tables and views is made fairly easy with the SQL Service Broker. You do run the risk of painting yourself into a corner.

What I mean is that Smart Objects, Forms and Workflows are extremely unforgiving of change in your data structure. A change to a table or view can cause a cascade of failure up through your solution causing you an immense amount of rework and in some cases complete form corruption. Granted, improvements have been made to make Smart Forms much more forgiving of changes to smart objects but it is still a real risk.

Also, with tables and views you are relying solely on the SQL that is generated by the service broker. Some subscribe to the idea that application generated queries are a bad. I am of the school of thought is that it really depends if you can take that risk. Not to say that the queries are bad, just that you as a developer have no say in what those queries look like.

Using a stored procedure mitigates a lot of these issues. I find in my projects that having the extra layer of abstraction between your data layer and the K2 components gives you the ability to absorb changes to the data structure with minimal or controlled impact to the solution and it gives you full control over how tables and views are queried allowing opportunity for performance optimization.

I realize that there is a whole school of thought in the development world against stored procedures and that they are unnecessary and I can say that I am not in complete disagreement with them. However, I submit that idea really is only a valid argument when you are dealing with code and in situations where you have control over those queries. Neither one of those cases are present here.

Managing data change is a big deciding factor for me, personally. Performance is another huge factor. Composite Smart Object makes things simpler for some, but there is a performance cost. Joins and Filters are done in the K2 stack so that data is brought into the K2 server before manipulating the dataset. If you are working with large datasets that is going to put a strain on the SQL Server K2 uses plus generate a fair amount of network traffic. In high volume scenarios, this is bad.

Understanding the frequency your data structure may change and the volumes of data you will be working with is a key factor in your decision making process. Also, take into consideration how the Db technology you are using handles views. In the case of some versions of oracle, views can be under performing as all the data that is required for the view is queried before any where clauses are applied. I don't know if this is the same for SQL, but it is something to think about and only matters if performance is a key factor.

Finally, using a stored procedure gives you some flexibility when dealing with any pesky "undocumented features" that may show up in the SQL or Oracle broker. Most of my experiences with those features revolved around the Oracle broker, but by adding the extra abstraction layer through the use of a stored procedure it did give me options when I did encounter them.

All in all, always do what is best for you and your scenario. Follow the standards of your organization, but I always recommend that developers cheat the odds in their favor when they can.

Hope this helps.

S.