Beefy Boxes and Bandwidth Generously Provided by pair Networks RobOMonk
XP is just a number
 
PerlMonks  

bind_col() and the SQL query

by Hans Castorp (Acolyte)
on Apr 17, 2013 at 17:51 UTC ( #1029187=perlquestion: print w/ replies, xml ) Need Help??
Hans Castorp has asked for the wisdom of the Perl Monks concerning the following question:

Greetings, Monks:

It's been awhile, and I'm not *as* much of a newbie as I was, but this has me stumped. I hope someone here can enlighten me. I have a Perl script that runs through our library database and picks out all new items we have gotten in the past three months and puts them in a table, which we use to run a new books search/rss feed. It works like a charm, but now we want to add e-books. I modified the SQL query to add e-books, ran it in Aquadata to make sure it works (it does), and plugged it in to the old script. When I run the script, I get the error: "cannot bind to non-existent field 11"

I understand that bind_col works with each column in the SQL select statement, but I don't understand why this particular field ($loca_id) is coming up as nonexistent, when others have not (such as \$requestor).

My questions are, how can I make this work? and Why aren't the other bind_cols throwing errors? Many thanks--code below.

Old SQL (the query that works, plus the bind_cols):

$sth = $dbh->prepare(qq{select BIB_TEXT.BIB_ID, BIB_TEXT.TITLE_BRIEF, +TO_CHAR(BIB_ITEM.ADD_DATE, 'YYYYMMDDHHMISS'), MFHD_MASTER.DISPLAY_CALL_NO, ITEM.ITEM +_ID, BIB_MFHD.MFHD_ID,TO_CHAR(ADD_MONTHS(SysDate, -3),'YYYYMMDDHHMISS +'), LINE_ITEM.BIB_ID, LINE_ITEM.REQUESTOR, LOCATION.LOCATI +ON_DISPLAY_NAME, MFHD_MASTER.LOCATION_ID, BIB_TEXT.ISBN from BIB_TEXT, BIB_ITEM, MFHD_MASTER, ITEM, + BIB_MFHD, DUAL, LINE_ITEM, LOCATION, BIB_MASTER where BIB_TEXT.BIB_ID = BIB_MFHD.BIB_ID and BIB_MFHD.MFHD_ID = MFHD_MASTER.MFHD_ID and BIB_TEXT.BIB_ID = BIB_ITEM.BIB_ID and BIB_TEXT.BIB_ID = LINE_ITEM.BIB_ID and BIB_ITEM.ITEM_ID = ITEM.ITEM_ID and BIB_TEXT.BIB_ID = BIB_MASTER.BIB_ID and BIB_MASTER.CREATE_DATE > ADD_MONTHS(Sy +sDate, -3) and BIB_MASTER.SUPPRESS_IN_OPAC = 'N' and MFHD_MASTER.LOCATION_ID = LOCATION.LOC +ATION_ID order by BIB_ITEM.ADD_DATE }); # -- # -- execute the query # -- $sth->execute(); $sth->bind_col( 1, \$bib_id ); $sth->bind_col( 2, \$_title_marc ); $sth->bind_col( 3, \$timedate ); $sth->bind_col( 4, \$callno ); $sth->bind_col( 5, \$item_id ); $sth->bind_col( 6, \$mfhd_id ); $sth->bind_col( 7, \$trash ); $sth->bind_col( 8, \$moretrash ); $sth->bind_col( 9, \$requestor ); $sth->bind_col( 10, \$location ); $sth->bind_col( 11, \$loca_id ); $sth->bind_col( 12, \$isbn );

New SQL (the query that throws the error, with bind_cols):

$sth = $dbh->prepare(qq{select BIB_TEXT.BIB_ID, BIB_TEXT.TITLE_BRIEF, BIB_MASTER.CREATE_DATE, TO_CHAR(BIB_MASTER.CREATE_DATE, 'YYYYMMDDHHMISS'), MFHD_MASTER.DISPLAY_CALL_NO, BIB_MFHD.MFHD_ID, TO_CHAR(ADD_MONTHS(SysDate, -3),'YYYYMMDDHHMISS'), LOCATION.LOCATION_DISPLAY_NAME, MFHD_MASTER.LOCATION_ID, BIB_TEXT.ISBN from BIB_TEXT, MFHD_MASTER, BIB_MFHD, DUAL, LOCATION, BIB_MASTER where BIB_TEXT.BIB_ID = BIB_MFHD.BIB_ID and BIB_MFHD.MFHD_ID = MFHD_MASTER.MFHD_ID and BIB_TEXT.BIB_ID = BIB_MASTER.BIB_ID and BIB_MASTER.CREATE_DATE > ADD_MONTHS(SysDate, -3) and BIB_MASTER.SUPPRESS_IN_OPAC = 'N' and MFHD_MASTER.LOCATION_ID = LOCATION.LOCATION_ID and LOCATION.LOCATION_ID in ( '9', '22', '25', '70', '95', '105', '110', '112', '114', '119', '120', '125', '127', '130', '135', '139', '151', '158', '159', '160', '161', '162', '163', '164', '165', '170', '173', '174', '176' ) order by BIB_MASTER.CREATE_DATE desc }); # -- # -- execute the query # -- $sth->execute(); $sth->bind_col( 1, \$bib_id ); $sth->bind_col( 2, \$_title_marc ); $sth->bind_col( 3, \$timedate ); $sth->bind_col( 4, \$callno ); $sth->bind_col( 5, \$item_id ); $sth->bind_col( 6, \$mfhd_id ); $sth->bind_col( 7, \$trash ); $sth->bind_col( 8, \$moretrash ); $sth->bind_col( 9, \$requestor ); $sth->bind_col( 10, \$location ); $sth->bind_col( 11, \$loca_id ); $sth->bind_col( 12, \$isbn );

Comment on bind_col() and the SQL query
Select or Download Code
Re: bind_col() and the SQL query
by runrig (Abbot) on Apr 17, 2013 at 18:04 UTC
    In the first query, you're selecting 12 columns and binding 12 columns. In the second query, you're only selecting 10 columns, but binding 12.

      Thank you--yes, sorry, I forgot to say that I have tried to delete the bind_cols that I don't need anymore--requestor and moretrash--but although that keeps it from throwing an error, the script does not work. (No data is put in the table.)

      I apologize--this may be too in-depth and specific to our library to ask here. :-}

        "put in the table"? But you're selecting data...not putting it in a table...
        You have added a new constraint to your query:
        and LOCATION.LOCATION_ID in (<list>)
        Does your data meet this criteria ?

                     "I'm fairly sure if they took porn off the Internet, there'd only be one website left, and it'd be called 'Bring Back the Porn!'"
                -- Dr. Cox, Scrubs

Re: bind_col() and the SQL query
by Hans Castorp (Acolyte) on May 07, 2013 at 13:25 UTC

    OK. For anyone who may be searching for a similar answer, I'm going to try to explain the solution.

    As renrig pointed out, the number of bind_cols did not match, and as NetWallah pointed out, I had added a new constraint to the SQL query. A close reading of the query brought me to a slightly different set of bind_cols. Perhaps the experts can forgive a newbie's lack of understanding of the whole picture, but I did not "get" the relationship between the select statement and the bind_cols. It seems I needed to account for everything in the select statement in the bind_cols, although the book I was reading told me I could pick and choose which to bind. Anyway, the new set of bind_cols:

    $sth->bind_col( 1, \$bib_id ); $sth->bind_col( 2, \$_title_marc ); $sth->bind_col( 3, \$timedate ); $sth->bind_col( 4, \$callno ); $sth->bind_col( 5, \$mfhd_id ); $sth->bind_col( 6, \$trash ); $sth->bind_col( 7, \$location ); $sth->bind_col( 8, \$loca_id ); $sth->bind_col( 9, \$isbn );

    These match, point for point, the select statement. The "trash" column, number 6, refers to the second To_Char. I still don't really understand why this was necessary, but when I added it, the query ran. I knew this because it started throwing errors when the script tried to insert the data into my newbooks table: "DBD::Oracle::db do failed: ORA-00947: not enough values"

    Progress! Errors are good! :-)

    This is the code I needed to look at:

    $dbh->do (qq{insert into newbooks values('$s_title','$timedate','$subj +ect','','$callno','$url','$display_date','$location','$loca_id','$isb +n')});

    Turns out the code was fine, but my table was off. I created a new table in the Oracle database that matched, in order and precisely, the above values. I then ran the script and it populated the table! Eureka!

    Again, not Earth-shaking for the experts out there, but for someone who is trying to come to grips with the way programming works, this was. I hope it may help someone else someday.

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others chanting in the Monastery: (6)
As of 2014-04-20 21:41 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    April first is:







    Results (488 votes), past polls