using a dbi select and returning nothing 2

[jcarrott]

I start by opening a csv file to get $field[3], which works. Then I do a select to get a value from the database.
I don't get a SQL error or a compile error. The programs appears to work just fine, but I get no data from the select.

#!/usr/bin/perl -w

#### program variables

if ($ENV{'PRODLINE'} eq "ocfp") {
 	$sys   = "ocfp";
	$dbsys = "lawocfp";
}
else {
	$sys   = "ocft";
	$dbsys = "lawocft";
}

## variables

$LAWDIR   = "/usr/lawson/lawson/";
$vName1   = "/interface/spd_lawsonx.csv";
$vName2   = "/interface/lawson_sms.csv";
$Company  = "0120";
$Location = "CS   ";
$user     = "procure";
$password = "*********";
$dsn      = "DBI:Oracle:host=$dbsys;sid=$dbsys";

use DBI;
$connect = DBI->connect($dsn, $user, $password) or die "$DBI::errstr";

open(IN, "$LAWDIR$sys$vName1") or die 'Could not open input file';
open(OUT, ">$LAWDIR$sys$vName2") or die 'Could not open outfile';
$line=<IN>;
foreach $line (<IN>) {
    chomp;
    @field = split(/,/, $line);
	$Item  = $field[3];
	$query = "SELECT ITEMMAST.DESCRIPTION, ITEMMAST.STOCK_UOM
			  FROM LAWSON.ITEMMAST
			  WHERE ITEMMAST.ITEM = '" . $Item . "'";
	$query_handle = $connect->prepare($query);
	@res = $query_handle->execute();
	$Str1 = "desc: " . $res[0] . " uom is: " . $res[1] . "Item is: " . $field[3];
	print $Str1;
	$field[4] = $res[0];
	$field[5] = $res[1];

      print OUT join(',', @fields), "\n";
}

close(IN);
close(OUT);

Can somebody help me figure out what I did wrong?

 

[travs69]

You need to return the data from the execute command some how..
I don't prepare or execute it just:
my @res = $dbh->selectrow_array($query);
you can do hashes and such to

http://search.cpan.org/~timb/DBI/DBI.pm

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Travis - Those who say it cannot be done are usually interrupted by someone else doing it; Give the wrong symptoms, get the wrong solutions;

 

[jcarrott]

I have tried this several ways and I get the same error.

foreach $line (<IN>) {
    chomp;
    @field = split(/,/, $line);
    $Item  = $field[3];
    $query = "SELECT ITEMMAST.DESCRIPTION, ITEMMAST.STOCK_UOM
              FROM LAWSON.ITEMMAST
              WHERE ITEMMAST.ITEM = '" . $Item . "'";
    $query_handle = $connect->prepare($query);
    @res = $query_handle->execute();
    my @res = $dbh->selectrow_array($query);
    $Str1 = "desc: " . $res[0] . " uom is: " . $res[1] . "Item is: " . $field[3 
    print $Str1;
    $field[4] = $res[0];
    $field[5] = $res[1];

    print OUT join(',', @fields), "\n";

The error is:

Can't call method "selectrow_array" on an undefined value at smsItem_sh line 57,
<IN> line 666.

I have tried removing the 2 lines before the line you suggested, and I get the same error.

Does this make sense?

 

[jcarrott]

The last part of the error message just hit me. I have 666 lines of input. The last 2 records will return no result, they are bad item numbers.
So do I just need to handle an error?

 

[travs69]

You really need to read the doc on dbi,
You need to change $dbh to $connect from what I can tell of your code.

You should use strict, try writing a script from scratch with it, it will force you to fix all kinds of things before you even realize they are errors.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Travis - Those who say it cannot be done are usually interrupted by someone else doing it; Give the wrong symptoms, get the wrong solutions;

 

[mikrom]

Here a trivial working example which does a select from DB2 on iSeries. Maybe it helps you to start:

[url=http://perldoc.perl.org/functions/use.html][black][b]use[/b][/black][/url] [green]strict[/green][red];[/red]
[black][b]use[/b][/black] [green]warnings[/green][red];[/red]
[black][b]use[/b][/black] [green]DBI[/green][red];[/red]
[black][b]use[/b][/black] [green]DBD::DB2::Constants[/green][red];[/red]
[black][b]use[/b][/black] [green]DBD::DB2[/green][red];[/red]

[url=http://perldoc.perl.org/functions/my.html][black][b]my[/b][/black][/url] [blue]$dbh[/blue] = DBI->[maroon]connect[/maroon][red]([/red][red]"[/red][purple]dbi:DB2:*LOCAL[/purple][red]"[/red][red])[/red] or [url=http://perldoc.perl.org/functions/die.html][black][b]die[/b][/black][/url][red];[/red]

[black][b]my[/b][/black] [blue]$stmt[/blue] = [red]'[/red][purple]SELECT * FROM IBPDDB.P001800V[/purple][red]'[/red][red];[/red] 
[black][b]my[/b][/black] [blue]$sth[/blue] = [blue]$dbh[/blue]->[maroon]prepare[/maroon][red]([/red][blue]$stmt[/blue][red])[/red] or [black][b]die[/b][/black] [red]"[/red][purple]prepare got error [/purple][red]"[/red] . [blue]$dbh[/blue]->[maroon]err[/maroon][red];[/red]
[blue]$sth[/blue]->[maroon]execute[/maroon][red]([/red][red])[/red] or [black][b]die[/b][/black] [red]"[/red][purple]execute got error[/purple][red]"[/red] . [blue]$dbh[/blue]->[maroon]err[/maroon][red];[/red]
[black][b]my[/b][/black] [blue]$arr[/blue] = [blue]$sth[/blue]->[maroon]fetchall_arrayref[/maroon][red];[/red] 
[olive][b]foreach[/b][/olive] [black][b]my[/b][/black] [blue]$a[/blue] [red]([/red][blue]@$arr[/blue][red])[/red] [red]{[/red]
  [olive][b]foreach[/b][/olive] [black][b]my[/b][/black] [blue]$ref[/blue] [red]([/red][blue]@$a[/blue][red])[/red] [red]{[/red]     
    [url=http://perldoc.perl.org/functions/print.html][black][b]print[/b][/black][/url] [red]"[/red][purple][blue]$ref[/blue] [/purple][red]"[/red][red];[/red] 
  [red]}[/red] 
  [black][b]print[/b][/black] [red]"[/red][purple][purple][b]\n[/b][/purple][/purple][red]"[/red][red];[/red] 
[red]}[/red]

[tt]------------------------------------------------------------
Pragmas (perl 5.8.8) used :
[ul]
[li]strict - Perl pragma to restrict unsafe constructs[/li]
[li]warnings - Perl pragma to control optional warnings[/li]
[/ul]
Other Modules used :
[ul]
[li]DBD:B2[/li]
[li]DBD:B2::Constants[/li]
[li]DBI[/li]
[/ul]
[/tt]