HTML Perl update button with SQL problem

Question

I'm trying to create a sort of update button, when a user clicks on a HTML Perl button it asks for confirmation (yes/no).

If the user clicks "Yes" it runs a SQL script then returns a pop-up (JavaScript) saying operation successful (or not).

The SQL code is creating a backup table then copying the current values stored in the database to this backup table, so no screen refresh is necessary. The complete and functioning code is provided below:

CREATE TABLE IF NOT EXISTS backup_tab_right_mapping
    LIKE tab_right_mapping;
DELETE FROM backup_tab_right_mapping
    WHERE group_id = "1"
    AND role_id = "1";
INSERT INTO backup_tab_right_mapping
SELECT * FROM tab_right_mapping
    WHERE group_id = "1"
    AND role_id = "1";

However I dont want to use PHP, I'm looking for a Sub solution.

Thanks for your time and any help you can provide XD

EDIT: Project requirements only allow me to use perl, HTML and JavaScript.

What do you mean by "Sub" solution, do you want a stored procedure to perform the backup? Also, you don't have to issue the DELETE after the CREATE as you have created an empty table. — Tony
– Tony, Commented Mar 7, 2011 at 13:13
@Tony The DELETE is there because it only creates the table IF it is doesn't exist, otherwise it will use the existing table. If the values already exist, they need to be deleted. When I say Sub I mean encased within a Perl Sub - that is of course if that is possible for what I want to do. — schuhmi2
– schuhmi2, Commented Mar 7, 2011 at 13:32

daxim · Accepted Answer · 2011-03-07 14:24:02Z

Works also without JavaScript. You need to add more client-side error-checking in case the program aborts abnormally.

To follow best practice, store the database credentials outside the program.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE HTML>
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en"><head><title />
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.5.1/jquery.min.js" />
</head><body><form action="backup-table" method="POST"><input type="submit" /></form>
<script>
$('form').submit(function() {
    if (confirm('Backup table?')) {
        $.post("backup-table", function(data) {
            alert($(data).text());
        });
        return false;
    };
});
</script>
</body></html>

#!/usr/bin/perl -T
use strict;
use warnings FATAL => 'all';
use CGI qw();
use DBI qw();

my $dbh = DBI->connect('dbi:…', '…username…', '…password…', {RaiseError => 1, AutoCommit => 1,});

for my $sql (
    'CREATE TABLE IF NOT EXISTS backup_tab_right_mapping
        LIKE tab_right_mapping;',
    'DELETE FROM backup_tab_right_mapping
        WHERE group_id = "1"
        AND role_id = "1";',
    'INSERT INTO backup_tab_right_mapping
    SELECT * FROM tab_right_mapping
        WHERE group_id = "1"
        AND role_id = "1";',
) {
    $dbh->do($sql);
}

my $cgi = CGI->new;
print $cgi->header('application/xhtml+xml');
print '<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE HTML>
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en"><head><title /></head>
<body><p>Backup complete</p></body></html>';

thanks for that, I'll try tomorrow and let you know, its finishing time now.

Tony · Accepted Answer · 2011-03-07 13:59:42Z

0

I am not sure why you want to do it this way. But I would do it

Create one script which does all the copy stuff keep it on server.
Create a web page with XMLHttpRequest in its submit button which inturn will be calling the original script doing the sql operation.
Capture the responseText response and print the message on your page using js, or put one alert box there.

Simpler solution is:

Create a script on server which has the submit button and one hidden input which can tell about the next operation
based on the hidden parameter call your sub available in same script as sub.
capture the response and do what ever java script magic you want to do. If you need I can provide a sample too for second approach.

edited Mar 7, 2011 at 13:59

Tony

10.4k3 gold badges51 silver badges78 bronze badges

answered Mar 7, 2011 at 13:57

awake416

3011 gold badge3 silver badges12 bronze badges

1 Comment

schuhmi2 Over a year ago

The purpose is to allow an admin user to backup or restore (code not shown) user privileges for a website, where the rights are stored in the database. The only reason I ask for it to be kept in perl is because I have to keep it simple - project requirements only allow me to use languages supported within the company (perl, HTML and JavaScript). So I am simply looking for a way to somehow run this query from a website, with a warning before, and a confirmation afterwards. other easier ways are of course welcome. Thanks,

Collectives™ on Stack Overflow

HTML Perl update button with SQL problem

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related