13

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!

  • Direct query is asking for troubles :) Maybe if you can explain more in detail what are your requirements we can propose alternative approaches. Not necessarily Core Service. Tridion is very rich in supported API's and options to risk going trough the unsupported route. – Puntero Mar 13 '13 at 11:40
  • Keep in mind that data in the broker is the result of a template executed at publish time. If this template outputs garbage, you get garbage - not all implementations publish structured data, many will output "pre-rendered" html... – Nuno Linhares Mar 13 '13 at 12:46
  • 2
    -1. Your question doesn't actually say what you are trying to achieve. Querying data from the broker using SQL is not a valid end in itself. Please share your actual problem. – Dominic Cronin Mar 13 '13 at 18:44
  • 2
    The API will easily give you a list of published components that are based on a given schema. I don't see any need to write SQL. And of course, if they aren't published yet, then querying the broker database directly still won't help you. – Dominic Cronin Mar 14 '13 at 10:18
  • 1
    Get SQL Developer from Oracle (it's free) or a trial version of Toad and reverse engineer the DB schema via these tools if you want more info about it. Nothing wrong with querying the DB. It is a sure way to debug. – Nickoli Roussakov Feb 9 '14 at 17:23
15

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:

  • Use BrokerQueries. Robert Curlette has some articles on his blog (e.g. http://www.curlette.com/?p=383), but you can also find examples on SDL Live Content
  • My preferred way: Publish the content + metadata to the broker as readeble XML (you define the structure based on the schemas), use the Tridion Broker API to query for the content/metadata and build up your own custom objects in your application.

Whatever you do, try to avoid a direct query at all times..

| improve this answer | |
  • Thank you, I will try to figure it out fromt he links you sent me. Where to get the broker API? sdltridionworld downloads are broken :( – Wallack Mar 13 '13 at 11:19
  • you can find the dll's in the /BIN folder of your Tridion 2011 installation. Look for Tridion.ContentDelivery.dll and Tridion.ContentDelivery.Configuration.dll – Kristof Camps Mar 13 '13 at 11:26
  • 1
    SDL Tridion World requires a login. If you do not have an account and are actively working on a client project, you could request a login account from SDL Customer Support. Note that Customer Support may ask for validation of work from the client. – Elena Serghie Mar 13 '13 at 11:26
  • 1
    Thank you all! I use the visitor account but honestly, I was hoping for something more close to the MSDN but yeah, a man can dream. – Wallack Mar 13 '13 at 11:42
  • 2
    If you go to SDL Tridion World, then choose the login option on the right, and provide the details you have gotten from Customer Support. You will see a DOWNLOADS option in the navigation. This contains a Documentation -> SDL Tridion 2011 SP1 link and on that page you can find the CHM files. The SDL Tridion 2011 SP1 Content delivery ASP.NET API will contain information about the Tridion.ContentDelivery.DynamicContent namespace which contains the Broker Query stuff. – Bart Koopman Mar 13 '13 at 16:49
9

+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:

Content Manager vs. Content Delivery

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.

Storage

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.

Inspecting Component Data

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.

| improve this answer | |
  • All good input. Just consider the DB to be read only when you use SQL. – Chris Summers Mar 14 '13 at 3:24
  • Thanks, Chris. I edited the answer to emphasize read-only access. – Alvin Reyes Mar 14 '13 at 5:45
4

Just don't do it. Use the API.

| improve this answer | |
3

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.