CallServer to SQLServer not returning data

claudiag

Scratching my head on this one.. hoping someone can come up with some ideas for me!

Platform: TS6.7.1 on RHL
DB: SQLServer2k5

I have a callserver in a DCR, fairly straight forward, taken from the example:

 function fetchFacets(catId) {
  var params = new Object();
  params.Id = catId;
  //alert the user that an external call is being made.
  IWDatacapture.displayMessage("Retrieving facets"); 
  top.hiddenFrameRunning = true;
  var server = window.location.hostname;
                IWDatacapture.callServer("http://"+server+"/iw-bin/loadspecifications.ipl", params, false);
 }

This piece works fine. It calls to loadspecifications.ipl, which also, theoretically, runs fine. Except it's not returning any rows, EVER. I get no error messages from the DB connection, and when run from the command line, everything returns swimmingly:

[html]
Content-type: text/html




<script language="javascript">
var myFacets = new Array();
// Set up the options object for a facet.
myFacets[0] = new Option('10769','10769', false, false);
</script>





[/html]

This should work, right? But for some reason, the perl is crapping out and not returning anything I can work with:

#!/usr/bin/perl  (using reg. perl for ODBC access... the system owners will only install it here)
$ENV{'LD_LIBRARY_PATH'} = '/usr/lib';
$ENV{'ODBCHOME'} = '/etc';
$ENV{'ODBC_INI'} = '/etc/odbc.ini';
$ENV{TEMP} = "/tmp"; 
$ENV{SYSTEMROOT} = "/";
use strict;
use DBI;
use CGI;

$| = 1;
my $in = new CGI;
my $catId = $in->param('Id');
print LOGFILE "check: $facetTrash\n";
my %Facets;
my $facetTrash;
my $server = q/dbiDBC:MSSQL/;
my $username = q/user/;
my $password = q/pass/;
my $db = 'dbname';
my $dbh = DBI->connect($server,$username,$password) or $facetTrash = "Problem with dbh";
$dbh->do("USE $db") or $facetTrash = $dbh->errstr;
my $sth = $dbh->prepare(qq{exec spCMSGetSpecificationFacet \@CMSID='$catId'}) or $facetTrash = $dbh->errstr;
$sth->execute() or $facetTrash = $dbh->errstr;
my $rows = $sth->rows();

$facetTrash = $rows;
$facetTrash = $server . "|" . $username . "|" . $password . "|" . $db . "|" . $catId . "|ROWS=" . $rows . "|" . $ENV{'ODBCHOME'};
while (my @row = $sth->fetchrow_array()) {
 $Facets{$row[0]} = $row[1];
}

#Generate Option objects for locations in that region.
my $facetOptions = '';
my $i = 0;
my $isDefaultSelected;

 while (my($key, $value) = each(%Facets)){

  $isDefaultSelected = $value eq '1' ? "true" : "false";
  $facetOptions .= "facets[" . $i++ . "] = new Option('$key','$key', $isDefaultSelected, $isDefaultSelected);\n";

 }

$dbh->disconnect;

Yes I realise my query is sql-injectiontastic as my coworker would say, it'll be fixed once I get this actually working

It does pass values back and forth, I've done a ton of fiddling with dumping values into the $facetTrash value just to get it echoed in an alertbox on the form itself - everything checks out ok, from the value of the Cat ID I'm passing to the SQL query.

For fun, I had it write out the DBD drivers it had access to:

 DBI DRIVERS
driver DBM
driver ExampleP
driver File
driver Gofer
driver JDBC
driver ODBC
driver Proxy
driver Sponge
driver mysql

So it sees the ODBC drivers fine. It also returns no error messages from the connection itself. I thought maybe environmental variables might be the cause, but even after taking what I get from my command line and explicitly setting all variables in the perl file itself (I can attach those outputs if anyone thinks it'll help) it still returns rows = -1. I've validated that there is data in the source it's connecting to.

I'm honestly at a loss as to how to troubleshoot this one. I'm not looking for easy answers, and I've dug around a lot to try and answer this one - came across this thread, which is why I have temp and systemroot defined now, just in case.

Any thoughts devnet?


ETA: I've also altered the query to a simple select statement for a value I know exists, and still get nothing back. Tried putting the values I expect back as a string and passing that instead of the array to see if that was crapping out, and it doesn't receive anything, though it will pass any other values I care to send just fine.

ETA2: the code setting the values in the DCR.

 function populateFacets(facets) {
  //Clear the message display...
  IWDatacapture.displayMessage("facets retrieved");
  var facetItem = IWDatacapture.getItem("/ProductDetails/Facets/facet");
  alert(facetItem);
  facetItem.addOption(facets[0]);
  alert(facets);
 }

Using the addOption both to test if I get values - it works if I set values manually, not if I try to use a query - and because setOptions appears to **** on me whenever I try to call it. Any tips on that piece would be appreciated too

Rick Poulin

Erm... add logging to your PERL script to check whether the problem is in retrieving the data or passing it back to the form? That $facetTrash looks like it might be useful in a log, amongst other things.

Also, to simplify things, perhaps just count how many times you loop through $sth->fetchrow_array and then print an alert() on that count. (I distrust $sth->rows() as it's not consistently implemented across query types and vendors). Using an alert disassociates yourself from JS problems... mostly.