[ic] SQL write to a table

[John Young]

Boyer, Jim wrote:
> I am trying to write data to an extra table I have created in the
> catalog database.  I am attempting this from a user tag and using the
> dbi method.  I am running IC 5.2.0 and the database is MySql.  The table
> is recognized in the IC Admin interface and I can create entries via the
> Admin interface.  Here is the code I am attempting to use without
> success:
> 
> 
> ::logDebug(qq{\nBEFORE SQL });
> 
>    # store the license data in the database under record number
>    my $dbh = $Sql{state_licenses}
>              or return HTML "Database not shared.";
> 
>    my $q = 'INSERT INTO state_licenses (order_number, state, license)'
>           . ' VALUES ("0000110", "WA", "WSDL7777")';
> 
>    my $sth = $dbh->prepare( $q )
>              or return HTML "Couldn't open database";
> 
>    $rc = $sth->execute()
>              or return HTML "Execute sql failed";
> 
>    $sth->finish();
>    $dbh->disconnect();
> 
> ::logDebug(qq{\nAFTER SQL });
> 
> This should be simple, but I can't seem to find the correct method to
> make it happen.  


Prior to the above, you need to do one of these:
     [perl tables="state_licenses"]              (in ITL/HTML file, of 
course).
  -or-
     $Tag->perl({tables => 'state_licenses'});   (in your usertag).

If you do those, you do not (should not) do the $dbh->disconnect();


Alternatively, you could create your own handle with something like:
$dbh = DBI->connect( $::Variable->{SQLDSN}, $::Variable->{SQLUSER}, 
$::Variable->{SQLPASS}, {'RaiseError' => 1} );
which would then require your to later use the $dbh->disconnect();

The first two options are better.

If you aren't doing more than one insert, there are shortcut
methods instead of prepare + execute.

In your "or return" code you might want to write to an error log
so that you'll know about any problems outside of your own testing,
as well as feed the user some kind of nice message.


-John Young