Thread: Perl and DBI

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 1999
    Location
    eugene, or, us
    Posts
    8
    Rep Power
    0
    Is it possible to refer to a table in a query statement, where the table name is a variable.
    For example:
    select * from $table where whatever

    $table, being the a varable that contains the name of the table?

    Thank you.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 1999
    Posts
    33
    Rep Power
    16
    Yes.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 1999
    Location
    eugene, or, us
    Posts
    8
    Rep Power
    0
    Imo,

    Do you know how to do it? When I tried to code a query, I recieved a Mysql error indicating that it was tring to look up the table database01.$table. (Assuming a database named database01 and refering to the table with the variable $table). It appeared to me that DBI was passing the variable name to Mysql in a literal fashion (i.e., the name of the variable and not the contents of the variable).

    Thanks

  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 1999
    Location
    Oxford, England
    Posts
    3
    Rep Power
    0
    Did you do "$table"? This would explain it.

    Mark
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 1999
    Location
    eugene, or, us
    Posts
    8
    Rep Power
    0
    Mark,

    Thank you. That was the problem. I completely overlooked the fact that I had single quoted my query statement....

    It always ends up being something simple like that doesn't it.
    Greg Zartman
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2000
    Location
    USA
    Posts
    67
    Rep Power
    15
    I have been having a similar problem. Would you mind showing me your database connection code? My code seems to get stuck on the
    $sth=$dbh->prepare($SQL)
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 1999
    Location
    eugene, or, us
    Posts
    8
    Rep Power
    0
    my $dbh = DBI->connect("dbi:mysql:$main_member_dbase", "$main_member_username", "$main_member_password") | | &p_error;
    my($sth) = $dbh->prepare("SELECT * FROM $hit_table WHERE username = ? ") | | &p_error;
    $sth->execute($user) | | &p_error;
    @stat_array = $sth->fetchrow_array();
    $sth->finish;
    $dbh->disconnect;
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2000
    Posts
    108
    Rep Power
    15
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by bydavid:
    I have been having a similar problem. Would you mind showing me your database connection code? My code seems to get stuck on the
    $sth=$dbh->prepare($SQL)
    [/quote]

    check your assignment code for $SQL. I had the same problem, where it got stuck at that line, until I changed
    $sth=$dbh->prepare($SQL) to
    $sth=$dbh->prepare($sql)

    -Josh http://www.datera.com
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2000
    Location
    USA
    Posts
    67
    Rep Power
    15
    i tried that , but i still have the same problem.

    i have switched to teh mysql module and i got
    results the frist time. what is the difference, where can i find information about all the functions included in mysql module.
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 1999
    Location
    eugene, or, us
    Posts
    8
    Rep Power
    0
    You need to double quote the prepare statement, otherwise Mysql interprets the information inside of the parentheses literally. For example:
    prepare ("select * from $table where $item = ?)
    This will pass the query statement to mysql with the values of the variables inserted in the command line. If you don't double quote this statement, then the command that mysql would see would be: select * from $table where $item = ?. This won't work as mysql has no idea what the values of $table and $item are.

IMN logo majestic logo threadwatch logo seochat tools logo