I need to do a SQL directly to the broker to get some data from a component and I don't really know how. I mean, I don't know the table structure to be able to from a tcm get all the component data for example.
I don't have any clue how the db is organized but I really need to do the query directly to the broker. Please, don't point me to the core service, just a little bit of help on how to get all the data from a component.
Thank you very much!
I wouldn't query it directly at all: You already said you don't have a clue about the structure, so image you would finally be able to write your queries, you'd probably have to change it when a new version comes out.
I'd try out the following options:
Whatever you do, try to avoid a direct query at all times..
Tridion.ContentDelivery.DynamicContent namespace which contains the Broker Query stuff.
Mar 13, 2013 at 16:49
+1 for a question that comes up frequently, though everyone say not to query SDL Tridion databases. It's easy to query "content" from the storage broker (hints below).
Some clarifications:
The Content Management System (CMS) and its database stores component and pages as well as their history. In CMS, can "inspect component data" using the Core Service as well as the TOM.NET API (in templates).
However, Content Delivery (CD) stores pages and component presentations, the rendered output of a component and a component template. You retrieve these as files or through the CD API. The Broker lives in CD.
CMS-side component information is available in CD if it's metadata or if your templates outputted them.
The broker database is just one of several ways to store rendered output. It assumes you're using dynamic component templates and storage configuration that places component presentations in the database, which may not apply for a given publication, implementation, or CMS.
As a practical example of why querying either the CM or CD databases is a bad idea, sometime between SDL Tridion R5.3 and 2011, the broker database changed from comma-separated values in the CUSTOM_META table to repeated key-value pairs. Also in SDL Tridion 2011 (SP1, I think), file-based metadata was deprecated.
Use a component's Source tab to see how SDL Tridion stores it on the CM-Side. Otherwise see my posts on how to get it with a trivial XSLT template or simple C# code.
To get fields from the delivery-side, consider starting with SDL Live Content's CD section (requires login).
I sometimes check (read-only, i.e. select statements) the broker database to troubleshoot. For example, you might need to check the COMPONENT_PRESENTATIONS and CUSTOM_META tables to confirm configuration settings or troubleshoot something like encoding.
However, again there are much easier and supported ways to get at components and component presentations.
Just don't do it. Use the API.
as everybody said don't do it, i will give your example why...
We have upgraded one of our client to New Tridion Version, their 80% of the CD logic was implemented using SQL queries.
When we upgraded, whole system stopped working, as Broker DB schema was changed.(though we knew this in advance :))
It was a major issue understanding those and then converting queries to updated schema. But we took the harder side, understood the whole logic and converted almost all SQL Queries to broker queries, so that future upgrades wouldn't face same problem.
Secondly CoreService is for accessing CMS not broker.
