third party database content lessons/examples

jim.lorenz's Avatar

jim.lorenz

24 Aug, 2015 03:10 PM

I need to include dynamic content from an external database. What's the recommended approach and are there any lessons/examples?

  1. 1 Posted by jim.lorenz on 24 Aug, 2015 06:30 PM

    jim.lorenz's Avatar

    ok, I've found some other discussions but none of them are current. Plus the references to web services and feeds seem way to complicated. Embedded code seems like most straight forward (asp.net). However, what's the current recommended practice? Any complete examples?

  2. Support Staff 2 Posted by Tim on 25 Aug, 2015 03:13 PM

    Tim's Avatar

    Hi Jim,

    There are a few different ways you could potentially do this (as you noticed), but if you are just looking to grab small bits of content from your external database then I agree that you can look to embed code within the content of your pages. Doing so will require that you make use of passthrough code sections to surround your asp.net code snippets. The end result will be that the external content will not be displayed within Cascade, but will be displayed once published to your .NET web server(s).

    For example, your code snippet might end up looking something like this:

    <!--#passthrough
    (your .NET code goes here)
    #passthrough-->
    

    As far as best practices are concerned, it's generally best to include these types of code snippets within Blocks and/or Formats as opposed to entering directly into a WYSIWYG editor. The reason for that is that since most end users don't have the ability to edit the HTML source directly, they could unintentionally delete this code without ever knowing it was there (since the passthrough code sections are essentially HTML comments that won't appear in the rendering).

    If you do go the .NET route, you'll also need to add your .NET header to your Template(s) using the passthrough-top code sections.

    I looked around for some examples of this but wasn't able to find anything. I'm sure other folks have done something similar so perhaps they can chime in here, but in the meantime feel free to comment back with any followup questions and I'll do what I can to provide more recommendations.

    Thanks!

  3. 3 Posted by jim.lorenz on 25 Aug, 2015 09:26 PM

    jim.lorenz's Avatar

    Hi Tim,

    Thanks so much for your attention. It’s too bad there isn’t a way to write a custom index which accessed the external db and returned an xml result set.

    Cheers

    Jim

  4. Support Staff 4 Posted by Tim on 25 Aug, 2015 09:30 PM

    Tim's Avatar

    Hey Jim,

    No problem.

    It’s too bad there isn’t a way to write a custom index which accessed the external db and returned an xml result set.

    Does your external database have an option to produce an XML file that you can place on a server somewhere? If so, you could easily do the following:

    • Create a new Feed Block and just provide it with the URL to your XML file (that was generated from the external database content)
    • Attach this Feed Block to any Template, Configuration Set, or Page region as needed
    • Optionally write a XSLT or Velocity Format to transform that XML file into XHTML elements for displaying on a web page

    Let me know if this helps! This is a very common method of consuming external content and working with it inside the system.

  5. 5 Posted by Bryce Roberts on 26 Aug, 2015 12:38 PM

    Bryce Roberts's Avatar

    You can write either custom XSLT with an extension or Velocity format (reflection) to return to the transform context an JDOM object. You can directly output this into the page and transform again with a page level XSLT, or you can continue to process the object.

  6. 6 Posted by jim.lorenz on 26 Aug, 2015 02:16 PM

    jim.lorenz's Avatar

    Bryce,
    This sounds interesting. However, as a Cascade “newbe” I don’t understand. I am writing my own XSLT at the page level and I can code c# aspx. I’d really appreciate seeing an example or even a diagram of your approach would help.

    Cheers

    jim

  7. 7 Posted by Roberts, Bryce ... on 27 Aug, 2015 02:12 AM

    Roberts, Bryce Leitner's Avatar

    Hello,

    Some example code:

    <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0" xmlns:db="http://www.stoneridge.net/XSLT/db" xmlns:xalan="http://xml.apache.org/xalan">
        <xsl:output indent="yes"/>
        <!-- Template for Selecting the root folder -->
        <xsl:variable name="connectStr" select="string(//sql/connect)"/>
        <xsl:variable name="userStr" select="string(//sql/user)"/>
        <xsl:variable name="passwordStr" select="string(//sql/password)"/>
        <xsl:variable name="sqlStatementStr" select="string(//sql/statement)"/>
        <xsl:variable name="dbConn" select="db:getConn($connectStr, $userStr, $passwordStr)"/>
        <xsl:variable name="resultXml" select="db:getResult($sqlStatementStr, $dbConn)"/>
        <xalan:component functions="getConn,getResult" prefix="db">
            <xalan:script lang="javascript"><![CDATA[
                var dbConnection, dbStatement, dbResultSet;
    
    
                function getResult(statment, dbConn){
                    if(statment && dbConn){
                        returnDoc = createDoc();
                        try {
                            var statements = dbConn.createStatement();
                            var resultSet = statements.executeQuery(statment);
                            getResultSetMetaData(resultSet, returnDoc);
                            getResultSet(resultSet, returnDoc);
                            return returnDoc;
    
                        } catch(e) {
                            return returnDoc.appendChild(returnDoc.createTextNode(e));
                        }
                    }
                }
    
                function getResultSet(rs, DOMDoc){
    
                    resultSetEl = DOMDoc.createElement("resultset");
                    DOMDoc.getDocumentElement().appendChild(resultSetEl);
    
                    while (rs.next()) {
                        result = DOMDoc.createElement("result");
                        resultSetEl.appendChild(result);
                        for (var i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
                            colName = rs.getMetaData().getColumnName(i);
                            column = DOMDoc.createElement(colName);
                            result.appendChild(column);
                            o = rs.getObject(i);
                            if(o != null){
                                //column.appendChild(DOMDoc.createTextNode(o.toString()));
                                column.appendChild(DOMDoc.createTextNode(rs.getString(i)));
                            } else {
                                column.appendChild(DOMDoc.createTextNode(""));
                            }
                        }
                    }
                };
    
    
                function getResultSetMetaData(rs, DOMDoc){
                    md = rs.getMetaData();
                    dbResultSetColumnName = [];
                    meta = DOMDoc.createElement("meta");
                    DOMDoc.getDocumentElement().appendChild(meta);
                    columnCount = md.getColumnCount();
                    columnCountEl = DOMDoc.createElement("columncount");
                    columnCountEl.appendChild(DOMDoc.createTextNode(columnCount));
                    meta.appendChild(columnCountEl);
                    for(var i = 1; i <= columnCount; i++){
                        var columnEl = DOMDoc.createElement("column");
                        var columnNameEl = DOMDoc.createElement("name");
                        columnNameEl.appendChild(DOMDoc.createTextNode(md.getColumnName(i)));
                        var columnTypeEl = DOMDoc.createElement("type");
                        columnTypeEl.appendChild(DOMDoc.createTextNode(md.getColumnTypeName(i)));
                        var columnPrecisionEl =  DOMDoc.createElement("precision");
                        columnPrecisionEl.appendChild(DOMDoc.createTextNode(md.getPrecision(i)));
                        columnEl.appendChild(columnTypeEl);
                        columnEl.appendChild(columnNameEl);
                        columnEl.appendChild(columnPrecisionEl);
                        meta.appendChild(columnEl);
                    }
                }
    
                function createDoc() {
                    docFactory = new Packages.javax.xml.parsers.DocumentBuilderFactory.newInstance();
                    docBuilder = docFactory.newDocumentBuilder();
                    doc = docBuilder.newDocument();
                    rootElement = doc.createElement("db");
                    doc.appendChild(rootElement);
                    return doc;
                }
    
    
    
                function getConn(connStr, userStr, passwordStr){
                    try{
                        var dbConnection = new Packages.java.sql.DriverManager.getConnection(connStr, userStr, passwordStr);
                        return dbConnection;
                    } catch(e) {
                        return e;
                    }
                };
    
                function cleanUp(){
                    dbConnection = null;
                    dbStatement = null;
                    dbResultset = null;
                }
    
                var driverSet = ["sun.jdbc.odbc.JdbcOdbcDriver", "net.sourceforge.jtds.jdbc.Driver", "com.mysql.jdbc.Driver", "oracle.jdbc.driver.OracleDriver"];
    
                function isDriverLoaded(driverClass){
                    var currentDrivers = getDrivers();
                    for(var i = 0; i < currentDrivers.length ; i++){
                        if(currentDrivers[i] == driverClass){
                            return true;
                        }
                    }
                    return false;
                };
    
                function getDrivers() {
                    var drivers = [];
                    var getSetDriverManager = new Packages.java.sql.DriverManager.getDrivers();
                    for (e = getSetDriverManager ; e.hasMoreElements() ;){
                        drivers.push(e.nextElement().getClass().getName());
                    }
                    return drivers;
                }
    
                function getDriversList(){
                    return getDrivers().join(' ,');
                }
    
                function loadDrivers() {
                    try{
                    for(var i = 0; i < driverSet.length ; i++){
                        if(!isDriverLoaded(driverSet[i])){
                            Packages.java.lang.Class.forName(driverSet[i]);
                        }
                    }
    
                    } catch(e) {return e.toString();}
                    return getDrivers();
                }
    
                function unLoadDrivers() {
                    /*THIS DOES NOT WORK AS EXCEPTED FOR LOADING AND UNLOADING*/
                    /*Once unloaded the driver is no longer able to be dynamically reloaded because the package announcement is only one time */
                    return getDrivers();
                }
    
    
    
    
                function SQLite() {
                    try{
                        Packages.java.lang.Class.forName('org.sqlite.JDBC');
                    } catch(e) {}
                    this.connection = null;
                    this.connect = function(sDBFileName) {
                        try {
                            this.connection = new Packages.java.sql.DriverManager.getConnection('jdbc:sqlite:' + sDBFileName);
                            this.statement = this.connection.createStatement();
                        } catch(e) {
                            this.connection = null;
                            this.statement = null;
                        }
                        return null;
                    };
                    this.close = function() {
                        if (!this.connection) return;
                        this.connection.close();
                        this.statement.close();
                        this.connection = null;
                        this.statement = null;
                        return null;
                    };
                    this.query = function(sQuery) {
                        if (!this.statement) return;
                        var aResultSet = [];
                        try {
                            var oResultSet = this.statement.executeQuery(sQuery);
                        } catch (e) {}
                        if (oResultSet) {
                            while (oResultSet.next()) {
                                var aRowData = {};
                                for (var c = 1; c <= oResultSet.columnCount; c++) {
                                var sColumnName = oResultSet.getColumnName(c);
                                aRowData[sColumnName] = oResultSet.getString(sColumnName);}
                                                                    aResultSet.push(aRowData);
                             }
                             oResultSet.close();
                         }
                             return aResultSet;
                         };
                };
                function getSQLite(){
                    var returnObj;
                    try {
                        returnObj = new SQLite();
                    } catch(e) {
                        returnObj = e;
                    }
                    return returnObj;
                }
    
            ]]></xalan:script>
        </xalan:component>
    
    </xsl:stylesheet>
    
  8. 8 Posted by jim.lorenz on 27 Aug, 2015 12:15 PM

    jim.lorenz's Avatar

    Thanks guys – I’ll study, and hopefully learn from, this.

    Cheers

    jim

  9. Support Staff 9 Posted by Tim on 27 Aug, 2015 02:42 PM

    Tim's Avatar

    Thanks for chiming in and providing that example, Bryce!

    Jim, let us know how things go and certainly reach out here again if you come up with more questions. Have a good one!

  10. Tim closed this discussion on 27 Aug, 2015 02:42 PM.

Comments are currently closed for this discussion. You can start a new one.

Keyboard shortcuts

Generic

? Show this help
ESC Blurs the current field

Comment Form

r Focus the comment reply box
^ + ↩ Submit the comment

You can use Command ⌘ instead of Control ^ on Mac