Perl~MySQL question

[tenor_david]

If you have a form that asks for a table, and returns a

SELECT * from $table

How would you incorporate a 'safety' element for when the user inputs a table not in a database?

Rather than getting a 'blank' response, the user should see something like:

"The table you requested is not in the database."


*edited for slight retardation*

[jkrohn]

Hmm, I have little knowledge of SQL, but this seems possible.

1) Make a sub function called in_database
2) In this function compare the entered table to all the tables in teh database. If you get a match return 1. If not return zero with a message "Table not found"

Then in main run
if(in_database) for your select query


Jkrohn

[tenor_david]

Let's back up a step....how do compare the entered table to tables that you have?

I am a SuperNewbie. I know next to nothing.

[vass0922]

I don't know how to do it mysql, but in sql server there is a table called sysobjects in each database. This is a special system table that holds all of the objects in that database.. including tables! So if you can find the table that stores all of the table names in the database that'd be one way

OR ... you could create your own table that stores that information.

[^hyd^]

had a quick look at mysql docs, and couldn't come up with much of a query to check for a single table in a db... but that doesn't neccessarily mean the functionality doesn't exist tho!! might be out there somewhere...

you can however, return "all" the tables in a db with the statement "SHOW TABLES FROM your_database_name", but I'm not sure if it returns a proper query to loop thru and check for your table. you'd have to try it out...

anyways, I've never done perl w/mysql, just php, but I just wrote a quick script and it appears that mysql returns an error number of 1146 if the table doesn't exist... I imagine it'd be the same in perl... but might not, again, you'd have to try it out!!

so... you could do the query, then test the error code from mysql, if it is 1146 then the table doesn't exist... and display your message...

anyways, early morning tomorrow, bedtime!!

cheers and good luck!

[tenor_david]

Sorry, I am an idiot. I meant Perl ~ SQLplus. Any thoughts on how to do it with SQLplus?

[^hyd^]

DOH!!! well, could still do similar to how I mentioned... find the error number for "table doesn't exist" (ie: run a query on a table that ain't there, and get the sql error number) then test for it and act on it accordingly...

once again, g'night!!



dang it's hard to leave this computer to go to bed!!!

[vass0922]

sqlplus meaning oracle?

[tenor_david]

Yes, I do mean Oracle.

I got it to work. We had to load the results into and array, so I used:

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

to check and see if there were no elements in the array.

Now I need to figure out how to display the records 10 records/page. I can get the query to only output 10 records via:

$db_command = "select * from " . $in{'table'} . " where rownum<=10";

But of course that only gives you 10 of xxx records...... Anyone have any pointers on getting all of the records and displaying them in xx amount of pages?