Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic
 
PerlMonks  

Win32::ODBC - connecting to Remedy AR System

by osfameron (Hermit)
on Jun 19, 2002 at 10:11 UTC ( #175611=perlquestion: print w/ replies, xml ) Need Help??
osfameron has asked for the wisdom of the Perl Monks concerning the following question:

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;

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

Comment on Win32::ODBC - connecting to Remedy AR System
Download Code
Re: Win32::ODBC - connecting to Remedy AR System
by strat (Canon) on Jun 19, 2002 at 11:15 UTC
Re: Win32::ODBC - connecting to Remedy AR System
by gav^ (Curate) on Jun 19, 2002 at 14:12 UTC
    You might find it useful to check for errors and write debugging information like:
    if ($odbc->Error) { $odbc->DumpError; exit; }
    Checking after you call FetchRow might help. Also you might need to increase your buffer size (in bytes): $odbc->SetMaxBufSize(50_000);
    I've found that ODBC has problems with memo fields in Access and this has helped. It seems to skip rows that are bigger than the maximum buffer size without flagging an error. I've had success cranking this number up to a meg or so (though record retrieval is slow).

    Hope this helps...

    gav^

      Hmm, placing a few error-checks as above gets me (after DumpData or FetchRow)
      ---------- Error Report: ---------- Errors for "16" on connection 1: Connection Number: 1 Error number: 911 Error message: "[Microsoft][ODBC Driver Manager] Driver does not suppo +rt this function" -----------------------------------
      This is despite the fact that DumpData does do something (show the column names). I guess that part of the API call that it makes is unsupported by this driver?

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

        Don't know if you still concerned with this... But I figured it out... Use DBD::ODBC NOT the Win32::ODBC... If you are just doing selects like I am... the DBI module works wonderfully....

        -----------------------
        Billy S.
        Slinar Hardtail - Hand of Dane
        Datal Ephialtes - Guildless
        RallosZek.Net Admin/WebMaster

        perl -e '$cat = "cat"; if ($cat =~ /\143\x61\x74/) { print "Its a cat! +\n"; } else { print "Thats a dog\n"; } print "\n";'

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://175611]
Approved by particle
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (5)
As of 2014-09-17 08:06 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (66 votes), past polls