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]

In reply to Trouble writing to SQLite from CGI on Windows by RedJeep

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.