[ic] SQL write to a table

[Daniel Davenport]

(pardon any icky formatting. Work makes me use groupwise.  :P )

>>> boyerj at wsu.edu 12/02/04 07:09PM >>>
>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 });

try this instead.  :)
 
$sql = "INSERT INTO state_licenses (order_number, state, license)" 
     . "VALUES ('0000110', 'WA', 'WSDL7777')";
$Tag->query({sql => $sql, table => "state_licenses" }) or return HTML
"Insert failed."; 
 
BTW, if you intend to use cgi variables and such in the insert instead
of the values you have in there right now, you'll want to (read: you
better) filter the results using either
 
$some_value =~ s/([\\'])/\1\1/g;
 
or
 
$value = $Tag->filter({op => 'sql'}, $value);
 
the former is preferable for mysql databases, as it also escapes
backslashes (which mysql can use for quoting, unlike most other
dbms's).
 
/me