Here is a catch-all post about all the new toys I'm playing with. I give each of these a thumbs-up.
Are you looking for a VPN? This one isn't free, but for those who are willing to pay for awesome-ness, this is the one. Check out Private Internet Access.
Yes, not the most creative name, but they really get the job done. The price isn't bad, and while they don't have a free trial, you can pay a small amount to try it for a month. That's what I did, and I'm sold.
It's easy to use and works great. I'm no expert on VPN's, so feel free to do more homework, but I would start with this one.
Next is phones....
I'm a fan of Skype. Again, it's easy to use and you can't beat the price. For 10$ you can call any number in US/Canada for 3 months. Target and other such stores even sell $10 gift cards that can be used towards Skype, so add it to your Christmas list.
I also use a pre-paid cellphone because I don't use a cell that much. I went with page plus cellular because you can hold onto purchased minutes for a whopping 120 months (!). They use verizon towers, so I haven't had a problem using my phone most places.
It has been finicky at times, when calling out. But only once in a blue moon. And frankly, you can't beat the price. I have to buy my minutes online, since they don't sell cards in my area, but it's very easy and quick to do.
So, if like me, you don't use a cell very often. And you want to hold on to minutes as long as you can before they expire, I would check them out. They have different plans, or you can pay by the minute.
I was in the market for a firewall, and I'm currently using Comodo Firewall. So far, I'm really liking it. They have a free version, which is great. And it comes with anti-virus too, if you need it.
Okay, that's all I got for now. Just my 2 cents, hope it helps.
Tuesday, February 26, 2013
Tuesday, January 8, 2013
Datastage - Loading MySQL Text into Oracle Varchar
I had to create a simple job to load data from a MySQL database into an Oracle database.
Everything was working fine until I had to move MySQL data that was in a Text format, into an equivalent Oracle field.
I grab the data in my first stage, and move it into a field of type Char, with the necessary byte size. The data then gets loaded into CLOB fields in Oracle, and in Datastage I label them as type 'Char' with the necessary byte size.
So far, this seems to be working well.
---------------------------
You could also try using a User-created SQL statement, that casts the MySQL text data into characters.
Example: cast(description as char(2000)) as description
Then in DataStage, I have the data coming in as Varchar, and loading into Varchar2(2000) fields in Oracle. Keep in mind that I know these fields aren't very big, even though they are Text fields.
Perhaps not the most elegant, but it got the job done.
It also worked to have the data coming in as Char, and loading as Char. Make sure the field size is large enough.
Sunday, December 16, 2012
Reading Datastage Logs
When using IBM InfoSphere DataStage and QualityStage, it's important to know how to use the logs. They can answer a lot of questions, and help isolate problems.
This is a simple introduction to reading the logs in Director.
1. Change the Refresh
You'll learn pretty quick that the default refresh is way too short. The page will refresh every 5 seconds, making it practically impossible to read anything.
First thing you should do, is lengthen the refresh time, or change it to manuel refresh only.
Go to Tools -> Options... -> General Tab -> change the refresh time to 60 seconds or disable it.
2. Filter/Search for Warnings/Abort
Open your log file in Director. Go to View -> Filter Entries...
This will open up a box where you can filter for particular errors (ex. Warnings). You can also use Ctrl-F to search for a specific word or phrase within your log. You can also have it show more or less of the logs.
4. Check the Details
When jobs abort, check the details. This is obvious, but some people gloss over these. Often there is more information about what caused the job to abort. Bad queries may display an error of bad syntax. A record with bad data may cause the job to abort, and often the record's data will be displayed in the error, so you can find the record and fix it, or delete it.
This is very basic information, but this is the best place to start when troubleshooting. For any just beginning in Datastage, hopefully this was helpful and Good Luck!
This is a simple introduction to reading the logs in Director.
1. Change the Refresh
You'll learn pretty quick that the default refresh is way too short. The page will refresh every 5 seconds, making it practically impossible to read anything.
First thing you should do, is lengthen the refresh time, or change it to manuel refresh only.
Go to Tools -> Options... -> General Tab -> change the refresh time to 60 seconds or disable it.
2. Filter/Search for Warnings/Abort
Open your log file in Director. Go to View -> Filter Entries...
This will open up a box where you can filter for particular errors (ex. Warnings). You can also use Ctrl-F to search for a specific word or phrase within your log. You can also have it show more or less of the logs.
3. Check Related Log(s)
When you are checking a log for a sequence, that has several jobs, you can right-click a "Run Job" line, to see the log for that specific job. Click on Related Log.
4. Check the Details
When jobs abort, check the details. This is obvious, but some people gloss over these. Often there is more information about what caused the job to abort. Bad queries may display an error of bad syntax. A record with bad data may cause the job to abort, and often the record's data will be displayed in the error, so you can find the record and fix it, or delete it.
This is very basic information, but this is the best place to start when troubleshooting. For any just beginning in Datastage, hopefully this was helpful and Good Luck!
Monday, December 10, 2012
Remote Desktop .exe
In Windows, hit Start, and Run... then mstsc.exe to open Remote Desktop.
Done!
I always forget this. What does it stand for? Microsoft Terminal Services Client.
Or maybe it's easier to remember as... My STupid Sucky Computer
The program is usually located here:
C:\WINDOWS\system32
Done!
I always forget this. What does it stand for? Microsoft Terminal Services Client.
Or maybe it's easier to remember as... My STupid Sucky Computer
The program is usually located here:
C:\WINDOWS\system32
Thursday, November 15, 2012
PHP script to search IP's and find matching URL's
For those who saw my Mobotix camera post, you probably have an idea of my love for security cameras and the like.
While trying to find more Mobotix cameras using Google, I quickly ran out of search results to check out. But I knew there had to be more out there that Google just isn't finding.
I decided there must be a simple script you could run that searches for URL's that match a particular search string (just like Google does). But you could have it search a particular range of IP's, routinely checking one for the search string.
It wouldn't be pretty or efficient of course, but it would be simple and I could probably code it.
Let me preface this by saying, I'd never coded in PHP before, which might already be obvious.
So, my little PHP search script looks like this:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>IP Check</title>
</head>
<body>
<a href ="index.php">Home</a>
<p>IP Check<p>
<p>
IP format XXX.XXX.XXX. (ex. 70.147.100.)<p>
X = lower number<p>
max = upper limit <p>
Filename to write to.<p><p><p>
<form action="camcheck.php" method="post">
Enter IP: <input name="ip" size="12"><p>
Enter X: <input name="x" size="3">
Enter Max: <input name="max" size="3"><p>
Filename: <input name="myfile" size="20">
<input type="submit" value="Start">
</form>
<p>
<img src="magnify.jpg" alt="magnify"/>
<?php
?><p><p>
</body>
</html>
$myFile = $_POST['myfile'];
$fh = fopen($myFile, 'w') or die("Can't open file"); //open file
for ($x =(int)$_POST['x']; $x <= (int)$_POST['max']; $x++)
{
$url= $_POST['ip'].strval($x);
//echo $url;
$valid = @fsockopen($url, 80, $errno, $errstr, 4); //check url
if (!$valid) {
// Output Error Message, if you care
// echo '<p>not valid<p>';
//echo "$errstr ($errno)<br />\n";
//$returnstring = $url." Not Valid $errstr ($errno)> \n
//$returnstring = "X = ".$x;
//fwrite($fh,$returnstring);
}
else {
// Output Success Message!
fputs($valid, "\nHEAD $path HTTP/1.1\r\nHost: $url[host]\r\n\r\n"); //socket opened
$headers = fread($valid, 4096);
$returnstring = "\n".$url." Valid\n";
fwrite($fh,$returnstring); //write valid IP to file
fwrite($fh,$headers); //write header info
fwrite($fh,"\n");
}
}
echo "\n Program Complete. Results sent to ".$myFile;
fclose($fh);
Here are some example results:
I find it interesting just reading the different headers, and learning what they mean. You get an idea of what sites are located in certain IP's.
I haven't played with the script much, but I think it could be even more fun with some minor tweaks.
Right now it won't let me search more than 100 at a time before timing out. I haven't tried fixing that just yet.
Let me know if you have any hints/tips/fixes. I'd love to hear them.
While trying to find more Mobotix cameras using Google, I quickly ran out of search results to check out. But I knew there had to be more out there that Google just isn't finding.
I decided there must be a simple script you could run that searches for URL's that match a particular search string (just like Google does). But you could have it search a particular range of IP's, routinely checking one for the search string.
It wouldn't be pretty or efficient of course, but it would be simple and I could probably code it.
Let me preface this by saying, I'd never coded in PHP before, which might already be obvious.
So, my little PHP search script looks like this:
You enter an IP address (minus the last 3 digits). This of course means that the search range is very limited.
Then you enter the IP range you want it to search. In the example above the IP's searched are:
216.172.100.1 through 216.172.100.80.
Then you enter a filename for the script to save its results to. If any valid IP's have a url that matches the search string, their header information is written to the file.
Like I said, it is very limited in what it can do, but not bad for my first PHP script, written on the fly. It's a starting place for anyone who wants to expand on it.
Here is the code. It's broken up into two files.
index.php
<html>
<head>
<title>IP Check</title>
</head>
<body>
<a href ="index.php">Home</a>
<p>IP Check<p>
<p>
IP format XXX.XXX.XXX. (ex. 70.147.100.)<p>
X = lower number<p>
max = upper limit <p>
Filename to write to.<p><p><p>
<form action="camcheck.php" method="post">
Enter IP: <input name="ip" size="12"><p>
Enter X: <input name="x" size="3">
Enter Max: <input name="max" size="3"><p>
Filename: <input name="myfile" size="20">
<input type="submit" value="Start">
</form>
<p>
<img src="magnify.jpg" alt="magnify"/>
<?php
?><p><p>
</body>
</html>
The index file is basically just a bunch of html text boxes. The info captured is then used by the second file.
camcheck.php
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>IP Check</title>
</head>
<body>
<?php
$path = "/control/userimage.html"; // url search string
<html>
<head>
<title>IP Check</title>
</head>
<body>
<?php
$path = "/control/userimage.html"; // url search string
$myFile = $_POST['myfile'];
$fh = fopen($myFile, 'w') or die("Can't open file"); //open file
for ($x =(int)$_POST['x']; $x <= (int)$_POST['max']; $x++)
{
$url= $_POST['ip'].strval($x);
//echo $url;
$valid = @fsockopen($url, 80, $errno, $errstr, 4); //check url
if (!$valid) {
// Output Error Message, if you care
// echo '<p>not valid<p>';
//echo "$errstr ($errno)<br />\n";
//$returnstring = $url." Not Valid $errstr ($errno)> \n
//$returnstring = "X = ".$x;
//fwrite($fh,$returnstring);
}
else {
// Output Success Message!
fputs($valid, "\nHEAD $path HTTP/1.1\r\nHost: $url[host]\r\n\r\n"); //socket opened
$headers = fread($valid, 4096);
$returnstring = "\n".$url." Valid\n";
fwrite($fh,$returnstring); //write valid IP to file
fwrite($fh,$headers); //write header info
fwrite($fh,"\n");
}
}
echo "\n Program Complete. Results sent to ".$myFile;
fclose($fh);
?>
<p><p>
</body>
<p><p>
</body>
</html>
In the camcheck.php file, you'll see that the url search string is hard-coded into $path. It would probably be easy to make that another textbox, so you can change what url string to search for.
"/control/userimage.html" is a typical Mobotix web cam url. Which is what I created the script to find.
When you run the script, it saves the header results to a file on your webserver.
Here are some example results:
|
216.172.104.49 Valid HTTP/1.1 302 Found Date: Thu, 15 Nov 2012 16:32:40 GMT Server: Apache Location: https://billing.dathorn.com/control/userimage.html Connection: close Content-Type: text/html; charset=iso-8859-1
216.172.104.50 Valid
HTTP/1.1 404 Not Found Date: Thu, 15 Nov 2012 16:32:42 GMT Server: LiteSpeed Connection: close Content-Type: text/html 216.172.104.55 Valid HTTP/1.1 302 Found Date: Thu, 15 Nov 2012 16:32:43 GMT Server: LiteSpeed Connection: Keep-Alive Keep-Alive: timeout=5, max=100 Location: https://2/control/userimage.html 216.172.104.56 Valid HTTP/1.1 404 Not Found Date: Thu, 15 Nov 2012 16:32:43 GMT Server: LiteSpeed Connection: close Expires: Mon, 26 Jul 1997 05:00:00 GMT Last-Modified: Thu, 15 Nov 2012 16:32:43 GMT Cache-Control: no-store, no-cache, must-revalidate, post-check=0, pre-check=0 Pragma: no-cache P3P: CP="NON CURa ADMa DEVa TAIa CONi OUR DELa BUS IND PHY ONL UNI PUR COM NAV DEM STA" Content-Type: text/html; charset=iso-8859-1 Set-Cookie: xid=788c930fc7639731a0609de479e12ae9; path=/ Set-Cookie: xid=788c930fc7639731a0609de479e12ae9; path=/; domain=www.comicjumps.com Set-Cookie: xid=788c930fc7639731a0609de479e12ae9; path=/; domain=www.comicjumps.com Set-Cookie: RefererCookie=deleted; expires=Thu, 01-Jan-1970 00:00:01 GMT; path=/; domain=www.comicjumps.com Set-Cookie: store_language=deleted; expires=Thu, 01-Jan-1970 00:00:01 GMT Set-Cookie: store_language=US; expires=Fri, 15-Nov-2013 16:32:43 GMT |
I find it interesting just reading the different headers, and learning what they mean. You get an idea of what sites are located in certain IP's.
I haven't played with the script much, but I think it could be even more fun with some minor tweaks.
Right now it won't let me search more than 100 at a time before timing out. I haven't tried fixing that just yet.
Let me know if you have any hints/tips/fixes. I'd love to hear them.
Tuesday, November 13, 2012
Q. Why did the Geek cross the road?
A. To get a better look at the Accessible Pedestrian Signals (APS).
Have you been at intersections and heard the push button talking to you (Wait. Wait. Wait.), or chirping like a manic sparrow? These crossings have always peaked my interest. How do they work? Who makes them? Can I make the little speaker yell "RUUUUUUN!" at a specified time of my choosing?
These questions would pop in my head every time I drove by, so I decided it was time to do a little research.
First off, as far as I can tell, these 'talking' pedestrian signals are currently (2012) only being manufactured by one company. Yes, just one. So it would seem this is a fairly new technology. The company behind this genius tech is Polara Engineering out of Corona, California. They create products used by many industries, including Traffic Management.
I was hoping, and happy to see that their website includes all the installation and operation manuals for their traffic products. From here you can look at pages and pages of information.
I'm not going to go into great detail. You can read the manuals yourself, if you want to learn more.
Here is the Basic Setup:
1. Their software communicates from their Central Control Unit to the individual intersection push buttons with digital data, over two low voltage wires. These wires also supply all of the power for the pushbuttons.
2. The Central Control Unit is installed in the Traffic Control Cabinet.
In my town, they are getting painted up by local artists.
3. The push buttons (EPBS) are installed on the traffic poles.
4. Run a system check to make sure everything can talk to each other.
5. Set preferences with E-Configurator.
6. Done!
So what I'm interested in mainly, is the audio setup. This is part of the software, which is loaded in the Central Control Unit.
You can configure the setup using an E-Configurator.
From the manual:
"The E-Configurator is a battery operated hand-held device for making changes to the operating characteristics of Polara Navigator and EZ Communicator Push Button Stations (EPBS).
It uses two-way infrared communication while held in front of a EPBS and pointed toward the red LED above the EPBS arrow button."
Sounds easy enough. Of course, it is password protected (factory default is AAAA).
Where could you find an E-configurator? Well, there are places, if you look around.
Using the Navigator Configurator (E-Config), you have access to a lot of setup options. As far as audio goes, you can change volume levels, and things like that. But what about a new audio file altogether?
Short answer.. No.
"Programming custom messages in the field requires a laptop, USB A/B cable and
the Polara EPBS Audio Programmer software. The software must be installed and
messages prepared prior to installation of the units on the intersection.
[...]
if units are to be programmed in the field, a laptop with the proper software and voice messages must be prepared prior to installation on the intersection."
Polara has their own Youtube video that walks you through custom audio setup. But it's all done prior to installation. I was disappointed by this, but reading the manuals was still pretty interesting.
For example, the system can vary the volume of the audio based on how much ambient noise there is. And an 'extended push' option, where it will say additional information (street names), if the button is pushed and held longer.
Just know the internet is full of manuals. So if there is a gadget you have an interest in, it's worth taking a look.
Have you been at intersections and heard the push button talking to you (Wait. Wait. Wait.), or chirping like a manic sparrow? These crossings have always peaked my interest. How do they work? Who makes them? Can I make the little speaker yell "RUUUUUUN!" at a specified time of my choosing?
These questions would pop in my head every time I drove by, so I decided it was time to do a little research.
First off, as far as I can tell, these 'talking' pedestrian signals are currently (2012) only being manufactured by one company. Yes, just one. So it would seem this is a fairly new technology. The company behind this genius tech is Polara Engineering out of Corona, California. They create products used by many industries, including Traffic Management.
I was hoping, and happy to see that their website includes all the installation and operation manuals for their traffic products. From here you can look at pages and pages of information.
I'm not going to go into great detail. You can read the manuals yourself, if you want to learn more.
Here is the Basic Setup:
1. Their software communicates from their Central Control Unit to the individual intersection push buttons with digital data, over two low voltage wires. These wires also supply all of the power for the pushbuttons.
2. The Central Control Unit is installed in the Traffic Control Cabinet.
3. The push buttons (EPBS) are installed on the traffic poles.
4. Run a system check to make sure everything can talk to each other.
5. Set preferences with E-Configurator.
6. Done!
So what I'm interested in mainly, is the audio setup. This is part of the software, which is loaded in the Central Control Unit.
You can configure the setup using an E-Configurator.
"The E-Configurator is a battery operated hand-held device for making changes to the operating characteristics of Polara Navigator and EZ Communicator Push Button Stations (EPBS).
It uses two-way infrared communication while held in front of a EPBS and pointed toward the red LED above the EPBS arrow button."
Sounds easy enough. Of course, it is password protected (factory default is AAAA).
Where could you find an E-configurator? Well, there are places, if you look around.
Using the Navigator Configurator (E-Config), you have access to a lot of setup options. As far as audio goes, you can change volume levels, and things like that. But what about a new audio file altogether?
Short answer.. No.
"Programming custom messages in the field requires a laptop, USB A/B cable and
the Polara EPBS Audio Programmer software. The software must be installed and
messages prepared prior to installation of the units on the intersection.
[...]
if units are to be programmed in the field, a laptop with the proper software and voice messages must be prepared prior to installation on the intersection."
Polara has their own Youtube video that walks you through custom audio setup. But it's all done prior to installation. I was disappointed by this, but reading the manuals was still pretty interesting.
For example, the system can vary the volume of the audio based on how much ambient noise there is. And an 'extended push' option, where it will say additional information (street names), if the button is pushed and held longer.
Just know the internet is full of manuals. So if there is a gadget you have an interest in, it's worth taking a look.
Sunday, November 4, 2012
Playing board games online (against humans)
Several years ago I found this site yourturnmyturn.com, and I've recently jumped back into it. Instead of playing a whole game in one sitting, you and your opponent take turns, sometimes days at a time. After your opponent makes a play, you receive an email reminding you that it's now your turn.
They have about 40 games, including classics like checkers, chess, stratego and scrabble. And some more obscure and newer games.
It's great being able to play friends and family, even when we can't get to a computer at the same time.
Registration is free and easy. If you want to register, use this special link. It will give me referral points (doesn't do much, except maybe put a star by my name). Once you register, go ahead and start a new game!
Tuesday, October 9, 2012
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
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
Wednesday, October 3, 2012
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.
In my controller, I call the getUserReport function to get the object with my query data.
I have an array of IInfoObject (not to be confused with IInfoObjects) for each row of data from the query.
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
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.
In my controller, I call the getUserReport function to get the object with my query data.
I have an array of IInfoObject (not to be confused with IInfoObjects) for each row of data from the query.
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 |
Subscribe to:
Posts (Atom)