≡ Menu

Moving a Legacy Citrix-Based Application to a Current Version of Windows

In my last post, the problem I experienced was with an old application (which I’ll call “App #1”) written in PowerBuilder 6.5, and accessed via Citrix.

Today, a similar type of application (“App #2”) is the source of the problem, and the fact that it’s being moved to the same new server made the cause of the problem even more difficult to discern.

Both applications connect to the same Microsoft SQL Server instance. After moving App #1, the problem was connecting to an Oracle Database via ODBC. Connecting to SQL Server worked fine.

However, App #2, while ostensibly identical in its method of connecting to the database, threw an error when trying to connect via the new Citrix server.

error dialog box

This is the same error given if one enters an incorrect username and password, so it’s not the most helpful notification to receive. Also, this application has no error log, and does not send errors to the Windows Event Viewer. App #2 is basically a black box.

I decided to check whether or not XP Compatibility mode would affect how App #2 ran on the new server. Though App #1 was unaffected by this, something had to be different and this should be easy enough to check.

Because the application ran from a network drive rather than locally on the Citrix server, checking the Properties on the executable was not fruitful. The entire Compatibility tab was grayed out.

Compatibility tab on Properties dialog box for app on network drive

By copying the application executable to the server, I was then able to set the Compatibility Mode selector for Windows XP SP 3. The application ran perfectly! This did not completely solve my problem though – this app was run by Citrix via batch file rather than an app shortcut.

I figured there must be a way to set the Compatibility Mode within the batch before executing via start.exe.

After digging a bit, I did find several options for doing this. Though the method preferred by many involved adding registry keys, I chose the simplest route.

Adding this line to the batch file just before the call to the application executable solved my problem!

set __COMPAT_LAYER=WinXP

User Cannot Access an ODBC System DSN

In moving a Citrix-based application from one server to another, I discovered in testing that users who were not in the local Windows Administrators group on the Citrix server were no longer able to connect to an ODBC System Data Source Name (DSN) when presented with the connection dialog box.

Though a box similar to the one below (except populated with Data Sources) did appear with all expected Data Sources present, the users received an error when selecting a Data Source and clicking OK.

ODBC Data Source Administrator

Though permissions for ODBC Data Sources can be set using Group Policy, when that is done, a user would not have been able to even see the list in the Data Source Administrator.

I discovered that the NTFS permissions on the folder containing the Oracle ODBC driver (in this case, C:\oracle\product\11.2.0\client_1) had been changed not to inherit from the parent folder. As a result, the local Users group was absent from the Access Control List.

By reapplying the inheritance to the folder in question and its subfolders, I was able to restore access to the ODBC System DSN to the application users.

Using MAX() in a SQL Subquery

One pro bono project I’m working on is improving a school website where parents can sign up for classes, view students’ grades, etc. One of the problems with the website was that the list of user accounts, which includes all parents and teachers, includes parents from previous years who no longer have students there.

The query for pulling this information was very simple:

SELECT * FROM UserAccounts ORDER BY lastName ASC

This query pulled all users and ordered them only by last name. The data was being dumped into an ASP.NET GridView with column sorting, and there were only a couple hundred people there, so it wasn’t completely unmanageable. However, dealing with all the parents who no longer have kids there did make visual searches more difficult. The request was to bring all current parents to the top of the list so that other admins don’t have to search through pages of people who no longer attend.

A quick fix for this would be to sort the list based on last academic year attended. We’ll do this by employing the MAX() function.

In addition to the UserAccounts table, there are other tables called “students” (which includes all current and past students) and “schedule” (which holds student schedules). In the schedule table, a column called “s_year” that holds the academic year (in the format of “2017-18”). We can join these tables based on user account IDs in the UserAccount table, the parentID column in the students table, and the student id (s_id) column in the schedule table.

One more thing: Since this list also includes people who have never had students there, the revised query will have to take that into account.

database diagram showing table relationships

The below query will not only solve this problem, but will also sort by first and last name. Users with no academic year (a NULL value) will be at the bottom of the list.

SELECT DISTINCT ua.*, sc.s_year FROM UserAccounts ua 
LEFT OUTER JOIN students s ON s.parentID = ua.ID 
LEFT OUTER JOIN schedule sc ON sc.s_id = s.ID 
WHERE sc.s_year IS NULL 
OR sc.s_year = (
   SELECT MAX(sc2.s_year) FROM schedule sc2 WHERE sc2.s_id = s.ID
) 
ORDER BY sc.s_year DESC, ua.lastName, ua.firstName

The only remaining thing to do is to add the year column to the GridView and make sure that sorting is enabled.

Using PHP to Scrape the Report Card from a DataCamp Profile

UPDATE: This does not currently work, as DataCamp has changed the structure of profile pages. I will revise this as soon as is feasible. (7/26/17)

Just as I have written scripts for displaying report cards for Code School, CodeEval, and Duolingo on my blog, I have written the below script for displaying a DataCamp profile.

DataCamp logo

datacamp.php:

<style>
#datacamp {
   border: 1px solid blue;
   text-align: center;
   vertical-align: middle;
   width: 100%;
}

#datacamp li {
   list-style-type: none;
}

.image-centered {
   display:block;
   margin-left: auto;
   margin-right: auto;
   margin-bottom: 10px;
}

.image-rounded {
    border-radius: 50%;
}

.progress-bar {
    position: relative;
    border: 1px solid #33aacc;
    width: 100%;
    height: 18px;
    margin-bottom: 1rem;
}

.progress-bar .inner {
   position: absolute;
   left: 0;
   top: 0;
   bottom: 0;
   background-color: #33aacc;
   min-width: 5px;
}

.wrapper-scores .container {
   min-width: 150px;
   max-width: 260px;
   width: 100%;
}

.course-block__completed, .course-block__certificate-download, .btn-linkedin-share,
.course-block__description, .course-block__author {
   display: none;
}

.col-sm-4 img {
   display: inline;
   padding: 1px;
}
</style>
<?php
    date_default_timezone_set('America/Los_Angeles');
    
    function getClass($classname, $htmltext)
    {
        $dom = new DOMDocument;
        $dom->loadHTML($htmltext);
        $xpath = new DOMXPath($dom);
        $results = $xpath->query("//*[@class='" . $classname . "']");
        return $results;
    }
    
    
    function buildContent($results)
    {
        $content = "";
        foreach ($results as $node) {
            $partial_content = innerHTML($node);
            $content = $content . $partial_content;
        }
        return $content;
    }
    
    
    /* this function preserves the inner content of the scraped element. 
    ** http://stackoverflow.com/questions/5349310/how-to-scrape-web-page-data-without-losing-tags
    ** So be sure to go and give that post an uptick too 🙂
    **/
    function innerHTML(DOMNode $node)
    {
      $doc = new DOMDocument();
      foreach ($node->childNodes as $child) {
        $doc->appendChild($doc->importNode($child, true));
      }
      return $doc->saveHTML();
    }
    
    $profilename = $_GET['nick'];
    if (strlen($profilename) == 0)
	exit(1);
    $profile_url =  'https://www.datacamp.com/profile/' . $profilename . '/';
    $filename = "datacamp_" . $profilename . ".txt";
    $full_content = '';   
    $norefresh = FALSE;
    $days = 1;
    $updated = 'no date';
    
    /* checks to see if file exists and is current */
    if (file_exists($filename)) {
	    $stats = stat($filename);
	    /* 86400 seconds in one day */
	    if ($stats[9] > (time() - (86400 * $days))) {
	    	$norefresh = TRUE;
	    	$updated = date("Y-m-d H:i:s", $stats[9]);
	    }
    }
    
    /* if $norefresh is still FALSE, file will be created or updated; otherwise, it will be loaded */
    if ($norefresh) {
    	$full_content = file_get_contents($filename);
    } else {         
	$previous_value = libxml_use_internal_errors(TRUE);
	$context = stream_context_create(array(
	'https' => array('ignore_errors' => true),
	));
	$html = file_get_contents($profile_url, false, $context);  
	
	$class = 'profile-page';
	$resultsBucket = getClass($class,$html);
	

	libxml_clear_errors();
	libxml_use_internal_errors($previous_value);
	        
	$full_content = $full_content . buildContent($resultsBucket);
	
	/* making sure correct path exists on images */
	$full_content = str_replace("src=\"/","src=\"http://datacamp.com/",$full_content);
	
	/* changing h2 tags to h1 tags and inserting line breaks */
	$full_content = str_replace("<h2","<br /><h1",$full_content);
	$full_content = str_replace("</h2>","</h1><br />",$full_content);
	
	/* disabling the anchor tags on each badge by changing to divs */
	$full_content = str_replace("<a href","<div class",$full_content);
	$full_content = str_replace("<a class","<div class",$full_content);
	$full_content = str_replace("</a>","</div>",$full_content);
	
	/* adding line breaks */
	$full_content = str_replace("<div class=\"stats\">","<br /><div class=\"stats\">",$full_content);
	$full_content = str_replace("Earned</span>","Earned</span><br />",$full_content);
	$full_content = str_replace("Completed</span>","Completed</span><br />",$full_content);
	$full_content = str_replace("Aced</span>","Aced</span><br /><br />",$full_content);
		
	file_put_contents($filename,$full_content);
	$updated = date("Y-m-d H:i:s");
    }
?>
<a href="<?php echo $profile_url; ?>" target="_blank">
	<div class="wrapper-scores">
	<!-- <?php echo "Last updated: $updated" ?> -->
	<?php
	    	/* return the html */
		echo $full_content;
	?>
	</div>
</a>

If you wish to display this in a WordPress widget, create a Text widget and add this code, replacing “NICKNAME” with a DataCamp username.

<div id="datacamp"></div>
<script>
(function($) {
$("#datacamp").load("/datacamp/datacamp.php?nick=NICKNAME");
})(jQuery);
</script>

Since my primary reason for writing these has been to populate widgets on this WordPress blog, at some point I’ll probably incorporate these into WP plugins.