Perl using DBI and 2 seperate connections 1

[jcarrott]

I have tables that are in 2 different schemas, so I need to start with one login, which is the outter loop. Then I need to login and connect to the other schema and read 3 tables.

I am trying to get the code to handle the inner login without dropping the outter login.

Here is the code:

## read table procure.recpo and process each entry

use DBI;
$dbh = DBI->connect($dsn, $user, $password)
		or die "Couldn't connect to DB: " . DBI->errstr;
$sth = $dbh->prepare("SELECT * FROM procure.RECPO")
		or die "Couldn't prepare statment: " . $dbh->errstr;

  ## Get R-NAME from BUYER
    my @data;
	my $rv = $sth->execute
		or die "Couldn't execute statement: " . $sth->errstr;

  ## Loop through each line of the PO
	while (@data = $sth->fetchrow_array()) {
		my $vCompany = $data[0];
		my $vPO      = $data[1];
		my $vVendor  = $data[2];
		my $vBuyCode = $data[3];
		print "\t$id: $vCompany $vPO $vVendor $vBuyCode\n";

	## log into schema OCFT.lawson with userid RECLAW & password RECPASS
	## read BUYER from lawson.buyer where BUYER-CODE equals $vBuyCode
	## return R.NAME

	## POLINESRC will return one record for each line of the PO
	## now loop through each line of the PO and get each lawson.POLINESRC & 
	## lawson.REQUESTER record
	## the key for POLINESRC is $vCompany & $vPO
		## return requester & source_doc_n
	## the key for REQUESTER is lawson.POLINESRC.requester
		## return r-name & email_address
	## load values from RECPO and all returned values into array
	## no duplicates on requester & source_doc_n

	}

I will work on the array after I get the inner loop working. The outter loop is working just fine.

I get this output:

$ perl reqNote_sh
        Source record: 120        1608496    241350 LT
        Source record: 120        1608495    391514 DT

 

[Trevoke]

So.. Create another database handle and statement handle and use those.

Tao Te Ching Discussions : Chapter 9 (includes links to previous chapters)
What is the nature of conflict?

 

[jcarrott]

So I would create another connection besides $dbh (maybe $dbg) and another select besides $sth (maybe $st1, $st2 and $st3)?

Then rather then my @data, I could add my @data2?

Is it that easy??

 

[jcarrott]

I tried this for the first part of the inner loop in my code:

    ## log into schema OCFT.lawson with userid RECLAW & password RECPASS
    ## read BUYER from lawson.buyer where BUYER-CODE equals $vBuyCode
    ## return R.NAME
        $st2 = $dbh->prepare("SELECT * FROM lawson.BUYER WHERE
            procure_group = 'OCF'and buyer_code = " . $vBuyCode)
            or die "Couldn't prepare statment: " . $db2->errstr;
        my @dat2;
        my $r2 = $st2->execute
            or die "Couldn't execute statement 2: " . $st2->errstr;
        @dat2 = $st2->fetchrow_array();
        my $vBuyer = $dat2[4];
        print "\t$id2: $vBuyer\n";

and got this error -

$ perl reqNote_sh
        Source record: 120        1608496    241350 LT
DBD::Oracle::db prepare failed: ORA-00904: "LT": invalid identifier (DBD ERROR:
error possibly near <*> indicator at char 75 in 'SELECT * FROM lawson.BUYER WHER
E
                        procure_group = 'OCF'and buyer_code = <*>LT ') [for Stat
ement "SELECT * FROM lawson.BUYER WHERE
                        procure_group = 'OCF'and buyer_code = LT "] at reqNote_s
h line 90.
Can't call method "errstr" on an undefined value at reqNote_sh line 90.
$

The prepare is failing and I have tried several ways to pass 2 values for the WHERE, but they don't seem to work. It keeps failing where I try to pass a varibale.

Does anybody have any ideas of what I am doing wrong?

 

[Trevoke]

Well, the error message tells you that this:

 $st2 = $dbh->prepare("SELECT * FROM lawson.BUYER WHERE procure_group = 'OCF'and buyer_code = " . $vBuyCode)

Becomes this:

'SELECT * FROM lawson.BUYER WHERE procure_group = 'OCF'and buyer_code = <*>LT ')

[for Statement "SELECT * FROM lawson.BUYER WHERE procure_group = 'OCF'and buyer_code = LT "]

So the question is ... Why is $vBuyCode translated to either "LT" or "<*>LT" ?

Also, if it's supposed to be a string, then your statement should be

buyer_code = 'LT'

So you should quote it!

$st2 = $dbh->prepare("SELECT * FROM lawson.BUYER WHERE procure_group = 'OCF'and buyer_code = '" . $vBuyCode . "'")

Tao Te Ching Discussions : Chapter 9 (includes links to previous chapters)
What is the nature of conflict?

 

[Trevoke]

Oh, I forgot to mention that, for various reasons, including security, it is better to do this:

use strict;
use warnings;
my $query = "SELECT * FROM lawson.BUYER WHERE procure_group = ? AND buyer_code = ?";
my $st2 = $dbh->prepare("query");
$st2->execute('OCF', "$vBuyCode");

It doesn't have to be quite that format (you use $r2, which is fine, of course), but I recommend passing in the variables at execute time.

Tao Te Ching Discussions : Chapter 9 (includes links to previous chapters)
What is the nature of conflict?

 

[jcarrott]

That was the answer, thank you it works great.

 

[ChrisHunt]

Another advantage of doing it the way Trevoke suggests - with ? placeholders instead of the actual variables - is that you can prepare the statement once, outside the loop, and execute it within the loop passing different values each time if necessary.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd