RedJeep has asked for the wisdom of the Perl Monks concerning the following question:

Hello folks. I have two Windows 10 computers. Running IIS. Both configured identically... well, as best as I can tell. I am trying to create a very basic CGI web site that registers users to a SQLite database. On one server, it works fine. On my desktop system the CGI page with database INSERT does not kick off any errors (that I can see) but it does not INSERT the new record. But, what is really odd, is that if I try and submit the same data again, I get a Unique Constraint error. The database has only three columns (fname, lname and email). "email" has a constraint.

Following is a snippet of the code used to create the database
my $sql = <<'END_SQL';
CREATE TABLE people (
  id       INTEGER PRIMARY KEY,
  fname    VARCHAR(100),
  lname    VARCHAR(100),
  email    VARCHAR(100) UNIQUE NOT NULL,

[download]

But, again I have this running fine on another system. Following is my test code. I have stripped out passing params and hard-coded the variables of fname, lname and email. The database currently only has one record so there are no obvious constraints.

What seems really odd to me is that the new record is not being written. No error kicked off (that I can see). But if I try to resubmit the same data (the same email) then I get the error of.

Output on first try submitting a new record from browser. But it does not actually write to the database

Test 103
dbfile: /inetpub/wwwroot/data/cgi_test1.db
lg_name: red.jeep2334@gmail.com

Output on second try submitting that same record from browser. But, again, it does not actually write to the database.

Software error:
DBD::SQLite::db do failed: UNIQUE constraint failed: people.email at C:\inetpub\wwwroot\cgi_test_sqlite.pl line 31.
For help, please send mail to this site's webmaster, giving this error message and the time and date of the error.

And I copy the same script to my other Windows 10 IIS server and it works fine. Or, I run from the command line and it INSERTs successfully.I know I should move on from CGI, but this is a small site and typically this sort of thing is super easy to do.

Following is output from SQLite query:

C:\inetpub\wwwroot\data>sqlite3 cgi_test1.db
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
sqlite> select * from people;
2|Joe|Smith|red.jeep2333@gmail.com
sqlite>

I appreciate any insight that you could provide. I don't think I have a Perl script problem. Maybe one of you has seen this. I just don't know.

#!/usr/local/bin/perl

### Setup environment 
use CGI;
use CGI::Carp qw(fatalsToBrowser);
use CGI::Session;
use CGI::Session qw/-ip-match/;  
use DBI;

my $q = CGI->new;

    ### People
    my $dbfile = "/inetpub/wwwroot/data/cgi_test1.db"; 
     
    my $dsn      = "dbi:SQLite:dbname=$dbfile";
    my $user_a     = "";
    my $password_a = "";
    my $dbh = DBI->connect($dsn, $user_a, $password_a, {
       PrintError       => 0,
       RaiseError       => 1,
       AutoCommit       => 0,
       FetchHashKeyName => 'NAME_lc',
        },) or die $dbh::errstr;
        
        

        my $fname = 'Joe';
        my $lname = 'Smith';
        my $email = 'red.jeep2334@gmail.com';   

    $dbh->do('INSERT INTO people (fname, lname, email) VALUES (?, ?, ?
+)',
    undef,
    $fname, $lname, $email);
    $dbh->commit();
    $dbh->disconnect();

print "Content-Type: text/html\n\n";
print "<html> <head>\n";
print "<title>Test</title>";
print "</head>\n";
print "<body>\n";
print "<h3>Test 103</h3>\n";
print "dbfile: $dbfile <br>\n";
print "lg_name: $email <br>\n";
print "</body> </html>\n";

[download]

Replies are listed 'Best First'.
Re: Trouble writing to SQLite from CGI on Windows
by hippo (Archbishop) on May 21, 2019 at 08:01 UTC

    Caveat: I don't use MS Windows - nobody has yet offered to pay enough.

    it does not INSERT the new record. But, what is really odd, is that if I try and submit the same data again, I get a Unique Constraint error.

    This suggests that it is indeed writing to a database, just not the same database where you go looking for the results. Perhaps your IIS is running your CGI script chrooted somewhere? Have a search through your system for other database files of the same name - maybe you will find your data there.

    PS. You could also try writing a second CGI script which just reads from the database and displays the table contents. If that magically finds the data then it proves that the above suggestion is valid.

[reply]
Re: Trouble writing to SQLite from CGI on Windows
by poj (Abbot) on May 21, 2019 at 08:34 UTC

    As Hippo suggested, here is a simple test cgi that both reads and writes to a database.

    #!perl
use strict;
use CGI;
use CGI::Carp qw(fatalsToBrowser);
use DBI;

my $q = CGI->new;
my $fname  = $q->param('fname');
my $lname  = $q->param('lname');
my $email  = $q->param('email');   
my $action = $q->param('action');

my $dbh = getDbh('c:/temp/web/cgi_test1.db');    
#createTable($dbh); # execute once

if ($action eq 'Add'){  
  $dbh->do('INSERT INTO people (fname, lname, email) VALUES (?, ?, ?)'
+,
  undef,$fname, $lname, $email);
} elsif ($action eq 'Delete All'){  
  $dbh->do('DELETE FROM people');
}
$dbh->commit();

my $table = join "\n", 
  map { join " | ",@$_ } 
  $dbh->selectall_array('SELECT * FROM people');    
my $now = scalar localtime;

print $q->header;
print << "END_HTML";
<head><title>Test</title></head>
<body>
People
<hr><pre>$table</pre><hr>
<form action = "" method="post">
fname  <input type="text" name="fname" value="Joe"><br/>
lname  <input type="text" name="lname" value="Smith"><br/>
email  <input type="text" name="email" value="joesmith\@somewhere.com"
+><br/>
  <input type="submit" name="action" value="Add">
  <input type="submit" name="action" value="Delete All">
</form>
$now</body></html>
END_HTML

sub getDbh {
  my $dbfile = shift;
  my $dsn = "dbi:SQLite:dbname=$dbfile";
  my $dbh = DBI->connect($dsn, '', '', {
     PrintError => 0, RaiseError => 1,
     AutoCommit => 0, 
     FetchHashKeyName => 'NAME_lc',
   } ) or die $dbh::errstr;
};

sub createTable {
  my $dbh = shift;
  my $sql = <<'END_SQL';
  CREATE TABLE people (
    id       INTEGER PRIMARY KEY,
    fname    VARCHAR(100),
    lname    VARCHAR(100),
    email    VARCHAR(100) UNIQUE NOT NULL
  );
END_SQL
  $dbh->do($sql);
  $dbh->commit();
};

    [download]
    poj
[reply]
[d/l]
Re: Trouble writing to SQLite from CGI on Windows
by RedJeep (Sexton) on May 22, 2019 at 05:32 UTC

    Ok my Perl friends. I super appreciate your help. The problem is partially solved but more bizarre. I found that the system is duplicating the database file in a directory called... C:\BOXRoot\HarddiskVolume2\inetpub\wwwroot\data

    Now, I have no idea what "BOXRoot" is. I did some searching on the Internet. No luck. I do not have dropbox.

    The behavior is super crazy but 100% consistent.

    If I create the database in the proper directory c:\inetpub\wwwroot\data and run the CGI script it will recreate the database in the c:\BOXRoot\..... directory and INSERT the record correctly. If I do not create the database at all and just plug in some whatever random database name then an empty (0 byte) database will be created in "BOXRoot". That ("0" byte) database does not allow writes.

    I have done virus scans and found nothing. This is my dev machine. I know where the directory is now. Although I have no idea where it came from I am going to use as is. My thinking is that I must have mucked up some config setting somewhere.

    I thought it only fair to share what I found in the event that someone else comes across the same problem. And, if in teh CGI script I open a filehandle to create a txt file and write to it... well you can guess where it ends up also... BOXRoot. So this is not a SQLite issue.

    Thanks again everyone!!!
[reply]

      Do you run Comodo anti virus or security software? It looks like the sandboxing feature.

[reply]

        Yes, I run Comodo. You, my friend, are very smart. That must be what is going on.

        Thanks!

        ---RedJeep
[reply]

Back to Seekers of Perl Wisdom