Clarity

Hi Expert's

I'm getting below error message while running simple gel script from my local PC, I'm not able to get connect with Oracle Db even i have kept all the 'c-'.*.jar file insdie " xog/lib"  folder and  C:\Program Files (x86)\Java\jre1.7.0_51\lib folder

Gel Script :

 <sql:setDataSource url="jdbc:clarity:oracle://******" driver="com.ca.clarity.jdbc.oracle.OracleDriver"
         user="${XOGusername}" password="${XOGpassword}"/> 

            <sql:query var="result" dataSource="niku">
                        select name, unique_name from srm_projects
            </sql:query>

Could you please help me on this.

Thanks,

Have you included your DB SID correctly?  (in the bit of code that you have *********** removed)

Hi David, Based on your suggestion, i have corrected the DB SID, but now i'm getting new error. [CA Clarity][Oracle JDBC Driver]This driver is locked for use with embedded applications. Gel Script : Please help me on this.

Please see my reply to you here: Re: Unable to get connection , Data Source Invalid

The driver is a licensed product for use on the Clarity server by the Clarity services.

Either use another driver that you are licensed to use outside of this environment (such as Oracle's own) or run the script using the gel shell script / batch file that exists in the Clarity server /bin folder (at least, I think that will avoid that error).

Hi Nick, I have customized my script like below.

<sql:setDataSource url="jdbc:oracle:thin:@machine.ECC1Q.***.COM:1521:niku"
  driver="oracle.jdbc.driver.OracleDriver" user="myuserid" password="password" var="clarityDS" />


     <sql:query dataSource="${clarityDS}" var="queryResults"> 
                       SELECT 1 from dual
            </sql:query>

I'm getting following error message.

You're trying to call a SQL query using Clarity's server bundled/licensed JDBC driver.  This driver isn't included in the XOG client or for use on your local PC.

You can try to source and use another JDBC driver and URL (like Oracle's ojdbc) or else if you are an On Premise customer the script should/could be run from the server's own $NIKU_HOME/bin folder instead (which I think will then give it access to the drivers being called).

Hi Nick, Thanks for help! I switched to using the oracle JDBC driver and it worked! ....:).

Gel Script

---------------

<sql:setDataSource url="jdbc:oracle:thin:@machine.xxxx.***.COM:1521:CLRTYDEV" 
driver="oracle.jdbc.driver.OracleDriver" user="userid" password="password" var="clarityDS" />

Install the Oracle Instant Client (get the right one 32/64 bit) on your computer that you are running GEL scripts on.

This is the datasource I use to run gel scripts on my laptop.

<!-- Connecting to Database -->
<sql:setDataSource url="jdbc:oracle:thin:@DatabaseServerName:1521:niku" driver="oracle.jdbc.driver.OracleDriver" user="DatabaseUserName" password="DatabaseUserPassword" var="clarityDS" />
<sql:query dataSource="${clarityDS}" var="queryResults">

If in On-Demand, you will have to VPN in to get access to the database.

I used this GEL script snippet to set the correct datasource base if I am on my laptop or on the server (Note: I haven't tested this on 14. but it worked on 13!).

<!-- Get the configuration manager for DB and XOG -->
<core:invokeStatic var="caConfigMngr" className="com.niku.union.config.ConfigurationManager" method="getInstance" />

<!-- Database Source -->
<core:catch var="dataSourceException">
<gel:setDataSource dbId="niku" />
</core:catch>
<core:if test="${dataSourceException != null}">
<core:set var="dbDatabase" value="${caConfigMngr.getProperties().getDatabase(0)}" />
<core:set var="dbHost" value="${dbDatabase.getHost()}" />
<core:set var="dbUrl" value="${dbDatabase.getUrl()}" />
<core:set var="dbHost" value="${dbUrl.replaceAll('.*//', '').replaceAll(':.*','')}" />
<core:set var="dbPort" value="${dbUrl.replaceAll('^.*//+.*?:', '').replaceAll(';.*','')}" />
<core:set var="dbSid" value="${dbUrl.replaceAll('^.*SID=', '').replaceAll(';.*','')}" />
<core:set var="dbDriver" value="oracle.jdbc.driver.OracleDriver" />
<core:set var="dbUser" value="${dbDatabase.getUsername()}" />
<core:set var="dbPassword" value="${dbDatabase.getPassword()}" />
<core:set var="dbUrl" value="jdbc:oracle:thin:@//${dbHost}:${dbPort}/${dbSid}" />
<gel:log message="dbUser = ${dbUser} | dbUrl = ${dbUrl}" />
<sql:setDataSource url="${dbUrl}" driver="${dbDriver}" user="${dbUser}" password="${dbPassword}" />
</core:if>

And make sure you laptop gel has access to the Oracle JDBC driver jar.

V/r,

Gene

FYI, for anyone needing this. I finally got this working for On-Demand 14.4 oracle sql connection command line. I did copy a bunch of jar files, to add to my lib folder. The last one i added was the ojdbc5.jar and now it works. whether it was that or the others, not sure, but it works and that's all i needed. 

<gel:script xmlns:core="jelly:core"
xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
xmlns:sql="jelly:sql">
    <gel:parameter default="development user id" var="DevDBUser"/>
    <gel:parameter default="production user id" var="ProdDBUser"/>
    <gel:parameter default="the users db password" secure="true" var="DatabaseUserPassword"/>
    <gel:parameter default="dev db ip address" secure="true" var="developmentIP"/>
    <gel:parameter default="prod db ip address" secure="true" var="productionIP"/>

    <!-- WORKING DB Connection – just change the params as needed -->
    <sql:setDataSource url="jdbc:oracle:thin:@//${productionIP}/niku" driver="oracle.jdbc.driver.OracleDriver" user="${ProdDBUser}" password="${DatabaseUserPassword}" var="clarityDS" /> 

    <sql:query dataSource="${clarityDS}" var="result">
            SELECT ID, NAME, CODE FROM inv_investments
    </sql:query>
    <core:forEach items="${result.rows}" var="row">
        <gel:out>${row.ID}, ${row.name}, ${row.code}</gel:out>
    </core:forEach>
</gel:script>

Hope this helps someone. 

Keri