{"id":55,"date":"2004-08-03T17:34:31","date_gmt":"2004-08-03T22:34:31","guid":{"rendered":"http:\/\/www.tigoe.com\/pcomp\/code2\/category\/perl\/55"},"modified":"2008-01-21T21:35:05","modified_gmt":"2008-01-22T02:35:05","slug":"accessing-sql-in-perl","status":"publish","type":"post","link":"https:\/\/www.tigoe.com\/pcomp\/code\/Perl\/55\/","title":{"rendered":"Accessing SQL in Perl"},"content":{"rendered":"

This is a perl script that uses DBI to open an SQL database, read all the lines of a table, and print them out in an HTML page. This script assumes you’ve got a table in your database called people_table, and that you’ve got at least two fields, one called firstname and one called lastname.
\nEverything I know about accessing SQP through perl (which isn’t much) I learned from Chris Sung’s notes<\/a>, in about half an hour. I copied much of this script from him too.<\/p>\n

<\/p>\n

Technorati Tags: networked objects<\/a>, networks<\/a><\/p>\n


\n
\nHere’s the script:<\/p>\n

#!\/usr\/bin\/perl\n\n# Get the directory of this script:\n\nif    ($0=~m#^(.*)\\\\#){ $execDir = \"$1\"; }  # Win\/DOS\nelsif ($0=~m#^(.*)\/# ){ $execDir = \"$1\"; }  # Unix\nelse  {`pwd` =~ \/(.*)\/; $execDir = \"$1\"; }  # Unix\n\n#     get the login (if you get the login like this,\n#    make sure that only you can read and write to the file (chmod 0600 on the file):\nrequire \"$execDir\/info.pl\";\nuse DBI();\n\n#    Set basic parameters:\n$database = $user;\n$data_source = \"dbi:mysql:$database\";\n$username = $user;\n$password = $passwd;\n\n#    Open the database:\n&connectSQL;\n#    Print the top of the HTML page:\n&print_header;\n#    Get everything from the database:\n&get_everything;\n#    Print the bottom of the HTML page:\n&print_footer;\n#    disconnect from the database:\n&disconnectSQL;\n\n#    End the script:\nexit(0);\n\n\n#########################################################\n#    Subroutines\n#########################################################\n\n\n#########################################################\n# Connect to the database.\n#########################################################\n# This only needs to be done once per script\nsub connectSQL {\n     $dbh = DBI->connect($data_source, $username, $password,\n        {'RaiseError' => 1, 'PrintError' => 1});;\n    \n}\n \n \n#########################################################\n# Disconnect from the database.\n#########################################################\n# Always the last thing you do before exiting your script\n\nsub disconnectSQL {\n    $dbh->disconnect();\n}\n\n#########################################################\n# Get everything from the given table:\n#########################################################\n\n\nsub get_everything {\n    # Retrieve all rows from the table and put in normal array:\n    $SqlStatement = \"SELECT * FROM people_table ORDER BY firstname\";\n    $sth = $dbh->prepare($SqlStatement);\n    $sth->execute();\n\n    while (@row_array = $sth->fetchrow_array())\n    {    $firstname = $row_array[0];\n        $lastname = $row_array[1];\n        print \"$firstname    $lastname <br>\\n\";\n    }\n    $sth->finish();\n}\n\n#########################################################\n# Print the beginning of an HTML document\n#########################################################\n\nsub print_header {\n#Print the header of the HTML file:    \nprint \"Content-type: text\/html\\n\\n\";\nprint qq^\n<html>\n\n<head>\n    <title>Database Results<\/title>\n<\/head>\n<body>\n<h1>database results<\/h1>\n^\n}\n\n#########################################################\n# Print the end of an HTML document\n#########################################################\n\nsub print_footer {\nprint qq^\n<\/body>\n\n<\/html>\n^\n}\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
This is a perl script that uses DBI to open an SQL database, read all the lines of a table, and print them out in an HTML page.  This script assumes you’ve got a table in your database called people_table, and that you’ve got at least two fields, one called firstname and one called lastname.Everything I know about accessing SQP through perl (which isn’t much) I learned from Chris Sung’s notes, in about half an hour.<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16],"tags":[],"class_list":["post-55","post","type-post","status-publish","format-standard","hentry","category-Perl"],"_links":{"self":[{"href":"https:\/\/www.tigoe.com\/pcomp\/code\/wp-json\/wp\/v2\/posts\/55","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.tigoe.com\/pcomp\/code\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.tigoe.com\/pcomp\/code\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.tigoe.com\/pcomp\/code\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.tigoe.com\/pcomp\/code\/wp-json\/wp\/v2\/comments?post=55"}],"version-history":[{"count":0,"href":"https:\/\/www.tigoe.com\/pcomp\/code\/wp-json\/wp\/v2\/posts\/55\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.tigoe.com\/pcomp\/code\/wp-json\/wp\/v2\/media?parent=55"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.tigoe.com\/pcomp\/code\/wp-json\/wp\/v2\/categories?post=55"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.tigoe.com\/pcomp\/code\/wp-json\/wp\/v2\/tags?post=55"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}