I'm trying to write a Perl script to connect to our Remedy system using Win32::ODBC. I'm aware of the wonderfully named arsperl module from Buffalo university, but I don't have agreement to use this (I am not the Remedy admin) whereas ODBC connection is accepted (we currently run reports from Excel, where the ODBC DSN works no problem).

I don't Win32::ODBC well enough - am I making a stupid mistake with my coding, or is there a known problem connecting to Remedy AR system using this module? I can connect (as proved by the fact that I can get a list of the columns in a particular table) but I can't get any information from them at all. I've tried to intersperse the code with various of the debugging functions suggested in the perldocs. 

#!perl 
use strict;

use Win32::ODBC;
use Data::Dumper;

my $odbc=Win32::ODBC->new('Remedy ODBC Data Source') or die "Couldn't 
+connect! $!";

my %sources=Win32::ODBC->DataSources ();
print Dumper(\%sources);
# $VAR1 = {};

my %drivers=Win32::ODBC->Drivers ();
print Dumper(\%drivers);
# This exists on the other hand... (large hash with details of availab
+le drivers)

my %funcs=$odbc->GetFunctions ();
print Dumper(\%funcs);
#  $VAR1 = {
#      '0' => '0',
#      '1' => '0'
#  };

print "Running SQL statement:\n";

# $odbc->Run( q(SELECT * FROM "SHR:PeopleGroup"));
#  Excecuting connection 1
#  sql statement: "SELECT * FROM "SHR:PeopleGroup""
#  Error: ""
#  --------------------
$odbc->Sql( q(SELECT * FROM "SHR:PeopleGroup"));

$odbc->DumpData ();
# Produces a Heading row including all headings in the SHR:PeopleGroup
+ schema,
# but with *no* data: for example-
#   Full-Name  First-Name  Last-Name  ID  Phone
#   ---------  ----------  ---------  --  -----

while ($odbc->FetchRow) {
    print ".";
# Never gets here
    my %hash=$odbc->DataHash;
    print Dumper(\%hash);
}

print $odbc->TableList('','','','');
# print $odbc->TableList();
# print $odbc->TableList('','','','TABLE');
#  All do nothing

$odbc->ShutDown;
# gives no useful information

$odbc->Close;

[download]

Cheerio!
Osfameron
http://osfameron.perlmonk.org/chickenman/

In reply to Win32::ODBC - connecting to Remedy AR System by osfameron

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.