GEL Database Operations

ccppmop1591
GEL can connect to one or more databases, and it is not limited to 
Classic PPM
 databases. Both Oracle and SQL Server are supported.
Example: JDBC Error
Most connection issues are a result of either login errors or JDBC issues. This example shows a JDBC error:
E:\Clarity\XOG\bin>gel -script gelsqlexample.xml ERROR 2005-08-31 16:45:40,549 [main] sql.SetDataSourceTag Could not load driver class: java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver...
If you see a similar error, find the necessary JDBC classes and copy them to the GEL classpath.
The GEL engine does not search the environment path for these files. GEL only looks in the
lib
folder (and the 
Classic PPM
 server classpath, if it has been installed on the local computer). For Oracle, ojdbc14.jar should be copied to the
lib
folder. For SQLServer, the Microsoft JDBC drivers must be made available to the GEL engine. Copy the files msbase.jar, mssqlserver.jar, and msutil.jar to the
lib
directory (after you have installed the latest JDBC driver from Microsoft, or copy the files from the lib directory within 
Classic PPM
).
Example: Connect to a 
Classic PPM
 Database and Print the Results of a Basic Query
<gel:script xmlns:core="jelly:core" xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary" xmlns:sql="jelly:sql"> <gel:parameter default="svong" var="ClarityUser"/> <gel:parameter default="svong" secure="true" var="ClarityPassword"/> <sql:setDataSource url="jdbc:oracle:thin:@localhost:1521:NIKU" driver="oracle.jdbc.driver.OracleDriver" user="${ClarityUser}" password="${ClarityPassword}"/> <sql:query var="result"> select name, unique_name from srm_projects </sql:query> <core:forEach trim="true" items="${result.rowsByIndex}" var="row"> <core:forEach var="field" items="${row}"> <gel:out>${field}</gel:out> </core:forEach> </core:forEach> <!--core:forEach trim="true" items="${result.rowsByIndex}" var="row"> <core:forEach var="columnName" items="${result.columnNames}" indexVar="i"> <field column="${columnName}">${row[i]}</field> <gel:out>${row[i]}</gel:out> </core:forEach> </core:forEach--> </gel:script>
The sql:setDataSource statement makes the connection to the database. Note the use of parameters for the login credentials. Using gel:parameter allows the UserID and Password to be set from within 
Classic PPM
. In addition, the secure="true" declaration masks the password in the user interface, if this script is called from a 
Classic PPM
 process.
sql:query encloses the query, and the two core:forEach loops cycle through the result. The first core:forEach loop runs through the rows; the embedded core:forEach reads the columns in each row. The results set for this code print one field for each line. The output looks similar to this example:
Project ABC P001 Consumer Confidence Project P002 John's Super Special Project P003 .
One alternative solution for this issue is to programmatically create rows of data. This example is for a query that returns three columns for each row. By using step="3", you can process one logical row at a time. Each item is referred to by using an index offset.
<core:forEach trim="true" items="${queryResult.rowsByIndex}" var="row"> <!-- 3 fields per row, so jump by 3 to build the next row --> <core:forEach var="field" items="${queryResult.columnNames}" indexVar="i" step="3"> <file:line> <file:column value="${row[i]}"/> <file:column value="${row[i+1]}"/> <file:column value="${row[i+2]}"/> </file:line> </core:forEach> </core:forEach>