Hash Value Part 2

[qball]

Thanks to all those who contributed to Hash Value, I now understand how to create and use a hash. However, and as always, I'm running into another situation. Here's the code:

foreach $comp_id (keys %hash){
    
$sql = <<END_SQL;

select comp_name from company where comp_name = '$hash{$comp_id}'

END_SQL

$query = &execsql($sql);

if(!$data[0]){
    print "insert '$hash{$comp_id}' in table COMPANY\n";
}
    
while (@data = $query->fetchrow) {

    if($data[0]){
        print "insert $comp_id in table TYPES for $data[0]\n";
    }
   }
}

I want to first print all values that aren't in table COMPANY. Then I want to print all keys that match up to values that are in the table based on the hash key values.

Am I making progress?

qball~"Maybe I node a little?!"

[arturo]

Well, hmmm ... you're using the hash values correctly where you use them, but this isn't exactly ANSI compliant SQL you're using in spots. You also appear to be using DBI, but not in a consistent fashion. This not being SQL monks, some of what you'll need to know isn't readily available on this site; get (buy, or borrow) a book on basic SQL.

I'll let you in on one of the neatest things about DBI, though: placeholders. Their use is well documented in the DBI documentation, which you should read in addition to any materials on SQL you tackle. Basically, you can prepare a statement once, and execute it many times while filling in different values on each execution.

Rather than just feed you the code, on this occasion I'm going to point to the Tutorials page (me do that? yes!) and especially to the DBI section thereof.

HTH!

[ton]

Couple of things:

1. You test the value of !$data[0] before assigning anything to it. So you end up testing whether or not you need to insert a $comp_id based on the result of the previous $comp_id.

2. Have you SQL-sanatized your comp_names? you need to replace any single quotes in a company name with two single quotes, otherwise the SQL statement will yield a syntax error. Try something along the lines of

map {$hash{$_} =~ s/'/''/g;} (keys %hash);

Also, it would help if you said what kind of errors you were getting...

[qball]

Let me comment on what arturo said.I'm only using SQL between >>END_SQL and END_SQL. The if statements where it says print "insert $comp_id in table TYPES for $data[0]\n"; and print "insert '$hash{$comp_id}' in table COMPANY\n";...all I'm telling Perl to do is print those lines...they are NOT intended for SQL calls.

qball~"I have node idea?!"