perl DBI question

Post #242,414 by boxley 1/25/06 11:35:29 AM Reply	perl DBI question inherited a script that uses the following terminology $query = $dbh ->prepare(select statement to get stuff;) $query->execute(); stuffed into an array $query->finish Now the question is if finish re-initializes the handle, is data contained in that array capable of being used to conduct another query? thanx, bill Any opinions expressed by me are mine alone, posted from my home computer, on my own time as a free american and do not reflect the opinions of any person or company that I have had professional relations with in the past 50 years. meep
Post #242,449 by ben_tilly 1/25/06 1:42:08 PM Reply	I don't understand your question I just can't make sense of it. I don't understand what connection you think that array has to that database. So let me take this step by step. First of all I would strongly recommend adding some error checks to that. If prepare or execute fail (and either could) then $DBI::errstr should have the actual error message. You'll probably want to see that. But that detail aside, all that finish does is indicates that the statement handle is really done. (So stuff may be flushed, the database told that you're done, etc.) After that, the statement handle is useless. The array is a structure in your program. Other than what you explictly see in the code, it has no connection to the statement handle or the database. Finishing the statement handle does not affect what is in the array. The database has no idea what you did with its data. If you run the same code again, the array will wind up with data from both runs. It is possible to write code that takes data from that array and sends it back to the database. It is not possible to write a query in the database that accesses that data. If you want to accomplish something that looks like the last step, then you either need to insert that data into a table (several databases have the idea of temporary tables that can be useful for this), or you need to make that select statement into a nested subquery in a larger query. I've tried to answer every permutation that I can think of what you might have meant by your question. Hopefully along the way I answered your actual question. Cheers, Ben I have come to believe that idealism without discipline is a quick road to disaster, while discipline without idealism is pointless. -- Aaron Ward (my brother)
Post #242,451 by boxley 1/25/06 1:50:23 PM Reply	Thank you sir The array is a structure in your program. Other than what you explictly see in the code, it has no connection to the statement handle or the database. Finishing the statement handle does not affect what is in the array. The database has no idea what you did with its data. that was the answer to my question, it turns out that my problem may actually be the following. I was running the code and receiving no output, which is why I was wondering what happened to the array. The output statement is print(BLOBLIST "$blobpath\\n"); which doesnt appear to write anything but print BLOBLIST "$blobpath\\n"; does output to the file. thanx, bill Any opinions expressed by me are mine alone, posted from my home computer, on my own time as a free american and do not reflect the opinions of any person or company that I have had professional relations with in the past 50 years. meep
Post #242,483 by broomberg 1/25/06 8:08:39 PM Reply	Those 2 are identical. Your problem lies elsewhere.
Post #242,493 by boxley 1/25/06 8:52:53 PM Reply	why does the one without parense print and not the other? well gonna be spending a few days relearning what little I ever knew about perl. thanx, bill Any opinions expressed by me are mine alone, posted from my home computer, on my own time as a free american and do not reflect the opinions of any person or company that I have had professional relations with in the past 50 years. meep
Post #242,498 by ben_tilly 1/25/06 9:07:42 PM Reply	Barry is right The two are completely the same to Perl. I'm going to guess that you had a typo you accidentally fixed. Ben I have come to believe that idealism without discipline is a quick road to disaster, while discipline without idealism is pointless. -- Aaron Ward (my brother)
Post #242,572 by boxley 1/26/06 1:51:59 PM Reply	found why no data shows up the STDIN never gets the output of the hash, it just sits there. If I type in a valid value for foo it processes it. So now off to google to see how to fix it. thanx, bill ******************************* $query = $dbh->prepare("SELECT foo, foo1 FROM foo_table"); $query->execute(); $res = $query->fetchall_arrayref(); foreach $row (@$res) { $hashmap{$row->[0]} = $row->[1]; } $query->finish; $query = $dbh->prepare("SELECT foo, bar, bar1, bar2 FROM bar_table WHERE foo1=? AND foo=?"); while (<STDIN>) { chomp ($foo = $_); print("$foo\\n"); $hashedfoo = $hashmap{$foo}; $query->execute($hashedfoo, $foo); Any opinions expressed by me are mine alone, posted from my home computer, on my own time as a free american and do not reflect the opinions of any person or company that I have had professional relations with in the past 50 years. meep
Post #242,585 by broomberg 1/26/06 3:33:57 PM Reply	What EXACTLY (in clear concise terms) are you trying to do. Don't show code. Show data. Tell me where it is, and what it is. Tell me where you want it to be, and what you want it to look like if it is different.
Post #242,588 by boxley 1/26/06 3:54:06 PM Reply	well hmm have 2 oracle tables, one identifying an ID and a second table that identifies where files owned by the ID reside on the disk. (ID's may have data but not be residents of this system) The second table has an entry which indicates the last time the file was opened. I want to identify which files have not been opened in the last 60 days, move them to a new location to slower storage disks then update oracle on the new location so the Application that uses this info will be able to locate the new location. I was supplied a perl script written by someone who is no longer here and was in a hurry to finish it before he left. The parts/queries work individually but the logic that merges the two and does the date logic stops at the <STDIN>. I have an email to the author asking that question. He is the decent sort that will answer. thanx, bill Any opinions expressed by me are mine alone, posted from my home computer, on my own time as a free american and do not reflect the opinions of any person or company that I have had professional relations with in the past 50 years. meep
Post #242,592 by ben_tilly 1/26/06 4:47:16 PM Reply	That someone sucks In the time that Barry and I have spent on this problem already, either of us could have written that script. Besides his variable names suck. What you need to do is get a list of old files (in one query), then loop over the set of files. For each file decide the new name, move the file, then update the database. You'll need to know how to figure out the new name (business knowledge, you probably have it), how to move a file (Perl's system command may help you here), and how to issue an update (my $sth = $dbh->prepare("update FOO set x = ? where y = ?") or die "Cannot prepare: $!"; then later $sth->execute("new filename", $file_id);) If the application was sanely built there is some locking needed before you move the file out from under the application. Odds are that you aren't so lucky, so I'd suggest running your script when the application is not in use. Cheers, Ben I have come to believe that idealism without discipline is a quick road to disaster, while discipline without idealism is pointless. -- Aaron Ward (my brother)
Post #242,595 by Steve Lowe 1/26/06 4:54:09 PM Reply	ICLRPD (new thread) Created as new thread #242594 titled [link\|/forums/render/content/show?contentid=242594\|ICLRPD] -- Steve [link\|http://www.ubuntulinux.org\|Ubuntu]
Post #242,602 by boxley 1/26/06 6:09:46 PM Reply	the application has a utility to do the actual move I just need to generate a list of what needs to be moved and who owns it. Thanks to both you and Barry for your help but the variable names here were changed by me to protect the innocent :-) thanx, bill Any opinions expressed by me are mine alone, posted from my home computer, on my own time as a free american and do not reflect the opinions of any person or company that I have had professional relations with in the past 50 years. meep

Welcome to IWETHEY!