Re: Select Multiple Form Field via Perl into Oracle error

From: Jacqui Caren <Jacqui.Caren_at_ig.co.uk>
Date: Tue, 31 Aug 1999 12:54:17 GMT
Message-ID: <FHByIH.Kp8@ig.co.uk>

In article <ej%w3.1$2B2.769_at_news.enterprise.net>, Simmo <simsi_at_hotmail.com.nospam> wrote:
>Hi,
>
>Apologies for submitting this to both groups but I'm relatively new to
>Oracle and i'm not sure which is relevant to this problem - it may even be
>an HTML thing although i probably need a Perl line or an SQL statement to
>rectify it:-).
>
>I have a web Form with a select multiple attribute which Perl takes and
>sends to an Oracle 8.1 table 'news'. If i select 1 item its fine...if i
>select more than one i get:
>
>DBD::Oracle::db prepare failed: ORA-01756: quoted string not properly
>terminated (DBD ERROR: OCIStmtPrepare) at news.pl line 855.
>
>....although my perl script suggests there is nothing unusual about the
>multiple FORM string, i have a sneaking suspicion that there's a funny
>character in there that i need to remove in the Perl script before Inserting
>into my table.
>
>Anyone come across this before? - i cant seem to find anything that sugegsts
>there *is* a character in there but....
>
>An example string i'm writing incidentally is:
>
>$categories_selected = $FORM{'categories'};

$categories_selected = $dbh->escape($FORM{'categories'});

should fix your problem...

>$SQL1 = "INSERT into news (type) VALUES ('$categories_selected')";

>my $sql = qq{ $SQL1 };

why do you do this? It has no real effect...

>my $sth=$dbh->prepare( $sql );
>$sth->execute();

If you can use bind variables you can do the following

my $sth = $dbh->prepare('insert into news (type) values (?)'); $sth->execute($FORM{categories});
$sth->finish();

Of course a much better approach would be something such as...

my $sth = $dbh->prepare('insert into news (type) values (?)'); die $dbh->errstr if $dbh->err;
foreach my $cat ($cgi->param('categories')) {

	$sth->execute($cat);
	warn $dbh->errstr if $dbh->err;

}#
$sth->finish();
die $dbh->errstr if $dbh->err;

this will create one entry for each button clicked - and 'warn' you if an entry could not be created for some reason...

Jacqui

--
Paul Ingram Group Ltd,140A High Street,Godalming GU7 1AB United Kingdom Email: J.Caren_at_ig.co.uk Fax: +44 1483 862801 Phone: +44 1483 862800 A rather special Perl vacancy :-) http://www.ig.co.uk/about/jobs.html Use SmartCards in e-commerce systems? http://www.smartaxis.com/ Received on Tue Aug 31 1999 - 07:54:17 CDT