Grails: Uploading a File

Need your Grails application to grab a file? This is the easy form to do it:

 Upload a File:
<br />
<g:uploadform action="browse" controller="Input">
         <input name="myFile" type="file" />
         <input type="submit" value="Submit File" />
  </g:uploadform>

These are the different HTML input types you can use.

button              A clickable button (mostly used with a JavaScript to activate a script)
checkbox         Defines a checkbox
color                 Defines a color picker
date                  Defines a date control (year, month and day (no time))
datetime           Defines a date and time control (year, month, day, hour, minute, second)
datetime-local  Defines a date and time control (year, month, day, hour, minute, second, and fraction)
email                Defines a field for an e-mail address
file                    Defines a file-select field and a "Browse..." button (for file uploads)
hidden              Defines a hidden input field
image               Defines an image as the submit button
month               Defines a month and year control (no time zone)
number            Defines a field for entering a number
password         Defines a password field (characters are masked)
radio                Defines a radio button
range              Defines a control for entering a number whose exact value is not important
reset               Defines a reset button (resets all form values to default values)
search            Defines a text field for entering a search string
submit            Defines a submit button
tel                   Defines a field for entering a telephone number
text                Default. Defines a single-line text field (default width is 20 characters)
time               Defines a control for entering a time
url                  Defines a field for entering a URL
week              Defines a week and year control

BOE: Query Builder API to pull Printer Info

In this BOE post I'm pulling Printer information, instead of User information. The Printer status is a sub-property, so it's slightly more complicated pulling this information using a query.


This is the query I used to pull the printer status for each report.

SELECT SI_NAME,SI_PROCESSINFO.SI_PRINTER_INFO 
From CI_INFOOBJECTS 
WHERE SI_PROGID='CrystalEnterprise.Report'

Which results in an HTML page like this for every report.

I need a .csv file that returns the Report name (in this case, 'Life Insurance Enrollment'), the printer enable status ('false'), and the printer name if any ('Adobe PDF').

I use my function that takes my query above as a string. Then creates and returns a IInfoObjects using the query.

public IInfoObjects getUserReport(String myquery, String token) {         IInfoStore iStore = getInfoStore(token);  //Creates session using my login token         IInfoObjects reports = iStore.query(myquery);                        return reports; }

In my controller, I call the getUserReport function to get the object with my query data.

IInfoObjects reportinfo = BoeService.getUserReport(query,tok);         recordcount = reportinfo.getResultSize();    //grab the number of records

I have an array of IInfoObject (not to be confused with IInfoObjects) for each row of data from the query.

IInfoObject[] report = new IInfoObject[recordcount];                for ( x = 0; x < recordcount; x = x+1 ){                    report[x] = (IInfoObject) reportinfo.get(x);                    }                    String outdata;              //.csv file for output         File uf = new File(u.exportfile)         String newfile=null         newfile = u.exportfile                        BufferedWriter out = new BufferedWriter(new FileWriter(newfile))         //Create Header         out.write("status,reportname,printername\n");          //Pulling data for each row   for ( y = 0; y < recordcount; y = y+1 ){                /* The SI_PRINTER_INFO is stored under "Processing Info". So use the function         getProcessingInfo() to point to this data   */             IProcessingInfo processinfo = report[y].getProcessingInfo();                  /* Retrieve all the data under Processing Info   */             IProperties processinfo_detail= processinfo.properties();        /* Just retrieve the data called SI_PRINTER_INFO and using a new IProperties*/             IProperties printerdetail = processinfo_detail.getProperties("SI_PRINTER_INFO");           /*Now you can retrieve the subdata under SI_PRINTER_INFO using IProperty*/             IProperty prop1 = printerdetail .getProperty("SI_ENABLED"); //printer enable setting             IProperty prop2 = printerdetail .getProperty("SI_NAME"); //printer name /* Create a .csv row of Printer status, Report Name, and Printer name */ outdata = prop1.getValue().toString() + ',' + report[y].properties().getProperty(CePropertyID.SI_NAME).getValue() + ',' + prop2.getValue().toString();             outdata = outdata + '\n';   //carriage return between rows             out.write(outdata);  //write to file         }                 out.close()  //close writer

This is probably not the easiest way, but it's the first way I got it to work. Hopefully this will help you get started.

Misc Queries:

List of all possible relations:
SELECT SI_NAME FROM CI_SYSTEMOBJECTS WHERE si_relation_table_name='RELATIONS'
 
All folders
Select SI_ID, SI_NAME From CI_INFOOBJECTS Where SI_PROGID='CrystalEnterprise.Folder'

All groups
Select * from CI_SYSTEMOBJECTS Where SI_KIND='UserGroup'

All universes
Select SI_ID, SI_NAME, SI_WEBI, SI_KIND From CI_APPOBJECTS where SI_KIND ='Universe'

All reports
Select SI_ID, SI_NAME From CI_INFOOBJECTS Where SI_PROGID='CrystalEnterprise.Report'
And SI_INSTANCE=0

(RELATIONS) List all Universes used by the report called 'Financial Summary Page'
Select   SI_ID, SI_NAME, SI_OWNER From CI_APPOBJECTS Where
CHILDREN("SI_NAME='Webi-Universe' ","SI_NAME='Financial Summary Page'")

(RELATIONS) List all reports that have a Universe called 'Activity'
 SELECT * FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS
 Where PARENTS("SI_NAME='Webi-Universe'","SI_NAME ='Activity'")

Other RELATIONS examples:

User groups
Select SI_ID, SI_NAME From CI_SYSTEMOBJECTS
Where PARENTS("SI_NAME='UserGroup-User'", "SI_NAME='Admin'")

Report Shortcuts
SELECT SI_NAME,SI_PROCESSINFO.SI_RECORD_FORMULA
FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS
WHERE parents("si_name = 'InfoObject-Shortcuts'","SI_NAME = 'Job Entry Report'")


Check out these sites for more help:
BOE SDK Query Language Reference
Interesting Post about ProcessInfo
Another good post by Ted Ueda
InfoStore Query Builder program

BOE: Using Query Builder API to get User Info

As I've been learning Grails, I've also been reading up on the BOE API. The Business Objects query builder returns results in HTML format, which isn't friendly data. So I've been using Grails to run BOE queries and returning the results as .csv files.

The first query I ran and output wasn't too complicated. The second query was a bit more complicated.
These are some notes on what I did.

Every quarter or so, we need to verify that the BOE users are still active and no one is missing. The query builder allows you to pull the users with this query:

SELECT SI_NAME, SI_USERFULLNAME,SI_EMAIL_ADDRESS FROM CI_SYSTEMOBJECTS WHERE SI_KIND = 'User'

Which results in an HTML page like this for every user.

I wanted to pull this info into a .csv file.  (Email, Userfullname, Name). With a user on each line.

I have some code in Grails that connects to BOE using a token to login. Then I have a function that takes my query above as a string. Then creates and returns a IInfoObjects using the query.

public IInfoObjects getUserReport(String myquery, String token) {         IInfoStore iStore = getInfoStore(token);  //Creates session using my login token         IInfoObjects reports = iStore.query(myquery);                        return reports; }

In my controller, I call the getUserReport function to get the object with my query data.

IInfoObjects reportinfo = BoeService.getUserReport(query,tok);         recordcount = reportinfo.getResultSize();    //grab the number of records

I have an array of IInfoObject (not to be confused with IInfoObjects) for each row of data from the query.

IInfoObject[] report = new IInfoObject[recordcount];                 for ( x = 0; x < recordcount; x = x+1 ){                     report[x] = (IInfoObject) reportinfo.get(x);   //Set new object with a row of data                     }              //Strings to hold data for each column         String user;         String email;         String fullname;         String line;          //Create .csv file to export data to               File uf = new File(u.exportfile)       //original file         String newfile=null         newfile = u.exportfile                         BufferedWriter out = new BufferedWriter(new FileWriter(newfile))                    //Each row in object gets written to a string for ( y = 0; y < recordcount; y = y+1 )    {            user = report[y].properties().getProperty(CePropertyID.SI_NAME).getValue();    email = report[y].properties().getProperty(CePropertyID.SI_EMAIL_ADDRESS).getValue();    fullname = report[y].properties().getProperty(CePropertyID.SI_USERFULLNAME).getValue();                     //Set NULL variables to something else           if ( email == '') { email = 'NoEmail'}           if ( fullname == '') { fullname = 'NoFullName'}           if ( user == '') { user = 'NoUser'}           //Write data to the .csv file           line = email + ',' + fullname + ',' + user + '\n'           out.write(line); }        out.close()  //close writer

Oracle, ODBC 32, Windows 7, and Golden32

Last week I needed a connection to an Oracle database. I planned on using Golden to access the database once the connection was in place. It took awhile to figure out all the steps and how they come together, but eventually I got it work.  If anyone would find it helpful, these are the steps I took:

First, I need to create an ODBC connection to my Oracle database.  But when I click 'add', this is what I see....

SQL, SQL, and only SQL. That does me no good.

#1 -  I need to download and install an Oracle Client. But Golden needs a 32bit ODBC connection, so don't download 64bit.

#2 - Find the download for "Oracle Database 11g Release 2 (11.2.0.1.0) for Microsoft Windows (32-bit)" on the Oracle website. Or whatever the new release is.

#3 - Download it, and follow the instructions to install it.  This usually means running a setup.exe, and adding the Oracle folder to your Windows path.

• In Command prompt: PATH C:\Oracle; %PATH%
• Once installed, add tnsnames.ora in your Oracle admin folder (C:\app\<user>\product\11.2.0\client_1\network\admin)

#4 - Add an Oracle Connection to ODBC-32bit. The path to ODBC-32 in Windows7 is here:  C:\Windows\SysWOW64\odbcad32.exe

#5 - Open Golden, and have it look in your tnsnames.ora, to find your database(s) and connect.