Returing Records From SQL - Tech Support Forums

tenor_david · November 5th, 2002, 12:45 PM

Hey folks.....I am writing a simple application with Perl and Oracle where the user can input a table name, and it will return the first 10 rows.

How can I expand this so that there is a 'next' button or something that will display the next 10 rows, and then the next, etc. etc.?

Here is what I have so far [code in blue retrieves 1st 10 rows]

#!/bin/perl

require './db_lib.pl';
require './cgi-lib.pl';

# read and parse the input variables
&ReadParse;

###################
# Make a connection
###################
$db = &DBlogin;

#################
# set the command
#################
$db_command = "select * from " . $in{'table'};

###################
# execute the query
###################
@data = &getdata($db, $db_command);

#################
# get the results
#################
if ($#data < 0) {
$result = "Could not find " . $in{'table'} . " in the database.";
}

while ($counter <= $#data) {
$result = "<table border>";
for ($i = 0; $i <= 9; $i++) {
$result = "$result<tr>";
for ($j = 0; $j <= $width; $j++) {
$result = "$result<td>$data[$i][$j]</td>";
}
$result = "$result</tr>";
}
$result = "$result</table>";
$counter++;
}

use CGI;
$mycgi = CGI->new;
print $mycgi->header;
print << "end";
<html>
<body>
<left>
<hr>
The query executed was:$db_command<br><br>
The result:<br>
$result

<hr>
<form action="db_form_10.pl" method=post>
<input type="submit" name="submit" value="Submit"><br><br>
Table <input type="text" name="table" value="" size="20">
</form>
</left>
</body>
</html>
end

Thanks for any input.

**Whir** · November 5th, 2002, 12:51 PM

Arrr, can you make arrays in Perl? If so, I would save all of the records to an array, then write some next/previous buttons to navigate through the array. I don't know Perl, so I can't help with the code.

tenor_david · November 5th, 2002, 12:56 PM

Yeah, the records are actuallystored in an array, one step above the blue highlighted code.

I will fiddle around w/ that idea and see what I can come up with. Thanks!

**Whir** · November 5th, 2002, 01:07 PM

Oops! Sorry, didn't even see that. I'm clueless on the data type prefixes in Perl.

All you really need then is a variable to keep track of which set you are currently displaying. So if it's 0 and they hit the next button, it jumps the variable up +10 and then iterates through the array displaying the next ten (10 - 19). I don't know if you can make global variables in Perl. If not, you would want to write some sort of search maybe and grab the data in the first field and search for it, then set your place holder variable that way?