[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Microsoft Access Database and PERL...Using the select tag?

Posted on 2002-05-14
10
Medium Priority
?
1,080 Views
Last Modified: 2013-12-25
I'm new to DBs and really, really suck at PERL. I need to have my database online. It has three columns, but only two will be displayed at this time. My question is, how do I get the two columns from Access to the web page? I tried looking for an answer, but all I found was:
SELECT *
FROM TABLE;

I know * is the column and TABLE is the table, but where do I go from there?
0
Comment
Question by:sjaguar13
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 10

Expert Comment

by:rj2
ID: 7011989
To select only two fields you can use

SELECT Field1, Field2 FROM TABLE;
0
 

Author Comment

by:sjaguar13
ID: 7012906
So I have a cgi script like this:

#!C:\perl
SELECT Field1, Field2 FROM TABLE;
Print "Field1 = $Field1";
Print "Field2 = $Field2";
0
 
LVL 10

Accepted Solution

by:
rj2 earned 800 total points
ID: 7014702
Below is sample CGI script that demonstrates getting data from access. To make it work you must first create a odbc data source named test. The .mdb file must be saved below
your wwwroot. You must also install the module DBI::ODBC (if not already installed).

You might also want to check out MySQL (http://www.mysql.org) instead of Access.
MySQL is free and is a much better database than Access.


#!/usr/bin/perl

use DBI;
use CGI ':standard';

print header,start_html('Database test');
# Connect To Database
$db = DBI->connect( "dbi:ODBC:test", "", "",{RaiseError => 1, PrintError => 1, AutoCommit => 1} )
or die "Unable to connect: " . $DBI::errstr . "\n";

# Execute a Query
# * executing a query is done in two steps. First,
# * the query is setup using the "prepare" method.
# * this requires the use of the variable used to
# * initiate the connection. Second, the "execute"
# * method is called, as shown below.
$query = $db->prepare("SELECT field2,field3 FROM test");
$query->execute;

# How many rows in result?
# * the "rows" method using the variable name the
# * query was executed under returns the number
# * of rows in the result.
$numrows = $query->rows;

# Display Results
# * the fetchrow_array method executed on the
# * query returns the first row as an array.
# * subsequent calls return the other rows in
# * sequence. It returns zero when all rows have
# * been retrieved.
print "<TABLE border=1><TR><TD>Field2</TD><TD>Field3</TD></TR>\n";
while (@array = $query->fetchrow_array) {
($field2, $field3) = @array;
print "<TR><TD>$field2</TD><TD>$field3</TD>\n";
}
print "</TABLE>";
print end_html;

# Cleaning Up
# * with the DBI module, it is a good idea to clean up by
# * explicitly ending all queries with the "finish" method,
# * and all connections with the "disconnect" method.
$query->finish;
$db->disconnect;

exit(0);
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 12

Expert Comment

by:lexxwern
ID: 7016165
first to start using databases with perl you need to install the DBI module.

DBI stands for Database Interface.

first strat your Perl Package Manager, you should get a prompt like this

ppm>

type the following command

ppm> install DBI

this should install the perl DBI module

now you can start using the DBI module in your scripts. and before you start you must master the sql, it is pretty easy.

and then this link will be of help,
http://www.mysql.com/doc/P/e/Perl_DBI_Class.html
0
 

Author Comment

by:sjaguar13
ID: 7016562
Ok, I can't get this to work. What is PPM? Because I don't think I have that. I use Apache with Windows 98...how do I install the DBI thing?
0
 
LVL 12

Expert Comment

by:lexxwern
ID: 7016583
1. look for a file named "ppm.bat" in your perl/bin directory.
2. run that file.
3. ppm>install DBI.

Thats All
0
 
LVL 10

Expert Comment

by:rj2
ID: 7016989
sjaguar13,
You have installed ActiveState Perl, right?
If not, download it from URL below and install it.
http://www.activestate.com/Products/Download/Get.plex?id=ActivePerl

After you have installed it, start up a DOS prompt, and type commands as shown below (as lexxwern suggested).
PPM is a tool that comes with ActiveState Perl you can use to install Perl modules. Press enter after each command
(after installing ActiveState Perl you should have path to ppm.bat).

ppm
install DBI
install DBD::ODBC
exit

Then enter control panel, doubleclick on ODBC, click on "System DSN" property sheet, then click button "Add".
Select "Microsoft Access Driver (*.mdb), and click "Finish".
Type "test" as data source name, click button "Select" and select the Access .mdb file you want to use. The .mdb file should be located below the root of the web server.

If you still have problems we need to know exactly what kind of problems you're having in order to help you. Include information about what have you done so far, and what kind of error message you get (if any).
0
 
LVL 15

Expert Comment

by:samri
ID: 7031126
sometime ppm might not work (well at least on my machine), try ppm3 instead.
0
 

Expert Comment

by:tonygreer
ID: 8685734
If you are using a windows machine why not use IIS and ASP... Much simpler

Code:

<html>
<head>
      <title>Access Database</title>
</head>

<body>

<table border="1">
<%
'Create objects to connect to database
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRs = Server.CreateObject("ADODB.RecordSet")

'Connect to database and execute query
objConn.Open "ODBC Name Here"
objRs.Open "SELECT FieldName1, FieldName2 FROM TableName", objConn

'Loop through records
Do Until objRs.EOF
      Response.Write "<tr>"
      Response.Write "<td>"& objRs("FieldName1") &"</td>"
      Response.Write "<td>"& objRs("FieldName2") &"</td>"
      Response.Write "</tr>"
Loop

'Close connection to database
objRs.Close
objConn.Close

'Kill of objects
Set objRs = Nothing
Set objConn = Nothing
%>
<table>

</body>
</html>


Simple... Create a text file, put that code in it and then access it through its URL.
0
 

Expert Comment

by:tonygreer
ID: 8693893
Sorry last comment should have been:

"Simple... Create a text file, put that code in it, save it as whatever.asp and then access it through its URL.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this tutorial I will focus on how to use WhizBase as a tool for sending ICQ messages to ICQ. Here I will use a new technology in WhizBase, published in WhizBase 5.1 version. In this tutorial I will use 3 files, pager.wbsp for the processing, e…
This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
Learn the basics of modules and packages in Python. Every Python file is a module, ending in the suffix: .py: Modules are a collection of functions and variables.: Packages are a collection of modules.: Module functions and variables are accessed us…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Suggested Courses

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question