Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
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 chilling in the Monastery: (6)
As of 2015-07-03 22:22 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (57 votes), past polls